R: remove all rows that are missing data based on columns

Question

I have the following sample dataframe in R:

Test <- data.frame("Individual"=c("John", "John", "Alice", "Alice", "Alice", "Eve", "Eve","Eve","Jack"), "ExamNumber"=c("Test1", "Test2", "Test1", "Test2", "Test3", "Test1", "Test2", "Test3",  "Test3"))

Which Gives:

  Individual ExamNumber
1       John      Test1
2       John      Test2
3      Alice      Test1
4      Alice      Test2
5      Alice      Test3
6        Eve      Test1
7        Eve      Test2
8        Eve      Test3
9       Jack      Test3

However I want to remove any Individual who does not have all three test to result in:

  Individual ExamNumber
1      Alice      Test1
2      Alice      Test2
3      Alice      Test3
4        Eve      Test1
5        Eve      Test2
6        Eve      Test3

JasonWang · Accepted Answer · 2017-03-08 23:19:03Z

3

Here is another way using dplyr to check whether all three tests exist within groups:

library(dplyr)
Test %>% 
  group_by(Individual) %>%
  filter(all(c("Test1", "Test2", "Test3") %in% ExamNumber)) %>%
  ungroup()

# A tibble: 6 × 2
  Individual ExamNumber
      <fctr>     <fctr>
1      Alice      Test1
2      Alice      Test2
3      Alice      Test3
4        Eve      Test1
5        Eve      Test2
6        Eve      Test3

answered Mar 8, 2017 at 23:19

JasonWang

2,43413 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

d.b · Accepted Answer · 2017-03-08 23:10:15Z

2

You can use ave to group by Individual and check if the count for each group is 3 using NROW

Test[ave(1:nrow(Test), Test$Individual, FUN = NROW)==3,]
#  Individual ExamNumber
#3      Alice      Test1
#4      Alice      Test2
#5      Alice      Test3
#6        Eve      Test1
#7        Eve      Test2
#8        Eve      Test3

And here is a slightly more robust approach using same idea but with split

Test[order(Test$Individual),][unlist(lapply(split(Test, Test$Individual), function(a)
          rep(all(unique(Test$ExamNumber) %in% a$ExamNumber), NROW(a)))),]

edited Mar 8, 2017 at 23:10

answered Mar 8, 2017 at 22:47

d.b

32.6k6 gold badges46 silver badges90 bronze badges

Comments

Sathish · Accepted Answer · 2017-03-09 00:17:16Z

2

Using base R

ind_eq3 <- names( which( with( Test, by( Test, 
                                         INDICES = list(Individual), 
                                         FUN = function(x) length(unique(x$ExamNumber)) == 3) ) ) )
with(Test, Test[ Individual %in% ind_eq3, ] )

#   Individual ExamNumber
# 3      Alice      Test1
# 4      Alice      Test2
# 5      Alice      Test3
# 6        Eve      Test1
# 7        Eve      Test2
# 8        Eve      Test3

Using data.table

library('data.table')
setDT(Test)[ , 
             j  = .SD[length( unique(ExamNumber) ) == 3, ],
             by = 'Individual']

edited Mar 9, 2017 at 0:17

answered Mar 9, 2017 at 0:11

Sathish

12.7k3 gold badges46 silver badges60 bronze badges

Collectives™ on Stack Overflow

R: remove all rows that are missing data based on columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related