How to look at the logic behind comma-separated strings in multiple `by` when aggregating in data.table?

user_6574038 0
Viewed 45

In data.table we can do the following to aggregate data:

as.data.table(dat)[, sum(x), by='g,d']
#     g d          V1
#  1: a 1  1.69036285
#  2: a 2 -3.17443208
#  3: a 3 -1.25045813
#  4: a 4 -0.54787167
#  5: b 1  0.88889824
#  6: b 2 -2.09544124
#  7: b 3 -0.98484236
#  8: b 4  1.43268901
#  9: c 1  4.79890459
# 10: c 2 -0.76440764
# 11: c 3 -1.18732658
# 12: c 4  0.08166043
# 13: d 1 -2.66427903
# 14: d 2 -0.30073505
# 15: d 3  0.09255934
# 16: d 4  4.18462582

However, when using predefined arguments within a function, from all attempts

as.data.table(dat)[, sum(x), by=paste("g", "d", sep=",")]
# Error in `[.data.table`(as.data.table(dat), , sum(x), by = paste(c("g",  :
# 'by' appears to evaluate to column names but isn't c() or key(). Use by=list(...)
# if you can. Otherwise, by=eval(paste(c("g", "d"), collapse = ",")) should work.
# This is for efficiency so data.table can detect which columns are needed.

as.data.table(dat)[, sum(x), by=paste(c("g", "d"), collapse=",")]
# Error in `[.data.table`(as.data.table(dat), , sum(x), by = paste("g",  : ...

bys <- c("g", "d")
as.data.table(dat)[, sum(x), by=paste(bys, collapse=",")]
# Error in `[.data.table`(as.data.table(dat), , sum(x), by = paste("g",  : ...

only the following seems to work:

bys <- paste("g", "d", sep=",")
as.data.table(dat)[, sum(x), by=bys]
#     g d          V1
#  1: a 1  1.69036285
#  2: a 2 -3.17443208
#  3: a 3 -1.25045813
#  4: a 4 -0.54787167
#  5: b 1  0.88889824
#  6: b 2 -2.09544124
#  7: b 3 -0.98484236
#  8: b 4  1.43268901
#  9: c 1  4.79890459
# 10: c 2 -0.76440764
# 11: c 3 -1.18732658
# 12: c 4  0.08166043
# 13: d 1 -2.66427903
# 14: d 2 -0.30073505
# 15: d 3  0.09255934
# 16: d 4  4.18462582

As I understand it, the differences shouldn't matter, should they? Why is that so?

Data

set.seed(42)
dat <- transform(expand.grid(d=1:4, g=letters[1:4], g2=1:4), x=rnorm(64))
data.table aggregate r
2 Answers

The error is thrown on this line in the [.data.table function in data.table.R. The by argument is captured by non-standard evaluation; in your case that happens using substitute on this line.

by=bysub= if (missing(by)) NULL else substitute(by)

This makes the three versions of your example evaluate differently, as you can see in this toy example:

f <- function(by) {
  substitute(by)
}

bys <- paste("g", "d", sep = ",")

f(bys)
#> bys

f(paste("g", "d", sep = ","))
#> paste("g", "d", sep = ",")

f("g,d")
#> [1] "g,d"

The result of this substitute call is stored as a variable called bysub, which is very carefully and thoroughly checked for whether it is a character, a name, an expression or a call. If, as in your case, it is a call, it is checked for which function is being called. Only certain function calls are handled without error, including eval, c, : and key. Calls to other functions are handled with the error in your question.

This appears to be a conscious design decision to improve the efficiency of the [.data.table function overall. Since this function is over 1700 lines long, I have to admit I haven't explored the "why" of this design, but hopefully you can at least see the "how".

user_125003150

As mentioned in the error, either wrap with eval

as.data.table(dat)[, sum(x), by=eval(paste("g", "d", sep=","))]

-output

#    g d          V1
# 1: a 1  1.69036285
# 2: a 2 -3.17443208
# 3: a 3 -1.25045813
# 4: a 4 -0.54787167
# 5: b 1  0.88889824
# 6: b 2 -2.09544124
# 7: b 3 -0.98484236
# 8: b 4  1.43268901
# 9: c 1  4.79890459
#10: c 2 -0.76440764
#11: c 3 -1.18732658
#12: c 4  0.08166043
#13: d 1 -2.66427903
#14: d 2 -0.30073505
#15: d 3  0.09255934
#16: d 4  4.18462582

Or concatenate with c

as.data.table(dat)[, sum(x), by=c(paste("g", "d", sep=","))]

-output

#     g d          V1
# 1: a 1  1.69036285
# 2: a 2 -3.17443208
# 3: a 3 -1.25045813
# 4: a 4 -0.54787167
# 5: b 1  0.88889824
# 6: b 2 -2.09544124
# 7: b 3 -0.98484236
# 8: b 4  1.43268901
# 9: c 1  4.79890459
#10: c 2 -0.76440764
#11: c 3 -1.18732658
#12: c 4  0.08166043
#13: d 1 -2.66427903
#14: d 2 -0.30073505
#15: d 3  0.09255934
#16: d 4  4.18462582
user_37322710
Related