How to exclude rows based on combination of values from a column in R?

user_13473249 0
Viewed 654

In my dataframe I have over 260K rows. Each individual (ID) has 4 rows, one for every year (2013 - 2016). I've a problem with a column. Each ID is only allowed to have a "0" for 2013 (I already exclude ID's who had a "1" in 2013). In addition, they are only allowed to have a "0" for 2014, 2015 and 2016 (so 3 times a "0") OR have a "1" for 2014, 2015 and 2016 (so 3 times a "1"). If they have a mix of "0" and "1" for 2014, 2015 and 2016, I want to exclude the ID completely. 

structure(list(Year = c(2013, 2014, 2015, 2016, 2013, 2014, 2015, 
2016, 2013, 2014, 2015, 2016, 2013, 2014, 2015, 2016), Value = c(0, 
0, 0, 0, 0, 1, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1), ID = c(1, 1, 1, 
1, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4), Gender = c(0, 0, 0, 0, 
0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1)), row.names = c(NA, -16L), class = c("tbl_df", 
"tbl", "data.frame")) 

tibble [16 x 4] (S3: tbl_df/tbl/data.frame)
 $ Year  : num [1:16] 2013 2014 2015 2016 2013 ...
 $ Value : num [1:16] 0 0 0 0 0 1 1 1 0 1 ...
 $ ID    : num [1:16] 1 1 1 1 2 2 2 2 3 3 ...
 $ Gender: num [1:16] 0 0 0 0 0 0 0 0 1 1 ...

I've already tried some things with filter/subset and |/&; but I couldn't figure it out. There are more columns in my dataframe then showed above. Hopefully someone can help me. Thank you!

dplyr tidyverse dataframe r
3 Answers

You can remove the groups where both 0 and 1 are present in Value where year is not 2013.

library(dplyr)
df %>% group_by(ID) %>% filter(!all(c(0, 1) %in% Value[Year != 2013]))

#   Year Value    ID Gender
#   <dbl> <dbl> <dbl>  <dbl>
#1  2013     0     1      0
#2  2014     0     1      0
#3  2015     0     1      0
#4  2016     0     1      0
#5  2013     0     2      0
#6  2014     1     2      0
#7  2015     1     2      0
#8  2016     1     2      0
user_39629140

Does this work?

dat %>%
  group_by(ID) %>%
  filter(all(Year == 2013 | Value == 0) | all(Year == 2013 | Value == 1)) %>%
  ungroup()
# # A tibble: 8 x 4
#    Year Value    ID Gender
#   <dbl> <dbl> <dbl>  <dbl>
# 1  2013     0     1      0
# 2  2014     0     1      0
# 3  2015     0     1      0
# 4  2016     0     1      0
# 5  2013     0     2      0
# 6  2014     1     2      0
# 7  2015     1     2      0
# 8  2016     1     2      0
user_33582720

We can use data.table

library(data.table)
setDT(df)[, .SD[!all(c(0, 1) %in% Value[Year != 2013]], ID]
user_37322710
Related