Stats_Dunce Stats_Dunce - 1 month ago 7
R Question

R first and last observations in data set

I have the following code:

df1<-data.frame(rep(sample(1:100,5),4),(sample(seq(as.Date('2010/01/01'), as.Date('2016/01/01'), by="day"), 20)),rep("A",20))
names(df1)<-c("ID","Date","Letter")
df2<-data.frame(df1$ID,(sample(seq(as.Date('2010/01/01'), as.Date('2016/01/01'), by="day"), 20)),rep("B",20))
names(df2)<-c("ID","Date","Letter")
df3<-data.frame(df1$ID,(sample(seq(as.Date('2010/01/01'), as.Date('2016/01/01'), by="day"), 20)),rep("C",20))
names(df3)<-c("ID","Date","Letter")
data<-rbind(df1,df2,df3)


What I am trying to do is find the first date and the last date for each letter and ID

Therefore the outcome would be a four column dataframe with the first column the ID, the second column the Letter, the third column the first occurrence and the fourth column the last occurrence.

Any assistance would be appreciated

Answer

Try this:

MIN <- aggregate(Date~ID+Letter, data, min)
MAX <- aggregate(Date~ID+Letter, data, max)
d <- merge(MIN, MAX, by = c("ID", "Letter"))