data.frame: create column by applying a function to groups of rows
I have a data frame consisting of results from multiple runs of an experiment, each of which serves as a log, with its own ascending counter. I'd like to add another column to the data frame that has the maximum value of iteration for each distinct value of experiment.num in the sample below:
df <- data.frame( iteration = rep(1:5,5), experiment.num = c(rep(1,5),rep(2,5),rep(3,5),rep(4,5),rep(5,5)), some.val=42, another.val=12 )
In this example, the extra column would look like this (as all the subsets have the same maximum for iteration):
df$max <- rep(5,25)
The naive solution I currently use is:
df$max <- sapply(df$experiment.num,function(exp.num) max(df$iteration[df$experiment.num == exp.num]))
I've also used sapply(unique(df$experiment.num), function(n) c(n,max(df$iteration[df$experiment.num==n]))) to build another frame which I can then merge with the original, but both of these approaches seem more complicated than necessary.
The experiment.num column is a factor, so I think I might be able to exploit that to avoid iteratively doing this naive subsetting for all rows.
Is there a better way to get a column of maximum values for subsets of a data.frame?
ddply(df, .(experiment.num), transform, max = max(iteration))
Using ave in base R:
df$i_max <- with(df, ave(iteration, experiment.num, FUN=max))
Here's a way in base R:
within(df[order(df$experiment.num), ], max <- rep(tapply(iteration, experiment.num, max), rle(experiment.num)$lengths))
I think you can use data.table:
install.packages("data.table") library("data.table") dt <- data.table(df) #make your data frame into a data table) dt[, pgIndexBY := .BY, by = list(experiment.num)] #this will add a new column to your data table called pgIndexBY