CODEWITHSUNDEEP
r
holastello
holastello

Reputation: 623

Filter data frame matching all values of a vector

I want to filter data frame x by including IDs that contain rows for Hour that match all values of testVector.

ID <- c('A','A','A','A','A','B','B','B','B','C','C')
Hour <- c('0','2','5','6','9','0','2','5','6','0','2')

x <- data.frame(ID, Hour)
x
   ID Hour
1   A    0
2   A    2
3   A    5
4   A    6
5   A    9
6   B    0
7   B    2
8   B    5
9   B    6
10  C    0
11  C    2

testVector <- c('0','2','5')

The solution should yield the following data frame:

x
       ID Hour
    1   A    0
    2   A    2
    3   A    5
    4   A    6
    5   A    9
    6   B    0
    7   B    2
    8   B    5
    9   B    6

All values of ID C were dropped because it was missing Hour 5. Note that I want to keep all values of Hour for IDs that match testVector.

A dplyr solution would be ideal, but any solution is welcome.

Based on other related questions on SO, I'm guessing I need some combination of %in% and all, but I can't quite figure it out.

Upvotes: 9

Views: 9757

Answers (3)

akrun
akrun

Reputation: 886998

Here is an option using table from base R

i1 <- !rowSums(table(x)[, testVector]==0)
subset(x, ID %in% names(i1)[i1])
#   ID Hour
#1  A    0
#2  A    2
#3  A    5
#4  A    6
#5  A    9
#6  B    0
#7  B    2
#8  B    5
#9  B    6

Or this can be done with data.table

library(data.table)
setDT(x)[, .SD[all(testVector %in% Hour)], ID]
#    ID Hour
#1:  A    0
#2:  A    2
#3:  A    5
#4:  A    6
#5:  A    9
#6:  B    0
#7:  B    2
#8:  B    5
#9:  B    6

Upvotes: 2

Oriol Mirosa
Oriol Mirosa

Reputation: 2826

Here's another dplyr solution without ever leaving the pipe:

ID <- c('A','A','A','A','A','B','B','B','B','C','C')
Hour <- c('0','2','5','6','9','0','2','5','6','0','2')

x <- data.frame(ID, Hour)

testVector <- c('0','2','5')

x %>%
  group_by(ID) %>%
  mutate(contains = Hour %in% testVector) %>%
  summarise(all = sum(contains)) %>%
  filter(all > 2) %>%
  select(-all) %>%
  inner_join(x)

##       ID   Hour
##   <fctr> <fctr>
## 1      A      0
## 2      A      2
## 3      A      5
## 4      A      6
## 5      A      9
## 6      B      0
## 7      B      2
## 8      B      5
## 9      B      6

Upvotes: 2

Florian
Florian

Reputation: 25385

Your combination of %in% and all sounds promising, in base R you could use those to your advantage as follows:

to_keep = sapply(lapply(split(x,x$ID),function(x) {unique(x$Hour)}), 
                                              function(x) {all(testVector %in% x)})
x = x[x$ID %in% names(to_keep)[to_keep],]

Or similiarly, but skipping an unneccessary lapply and more efficient as per d.b. in the comments:

temp = sapply(split(x, x$ID), function(a) all(testVector %in% a$Hour))
x[temp[match(x$ID, names(temp))],]

Output:

  ID Hour
1  A    0
2  A    2
3  A    5
4  A    6
5  A    9
6  B    0
7  B    2
8  B    5
9  B    6

Hope this helps!

Upvotes: 5

Related Questions