efficiently subsetting data.table multiple times

Question

I have data in this format

> data = data.table(id = 1:10, date = seq(as.Date("2016-01-01"), by = 1, length = 10))
> data
    id       date
 1:  1 2016-01-01
 2:  2 2016-01-02
 3:  3 2016-01-03
 4:  4 2016-01-04
 5:  5 2016-01-05
 6:  6 2016-01-06
 7:  7 2016-01-07
 8:  8 2016-01-08
 9:  9 2016-01-09
10: 10 2016-01-10

I have another matrix which is the queries / subsets that I wish to preform.

> query = data.table(id = c(1,4,7), date_start = c("2016-01-01", "2016-01-01", "2016-01-01"), date_end = c("2016-01-04", "2016-01-02", "2016-01-03"))
> query
   id date_start   date_end
1:  1 2016-01-01 2016-01-04
2:  4 2016-01-01 2016-01-02
3:  7 2016-01-01 2016-01-03

I wish to do something like this:

subset(data, (id == query[1] & date > date_start[1] & date < date_end[1]) | 
       (id == query[2] & date > date_start[2] & date < date_end[2]) |
       (id == query[3] & date > date_start[3] & date < date_end[3]))

Is there a automatically generate the subset query without using a for-loop and rbinding the result.

Thanks

You can use `foverlaps(data, query, nomatch=0)` after formatting your data correctly (with `IDate` columns, see `?foverlaps` and `?IDate`). This Q&A may help: http://stackoverflow.com/q/27487949/ — Frank, Jun 27 '16 at 20:16
i think foverlaps is what i'm looking for, if you reply as an answer, I will be able to accept it. — yingw, Jun 27 '16 at 20:33

score 5 · Accepted Answer · answered Jun 27 '16 at 20:50

If we transform the OP's data a bit to get

library(data.table)
data = setDT(structure(list(id = 1:10, date = structure(16801:16810, class = c("IDate", 
"Date")), date2 = structure(16801:16810, class = c("IDate", "Date"
))), .Names = c("id", "date", "date2"), row.names = c(NA, -10L
), class = c("data.table", "data.frame"), sorted = c("id", 
"date", "date2")))

query = setDT(structure(list(id = c(1, 4, 7), date_start = 
structure(c(16801L, 
16801L, 16801L), class = c("IDate", "Date")), date_end = structure(c(16804L, 
16802L, 16803L), class = c("IDate", "Date"))), .Names = c("id", 
"date_start", "date_end"), row.names = c(NA, -3L), class = c("data.table", 
"data.frame"), sorted = c("id", 
"date_start", "date_end")))

... then we can use foverlaps like

foverlaps(data, query, nomatch=0)
#    id date_start   date_end       date      date2
# 1:  1 2016-01-01 2016-01-04 2016-01-01 2016-01-01

For this approach, I think one needs to take these steps before merging:

have all dates as IDates
create an extra date column in the main data
set the key on each table

Any corrections are welcome. I don't actually use `foverlaps` much. — Frank, Jun 27 '16 at 20:50

score 3 · Answer 2 · answered Jun 28 '16 at 05:15

3

In the current development version, you can directly perform a non-equi join as follows:

# data.table v1.9.7+
data[query, .(id, x.date), on=.(id, date>=date_start, date<=date_end)]

If necessary add nomatch=0L to remove non-matching rows in result.

The .(id, x.date) is necessary at the moment until I workout how the default output for a non-equi join should look like.

answered Jun 28 '16 at 05:15

Arun

116,683
26
284
387

on a related note, is there any way to do a query-query join and remove the diagonal (exact matches from self?) – yingw Jun 28 '16 at 18:19

Hack-R · Answer 3 · 2016-06-27T20:02:13.587

require(data.table)
data = data.table(id = 1:10, date = seq(as.Date("2016-01-01"), by = 1, length = 10))
query = data.table(id = c(1,4,7), date_start = c("2016-01-01", "2016-01-01", 
"2016-01-01"), date_end = c("2016-01-04", "2016-01-02", "2016-01-03"))

First you can just join them:

data.full <- merge(data,query,by="id", all.x=T)

Next, if you want to exclude observations that were not referenced in query and keep the ones that were referenced if they fell in the date range then you can do this:

data.final <- data.full[date >= date_start & date <= date_end,]

data.final
   id       date date_start   date_end
1:  1 2016-01-01 2016-01-01 2016-01-04

or if you want to keep the records that were not referenced in query and keep the ones that were referenced if they fell in the date range:

data.final <- data.full[is.na(date_start) | (date >= date_start & date <= date_end),]
data.final
   id       date date_start   date_end
1:  1 2016-01-01 2016-01-01 2016-01-04
2:  2 2016-01-02         NA         NA
3:  3 2016-01-03         NA         NA
4:  5 2016-01-05         NA         NA
5:  6 2016-01-06         NA         NA
6:  8 2016-01-08         NA         NA
7:  9 2016-01-09         NA         NA
8: 10 2016-01-10         NA         NA

@docendodiscimus Oh, that's true. I didn't even really think about it being a data.table when I wrote this. Fortunately it still works this way, but your comment is correct. I went ahead and updated it. — Hack-R, Jun 27 '16 at 20:00

efficiently subsetting data.table multiple times

3 Answers3