How to join a data.table with multiple columns and multiple values

Question

An example case is here:

DT = data.table(x=1:4, y=6:9, z=3:6)
setkey(DT, x, y)

Join columns have multiple values:

xc = c(1, 2, 4)
yc = c(6, 9)
DT[J(xc, yc), nomatch=0]
   x y z
1: 1 6 3

This use of J() returns only single row. Actually, I want to join as %in% operator.

DT[x %in% xc & y %in% yc]
   x y z
1: 1 6 3
2: 4 9 6

But using %in% operator makes the search a vector scan which is very slow compared to binary search. In order to have binary search, I build every possible combination of join values:

xc2 = rep(xc, length(yc))
yc2 = unlist(lapply(yc, rep, length(xc)))
DT[J(xc2, yc2), nomatch=0]
   x y z
1: 1 6 3
2: 4 9 6

But building xc2, yc2 in this way makes code difficult to read. Is there a better way to have the speed of binary search and the simplicity of %in% operator in this case?

I think you're looking for cross join - which is the function CJ. Try DT[CJ(xc,yc), nomatch=0L]. Feel free to post this as the answer (if correct) and accept it. — Arun
– Arun, Commented Sep 1, 2014 at 15:46

jangorecki · Accepted Answer · 2014-12-12 19:22:54Z

1

Answering to remove this question from DT tag open questions.
Code from Arun's comment DT[CJ(xc,yc), nomatch=0L] will do the job.

answered Dec 12, 2014 at 19:22

jangorecki

16.8k5 gold badges86 silver badges169 bronze badges

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

Collectives™ on Stack Overflow

How to join a data.table with multiple columns and multiple values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related