Reverse a Cumulative Column

Question

I want to reverse of this - Calculating YTD totals in Pandas

ID  month   year    Fee
68119   February    2015    25
68119   March   2015    25
68119   April   2015    25
68119   May 2015    25
00200   February    2015    50
00200   March   2015    375
00200   April   2015    375
00200   May 2015    375
00200   June    2015    375
00200   July    2015    375
00200   August  2015    375

The Fee column is the YTD numbers. I want the MTD numbers. Expected output -

ID  month   year    Fee
68119   February    2015    25
68119   March   2015    0
68119   April   2015    0
68119   May 2015    0
00200   February    2015    50
00200   March   2015    325
00200   April   2015    0
00200   May 2015    0
00200   June    2015    0
00200   July    2015    0
00200   August  2015    0

The logic for the YTD -

df.groupby('ID')['Fee'].cumsum()

Now for MTD, I figure if there isn't a pandas way of doing it, then I have to use a for loop on the ID, sort it by [year, month] and then do the subtraction. I have tried, but I know there is a more native pandas way of doing it. Thanks in advance.

I think folks are expecting to see some sort of sample code of what you have already tried and does not work. — , Apr 10 '18 at 13:04
Yes. I know a `groupby('ID').cumsum()` will give me the YTD. I can't figure out the logic for the reverse — Vivek Kalyanarangan, Apr 10 '18 at 13:04
@PearlySpencer I just described my approach. Didn't want to flood the question with useless code that I know that can be vectorized in a `pandas` way and it's just that I can't figure it out :-( — Vivek Kalyanarangan, Apr 10 '18 at 13:11

score 3 · Accepted Answer · answered Apr 10 '18 at 13:18

Let's try this:

df['FeeMonthly'] = df.groupby(['ID','year'])['Fee'].diff().fillna(df.Fee)

Output:

       ID     month  year  Fee  FeeMonthly
0   68119  February  2015   25        25.0
1   68119     March  2015   25         0.0
2   68119     April  2015   25         0.0
3   68119       May  2015   25         0.0
4     200  February  2015   50        50.0
5     200     March  2015  375       325.0
6     200     April  2015  375         0.0
7     200       May  2015  375         0.0
8     200      June  2015  375         0.0
9     200      July  2015  375         0.0
10    200    August  2015  375         0.0

thanks! This works! – Vivek Kalyanarangan Apr 10 '18 at 13:21 — Vivek Kalyanarangan, Apr 10 '18 at 13:21

Reverse a Cumulative Column

1 Answers1