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

Time:09-12

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'))

CodePudding user response:

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     
  • Related