<div dir="ltr">I think the following will achieve the same as your merge's:<div><br></div><div style><div>setkey(g_ctpty, ctpty_head)</div><div><div>setkey(links, ctpty_head)</div></div><div style>setkey(g_iss, iss_head)</div>
<div style>g_iss[data.table(g_ctpty[links], key = "iss_head")]<br></div></div><div><br></div><div style>And in general, merge(X, Y, all.x = TRUE) is (more or less) equivalent to Y[X]</div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Thu, Jun 20, 2013 at 11:43 AM, Ivan Alves <span dir="ltr"><<a href="mailto:papucho@me.com" target="_blank">papucho@me.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="word-wrap:break-word">Many thanks to both Eduard and Frank on the issue of the needed key. One aspect of the merging that is not clear is how to do 'inner' vs. 'outer' 'joins' (like in SQL). Whereas it works with merge (using the all.x=TRUE option), how is it done with data.table? In the improved example below<div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)"><br></div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">g_ctpty <span style="color:#000000">=</span> data.table<span style="color:#bf7078">(</span>ctpty_head<span style="color:#000000">=</span>c<span style="color:#bf7078">(</span><span style="color:#3d92d6">"a"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"b"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"c"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"d"</span><span style="color:#bf7078">)</span><span style="color:#000000">,</span> ctpty_cty<span style="color:#000000">=</span>c<span style="color:#bf7078">(</span><span style="color:#3d92d6">"US"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"DE"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"JP"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"CN"</span><span style="color:#bf7078">))</span></div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">g_iss <span style="color:#000000">=</span> data.table<span style="color:#bf7078">(</span>iss_head<span style="color:#000000">=</span>c<span style="color:#bf7078">(</span><span style="color:#3d92d6">"a"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"b"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"c"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"d"</span><span style="color:#bf7078">)</span><span style="color:#000000">,</span> iss_cty<span style="color:#000000">=</span>c<span style="color:#bf7078">(</span><span style="color:#3d92d6">"US"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"DE"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"JP"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"CN"</span><span style="color:#bf7078">))</span></div>
<div class="im"><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">links <span style="color:#000000">=</span> data.table<span style="color:#bf7078">(</span>ctpty_head<span style="color:#000000">=</span>c<span style="color:#bf7078">(</span><span style="color:#3d92d6">"a"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"b"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"c"</span><span style="color:#bf7078">)</span><span style="color:#000000">,</span> iss_head<span style="color:#000000">=</span>c<span style="color:#bf7078">(</span><span style="color:#3d92d6">"b"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"b"</span><span style="color:#000000">,</span><span style="color:#3d92d6">"a"</span><span style="color:#bf7078">))</span></div>
</div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">setkey<span style="color:#bf7078">(</span>g_ctpty<span style="color:#000000">,</span>ctpty_head<span style="color:#bf7078">)</span></div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">setkey<span style="color:#bf7078">(</span>g_iss<span style="color:#000000">,</span>iss_head<span style="color:#bf7078">)</span></div><div class="im">
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">merge<span style="color:#bf7078">(</span></div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)"> merge<span style="color:#bf7078">(</span>links<span style="color:#000000">,</span> g_ctpty<span style="color:#000000">,</span> all.x <span style="color:#000000">=</span> <span style="color:#9b704b">TRUE</span><span style="color:#000000">,</span> by <span style="color:#000000">=</span> <span style="color:#3d92d6">"ctpty_head"</span><span style="color:#bf7078">)</span><span style="color:#000000">,</span></div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)"> g_iss<span style="color:#000000">,</span></div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)"> all.x <span style="color:#000000">=</span> <span style="color:#9b704b">TRUE</span><span style="color:#000000">,</span> by <span style="color:#000000">=</span> <span style="color:#3d92d6">"iss_head"</span></div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(191,112,120)">)</div></div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">g_iss<span style="color:#000000">[</span>g_ctpty<span style="color:#000000">[</span>links<span style="color:#000000">]] # error</span></div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">links<span style="color:#000000">[</span>g_ctpty<span style="color:#000000">][</span>g_iss<span style="color:#000000">] # still error</span></div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">setkey<span style="color:#bf7078">(</span>links<span style="color:#000000">,</span>ctpty_head<span style="color:#000000">,</span>iss_head<span style="color:#bf7078">) # keys are needed</span></div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(107,107,107)">links<span style="color:#000000">[</span>g_ctpty<span style="color:#000000">][</span>g_iss<span style="color:#000000">] # how to get inner join?</span></div>
</div><div><span style><br></span></div><div>How do it not include the last line in the link? (Again, it works with merge). Many thanks.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Ivan</div></font></span><div>
<div class="h5"><div><br></div><div><br><div><div>On 18 Jun 2013, at 17:34, Eduard Antonyan <<a href="mailto:eduard.antonyan@gmail.com" target="_blank">eduard.antonyan@gmail.com</a>> wrote:</div><br><blockquote type="cite">
<div dir="ltr"><div><span style="font-family:arial,sans-serif;font-size:13px">Frank has already answered why you're getting the results you are.</span></div><div><span style="font-family:arial,sans-serif;font-size:13px"><br>
</span></div><div><span style="font-family:arial,sans-serif;font-size:13px">Re </span><span style="font-size:13px;font-family:arial,sans-serif">"how do I tell DT to do </span><u style="font-size:13px;font-family:arial,sans-serif">separate matchings at each join</u><span style="font-size:13px;font-family:arial,sans-serif">?":</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">Currently you have to use 'merge' or 'data.table', s</span>ee this - <a href="https://r-forge.r-project.org/tracker/?func=detail&atid=978&aid=4675&group_id=240" target="_blank">https://r-forge.r-project.org/tracker/?func=detail&atid=978&aid=4675&group_id=240</a> and the SO link inside.</div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jun 18, 2013 at 10:08 AM, Ivan Alves <span dir="ltr"><<a href="mailto:papucho@me.com" target="_blank">papucho@me.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="word-wrap:break-word">Hi Frank,<div><br></div><div>Many thanks for the thoughts.</div><div><div><br></div><div>It is something that has to do with the keys, that is for sure (see below). A double DT join does not understand that it has to do one join by one variable and the other by another variable: the output simply has lines where the two keys are the same (ctpty_head==iss_head),which is of course not optimal.</div>
<div><br></div><div>how do I tell DT to do <u>separate matchings at each join</u>? Setting <span style="color:rgb(107,107,107);font-family:Monaco;font-size:11px">setkey</span><span style="font-family:Monaco;font-size:11px;color:rgb(191,112,120)">(</span><span style="color:rgb(107,107,107);font-family:Monaco;font-size:11px">links</span><span style="font-family:Monaco;font-size:11px">,</span><span style="color:rgb(107,107,107);font-family:Monaco;font-size:11px">ctpty_head</span><span style="font-family:Monaco;font-size:11px">,</span><span style="color:rgb(107,107,107);font-family:Monaco;font-size:11px">iss_head</span><span style="font-family:Monaco;font-size:11px;color:rgb(191,112,120)">) </span>before the join does not work either.</div>
<div><br></div><div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">> </span>key(g_ctpty[links])</div><div style="margin:0px;font-size:11px;font-family:Monaco">
NULL</div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">> </span>key(g_ctpty)</div><div style="margin:0px;font-size:11px;font-family:Monaco">[1] "ctpty_head"</div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">> </span>key(g_iss)</div><div style="margin:0px;font-size:11px;font-family:Monaco">[1] "iss_head"</div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">> </span>key(links)</div><div style="margin:0px;font-size:11px;font-family:Monaco">NULL</div><div><br></div><div>
Hi Arunkumar,</div><div><br></div><div>An example would look like follows:</div><div><br></div><div><span style="color:rgb(29,172,114);font-family:Monaco;font-size:11px">g_ctpty</span> = data.table(ctpty_head=c("a","b","c"), ctpty_cty=c("US","DE","JP"))</div>
<div><div><span style="color:rgb(29,172,114);font-family:Monaco;font-size:11px">g_iss</span> = data.table(iss_head=c("a","b","c"), iss_cty=c("US","DE","JP"))</div>
</div><div><div><font color="#1dac72" face="Monaco"><span style="font-size:11px">links</span></font> = data.table(ctpty_head=c("a","b","c"), iss_head=c("b","b","a"))</div>
</div><div><br></div><div><span style="color:rgb(29,172,114);font-family:Monaco;font-size:11px">merge(</span></div><div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">+ </span> merge(links, g_ctpty, all.x = TRUE, by = "ctpty_head"),</div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">+ </span> g_iss,</div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">+ </span> all.x = TRUE, by = "iss_head"</div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">+ </span> )</div><div style="margin:0px;font-size:11px;font-family:Monaco"> iss_head ctpty_head ctpty_cty iss_cty</div>
<div style="margin:0px;font-size:11px;font-family:Monaco">1: a c JP US</div><div style="margin:0px;font-size:11px;font-family:Monaco">2: b a US DE</div><div style="margin:0px;font-size:11px;font-family:Monaco">
3: b b DE DE</div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">> </span>g_iss[g_ctpty[links]]</div><div style="margin:0px;font-size:11px;font-family:Monaco">
iss_head iss_cty ctpty_cty iss_head.1</div><div style="margin:0px;font-size:11px;font-family:Monaco">1: a US US b</div><div style="margin:0px;font-size:11px;font-family:Monaco">2: b DE DE b</div>
<div style="margin:0px;font-size:11px;font-family:Monaco">3: c JP JP a</div><div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">> </span>setkey(links,ctpty_head,iss_head)</div>
<div style="margin:0px;font-size:11px;font-family:Monaco;color:rgb(29,172,114)"><span style="color:#2766bd">> </span>g_iss[g_ctpty[links]]</div><div style="margin:0px;font-size:11px;font-family:Monaco"> iss_head iss_cty ctpty_cty iss_head.1</div>
<div style="margin:0px;font-size:11px;font-family:Monaco">1: a US US b</div><div style="margin:0px;font-size:11px;font-family:Monaco">2: b DE DE b</div><div style="margin:0px;font-size:11px;font-family:Monaco">
3: c JP JP a</div></div><div><div><div><br></div><div><div>On 17 Jun 2013, at 14:36, Frank Erickson <<a href="mailto:FErickson@psu.edu" target="_blank">FErickson@psu.edu</a>> wrote:</div>
<br><blockquote type="cite"><div dir="ltr">I think that key(g_ctpty[links]) == key(g_ctpty) == "ctpty_head", which is used when you do your second merge with [, instead of "iss_head". You can check this by running key(g_ctpty[links]) . --Frank</div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Jun 17, 2013 at 1:57 AM, Arunkumar Srinivasan <span dir="ltr"><<a href="mailto:aragorn168b@gmail.com" target="_blank">aragorn168b@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div>Since you have the data as well, why not provide it (a small part at least with which your issue is reproducible)? Isn't it much easier than to ask everyone who's willing to help to create a data and test your code?</div>
<div></div>
</div>
<div><div><br></div><div>Arun</div><div><br></div></div><div><p style="color:#a0a0a8">On Monday, June 17, 2013 at 8:54 AM, Ivan Alves wrote:</p>
<blockquote type="cite" style="border-left-style:solid;border-width:1px;margin-left:0px;padding-left:10px">
<span><div>Dear all,</div><div><br></div><div>I am not sure I understand the syntax for merging data.tables. I have keyed the two 'satelite' tables from which I want to match information to the main table 'links'</div>
<div><br></div><div> g_ctpty <- gultimate[,list(ctpty_head,ctpty_cty)]</div><div> setkey(g_ctpty,ctpty_head)</div><div> g_iss <- gultimate[,list(iss_head,iss_cty)]</div><div> setkey(g_iss,iss_head)</div><div><br>
</div><div>Why are the two below not equivalent?</div><div><br></div><div>This works:</div><div><br></div><div> data = merge(</div><div> merge(links, g_ctpty, all.x = TRUE, by = "ctpty_head"),</div>
<div>
g_iss,</div><div> all.x = TRUE, by = "iss_head"</div><div> ),</div><div><br></div><div>And this does not:</div><div><br></div><div> data = g_iss[g_ctpty[links]],</div><div><br></div><div>
Any guidance would be appreciated.</div><div>Kind regards,</div><div>Ivan</div><div>_______________________________________________</div><div>datatable-help mailing list</div><div><a href="mailto:datatable-help@lists.r-forge.r-project.org" target="_blank">datatable-help@lists.r-forge.r-project.org</a></div>
<div><a href="https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help" target="_blank">https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help</a></div></span>
</blockquote>
<div>
<br>
</div>
</div><br>_______________________________________________<br>
datatable-help mailing list<br>
<a href="mailto:datatable-help@lists.r-forge.r-project.org" target="_blank">datatable-help@lists.r-forge.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-project.org/cgi-bin/mailman/listinfo/datatable-help</a><br></blockquote></div><br></div>
</blockquote></div><br></div></div></div></div></div><br>_______________________________________________<br>
datatable-help mailing list<br>
<a href="mailto:datatable-help@lists.r-forge.r-project.org" target="_blank">datatable-help@lists.r-forge.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-project.org/cgi-bin/mailman/listinfo/datatable-help</a><br></blockquote></div><br></div>
</blockquote></div><br></div></div></div></div></blockquote></div><br></div>