R help - Jan 2012 - Extracting rows with latest date from a data frame

Hi,

I have a list of unique patient IDs which I want to run against a dataframe
of unique patient IDs with dates. There are multiple date instances against
unique IDs. The end result I wish is that I have a list of IDs (which are
unique) with the latest date. I have begun with using test data (but I
haven't got very far!)

#Create test data
#Data frame creation
df1<-data.frame(uID=c("1","2"))
df2<-data.frame(uID2=c("1","1","1","2","2","2"),Date=c(as.Date("20/12/2010",
format="%d/%m/%Y"),
as.Date("12/05/2011",
format="%d/%m/%Y"),as.Date("12/07/2011",
format="%d/%m/%Y"),
as.Date("12/07/2010",format="%d/%m/%Y"),as.Date("12/05/2009",format="%d/%m/%Y"),
as.Date("20/10/2012",format="%d/%m/%Y")))

for (i in uID) {
#For all the unique patient IDs
                df3<-data.frame(i)
#Create a one record data frame of record
                m1<-merge(df3,df2,by.x="i", by.y="uID2")
#Merge with list of IDs and dates of event
                                                for (j in m1) {
                                               #maybe an if statement in
here but I?m not sure how to seperate the dates out and then create a new
dataframe with the IDs and latest date??
                                }
                }

I am completely new to R so R vocabulary is limited at the moment. I'm not
sure if i'm on the right tracted here. For example I not sure if I need
dataframe of uniqueIDs (df1). Is there a way in a For loop  that you can
pinpoint all date instances for a given ID and then select the latest and
add to new dataframe?

Your help is much appreciated.
D

--
View this message in context:
http://r.789695.n4.nabble.com/Extracting-rows-with-latest-date-from-a-data-frame-tp4305651p4305651.html
Sent from the R help mailing list archive at Nabble.com.

Hi
 
> Hi,
> 
> I have a list of unique patient IDs which I want to run against a 
dataframe
> of unique patient IDs with dates. There are multiple date instances 
against
> unique IDs. The end result I wish is that I have a list of IDs (which 
are
> unique) with the latest date. I have begun with using test data (but I
> haven't got very far!)
> 
> #Create test data
> #Data frame creation
> df1<-data.frame(uID=c("1","2"))
> 
df2<-data.frame(uID2=c("1","1","1","2","2","2"),Date=c(as.Date("20/12/2010",
> format="%d/%m/%Y"),
> as.Date("12/05/2011",
format="%d/%m/%Y"),as.Date("12/07/2011",
> format="%d/%m/%Y"),
> 
as.Date("12/07/2010",format="%d/%m/%Y"),as.Date("12/05/2009",format="%d/%m/%Y"),
> as.Date("20/10/2012",format="%d/%m/%Y")))
> 
> for (i in uID) {  
> #For all the unique patient IDs
>                 df3<-data.frame(i)  
> #Create a one record data frame of record
>                 m1<-merge(df3,df2,by.x="i",
by.y="uID2")
> #Merge with list of IDs and dates of event
>                                                 for (j in m1) {
>                                                #maybe an if statement in
> here but I?m not sure how to seperate the dates out and then create a 
new
> dataframe with the IDs and latest date??
>                                 }
>                 }
> 
> I am completely new to R so R vocabulary is limited at the moment. I'm 
not
> sure if i'm on the right tracted here. For example I not sure if I need
> dataframe of uniqueIDs (df1). Is there a way in a For loop  that you can
> pinpoint all date instances for a given ID and then select the latest 
and
> add to new dataframe?
latest <- aggregate(df2$Date, list(df2$uID2), max)

gives you newest date from df2 for each id. After that you can select rows 
from df2.

df2[df2$Date %in% latest$x,]
  uID2       Date
3    1 2011-07-12
6    2 2012-10-20

Or you can use ?merge

Regards
Petr
>
> 
> Your help is much appreciated.
> D
> 
> --
> View this message in context: http://r.789695.n4.nabble.com/Extracting-
> rows-with-latest-date-from-a-data-frame-tp4305651p4305651.html
> Sent from the R help mailing list archive at Nabble.com.
> 
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide 
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

Thanks Petr, one line of code -  awesome.

--
View this message in context:
http://r.789695.n4.nabble.com/Extracting-rows-with-latest-date-from-a-data-frame-tp4305651p4319303.html
Sent from the R help mailing list archive at Nabble.com.