0

I have a DATAFRAME which looks like this. I want to substract the cumulative sum of AMOUNT WITH TOTAL_AMOUNT for each ID seperately. so the result must be grouped by ID

ID      AMOUNT     TOTAL_AMOUNT   
110      1250         4000           
110       250         4000           
110       350         4000          
111       985         3500           
111       440         3500          
111        50         3500           
112      1080         4000         
112       380         4000

After the calculation, my output should look something like this

 ID      AMOUNT     TOTAL_AMOUNT   CALCULATION(new column)**(TOTAL_AMOUNT-CUMSUM(AMOUNT)**
110      1250         4000           2750
110       250         4000           2500
110       350         4000           2150
111       985         3500           2515
111       440         3500           2075
111        50         3500           2025
112      1080         4000           2920
112       380         4000           2540
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
rahul mishra
  • 11
  • 1
  • run `dput(yourdataframename)` in R then paste output here. Read [this](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – Daman deep Dec 21 '20 at 10:29
  • In base R, you can do : `df$CALCULATION <- with(df, TOTAL_AMOUNT - ave(AMOUNT, ID, FUN = cumsum))` – Ronak Shah Dec 21 '20 at 11:18

1 Answers1

1
library(dplyr)

df %>%
  group_by(ID) %>%
  mutate(cumsum = cumsum(AMOUNT),
         diff = TOTAL_AMOUNT-cumsum)
Pal R.K.
  • 118
  • 1
  • 4