pandas resample by a specific date

user_11547552 0
Viewed 252

I try to resample weekly data to 4W basis but with arbitrary start week, however, the base option doesnot do the work. For example:

index = pd.date_range('1/1/2020', periods=14, freq='W')
series = pd.Series(range(14), index=index)             
print(series) 
2020-01-05     0
2020-01-12     1
2020-01-19     2
2020-01-26     3
2020-02-02     4
2020-02-09     5
2020-02-16     6
2020-02-23     7
2020-03-01     8
2020-03-08     9
2020-03-15    10
2020-03-22    11
2020-03-29    12
2020-04-05    13

default 4W bin by pandas:

print(series.resample('4W', label='left').sum()) 
2019-12-08     0
2020-01-05    10
2020-02-02    26
2020-03-01    42
2020-03-29    13
Freq: 4W-SUN, dtype: int64

What I need is the sum of 4 weeks from 1/19 to 2/9, instead of the default bin by pandas above.

pandas base python
1 Answers

You have to shift the Series by 2 positions. If the first cumulated value does not matter, you could remove the first two point:

print(series.iloc[2:].resample('4W', label='left').sum())

to get:

2019-12-22     2
2020-01-19    18
2020-02-16    34
2020-03-15    36
Freq: 4W-SUN, dtype: int64

But as the first rows are gone, the first sum is wrong. The correct way is to add 2 rows before the series, sum it, and discard the first row:

print(series.append(pd.Series(0, index=pd.date_range(end = series.index[0], periods=3, freq='W')[:2])
                    ).sort_index().resample('4W', label='left').sum()[1:])

You now get correct values in each row:

2019-12-22     3
2020-01-19    18
2020-02-16    34
2020-03-15    36
Freq: 4W-SUN, dtype: int64
user_35452730
Related