0

The problem is as follows: I have a data.table with columns A and B. A summary is required and its name is passed as a character vector in variable var1.

I have tried to find an answer for some time now, see e.g. this and this SO posts. Being unable to find a proper solution, I feel forced to ask this myself.

Now what I want to do is (using data.frame)

tmp[, var1] <- rep(1, nrow(tmp))
tmp <- aggregate(formula(paste(var1, "~ A + B")), tmp, sum)

but I fail to do so with data.table with my last and best effort being

tmp <- tmp[, list(..var1 = .N), by = list(A, B)]

Now, what is wrong with my code and how do I fix it?

And note that I do NOT want to use the := operator, because I want the result to be exactly as it would be from aggregate().

Edit 1: A working example:

library(data.table)
tmp <- data.table(A=c("R","G","G","B","B","B"), B=c(1,1,1,2,1,2))
print(tmp)

var1 <- "C"

tmp[, var1] <- rep(1, nrow(tmp))
tmp2 <- aggregate(formula(paste(var1, "~ A + B")), tmp, sum)
print(tmp2)

tmp3 <- tmp[, list(..var1 = .N), by = list(A, B)]
print(tmp3)
Laurimann
  • 135
  • 1
  • 6
  • You should share a minimal [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – markus Jan 29 '19 at 22:31
  • 1
    Is it enough to just run `setnames(tmp[, .N, by = list(A, B)], "N", var1)`? – Hugh Jan 30 '19 at 02:12

1 Answers1

2

Hope that I did not misread your qn. Here are some options:

1) using base::setNames

DT[, setNames(.(.N), var1), by=.(A, B)]

2) using data.table::setnames

setnames(DT[, .N, by=.(A, B)], "N", var1)[]

3) using base::structure followed by base::as.list

DT[, as.list(structure(.N, names=var1)), by=.(A, B)]

data:

DT <- data.table(A=c(1,1,2,2), B=c(1,1,2,3))
var1 <- "myCol"
chinsoon12
  • 25,005
  • 4
  • 25
  • 35
  • 1
    Doing it after the by= like (2) is going to be the most efficient since that uses GForce. – Frank Jan 30 '19 at 05:42
  • Yes, (1) is nearest to what I need although far too inelegant to match my expectations of data.table's syntax. – Laurimann Feb 08 '19 at 07:51