15

Consider the following:

DT = data.table(a=sample(1:2), b=sample(1:1000,20))

How to display b, say the n highest values, by each a?

I am stucked in DT[,b,by=a][order(a,-b)].

Thanks!

eddi
  • 49,088
  • 6
  • 104
  • 155
unmark1
  • 322
  • 3
  • 13

1 Answers1

24

The most elegant would be:

DT[order(-b),head(b,5),by=a]

In terms of pure performance:

DT[order(-b), indx := seq_len(.N), "a"][indx <= 5][,indx:=NULL][]

Or the one suggested by @Frank:

DT[DT[order(-b),.I[1:.N<=5],"a"]$V1]

Below the benchmark of all three above:

# devtools::install_github("jangorecki/dwtools")
library(dwtools) # to populate complex dataset
N <- 5e6
DT <- dw.populate(N, scenario="fact")
str(DT)
#Classes ‘data.table’ and 'data.frame': 5000000 obs. of  8 variables:
# $ cust_code: chr  "id010" "id076" "id024" "id081" ...
# $ prod_code: int  8234 5689 31198 35479 39140 37589 8184 39489 35266 3596 ...
# $ geog_code: chr  "OH" "NH" "TN" "MI" ...
# $ time_code: Date, format: "2012-03-11" "2014-02-10" "2012-11-05" "2013-01-30" ...
# $ curr_code: chr  "XRP" "HRK" "CAD" "BRL" ...
# $ amount   : num  486 382 695 470 749 ...
# $ value    : num  193454 33694 351418 84888 20673 ...

By cust_code column, uniqueN equal to 100: 

system.time(DT[order(-time_code),head(.SD,5),"cust_code"])
#   user  system elapsed 
#  1.804   0.084   1.890 
system.time(DT[order(-time_code), indx := seq_len(.N),"cust_code"][indx <= 5][,indx:=NULL][])
#   user  system elapsed 
#  1.414   0.092   1.508 
system.time(DT[DT[order(-time_code),.I[1:.N<=5],"cust_code"]$V1])
#   user  system elapsed 
#  1.405   0.096   1.502

If there are much more groups (prod_code column, uniqueN equal to 50000), then we can see the impact on the performance:

system.time(DT[order(time_code),head(.SD,5),"prod_code"])
#   user  system elapsed 
# 10.177   0.109  10.322
system.time(DT[order(time_code), indx := seq_len(.N),"prod_code"][indx <= 5][,indx:=NULL][])
#   user  system elapsed 
#  1.555   0.099   1.665 
system.time(DT[DT[order(time_code),.I[1:.N<=5],"prod_code"]$V1])
#   user  system elapsed 
#  1.697   0.064   1.764

Update on 2015-11-09:

With today's Arun commit e615532 the head and tail should be optimized under the hood.

jangorecki
  • 16,384
  • 4
  • 79
  • 160
  • 3
    I don't know why the second method should be faster. Is making a copy of `b` for each `a` (to pass to `head`) costly relative to making `1:.N`? I see that the end result still isn't "by each `a`" per the OP... If there is a speedup to be had, I bet this would also do it: `DT[DT[order(-b),.I[1:.N<=5],"a"]$V1]` ... copied from http://stackoverflow.com/questions/16573995/subset-by-group-with-data-table/16574176#16574176 – Frank Feb 24 '15 at 00:00
  • Using @Frank's substitute for `head` I think will speed up the first version significantly as it will avoid the various checks `head` makes. – eddi Feb 24 '15 at 00:14
  • 1
    @Frank, I've added benchmark, it seems there is not big difference between all three of the solutions. Anyway quite a lot depends on the dataset. – jangorecki Mar 01 '15 at 11:00
  • 1
    update: just added benchmark for more groups and now it is clear the first solution is much slower. – jangorecki Mar 01 '15 at 11:08