R data.table join with inequality conditions between left and right table

3
votes

I saw the question on join with inequality conditions R data.table join with inequality conditions and I ask myself, how would we proceed to join two data.tables with an inequality condition between these two?

This is usually of great interest in time series, where we want to join one table X with another Y but the join should choose the first row in X that matches in a time frame smaller than the time of the row in Y. 

dt <- data.table(rep(c("A", "B", "C"), 4), 5:16)
dt2 <- data.table(rep(c("A", "B", "C"), 4), 1:12)
cbind(dt,dt2)
    V1 V2 V1 V2
 1:  A  5  A  1
 2:  B  6  B  2
 3:  C  7  C  3
 4:  A  8  A  4
 5:  B  9  B  5
 6:  C 10  C  6
 7:  A 11  A  7
 8:  B 12  B  8
 9:  C 13  C  9
10:  A 14  A 10
11:  B 15  B 11
12:  C 16  C 12

Now, take the rows from dtthat match the rows from dt2 on the variable V1 but have dt$V2smaller than dt2$V2.

How would you do this?

1
rdata.table

1 Answers

6
votes

You could use the roll argument here. 

timeresolution = 0.01
dt[,V2 := V2 - timeresolution]

setkeyv(dt,c("V1","V2"))
setkeyv(dt2,c("V1","V2"))
dt2[dt,roll=-Inf]

> dt2[dt,roll=-Inf]
    V1 V2 V2.1
 1:  A  4    5
 2:  A  7    8
 3:  A 10   11
 4:  A 13   14
 5:  B  5    6
 6:  B  8    9
 7:  B 11   12
 8:  B 14   15
 9:  C  6    7
10:  C  9   10
11:  C 12   13
12:  C 15   16