I have two dataframes df1
and df2
.
df1
:
Id Date Remark
0 28 2010-04-08 xx
1 29 2010-10-10 yy
2 30 2012-12-03 zz
3 31 2010-03-16 aa
df2
:
Id Timestamp Value Site
0 28 2010-04-08 13:20:15.120 125.0 93
1 28 2010-04-08 13:20:16.020 120.0 94
2 28 2010-04-08 13:20:18.020 135.0 95
3 28 2010-04-08 13:20:18.360 140.0 96
...
1000 29 2010-06-15 05:04:15.120 16.0 101
1001 29 2010-06-15 05:05:16.320 14.0 101
...
I would like to select all Value
data 10 days before/including the Date
in df1
from df2
for the same Id
. For example, for Id 28
, Date
is 2010-04-08
, so select Value
where Timestamp
is between 2010-03-30 00:00:00
and 2010-04-08 23:59:59
(inclusive).
Then, I want to resample the Value
data using forward fill ffill
and backward fill bfill
at 1min
frequency so that there will be 10 x 24 x 60 = 14400
values exactly for each Id
.
Lastly, I'd like to rearrange the dataframe horizontally with transpose
.
Expected output looks like this:
Id Date value1 value2 ... value14399 value14400 Remark
0 28 2010-04-08 125.0 125.0 ... ... xx (value1 and value2 and following values before "2010-04-08 13:20:15.120" are 125.0 as a result of backward fill since the first value for Id 29 is 125.0)
1 29 2010-10-10 16.0 16.0 ... yy
...
I am not sure what's the best way to approach this problem since I'm adding "another time series dimension" to the dataframe. Any idea is appreciated.