R: aggregate columns of a data.frame

Question

I have a data.frame that looks like this

> head(df)
            Memory    Memory    Memory    Memory    Memory     Naive     Naive
10472501  6.075714  5.898929  6.644946  6.023901  6.332126  8.087944  7.520194
10509163  6.168941  6.495393  5.951124  6.052527  6.404401  7.152890  8.335509
10496091 10.125575  9.966211 10.075613 10.310952 10.090649 11.803949 11.274480
10427035  6.644921  6.658567  6.569745  6.499243  6.990852  8.010784  7.798154
10503695  8.379494  8.153917  8.246484  8.390747  8.346748  9.540236  9.091740
10451763 10.986717 11.233819 10.643245 10.230697 10.541396 12.248487 11.823138  

and I'd like to find the mean of the Memory columns and the mean of the Naive columns. The aggregate function aggregates rows. This data.frame could potentially have a large number of rows, and hence transposing then applying aggregate by the colnames of the original data.frame strikes me as bad, and is generally annoying:

> head(t(aggregate(t(df),list(colnames(df)), mean)))
         [,1]       [,2]      
Group.1  "Memory"   "Naive"   
10472501 "6.195123" "8.125439"
10509163 "6.214477" "7.733625"
10496091 "10.11380" "11.55348"
10427035 "6.672665" "8.266854"
10503695 "8.303478" "9.340436"

What's the blindingly obvious thing I'm missing?

Answer 1

I am a big advocate of reformatting data so that it's in a "long" format. The utility of the long format is especially evident when it comes to problems like this one. Fortunately, it's easy enough to reshape data like this into almost any format with the reshape package.

If I understood your question right, you want the mean of Memory and Naive for every row. For whatever reason, we need to make column names unique for reshape::melt().

colnames(df) <- paste(colnames(df), 1:ncol(df), sep = "_")

Then, you'll have to create an ID column. You could either do

df$ID <- 1:nrow(df)

or, if those rownames are meaningful

df$ID <- rownames(df)

Now, with the reshape package

library(reshape)
df.m <- melt(df, id = "ID")
df.m <- cbind(df.m, colsplit(df.m$variable, split = "_", names = c("Measure", "N")))
df.agg <- cast(df.m, ID ~ Measure, fun = mean)

df.agg should now look like your desired output snippit.

Or, if you want just the overall means across all the rows, Zack's suggestion will work. Something like

m <- colMeans(df)
tapply(m, colnames(df), mean)

You could get the same result, but formatted as a dataframe with

cast(df.m, .~variable, fun = mean)