How to group by different columns in R data.table?

Question

I am writing a function that will group by a data.table by different grouping columns and return specified summary stats (for each of the grouping) in a new column in the same data.table

I have tried 2 methods using for loops and both are giving me expected results but I am wondering if there is an efficient way to do it

This might be another method but i have not managed to get it to work

a = sample(c("a1","a2","a3","a4","a5"), 5000000, replace = TRUE)
b = sample(c("b1","b2","b3","b4","b5","b6","b7"), 5000000, replace = TRUE)
c = sample(c("c1","c2","c3","c4","c5","c6"), 5000000, replace = TRUE)
d = sample(1:100000, 5000000, replace = TRUE)
DT = data.table(a = a, b = b,c= c, d = d)

#columns to group by  
grp_by  <- list(c("a","b"),c("a","c"),c("b","c")) 

# required summary stats
FUNs <- c("mean") 

#stats required on this column
measure_col <- "d" 


#Method 1 - Using .SD
fn_agg_1 <- function(DT,grp_by,FUNs,measure_col) {
    for(i in grp_by) {
        for(j in FUNs) {
            new_col_name <- paste0(paste0(unlist(i),collapse  ="_"),"_",eval(j))
            DT[,(new_col_name) := lapply(.SD,get(j)), by = i, .SDcols = measure_col]
        }
    }
}

#Method 2 - Using eval-parse
fn_agg_2 <- function(DT,grp_by,FUNs,measure_col) {
    for(i in grp_by) {
        for(j in FUNs) {
            new_col_name <- paste0(paste0(unlist(i),collapse  ="_"),"_",eval(j))
            measure <- paste0(eval(j),"(",eval(measure_col),")")
            DT[,(new_col_name) := eval(parse(text=measure)), by = i]
        }
    }
}

system.time(fn_agg_1(DT,grp_by,FUNs,measure_col))
DT = data.table(a = a, b = b,c= c, d = d)
system.time(fn_agg_2(DT,grp_by,FUNs,measure_col))

#Result as expected 
DT[,.N, by = .(a,b,a_b_mean)][,-"N",with=FALSE]
 a  b a_b_mean
 1: a5 b3 49880.27
 2: a2 b7 49993.50
 3: a3 b3 49823.01
 4: a5 b7 50023.66
 5: a3 b6 50017.05

Answer 1

Another option using base::Map:

#columns to group by  
grp_by  <- list(c("a","b"),c("a","c"),c("b","c")) 

# required summary stats
funcs <- list(mean)

#stats required on this column
measure_col <- "d" 

DT[, .(Map(function(f, g) .SD[, lapply(.SD, f), by=g, .SDcols=measure_col], 
        rep(funcs, each=length(grp_by)),
        rep(grp_by, times=length(funcs))
    )), 
]$V1

output:

[[1]]
     a  b        d
 1: a5 b6 2.000000
 2: a2 b7 4.000000
 3: a2 b2 2.333333
 4: a3 b5 3.000000
 5: a5 b1 2.000000
 6: a5 b3 2.000000
 7: a4 b3 4.000000
 8: a4 b7 1.000000
 9: a1 b3 4.000000
10: a2 b4 2.000000
11: a1 b5 4.000000
12: a4 b4 2.000000
13: a4 b6 3.000000
14: a2 b6 4.000000

[[2]]
     a  c d
 1: a5 c3 2
 2: a2 c5 4
 3: a2 c4 3
 4: a3 c5 2
 5: a5 c4 2
 6: a5 c5 2
 7: a5 c1 2
 8: a4 c3 3
 9: a4 c5 1
10: a1 c5 4
11: a2 c3 2
12: a1 c6 4
13: a2 c2 2
14: a4 c1 2
15: a3 c1 4
16: a4 c2 4

[[3]]
     b  c   d
 1: b6 c3 2.0
 2: b7 c5 2.5
 3: b2 c4 2.5
 4: b5 c5 2.0
 5: b1 c4 2.0
 6: b3 c5 3.0
 7: b1 c1 2.0
 8: b3 c3 4.0
 9: b4 c3 2.0
10: b5 c6 4.0
11: b2 c2 2.0
12: b6 c1 2.0
13: b5 c1 4.0
14: b6 c2 4.0
15: b6 c4 4.0

---

edit: adding in jangorecki's alternative approach in comments

for (f in funcs)
    groupingsets(DT, j=lapply(.SD, f), by=Reduce(union, grp_by), 
        sets=grp_by, 
        .SDcols=measure_col)

---

data:

library(data.table)
set.seed(0L)
nr <- 20
a = sample(c("a1","a2","a3","a4","a5"), nr, replace = TRUE)
b = sample(c("b1","b2","b3","b4","b5","b6","b7"), nr, replace = TRUE)
c = sample(c("c1","c2","c3","c4","c5","c6"), nr, replace = TRUE)
d = sample(nr/5, nr, replace = TRUE)
DT = data.table(a = a, b = b,c= c, d = d)