<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="http://www.r-bloggers.com/understanding-data-table-rolling-joins/"></div></div><div><a data-url="http://www.r-bloggers.com/understanding-data-table-rolling-joins/" data-counturl="http://www.r-bloggers.com/understanding-data-table-rolling-joins/" data-count="horizontal" data-text="Understanding data.table Rolling Joins" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://r-norberg.blogspot.com/2016/06/understanding-datatable-rolling-joins.html"> bRogramming</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<h1>Understanding data.table Rolling Joins</h1>
<h4><em>Robert Norberg</em></h4>
<h4><em>June 5, 2016</em></h4>
</div>
<div>
<h2>Introduction</h2>
<div>Rolling joins in <code>data.table</code> are incredibly useful, but not that well documented. I wrote this to help myself figure out how to use them and perhaps it can help you too.</div>
<pre><code><span>library</span><span>(</span><span>data.table</span><span>)</span></code></pre>
</div>
<div>
<h2>The Setup</h2>
<div>Imagine we have an eCommerce website that uses a third party (like PayPal) to handle payments. We track user sessions on our website and PayPal tracks our payments, and we would like to attribute sales to user sessions. This way we can answer all kinds of questions along the lines of “What types of sessions lead to sales?”</div>
<div>Lets create some example data for a diverse group of customers.</div>
<div>Indecisive Isabel shops a lot before she buys anything.</div>
<pre><code><span>isabel_website</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>rep</span><span>(</span><span>'Indecisive Isabel'</span>, <span>5</span><span>)</span>,<br>                     <span>session_start_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>c</span><span>(</span><span>'2016-01-01 11:01'</span>, <span>'2016-01-02 8:59'</span>, <span>'2016-01-05 18:18'</span>, <span>'2016-01-07 19:03'</span>, <span>'2016-01-08 19:01'</span><span>)</span><span>)</span><span>)</span><br><span>isabel_paypal</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>'Indecisive Isabel'</span>, <span>purchase_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>'2016-01-08 19:10'</span><span>)</span><span>)</span></code></pre>
<div>Spendy Sally visits the website once and makes multiple purchases.</div>
<pre><code><span>sally_website</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>'Spendy Sally'</span>, <span>session_start_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>'2016-01-03 10:00'</span><span>)</span><span>)</span><br><span>sally_paypal</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>rep</span><span>(</span><span>'Spendy Sally'</span>, <span>2</span><span>)</span>, <span>purchase_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>c</span><span>(</span><span>'2016-01-03 10:06'</span>, <span>'2016-01-03 10:15'</span><span>)</span><span>)</span><span>)</span></code></pre>
<div>Frequent Francis comes to the site a lot and sometimes buys things.</div>
<pre><code><span>francis_website</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>rep</span><span>(</span><span>'Frequent Francis'</span>, <span>6</span><span>)</span>,<br>                     <span>session_start_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>c</span><span>(</span><span>'2016-01-02 13:09'</span>, <span>'2016-01-03 19:22'</span>, <span>'2016-01-08 8:44'</span>, <span>'2016-01-08 20:22'</span>, <span>'2016-01-10 17:36'</span>, <span>'2016-01-15 16:56'</span><span>)</span><span>)</span><span>)</span><br><span>francis_paypal</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>rep</span><span>(</span><span>'Frequent Francis'</span>, <span>3</span><span>)</span>, <span>purchase_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>c</span><span>(</span><span>'2016-01-03 19:28'</span>, <span>'2016-01-08 20:33'</span>, <span>'2016-01-10 17:46'</span><span>)</span><span>)</span><span>)</span></code></pre>
<div>Error-prone Erica has mysteriously managed to make a purchase before ever visiting the site!</div>
<pre><code><span>erica_website</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>rep</span><span>(</span><span>'Error-prone Erica'</span>, <span>2</span><span>)</span>,<br>                     <span>session_start_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>c</span><span>(</span><span>'2016-01-04 19:12'</span>, <span>'2016-01-04 21:05'</span><span>)</span><span>)</span><span>)</span><br><span>erica_paypal</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>'Error-prone Erica'</span>, <span>purchase_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>'2016-01-03 08:02'</span><span>)</span><span>)</span></code></pre>
<div>Visitor Vivian visits our website a couple times, but never makes a purchase (so she appears in the website data, but not in the payment data).</div>
<pre><code><span>vivian_website</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>rep</span><span>(</span><span>'Visitor Vivian'</span>, <span>2</span><span>)</span>,<br>                     <span>session_start_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>c</span><span>(</span><span>'2016-01-01 9:10'</span>, <span>'2016-01-09 2:15'</span><span>)</span><span>)</span><span>)</span><br><span>vivian_paypal</span> <span>&lt;-</span> <span>erica_paypal</span><span>[</span><span>0</span><span>]</span> <span># has 0 rows, but the same column names/classes</span></code></pre>
<div>And Mom sent money to my PayPal account before my website was up and running (so she appears in the payment data, but not in the website data).</div>
<pre><code><span>mom_website</span> <span>&lt;-</span> <span>vivian_website</span><span>[</span><span>0</span><span>]</span> <span># has 0 rows, but the same column names/classes</span><br><span>mom_paypal</span> <span>&lt;-</span> <span>data.table</span><span>(</span><span>name</span> <span>=</span> <span>'Mom'</span>, <span>purchase_time</span> <span>=</span> <span>as.POSIXct</span><span>(</span><span>'2015-12-02 17:58'</span><span>)</span><span>)</span></code></pre>
<div>Combine these into two <code>data.table</code>s:</div>
<pre><code><span>website</span> <span>&lt;-</span> <span>rbindlist</span><span>(</span><span>list</span><span>(</span><span>isabel_website</span>, <span>sally_website</span>, <span>francis_website</span>, <span>erica_website</span>, <span>vivian_website</span>, <span>mom_website</span><span>)</span><span>)</span><br><span>paypal</span> <span>&lt;-</span> <span>rbindlist</span><span>(</span><span>list</span><span>(</span><span>isabel_paypal</span>, <span>sally_paypal</span>, <span>francis_paypal</span>, <span>erica_paypal</span>, <span>vivian_paypal</span>, <span>mom_paypal</span><span>)</span><span>)</span></code></pre>
<div>To keep things straight, lets give each website session a unique ID and each payment a unique ID.</div>
<pre><code><span>website</span><span>[</span>, <span>session_id</span><span>:</span><span>=</span><span>.GRP</span>, <span>by</span> <span>=</span> .<span>(</span><span>name</span>, <span>session_start_time</span><span>)</span><span>]</span><br><span>paypal</span><span>[</span>, <span>payment_id</span><span>:</span><span>=</span><span>.GRP</span>, <span>by</span> <span>=</span> .<span>(</span><span>name</span>, <span>purchase_time</span><span>)</span><span>]</span></code></pre>
<div>Finally, have a look at the data.</div>
<pre><code><span>website</span></code></pre>
<pre><code>##                  name  session_start_time session_id<br>##  1: Indecisive Isabel 2016-01-01 11:01:00          1<br>##  2: Indecisive Isabel 2016-01-02 08:59:00          2<br>##  3: Indecisive Isabel 2016-01-05 18:18:00          3<br>##  4: Indecisive Isabel 2016-01-07 19:03:00          4<br>##  5: Indecisive Isabel 2016-01-08 19:01:00          5<br>##  6:      Spendy Sally 2016-01-03 10:00:00          6<br>##  7:  Frequent Francis 2016-01-02 13:09:00          7<br>##  8:  Frequent Francis 2016-01-03 19:22:00          8<br>##  9:  Frequent Francis 2016-01-08 08:44:00          9<br>## 10:  Frequent Francis 2016-01-08 20:22:00         10<br>## 11:  Frequent Francis 2016-01-10 17:36:00         11<br>## 12:  Frequent Francis 2016-01-15 16:56:00         12<br>## 13: Error-prone Erica 2016-01-04 19:12:00         13<br>## 14: Error-prone Erica 2016-01-04 21:05:00         14<br>## 15:    Visitor Vivian 2016-01-01 09:10:00         15<br>## 16:    Visitor Vivian 2016-01-09 02:15:00         16</code></pre>
<pre><code><span>paypal</span></code></pre>
<pre><code>##                 name       purchase_time payment_id<br>## 1: Indecisive Isabel 2016-01-08 19:10:00          1<br>## 2:      Spendy Sally 2016-01-03 10:06:00          2<br>## 3:      Spendy Sally 2016-01-03 10:15:00          3<br>## 4:  Frequent Francis 2016-01-03 19:28:00          4<br>## 5:  Frequent Francis 2016-01-08 20:33:00          5<br>## 6:  Frequent Francis 2016-01-10 17:46:00          6<br>## 7: Error-prone Erica 2016-01-03 08:02:00          7<br>## 8:               Mom 2015-12-02 17:58:00          8</code></pre>
</div>
<div>
<h2>The Joins</h2>
<div>Before doing any rolling joins, I like to create a separate date/time column in each table to join on because one of the two tables loses it’s date/time field and I can never remember which.</div>
<pre><code><span>website</span><span>[</span>, <span>join_time</span><span>:</span><span>=</span><span>session_start_time</span><span>]</span><br><span>paypal</span><span>[</span>, <span>join_time</span><span>:</span><span>=</span><span>purchase_time</span><span>]</span></code></pre>
<div>Next, set keys on each table. The last key column is the one the rolling join will “roll” on. We want to first join on <code>name</code> and then within each <code>name</code>, match website sessions to purchases. So we key on <code>name</code> first, then on the newly created <code>join_time</code>.</div>
<pre><code><span>setkey</span><span>(</span><span>website</span>, <span>name</span>, <span>join_time</span><span>)</span><br><span>setkey</span><span>(</span><span>paypal</span>, <span>name</span>, <span>join_time</span><span>)</span></code></pre>
<div>
<h3>Rolling Forward</h3>
<div>Now let’s answer the question “what website session immediately preceded each payment?”</div>
<pre><code><span>website</span><span>[</span><span>paypal</span>, <span>roll</span> <span>=</span> <span>T</span><span>]</span> <span># equivalent to website[paypal, roll = Inf]</span></code></pre>
<pre><code>##                 name  session_start_time session_id           join_time<br>## 1: Error-prone Erica                &lt;NA>         NA 2016-01-03 08:02:00<br>## 2:  Frequent Francis 2016-01-03 19:22:00          8 2016-01-03 19:28:00<br>## 3:  Frequent Francis 2016-01-08 20:22:00         10 2016-01-08 20:33:00<br>## 4:  Frequent Francis 2016-01-10 17:36:00         11 2016-01-10 17:46:00<br>## 5: Indecisive Isabel 2016-01-08 19:01:00          5 2016-01-08 19:10:00<br>## 6:               Mom                &lt;NA>         NA 2015-12-02 17:58:00<br>## 7:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:06:00<br>## 8:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:15:00<br>##          purchase_time payment_id<br>## 1: 2016-01-03 08:02:00          7<br>## 2: 2016-01-03 19:28:00          4<br>## 3: 2016-01-08 20:33:00          5<br>## 4: 2016-01-10 17:46:00          6<br>## 5: 2016-01-08 19:10:00          1<br>## 6: 2015-12-02 17:58:00          8<br>## 7: 2016-01-03 10:06:00          2<br>## 8: 2016-01-03 10:15:00          3</code></pre>
<div>Notice several things about this result:</div>
<ul>
<li>Each payment is matched to the closest preceding payment. <code>all(purchase_time > session_start_time, na.rm = T)</code> evaluates to <code>TRUE</code>.</li>
<li>Payments with no preceding sessions still appear in the result (that is, <code>nrow(result) == nrow(paypal)</code>).</li>
<li>Visitor Vivian does not appear in the results because she does not appear in the <code>paypal</code> table.</li>
<li>Mom’s “purchase” has no website session associated with it because she has never visited the website <em>at all</em>.</li>
<li>Error-prone Erica’s mysterious purchase has no website session associated with it because she never visited the website <em>prior to her purchase</em>.</li>
<li>Spendy Sally’s one website session is matched to both of her purchases.</li>
</ul>
</div>
<div>
<h3>Rolling Backward</h3>
<div>Now lets switch the order of the two tables and answer the question “which sessions led to a purchase?” In this case, we want to match payments to website sessions, so long as the payment occurred <em>after</em> the beginning of the website session.</div>
<pre><code><span>paypal</span><span>[</span><span>website</span>, <span>roll</span> <span>=</span> <span>-</span><span>Inf</span><span>]</span></code></pre>
<pre><code>##                  name       purchase_time payment_id           join_time<br>##  1: Error-prone Erica                &lt;NA>         NA 2016-01-04 19:12:00<br>##  2: Error-prone Erica                &lt;NA>         NA 2016-01-04 21:05:00<br>##  3:  Frequent Francis 2016-01-03 19:28:00          4 2016-01-02 13:09:00<br>##  4:  Frequent Francis 2016-01-03 19:28:00          4 2016-01-03 19:22:00<br>##  5:  Frequent Francis 2016-01-08 20:33:00          5 2016-01-08 08:44:00<br>##  6:  Frequent Francis 2016-01-08 20:33:00          5 2016-01-08 20:22:00<br>##  7:  Frequent Francis 2016-01-10 17:46:00          6 2016-01-10 17:36:00<br>##  8:  Frequent Francis                &lt;NA>         NA 2016-01-15 16:56:00<br>##  9: Indecisive Isabel 2016-01-08 19:10:00          1 2016-01-01 11:01:00<br>## 10: Indecisive Isabel 2016-01-08 19:10:00          1 2016-01-02 08:59:00<br>## 11: Indecisive Isabel 2016-01-08 19:10:00          1 2016-01-05 18:18:00<br>## 12: Indecisive Isabel 2016-01-08 19:10:00          1 2016-01-07 19:03:00<br>## 13: Indecisive Isabel 2016-01-08 19:10:00          1 2016-01-08 19:01:00<br>## 14:      Spendy Sally 2016-01-03 10:06:00          2 2016-01-03 10:00:00<br>## 15:    Visitor Vivian                &lt;NA>         NA 2016-01-01 09:10:00<br>## 16:    Visitor Vivian                &lt;NA>         NA 2016-01-09 02:15:00<br>##      session_start_time session_id<br>##  1: 2016-01-04 19:12:00         13<br>##  2: 2016-01-04 21:05:00         14<br>##  3: 2016-01-02 13:09:00          7<br>##  4: 2016-01-03 19:22:00          8<br>##  5: 2016-01-08 08:44:00          9<br>##  6: 2016-01-08 20:22:00         10<br>##  7: 2016-01-10 17:36:00         11<br>##  8: 2016-01-15 16:56:00         12<br>##  9: 2016-01-01 11:01:00          1<br>## 10: 2016-01-02 08:59:00          2<br>## 11: 2016-01-05 18:18:00          3<br>## 12: 2016-01-07 19:03:00          4<br>## 13: 2016-01-08 19:01:00          5<br>## 14: 2016-01-03 10:00:00          6<br>## 15: 2016-01-01 09:10:00         15<br>## 16: 2016-01-09 02:15:00         16</code></pre>
<div>In this result</div>
<ul>
<li>Each website session is match to the nearest following payment. <code>all(session_start_time > purchase_time, na.rm = T)</code> evaluates to<code>TRUE</code>.</li>
<li>Mom does not appear because she has no record in the <code>website</code> table.</li>
<li>Visitor Vivian’s sessions are not matched to any purchases because she hasn’t purchased anything.</li>
<li>Neither of Erica’s website sessions are matched to her purchase because it took place <em>before</em> both sessions.</li>
<li>Frequent Francis’s most recent session isn’t matched to a purchase because she hasn’t made a purchase after that session.</li>
<li>All of Indecisive Isabel’s sessions are matched to her one purchase. In fact, several purchases appear more than once.</li>
</ul>
</div>
<div>
<h3>Rolling Windows</h3>
<div>What if we wanted to add an additional criteria to the rolling join above: match payments to website sessions, so long as the payment occurred after the beginning of the website session <em>and within 12 hours of the website session</em>?</div>
<pre><code><span>twelve_hours</span> <span>&lt;-</span> <span>60</span><span>*</span><span>60</span><span>*</span><span>20</span> <span># 12 hours = 60 sec * 60 min * 12 hours</span><br><span>paypal</span><span>[</span><span>website</span>, <span>roll</span> <span>=</span> <span>-</span><span>twelve_hours</span><span>]</span></code></pre>
<pre><code>##                  name       purchase_time payment_id           join_time<br>##  1: Error-prone Erica                &lt;NA>         NA 2016-01-04 19:12:00<br>##  2: Error-prone Erica                &lt;NA>         NA 2016-01-04 21:05:00<br>##  3:  Frequent Francis                &lt;NA>         NA 2016-01-02 13:09:00<br>##  4:  Frequent Francis 2016-01-03 19:28:00          4 2016-01-03 19:22:00<br>##  5:  Frequent Francis 2016-01-08 20:33:00          5 2016-01-08 08:44:00<br>##  6:  Frequent Francis 2016-01-08 20:33:00          5 2016-01-08 20:22:00<br>##  7:  Frequent Francis 2016-01-10 17:46:00          6 2016-01-10 17:36:00<br>##  8:  Frequent Francis                &lt;NA>         NA 2016-01-15 16:56:00<br>##  9: Indecisive Isabel                &lt;NA>         NA 2016-01-01 11:01:00<br>## 10: Indecisive Isabel                &lt;NA>         NA 2016-01-02 08:59:00<br>## 11: Indecisive Isabel                &lt;NA>         NA 2016-01-05 18:18:00<br>## 12: Indecisive Isabel                &lt;NA>         NA 2016-01-07 19:03:00<br>## 13: Indecisive Isabel 2016-01-08 19:10:00          1 2016-01-08 19:01:00<br>## 14:      Spendy Sally 2016-01-03 10:06:00          2 2016-01-03 10:00:00<br>## 15:    Visitor Vivian                &lt;NA>         NA 2016-01-01 09:10:00<br>## 16:    Visitor Vivian                &lt;NA>         NA 2016-01-09 02:15:00<br>##      session_start_time session_id<br>##  1: 2016-01-04 19:12:00         13<br>##  2: 2016-01-04 21:05:00         14<br>##  3: 2016-01-02 13:09:00          7<br>##  4: 2016-01-03 19:22:00          8<br>##  5: 2016-01-08 08:44:00          9<br>##  6: 2016-01-08 20:22:00         10<br>##  7: 2016-01-10 17:36:00         11<br>##  8: 2016-01-15 16:56:00         12<br>##  9: 2016-01-01 11:01:00          1<br>## 10: 2016-01-02 08:59:00          2<br>## 11: 2016-01-05 18:18:00          3<br>## 12: 2016-01-07 19:03:00          4<br>## 13: 2016-01-08 19:01:00          5<br>## 14: 2016-01-03 10:00:00          6<br>## 15: 2016-01-01 09:10:00         15<br>## 16: 2016-01-09 02:15:00         16</code></pre>
<div>Now Indecisive Isabel’s last session only is associated with a purchase.</div>
</div>
</div>
<div>
<h2>The <code>rollends</code> Argument</h2>
<div>Recall the first join from above, matching the preceding website session to each payment.</div>
<pre><code><span>website</span><span>[</span><span>paypal</span>, <span>roll</span> <span>=</span> <span>T</span><span>]</span> <span># equivalent to website[paypal, roll = T, rollends = c(F, T)]</span></code></pre>
<pre><code>##                 name  session_start_time session_id           join_time<br>## 1: Error-prone Erica                &lt;NA>         NA 2016-01-03 08:02:00<br>## 2:  Frequent Francis 2016-01-03 19:22:00          8 2016-01-03 19:28:00<br>## 3:  Frequent Francis 2016-01-08 20:22:00         10 2016-01-08 20:33:00<br>## 4:  Frequent Francis 2016-01-10 17:36:00         11 2016-01-10 17:46:00<br>## 5: Indecisive Isabel 2016-01-08 19:01:00          5 2016-01-08 19:10:00<br>## 6:               Mom                &lt;NA>         NA 2015-12-02 17:58:00<br>## 7:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:06:00<br>## 8:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:15:00<br>##          purchase_time payment_id<br>## 1: 2016-01-03 08:02:00          7<br>## 2: 2016-01-03 19:28:00          4<br>## 3: 2016-01-08 20:33:00          5<br>## 4: 2016-01-10 17:46:00          6<br>## 5: 2016-01-08 19:10:00          1<br>## 6: 2015-12-02 17:58:00          8<br>## 7: 2016-01-03 10:06:00          2<br>## 8: 2016-01-03 10:15:00          3</code></pre>
<div>What if we want the rolling join to handle Error-prone Erica’s case differently? Perhaps in cases like hers, where there is a purchase with no preceding session, we prefer the user’s first website session to be matched to the offending purchase. We can use the <code>rollends</code> argument for this. From the <code>data.table</code> documentation (<code>?data.table</code>),</div>
<pre><code>rollends[1]=TRUE will roll the first value backwards if the value is before it</code></pre>
<pre><code><span>website</span><span>[</span><span>paypal</span>, <span>roll</span> <span>=</span> <span>T</span>, <span>rollends</span> <span>=</span> <span>c</span><span>(</span><span>T</span>, <span>T</span><span>)</span><span>]</span> <span># equivalent to website[paypal, roll = T, rollends = T]</span></code></pre>
<pre><code>##                 name  session_start_time session_id           join_time<br>## 1: Error-prone Erica 2016-01-04 19:12:00         13 2016-01-03 08:02:00<br>## 2:  Frequent Francis 2016-01-03 19:22:00          8 2016-01-03 19:28:00<br>## 3:  Frequent Francis 2016-01-08 20:22:00         10 2016-01-08 20:33:00<br>## 4:  Frequent Francis 2016-01-10 17:36:00         11 2016-01-10 17:46:00<br>## 5: Indecisive Isabel 2016-01-08 19:01:00          5 2016-01-08 19:10:00<br>## 6:               Mom                &lt;NA>         NA 2015-12-02 17:58:00<br>## 7:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:06:00<br>## 8:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:15:00<br>##          purchase_time payment_id<br>## 1: 2016-01-03 08:02:00          7<br>## 2: 2016-01-03 19:28:00          4<br>## 3: 2016-01-08 20:33:00          5<br>## 4: 2016-01-10 17:46:00          6<br>## 5: 2016-01-08 19:10:00          1<br>## 6: 2015-12-02 17:58:00          8<br>## 7: 2016-01-03 10:06:00          2<br>## 8: 2016-01-03 10:15:00          3</code></pre>
<div>In this result, Erica’s first session is matched to her purchase, even though the session was after her purchase. Mom’s “purchase” still has no matching session because Mom does not appear in the <code>website</code> table. So <code>all(purchase_time > session_start_time, na.rm = T)</code> no longer evaluates to <code>TRUE</code>.</div>
<div>What if we want to perform the same join as above, but only returning matches for payments with sessions before <em>and after</em>?</div>
<pre><code><span>website</span><span>[</span><span>paypal</span>, <span>roll</span> <span>=</span> <span>T</span>, <span>rollends</span> <span>=</span> <span>c</span><span>(</span><span>F</span>, <span>F</span><span>)</span><span>]</span> <span># equivalent to website[paypal, roll = T, rollends = F]</span></code></pre>
<pre><code>##                 name  session_start_time session_id           join_time<br>## 1: Error-prone Erica                &lt;NA>         NA 2016-01-03 08:02:00<br>## 2:  Frequent Francis 2016-01-03 19:22:00          8 2016-01-03 19:28:00<br>## 3:  Frequent Francis 2016-01-08 20:22:00         10 2016-01-08 20:33:00<br>## 4:  Frequent Francis 2016-01-10 17:36:00         11 2016-01-10 17:46:00<br>## 5: Indecisive Isabel                &lt;NA>         NA 2016-01-08 19:10:00<br>## 6:               Mom                &lt;NA>         NA 2015-12-02 17:58:00<br>## 7:      Spendy Sally                &lt;NA>         NA 2016-01-03 10:06:00<br>## 8:      Spendy Sally                &lt;NA>         NA 2016-01-03 10:15:00<br>##          purchase_time payment_id<br>## 1: 2016-01-03 08:02:00          7<br>## 2: 2016-01-03 19:28:00          4<br>## 3: 2016-01-08 20:33:00          5<br>## 4: 2016-01-10 17:46:00          6<br>## 5: 2016-01-08 19:10:00          1<br>## 6: 2015-12-02 17:58:00          8<br>## 7: 2016-01-03 10:06:00          2<br>## 8: 2016-01-03 10:15:00          3</code></pre>
<div>In this result, the purchases of Error-prone Erica and Mom are unmatched because they have no <em>preceding</em> sessions, and Spendy Sally’s two purchases are unmatched because they have no <em>following</em> website session.</div>
<div>Note that when <code>roll</code> is set to a negative number, the meaning of the two <code>rollends</code> elements kind of flip-flops:</div>
<pre><code><span>website</span><span>[</span><span>paypal</span>, <span>roll</span> <span>=</span> <span>-</span><span>Inf</span>, <span>rollends</span> <span>=</span> <span>c</span><span>(</span><span>F</span>, <span>T</span><span>)</span><span>]</span> <span># default when roll &lt; 0 is rollends = c(T, F)</span></code></pre>
<pre><code>##                 name  session_start_time session_id           join_time<br>## 1: Error-prone Erica                &lt;NA>         NA 2016-01-03 08:02:00<br>## 2:  Frequent Francis 2016-01-08 08:44:00          9 2016-01-03 19:28:00<br>## 3:  Frequent Francis 2016-01-10 17:36:00         11 2016-01-08 20:33:00<br>## 4:  Frequent Francis 2016-01-15 16:56:00         12 2016-01-10 17:46:00<br>## 5: Indecisive Isabel 2016-01-08 19:01:00          5 2016-01-08 19:10:00<br>## 6:               Mom                &lt;NA>         NA 2015-12-02 17:58:00<br>## 7:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:06:00<br>## 8:      Spendy Sally 2016-01-03 10:00:00          6 2016-01-03 10:15:00<br>##          purchase_time payment_id<br>## 1: 2016-01-03 08:02:00          7<br>## 2: 2016-01-03 19:28:00          4<br>## 3: 2016-01-08 20:33:00          5<br>## 4: 2016-01-10 17:46:00          6<br>## 5: 2016-01-08 19:10:00          1<br>## 6: 2015-12-02 17:58:00          8<br>## 7: 2016-01-03 10:06:00          2<br>## 8: 2016-01-03 10:15:00          3</code></pre>
<div>In this example,</div>
<ul>
<li>Each payment is matched to the nearest following website session (because of <code>roll = -Inf</code>).</li>
<li>Error-prone Erica’s purchase is not matched to the following session because there is no previous session (due to <code>rollends[1] = F</code>).</li>
<li>Spendy Sally’s purchases are joined to her most recent website session, even though it occurred before her purchases (because of<code>rollends[2] = T</code>).</li>
</ul>
</div>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/understanding-data-table-rolling-joins/"></div></div><div><a data-url="http://www.r-bloggers.com/understanding-data-table-rolling-joins/" data-counturl="http://www.r-bloggers.com/understanding-data-table-rolling-joins/" data-count="vertical" data-text="Understanding data.table Rolling Joins" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://r-norberg.blogspot.com/2016/06/understanding-datatable-rolling-joins.html"> bRogramming</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/EB905stUzTo"></a></div>