groupby cumsum sorted dataframe

user_3379546 0
Viewed 218

I want to group a dataframe by a column then apply a cumsum over the other ordered by the first column descending

df1:
id   PRICE  DEMAND
0      120     10
1      232     2
2      120     3
3      232     8
4      323     5
5      323     6
6      323     2

df2:
id   PRICE  DEMAND
0      323     13
1      232     23
2      120     36

I do it in two instructions but I am feeling it can be done with only one sum

data = data.groupby('PRICE',as_index=False).agg({'DEMAND': 'sum'}).sort_values(by='PRICE', ascending=False)
data['DEMAND'] = data['DEMAND'].cumsum()
cumsum pandas dataframe python
1 Answers

What you have seems perfectly fine to me. But if you want to chain everything together, first sort then groupby with sort=False so it doesn't change the order. Then you can sum within group and cumsum the resulting Series

(df.sort_values('PRICE', ascending=False)
   .groupby('PRICE', sort=False)['DEMAND'].sum()
   .cumsum()
   .reset_index())

   PRICE  DEMAND
0    323      13
1    232      23
2    120      36

Another option would be to sort then cumsum and then drop_duplicates:

(df.sort_values('PRICE', ascending=False)
   .set_index('PRICE')
   .DEMAND.cumsum()
   .reset_index()
   .drop_duplicates('PRICE', keep='last'))

   PRICE  DEMAND
2    323      13
4    232      23
6    120      36
user_43333590
Related