Aggregation in data.table by reference to column names

Question

I would like to aggregate some columns by a list of columns in a data.table. However, I would like to refrain from using the column names outside the quotation marks (in the by = .(desiredColumn1, desiredColumn2), that is). I am happy with using either the column names or the column indices. For example:

library(data.table)
x = as.data.table(iris)
x[, sum(Sepal.Width), by = .(Sepal.Length, Species)] # I want to avoid doing this
x[, sum("Sepal.Width"), by = .("Sepal.Length", "Species"), with = FALSE] # this does not work
x[, sum("Sepal.Width"), by = .(1, 5), with = FALSE]

Any ideas on how to do this?

score 2 · Accepted Answer · edited Oct 10 '16 at 19:10

2

We can use c with names

x[, sum(Sepal.Width), by = c(names(x)[c(1, 5)])]

edited Oct 10 '16 at 19:10

djhurio

5,437
4
27
48

answered Oct 10 '16 at 08:53

akrun

874,273
37
540
662

Hi akrun, thank you for your answer. How can I also avoid referring to `Sepal.Length`? For example: `x[, sum("Sepal.Length"), by = c(names(x)[c(1, 5)]), with = FALSE] # this fails` – GerasimosPanagiotakopoulos Oct 10 '16 at 08:59
@GerasimosPanagiotakopoulos You are not referring to `Sepal.Length` directly. Did you meant `Sepal.Width` – akrun Oct 10 '16 at 08:59
Yes, you are right. I meant `Sepal.Width`. – GerasimosPanagiotakopoulos Oct 10 '16 at 09:01
1

@GerasimosPanagiotakopoulos You can either specify it in `.SDcols` or use `get` or `eval` i.e. `x[, sum(get(names(x)[2])), by = c(names(x)[c(1, 5)])]` – akrun Oct 10 '16 at 09:02
1

There is no need for `names(x)`. This works: `x[, sum(get("Sepal.Width")), by = c("Sepal.Length", "Species")]` – djhurio Oct 10 '16 at 09:19
@djhurio It also works, but I think the OP wanted to pass the variables – akrun Oct 10 '16 at 09:21
He wanted to pass variable names as character vector. – djhurio Oct 10 '16 at 19:06
@djhurio I am not so sure. – akrun Oct 11 '16 at 05:14
Actually, I wanted to know how to do both, so thank you both! :) – GerasimosPanagiotakopoulos Oct 19 '16 at 08:18

Aggregation in data.table by reference to column names

1 Answers1

Linked