Python - Pandas - Unroll / Remove Cumulative Sum

Question

I have a data frame like the following (specific data below, this is generic). The no gives me a cumulative sum:

                 no
name day           
Jack Monday      10
     Tuesday     40
     Wednesday   90
Jill Monday      40
     Wednesday  150

I want to "unroll" the cumulative sum to give me something like this:

print df
   name        day   no
0  Jack     Monday   10
1  Jack    Tuesday   30
2  Jack  Wednesday   50
3  Jill     Monday   40
4  Jill  Wednesday  110

In essence, I'd like to do something like the following, but in reverse: Pandas groupby cumulative sum

I'm not sure what changed, but I cleaned up my file and reran your code and now it worked. I removed mention to any errors for clarity of the question. Thanks for your help. — Afflatus, Apr 06 '16 at 19:35

score 9 · Accepted Answer · edited Apr 23 '20 at 05:51

9

If I understand correctly you can do the following:

In [103]:
df.groupby(level=0).diff().fillna(df).reset_index()

Out[103]:
   name        day     no
0  Jack     Monday   10.0
1  Jack    Tuesday   30.0
2  Jack  Wednesday   50.0
3  Jill     Monday   40.0
4  Jill  Wednesday  110.0

So groupby the first index level and call diff to calculate the inter row differences per group and fill the NaN values with the original df values and call reset_index

edited Apr 23 '20 at 05:51

tommy.carstensen

8,962
15
65
108

answered Apr 06 '16 at 13:05

EdChum

376,765
198
813
562

@AmiTavory yes I thought I would get negative values, I'll remove it – EdChum Apr 06 '16 at 13:13
Please edit your question with sample data and code that demonstrates the problem and the desired output – EdChum Apr 06 '16 at 16:10

score 0 · Answer 2 · answered Jul 05 '20 at 21:03

0

Here's a method based on zip. It creates two series, the 2nd being offset by 1, and subtracts the difference between the two.

[n-nn for n,nn in zip(df['No'],df['No'][1:]+[0])]

answered Jul 05 '20 at 21:03

BSalita

8,420
10
51
68

Python - Pandas - Unroll / Remove Cumulative Sum

2 Answers2

Linked