conditional cumsum in pandas

Question

I have following dataframe in pandas

   code     rank    quant       sales
   123      1       0           2
   123      1       12          2
   123      1       0           2
   123      2       0           1
   123      2       10          1

I want to do a conditional cumsum of sales groupby rank. where quant is not zero add it in cumulative sum on the same row.

   code     rank    quant       sales      cumsum
   123      1       0           2          2
   123      1       12          2          16
   123      1       0           2          18
   123      2       0           1          1
   123      2       10          1          12

How to do it in pandas.

jezrael · Accepted Answer · 2019-08-23T11:39:12.980

2

Add columns first and then use GroupBy.cumsum with df['rank'] Series:

df['cumsum'] = df['quant'].add(df['sales']).groupby(df['rank']).cumsum()

Or use sum by both columns:

df['cumsum'] = df[['quant', 'sales']].sum(axis=1).groupby(df['rank']).cumsum()

Alternative is create new column before groupby:

df['cumsum'] = (df.assign(cumsum=df['quant'].add(df['sales']))
                  .groupby('rank')['cumsum'].cumsum())

print (df)
   code  rank  quant  sales  cumsum
0   123     1      0      2       2
1   123     1     12      2      16
2   123     1      0      2      18
3   123     2      0      1       1
4   123     2     10      1      12

edited Aug 23 '19 at 11:39

answered Aug 23 '19 at 11:32

jezrael

822,522
95
1,334
1,252

What if my groupby is with 2 variables – Neil Aug 23 '19 at 11:46
@Neil - Then use last solution or first 2 with `df['rank']` change to `[df['code'], df['rank']]` – jezrael Aug 23 '19 at 11:47

conditional cumsum in pandas

1 Answers1