using %in% to subset a data.table

user_5089467 0
Viewed 598

I have a data.table

library(data.table)
DT <- data.table(a=c(1,2,3,4), b=c(4,4,4,4), x=c(1,3,5,5))
> DT
   a b x
1: 1 4 1
2: 2 4 3
3: 3 4 5
4: 4 4 5

and I would like to select rows where x equals either a or b. Obviously, I could use

> DT[x==a | x==b]
   a b x
1: 1 4 1

which gives the correct result. However, with many columns I thought, the follwoing should work just as well

> DT[x%in%c(a,b)]
   a b x
1: 1 4 1
2: 2 4 3

but it gives a different result that is not intuitive to me. Can anyone help?

data.table subset r
3 Answers

The expression

 DT[x==a | x==b]

returns all rows in DT where the values in x and a are equal or x and b are equal. This is the desired result.

On the other hand

 DT[x%in%c(a,b)]

returns all rows where x matches any value in c(a, b), not just the corresponding value. Thus your second row appears because x == 3 and 3 appears (somewhere) in a.

user_16649780

We can use Reduce with .SDcols for multiple columns. Specify the columns of interest in .SDcols, then loop over the .SD (Subset of Data.table), do the comparison (==) with 'x', and Reduce it to a single logical vector with |

DT[DT[, Reduce(`|`, lapply(.SD, `==`, x)), .SDcols = a:b]]
#   a b x
#1: 1 4 1
user_37322710

Another way is use rowSums

DT[rowSums(DT[,.SD,.SDcols=-'x']==x)>0,]
#   a b x
#1: 1 4 1

You can change to rowMeans...==1 if you want to select rows where all columns equal x

user_75841380
Related