If a value of one column appears in any of three other columns, change the value of the respective c-CodePudding

I want to change the values per row in each of three columns Q10, Q11, Q12 to NA conditional on the appearance of one of these values based on the column Groups. In my example, Groupsin row 4 contains Group 4 same as Q11. Thus, I would like to change the value for df[4,2] to NA. I have tried to combine rowwise() with mutate() and case_when(), however doing so results in all three columns being populated by NAs.

df %>% 
  rowwise() %>%
  mutate(Q10 = case_when(Groups %in% Q10 ~ NA, TRUE ~ Q10),
         Q11 = case_when(Groups %in% Q11 ~ NA, TRUE ~ Q11),
         Q12 = case_when(Groups %in% Q12 ~ NA, TRUE ~ Q12))

Data:

structure(list(Q10 = structure(c(8L, 8L, 6L, 1L, 7L, 6L), .Label = c("Group 1", 
"Group 2", "Group 3", "Group 4", "Group 5", "Group 6", "Group 7", 
"Group 8", "Group 9"), class = "factor"), Q11 = structure(c(1L, 
6L, 5L, 4L, 7L, 6L), .Label = c("Group 1", "Group 2", "Group 3", 
"Group 4", "Group 5", "Group 6", "Group 7", "Group 8", "Group 9"
), class = "factor"), Q12 = structure(c(3L, 5L, 4L, 3L, 5L, 4L
), .Label = c("Group 1", "Group 2", "Group 3", "Group 4", "Group 5", 
"Group 6", "Group 7", "Group 8", "Group 9"), class = "factor"), 
    Groups = c("Group 2", "Group 9", "Group 2", "Group 4", "Group 6", 
    "Group 7")), class = c("rowwise_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -6L), groups = structure(list(.rows = structure(list(
    1L, 2L, 3L, 4L, 5L, 6L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame")))

CodePudding user response：

Someone should come along with a more elegant solution, but for now you can use this brute-force method that uses which to identify the row position of the matching values, then replace with NA:

for(xx in 1:3){
  df[which(df[xx] == df[4]), xx] <- NA
}

Output:

#   Q10     Q11     Q12     Groups 
# <fct>   <fct>   <fct>   <chr>  
# 1 Group 8 Group 1 Group 3 Group 2
# 2 Group 8 Group 6 Group 5 Group 9
# 3 Group 6 Group 5 Group 4 Group 2
# 4 Group 1 NA      Group 3 Group 4
# 5 Group 7 Group 7 Group 5 Group 6
# 6 Group 6 Group 6 Group 4 Group 7