title: Merge two data frames with different dimensions with partial overwrite in R
body: <p>I've spent the better part of a day on this but I keep getting stuck. This wouldn't take me very long using index-match-match in Excel, but I'm newer to R and merging data doesn't seem very straight-forward. I've searched the site and found similar problems but no solutions specific to this type of issue.</p>
  
  <p>I have two data frames. They have different lengths in both dimensions. <code>a</code> is 4x4 and <code>b</code> is 3x3. They partially overlap:</p>
  
  <pre><code>a &lt;- data.frame("ID" = c(1:4), "A" = c(21:24), "B" = c(31:34), "C" = c(41:44))
  a
  
    ID  A  B  C
  1  1 21 31 41
  2  2 22 32 42
  3  3 23 33 43
  4  4 24 34 44
  </code></pre>
  
  <p>and</p>
  
  <pre><code>b &lt;- data.frame("ID" = c(4:6), "C" = c(22:24), "D" = c(32:34))
  b
    ID  C  D
  1  4 22 32
  2  5 23 33
  3  6 24 34
  </code></pre>
  
  <p>I'm merging on "ID" number. My goal is to get them to look like</p>
  
  <pre><code>c &lt;- data.frame("ID" = c(1:6), "A" = c(21:24, NA, NA), "B" = c(31:34, NA, NA), "C" = c(41:43,22:24), "D" = c(NA, NA, NA, 32:34))
  c
  
   ID  A  B  C  D 
   1   21 31 41 NA 
   2   22 32 42 NA 
   3   23 33 43 NA 
   4   24 34 22 32 
   5   NA NA 23 33 
   6   NA NA 24 34
  </code></pre>
  
  <p>As you can see, the final data frame combines the two and assigns NA to the missing information. In column "C", I would like <code>b</code> to overwrite <code>a</code> where it has numerical values. In this example, the value in <code>c[4,3]</code> should change from 44 to 22.</p>
  
  <p>Most of this is simple enough. But getting column "C" correct has been a nightmare. I did the simple thing first:</p>
  
  <pre><code>merge(a, b, by = "ID", all = T)
  </code></pre>
  
  <p>It almost does the trick but ends up with duplicate row "C"s:</p>
  
  <pre><code>  ID  A  B C.x C.y  D
  1  1 21 31  41  NA NA
  2  2 22 32  42  NA NA
  3  3 23 33  43  NA NA
  4  4 24 34  44  22 32
  5  5 NA NA  NA  23 33
  6  6 NA NA  NA  24 34
  </code></pre>
  
  <p>This wouldn't be so bad if I could find out how to merge the duplicate rows correctly because then I could just run</p>
  
  <pre><code>merge(a[-4], b[-2], by = "ID", all = T)
    ID  A  B  D
  1  1 21 31 NA
  2  2 22 32 NA
  3  3 23 33 NA
  4  4 24 34 32
  5  5 NA NA 33
  6  6 NA NA 34
  </code></pre>
  
  <p>to merge everything else, then bring in the merged "C" after the fact.</p>
  
  <p>But I can't figure it out how to deal with this part of it:</p>
  
  <pre><code>merge(a[c(1,4)], b[c(1,2)], by = "ID", all = T)
    ID C.x C.y         ID C
  1  1  41  NA      1  1  41
  2  2  42  NA      2  2  42
  3  3  43  NA  -&gt;  3  3  43
  4  4  44  22      4  4  22
  5  5  NA  23      5  5  23
  6  6  NA  24      6  6  24
  </code></pre>
  
  <p>There's gotta be way.</p>
  
  <p>Thanks for your help!</p>