What is the best way to assign to multiple columns using data.table? For example:
f <- function(x) {c("hi", "hello")}
x <- data.table(id = 1:10)
I would like to do something like this (of course this syntax is incorrect):
x[ , (col1, col2) := f(), by = "id"]
And to extend that, I may have many columns with names stored in a variable (say col_names) and I would like to do:
x[ , col_names := another_f(), by = "id", with = FALSE]
What is the correct way to do something like this?
This now works in v1.8.3 on R-Forge. Thanks for highlighting it!
x <- data.table(a = 1:3, b = 1:6)
f <- function(x) {list("hi", "hello")}
x[ , c("col1", "col2") := f(), by = a][]
# a b col1 col2
# 1: 1 1 hi hello
# 2: 2 2 hi hello
# 3: 3 3 hi hello
# 4: 1 4 hi hello
# 5: 2 5 hi hello
# 6: 3 6 hi hello
x[ , c("mean", "sum") := list(mean(b), sum(b)), by = a][]
# a b col1 col2 mean sum
# 1: 1 1 hi hello 2.5 5
# 2: 2 2 hi hello 3.5 7
# 3: 3 3 hi hello 4.5 9
# 4: 1 4 hi hello 2.5 5
# 5: 2 5 hi hello 3.5 7
# 6: 3 6 hi hello 4.5 9
mynames = c("Name1", "Longer%")
x[ , (mynames) := list(mean(b) * 4, sum(b) * 3), by = a]
# a b col1 col2 mean sum Name1 Longer%
# 1: 1 1 hi hello 2.5 5 10 15
# 2: 2 2 hi hello 3.5 7 14 21
# 3: 3 3 hi hello 4.5 9 18 27
# 4: 1 4 hi hello 2.5 5 10 15
# 5: 2 5 hi hello 3.5 7 14 21
# 6: 3 6 hi hello 4.5 9 18 27
x[ , get("mynames") := list(mean(b) * 4, sum(b) * 3), by = a][] # same
# a b col1 col2 mean sum Name1 Longer%
# 1: 1 1 hi hello 2.5 5 10 15
# 2: 2 2 hi hello 3.5 7 14 21
# 3: 3 3 hi hello 4.5 9 18 27
# 4: 1 4 hi hello 2.5 5 10 15
# 5: 2 5 hi hello 3.5 7 14 21
# 6: 3 6 hi hello 4.5 9 18 27
x[ , eval(mynames) := list(mean(b) * 4, sum(b) * 3), by = a][] # same
# a b col1 col2 mean sum Name1 Longer%
# 1: 1 1 hi hello 2.5 5 10 15
# 2: 2 2 hi hello 3.5 7 14 21
# 3: 3 3 hi hello 4.5 9 18 27
# 4: 1 4 hi hello 2.5 5 10 15
# 5: 2 5 hi hello 3.5 7 14 21
# 6: 3 6 hi hello 4.5 9 18 27
Older version using the with argument (we discourage this argument when possible):
x[ , mynames := list(mean(b) * 4, sum(b) * 3), by = a, with = FALSE][] # same
# a b col1 col2 mean sum Name1 Longer%
# 1: 1 1 hi hello 2.5 5 10 15
# 2: 2 2 hi hello 3.5 7 14 21
# 3: 3 3 hi hello 4.5 9 18 27
# 4: 1 4 hi hello 2.5 5 10 15
# 5: 2 5 hi hello 3.5 7 14 21
# 6: 3 6 hi hello 4.5 9 18 27
dim returns a vector so converting that to type list should rotate it; e.g. [,c("rows","cols"):=as.list(dim(get(objectName))),by=objectName]. Trouble is that as.list has call overhead and also copies the small vector. If efficiency is a problem as number of groups rises then please let us know.
x[,mynames:=list(mean(b)*4,sum(b)*3),by=a,with=FALSE][]) now throws a warning, so maybe remove it? On a related note, has anyone suggested that, with options(datatable.WhenJisSymbolThenCallingScope=TRUE), an assignment like x[,mynames:=list(mean(b)*4,sum(b)*3),by=a] should in fact work? Seems like that would be consistent with the other changes, though I guess it might break too much existing user code (?).by=a it will work, but return a different answer. The mean(a) and sum(a) aggregates are being recycled within each group when by=a. Without by=a it just sticks the mean and sum for the entire column into each cell (i.e. different numbers).
x[, {ans=f(); names(ans):=ans}, by=a] could be implemented. That syntax conveys the intent quite nicely to my eye. What you think?The following shorthand notation might be useful. All credit goes to Andrew Brooks, specifically this article.
dt[,`:=`(avg=mean(mpg), med=median(mpg), min=min(mpg)), by=cyl]
c() := list()
byas @Christoph_J is correct to say. Link to your question added to FR#2120 "Drop needing with=FALSE for LHS of :=", so it won't get forgotten to revisit.f()is a function returning multiple values, one for each of your columns.