CODEWITHSUNDEEP
pythonpandasdataframe
user6435943
user6435943

Reputation: 199

Resampling daily to monthly with 'month-end minus t days' offset in pandas

I have daily timeseries data in a pandas dataframe. I need to resample this to monthly using different offsets from a standard month-end frequency.

dates = pd.date_range('2016-09-01', '2017-01-10')
df = pd.DataFrame(data=[x for x in range(len(dates))],index=dates,columns=['MyData'])

I can get to a monthly series with month end frequency:

df_monthly = df.resample('M').last()

df_monthly
Out[78]: 
            MyData
2016-09-30      29
2016-10-31      60
2016-11-30      90
2016-12-31     121
2017-01-31     131

If Month End = 'M', I would like to have similar monthly slices for 'M-15', ...'M-2', 'M-1', 'M', 'M+1', M+2', ...'M+15'. Ultimately I plan to combine these into a single dataframe with column names 'M+T'.

Is there an easy way to do this within pd.DataFrame.resample() or pd.DataFrame.asfreq()?

Upvotes: 4

Views: 11801

Answers (1)

gold_cy
gold_cy

Reputation: 14216

So as per the several ways you asked here is how it is done.

import pandas as pd
dates = pd.date_range('2016-09-01', '2017-01-10')
df = pd.DataFrame(data=[x for x in range(len(dates))],index=dates,columns=['MyData'])

df_monthly_m1 = df.shift(-1).resample('M').last()
df_monthly_m1

            MyData
2016-09-30    30.0
2016-10-31    61.0
2016-11-30    91.0
2016-12-31   122.0
2017-01-31   131.0

df_monthly_p1 = df.shift(1).resample('M').last()
df_monthly_p1

            MyData
2016-09-30    28.0
2016-10-31    59.0
2016-11-30    89.0
2016-12-31   120.0
2017-01-31   130.0

Upvotes: 5

Related Questions