Aggregate a dataframe on a given column and display another column

Question

A base R solution is to combine the output of aggregate() with a merge() step. I find the formula interface to aggregate() a little more useful than the standard interface, partly because the names on the output are nicer, so I'll use that:

The aggregate() step is

maxs <- aggregate(Score ~ Group, data = dat, FUN = max)

and the merge() step is simply

merge(maxs, dat)

This gives us the desired output:

R> maxs <- aggregate(Score ~ Group, data = dat, FUN = max)
R> merge(maxs, dat)
  Group Score Info
1     1     3    c
2     2     4    d

You could, of course, stick this into a one-liner (the intermediary step was more for exposition):

merge(aggregate(Score ~ Group, data = dat, FUN = max), dat)

The main reason I used the formula interface is that it returns a data frame with the correct names for the merge step; these are the names of the columns from the original data set dat. We need to have the output of aggregate() have the correct names so that merge() knows which columns in the original and aggregated data frames match.

The standard interface gives odd names, whichever way you call it:

R> aggregate(dat$Score, list(dat$Group), max)
  Group.1 x
1       1 3
2       2 4
R> with(dat, aggregate(Score, list(Group), max))
  Group.1 x
1       1 3
2       2 4

We can use merge() on those outputs, but we need to do more work telling R which columns match up.

First, you split the data using split:

split(z,z$Group)

Than, for each chunk, select the row with max Score:

lapply(split(z,z$Group),function(chunk) chunk[which.max(chunk$Score),])

Finally reduce back to a data.frame do.calling rbind:

do.call(rbind,lapply(split(z,z$Group),function(chunk) chunk[which.max(chunk$Score),]))

Result:

  Group Score Info
1     1     3    c
2     2     4    d

One line, no magic spells, fast, result has good names =)

Here is a solution using the plyr package.

The following line of code essentially tells ddply to first group your data by Group, and then within each group returns a subset where the Score equals the maximum score in that group.

library(plyr)
ddply(data, .(Group), function(x)x[x$Score==max(x$Score), ])

  Group Score Info
1     1     3    c
2     2     4    d

And, as @SachaEpskamp points out, this can be further simplified to:

ddply(df, .(Group), function(x)x[which.max(x$Score), ])

(which also has the advantage that which.max will return multiple max lines, if there are any).

The plyr package can be used for this. With the ddply() function you can split a data frame on one or more columns and apply a function and return a data frame, then with the summarize() function you can use the columns of the splitted data frame as variables to make the new data frame/;

dat <- read.table(textConnection('Group Score Info
1     1     1    a
2     1     2    b
3     1     3    c
4     2     4    d
5     2     3    e
6     2     1    f'))

library("plyr")

ddply(dat,.(Group),summarize,
    Max = max(Score),
    Info = Info[which.max(Score)])
  Group Max Info
1     1   3    c
2     2   4    d

A late answer, but and approach using data.table

library(data.table)
DT <- data.table(dat)

DT[, .SD[which.max(Score),], by = Group]

Or, if it is possible to have more than one equally highest score

DT[, .SD[which(Score == max(Score)),], by = Group]

Noting that (from ?data.table

.SD is a data.table containing the Subset of x's Data for each group, excluding the group column(s)

To add to Gavin's answer: prior to the merge, it is possible to get aggregate to use proper names when not using the formula interface:

aggregate(data[,"score", drop=F], list(group=data$group), mean)