Create n new columns by n distinct groups in data.table

user_8099679 0
Viewed 94

I have the following data table, and would like to sum y twice grouping the first time by g1 and the second time by g2.

Usually I would just chain the calculations together, but I would like to be able to do the grouped sum n different times by n groups.

library(data.table)
  DT <- data.table(
    g1 = c("a", "b"),
    g2 = c("a", "a"),
    y = c(3,5)
  )

new_cols <- paste0("sum_by_", c("g1", "g2"))
group_cols <- c("g1", "g2")

# Supplying cols to by like this groups by g1 AND g2, when in reality I want it to 
# take g1 the first time and g2 the second time. 
DT[, paste(new_cols) := lapply(rep(y, length(new_cols)), sum),
   by = .(group_cols)][]

this gives me:

#    g1 g2 y sum_by_g1 sum_by_g2
# 1:  a  a 3         3         3
# 2:  b  a 5         5         5

when I actually want:

#    g1 g2 y sum_by_g1 sum_by_g2
# 1:  a  a 3         3         8
# 2:  b  a 5         5         8

Is there any slick data.table way to do this? Something like supplying .SD to by (this in itself doesn't seem to work)?

Edit: Changed y from c(1,1) to c(3,5)

Edit Rationale: Actual and desired outputs while y = c(1,1) gave the impression that I wanted to count the observations in each group, when I actually want to sum(y) for each group.

data.table r
2 Answers

The grouping should be separate as a a and a b are regarded as unique elements thus, there is only a single observation per group

for(i in seq_along(group_cols)) DT[, (new_cols[i]) := sum(y), by = c(group_cols[i])]

-output

DT
   g1 g2 y sum_by_g1 sum_by_g2
1:  a  a 3         3         8
2:  b  a 5         5         8
user_37322710

You can try Reduce like below

> Reduce(function(dt, g) dt[, paste0("sum_by_", g) := .N, g], list(DT, "g1", "g2"))[]
   g1 g2 y sum_by_g1 sum_by_g2
1:  a  a 1         1         2
2:  b  a 1         1         2

or

> Reduce(function(dt, g) dt[, paste0("sum_by_", g) := .N, g], c("g1", "g2"),init = DT)[]
   g1 g2 y sum_by_g1 sum_by_g2
1:  a  a 1         1         2
2:  b  a 1         1         2
user_121587570
Related