Apply aggregation function to multiple data.table columns with customized output names

Question

Apologies if this is a duplicate. I am very new to data.table, and have seen very similar questions asked here but none that exactly answered my question.

I would like to find a concise syntax to aggregate multiple columns of a data.table with the same aggregation function, with customized names of the resulting aggregated columns.

setup

library(data.table)
data(mtcars)
setDT(mtcars)

If I call

mtcars[, lapply(.SD, sum, na.rm = TRUE), by = .(am, gear), .SDcols = c('mpg','cyl')]

The result is

   am gear   mpg cyl
1:  1    4 210.2  36
2:  0    3 241.6 112
3:  0    4  84.2  20
4:  1    5 106.9  30

This is great but I want the last two columns to be called by customized names that I define ahead of time.

I can achieve the desired result with

mtcars[, .(sum_of_mpg = sum(mpg, na.rm = TRUE), sum_of_cyl = sum(cyl, na.rm = TRUE)), by = .(am, gear)]

This results in

  am gear sum_of_mpg sum_of_cyl
1:  1    4      210.2         36
2:  0    3      241.6        112
3:  0    4       84.2         20
4:  1    5      106.9         30

But this result cannot be generalized to allow me to define the custom names beforehand.

I've tried the code below and various variants of it, but nothing gives this result in one step.

custom_names <- c('sum_of_mpg','sum_of_cyl')
mtcars[, (custom_names) = lapply(.SD, sum, na.rm = TRUE), by = .(am, gear), .SDcols = c('mpg','cyl')]

Is there a way to do this concisely? This is necessary because the code may be embedded in a function and may need to work on an indefinite number of columns.

Peace Wang · Accepted Answer · 2021-02-13 16:02:23Z

1

Here is an available solution

in_names <- c('mpg','cyl')
custom_names <- c('sum_of_mpg','sum_of_cyl')

mtcars[, lapply(.SD, sum, na.rm = TRUE), by = .(am, gear), .SDcols = in_names][
,setnames(.SD,in_names,custom_names)][]

Another a little complex solution you can have try

mtcars[,as.list(unlist(lapply(.SD, function(x)
               list(sum=sum(x))))),
               by = .(am,gear),
               .SDcols = in_names]

Improved solution

mtcars[, sapply(.SD, function(x) list(sum = sum(x))),
  .SDcols = in_names,
  by = .(am, gear)]

edited Feb 13, 2021 at 16:02

answered Feb 13, 2021 at 6:13

Peace Wang

2,4291 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

qdread Over a year ago

I like the "pipe" style here, I guess it isn't possible to assign the names in the first bracketed statement though?

Peace Wang Over a year ago

I think it's possible but I fail to realize it after several failure. Luckily, I finally get a satifying solution. Please see the edited answer.

r2evans · Accepted Answer · 2021-04-02 14:25:03Z

1

Single [-call using .SDcols and setNames within the lapply:

cols <- c('mpg','cyl')
mtcars[, lapply(setNames(.SD, paste0("sum_of_", cols)), sum, na.rm = TRUE),
       by = .(am, gear), .SDcols = cols]
#       am  gear sum_of_mpg sum_of_cyl
#    <num> <num>      <num>      <num>
# 1:     1     4      210.2         36
# 2:     0     3      241.6        112
# 3:     0     4       84.2         20
# 4:     1     5      106.9         30

answered Apr 2, 2021 at 14:25

r2evans

167k8 gold badges92 silver badges176 bronze badges

Collectives™ on Stack Overflow

Apply aggregation function to multiple data.table columns with customized output names

setup

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

setup

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related