Fast data.table assign of multiple columns by group from lookup

Question

I have searched for the canonical way to do what I'm trying but I seem to have little luck getting something working that is fast and elegant. In short, I have a large table with multiple value columns and want to multiply each by a corresponding factor from a lookup table. I cannot figure out how to dynamically pass in which columns I want multiplied by the lookup values, or how to refer to the lookup values in general outside of basic expressions.

Here is my example, I have it set up with 3 million rows with 10 value columns, this doesn't take too long and is somewhat representative of the data size (this will be implemented as part of a much larger loop, hence the emphasis on performance). There is also a lookup table with 6 levels and some assorted multipliers for our value_1:value_10 columns.

library(data.table)

setsize <- 3000000
value_num <- 10
factors <- c("factor_a", "factor_b", "factor_c", "factor_d", "factor_e", "factor_f")
random <- data.table(replicate(10, sample(factors, size = setsize,  replace = T))
                     , replicate(10, rnorm(setsize, mean = 700, sd = 50)))
lookup <- data.table("V1" = factors, replicate(10, seq(.90, 1.5, length.out = length(factors))))
wps <- paste("value", c(1:10), sep = "_")
names(random)[11:20] <- wps
names(lookup)[2:11] <- wps
setkeyv(random, "V1")
setkeyv(lookup, "V1")

Solution 1: It is fairly quick but I can't figure out how to generically refer to the i-columns like i.value_1 so I can pass them into a loop or better yet apply them all at once.

f <- function() {
  random[lookup, value_1 := value_1 * i.value_1, by = .EACHI]
  random[lookup, value_2 := value_2 * i.value_2, by = .EACHI]
  random[lookup, value_3 := value_3 * i.value_3, by = .EACHI]
  random[lookup, value_4 := value_4 * i.value_4, by = .EACHI]
  random[lookup, value_5 := value_5 * i.value_5, by = .EACHI]
  random[lookup, value_6 := value_6 * i.value_6, by = .EACHI]
  random[lookup, value_7 := value_7 * i.value_7, by = .EACHI]
  random[lookup, value_8 := value_8 * i.value_8, by = .EACHI]
  random[lookup, value_9 := value_9 * i.value_9, by = .EACHI]
  random[lookup, value_10 := value_10 * i.value_10, by = .EACHI]
}

system.time(f())

   user  system elapsed 
  0.184   0.000   0.181 

Solution 2: After I could not get solution 1 to be generic, I tried a set() based approach. However despite allowing me to specify the targeted value columns in the character vector wps, it is actually much much slower than the above. I know I am using it wrong but am unsure how to improve it to remove all the [.data.table overhead.

idx_groups <- random[,.(rowstart = min(.I), rowend = max(.I)), by = key(random)][lookup]
system.time(
for (i in 1:nrow(idx_groups)){
  rows <- idx_groups[["rowstart"]][i]:idx_groups[["rowend"]][i]
  for (j in wps) {
    set(random, i=rows, j=j, value= random[rows][[j]] * idx_groups[[j]][i])
  }  
})

   user  system elapsed 
  3.940   0.024   3.967 

Any advice on how to better structure these operations would be appreciated.

Edit: I'm very frustrated with myself for failing to try this obvious solution before posting this question:

system.time(
for (col in wps){
  random[lookup, (col) := list(get(col) * get(paste0("i.", col))), by = .EACHI, with = F]
})

   user  system elapsed 
  1.600   0.048   1.652 

which seems to do what I want with relative speed. However it is still 10x slower than the first solution above (I'm sure due to the repeated get()) so I'm still open to advice.

Edit 2: Replacing get() with eval(parse(text=col)) seems to have done the trick.

system.time(
for (col in wps){
  random[lookup, (col) := list(eval(parse(text=col)) * eval(parse(text=paste0("i.", col)))), by = .EACHI, with = F]
})
   user  system elapsed 
  0.184   0.000   0.185 

Edit 3: Several good working answers have been provided. Rafael's solution is probably best in the general case, though I will note that I could squeeze a few more milliseconds out of the call construction recommended by Jangorecki in exchange for a rather intimidating looking helper function. I've marked it as answered, thanks for the help everyone.

Answer 1

You can also use lapply:

cols <- noquote(paste0("value_",1:10))

random[lookup, (cols) := lapply (cols, function(x)  get(x) * get(paste0("i.", x))), by = .EACHI ]

In case your dataset is too big and you want to see a progress bar of your operation, you can use pblapply:

library(pbapply)

random[lookup, (cols) := pblapply(cols, function(x)  get(x) * get(paste0("i.", x))), by = .EACHI ]

Answer 2

This is about 2x slower than text parsing/call construction, but is a bit more readable:

random[lookup, (wps) := Map('*', mget(wps), mget(paste0('i.', wps))), by = .EACHI]

Answer 3

Thanks to jangorecki for pointing out his answer here, which dynamically builds the J expression using a helper function and then evaluates all at once. It avoids the overhead of parsing/get and seems to be the fastest solution I am going to get. I also like the ability to manually specify the function being called (some instances I might want / instead of *) and to inspect the J expression before it is evaluated.

batch.lookup = function(x) {
  as.call(list(as.name(":="),x
               ,as.call(c(
                 list(as.name("list")),
                 sapply(x, function(x) call("*", as.name(x), as.name(paste0("i.",x))), simplify=FALSE)
               ))
  ))
}

print(batch.lookup(wps))

`:=`(c("value_1", "value_2", "value_3", "value_4", "value_5", 
"value_6", "value_7", "value_8", "value_9", "value_10"), list(value_1 = value_1 * 
    i.value_1, value_2 = value_2 * i.value_2, value_3 = value_3 * 
    i.value_3, value_4 = value_4 * i.value_4, value_5 = value_5 * 
    i.value_5, value_6 = value_6 * i.value_6, value_7 = value_7 * 
    i.value_7, value_8 = value_8 * i.value_8, value_9 = value_9 * 
    i.value_9, value_10 = value_10 * i.value_10))

system.time(
  random[lookup, eval(batch.lookup(wps)), by = .EACHI])

   user  system elapsed 
   0.14    0.04    0.18