Dealing with last hour in a resampled year in pandas

user_10251239 0
Viewed 77

I have been dealing with time-series with one entry per hour over a year. In order to better analyse the data, I have been resampling by month with pandas and summing the results with df = df.resample('M').sum()

As the last hour of the last day runs from 23:00 31/12 to 00:00 01/01 of the following year, the final hour is resampled into January of the following year (e.g. my time-series is for 2020, the last hour of 31/12/2020 is resampled into January 2021). This means I lose data for December.

I have considered adding the data back in to December, but is there a better way to achieve this?

pandas time-series python-3.x
1 Answers

Unfortunately you need add it to previous hour, e.g. by:

rng = pd.date_range('2017-12-31 12:00:00', periods=13, freq='H')
df = pd.DataFrame({'a': range(len(rng))}, index=rng)  

y = df.index.year
df.index = df.index.where(y != y.max(),  df.index - pd.Timedelta(1, unit='H'))
print (df)
                      a
2017-12-31 12:00:00   0
2017-12-31 13:00:00   1
2017-12-31 14:00:00   2
2017-12-31 15:00:00   3
2017-12-31 16:00:00   4
2017-12-31 17:00:00   5
2017-12-31 18:00:00   6
2017-12-31 19:00:00   7
2017-12-31 20:00:00   8
2017-12-31 21:00:00   9
2017-12-31 22:00:00  10
2017-12-31 23:00:00  11
2017-12-31 23:00:00  12


df = df.resample('M').sum()
print (df)

             a
2017-12-31  78
user_29010020
Related