2

I am trying to use values from a look up table, to multiply corresponding values in a main table.

This is an example of some data

The look up

lu = structure(list(year = 0:12, val = c(1.6422, 1.6087, 1.5909, 1.4456, 
1.4739, 1.4629, 1.467, 1.4619, 1.2588, 1.1233, 1.1664, 1.1527, 
1.2337)), .Names = c("year", "val"), class = "data.frame", row.names = c(NA, 
-13L))

Main data

dt = structure(list(year = c(3L, 4L, 6L, 10L, 3L, 9L, 10L, 7L, 7L, 
1L), x = 1:10, y = 1:10), .Names = c("year", "x", "y"), row.names = c(NA, 
-10L), class = c("data.table", "data.frame"))

I can produce the results I want by merging and then multiplying one column at a time

library(data.table) 

dt = merge(dt, lu, by = "year")
dt[, xnew := x*val][, ynew := y*val]

However, I have many variables to apply this over. There have been many questions on this, but I cannot get it to work.

Using ideas from How to apply same function to every specified column in a data.table , and R Datatable, apply a function to a subset of columns , I tried

dt[, (c("xnew", "ynew")):=lapply(.SD, function(i) i* val), .SDcols=c("x", "y")]

Error in FUN(X[[i]], ...) : object 'val' not found

for (j in c("x", "y")) set(dt, j = j, value = val* dat[[j]])

Error in set(dt, j = j, value = val * dt[[j]]) : object 'val' not found

And just trying the multiplication without assigning (from Data table - apply the same function on several columns to create new data table columns) also didnt work.

dt[, lapply(.SD, function(i) i* val), .SDcols=c("x", "y")]

Error in FUN(X[[i]], ...) : object 'val' not found

Please could you point out my error. Thanks.

Im using data.table version v1.9.6.

Community
  • 1
  • 1
user2957945
  • 2,353
  • 2
  • 21
  • 40
  • It works for me. Did you update to the latest version of `data.table`? (which is 1.10.4) – Jaap Mar 12 '17 at 17:06
  • Im at v1.9.6. I can update on my home PC to try Akrun's answer, however, I will be restricted to v1.9.6 at work. (didnt think version would be an issue as the other questions were a couple of years old). Ill add details to the question. – user2957945 Mar 12 '17 at 17:07
  • It should work in 1.9.6 as well AFAIK – Jaap Mar 12 '17 at 17:09
  • @Jaap ; okay thanks. I reinstalled v1.9.6 again and the code in the Q is working. (bit embarrassed must have been something local to my pc) – user2957945 Mar 12 '17 at 17:40

1 Answers1

3

We can try by join and then by specifying .SDcols

dt[lu, on = .(year), nomatch =0 
    ][, c("x_new", "y_new") := lapply(.SD, `*`, val), .SDcols = x:y][]
akrun
  • 874,273
  • 37
  • 540
  • 662
  • thanks. *could not find function "."* . I'll update my package version – user2957945 Mar 12 '17 at 17:06
  • @user2957945 if you have trouble in installing the new version, try with `https://github.com/Rdatatable/data.table/wiki/Installation` to get the devel version – akrun Mar 12 '17 at 17:10