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!
data.table
package? If you triedtranspose
andaggregate
then it's best to provide the exact commands you tried so that people can help. No idea what you mean by 'Data.table method'. There's probably some guidelines for asking good questions on Stack Overflow somewhere (but I can't them quickly). Try the R chat room to ask for advice. – Matt Dowle