A cumulative sum that resets is equivalent to apply it to groups : each new group will reset the cumulative sum when it starts.
It is always easier to illustrate an answer with a good minimal reproducible example :
df = pd.DataFrame([
['20191224', '20191224 01:00', 50, 'Merry'],
['20191224', '20191224 02:30', 50, 'Christmas'],
['20191225', '20191225 02:00', 50, 'Merry'],
['20191225', '20191225 04:25', 50, 'Christmas'],
['20191225', '20191225 06:50', 50, ':)']],
columns = ['date_str', 'date_time', 'quantity', 'msg'])
To make sure to cast ‘date_time’ column to actual timestamps (all time formats in documentation)
df['date_time'] = pd.to_datetime(df['date_time'], format = "%Y%m%d %H:%M")
To make sure that your dates are ordered (important as you will cumsum
)
df = df.sort_values('date_time')
You can groupby date_str
as they are represent your daily groups :
df.groupby('date_str').agg({
'quantity': 'sum',
'message': lambda x: x.join(' ')
})
quantity msg
date_str
20191224 100 Merry Christmas
20191225 150 Merry Christmas :)
In your case, what you want is to transform
cumsum
:
df['daily_cum_quantity'] = df.groupby('date_str')['quantity'].transform('cumsum')
Resulting in :
date_str date_time quantity msg cum_quantity
0 20191224 2019-12-24 01:00:00 50 Merry 50
1 20191224 2019-12-24 02:30:00 50 Christmas 100
2 20191225 2019-12-25 02:00:00 50 Merry 50
3 20191225 2019-12-25 04:25:00 50 Christmas 100
4 20191225 2019-12-25 06:50:00 50 :) 150