Conditional cumulative sum with dplyr

问题:

I'm trying to compute a conditional cumulative sum using dplyr but running into trouble. I have a dataframe and want to cumsum by group as long as a condition is true. See the following example:

df <- data.frame(prod = c("A", "A", "A", "A", "B", "B", "B", "B", "B"),
                 act = c(TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE),
                 sales = c(100,120, 190, 50, 30, 40, 50, 10, 30))
prod   act sales
1    A  TRUE   100
2    A  TRUE   120
3    A  TRUE   190
4    A FALSE    50
5    B  TRUE    30
6    B  TRUE    40
7    B FALSE    50
8    B FALSE    10
9    B FALSE    30

goes to:

prod   act sales cum_sales
1    A  TRUE   100       100
2    A  TRUE   120       220
3    A  TRUE   190       410
4    A FALSE    50       410
5    B  TRUE    30        30
6    B  TRUE    40        70
7    B FALSE    50        70
8    B FALSE    10        70
9    B FALSE    30        70

I was thinking along the lines of the following but it's not working, anyone have ideas?

dfb <- df %>% group_by(prod) %>%
 mutate(cum_sales = ifelse(act == TRUE, cumsum(sales), lag(sales))) 

Thanks!

回答1:

Since converting a logical to numeric gives 0 for FALSE and 1 for TRUE, you can simply multiply sales by act :

library(dplyr)
df %>% group_by(prod) %>%
  mutate(cum_sales = cumsum(sales*act))

    prod   act sales cum_sales
  <fctr> <lgl> <dbl>     <dbl>
1      A  TRUE   100       100
2      A  TRUE   120       220
3      A  TRUE   190       410
4      A FALSE    50       410
5      B  TRUE    30        30
6      B  TRUE    40        70
7      B FALSE    50        70
8      B FALSE    10        70
9      B FALSE    30        70

回答2:

Here are some other options in base R

df$cum_sales <- with(df, ave(sales*act, prod, FUN = cumsum))

and data.table

library(data.table)
setDT(df)[, cum_sales := sales*act, by = prod]