Python cumulative sum per group with pandas   

Python and pandas offers great functions for programmers and data science. Once of this functions is cumsum which can be used with pandas groups in order to find the cumulative sum in a group.

Previous article about pandas and groups: Python and Pandas group by and sum

Video tutorial on the article: Python/Pandas cumulative sum per group

Let's see a simple example of pandas.cumsum() and what is the main idea behind this function explained for beginners:

import pandas as pd
import numpy as np

# define a simple pandas series
s = pd.Series([2, np.nan, 5, -1, 0])


print(s)
# cumulative sum of the previous series
print(s.cumsum())

# cumulative sum of first ten numbers
x = range(0, 10)

s = pd.Series(x).cumsum()
print(s)

result:

0    2.0
1    NaN
2    5.0
3   -1.0
4    0.0
dtype: float64
0    2.0
1    NaN
2    7.0
3    6.0
4    6.0
dtype: float64
0     0
1     1
2     3
3     6
4    10
5    15
6    21
7    28
8    36
9    45

So the cumulative sum is calculating the sum for each member up to the current one. This is extremely useful for data science and many business areas. The first result is the output of our number series. The second one we calculate the cumulative sum for this series - as you can see np.NaN ( similar to None but optimized for pandas needs) doesn't break the sum. You can change this behavior by:

s.cumsum(skipna=False)

which will result in:

0    2.0
1    NaN
2    NaN
3    NaN
4    NaN
dtype: float64

Now lets check a more complicated examples including grouping on pandas dataframes on one and more columns. Having this data:

• table 1

table 2

If we want to calculate the cumulative sum of Promotions(Promo) per Company and for every date we can do:

import pandas as pd
df = pd.DataFrame({'Date': ['08/09/2018', '10/09/2018', '09/09/2018', '04/09/2018', '08/09/2018', '10/09/2018',
                            '09/09/2018', '04/09/2018'],
                   'Company': ['Samsung', 'Samsung', 'Samsung', 'Samsung', 'Sony', 'Sony', 'Sony', 'Sony'],
                   'Promotions': [54, 56, 54, 4, 3, 34, 32, 15]})

print(df)
print (df.groupby(by=['Company','Date']).sum().groupby(level=[0]).cumsum())

result:

depending on your needs you can change the grouping or the levels. Lets check another example this time with grouping on 3 columns from the pandas dataframe:

import pandas as pd

df = pd.DataFrame({'Company': ['Samsung', 'Samsung', 'Samsung', 'Samsung', 'Samsung',
                               'LG', 'LG', 'LG', 'LG', 'LG', 'Sony', 'Sony', 'Sony',
                               'Sony', 'Sony'],
                   'Date': ['10/9/2018', '10/9/2018', '10/9/2018', '10/10/2018',
                            '10/10/2018', '10/10/2018', '10/9/2018', '10/9/2018',
                            '10/9/2018', '10/10/2018', '10/10/2018', '10/10/2018',
                            '10/10/2018', '10/10/2018', '10/10/2018'],
                   'Country': ['India', 'India', 'USA', 'France', 'India', 'India',
                               'Germany', 'USA', 'Brazil', 'Brazil', 'India', 'Germany',
                               'India', 'India', 'Brazil'],
                   'Sells': [15, 81, 29, 33, 21, 42, 67, 35, 2, 34, 21, 50, 10, 26, 53]})

print(df.groupby(['Country', 'Company', 'Date']).sum().groupby(level=[0]).cumsum())

you can find the result of this execution below:

as you can see the Date column is not sorted as a date but as a string. Because it's stored as a string.

If you want to convert string dates to normal dates with Python pandas then you can first convert your column from string to date and after that do the same operation:

import pandas as pd

df = pd.DataFrame({'Company': ['Samsung', 'Samsung', 'Samsung', 'Samsung', 'Samsung',
                               'LG', 'LG', 'LG', 'LG', 'LG', 'Sony', 'Sony', 'Sony',
                               'Sony', 'Sony'],
                   'Date': ['10/9/2018', '10/9/2018', '10/9/2018', '10/10/2018',
                            '10/10/2018', '10/10/2018', '10/9/2018', '10/9/2018',
                            '10/9/2018', '10/10/2018', '10/10/2018', '10/10/2018',
                            '10/10/2018', '10/10/2018', '10/10/2018'],
                   'Country': ['India', 'India', 'USA', 'France', 'India', 'India',
                               'Germany', 'USA', 'Brazil', 'Brazil', 'India', 'Germany',
                               'India', 'India', 'Brazil'],
                   'Sells': [15, 81, 29, 33, 21, 42, 67, 35, 2, 34, 21, 50, 10, 26, 53]})

df['Date'] = pd.to_datetime(df['Date'])

print(df.groupby(['Country', 'Company', 'Date']).sum().groupby(level=[0]).cumsum())

result: