20

I am trying to subset a data.table ( from the package data.table ) in R (not a data.frame). I have a 4 digit year as a key. I would like to subset by taking a series of years. For example, I want to pull all the records that are from 1999, 2000, 2001.

I have tried passing in my DT[J(year)] binary search syntax the following:

1999,2000,2001 c(1999,2000,2001) 1999, 2000, 2001

but none of these seem to work. Anyone know how to do a subset where the years you want to select are not just 1 but multiple years?

Improve this question
5
  • 5
    Sorry for not being a good citizen on Stackoverflow. Will attend to this now. Will also be more mindful about getting references included to save time for those who are trying to help me. Commented Mar 31, 2011 at 13:50
  • 1
    @Andrie : question is edited to include it (@exl did that, I just made it a bit more clear), so your downvote can be reversed if you wished to do so. For the rest the question is at least valid. Commented Mar 31, 2011 at 17:13
  • 3
    @Joris, That is already an improvement, so I have reversed my downvote. However, for this to be a good question, it needs a library(data.table) statement plus some real example code. Commented Mar 31, 2011 at 20:29
  • 1
    -1 for not providing the error message or example data. Commented May 28, 2012 at 8:57
  • 10
    +1 to reverse some excessive markdowns. -1 for no data.table ref perhaps, but -5? And why the need to list error messages for such a simple matter of syntax? Commented Oct 29, 2013 at 13:41

4 Answers 4

Reset to default
20

What works for data.frames works for data.tables. 

subset(DT, year %in% 1999:2001)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Not everything: dt <- data.table(a=c(1,2,3), b=c(4,5,6), c=c(7,8,9)); dt[, c('b', 'c')] returns a vector of column names - with data.frame, it returns the columns.
data.table has its own subset now: subset.data.table {data.table}
18

The question is not clear and does not provide sufficient data to work with BUT it is usefull, so if some one can edit it with the data I provide hereafter, one is welcome. The title of the post could also be completed : Matthew Dowle often answers the subsetting-over-two-vectors question, but less frequently the subsetting-according-a-in-statement-on-one-vector one. I have been looking a while for an answer, untill finding one for character vectors here.

Let's consider this data :

library(data.table) n <- 100 X <- data.table(a=sample(c(10,20,25,30,40),n,replace=TRUE),b=1:n)

The data.table-style query corresponding to X[X$a %in% c(10,20),] is somehow surprising :

setkey(X,a) X[.(c(10,20))] X[.(10,20)] # works for characters but not for integers # instead, treats 10 as the filter # and 20 as a new variable # for comparison : X[X$a %in% c(10,20),]

Now, which is best? If your key is already set, data.table, obviously. Otherwise, it might not, as prove the following time-measurements (on my 1,75 Go RAM computer) :

n <- 1e7 X <- data.table(a=sample(c(10,20,25,30,40),n,replace=TRUE),b=1:n) system.time(X[X$a %in% c(10,20),]) # utilisateur système écoulé (yes, I'm French) # 1.92 0.06 1.99 system.time(setkey(X,a)) # utilisateur système écoulé # 34.91 0.05 35.23 system.time(X[J(c(10,20))]) # utilisateur système écoulé # 0.15 0.08 0.23

But maybe Matthew has better solutions...

[Matthew] You've discovered that sorting type numeric (a.k.a. double) is much slower than integer. For many years we didn't allow double in keys for fear of users falling into this trap and reporting terrible timings like this. We allowed double in keys with some trepidation because fast sorting isn't implemented for double yet. Fast sorting on integer and character is pretty good because those are done using a counting sort. Hopefully we'll get to fast sorting numeric one day! (Now implemented - see below).

Timings on data.table pre-1.9.0

n <- 1e7 X <- data.table(a=sample(c(10,20,25,30,40),n,replace=TRUE),b=1:n) system.time(setkey(X,a)) # user system elapsed # 13.898 0.138 14.216 X <- data.table(a=sample(as.integer(c(10,20,25,30,40)),n,replace=TRUE),b=1:n) system.time(setkey(X,a)) # user system elapsed # 0.381 0.019 0.408

Rememeber that 2 is type numeric in R by default. 2L is integer. Although data.table accepts numeric it still much prefers integer.

Fast radix sort for numerics is implemented since v1.9.0.

From v1.9.0 on

n <- 1e7 X <- data.table(a=sample(c(10,20,25,30,40),n,replace=TRUE),b=1:n) system.time(setkey(X,a)) # user system elapsed # 0.832 0.026 0.871
Improve this answer

3 Comments

On the "less frequently" comment can you provide some links? I'm not aware I've avoided answering any questions at all. Also I don't follow the "works for characters but not integers" bit, do you have an example of it working for character? Needing to make a vector using c() inside a call to list() or data.frame|table is a common R idiom.
But now you've answered this question, I see what the asker was asking now. But at the time (2 years ago) I honestly did not understand. I usually guess if I can (it would have helped if the 1st and 3rd attempts in the question had been valid syntax). I agree it's actually a good question, deep down.
I did not mean you actually avoided to answer :) It's just that the question is less often asked. OK, fot the Integer case, my mistake. For an example with characters, see the link provided in the beginning. With X <- data.table(a=sample(as.character(c(10,20,25,30,40)),n,replace=TRUE),b=1:n), you can subset X according to a subset of a with X[.('10','20')].
8

Like the above, but more data.table esque:

DT[year %in% c(1999, 2000, 2001)]

Improve this answer

2 Comments

True but we don't want to encourage vector scans. And the question is pretty bad. If they had included basic example and the error message, it could have been solved using binary search.
These days, this is the right answer thanks to auto indexing, I guess.
1

This will work:

sample_DT = data.table(year = rep(1990:2010, length.out = 1000), random_number = rnorm(1000), key = "year") year_subset = sample_DT[J(c(1990, 1995, 1997))]

Similarly, you can key an already existing data.table with setkey(existing_DT, year) and then use the J() syntax as shown above.

I think the problem may be that you didn't key the data first.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.