StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm not sure this will work on your data as you perhaps need to post a more complete example, but something like the following might work. It does 1 join (binary search) then one vector scan (which creates a couple of long logical vectors behind the scenes, so is not ideal)</p> <p>I've provided a simple but larger example data set with a bit more replication.</p> <pre><code>DT <- as.data.table(list(id1 = c(5L, 1L, 5L, 1L, 5L, 3L, 5L, 3L, 1L, 3L), id2 = 1:10, startunix = 1:10, endunix = 5:14)) DA <- as.data.table(list(id1 = c(3L, 5L, 5L, 5L), unixtime = c(5L, 1L, 6L, 12L))) setkey(DA,id1) setkey(DT,id1) DT[DA, nomatch=0][unixtime %between% c(startunix, endunix)] id1 id2 startunix endunix unixtime 1: 5 1 1 5 6 2: 5 3 3 7 6 3: 5 5 5 9 6 4: 5 7 7 11 6 </code></pre> <p>To explain what it is doing, it is matching by <code>id1</code>, and <code>nomatch = 0</code> means that these are not included. This expands to all combinations of the multiple rows in DA[J(5)] and DT[J(5)] - in this case there </p> <pre><code> DA[J(5)] id1 unixtime 1: 5 1 2: 5 6 3: 5 12 > DT[J(5)] id1 id2 startunix endunix 1: 5 1 1 5 2: 5 3 3 7 3: 5 5 5 9 4: 5 7 7 11 </code></pre> <p>So the created merged data set contains all 12 combinations. ( 4 times 3)</p> <p>I then use the function <code>between</code> ( part of the <code>data.table</code> package) to subset those values where <code>unixtime</code> is between <code>startunix</code> and <code>endunix</code>.</p> <p>To the best of my knowledge you won't be able to use binary searches to find whether something is within a range (however @MatthewDowle, the main <code>data.table</code> package author is active on SO and may jump in here with a comment as to whether this is possible or likely in the future)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload