select maximum row value by group

Question

I've been trying to do this with my data by looking at other posts, but I keep getting an error. My data new looks like this:

id  year    name    gdp
1   1980    Jamie   45
1   1981    Jamie   60
1   1982    Jamie   70
2   1990    Kate    40
2   1991    Kate    25
2   1992    Kate    67
3   1994    Joe     35
3   1995    Joe     78
3   1996    Joe     90

I want to select the row with the highest year value by id. So the wanted output is:

id  year    name    gdp
1   1982    Jamie   70
2   1992    Kate    67
3   1996    Joe     90

From Selecting Rows which contain daily max value in R I tried the following but did not work

ddply(new,~id,function(x){x[which.max(new$year),]})

I've also tried

tapply(new$year, new$id, max)

But this didn't give me the wanted output.

Any suggestions would really help!

Answer 1

Another option that scales well for large tables is using data.table.

DT <- read.table(text = "id  year    name    gdp
                          1   1980    Jamie   45
                          1   1981    Jamie   60
                          1   1982    Jamie   70
                          2   1990    Kate    40
                          2   1991    Kate    25
                          2   1992    Kate    67
                          3   1994    Joe     35
                          3   1995    Joe     78
                          3   1996    Joe     90",
                 header = TRUE)

require("data.table")
DT <- as.data.table(DT)

setkey(DT,id,year)
res = DT[,j=list(year=year[which.max(gdp)]),by=id]
res

setkey(res,id,year)
DT[res]
# id year  name gdp
# 1:  1 1982 Jamie  70
# 2:  2 1992  Kate  67
# 3:  3 1996   Joe  90