Performing functions on grouped rows in an R dataframe

Question

I have a large dataframe where multiple rows are repeated measurements for a single ID. I want to return the rows with the maximum value of a column for each individual. Essentially performing a group.by() function as per SQL.

Dataframe (for illustrative purposes)

ID lac pO2
M1 1   80
M1 4   80
M2 2   70
M2 3   70
M3 3   75
M3 5   75

I want to call max(lac) and return the following results.

ID lac pO2
M1 4   80
M2 3   70
M3 5   75

I've had a look around and thought that the by() function might be useful, but haven't had any joy (code below).

newdf <- by(df, df$ID, max(df$lac))

Error in FUN(X[[1L]], ...) : could not find function "FUN"

I also looked at tapply but this doesn't work because I'm using a dataframe rather than a vector.

newdf <- tapply(df, df$ID, max)

Error: "arguments must have same length"

I've looked at similar answers, but these haven't helped. I'd appreciate some input from people more experienced than I!

Edit

Having dug a little deeper I've uncovered this question which suggests the plyr package might be useful.

Answer 1

Try this:

> by(mtcars, mtcars$cyl, max)
mtcars$cyl: 4
[1] 146.7
--------------------------------------------------------------------------------------- 
mtcars$cyl: 6
[1] 258
--------------------------------------------------------------------------------------- 
mtcars$cyl: 8
[1] 472

Alternatively use plyr:

> require(plyr)
Loading required package: plyr
> ddply(mtcars, .(cyl), max)
  cyl    V1
1   4 146.7
2   6 258.0
3   8 472.0

Answer 2

For large data set try data.table (assuming df is your data set)

library(data.table)
setDT(df)[, .SD[which.max(lac)], by = ID]

##    ID lac pO2
## 1: M1   4  80
## 2: M2   3  70
## 3: M3   5  75

Answer 3

Found a solution using plyr as discussed in update.

Code used was:

max_lac <- ddply(.data=df, .variables=.(ID), function(x) 
+ x[which(x$lac == max(x$lac)), ])

Answer 4

Here's a dplyr alternative in case you're processing large data sets:

library(dplyr)

df %>% group_by(ID) %>% filter(lac == max(lac))

#Source: local data frame [3 x 3]
#Groups: ID
#
#  ID lac pO2
#1 M1   4  80
#2 M2   3  70
#3 M3   5  75

Note that in case of multiple rows with maximas in the same group of ID, this function will return all rows containing the maximum value in lac, whereas functions using which.max(.) will only return the first row containing the maximum (per group).

If you only want to return the first max per group, you can use for example:

df %>% group_by(ID) %>% filter(1:n() == which.max(lac))

or

df %>% group_by(ID) %>% filter(lac == max(lac)) %>% do(head(.,1))

Answer 5

(if you look at ddply and which.max) Can it be?:

ddply(df,.ID,function(x){x[which.max(x$lac)),]}