title: How to merge (full join) a list of more than three data.tables correctly?
body: <p>When using <code>merge.data.table()</code> I get ugly error messages about duplicated columns names—strangely enough only if there are more than three data.tables. On the contrary standard <code>merge()</code> with <code>data.frame</code> versions works fine.</p>
  <p>I use <a href="https://stackoverflow.com/a/53439312/6574038">this code</a>, to achieve a full join avoid time-split columns with <code>merge.data.table()</code>.</p>
  <p>So, in <strong>base R</strong></p>
  <pre><code>Reduce(function(...) merge(..., all=TRUE), L)
  </code></pre>
  <p>runs fine, whereas in <strong>data.table</strong></p>
  <pre><code>Reduce(function(...)
    merge(..., all=TRUE, by=Reduce(intersect, lapply(L.dt, names))), L.dt)
  </code></pre>
  <p>trows the error:</p>
  <pre class="lang-none prettyprint-override"><code>  Error in merge.data.table(..., all = TRUE, by = Reduce(intersect, lapply(L.dt,  : 
    x has some duplicated column name(s): p.l01.x,p.l02.x,p.l03.x,p.l05.x,p.l06.x,p.l07.x,
  p.l08.x,p.l10.x,p.l11.x,p.l12.x,
  p.l01.y,p.l02.y,p.l03.y,p.l05.y,p.l06.y,p.l07.y,
  p.l08.y,p.l10.y,p.l11.y,p.l12.y. Please remove 
  or rename the duplicate(s) and try again.
  In addition: Warning message:
  In merge.data.table(..., all = TRUE, by = Reduce(intersect, lapply(L.dt,  :
  
   Error in merge.data.table(..., all = TRUE, by = Reduce(intersect, lapply(L.dt,  : 
    x has some duplicated column name(s): p.l01.x,p.l02.x,p.l03.x,p.l05.x,p.l06.x,p.l07.x,
  p.l08.x,p.l10.x,p.l11.x,p.l12.x,
  p.l01.y,p.l02.y,p.l03.y,p.l05.y,p.l06.y,p.l07.y,p.l08.y,p.l10.y,p.l11.y,p.l12.y. Please remove
  or rename the duplicate(s) and try again. 
  </code></pre>
  <p><strong>Note</strong> that the issue appears to be cumulative somehow...</p>
  <p>While</p>
  <pre><code>Reduce(function(...)
    merge(..., all=TRUE, by=Reduce(intersect, lapply(L.dt[1:2], names))), L.dt[1:2])
  
  Reduce(function(...)
    merge(..., all=TRUE, by=Reduce(intersect, lapply(L.dt[1:3], names))), L.dt[1:3])
  
  Reduce(function(...)
    merge(..., all=TRUE, by=Reduce(intersect, lapply(L.dt[1:4], names))), L.dt[1:4])
  </code></pre>
  <p>still runs fine,</p>
  <pre><code>Reduce(function(...)
    merge(..., all=TRUE, by=Reduce(intersect, lapply(L.dt[1:5], names))), L.dt[1:5])
  </code></pre>
  <p>throws a warning, and from here</p>
  <pre><code>Reduce(function(...)
    merge(..., all=TRUE, by=Reduce(intersect, lapply(L.dt[1:6], names))), L.dt[1:6])
  </code></pre>
  <p>finally the error starts.</p>
  <p>Thus, <strong><code>merge.data.table()</code> appears to be working fine only until the number of data.tables exceeds 3??</strong> Any ideas how I could solve this issue?</p>
  <p><em>Note:</em> With <a href="https://stackoverflow.com/q/53439279/6574038">easy example data</a> no error appears. So it could be caused by my data. But I have no idea why—<em>standard <code>merge()</code> works fine!</em> My real data has about dim=15.000*500, some variable names match throughout the list, others are individual. This MCVE seems to reproduce the issue quite well.</p>
  <h1>Data and code to reproduce</h1>
  <pre><code># names vector mimicking my data   
  nm &lt;- list(c(&quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d29&quot;, &quot;p.d31&quot;, &quot;p.n03&quot;, &quot;p.n04&quot;, 
  &quot;p.n05&quot;, &quot;p.d36&quot;, &quot;p.d40&quot;, &quot;p.d41&quot;, &quot;p.d45&quot;, &quot;p.d46&quot;, &quot;p.d50&quot;, 
  &quot;p.d51&quot;, &quot;p.d55&quot;, &quot;p.d56&quot;, &quot;p.d60&quot;, &quot;p.d61&quot;, &quot;p.d65&quot;, &quot;p.d66&quot;, 
  &quot;p.d70&quot;, &quot;p.d71&quot;, &quot;p.n08&quot;), c(&quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.c44&quot;, &quot;p.l01&quot;, 
  &quot;p.l02&quot;, &quot;p.l03&quot;, &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;, &quot;p.l08&quot;, &quot;p.l10&quot;, 
  &quot;p.l11&quot;, &quot;p.l12&quot;, &quot;p.l13&quot;, &quot;p.l15&quot;, &quot;p.l16&quot;, &quot;p.l17&quot;, &quot;p.l18&quot;, 
  &quot;p.l20&quot;, &quot;p.l21&quot;, &quot;p.l22&quot;, &quot;p.l23&quot;, &quot;p.l25&quot;), c(&quot;p.d17m&quot;, &quot;p.d17&quot;, 
  &quot;p.c44&quot;, &quot;p.l01&quot;, &quot;p.l02&quot;, &quot;p.l03&quot;, &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;, 
  &quot;p.l08&quot;, &quot;p.l10&quot;, &quot;p.l11&quot;, &quot;p.l12&quot;, &quot;p.l13&quot;, &quot;p.l15&quot;, &quot;p.l16&quot;, 
  &quot;p.l17&quot;, &quot;p.l18&quot;, &quot;p.l20&quot;, &quot;p.l21&quot;, &quot;p.l22&quot;, &quot;p.l23&quot;, &quot;p.l25&quot;
  ), c(&quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.c44&quot;, &quot;p.l01&quot;, &quot;p.l01r&quot;, &quot;p.l02&quot;, 
  &quot;p.l03&quot;, &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;, &quot;p.l08&quot;, &quot;p.l10&quot;, &quot;p.l11&quot;, 
  &quot;p.l12&quot;, &quot;p.l13&quot;, &quot;p.l15&quot;, &quot;p.l16&quot;, &quot;p.l17&quot;, &quot;p.l18&quot;, &quot;p.l20&quot;, 
  &quot;p.l21&quot;, &quot;p.l22&quot;, &quot;p.l23&quot;), c(&quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.c44&quot;, &quot;p.l01&quot;, 
  &quot;p.l01r&quot;, &quot;p.l02&quot;, &quot;p.l03&quot;, &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;, &quot;p.l08&quot;, 
  &quot;p.l10&quot;, &quot;p.l11&quot;, &quot;p.l12&quot;, &quot;p.l13&quot;, &quot;p.l15&quot;, &quot;p.l16&quot;, &quot;p.l17&quot;, 
  &quot;p.l18&quot;, &quot;p.l20&quot;, &quot;p.l21&quot;, &quot;p.l22&quot;, &quot;p.l23&quot;), c(&quot;pdate.&quot;, &quot;p.d17m&quot;, 
  &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, 
  &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.l01&quot;, &quot;p.l01r&quot;, &quot;p.l02&quot;, &quot;p.l03&quot;, 
  &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;, &quot;p.l08&quot;, &quot;p.l10&quot;, &quot;p.l11&quot;, &quot;p.l12&quot;
  ), c(&quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, 
  &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.l01&quot;, 
  &quot;p.l01r&quot;, &quot;p.l02&quot;, &quot;p.l03&quot;, &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;, &quot;p.l08&quot;, 
  &quot;p.l10&quot;, &quot;p.l11&quot;, &quot;p.l12&quot;), c(&quot;pdate.&quot;, &quot;p.d200&quot;, &quot;p.d201&quot;, &quot;p.d17m&quot;, 
  &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, 
  &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, 
  &quot;p.l01&quot;, &quot;p.l01r&quot;, &quot;p.l02&quot;, &quot;p.l03&quot;, &quot;p.l05&quot;), c(&quot;pdate.&quot;, &quot;p.d17m&quot;, 
  &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, 
  &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, 
  &quot;p.l01&quot;, &quot;p.l01r&quot;, &quot;p.l02&quot;, &quot;p.l03&quot;, &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;
  ), c(&quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, 
  &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, 
  &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, &quot;p.l01&quot;, &quot;p.l01r&quot;, &quot;p.l02&quot;, &quot;p.l03&quot;, 
  &quot;p.l05&quot;, &quot;p.l06&quot;, &quot;p.l07&quot;), c(&quot;plingu.&quot;, &quot;pdate.&quot;, &quot;p.d17m&quot;, 
  &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, 
  &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, 
  &quot;p.c60&quot;, &quot;p.c61&quot;, &quot;p.c62&quot;, &quot;p.c63&quot;, &quot;p.c64&quot;, &quot;p.c65&quot;), c(&quot;hab_ch.&quot;, 
  &quot;plingu.&quot;, &quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, 
  &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, 
  &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, &quot;p.c60&quot;, &quot;p.c61&quot;, &quot;p.c62&quot;, &quot;p.c63&quot;, 
  &quot;p.c64&quot;), c(&quot;hab_ch.&quot;, &quot;plingu.&quot;, &quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, 
  &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, 
  &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, &quot;p.c60&quot;, 
  &quot;p.c61&quot;, &quot;p.c62&quot;, &quot;p.c63&quot;, &quot;p.c64&quot;), c(&quot;hab_ch.&quot;, &quot;plingu.&quot;, 
  &quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, 
  &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c100&quot;, &quot;p.c101&quot;, 
  &quot;p.c102&quot;, &quot;p.c103&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, &quot;p.l01&quot;
  ), c(&quot;hab_ch.&quot;, &quot;plingu.&quot;, &quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d110a&quot;, 
  &quot;p.d110b&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, 
  &quot;p.d165&quot;, &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, 
  &quot;p.c50&quot;, &quot;p.l01&quot;, &quot;p.l01r&quot;, &quot;p.l02&quot;), c(&quot;hab_ch.&quot;, &quot;plingu.&quot;, 
  &quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d110a&quot;, &quot;p.d110b&quot;, &quot;p.d160&quot;, 
  &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, &quot;p.d167&quot;, 
  &quot;p.c44&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, &quot;p.l01&quot;, &quot;p.l01r&quot;, 
  &quot;p.l02&quot;), c(&quot;hab_ch.&quot;, &quot;plingu.&quot;, &quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, 
  &quot;p.d110a&quot;, &quot;p.d110b&quot;, &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, 
  &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c100&quot;, &quot;p.c101&quot;, 
  &quot;p.c102&quot;, &quot;p.c103&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;), c(&quot;hab_ch.&quot;, 
  &quot;plingu.&quot;, &quot;pdate.&quot;, &quot;p.d17m&quot;, &quot;p.d17&quot;, &quot;p.d110a&quot;, &quot;p.d110b&quot;, 
  &quot;p.d160&quot;, &quot;p.d161&quot;, &quot;p.d162&quot;, &quot;p.d163&quot;, &quot;p.d164&quot;, &quot;p.d165&quot;, &quot;p.d166&quot;, 
  &quot;p.d167&quot;, &quot;p.c44&quot;, &quot;p.c47&quot;, &quot;p.c48&quot;, &quot;p.c49&quot;, &quot;p.c50&quot;, &quot;p.l01&quot;, 
  &quot;p.l01r&quot;, &quot;p.l02&quot;))
  
  n &lt;- 10  # row numbers
  
  fun &lt;- function(x) {
    # fun() produces a n x 22 data.frame
    set.seed(x)
    d &lt;- data.frame(
      cbind(id=1:n, yr=x, 
            matrix(rnorm(n*(20)), n)))
  }
  
  tmp &lt;- setNames(lapply(1:6, fun), paste0(&quot;df&quot;, 1:6))  # produce six data frames
  
  list2env(tmp, globalenv())  # get data frames into globalenv
  
  # insert extra columns into some df's
  df3 &lt;- cbind(id=df3[, 1], foo=sample(1e3:2e3, nrow(df3), replace=TRUE), df3[, -1])
  df4 &lt;- cbind(id=df4[, 1], foo=sample(1e3:2e3, nrow(df4), replace=TRUE), df4[, -1])
  df5 &lt;- cbind(id=df5[, 1], foo=sample(1e3:2e3, nrow(df5), replace=TRUE), df5[, -1])
  df6 &lt;- cbind(id=df6[, 1], foo=sample(1e3:2e3, nrow(df5), replace=TRUE), df6[, -1])
  
  # giving var names:
  names(df1)[3:22] &lt;- nm[[1]][3:22]
  names(df2)[3:22] &lt;- nm[[2]][3:22]
  names(df3)[4:23] &lt;- nm[[3]][4:23]
  names(df4)[4:23] &lt;- nm[[4]][4:23]
  names(df5)[4:23] &lt;- nm[[5]][4:23]
  names(df5)[4:23] &lt;- nm[[6]][4:23]
  
  # list of data.frames:
  L &lt;- list(df1, df2, df3, df4, df5, df6)
  
  # list of data.tables:
  library(data.table)
  L.dt &lt;- lapply(L, function(x) setkeyv(data.table(x), c(&quot;id&quot;, &quot;yr&quot;)))
  </code></pre>
  <p><sub><em>Also related:</em> <a href="https://stackoverflow.com/a/8097519/6574038">1</a>, <a href="https://stackoverflow.com/a/13274291/6574038">2</a></sub></p>