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.

Conveyor

Wow, that is slow, but if you use "year" in place of ".SD"... I'm getting .01, 1.58, 2.39 user time for year, .SD, plyr, respectively. – Subjacent

@Subjacent but I want the whole data frame, not just the year. I'll clarify the question. – Conveyor

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.

Debug

+1 I'm betting that Hadley wants to do this somewhat programmatically, in which case he'd want to use this syntax, bdt[bdt[, .I[g == max(g)], by = id][,V1]] right? – Losing

@Losing I'm constructing the call manually, so it doesn't really matter – Conveyor

Eventually the original approach will be optimized. See FR 2330 Optimize .SD[i] query to keep the elegance but make it faster unchanged. – Chiromancy

That issue link since moved from R-Forge to GitHub here #613 – Redress

If I add verbose = TRUE to the inner frame, I see GForce FALSE, yet it's still faster than something like bdt[bdt[, .(g=max(g)), by=id], on=c("id","g")], though I don't know if that would always be the case. – Mllly