Append current row with data from next row
3 views (last 30 days)
I have a set of data (provided) that is 25 rows by 4001 columns (including the timestamp/date column). The timestamps are irregular and at different durations. Some rows are 30 seconds while other are 1 minute.
I would like to retime the dataset to 1-min resolution. However I'm not clear on how to keep the data found in pairs of 30 second data (which are combined in the retime function) when I run retime command.
For matching pairs of 30 second rows (13:00:00 and 13:00:30) that combine to 1 minute all values should be retained and placed at the end of the other filling in existing columns with 'NAN' placeholders.
The data is broken into this format:
timestamp, [1, 1], [1, 2], ..., [1, n], [2, 1], [2, 2], ..., [2, n]. Note that there is a location relationship for this data that needs to be maintained. The relationship is: A = [1,1], B = [2,1] and C = [A, B]. I need to be able to use the paired value in C.
In the sample dataset n=2000. Every row can have different values of total [x, n]. Meaning one row can have filled cells to [1,300], [2,300] while the next can have [1,27], [2, 27].
The remaining cells are filled with 'NAN' values as placeholders.
In the scenario of these two rows above, the final product creates a single row where the first 27 values of original row 2 are moved to the first value of 'NAN' in the original row 1. Then the values from column 2001-2027 would be placed to the first 'NAN' found after column 2000 (to make sure that the the pairs still match.
Let me try and illustrate as well it may help explain what I want to do.
30 seconds data: A = [1 2 3 'NAN' 'NAN' 'NAN' 'NAN' 1 5 7 'NAN' 'NAN' 'NAN' 'NAN']
30 seconds data: B = [4 2 'NAN' 'NAN' 'NAN' 'NAN' 'NAN' 1 3 'NAN' 'NAN' 'NAN' 'NAN' 'NAN']
You can see that in this small example that there are 14 total placeholders in each row. Some have values, others do not. The first seven placeholders are paired with the second 7 placeholders.
When we combine them the final product should be:
1 minute data: C = [1 2 3 4 2 'NAN' 'NAN' 1 5 7 1 3 'NAN' 'NAN']