Calculate multiple aggregations on several variables using lapply(.SD, ...)

Question

I'd like to perform multiple aggregations, using data.table's lapply(.SD, ...) approach, i.e. calculate several different summary statistics on several variables. But my guesses as to how to do this end in either errors or the equivalent of rbind rather than cbind.

For example, to get the mean and median mpg in mtcars by cyl, one could do the following:

mtcars.dt <- data.table(mtcars)
mtcars.dt[, list(mpg.mean = mean(mpg), mpg.median = median(mpg)), by = "cyl"]
# Result:
    cyl mpg.mean mpg.median
|1:   6    19.74       19.7
|2:   4    26.66       26.0
|3:   8    15.10       15.2

But applying the .SD approach either rbinds the result on the functions:

mtcars.dt[, lapply(.SD, function(x) list(mean(x), median(x))),
          by = "cyl", .SDcols = c("mpg")]
# Result:
   cyl              mpg
1:   6 19.7428571428571
2:   6             19.7
3:   4 26.6636363636364
4:   4               26
5:   8             15.1
6:   8             15.2

Or breaks altogether:

mtcars.dt[, lapply(.SD, list(mean, median)),
          by = "cyl", .SDcols = c("mpg")]
# Result:
# Error in `[.data.table`(mtcars.dt, , lapply(.SD, list(mean, median)),  :
#  attempt to apply non-function

EDIT: As Senor O noted, some answers provided work for my example, but only because there's a single aggregation column. An ideal solution would work for multiple columns, for example replacing the following:

mtcars.dt[, list(mpg.mean = mean(mpg), mpg.median = median(mpg), 
                 hp.mean = mean(hp), hp.median = median(hp)), by = "cyl"]
# Result:
   cyl mpg.mean mpg.median hp.mean hp.median
1:   6    19.74       19.7  122.29     110.0
2:   4    26.66       26.0   82.64      91.0
3:   8    15.10       15.2  209.21     192.5

However, even if it works for a single column, it can still be useful. For example, my immediate use case is a function which takes a column name as a string and calculates multiple grouped-by metrics for it, something which is not possible without .SDcols AFAIK.

-1. This is redundant because the only case where you could expect good behavior is when .SD only has one column, in which case you're better of using that column name explicitly in j (lapply(mpg, function(x) list(mean(x), median(x)))) — Señor O
Thanks for noting that solutions provided so far may address my example but not an example with multiple columns; question updated to reflect this. As I also noted in the edit though, solutions are still useful if one is providing a column to aggregated as a string, e.g. in a function. — Max Ghenis

eddi eddi · Accepted Answer · 2014-06-10T22:23:33

You're missing a [[1]] or $mpg:

mtcars.dt[, lapply(.SD, function(x) list(mean(x), median(x)))[[1]],
            by="cyl", .SDcols=c("mpg")]
#or
mtcars.dt[, lapply(.SD, function(x) list(mean(x), median(x)))$mpg,
            by="cyl", .SDcols=c("mpg")]
#   cyl       V1   V2
#1:   6 19.74286 19.7
#2:   4 26.66364 26.0
#3:   8 15.10000 15.2

For the more general case, try:

mtcars.dt[, as.list(unlist(lapply(.SD, function(x) list(mean=mean(x),
                                                        median=median(x))))),
            by="cyl", .SDcols=c("mpg", "hp")]
#    cyl mpg.mean mpg.median hp.mean hp.median
# 1:   6    19.74       19.7  122.29     110.0
# 2:   4    26.66       26.0   82.64      91.0
# 3:   8    15.10       15.2  209.21     192.5

(or as.list(sapply(.SD, ...)))

Calculate multiple aggregations on several variables using lapply(.SD, ...)

2 Answers