11

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!

Matt Dowle
  • 58,872
  • 22
  • 166
  • 224
user1444754
  • 137
  • 1
  • 3
  • 10
  • First question eh? Welcome. Do you really mean data.table as in the `data.table` package? If you tried `transpose` and `aggregate` 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 Jun 13 '12 at 09:43
  • Sorry it was not clear. I have edited it to provide more details. Hope it's more understandable now. – user1444754 Jun 14 '12 at 14:07
  • Much better, thank you. This made it very quick to answer. – Matt Dowle Jun 14 '12 at 15:18

1 Answers1

12

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".

Matt Dowle
  • 58,872
  • 22
  • 166
  • 224
  • Thanks Matthew. Your code worked beautifully. I just started to look into .SD and run the examples this morning. By the way, is there any way to handle the data with group (i.e. ID) on the first row (i.e. transposed table)? The reason I ask is that R seems to handle longer [30,000, 3,000], not wider [3,000, 30,000], tables better. for example, it takes 1 second to write.table that is long, but 5 minutes to save the same sized transposed wide table. So I have to always transpose the table before saving, and transpose the table before operating on it. Is this "my" unique problem? – user1444754 Jun 14 '12 at 16:18
  • 1
    @user1444754 Glad it works now. Don't forget to vote for my answer and accept it (tick it) please. So that others know it is resolved. On this comment it sounds appropriate for a new question. Best to provide a reprodicible example that a responder can paste into a fresh R session to see what you mean, quickly. – Matt Dowle Jun 14 '12 at 16:24