How to extrapolate to 10 min granularity from timestamps and backfill the columns

user_15299852 0
Viewed 29

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?

pandas dataframe python-3.x python
1 Answers

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]
user_163434640
Related