Group by multiple columns and sum other multiple columns

Question

I have a data frame with about 200 columns, out of them I want to group the table by first 10 or so which are factors and sum the rest of the columns.

I have list of all the column names which I want to group by and the list of all the cols which I want to aggregate.

The output format that I am looking for needs to be the same dataframe with same number of cols, just grouped together.

Is there a solution using packages data.table, plyr or any other?

Answer 1

The data.table way is :

DT[, lapply(.SD,sum), by=list(col1,col2,col3,...)] 

or

DT[, lapply(.SD,sum), by=colnames(DT)[1:10]] 

where .SD is the (S)ubset of (D)ata excluding group columns. (Aside: If you need to refer to group columns generically, they are in .BY.)

Answer 2

In base R this would be...

aggregate( as.matrix(df[,11:200]), as.list(df[,1:10]), FUN = sum) 

EDIT: The aggregate function has come a long way since I wrote this. None of the casting above is necessary.

aggregate( df[,11:200], df[,1:10], FUN = sum ) 

And there are a variety of ways to write this. Assuming the first 10 columns are named a1 through a10 I like the following, even though it is verbose.

aggregate(. ~ a1 + a2 + a3 + a4 + a5 + a6 + a7 + a8 + a9 + a10, data = dat, FUN = sum) 

(You could use paste to construct the formula and use formula)