Chaning values to NA based on multiple columns

April 2, 2024

I have a data frame where I wish to change any values from a 0 to an NA but only of the values of paired columns are both 0. I have attached an example below of what I currently have and what I am trying to return. I think to do this its best to pair off the columns we want to check? then once we have identified the positions we want we can simply replace the 0s with NAs. I also thought there might be a way we couple use something like sapply but wasn’t sure how we could pair off columns ‘a’ and columns ‘b’.

df
a1   a2    b1   b2
1     3    0    6
7     9    0    0    
5     5    4    6    
0     0    0    0     
0     0    6    0

What I’m trying to achieve;

newdf
a1   a2    b1   b2
1     3    0    6
7     9    NA    NA    
5     5    4    6    
NA    NA   NA   NA     
NA    NA   6    0

>Solution :

How about this:

unname(split.default(quux, sub("\\d", "", names(quux)))) |>
  lapply(function(z) { z[rowSums(z > 0) == 0,] <- NA; z; }) |>
  do.call(cbind, args = _)
#   a1 a2 b1 b2
# 1  1  3  0  6
# 2  7  9 NA NA
# 3  5  5  4  6
# 4 NA NA NA NA
# 5 NA NA  6  0

Data

quux <- structure(list(a1 = c(1L, 7L, 5L, 0L, 0L), a2 = c(3L, 9L, 5L, 0L, 0L), b1 = c(0L, 0L, 4L, 0L, 6L), b2 = c(6L, 0L, 6L, 0L, 0L)), class = "data.frame", row.names = c(NA, -5L))