[Solved] Look up from different dataframes depending on a column

Question

Here’s one way to add a new column by reference using data.table:

require(data.table)
setDT(d1); setDT(d2); setDT(data) # convert all data.frames to data.tables

data[src == "one", location := d1[.SD, location, on="index"]]
data[src == "two", location := d2[.SD, location, on="index"]]

.SD stands for subset of data, and contains all columns in data that matches the condition provided in i-argument.

See the vignettes for more.

You can use match in the expression to the right of := as well instead of extracting location using a join. But it’d not be extensible if you’d want to match on multiple columns.

Accepted Answer

Here’s one way to add a new column by reference using data.table:

require(data.table)
setDT(d1); setDT(d2); setDT(data) # convert all data.frames to data.tables

data[src == "one", location := d1[.SD, location, on="index"]]
data[src == "two", location := d2[.SD, location, on="index"]]

.SD stands for subset of data, and contains all columns in data that matches the condition provided in i-argument.

See the vignettes for more.

You can use match in the expression to the right of := as well instead of extracting location using a join. But it’d not be extensible if you’d want to match on multiple columns.