Assign multiple columns using := in data.table, by group
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
Apply multiple functions to multiple columns in data.table by group
First you need to change your function. data.table expects consistent types and median can return integer or double values depending on input.
my.summary <- function(x) list(mean = mean(x), median = as.numeric(median(x)))
Then you need to ensure that only the first level of the nested list is unlisted. The result of the unlist call still needs to be a list (remember, a data.table is a list of column vectors).
DT[, unlist(lapply(.SD, my.summary), recursive = FALSE), by = c, .SDcols = c("a", "b")]
# c a.mean a.median b.mean b.median
#1: 1 1.5 1.5 2.5 2.5
#2: 2 4.0 4.0 5.0 5.0
Multiple functions on multiple columns by group, and create informative column names
If I understand correctly, this question consists of two parts:
- How to group and aggregate with multiple functions over a list of columns and generate new column names automatically.
- How to pass the names of the functions as a character vector.
For part 1, this is nearly a duplicate of Apply multiple functions to multiple columns in data.table but with the additional requirement that the results should be grouped using by =.
Therefore, eddi's answer has to be modified by adding the parameter recursive = FALSE in the call to unlist():
my.summary = function(x) list(N = length(x), mean = mean(x), median = median(x))
dt[, unlist(lapply(.SD, my.summary), recursive = FALSE),
.SDcols = ColChoice, by = category]
category c1.N c1.mean c1.median c4.N c4.mean c4.median
1: f 3974 9999.987 9999.989 3974 9.994220 9.974125
2: w 4033 10000.008 9999.991 4033 10.004261 9.986771
3: n 4025 9999.981 10000.000 4025 10.003686 9.998259
4: x 3975 10000.035 10000.019 3975 10.010448 9.995268
5: k 3957 10000.019 10000.017 3957 9.991886 10.007873
6: j 4027 10000.026 10000.023 4027 10.015663 9.998103
...
For part 2, we need to create my.summary() from a character vector of function names. This can be achieved by "programming on the language", i.e, by assembling an expression as character string and finally parsing and evaluating it:
my.summary <-
sapply(FunChoice, function(f) paste0(f, "(x)")) %>%
paste(collapse = ", ") %>%
sprintf("function(x) setNames(list(%s), FunChoice)", .) %>%
parse(text = .) %>%
eval()
my.summary
function(x) setNames(list(length(x), mean(x), sum(x)), FunChoice)
<environment: 0xe376640>
Alternatively, we can loop over the categories and rbind() the results afterwards:
library(magrittr) # used only to improve readability
lapply(dt[, unique(category)],
function(x) dt[category == x,
c(.(category = x), unlist(lapply(.SD, my.summary))),
.SDcols = ColChoice]) %>%
rbindlist()
Benchmark
So far, 4 data.table and one dplyr solutions have been posted. At least one of the answers claims to be "superfast". So, I wanted to verify by a benchmark with varying number of rows:
library(data.table)
library(magrittr)
bm <- bench::press(
n = 10L^(2:6),
{
set.seed(12212018)
dt <- data.table(
index = 1:n,
category = sample(letters[1:25], n, replace = T),
c1 = rnorm(n, 10000),
c2 = rnorm(n, 1000),
c3 = rnorm(n, 100),
c4 = rnorm(n, 10)
)
# use set() instead of <<- for appending additional columns
for (i in 5:100) set(dt, , paste0("c", i), rnorm(n, 1000))
tables()
ColChoice <- c("c1", "c4")
FunChoice <- c("length", "mean", "sum")
my.summary <- function(x) list(length = length(x), mean = mean(x), sum = sum(x))
bench::mark(
unlist = {
dt[, unlist(lapply(.SD, my.summary), recursive = FALSE),
.SDcols = ColChoice, by = category]
},
loop_category = {
lapply(dt[, unique(category)],
function(x) dt[category == x,
c(.(category = x), unlist(lapply(.SD, my.summary))),
.SDcols = ColChoice]) %>%
rbindlist()
},
dcast = {
dcast(dt, category ~ 1, fun = list(length, mean, sum), value.var = ColChoice)
},
loop_col = {
lapply(ColChoice, function(col)
dt[, setNames(lapply(FunChoice, function(f) get(f)(get(col))),
paste0(col, "_", FunChoice)),
by=category]
) %>%
Reduce(function(x, y) merge(x, y, by="category"), .)
},
dplyr = {
dt %>%
dplyr::group_by(category) %>%
dplyr::summarise_at(dplyr::vars(ColChoice), .funs = setNames(FunChoice, FunChoice))
},
check = function(x, y)
all.equal(setDT(x)[order(category)],
setDT(y)[order(category)] %>%
setnames(stringr::str_replace(names(.), "_", ".")),
ignore.col.order = TRUE,
check.attributes = FALSE
)
)
}
)
The results are easier to compare when plotted:
library(ggplot2)
autoplot(bm)
Please, note the logarithmic time scale.
For this test case, the unlist approach is always the fastest method, followed by dcast. dplyr is catching up for larger problem sizes n. Both lapply/loop approaches are less performant. In particular, Parfait's approach to loop over the columns and merge subresults afterwards seems to be rather sensitive to problem sizes n.
Edit: 2nd benchmark
As suggested by jangorecki, I have repeated the benchmark with much more rows and also with a varying number of groups.
Due to memory limitations, the largest problem size is 10 M rows times 102 columns which takes 7.7 GBytes of memory.
So, the first part of the benchmark code is modified to
bm <- bench::press(
n_grp = 10^(1:3),
n_row = 10L^seq(3, 7, by = 2),
{
set.seed(12212018)
dt <- data.table(
index = 1:n_row,
category = sample(n_grp, n_row, replace = TRUE),
c1 = rnorm(n_row),
c2 = rnorm(n_row),
c3 = rnorm(n_row),
c4 = rnorm(n_row, 10)
)
for (i in 5:100) set(dt, , paste0("c", i), rnorm(n_row, 1000))
tables()
...
As expected by jangorecki, some solutions are more sensitive to the number of groups than others. In particular, performance of loop_category is degrading much stronger with the number of groups while dcast seems to be less affected. For fewer groups, the unlist approach is always faster than dcast while for many groups dcast is faster. However, for larger problem sizes unlist seems to be ahead of dcast.
Edit 2019-03-12: Computing on the language, 3rd benchmark
Inspired by this follow-up question, I have have added a computing on the language approach where the whole expression is created as character string, parsed and evaluated.
The expression is created by
library(magrittr)
ColChoice <- c("c1", "c4")
FunChoice <- c("length", "mean", "sum")
my.expression <- CJ(ColChoice, FunChoice, sorted = FALSE)[
, sprintf("%s.%s = %s(%s)", V1, V2, V2, V1)] %>%
paste(collapse = ", ") %>%
sprintf("dt[, .(%s), by = category]", .) %>%
parse(text = .)
my.expression
expression(dt[, .(c1.length = length(c1), c1.mean = mean(c1), c1.sum = sum(c1),
c4.length = length(c4), c4.mean = mean(c4), c4.sum = sum(c4)), by = category])
This is then evaluated by
eval(my.expression)
which yields
category c1.length c1.mean c1.sum c4.length c4.mean c4.sum
1: f 3974 9999.987 39739947 3974 9.994220 39717.03
2: w 4033 10000.008 40330032 4033 10.004261 40347.19
3: n 4025 9999.981 40249924 4025 10.003686 40264.84
4: x 3975 10000.035 39750141 3975 10.010448 39791.53
5: k 3957 10000.019 39570074 3957 9.991886 39537.89
6: j 4027 10000.026 40270106 4027 10.015663 40333.07
...
I have modified the code of the 2nd benchmark to include this approach but had to reduce the additional columns from 100 to 25 in order to cope with the memory limitations of a much smaller PC. The chart shows that the "eval" approach is almost always the fastest or second:
Any simpler way to assign multiple columns in Python like R data.table :=
You can use DataFrame.assign to assign multiple columns:
df = df.assign(Col_A=df.A/df.B, Col_B=df.C/df.D, Col_C=df.E/df.F*1000000, Col_D=df.G/df.H*1000000)
Example:
df = pd.DataFrame(np.random.random((4, 8)), columns=list('ABCDEFGH'))
# A B ... H
# 0 0.771211 0.238201 ... 0.311904
# 1 0.197548 0.635218 ... 0.626639
# 2 0.332333 0.838589 ... 0.477978
# 3 0.929690 0.327412 ... 0.046179
df = df.assign(Col_A=df.A/df.B, Col_B=df.C/df.D, Col_C=df.E/df.F*1000000, Col_D=df.G/df.H*1000000)
# A B ... H Col_A Col_B Col_C Col_D
# 0 0.771211 0.238201 ... 0.311904 3.237647 1.547285 1.463586e+06 2.845234e+06
# 1 0.197548 0.635218 ... 0.626639 0.310993 1.385892 1.394466e+07 2.685293e+05
# 2 0.332333 0.838589 ... 0.477978 0.396300 0.078238 8.494174e+06 6.001031e+05
# 3 0.929690 0.327412 ... 0.046179 2.839514 0.852443 1.962892e+06 8.791233e+06
If you want column names with spaces, you can use a dict:
df = df.assign(**{'Col A': df.A/df.B, 'Col B': df.C/df.D, 'Col C': df.E/df.F*1000000, 'Col D': df.G/df.H*1000000})
# A B ... H Col A Col B Col C Col D
# 0 0.868320 0.086743 ... 0.505330 10.010311 6.680195 1.147554e+06 2.620416e+05
# 1 0.244341 0.908793 ... 0.389684 0.268863 2.388179 2.196769e+06 2.235063e+06
# 2 0.917949 0.248149 ... 0.710027 3.699188 0.453094 1.311617e+06 1.004200e+06
# 3 0.616655 0.498817 ... 0.703579 1.236235 2.186589 1.272981e+06 8.602272e+05
Add multiple columns to R data.table in one function call?
Since data.table v1.8.3, you can do this:
DT[, c("new1","new2") := myfun(y,v)]
Another option is storing the output of the function and adding the columns one-by-one:
z <- myfun(DT$y,DT$v)
head(DT[,new1:=z$r1][,new2:=z$r2])
# x y v new1 new2
# [1,] a 1 42 43 -41
# [2,] a 3 42 45 -39
# [3,] a 6 42 48 -36
# [4,] b 1 4 5 -3
# [5,] b 3 5 8 -2
# [6,] b 6 6 12 0
Related Topics
What Is the Purpose of Setting a Key in Data.Table
Simpler Population Pyramid in Ggplot2
Merging Two Data Frames Using Fuzzy/Approximate String Matching in R
Read All Files in Directory and Apply Multiple Functions to Each Data Frame
Sum Values in a Rolling/Sliding Window
How to Calculate Cumulative Sum
Check If the Number Is Integer
Use Variable Names in Functions of Dplyr
Lattice: Multiple Plots in One Window
Select First and Last Row from Grouped Data
Summarizing Multiple Columns With Data.Table
Subscript Letters in Ggplot Axis Label
Create a Data.Frame Where a Column Is a List
Add Correct Century to Dates With Year Provided as "Year Without Century", %Y
Call Apply-Like Function on Each Row of Dataframe With Multiple Arguments from Each Row