title: Replace multiple values in a dataframe with NA based on conditions given in another dataframe in R
body: <p>Using R, I'd like to replace numeric values in a data frame with NA's based on conditions contained in another data frame:</p>
  <pre class="lang-r prettyprint-override"><code># An example data frame with numeric values I want to  
  # change to `NA` based on values given in another data frame.
  df1 &lt;- tibble::tribble(
    ~col_1, ~col_2, ~col_3, ~group, ~subgroup,
    1,      3,      5,     'A',    'p',
    6,      8,      5,     'A',    'q',
    5,      3,      3,     'B',    'p',
    1,      7,      7,     'B',    'q'
  )
  
  # A second data frame containing conditions  
  # to be used for subsetting the first data frame.
  df2 &lt;- tibble::tribble(
    ~group, ~subgroup, ~cols,
    'A',    'q',       'col_1',
    'A',    'q',       'col_3',
    'B',    'p',       'col_2', 
    'B',    'p',       'col_3'
  )
  
  # My problematic approach to subsetting df1 and replacing 
  # values with `NA` based on the conditions given in df2.
  df1[df1$group %in% unique(df2$group) &amp; 
      df1$subgroup %in% unique(df2$subgroup), 
      unique(df2$cols)] &lt;- NA
  
  # The incorrect result of my approach.
  print(df1)
  # A tibble: 4 × 5
    col_1 col_2 col_3 group subgroup
    &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;chr&gt; &lt;chr&gt;   
  1    NA    NA    NA A     p       
  2    NA    NA    NA A     q       
  3    NA    NA    NA B     p       
  4    NA    NA    NA B     q       
  </code></pre>
  <p><sup>Created on 2021-09-20 by the <a href="https://reprex.tidyverse.org" rel="nofollow noreferrer">reprex package</a> (v2.0.1)</sup></p>
  <p>My strategy was to subset df1 using the TRUE indices where df1 observations matched df2 and use <code>]&lt;-NA</code> to replace those observations with NA's. However, my approach selected all the observations rather than the desired result of only replacing the observations indicated in df2.</p>
  <p>How can I do this functionally/programmatically without manual replacement? This example dataset is small enough to use <code>]&lt;-</code> methods for each value I'd like to replace, but I'd like to do this functionally on a larger, more complicated dataset.</p>
  <p><strong>Solutions and caveats:</strong> both solutions offered by @Ronak and @akrun worked for the example data set in this question. However, after discovering rare cases of duplicated subgroup and group values in my real data set I found that only the solution by @akrun worked. Below I have added another example that recreates the rare cases I observed in my real data and added a modification of the solution by @Ronak that makes it work for these duplications.</p>
  <pre class="lang-r prettyprint-override"><code># Unique numeric observations were added
  # in rows 1 and 2 with group and subgroup
  # values that are duplicated with existing
  # group and subgroup values.
  df1 &lt;- tibble::tribble(
    ~col_1, ~col_2, ~col_3, ~group, ~subgroup,
    7, 4, 9, &quot;A&quot;, &quot;p&quot;,
    1, 3, 5, &quot;A&quot;, &quot;p&quot;,
    6, 8, 5, &quot;A&quot;, &quot;q&quot;,
    5, 3, 3, &quot;B&quot;, &quot;p&quot;,
    1, 7, 7, &quot;B&quot;, &quot;q&quot;
  )
  
  # Conditions were added in rows 1 and 2
  # to indicate which values to replace
  # in df1 with NA.
  df2 &lt;- tibble::tribble(
    ~group, ~subgroup, ~cols,
    &quot;A&quot;,    &quot;p&quot;,       &quot;col_1&quot;,
    &quot;A&quot;,    &quot;p&quot;,       &quot;col_2&quot;,
    &quot;A&quot;,    &quot;q&quot;,       &quot;col_1&quot;,
    &quot;A&quot;,    &quot;q&quot;,       &quot;col_3&quot;,
    &quot;B&quot;,    &quot;p&quot;,       &quot;col_2&quot;,
    &quot;B&quot;,    &quot;p&quot;,       &quot;col_3&quot;
  )
  
  # Modifications of @Ronak's solution
  df1 &lt;- as.data.frame(df1)
  df2 &lt;- as.data.frame(df2)
  
  key1 &lt;- lapply(
    setNames(names(df1)[grep(&quot;col_\\d&quot;, x = names(df1))], 1:3),
    function(x) {
      paste(x, df1$group, df1$subgroup)
    }
  )
  
  key2 &lt;- with(df2, paste(cols, group, subgroup))
  
  indices &lt;- lapply(
    key1,
    function(x) {
      which(x %in% key2)
    }
  )
  
  indices &lt;- indices[sapply(indices, function(x) length(x) &gt; 0)]
  
  selection &lt;- lapply(
    1:length(indices),
    function(x) {
      cbind(indices[[x]], as.numeric(names(indices)[x]))
    }
  )
  
  selection &lt;- do.call(rbind, selection)
  df1[selection] &lt;- NA
  df1
  #   col_1 col_2 col_3 group subgroup
  # 1    NA    NA     9     A        p
  # 2    NA    NA     5     A        p
  # 3    NA     8    NA     A        q
  # 4     5    NA    NA     B        p
  # 5     1     7     7     B        q
  </code></pre>