I have a time series data, converted to a dataframe. I have multiple columns, where the first column is timestamps and rest of the column names are timestamps with values.
The dataframe looks like
date 2022-01-02 10:20:00 2022-01-02 10:25:00 2022-01-02 10:30:00 2022-01-02 10:35:00 2022-01-02 10:40:00 2022-01-02 10:45:00 2022-01-02 10:50:00 2022-01-02 10:55:00 2022-01-02 11:00:00
2022-01-02 10:30:00 25.5 26.3 26.9 NaN NaN NaN NaN NaN NaN
2022-01-02 10:45:00 60.3 59.3 59.2 58.4 56.9 58.0 NaN NaN NaN
2022-01-02 11:00:00 43.7 43.9 48 48 48.1 48.9 49 49.5 49.5
Note that if value in date column matches with columns names, there are NaNs after the intersecting column.
The dataframe I am trying to achieve is as below where the column names are the minutes before date (40,35,30,25,20,15,10,5,0) and the values remain the same:
For example : 1) 2022-01-02 10:30:00 - 2022-01-02 10:30:00 = 0 mins, hence the corresponding value there should be 26.9. 2) 2022-01-02 10:30:00 - 2022-01-02 10:25:00 = 5 mins, hence the value there should be 26.3 and so on.
Note - values with * are dummy values to represent.
date 40mins 35mins 30mins 25mins 20mins 15mins 10mins 5mins 0mins
2022-01-02 10:30:00 24* 24* 24.8* 24.8* 25* 25* 25.5 26.3 26.9
2022-01-02 10:45:00 59* 58* 60* 60.3 59.3 59.2 58.4 56.9 58.0
2022-01-02 11:00:00 43.7 43.9 48 48 48.1 48.9 49 49.5 49.5
I understand the difference in times should be calculated but I am not sure on how to go about it. Would highly appreciate your help with this.