Subset rows corresponding to max value by group using data.tableSubset rows corresponding to max value by group using data.table - Solution Checker - solutionschecker.com - Find the solution for any programming question. We as a solution checker will focus on finding the fastest possible solution for developers. Main topics like coding, learning.
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.
Solution 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.
