R help - Feb 2005 - aggregation with extra columns

R People,

Thanks for your help on my recent questions, Excel is never going to disappear
from my office but with graphics from lattice package and some other stuff in R
I have been able to add some value.

I have a problem I haven't been able to figure out with aggregation, I
mentioned it earlier but didn't state it very clearly.

Basically I have many "defect events" and I want to grab the most
recent event for each defect number:

eg:
"date" 	"defectnum" "state"
2004-12-1	10		create
2004-12-2	11		create
2004-12-4	10		close
2004-12-7	11		fix

to:
"date" 	"defectnum" "state"
2004-12-4	10		close
2004-12-7	11		fix

Now with aggregate I can get the rows I want but not with the state
"attached":

aggregate(list(date=ev$date), by=list(defectnum=ev$defectnum), max)

Gives me the rows I want but I have lost the "state".  I have tried
doing a merge afterwards but now I realise why they warned me avoid using dates
as database keys.

What would be handy is somehow getting back the index vector from the aggregate
function.  I realize in the general case this wouldn't work for aggregate
but in the case of min/max the result is a specific record.

Someone earlier mentioned some tricks with sort but I haven't been able to
make that get to where I want.

I think Gabor gave you the answer, even if you didn't see it

x <- rep(c("02/27/92", "02/27/92", "01/14/92",
"02/28/92", "02/01/92"),each = 5)
z <- data.frame(Date = strptime(x, "%m/%d/%y"))
z$Value <- trunc(runif(25) * 5) + 1
z$State <-
c("A","B","C","D","E")[z$Value]
z <- z[order(z$Date),]
z
z[aggregate(1:nrow(z), list(z$State), max)$x,]


Tom

Even if it's not quite right, the technique is there.


> -----Original Message-----
> From: Paul Sorenson [mailto:Paul.Sorenson at vision-bio.com]
> Sent: Wednesday, 2 February 2005 9:18 AM
> To: r-help at stat.math.ethz.ch
> Subject: [R] aggregation with extra columns
> 
> 
> R People,
> 
> Thanks for your help on my recent questions, Excel is never 
> going to disappear from my office but with graphics from 
> lattice package and some other stuff in R I have been able to 
> add some value.
> 
> I have a problem I haven't been able to figure out with 
> aggregation, I mentioned it earlier but didn't state it very clearly.
> 
> Basically I have many "defect events" and I want to grab the 
> most recent event for each defect number:
> 
> eg:
> "date" 	"defectnum" "state"
> 2004-12-1	10		create
> 2004-12-2	11		create
> 2004-12-4	10		close
> 2004-12-7	11		fix
> 
> to:
> "date" 	"defectnum" "state"
> 2004-12-4	10		close
> 2004-12-7	11		fix
> 
> Now with aggregate I can get the rows I want but not with the 
> state "attached":
> 
> aggregate(list(date=ev$date), by=list(defectnum=ev$defectnum), max)
> 
> Gives me the rows I want but I have lost the "state".  I have 
> tried doing a merge afterwards but now I realise why they 
> warned me avoid using dates as database keys.
> 
> What would be handy is somehow getting back the index vector 
> from the aggregate function.  I realize in the general case 
> this wouldn't work for aggregate but in the case of min/max 
> the result is a specific record.
> 
> Someone earlier mentioned some tricks with sort but I haven't 
> been able to make that get to where I want.
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
>