Replace multiple values in a dataframe with NA based on conditions given in another dataframe in R

user_11417996 0
Viewed 334

Using R, I'd like to replace numeric values in a data frame with NA's based on conditions contained in another data frame:

# An example data frame with numeric values I want to  
# change to `NA` based on values given in another data frame.
df1 <- 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 <- 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) & 
    df1$subgroup %in% unique(df2$subgroup), 
    unique(df2$cols)] <- NA

# The incorrect result of my approach.
print(df1)
# A tibble: 4 × 5
  col_1 col_2 col_3 group subgroup
  <dbl> <dbl> <dbl> <chr> <chr>   
1    NA    NA    NA A     p       
2    NA    NA    NA A     q       
3    NA    NA    NA B     p       
4    NA    NA    NA B     q

Created on 2021-09-20 by the reprex package (v2.0.1)

My strategy was to subset df1 using the TRUE indices where df1 observations matched df2 and use ]<-NA 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.

How can I do this functionally/programmatically without manual replacement? This example dataset is small enough to use ]<- methods for each value I'd like to replace, but I'd like to do this functionally on a larger, more complicated dataset.

Solutions and caveats: 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.

# 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 <- tibble::tribble(
  ~col_1, ~col_2, ~col_3, ~group, ~subgroup,
  7, 4, 9, "A", "p",
  1, 3, 5, "A", "p",
  6, 8, 5, "A", "q",
  5, 3, 3, "B", "p",
  1, 7, 7, "B", "q"
)

# Conditions were added in rows 1 and 2
# to indicate which values to replace
# in df1 with NA.
df2 <- tibble::tribble(
  ~group, ~subgroup, ~cols,
  "A",    "p",       "col_1",
  "A",    "p",       "col_2",
  "A",    "q",       "col_1",
  "A",    "q",       "col_3",
  "B",    "p",       "col_2",
  "B",    "p",       "col_3"
)

# Modifications of @Ronak's solution
df1 <- as.data.frame(df1)
df2 <- as.data.frame(df2)

key1 <- lapply(
  setNames(names(df1)[grep("col_\\d", x = names(df1))], 1:3),
  function(x) {
    paste(x, df1$group, df1$subgroup)
  }
)

key2 <- with(df2, paste(cols, group, subgroup))

indices <- lapply(
  key1,
  function(x) {
    which(x %in% key2)
  }
)

indices <- indices[sapply(indices, function(x) length(x) > 0)]

selection <- lapply(
  1:length(indices),
  function(x) {
    cbind(indices[[x]], as.numeric(names(indices)[x]))
  }
)

selection <- do.call(rbind, selection)
df1[selection] <- 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
tidyverse r
2 Answers

Here is one method to assign i.e. loop across columns that starts_with 'col' in first dataset ('df1'), create a single string vector by pasteing the 'group', 'subgroup' and the corresponding column name (cur_column()), check if that elements are %in% the pasted rows of 'df2' to create logical vector. Use that in replace to replace those elements to NA

library(dplyr)
library(stringr)
library(purrr)
df1 <- df1 %>% 
   mutate(across(starts_with('col'), 
   ~ replace(., str_c(group, subgroup, cur_column()) %in%  
        invoke(str_c, c(df2, sep = '')), NA) ))

-output

df1
# A tibble: 4 x 5
  col_1 col_2 col_3 group subgroup
  <dbl> <dbl> <dbl> <chr> <chr>   
1     1     3     5 A     p       
2    NA     8    NA A     q       
3     5    NA    NA B     p       
4     1     7     7 B     q
user_37322710

A base R option creating a matrix of row/column index to replace values in df1.

key1 <- with(df1, paste(group, subgroup))
key2 <- with(df2, paste(group, subgroup))
df1 <- data.frame(df1)
df1[cbind(match(key2, key1), match(df2$cols, names(df1)))] <- NA
df1

#  col_1 col_2 col_3 group subgroup
#1     1     3     5     A        p
#2    NA     8    NA     A        q
#3     5    NA    NA     B        p
#4     1     7     7     B        q
user_39629140
Related