Filter two consecutive values per group while depend on specific value in other column values

Mahlet Tadesse 0
Viewed 18

I have data and need to filter two consecutive no values for category per group id, while on type == x. NA between the two no values is fine.

dt <- data.frame(id=c(1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,5,5,5,5,6,6,6,6,6),
                   type=c('x','x','x', 'x', 'y','y','x','x','x', 'y', 'y','x','x','x','x','x','x','x','x', 'w', 'w', 'x','x','x', 'w','x','x','x', 'y', 'y'),
category=c(NA,'no',NA,'no',NA,'yes',NA,NA,'no',NA,'no','no',NA,'no','yes','no','no',NA,'no',NA,'yes','no',NA,NA,'no',NA,'no','no','no','yes'))

Expected output:

   id type category
1   1    x     <NA>
2   1    x       no
3   1    x     <NA>
4   1    x       no
5   1    y     <NA>
6   1    y      yes
7   3    x       no
8   3    x     <NA>
9   3    x       no
10  3    x      yes
11  3    x       no
12  4    x       no
13  4    x     <NA>
14  4    x       no
15  4    w     <NA>
16  4    w      yes
17  6    x     <NA>
18  6    x       no
19  6    x       no
20  6    y       no
21  6    y      yes

I tried it using

library(dplyr)
dt1 <- dt %>% 
  group_by(id) %>%  
  filter(any(category[!is.na(category)] == 'no' & 
               lag(category[!is.na(category)]) == 'no'&type=='x'))
dplyr r
1 Answers

You were close; put type == "x" inside the brackets as well.

dt %>% 
  group_by(id) %>% 
  filter(any(category[!(is.na(category)) & type == "x"] == "no" &
           lag(category[!(is.na(category)) & type == "x"] == "no")))

      id type  category
 1     1 x     NA      
 2     1 x     no      
 3     1 x     NA      
 4     1 x     no      
 5     1 y     NA      
 6     1 y     yes     
 7     3 x     no      
 8     3 x     NA      
 9     3 x     no      
10     3 x     yes     
11     3 x     no      
12     4 x     no      
13     4 x     NA      
14     4 x     no      
15     4 w     NA      
16     4 w     yes     
17     6 x     NA      
18     6 x     no      
19     6 x     no      
20     6 y     no      
21     6 y     yes
Maël0
Related