<div dir="ltr"><div style><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 style><span style="font-family:arial,sans-serif;font-size:13px"><br>
</span></div><div style><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">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 class="h5"><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><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></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">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>