Pandas groupby cumulative sum

Question

I would like to add a cumulative sum column to my Pandas dataframe so that:

name	day	no
Jack	Monday	10
Jack	Tuesday	20
Jack	Tuesday	10
Jack	Wednesday	50
Jill	Monday	40
Jill	Wednesday	110

becomes:

Jack | Monday     | 10  | 10
Jack | Tuesday    | 30  | 40
Jack | Wednesday  | 50  | 90
Jill | Monday     | 40  | 40
Jill | Wednesday  | 110 | 150

I tried various combos of df.groupby and df.agg(lambda x: cumsum(x)) to no avail.

To create both columns using a one-liner, use [this answer](https://stackoverflow.com/a/73729501/19123103). — cottontail, Nov 16 '22 at 22:00

score 143 · Answer 1 · edited Apr 04 '20 at 21:44

This should do it, need groupby() twice:

df.groupby(['name', 'day']).sum() \
  .groupby(level=0).cumsum().reset_index()

Explanation:

print(df)
   name        day   no
0  Jack     Monday   10
1  Jack    Tuesday   20
2  Jack    Tuesday   10
3  Jack  Wednesday   50
4  Jill     Monday   40
5  Jill  Wednesday  110

# sum per name/day
print( df.groupby(['name', 'day']).sum() )
                 no
name day           
Jack Monday      10
     Tuesday     30
     Wednesday   50
Jill Monday      40
      Wednesday  110

# cumulative sum per name/day
print( df.groupby(['name', 'day']).sum() \
         .groupby(level=0).cumsum() )
                 no
name day           
Jack Monday      10
     Tuesday     40
     Wednesday   90
Jill Monday      40
     Wednesday  150

The dataframe resulting from the first sum is indexed by 'name' and by 'day'. You can see it by printing

df.groupby(['name', 'day']).sum().index

When computing the cumulative sum, you want to do so by 'name', corresponding to the first index (level 0).

Finally, use reset_index to have the names repeated.

df.groupby(['name', 'day']).sum().groupby(level=0).cumsum().reset_index()

   name        day   no
0  Jack     Monday   10
1  Jack    Tuesday   40
2  Jack  Wednesday   90
3  Jill     Monday   40
4  Jill  Wednesday  150

What a brute method to achieve the result, wished this was simple in pandas — MGLondon, Mar 29 '23 at 21:53

score 81 · Answer 2 · answered Mar 30 '18 at 16:49

Modification to @Dmitry's answer. This is simpler and works in pandas 0.19.0:

print(df) 

 name        day   no
0  Jack     Monday   10
1  Jack    Tuesday   20
2  Jack    Tuesday   10
3  Jack  Wednesday   50
4  Jill     Monday   40
5  Jill  Wednesday  110

df['no_csum'] = df.groupby(['name'])['no'].cumsum()

print(df)
   name        day   no  no_csum
0  Jack     Monday   10       10
1  Jack    Tuesday   20       30
2  Jack    Tuesday   10       40
3  Jack  Wednesday   50       90
4  Jill     Monday   40       40
5  Jill  Wednesday  110      150

This works but you need to be careful with the order of the 'day' column. For example, if 'day' was in alphabetical order, 'no_csum' probably wouldn't reflect the information you actually need. — ovpira, Jun 26 '23 at 07:12

score 59 · Answer 3 · answered Dec 07 '15 at 10:03

This works in pandas 0.16.2

In[23]: print df
        name          day   no
0      Jack       Monday    10
1      Jack      Tuesday    20
2      Jack      Tuesday    10
3      Jack    Wednesday    50
4      Jill       Monday    40
5      Jill    Wednesday   110
In[24]: df['no_cumulative'] = df.groupby(['name'])['no'].apply(lambda x: x.cumsum())
In[25]: print df
        name          day   no  no_cumulative
0      Jack       Monday    10             10
1      Jack      Tuesday    20             30
2      Jack      Tuesday    10             40
3      Jack    Wednesday    50             90
4      Jill       Monday    40             40
5      Jill    Wednesday   110            150

sushmit · Answer 4 · 2018-05-30T18:22:13.630

13

you should use

df['cum_no'] = df.no.cumsum()

http://pandas.pydata.org/pandas-docs/version/0.19.2/generated/pandas.DataFrame.cumsum.html

Another way of doing it

import pandas as pd
df = pd.DataFrame({'C1' : ['a','a','a','b','b'],
           'C2' : [1,2,3,4,5]})
df['cumsum'] = df.groupby(by=['C1'])['C2'].transform(lambda x: x.cumsum())
df

edited May 30 '18 at 18:22

answered Apr 26 '17 at 04:33

sushmit

4,369
2
35
38

score 7 · Answer 5 · answered Jul 19 '17 at 10:40

Instead of df.groupby(by=['name','day']).sum().groupby(level=[0]).cumsum() (see above) you could also do a df.set_index(['name', 'day']).groupby(level=0, as_index=False).cumsum()

df.groupby(by=['name','day']).sum() is actually just moving both columns to a MultiIndex
as_index=False means you do not need to call reset_index afterwards

score 2 · Answer 6 · answered Nov 04 '20 at 10:56

data.csv:

name,day,no
Jack,Monday,10
Jack,Tuesday,20
Jack,Tuesday,10
Jack,Wednesday,50
Jill,Monday,40
Jill,Wednesday,110

Code:

import numpy as np
import pandas as pd

df = pd.read_csv('data.csv')
print(df)
df = df.groupby(['name', 'day'])['no'].sum().reset_index()
print(df)
df['cumsum'] = df.groupby(['name'])['no'].apply(lambda x: x.cumsum())
print(df)

Output:

   name        day   no
0  Jack     Monday   10
1  Jack    Tuesday   20
2  Jack    Tuesday   10
3  Jack  Wednesday   50
4  Jill     Monday   40
5  Jill  Wednesday  110
   name        day   no
0  Jack     Monday   10
1  Jack    Tuesday   30
2  Jack  Wednesday   50
3  Jill     Monday   40
4  Jill  Wednesday  110
   name        day   no  cumsum
0  Jack     Monday   10      10
1  Jack    Tuesday   30      40
2  Jack  Wednesday   50      90
3  Jill     Monday   40      40
4  Jill  Wednesday  110     150

score 1 · Answer 7 · edited Sep 15 '22 at 10:16

as of version 1.0 pandas got a new api for window functions.

specifically, what was achieved earlier with

df.groupby(['name'])['no'].apply(lambda x: x.cumsum())

or

df.set_index(['name', 'day']).groupby(level=0, as_index=False).cumsum()

now becomes

df.groupby(['name'])['no'].expanding().sum()

I find it more intuitive for all window-related functions than groupby+level operations

although learning to use groupby is useful for general purpose.
see docs: https://pandas.pydata.org/docs/user_guide/window.html

score 0 · Answer 8 · answered Sep 15 '22 at 10:25

If you want to write a one-liner (perhaps you want to pass the methods into a pipeline), you can do so by first setting as_index parameter of groupby method to False to return a dataframe from the aggregation step and use assign() to assign a new column to it (the cumulative sum for each person).

These chained methods return a new dataframe, so you'll need to assign it to a variable (e.g. agg_df) to be able to use it later on.

agg_df = (
    # aggregate df by name and day
    df.groupby(['name','day'], as_index=False)['no'].sum()
    .assign(
        # assign the cumulative sum of each name as a new column
        cumulative_sum=lambda x: x.groupby('name')['no'].cumsum()
    )
)

Pandas groupby cumulative sum

8 Answers8

Linked

Related