如何在data.table R中的子组中排序数据 [英] How to order data within subgroups in data.table R
问题描述
请考虑以下内容:
DT = data.table(a = sample(1:2),b = sample(1:1000) ,20))
如何显示b,并说出 n 的最大值,每个a?
How to display b, say the n highest values, by each a?
我被困在 DT [,b,by = a] [order(a,-b)]
。
谢谢!
推荐答案
最多优雅的是:
DT[order(-b),head(b,5),by=a]
就纯性能而言:
DT[order(-b), indx := seq_len(.N), "a"][indx <= 5][,indx:=NULL][]
或@Frank建议的那个:
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 ...
通过 cust_code 列,uniqueN等于100:
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
如果有更多的组( prod_code 列,uniqueN等于50000 ),那么我们可以看到对性能的影响:
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
2015年11月9日更新:
使用今天的Arun提交 e615532 head
和 tail
应该在后台进行优化。
With today's Arun commit e615532 the head
and tail
should be optimized under the hood.
这篇关于如何在data.table R中的子组中排序数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!