R: aggregate columns of a data.frame

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)

What about something like

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



df <- do.call(cbind.data.frame, l)

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

Tags:

R

Dataframe