Search code examples
rdataframedplyrgroup-bysubtraction

How to subtract values from prior row of another column from current row with an initial start value and by grouping another column?


I have a dataframe which looks like:

df <- data.frame(col1 = c(2,3,6,1,8,4,8,2,4,5,7,4,2,7),col2 = c(rep(1,4),rep(2,3),rep(3,4),rep(4,3)))

Now I want a column rem_val which starts with a starting value 40 grouped by column col2, and subtracts the previous row from col1 from this value.

So the dataframe should look like:

I thought of reverse cumulative frequency but I want to have a starting value which is defined by the user, like 40 here.


Solution

  • library(dplyr)
    df %>%
      group_by(col2) %>%
      mutate(rem_val = 40 - cumsum(lag(col1, default = 0))) %>% 
      ungroup()