28

Let's say I have a data.table and I want to select all the rows where the variable x has a value of b. That is easy

library(data.table)
DT <- data.table(x=rep(c("a","b","c"),each=3), y=c(1,3,6), v=1:9)
setkey(DT,x)               # set a 1-column key
DT["b"]

By the way, it appears that one has to set a key, if the key is not set to x then this does not work. By the way what would happen if I set two columns as keys?

Anyway, moving along, lets say that I want to select all the rows where the variable x was a or b

DT["b"|"a"]

does not work

But the following works

DT[x=="a"|x=="b"]

But that uses vector scanning a la data frames. It does not use the binary search. I guess for smaller data sets it will not matter.

Is that what I should do or am I ignorant of data.table syntax?

And one more thing. Are there any examples of more complex Boolean multi-variable selection (or subset) procedures with data.table?

I know I could always revert to using the subset() function since a data.table will behave as a data.frame if it must.

Farrel
  • 10,244
  • 19
  • 61
  • 99

2 Answers2

17

Here is a way that only crossed my mind after I asked the question and it works but I do not know how it does in benchmarks. I am not currently at a computer with an installed R. I guess I should use a cloud instance. Anyway, I like the syntax

DT[c("a","b")]
Farrel
  • 10,244
  • 19
  • 61
  • 99
  • 1
    I added this to the benchmark's below, it performs the fastest by far of the three solutions. Nice work. – Chase Dec 14 '11 at 21:03
  • 1
    Great. That is known as _by without by_, a.k.a. _grouping by `i`_. Somewhat analagous to _having_ in SQL. – Matt Dowle Dec 15 '11 at 09:17
  • 3
    When you get the hang of grouping by `i`, the next learning step is _join inherited scope_. Run `example(data.table)` and the results show an example. – Matt Dowle Dec 15 '11 at 09:23
16

Using the %in% operator seems to give a factor of 2 performance bump. Consider:

library(data.table)
library(rbenchmark)
DT <- data.table(x=sample(letters, 1e6, TRUE), y=rnorm(1e6), v=runif(1e6))
setkey(DT,x)               # set a 1-column key
DT["b"]
f1 <- function() DT[x %in% letters[1:2]]
f2 <- function() DT[x=="a"| x == "b"]

> benchmark(f1(),f2())
  test replications elapsed relative user.self sys.self user.child sys.child
1 f1()          100    8.40 1.000000      7.58     0.81         NA        NA
2 f2()          100   17.11 2.036905     15.54     1.56         NA        NA

> all.equal(f1(), f2())
[1] TRUE

EDIT: Adding Farrel's option

Note, this is on a different computer, but the relative bumps are the same.

f3 <- function() DT[c("a", "b")]

  test replications elapsed  relative user.self sys.self user.child sys.child
1 f1()          100  11.281  7.121843     9.745    1.323          0         0
2 f2()          100  23.106 14.587121    20.824    2.224          0         0
3 f3()          100   1.584  1.000000     1.042    0.541          0         0
Chase
  • 67,710
  • 18
  • 144
  • 161