在 R 中的 data.table 中选择 NA [英] Select NA in a data.table in R
问题描述
如何选择数据表中主键中缺失值的所有行.
How do I select all the rows that have a missing value in the primary key in a data table.
DT = data.table(x=rep(c("a","b",NA),each=3), y=c(1,3,6), v=1:9)
setkey(DT,x)
选择特定值很容易
DT["a",]
选择缺失值似乎需要矢量搜索.不能使用二分查找.我说得对吗?
Selecting for the missing values seems to require a vector search. One cannot use binary search. Am I correct?
DT[NA,]# does not work
DT[is.na(x),] #does work
推荐答案
幸运的是,DT[is.na(x),]
几乎和 (eg) DT[" 一样快a",]
,所以在实践中,这可能并不重要:
Fortunately, DT[is.na(x),]
is nearly as fast as (e.g.) DT["a",]
, so in practice, this may not really matter much:
library(data.table)
library(rbenchmark)
DT = data.table(x=rep(c("a","b",NA),each=3e6), y=c(1,3,6), v=1:9)
setkey(DT,x)
benchmark(DT["a",],
DT[is.na(x),],
replications=20)
# test replications elapsed relative user.self sys.self user.child
# 1 DT["a", ] 20 9.18 1.000 7.31 1.83 NA
# 2 DT[is.na(x), ] 20 10.55 1.149 8.69 1.85 NA
===
来自马修的补充(不适合评论):
Addition from Matthew (won't fit in comment) :
不过,上面的数据有 3 个非常大的组.所以这里二分查找的速度优势主要在于创建大子集的时间(复制了 1/3 的数据).
The data above has 3 very large groups, though. So the speed advantage of binary search is dominated here by the time to create the large subset (1/3 of the data is copied).
benchmark(DT["a",], # repeat select of large subset on my netbook
DT[is.na(x),],
replications=3)
test replications elapsed relative user.self sys.self
DT["a", ] 3 2.406 1.000 2.357 0.044
DT[is.na(x), ] 3 3.876 1.611 3.812 0.056
benchmark(DT["a",which=TRUE], # isolate search time
DT[is.na(x),which=TRUE],
replications=3)
test replications elapsed relative user.self sys.self
DT["a", which = TRUE] 3 0.492 1.000 0.492 0.000
DT[is.na(x), which = TRUE] 3 2.941 5.978 2.932 0.004
随着返回子集的大小减小(例如添加更多组),差异变得明显.单列上的矢量扫描还不错,但在 2 列或更多列上它会迅速降级.
As the size of the subset returned decreases (e.g. adding more groups), the difference becomes apparent. Vector scans on a single column aren't too bad, but on 2 or more columns it quickly degrades.
也许 NA 应该可以加入.不过,我似乎记得一个问题.以下是从 FR#1043 允许或禁止密钥中的 NA? 链接的一些历史记录.它在那里提到 NA_integer_
在内部是一个负整数.这会导致基数/计数排序 (iirc) 跳闸,导致 setkey
变慢.但它在重新访问的列表中.
Maybe NAs should be joinable to. I seem to remember a gotcha with that, though. Here's some history linked from FR#1043 Allow or disallow NA in keys?. It mentions there that NA_integer_
is internally a negative integer. That trips up radix/counting sort (iirc) resulting in setkey
going slower. But it's on the list to revisit.
这篇关于在 R 中的 data.table 中选择 NA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!