pandas monthly resample 15th day

user_5080612 0
Viewed 3299

I am trying to resample to monthly values but with respect to 15th day

I checked the timeseries offsets documentation but there is only

M month end frequency SM semi-month end frequency (15th and end of month) MS month start frequency SMS semi-month start frequency (1st and 15th)

while I need just the 15th day

Something like

2000-01-15 8.7
2000-02-15 6.9
2000-03-15 15.8
2000-04-15 12.4

I tried with pd.offsets.MonthBegin and MonthOffset with no results

pandas resampling python
2 Answers

Aggregate by starts of months MS and then adjust the resampled time labels by loffset parameter:

df1 = df.resample('MS', loffset=pd.Timedelta(14, 'd')).sum()

Sample:

rng = pd.date_range('2017-04-03', periods=15, freq='5D')
df = pd.DataFrame({'a': range(15)}, index=rng)  
print (df)
             a
2017-04-03   0
2017-04-08   1
2017-04-13   2
2017-04-18   3
2017-04-23   4
2017-04-28   5
2017-05-03   6
2017-05-08   7
2017-05-13   8
2017-05-18   9
2017-05-23  10
2017-05-28  11
2017-06-02  12
2017-06-07  13
2017-06-12  14

df1 = df.resample('MS', loffset=pd.Timedelta(14, 'd')).sum()
print (df1)
             a
2017-04-15  15
2017-05-15  51
2017-06-15  39

df1 = df.resample('SMS').sum()
print (df1)
             a
2017-04-01   3
2017-04-15  12
2017-05-01  21
2017-05-15  30
2017-06-01  39
user_29010020

The other answer is deprecated in pandas 1.4.2 and comes with a warning FutureWarning: 'loffset' in .resample() and in Grouper() is deprecated.

The recommended alternative is do first resample normally, then add a Timedelta to the index:

df1 = df.resample('MS').sum()
df1.index += pd.Timedelta(14, 'd')
user_94390970
Related