CODEWITHSUNDEEP
pythonpandas
Satyajit Pattnaik
Satyajit Pattnaik

Reputation: 135

Resampling hourly data to 6 hours

              Timestamp                  Value
0   2017-11-22 09:00:00                 12.356965
1   2017-11-22 10:00:00                 26.698426
2   2017-11-22 11:00:00                 13.153104
3   2017-11-22 12:00:00                 15.425182
4   2017-11-22 13:00:00                 15.161085
5   2017-11-22 14:00:00                 17.038580
6   2017-11-22 15:00:00                 11.035375
7   2017-11-22 16:00:00                  5.208686
8   2017-11-22 17:00:00                  6.026359
9   2017-11-22 18:00:00                  6.259712
10  2017-11-22 19:00:00                 21.792882
11  2017-11-22 20:00:00                  9.053889

Let say, above is my dataframe, i need to resample the data for 6 hours, so for 9:00, the value should be average of data from 9,10,11,12,13,14.. Similarly for 10, the value should be average of data from 10, 11, 12, 13, 14, 15... and so on.......

Upvotes: 1

Views: 1595

Answers (1)

cs95
cs95

Reputation: 402922

You can use rolling.mean:

df.set_index('Timestamp').rolling('6h').mean()

                         Value
Timestamp                     
2017-11-22 09:00:00  12.356965
2017-11-22 10:00:00  19.527696
2017-11-22 11:00:00  17.402832
2017-11-22 12:00:00  16.908419
2017-11-22 13:00:00  16.558952
2017-11-22 14:00:00  16.638890
2017-11-22 15:00:00  16.418625
2017-11-22 16:00:00  12.837002
2017-11-22 17:00:00  11.649211
2017-11-22 18:00:00  10.121633
2017-11-22 19:00:00  11.226932
2017-11-22 20:00:00   9.896151

Alternative using asfreq + rolling.mean + shift:

df.set_index('Timestamp').asfreq('h').rolling(6).mean().shift(-5)

                         Value
Timestamp                     
2017-11-22 09:00:00  16.638890
2017-11-22 10:00:00  16.418625
2017-11-22 11:00:00  12.837002
2017-11-22 12:00:00  11.649211
2017-11-22 13:00:00  10.121633
2017-11-22 14:00:00  11.226932
2017-11-22 15:00:00   9.896150
2017-11-22 16:00:00        NaN
2017-11-22 17:00:00        NaN
2017-11-22 18:00:00        NaN
2017-11-22 19:00:00        NaN
2017-11-22 20:00:00        NaN

The result is the same as before, but shifted by 5 places.

Upvotes: 3

Related Questions