is there a way in R to mutate a cumulative subtraction to update the same mutated var?

user_12097388 0
Viewed 50

This is straightforward but for whatever reason I'm having difficulty. Say I have:

data <- data.frame (n1  = c(14,9,7,2, 23,21,10,8),
                  n2 = c(65, 65, 65, 65, 78, 78, 78, 78),
n3=c(51, NA, NA, NA, 55, NA, NA, NA),
want=c(51, 37, 28, 21, 55, 32, 11, 1 ), 
group=c(1,1,1,1,2,2,2,2 ))

I have n1, n2 and n3, where I have the first n3 value (diff b/w n1 and n2). I want to make a 'want' variable where I subtract the lag rows above the next value. So for row 2 below, it should be 51-14=37, then 37-9=28, etc etc. by group.

  n1 n2 n3 want group
1 14 65 51   51     1
2  9 65 NA   37     1
3  7 65 NA   28     1
4  2 65 NA   21     1
5 23 78 55   55     2
6 21 78 NA   32     2
7 10 78 NA   11     2
8  8 78 NA    1     2

Any ideas? Thanks!

r
2 Answers

There's no cumulative subtraction, but you can do a cumulative sum and then subtract it.

library(dplyr)

data %>%
  group_by(group) %>%
  mutate(want = ifelse(!is.na(n3), n3, first(n3) - lag(cumsum(n1)))) %>%
  ungroup()
user_23328490

cumsum could be used in ave to get it per group.

with(data, n2 - ave(n1, group, FUN=\(x) cumsum(c(x[1], x[-length(x)]))))
#[1] 51 37 28 21 55 32 11  1
user_104885040
Related