<div dir="ltr">Np. Those are documented in ?data.table and ?.SD, but it's hard to put the pieces together.<div><br></div><div>This is an "update join". The developers have a vignette on joins planned, but in the meantime, maybe my notes on it can help: <a href="http://franknarf1.github.io/r-tutorial/_book/tables.html#dt-joins">http://franknarf1.github.io/r-tutorial/_book/tables.html#dt-joins</a></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Feb 14, 2018 at 4:01 PM, Bernstein, Elliot J <span dir="ltr"><<a href="mailto:EJBernstein@wellington.com" target="_blank">EJBernstein@wellington.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple">
<div class="m_-4899355699446734405WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Frank –<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thank you very much for your help.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">That works, but unfortunately I don’t understand why. I’ve been searching the package help and vignettes, but haven’t found an explanation. Is this syntax documented
somewhere? (Nearly everything about that line is a mystery to me: What does it mean to use .SD in the i argument to DT[]? What is “on = .(date) doing? And how is “x.y” apparently referring to column “y” of y.dt?)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">- Elliot<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Frank Erickson [mailto:<a href="mailto:by.hook.or@gmail.com" target="_blank">by.hook.or@gmail.com</a>]
<br>
<b>Sent:</b> Wednesday, February 14, 2018 3:27 PM<br>
<b>To:</b> Bernstein, Elliot J<br>
<b>Cc:</b> <a href="mailto:datatable-help@lists.r-forge.r-project.org" target="_blank">datatable-help@lists.r-forge.<wbr>r-project.org</a><br>
<b>Subject:</b> Re: [datatable-help] Rolling Join With Groups in One Table<u></u><u></u></span></p><div><div class="h5">
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">You mean<u></u><u></u></p>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">x.dt[, y := y.dt[.SD, on=.(date), roll=TRUE, x.y]]</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">?<u></u><u></u></p>
</div>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">On Wed, Feb 14, 2018 at 3:18 PM, Bernstein, Elliot J <<a href="mailto:EJBernstein@wellington.com" target="_blank">EJBernstein@wellington.com</a>> wrote:<u></u><u></u></p>
<div>
<div>
<p class="MsoNormal">How do I execute a rolling join when one table has groups, and the other does not? For example:<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">x.dt <- as.data.table(</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> expand.grid(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "days"),</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> group = c("A", "B")</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> )</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">x.dt[, x := rnorm(.N)]</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">setkey(x.dt, group, date)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">y.dt <- data.table(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "months"))</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">y.dt[, y := month(date)]</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">setkey(y.dt, date)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">result <- y.dt[x.dt, roll = TRUE]</span><u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal">The last line fails because “group” is part of the key in x.dt, but not y.dt:<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">Error in bmerge(i, x, leftcols, rightcols, io, xo, roll, rollends, nomatch, :
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> typeof x.date (double) != typeof i.group (integer)</span><u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal">I would like the value of y in each row of the result to be the month number (from y.dt), regardless of the value of the group column.<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal">Thanks.<u></u><u></u></p>
<p class="MsoNormal"><span style="color:#888888"> <u></u><u></u></span></p>
<p class="MsoNormal"><span style="color:#888888">- Elliot<u></u><u></u></span></p>
<p class="MsoNormal"><span style="color:#888888"> <u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal"><br>
______________________________<wbr>_________________<br>
datatable-help mailing list<br>
<a href="mailto:datatable-help@lists.r-forge.r-project.org" target="_blank">datatable-help@lists.r-forge.<wbr>r-project.org</a><br>
<a href="https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help" target="_blank">https://lists.r-forge.r-<wbr>project.org/cgi-bin/mailman/<wbr>listinfo/datatable-help</a><u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div></div></div>
</div>
</blockquote></div><br></div>