I have a data table with POSIXct datetime (t1) data which is grouped by multiple columns. My data should have 48 rows at half hourly intervals (24 hrs total) for each group, whose datetime (t1) value is determined by another datetime (t2) variable used as part of the grouping variables. The 48 rows of (t1) data should be 24 hours of half hourly intervals leading up to the grouped datetime (t2).
I need the missing rows to be populated with the values from the previous (chronologically) complete row of data for each group.
For example: If the data grouped by ID = A, datetime (t2) = 10-01-2019 06:00:00 (format = 'dd-mm-yyyy hh:mm:ss') has datetime (t1) data that spans from 09-01-2019 08:00:00 until 10-01-2019 04:00:00. It should span from 09-01-2019 06:00:00 until 10-01-2019 05:30:00, meaning 4 rows of data (2 hrs) are missing from the beginning, 3 rows of data are missing from the end, and several rows are missing in between the available data points as well. While rows missing at the start of the time period cannot be filled in with complete data, I would still like the rows to be added to the table as NA. However it is very important for the rows at the end of the table to be populated with complete data from the previous complete row of data.
An example of my dataset is shown below: (grouped by ID and t2)
ID t2 t1 varX varY
...
A 10-01-2019 06:00:00 10-01-2019 12:00:00 100 Y1
A 10-01-2019 06:00:00 10-01-2019 12:30:00 60 Y2
B 10-01-2019 06:00:00 10-01-2019 01:00:00 100 Y2
A 10-01-2019 06:30:00 10-01-2019 01:30:00 100 Y3
B 10-01-2019 06:30:00 10-01-2019 02:00:00 200 Y4
C 10-01-2019 07:00:00 10-01-2019 04:00:00 100 Y3