Mahdi Jadaliha Mahdi Jadaliha - 4 months ago 16
R Question

Combine several dcast data.table (which share same key) efficiently

Here is the simple problem I'm trying to solve: I have a data.table like following table, and I'm trying to use

dcast.data.table
function to calculate number of advancement for each group, but also I'm interested to calculate
median
of grades in each group:

set.seed(10);
DT = data.table(GROUP = sample(c("a","b","c"),100,replace = T),
ADVANCED = sample(c("ADVANCED","DROP"),100,replace = T),
GRADE = sample(1:10,100, replace=T))

GROUP ADVANCED GRADE
1: b ADVANCED 3
2: a ADVANCED 6
3: b ADVANCED 7
4: c ADVANCED 9

95: b DROP 6
96: c ADVANCED 5
97: a DROP 10
98: b ADVANCED 1
99: c DROP 6
100: a DROP 2
GROUP ADVANCED GRADE


Essentially here is the result I'm looking for:

result = merge(
dcast.data.table(DT,.Primitive("~")(GROUP,ADVANCED)),
dcast.data.table(DT,.Primitive("~")(GROUP,.),
value.var="GRADE",
fun.aggregate=median));

setnames(result,".","MEDIAN_GRADE")

GROUP ADVANCED DROP MEDIAN_GRADE
1: a 17 19 6
2: b 20 21 7
3: c 13 10 6


Now I'm wondering how can I do it without making two separate dcast tables and merge at the end. I'm dealing with many row and column in my tables and grouping by key is a bottleneck. I'm wondering is there a better way to calculate this?

** Since my first question was vague I edit completely (thanks to Frank and Akrun for their feedback).

Answer

For the updated question

setnames(dcast(DT, GROUP~ADVANCED, length)[dcast(DT, GROUP~., median),
            on = "GROUP"], ".", "MEDIAN_GRADE")[]
#   GROUP ADVANCED DROP MEDIAN_GRADE
#1:     a       17   19            6
#2:     b       20   21            7
#3:     c       13   10            6

Or a faster approach would be to group by 'GROUP', get the median of 'GRADE' and then do the join on the dcast output

DT[,.(MEDIAN_GRADE = median(GRADE)) , .(GROUP)][
              dcast(DT, GROUP ~ ADVANCED, length), on = 'GROUP']