R data.table: accessing column with variable name

user_4498251 0
Viewed 2071

I am using the wonderful R data.table package. However, accessing (i.e. manipulating by reference) a column with a variable name is very clumsy: If we are given a data.table dt which has two columns x and y and we want to add two columns and name it z then the command is

dt = dt[, z := x + y]

Now let us write a function add that takes as arguments a (reference to a) data.table dt and three column names summand1Name, summand2Name and resultName and it is supossed to execute the exact same command as above only with general column names. The solution I am using right now is reflection, i.e.

add = function(dt, summand1Name, summand2Name, resultName) {
  cmd = paste0('dt = dt[, ', resultName, ' := ', summand1Name, ' + ', summand2Name, ']')
  eval(parse(text=cmd))
  return(dt) # optional since manipulated  by reference
}

However I am absolutely not satisfied with this solution. First of all it's clumsy, it does not make fun to code like this. It is hard to debug and it just pisses me off and burns time. Secondly, it is harder to read and understand. Here is my question:

Can we write this function in a somewhat nicer way?

I am aware of the fact that one can access columns with variable name like so: dt[[resultName]] but when I write

dt[[resultName]] = dt[[summand1Name]] + dt[[summand2Name]]

then data.table starts to complain about having taken copies and not working by reference. I don't want that. Also I like the syntax dt = dt[<all 'database related operations'>] so that everything I am doing is stuck together in one pair of brackets. Isn't it possible to make use of a special symbol like backticks or so in order to indicate that the name currently used is not referencing an actual column of the data table but rather is a placeholder for the name of an actual column?

data.table r
4 Answers
new_add <- function(dt, summand1Name, summand2Name, resultName) {
    dt[, (resultName) := rowSums(.SD), .SDcols = c(summand1Name, summand2Name)]
}

This just takes the column names as strings. Adding this to amatsuo_net's speed test, and adding sindri's two versions too, we get the following:

microbenchmark::microbenchmark(
  original_add(dt, 'a', 'b', 'c'),
  my_add(dt, 'a', 'b', 'c'),
  list_access_add(dt, 'a', 'b', 'c'),
  david_add(dt, 'a', 'b', 'c'),
  new_add(dt, 'a', 'b', 'c'),
  get_add(dt, 'a', 'b', 'c'),
  mget_add(dt, 'a', 'b', 'c'))

## Unit: microseconds
##                               expr   min      lq     mean median      uq     max neval
##    original_add(dt, "a", "b", "c") 433.3  491.00  635.315  531.4  600.00  6064.0   100
##          my_add(dt, "a", "b", "c") 978.0 1062.35 1310.808 1208.8 1357.80  4157.3   100
## list_access_add(dt, "a", "b", "c") 303.9  331.95  432.939  363.8  434.05  3361.6   100
##       david_add(dt, "a", "b", "c") 401.3  440.65  659.748  474.5  577.75 11623.0   100
##         new_add(dt, "a", "b", "c") 518.9  588.30  765.394  667.1  741.95  5636.5   100
##         get_add(dt, "a", "b", "c") 415.1  454.50  674.699  491.1  546.70  9804.3   100
##        mget_add(dt, "a", "b", "c") 425.4  474.65  596.165  533.2  590.75  3888.0   100

It's not the fastest among the versions, but if you're looking for code that's painless to write then this is pretty simple. Since it works off of rowSums, it can also more easily be generalised to sum over an arbitrary number of columns at once.

Additionally, since dt isn't mentioned inside the square brackets, you can add this column definition inside a data.table "pipe" instead of as a function, if you want to:

dt[, (resultName) := rowSums(.SD), .SDcols = c(summand1Name, summand2Name)
][, lapply(.SD, range), .SDcols = c(summand1Name, summand2Name, resultName)
][... # etc
]
user_120806220

Using get():

add <- function(dt, summand1Name, summand2Name, resultName) {
  dt[, (resultName) := get(summand1Name) + get(summand1Name)]
}

Using mget():

add2 <- function(dt, summand1Name, summand2Name, resultName) {
  dt[, (resultName) := do.call(`+`, mget(c(summand1Name,summand2Name)))]
}

# Let
dt <- data.table(a = 1:5, b = 10:14)
# Then
add(dt, 'x', 'y', 'z')
dt[]
#    x y z
# 1: 1 2 2
user_45522950
Related