r - Find rows in dataframe with maximum values grouped by values in another column -

i solve problem in r without using sql.

how can select rows max(column value), distinct column in sql?

sure, use sqldf it, there must cool apply method in r it, too?

setup data first read in data:

lines <- "id  home  datetime  player   resource 1   10   04/03/2009  john    399  2   11   04/03/2009  juliet  244 5   12   04/03/2009  borat   555 3   10   03/03/2009  john    300 4   11   03/03/2009  juliet  200 6   12   03/03/2009  borat   500 7   13   24/12/2008  borat   600 8   13   01/01/2009  borat   700 " df <- read.table(text = lines, header = true) df$datetime <- as.date(df$datetime, format = "%d/%m/%y") 

1) base - by there many ways process using various packages here show base solution first:

> do.call("rbind", by(df, df$home, function(x) x[which.max(x$datetime), ]))    id home   datetime player resource 10  1   10 2009-03-04   john      399 11  2   11 2009-03-04 juliet      244 12  5   12 2009-03-04  borat      555 13  8   13 2009-01-01  borat      700 

1a) base - ave , variation (also using base of r):

fun <- function(x) which.max(x) == seq_along(x) is.max <- ave(xtfrm(df$datetime), df$home, fun = fun) == 1 df[is.max, ] 

2) sqldf , here using sqldf in case:

> library(sqldf) > sqldf("select id, home, max(datetime) datetime, player, resource  +        df  +        group home")   id home   datetime player resource 1  1   10 2009-03-04   john      399 2  2   11 2009-03-04 juliet      244 3  5   12 2009-03-04  borat      555 4  8   13 2009-01-01  borat      700