data.table select rows where key is not equal to value

user_189035 0
Viewed 956

Consider this data.table:

DT <- data.table(mtcars)
setkey(DT, am, gear)

How do I select the rows for which am == 1 & gear != 4 using fast binary indexing?

e.g. I think that to select a row with say am == 1 & gear == 4 using fast binary indexing, I do:

DT[.c(1, 4)]
data.table r
1 Answers

Rewritten from scratch

https://cran.r-project.org/web/packages/data.table/vignettes/datatable-keys-fast-subset.html. Section 4) binary search vs vector scans states that, for example, the following 

flights[origin == "JFK" & dest == "MIA"]

will get , I quote, automatically optimized to use binary search.

However!, I've taken the liberty to modify their included example and compare selection with negation, as you're trying to, and the results weren't so good :/

N = 2e7L
DT = data.table(x = sample(letters, N, TRUE),
                y = sample(1000L, N, TRUE),
                z = sample(c(T, F), N, TRUE, probs=c(0.00001, 0.99999)),
                val = runif(N))
key(DT) # should be null
t1 <- system.time(ans1 <- DT[z == T & x == "g"])
t2 <- system.time(ans1 <- DT[z != F & x == "g"])
setkey(DT, x, z)
t3 <- system.time(ans1 <- DT[z == T & x == "g"])
t4 <- system.time(ans1 <- DT[z != F & x == "g"])

on my machine the results go as follows:

> t1
user  system elapsed 
0.464   0.001   0.124 
> t2
user  system elapsed 
0.169   0.000   0.169 
> t3
user  system elapsed 
0.002   0.000   0.002  
> t4
user  system elapsed 
0.168   0.000   0.167

It seems that if there's negation in the selections, not only do we not use the fast binary search, we also only utilise only one core (compare t1 and t2 - user and elapsed times). In this scenario, we only get the insane binary performance if the selects don't use negation and the keys are set. This is disappointing, and maybe even a bug?

user_79539240
Related