CODEWITHSUNDEEP
rdplyrcumsum
Omry Atia
Omry Atia

Reputation: 2443

dplyr conditional summation

I have the following data frame:

set.seed(42)
df <- data_frame(x = sample(0:100, 50, replace = T), 
                 y = sample(c(T, F), 50, replace = T))

I would like to create a third column z that will be the sum of column x, but only if there are more than 3 trues in a row in column y. Is there a vectorized way to do it with dplyr? I don't even know how to approach this.

Upvotes: 1

Views: 2513

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269566

The question did not specify what values to use if there are not 3 TRUE values so we will use 0.

library(dplyr)
library(zoo)

sum3 <- function(z) all(z[, "y"]) * sum(z[, "x"])
df %>% mutate(sum = rollapplyr(df, 3, sum3, by.column = FALSE, fill = 0))

giving:

 # A tibble: 50 x 3
       x y       sum
   <int> <lgl> <int>
 1    92 TRUE      0
 2    94 TRUE      0
 3    28 TRUE    214
 4    83 FALSE     0
 5    64 TRUE      0
 6    52 FALSE     0
 7    74 FALSE     0
 8    13 TRUE      0
 9    66 TRUE      0
10    71 FALSE     0
# ... with 40 more rows

Upvotes: 1

akrun
akrun

Reputation: 887108

We create a grouping variable with rleid from data.table and get the sum of 'x' if there are more than 3 elements (n() >3) and if all the elements in 'y' are TRUE or else return NA

library(dplyr)
library(data.table)
df %>% 
  group_by(grp = rleid(y)) %>% 
  mutate(Sum = if(n() > 3 & all(y)) sum(x) else NA_integer_) %>%
  ungroup %>%
  select(-grp)

It can be also done with data.table

library(data.table)
setDT(df)[,  Sum := sum(x) * NA^(!((.N > 3) & all(y))), .(grp = rleid(y))]

Upvotes: 1

Related Questions