I have the data that logs 3 times a day (morning-afternoon-night):
df1
timestamp var1
0 2020-07-05 10:32:00 2
1 2020-07-05 18:22:00 8
2 2020-07-05 22:30:00 6
3 2020-07-06 09:13:00 5
I want to round the timestamp into 10 min intervals and backfill var1 for 3-hours before the timestamp, and display var1 for the remaining hours as "N/A", as such:
df1
timestamp var1
0 2020-07-05 07:20:00 N/A
1 2020-07-05 07:30:00 2
2 2020-07-05 07:40:00 2
3 2020-07-05 07:50:00 2
...
12 2020-07-05 10:30:00 2
13 2020-07-05 10:40:00 N/A
...
92 2020-07-05 18:10:00 8
93 2020-07-05 18:20:00 8
94 2020-07-05 18:30:00 N/A
Any idea how I could achieve this?
CodePudding user response:
IIUC, add a dummy value 3 hours before, then resample
with bfill
:
df['timestamp'] = pd.to_datetime(df['timestamp'])
(pd.concat([pd.DataFrame({'timestamp': [df['timestamp'].min()-pd.Timedelta('3h')]}), df])
.set_index('timestamp')
.resample('10min')
.bfill(3*6) # 3h = 18 * 10min
.reset_index()
)
Output:
timestamp var1
0 2020-07-05 07:30:00 NaN
1 2020-07-05 07:40:00 2.0
2 2020-07-05 07:50:00 2.0
3 2020-07-05 08:00:00 2.0
4 2020-07-05 08:10:00 2.0
.. ... ...
17 2020-07-05 10:20:00 2.0
18 2020-07-05 10:30:00 2.0
19 2020-07-05 10:40:00 NaN
.. ... ...
150 2020-07-06 08:30:00 5.0
151 2020-07-06 08:40:00 5.0
152 2020-07-06 08:50:00 5.0
153 2020-07-06 09:00:00 5.0
154 2020-07-06 09:10:00 5.0
[155 rows x 2 columns]