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.

if the number column equals 1 You mean rows not columns, right? Your expected output for Y_1 is off too. Values have 3 but are replaced with NA? — NelsonGon
– NelsonGon, Commented Jun 15, 2020 at 11:58

Ahorn · Accepted Answer · 2020-06-15 12:54:08Z

5

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
        )

edited Jun 15, 2020 at 12:54

answered Jun 15, 2020 at 12:11

Ahorn

3,9141 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eric Over a year ago

Can you briefly explain what the ~ is doing in your solution?

Ahorn Over a year ago

It is a shortcut for passing an anonymous function, from the advanced R book: "Anonymous functions are very useful, but the syntax is verbose. So purrr supports a special shortcut: map_dbl(mtcars, ~ length(unique(.x)))". So in our case we do not have to type out " function(x) na_if(number, 1))" but can replace the "function(x)" with "~".

Tom Over a year ago

I just realised that all the values have changed to 0 or 1 when I wanted them to retain their original value. For example, row 2 should still be 1, 3 for X_1 and Y_1 respectively. All the NAs are in the right place, but I didn't want to replace any other values. Do you have any idea how to solve that?

Wimpel · Accepted Answer · 2020-06-15 12:46:37Z

0

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)

answered Jun 15, 2020 at 12:46

Wimpel

27.9k1 gold badge25 silver badges40 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related