group by week in pandas
First, convert column date
to_datetime
and subtract one week as we want the sum for the week ahead of the date and not the week before that date.
Then use groupby
with Grouper
by W-MON and aggregate sum
:
df['Date'] = pd.to_datetime(df['Date']) - pd.to_timedelta(7, unit='d')
df = df.groupby(['Name', pd.Grouper(key='Date', freq='W-MON')])['Quantity']
.sum()
.reset_index()
.sort_values('Date')
print (df)
Name Date Quantity
0 Apple 2017-07-10 90
3 orange 2017-07-10 20
1 Apple 2017-07-17 30
2 Orange 2017-07-24 40
Let's use groupby
, resample
with W-Mon
, and sum
:
df.groupby('Name').resample('W-Mon', on='Date').sum().reset_index().sort_values(by='Date')
Output:
Name Date Quantity
0 Apple 2017-07-17 90
3 orange 2017-07-17 20
1 Apple 2017-07-24 30
2 Orange 2017-07-31 40