Three Diag Three Diag - 2 months ago 6
R Question

Aggregating across columns of data table

I have data.table that looks like

ID1 ID2 ID3 X1 X2 X3 X4 X5 ....
E01 ASD DSA 9 2 1 22 4
E03 SDF FDX 21 9 0 10 2
E04 MAX XXX 77 2 .5 23 1.5 ..


For each ID1 (and leaving ID2 and ID3 unaltered), I would like to compute the mean of different subgroups of Xx, the output should look like

ID1 ID2 ID3 av_X1_X3 av_X4_X5 ...
E01 ASD DSA 4 13 ...
E03 SDF FDX 10 6


Xx ranges from X0 to X90 and I need to be able to easily input three to five possible groupings of Xx.

Answer

You didn't mention how subgroups is defined. If you intended to do it manually for each subgroup, this should not be too hard to input

s <- "ID1 ID2 ID3 X1 X2 X3 X4 X5
E01 ASD DSA 9  2  1  22 4
E03 SDF FDX 21 9  0  10 2
E04 MAX XXX 77 2  .5 23 1.5"

dt <- fread(s)
dt[, av_X1_X3 := mean(c(X1, X3)), by = ID1]

To add more columns in one line:

dt[, `:=`(av_X1_X3 = mean(c(X1,X3)), av_X4_X5 = mean(c(X4,X5))),by=ID1]