R: How to filter column as long as it contains combination of values?

Question

I have a df like this:

VisitID | Item |
1       | A    |
1       | B    |
1       | C    |
1       | D    |
2       | A    |
2       | D    |
2       | B    |
3       | B    |
3       | C    |
4       | D    |
4       | C    |

In R, how do I filter for VisitIDs as long as they contain Item A & B? Expected Outcome:

VisitID | Item |
1       | A    |
1       | B    |
1       | C    |
1       | D    |
2       | A    |
2       | D    |
2       | B    |

I tried df %>% group_by(VisitID) %>% filter(any(Item == 'A' & Item == 'B')) but it doesn't work..

df <- read_delim("ID | Item 
1  | A    
1  | B    
2  | A    
3  | B    
1  | C    
4  | C    
5  | B    
3  | A    
4  | A    
5  | D", delim = "|", trim_ws = TRUE)

Try `filter(any(Item == 'A') & any(Item == 'B'))` It's not possible for an Item to be both A and B at the same time — MrFlick, Jun 02 '20 at 05:57

score 3 · Accepted Answer · edited Jun 02 '20 at 06:03

3

Since you want both "A" and "B" you can use all

library(dplyr)
df %>% group_by(VisitID) %>% filter(all(c("A", "B") %in% Item))

#  VisitID Item 
#    <int> <chr>
#1       1 A    
#2       1 B    
#3       1 C    
#4       1 D    
#5       2 A    
#6       2 D    
#7       2 B

OR if you want to use any use them separately.

df %>% group_by(VisitID) %>% filter(any(Item == 'A') && any(Item == 'B'))

edited Jun 02 '20 at 06:03

MichaelChirico

33,841
14
113
198

answered Jun 02 '20 at 05:58

Ronak Shah

377,200
20
156
213

1

changed `&` to `&&`, but I'm not 100% sure that's right. please redact if I'm mistaken – MichaelChirico Jun 02 '20 at 06:03
1

That's correct since `any` would return a single `TRUE`/`FALSE` . Thanks @MichaelChirico – Ronak Shah Jun 02 '20 at 06:04
could someone explain on the use of && instead of &? – spidermarn Jun 02 '20 at 06:10
1

@spidermarn `&&` works for scalars (basically, `TRUE` or `FALSE` _only_) -- `&` works for vectors, i.e., sequences of `TRUE`/`FALSE`/`NA`. Because `&&` is a special case it works much faster when it's appropriate – MichaelChirico Jun 02 '20 at 06:17
2

You can also read https://stackoverflow.com/questions/6558921/boolean-operators-and – Ronak Shah Jun 02 '20 at 06:18

score 0 · Answer 2 · answered Jun 02 '20 at 20:01

0

An otion with data.table

library(data.table)
setDT(df)[, .SD[all(c("A", "B") %in% Item)], VisitID]

answered Jun 02 '20 at 20:01

akrun

874,273
37
540
662

R: How to filter column as long as it contains combination of values?

2 Answers2