filter some rows of a data frame depending values of others rows

Question

I would like to filter some rows of a dataframe based on the values of other rows and I don't know how to proceed. Below is an example of what I want to do.

data=data.frame(ID=c("ID1",NA, NA, "ID2", NA, "ID3", NA, NA), 
                l2=c(NA,9,4,NA,5,NA,6,8), 
                var3=c("aa", NA, NA, "bc",NA, "cc", NA, NA), 
                var4=c(NA,"yes","no",NA,"yes",NA,"yes","no"))


> data
    ID l2 var3 var4
1  ID1 NA   aa <NA>
2 <NA>  9 <NA>  yes
3 <NA>  4 <NA>  no
4  ID2 NA   bc <NA>
5 <NA>  5 <NA>  yes
6  ID3 NA   cc <NA>
7 <NA>  6 <NA>  yes
8 <NA>  8 <NA>  no

On this dataframe I would like to select the rows using the ID variable if the rows following this ID (until the next one) have at least one value < 7 for the l2 variable AND a "yes" for the var4 variable. Following this rule I would expect the following output. This is just an example, I have much more rows and much more other variables. If someone have a solution with dplyr it would be perfect.

> outpout=data.frame(ID=c("ID2","ID3"), l2=c(NA,NA), var3=c( "bc","cc"), var4=c(NA,NA))

> outpout
   ID l2 var3 var4
1 ID2 NA   bc   NA
2 ID3 NA   cc   NA

Answer 1

You could do:

a) if it would be ok if the conditions for l2 and var4 could be satisfied in different rows:

library(tidyverse)
data %>%
  fill(ID) %>%
  group_by(ID) %>%
  filter((any(l2 < 7) & any(var4 == "yes")) & row_number() == 1) %>%
  ungroup()

# A tibble: 3 x 4
  ID       l2 var3  var4 
  <chr> <dbl> <chr> <chr>
1 ID1      NA aa    NA   
2 ID2      NA bc    NA   
3 ID3      NA cc    NA   

b) if the conditions for l2 and var4 have to be satisfied in the same row (could probably be simplified a bit, but I be a bit more verbose here for illustrative purposes):

data %>%
  fill(ID) %>%
  group_by(ID) %>%
  filter((l2 < 7 & var4 == "yes") | row_number() == 1) %>%
  filter(n() > 1 & row_number() == 1) %>%
  ungroup()

# A tibble: 2 x 4
  ID       l2 var3  var4 
  <chr> <dbl> <chr> <chr>
1 ID2      NA bc    NA   
2 ID3      NA cc    NA