Replace values in multiple columns with NA based on value in a different column

Question

I have a tibble...

# A tibble: 20 x 6
      id   X_1   Y_1 number   X_2   Y_2
   <int> <dbl> <dbl>  <dbl> <dbl> <dbl>
 1     1     1     3      1     1     3
 2     1     1     3      0     1     3
 3     2     2     4      1     2     4
 4     2     2     4      0     2     4
 5     3     1     3      1     1     3
 6     3     1     3      0     1     3

I want to make all values equal NA if the value in the number column equals 1, but only in columns ending "_1" (so X_1 and Y_1).

I would also like to do the opposite in _2 columns (i.e. rows where number equals zero become NA).

It should end up looking like this...

# A tibble: 20 x 6
      id   X_1   Y_1 number   X_2   Y_2
   <int> <dbl> <dbl>  <dbl> <dbl> <dbl>
 1     1    NA    NA      1     1     3
 2     1     1     3      0     1     3
 3     2    NA    NA      1     2     4
 4     2     2     4      0     2     4
 5     3    NA    NA      1     1     3
 6     3     1     3      0     1     3

I tried the following...

df %>% mutate_at(vars(contains("_1")), .funs = list(~if_else(number == 1, NA_real_, .)))

But that didn't work.

I work mostly using tidyverse, so tidyverse solution would be preferable.

Answer 1

Here a solution that actually evaluates if the variable number is 0 or 1 (previous solutions evaluated whether the varible that end with "_1" or "_2" are 1 or 0).

library(dplyr)
df %>% 
  mutate(across((ends_with("_1")), ~ na_if(number, 1)),
        (across((ends_with("_2")), ~ na_if(number, 0))))

# A tibble: 6 x 6
     id   X_1   Y_1 number   X_2   Y_2
  <int> <int> <int>  <int> <int> <int>
1     1    NA    NA      1     1     1
2     1     0     0      0    NA    NA
3     2    NA    NA      1     1     1
4     2     0     0      0    NA    NA
5     3    NA    NA      1     1     1
6     3     0     0      0    NA    NA

---

Edit (keep original values)

df %>% 
  mutate(across((ends_with("_1")), ~if_else(number == 1, NA_integer_, .))) %>% 
  mutate(across((ends_with("_2")), ~if_else(number == 0, NA_integer_, .)))

# A tibble: 6 x 6
     id   X_1   Y_1 number   X_2   Y_2
  <int> <int> <int>  <int> <int> <int>
1     1    NA    NA      1     1     3
2     1     1     3      0    NA    NA
3     2    NA    NA      1     2     4
4     2     2     4      0    NA    NA
5     3    NA    NA      1     1     3
6     3     1     3      0    NA    NA

---

Data

df <- tibble::tribble(
        ~id, ~X_1, ~Y_1, ~number, ~X_2, ~Y_2,
         1L,   1L,   3L,      1L,   1L,   3L,
         1L,   1L,   3L,      0L,   1L,   3L,
         2L,   2L,   4L,      1L,   2L,   4L,
         2L,   2L,   4L,      0L,   2L,   4L,
         3L,   1L,   3L,      1L,   1L,   3L,
         3L,   1L,   3L,      0L,   1L,   3L
        )

Answer 2

if yor data is large, speed may be gained using the data.table-package like this

library( data.table )
#first make your data a data.table, using `setDT( mydata )`
cols <- grep( "_1$", names(DT), value = TRUE )
for(col in cols) set(dt, i=which(dt[[col]]==1), j=cols, value=NA)
cols <- grep( "_2$", names(DT), value = TRUE )
for(col in cols) set(dt, i=which(dt[[col]]==0), j=cols, value=NA)