Using dplyr to mutate the following rows after meeting condition

user_13393473 0
Viewed 70

I am trying to add a new column, with character strings based on another column, via an ifelse statement in dplyr. When the condition is met, I also want the following two rows to also show the same value.

I show an example from the mtcars dataset

mtcars %>%
  mutate(type=ifelse(mpg>20,"Event", "No event")) %>%
  mutate(type=ifelse(type=="Event", lead(type),`type`))

What I am trying to do here is produce a new column called type, which if the mpg>20, I want the row to state "event" and if not "no event". However, I also want the two rows following the mpg>20 also to show "Event", even if they don't meet the criteria.

Hope this makes sense

dplyr r
2 Answers

I am not sure I understand the problem correctly. However you can try to modify the logical expression inside if_else:

mtcars %>%
  mutate(type = if_else(mpg > 20 | lag(mpg) > 20 | lag(mpg, n = 2) > 20, "Event", "No event"))

    mpg     type
1  21.0    Event
2  21.0    Event
3  22.8    Event
4  21.4    Event
5  18.7    Event
6  18.1    Event
7  14.3 No event
8  24.4    Event
9  22.8    Event
10 19.2    Event
11 17.8    Event
12 16.4 No event
13 17.3 No event
14 15.2 No event
15 10.4 No event
16 10.4 No event
17 14.7 No event
18 32.4    Event
user_54368770

For a general solution, you can use zoos rolling function. You can adjust the window size based on how much you want to look back.

library(dplyr)
library(zoo)

mtcars %>% mutate(type = rollapplyr(mpg > 20, 3, any, partial = TRUE))


#    mpg cyl  disp  hp drat    wt  qsec vs am gear carb  type
#1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4  TRUE
#2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4  TRUE
#3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1  TRUE
#4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1  TRUE
#5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2  TRUE
#6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1  TRUE
#7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4 FALSE
#8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2  TRUE
#9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2  TRUE
#10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4  TRUE
#11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4  TRUE
#12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3 FALSE
#13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3 FALSE
#...
#...

You can then change it to "Event", "No Event" using ifelse :

mtcars %>%
  mutate(type = ifelse(rollapplyr(mpg > 20, 3, any, partial = TRUE), 
                      'Event', 'No event'))

Or without ifelse :

mtcars %>%
  mutate(type = c('No event', 'Event')
                  [rollapplyr(mpg > 20, 3, any, partial = TRUE) + 1])
user_39629140
Related