Dynamically build call for lookup multiple columns
data.table, dynamic, lookup, r
Solution
In recent development version it has been made much easier
ID[JN, (select) := .list_of_fields,
env=list(.list_of_fields=as.list(paste0('i.', select)))]
OLD solution before 1.14.1
Instead of `mget` or `eval-parse` there is still possibility to build the lookup call. While the `mget` is the most user friendly, this one is both flexible and actually corresponds to building the `j` expression. Solution wrapped into `batch.lookup` helper function taking character vector of column names to lookup.
library(data.table)
set.seed(1)
ID <- data.table(id = 1:3, meta = rep(1,3), key = "id")
JN <- data.table(idd = sample(ID$id, 3, FALSE), value = sample(letters, 3, FALSE), meta = rep(1,3), key = "idd")
select <- c("value","meta") # my fields to lookup
batch.lookup = function(x) {
as.call(list(
as.name(":="),
x,
as.call(c(
list(as.name("list")),
sapply(x, function(x) as.name(paste0("i.",x)), simplify=FALSE)
))
))
}
batch.lookup(select)
#`:=`(c("value", "meta"), list(value = i.value, meta = i.meta))
ID[JN, eval(batch.lookup(select))][]
# id meta value
#1: 1 1 x
#2: 2 1 v
#3: 3 1 f
To be fair this answer actually address call construction issue described by me as OP.
Problem
How can I dynamically lookup multiple fields and add by reference using character vector variable as argument. In below case I want to lookup two columns and get rid of `i.` prefix in them. Of course they can override already existing columns with the same name. ``` library(data.table) set.seed(1) ID <- data.table(id = 1:3, meta = rep(1,3), key = "id") JN <- data.table(idd = sample(ID$id, 3, FALSE), value = sample(letters, 3, FALSE), meta = rep(1,3), key = "idd") select <- c("value","meta") # my fields to lookup j.lkp <- call(":=", select, lapply(paste0("i.",select), as.symbol)) j.lkp # `:=`(c("value", "meta"), list(i.value, i.meta)) ID[JN, eval(j.lkp)] # Error in eval(expr, envir, enclos) : could not find function "i.value" ID[JN, `:=`(c("value", "meta"), list(i.value, i.meta))] # id meta value # 1: 1 1 x # 2: 2 1 v # 3: 3 1 f ``` I'm aware of similar question but this one asks for vectorized argument during join and directly building call for `j`. edit: I'm aware I can do it using `.SDcols` but then I cannot perform this by reference