Using groupby to calculate cum sum in pandas dataframe

user_12077980 0
Viewed 375

I have dataframe below which I intended to calculate the cumulative sum:

df_a = pd.DataFrame({'Location': ['SR01','SR01','SR02','SR01','SR01','SR02'],
                 'User':['101','101','101','102','102','102'],
                 'Year':['2018','2019','2019','2018','2019','2019'],
                 'Month':[12, 1, 2, 12, 1, 2],
                 'Qty':[10, -2, 3, 4, -5, 6]})

My intended output is like below:

df_a = pd.DataFrame({'Location': ['SR01','SR01','SR02','SR01','SR01','SR02'],
                 'User':['101','101','101','102','102','102'],
                 'Year':['2018','2019','2019','2018','2019','2019'],
                 'Month':[12, 1, 2, 12, 1, 2],
                 'Qty':[10, -2, 3, 4, -5, 6],
                'CumSum': [10, 8, 3, 4, -1, 6]})

However when I use df_a.groupby(['Location','User','Year','Month']).sum().groupby(level=1).cumsum(), i get this instead:

df_a = pd.DataFrame({'Location': ['SR01','SR01','SR02','SR01','SR01','SR02'],
                 'User':['101','101','101','102','102','102'],
                 'Year':['2018','2019','2019','2018','2019','2019'],
                 'Month':[12, 1, 2, 12, 1, 2],
                 'Qty':[10, 8, 4, -1, 11, 5]})

Can somebody explain why my code is not working and solve this issue?

pandas cumulative-sum python
1 Answers

You need 

df_a.groupby(['Location','User']).Qty.cumsum()
0    10
1     8
2     3
3     4
4    -1
5     6
Name: Qty, dtype: int64

df_a['cumSum']= df_a.groupby(['Location','User']).Qty.cumsum()
user_79645270
Related