R row means on multiple columns by groups (or unique IDs)
I have a data frame like below (20,000 rows by 49 cols). Each row has a unique name (ID), each ID has 3 repeat reads in 3 columns (e.g. D15C D15C.1 D15C.2). The first 4 letters of the colnames ("D15C") are group names. I need to average the columns by the group names (e.g. average D15C, D15C.1 and D15.2 to get D15C), so the final table will be consolidated to 16 columns from 49 columns.
ID D04C D04C.1 D08H D08H.1 D08H.2 D15C D15C.1 D15C.2 D15L D15L.1 D15L.2
1 1367452_at 11.11 10.93 11.85 10.94 10.87 10.73 10.62 10.85 10.73 10.77 10.52
2 1367453_at 9.65 9.94 9.78 9.68 9.67 9.86 9.71 9.82 9.74 9.71 9.76
3 1367454_at 10.19 10.36 9.68 10.07 10.08 10.35 10.26 10.32 10.27 10.19 10.47
(… 20000 rows)
I transposed and edited it to the following data frame in order to use aggregate:
ID 1367452_at 1367453_at 1367454_at ... ...
1 D04C 11.11 9.65 10.19
2 D04C 10.93 9.94 10.36
3 D08H 11.85 9.78 9.68
4 D08H 10.94 9.68 10.07
5 D08H 10.87 9.67 10.08
6 D15C 10.73 9.86 10.35
7 D15C 10.62 9.71 10.26
8 D15C 10.85 9.82 10.32
9 D15L 10.73 9.74 10.27
10 D15L 10.77 9.71 10.19
11 D15L 10.52 9.76 10.47
But, the following aggregate ("agg" is the data frame name) took 370 seconds to complete. The problem is that I have 100's of this kind of tables waiting......
agg <- aggregate(x = agg[, 2:ncol(agg)], by = list(ID = agg$ID), FUN = "mean", na.rm = T)
So I converted it to a data.table and run a data table method.
dt <- as.data.table(agg)
setkey(dt, ID)
dt2 <- dt[,lapply(list(dt[2:ncol(dt)]),mean),by = ID]
but got an error message after a few minutes:
Error: cannot allocate vector of size 144 Kb
In addition: Warning messages:
1: Reached total allocation of 1535Mb: see help(memory.size)
2: Reached total allocation of 1535Mb: see help(memory.size)
Not sure what is wrong. Can't use dt[1:5,1:5] to see the "head" part of dt, and head(dt) returns too many lines that ran through the roof I can't see the "head" either. Don't know what to do now.
I can list the ID's in one column (as in data.frame) or transpose the table and list the ID's in the first row (as in data.table). Either way, is there any faster way to aggregate the data? Very much appreciated!
Answer
This :
dt2 <- dt[,lapply(list(dt[2:ncol(dt)]),mean),by = ID]
should be just :
dt2 <- dt[, lapply(.SD,mean), by=ID]
The dt[2:ncol(dt)] was in fact taking a subset of rows.
One quick way to learn data.table syntax is to run example(data.table) at the prompt and work through the examples at the prompt. If you search that for "# applying through columns by group" you'll find exactly this example.
And to learn .SD, best way is to search ?data.table for the string ".SD" and then also there are some good questions and very detailed answers about .SD in this data.table tag which are returned by a search "[data.table] .SD".