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

I want to take cumulative sum of a column based on another column and the code below successfully does it. But additionally I need to exclude the current element.

library(data.table)

cat_var <- c("rock", "indie", "rock", "rock", "pop", "indie", "rock")
cat_var_2 <- c("blue", "green", "red", "red", "blue", "green", "blue")
target_var <- c(0, 0, 1, 1, 1, 1, 0)
df <- data.table("categorical_variable" = cat_var, "categorical_variable_2" = cat_var_2, "target_variable" =  target_var)

ave(df[,"target_variable"], df[,c("categorical_variable")], FUN=cumsum)

For now I can take cumulative sum of the target_variable based on categorical_variable. I want to take cumulative sums of both categorical_variable and categorical_variable_2 in one piece of code that excludes the current value. Like this:

ave(df[,"target_variable"], df[,c("categorical_variable", "categorical_variable_2")], FUN=cumsum)

Expected output is:

categorical_variable_transformed <- c(0, 0, 0, 1, 0, 0, 2)
categorical_variable_2_transformed <- c(0, 0, 0, 1, 0, 0, 1)
df$categorical_variable_transformed <- categorical_variable_transformed
df$categorical_variable_2_transformed <- categorical_variable_2_transformed

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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