"on=" doesn't retain keys correctly.

[arunsrinivasan]

Sample data taken from this SO post from Douglas Clark. Thanks for the nice example!

require(data.table)

# main data table DT, keyed on site and date, with data column x
DT <- data.table(site = rep(LETTERS[1:2], each=3),
                 date = rep(1:3, times=2),
                 x = rep(1:3*10, times=2),
                 key = "site,date")
DT
#    site date  x
#1:    A    1 10
#2:    A    2 20
#3:    A    3 30
#4:    B    1 10
#5:    B    2 20
#6:    B    3 30

# lookup table for x to y lookup, keyed on x
x2y <- data.table(x = c(10,20), y = c(100,200), key = "x")
x2y

To join on column x, we don't have to re-key anymore:

DT[x2y, on="x"]
#    site date  x   y
#1:    A    1 10 100
#2:    B    1 10 100
#3:    A    2 20 200
#4:    B    2 20 200

key(DT[x2y, on="x"])
# [1] "site" # <~~~ wrong

It should be x as i here is keyed on that column, and corresponding column in DT is also named x.

[jan-glx]

I would prefer if the result was indeed keyed on "site,date".

[arunsrinivasan]

The order of rows in joined result is determined by i. So no, that'd not be possible.

[arunsrinivasan]

Updated SO post linked above.