This question can be seen as an extension of the following question:
Resample hourly TimeSeries with certain starting hour
I have the following dataframe:
import pandas as pd
index = pd.date_range(start='2023-01-01 17:00', periods=24*59, freq='H') # two months of data.
df = pd.DataFrame(data={'values':1}, index=index)
that looks like:
values
2023-01-01 17:00:00 1
2023-01-01 18:00:00 1
... ...
2023-03-01 15:00:00 1
2023-03-01 16:00:00 1
I would like to perform resampling on this dataframe, where the sum of values is calculated for each month, starting from the 17th hour of one month and extending up to the 17th hour of the following month. The resulting dataframe should have the following structure:
values
2023-01-01 17:00:00 744
2023-02-01 17:00:00 672
I can not seem to figure out how to perform this operation. I tried:
df.resample('MS', offset='17H').sum()
to no avail. Any help/suggestions on what would be the 'pythonic' way to do this?
UPDATE: I found out the exact same question was asked earlier. This question also contained a work-around and discusses the problem further, including a filed bug report: Pandas: resample hourly values to monthly values with offset
