title: Overwrite values from selected columns and matching rows from one data frame into another, R
body: <p>I have 2 data frames, df1 and df2. Column names are the same in both data frames and column 'field_name' is a unique identifier (each row has a unique field name). df2 doesn't have as many rows as df1.</p>
  <p>Some values in df2 differ from those in df1, and I want to import these values to df1 so that the original values in df1 are overwritten with those from df2.</p>
  <p>The 'different' values that I want to pull into df1 will always be found in columns of df2 which contain the string '4' (definitely a string, has text before the number) or the string 'tgp'. For these columns, I want to replace the values in df1 with those from df2. However, this won't apply to every row: df2 is smaller than df1 so I need to match on rows so that the values are replaced only in rows of df1 which are also present in df2.</p>
  <p>Code here shows what I'm after:</p>
  <pre><code>df1 &lt;- data.frame(list(field_name = c(&quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;d&quot;),  A3=c(&quot;23&quot;, &quot;35&quot;, &quot;2&quot;, &quot;5&quot;), A4=c(&quot;94&quot;, &quot;120&quot;, &quot;67&quot;, &quot;5&quot;), B3=c(&quot;35&quot;, &quot;64&quot;, &quot;87&quot;, &quot;70&quot;), B4=c(&quot;99&quot;, &quot;76&quot;, &quot;22&quot;, &quot;83&quot;), tgp=c(&quot;1000&quot;, &quot;1542&quot;, &quot;758&quot;, &quot;964&quot;)))
  
  df2 &lt;- data.frame(list(field_name = c(&quot;a&quot;, &quot;b&quot;, &quot;d&quot;),       A3=c(&quot;13&quot;, &quot;28&quot;, &quot;10&quot;),      A4=c(&quot;56&quot;, &quot;11&quot;, &quot;16&quot;),       B3=c(&quot;42&quot;, &quot;58&quot;, &quot;91&quot;),       B4=c(&quot;13&quot;, &quot;64&quot;, &quot;12&quot;),       tgp=c(&quot;1154&quot;, &quot;1200&quot;, &quot;900&quot;)))
  
  df_result &lt;- data.frame(list(field_name = c(&quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;d&quot;), A3=c(&quot;23&quot;, &quot;35&quot;, &quot;2&quot;, &quot;5&quot;), A4=c(&quot;56&quot;, &quot;11&quot;, &quot;67&quot;, &quot;16&quot;), B3=c(&quot;35&quot;, &quot;64&quot;, &quot;87&quot;, &quot;70&quot;), B4=c(&quot;13&quot;, &quot;64&quot;, &quot;22&quot;, &quot;12&quot;), tgp=c(&quot;1154&quot;, &quot;1200&quot;, &quot;758&quot;, &quot;900&quot;)))
  </code></pre>
  <p>In this example, I've taken values from df2 for field names &quot;a&quot;, &quot;b&quot; and &quot;d&quot;, from columns 'A4', 'B4' and 'tgp', and overwritten the values in the same locations in df1. For this small example I could do it by hand with indexing but I have many columns containing the required strings, and many rows.</p>
  <p>I got this far (with help!) but it doesn't work because of the unequal number of rows in the two data frames:</p>
  <pre><code>columns &lt;- which(grepl(colnames(df1), pattern = &quot;4|tgp&quot;))
  rows &lt;- which(apply(df1, 1, function(x) which(x %in% intersect(df1$field_name, df2$field_name)))==T)
   
  
  new_df &lt;- df1
  for(col in columns){
    for (r in rows){
    new_df[r,col] &lt;- df2[r,col]
    }
  }
  </code></pre>
  <p>I'd love a vectorized solution if possible. I've tried playing with 'mutate_at' and 'replace' but so far haven't managed to get anything that works. The following...</p>
  <pre><code>df1 %&gt;%
    left_join(df2, by = &quot;field_name&quot;)
  </code></pre>
  <p>...from <a href="https://stackoverflow.com/questions/60234780/r-update-column-based-on-matching-rows-from-another-data-frame">this post</a> looked hopeful but I haven't yet managed to make the 'transmute' work.</p>
  <p>Thank you very much.</p>