Subset rows corresponding to max value by group using data.table

Question

Assume I have a data.table containing some baseball players:

library(plyr) library(data.table)  bdt <- as.data.table(baseball) 

For each group (given by player 'id'), I want to select rows corresponding to the maximum number of games 'g'. This is straightforward in plyr:

ddply(baseball, "id", subset, g == max(g)) 

What's the equivalent code for data.table?

I tried:

setkey(bdt, "id")  bdt[g == max(g)]  # only one row bdt[g == max(g), by = id]  # Error: 'by' or 'keyby' is supplied but not j bdt[, .SD[g == max(g)]] # only one row 

This works:

bdt[, .SD[g == max(g)], by = id]  

But it's is only 30% faster than plyr, suggesting it's probably not idiomatic.

Answer 1

Here's the fast data.table way:

bdt[bdt[, .I[g == max(g)], by = id]$V1] 

This avoids constructing .SD, which is the bottleneck in your expressions.

edit: Actually, the main reason the OP is slow is not just that it has .SD in it, but the fact that it uses it in a particular way - by calling [.data.table, which at the moment has a huge overhead, so running it in a loop (when one does a by) accumulates a very large penalty.