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 think you have loaded your data without header=TRUE and what you have is a factor matrix, and so your generally good idea fails.

Answer 2

What about something like

lapply(unique(colnames(df)), function(x) rowMeans(df[,colnames(df) == x]))

Answer 3

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)

Answer 4

To clarify Jonathan Chang's answer... the blindly obvious thing you're missing is that you can just select the columns and issue the rowMeans command. That'll give vector of the means for each row. His command gets the row means for each group of unique column names and was exactly what I was going to write. With your sample data the result of his command is two lists.

rowMeans is also very fast.

To break it down, to get the means of all of your memory columns only is just

rowMeans(df[,colnames(df) == 'Memory']) #or from you example, rowMeans(df[,1:5])

It's the simplest complete correct answer, vote him up and mark him correct if you like it.

(BTW, I also liked Jo's recommendation to keep generally things as long data.)