Cumulative sum in r based on another column excluding the current value for more than one column

Solution 1:

Try this:

library(data.table)
nms <- c("categorical_variable", "categorical_variable_2")
df[, paste0(nms, "_transformed") :=
       lapply(nms, \(g) ave(target_variable, get(g), FUN = cumsum) - target_variable)]
df
#    categorical_variable categorical_variable_2 target_variable categorical_variable_transformed categorical_variable_2_transformed
#                  <char>                 <char>           <num>                            <num>                              <num>
# 1:                 rock                   blue               0                                0                                  0
# 2:                indie                  green               0                                0                                  0
# 3:                 rock                    red               1                                0                                  0
# 4:                 rock                    red               1                                1                                  1
# 5:                  pop                   blue               1                                0                                  0
# 6:                indie                  green               1                                0                                  0
# 7:                 rock                   blue               0                                2                                  1

Solution 2:

We may use data.table methods as it is a data.table

nm1 <- grep("categorical", names(df), value = TRUE)
nm2 <- paste0(nm1, "_transformed")
  
for(i in seq_along(nm1)) 
   df[, (nm2)[i] := cumsum(target_variable) - target_variable, by = c(nm1[i])]

-output

> df
   categorical_variable categorical_variable_2 target_variable categorical_variable_transformed categorical_variable_2_transformed
1:                 rock                   blue               0                                0                                  0
2:                indie                  green               0                                0                                  0
3:                 rock                    red               1                                0                                  0
4:                 rock                    red               1                                1                                  1
5:                  pop                   blue               1                                0                                  0
6:                indie                  green               1                                0                                  0
7:                 rock                   blue               0                                2                                  1

Solution 3:

With .SD the problem seems easy to solve:

df[, target_variable := lapply(.SD, \(x) if(length(x) > 1L) sapply(seq_along(x), \(i) cumsum(x[-i])) else x),
   by = c("categorical_variable", "categorical_variable_2")]

df
#   categorical_variable categorical_variable_2 target_variable
#1:                 rock                   blue               0
#2:                indie                  green               0
#3:                 rock                    red               1
#4:                 rock                    red               1
#5:                  pop                   blue               1
#6:                indie                  green               1
#7:                 rock                   blue               0