[R] data frame select max group by like function

2010-03-09 Thread Tan, Richard
Hi, I have a data frame with 3 columns: ID, year and score. How can I select for each unique ID, the year that has the max score? For example, for data frame ID, year, score tom, 1995, 88 rick, 1994, 90 mary, 2000, 97 tom, 1998, 60 mary, 1998,100 I shall have ID, year, score tom, 1995, 88

Re: [R] data frame select max group by like function

2010-03-09 Thread Henrique Dallazuanna
Try this: library(sqldf) sqldf(SELECT ID, Year, MAX(score) FROM DF GROUP BY ID) Or do.call(rbind, lapply(split(DF - DF[order(DF$score),], DF$ID), tail, 1)) On Tue, Mar 9, 2010 at 3:35 PM, Tan, Richard r...@panagora.com wrote: Hi, I have a data frame with 3 columns: ID, year and score.  How

Re: [R] data frame select max group by like function

2010-03-09 Thread Ista Zahn
Hi Richard, There are probably better ways, but here is one approach: Dat - read.table(textConnection(ID, year, score tom, 1995, 88 rick, 1994, 90 mary, 2000, 97 tom, 1998, 60 mary, 1998,100), header=TRUE, sep=,) MaxScore - aggregate(Dat$score, list(Dat$ID), max) names(MaxScore) - c(ID, score)

Re: [R] data frame select max group by like function

2010-03-09 Thread Peter Ehlers
I find ddply() in package plyr handy for this sort of thing: library(plyr) f - function(x) x[which.max( x[[score]] ), ] ## x will be a subset of Dat according to ID ddply(Dat, ID, f) -Peter Ehlers On 2010-03-09 11:59, Ista Zahn wrote: Hi Richard, There are probably better ways, but

Re: [R] data frame select max group by like function

2010-03-09 Thread Bert Gunter
Genentech Nonclinical Biostatistics -Original Message- From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org] On Behalf Of Ista Zahn Sent: Tuesday, March 09, 2010 11:00 AM To: Tan, Richard Cc: r-help@r-project.org Subject: Re: [R] data frame select max group by like function

Re: [R] data frame select max group by like function

2010-03-09 Thread Phil Spector
Yet another way to do this with base R: dat = read.csv(textConnection('ID, year, score + tom, 1995, 88 + rick, 1994, 90 + mary, 2000, 97 + tom, 1998, 60 + mary, 1998,100')) do.call(rbind,lapply(split(dat,dat$ID),function(x)x[which.max(x$score),])) ID year score mary mary 1998 100

Re: [R] data frame select max group by like function

2010-03-09 Thread Gabor Grothendieck
Try this. The aggregate call gives a data frame with the ID and max score. Then we merge that back with the original data frame so that we pick up the year too: merge(DF, aggregate(DF['score'], DF['ID'], max)) On Tue, Mar 9, 2010 at 1:35 PM, Tan, Richard r...@panagora.com wrote: Hi, I have a

Re: [R] data frame select max group by like function

2010-03-09 Thread William Dunlap
-project.org Subject: Re: [R] data frame select max group by like function Yet another way to do this with base R: dat = read.csv(textConnection('ID, year, score + tom, 1995, 88 + rick, 1994, 90 + mary, 2000, 97 + tom, 1998, 60 + mary, 1998,100')) do.call(rbind,lapply(split(dat,dat$ID

Re: [R] data frame select max group by like function

2010-03-09 Thread Tan, Richard
Thanks all for the help! -Original Message- From: William Dunlap [mailto:wdun...@tibco.com] Sent: Tuesday, March 09, 2010 5:58 PM To: Phil Spector; Tan, Richard Cc: r-help@r-project.org Subject: RE: [R] data frame select max group by like function And yet another way is isLastInRun