How to select multiple rows based on the other column?

Question

I have a data frame with many columns and rows with many ids, the following data only shows 2 ids.

id  treatment  
1   A          
1   B          
1   C          
1   D          
1   E          
2   A          
2   B         
2   C  

I want to get the subgroup of id with the five treatments (A,B,C,D,E). So the output table would look like this:

id  treatment
1   A          
1   B        
1   C           
1   D           
1   E   

Thank you very much.

Answer 1

To return a subset of ID groups that contain all treatments do something like the following:

install.packages("dplyr")
library(dplyr)

treatments <- c("A", "B", "C", "D", "E")

df %>% group_by(id) %>% filter(all(treatments %in% treatment))

#### OUTPUT ####

# A tibble: 5 x 2
# Groups:   id [1]
     id treatment
  <int> <fct>    
1     1 A        
2     1 B        
3     1 C        
4     1 D        
5     1 E        

The main advantage here is that it deals with repeated conditions correctly. That is to say, in the off chance that you have the identical condition within an ID group, e.g.:

# A tibble: 11 x 2
      id treatment
   <dbl> <chr>    
 1     1 A        
 2     1 A        
 3     1 B        
 4     1 C        
 5     1 D        
 6     1 E        
 7     2 A        
 8     2 A        
 9     2 B        
10     2 B        
11     2 C        

The above code will return all observations of any groups containing every condition:

# A tibble: 6 x 2
# Groups:   id [1]
     id treatment
  <dbl> <chr>    
1     1 A        
2     1 A        
3     1 B        
4     1 C        
5     1 D        
6     1 E        

Answer 2

Here's a super concise way to do it:

 head(my.data)
  id treatment
1  1         A
2  1         B
3  1         C
4  1         D
5  1         E
6  2         A

group_by(my.data, id) %>% filter(n_distinct(treatment) == 5)
# A tibble: 5 x 2
# Groups:   id [1]
     id treatment     
  <int> <chr>     
1     1 A            
2     1 B            
3     1 C            
4     1 D            
5     1 E   

Explanation

Internally, n_distinct(treatment) tallies up instances of the unique values in each id category, since you grouped by that column. Then filter retains the ids that occurred 5 times.

Answer 3

One option is the following, using dplyr.

EDIT

This is a more concise solution, and the original solution is below. This shorter solution also removes duplicate rows. The num.treatments is hard coded to 5 but can be set any way you need to.

library(dplyr)

# load your data
treatment.df <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L), treatment = c("A", "B", "C", "D", "E", "A", "B", "C")), class = "data.frame", row.names = c(NA, -8L))
num.treatments <- 5
unique(treatment.df[c("id", "treatment")]) %>%
  group_by(id) %>%
  filter(n() == num.treatments)

Original Solution

library(dplyr)

# load your data
treatment.df <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L), treatment = c("A", "B", "C", "D", "E", "A", "B", "C")), class = "data.frame", row.names = c(NA, -8L))
treatment.df
##   id treatment
## 1  1         A
## 2  1         B
## 3  1         C
## 4  1         D
## 5  1         E
## 6  2         A
## 7  2         B
## 8  2         C

## Get IDs with a complete set of treatments
ids.with.all.treatments <-
  treatment.df %>%
  group_by(id, treatment) %>%
  summarise() %>%          # Get unique set if id/treatment pairs in cases of duplicates.
  summarise(cnt = n()) %>% # Summarise by 'id' to the count per id.
  filter(cnt == 5)         # Get items with the expected number of treatments


treatment.df %>%
  filter(id %in% ids.with.all.treatments$id)
##   id treatment
## 1  1         A
## 2  1         B
## 3  1         C
## 4  1         D
## 5  1         E

Answer 4

Also a dplyr possibility:

df %>%
 group_by(id) %>%
 filter(grepl("A,B,C,D,E", paste(treatment, collapse = ","), fixed = TRUE))

     id treatment
  <int> <chr>    
1     1 A        
2     1 B        
3     1 C        
4     1 D        
5     1 E 

Here it takes into account also the ordering, meaning it filters out cases where treatment is not going A,B,C,D,E.

If the order is not important, then you can arrange the "treatment" column first:

df %>%
 group_by(id) %>%
 arrange(treatment, .by_group = TRUE) %>%
 filter(grepl("A,B,C,D,E", paste(treatment, collapse = ","), fixed = TRUE))

Considering a scenario shown by @gersht, it can be modified to:

df %>%
 group_by(id) %>%
 filter(grepl("A,B,C,D,E", paste(unique(treatment), collapse = ","), fixed = TRUE))

Or:

df %>%
 group_by(id) %>%
 arrange(treatment, .by_group = TRUE) %>%
 filter(grepl("A,B,C,D,E", paste(unique(treatment), collapse = ","), fixed = TRUE))

Answer 5

I thought it would be interesting to give a base R solution,

DF = data.frame(id = c(rep(1,5),rep(2,3)), treatment = c('A','C','B','D','E','A','B','C'))

Note that I permuted the order slightly in treatment to account for its possibility.

do.call(rbind,
  lapply(split(DF,DF$id),
    function(X){
      if(identical(sort(unique(X$treatment)),c('A','B','C','D','E'))) X
      else NULL}))

I allow for the possibility that an id can have multiples of the same treatment since it is unclear what the rest of the data.frame looks like and that the treatments for an id group might not be sorted.

Answer 6

Here's a way in base R using subset and ave (borrowing @steveb's data):

treatments <- LETTERS[1:5]
subset(treatment.df, as.logical(ave(treatment, id, FUN = function(x) all(treatments %in% x))))
#   id treatment
# 1  1         A
# 2  1         B
# 3  1         C
# 4  1         D
# 5  1         E