Subsetting multiple rows from same column based on condition in additional column in data.table

user_4848029 0
Viewed 104

I am trying to get a subset of my data that includes two different values for a single column that match to a second column using data.table.

This feels like a fairly trivial task, yet I have been unable to find an example like this anywhere:

library(data.table)
dat <- data.table(id = c("100", "100", "101", "101", "101", "103", "105", "105"),
                  V1 = c("A", "B", "A", "B", "C", "B", "A", "B"),
                  V2 = c(NA, NA, 20, NA, 30, NA, 30, 30))

What I am trying to do is find all instances where both A and B are NA for a given id.

I can get the id's for which either is true easily:

dat[(V1 == "A" & is.na(V2)) | (V1 == "B" & is.na(V2)), ] # works as expected

# id V1 V2
# 1: 100  A NA
# 2: 100  B NA
# 3: 101  B NA
# 4: 103  B NA

dat[V1 %in% c("A", "B") & is.na(V2), ] # same as above

But if I try to combine the call with &, it doesn't work

dat[(V1 == "A" & is.na(V2)) & (V1 == "B" & is.na(V2)), ] # empty data table
dat[(V1 == "A" & is.na(V2)) && (V1 == "B" & is.na(V2)), ] # empty data table

I suppose the data.table is empty because there are no rows for which V1 is equal to A AND B, but nothing I have tried comes close.

This is what I am trying to get out:

# id V1 V2
# 1: 100  A NA
# 2: 100  B NA

I suppose I need to include the id information in some way here, but it is not clear to me how because adding by = doesn't work

data.table data-manipulation filtering r
3 Answers

You can select those groups that have both the 'A' and 'B' value and both of them are NA.

library(data.table)

dat[, .SD[all(c('A', 'B') %in% V1 & is.na(V2[match(c('A', 'B'), V1)]))], id]

#    id V1 V2
#1: 100  A NA
#2: 100  B NA
user_39629140

i recommend using @Ronak Shah's answer - i just add this because it was fun to play around with and may be easier to understand if you (like me) still work on your understanding of the data table syntax

dat <- data.frame(id = c("100", "100", "101", "101", "101", "103", "105", "105"),
                  V1 = c("A", "B", "A", "B", "C", "B", "A", "B"),
                  V2 = c(NA, NA, 20, NA, 30, NA, 30, 30))

dat <- dat[(dat$V1 == "A" & is.na(dat$V2)) | (dat$V1 == "B" & is.na(dat$V2)), ] 

#find all id's that exist more than once
non_unique<-as.data.frame(table(dat$id))
non_unique<-non_unique[non_unique$Freq>1,]

dat<-dat[dat$id %in% as.character(non_unique[,1]),]
dat

   id V1 V2
1 100  A NA
2 100  B NA
user_124402760

Maybe I don't get what you need, but have you tried this way?

dat[(V1 %in% c("A", "B") & is.na(V2)),]
user_142156260
Related