Subsetting data.table by 2nd column only of a 2 column key, using binary search not vector scan
data.table, r
Solution
Yes, you can pass all values to the first key value and subset with the specific value for the second key.
DT[J(unique(x), 25), nomatch=0]
If you need to subset by more than one value in the second key (e.g. the equivalent of `DT[y %in% 25:24]`), a more general solution is to use `CJ`
DT[CJ(unique(x), 25:24), nomatch=0]
Note that `CJ` by default sorts the columns and sets key to all the columns, which means the result would be sorted as well. If that's not desirable, you should use `sorted=FALSE`
DT[CJ(unique(x), 25:24, sorted=FALSE), nomatch=0]
There's also a feature request to add secondary keys to `data.table` in future. I believe the plan is to add a new function `set2key`.
FR#1007 Build in secondary keys
There is also `merge`, which has a method for `data.table`. It builds the secondary key inside it for you, so should be faster than base merge. See `?merge.data.table`.
Problem
I recently discovered binary search in `data.table`. If the table is sorted on multiple keys it possible to search on the 2nd key only ? ``` DT = data.table(x=sample(letters,1e7,T),y=sample(1:25,1e7,T),rnorm(1e7)) setkey(DT,x,y) #R> DT[J('x')] # x y V3 # 1: x 1 0.89109 # 2: x 1 -2.01457 # --- #384922: x 25 0.09676 #384923: x 25 0.25168 #R> DT[J('x',3)] # x y V3 # 1: x 3 -0.88165 # 2: x 3 1.51028 # --- #15383: x 3 -1.62218 #15384: x 3 -0.63601 ``` EDIT: thanks to @Arun ``` R> system.time(DT[J(unique(x), 25)]) user system elapsed 0.220 0.068 0.288 R> system.time(DT[y==25]) user system elapsed 0.268 0.092 0.359 ```