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