Replace values in multiple columns based on values from preceding column

Question

My sample data looks like this (real data is much bigger)

library(tidyverse, warn.conflicts = F)
df <- tibble(chr_x = c(0,0,1,1,1,4,4,4,5,5,6,7),
             pos_x = c(1,2,3,4,5,6,7,8,9,0,1,2),
             chr_y = c(1,2,3,3,3,2,1,1,0,0,1,5),
             pos_some = c(1,2,3,4,5,6,7,8,9,0,1,2))

I need to replace the certain values in some columns (let's say starting with chr) to NA that I did this way

df %>% mutate_at(vars(starts_with("chr")), ~ na_if(., 0))
#> # A tibble: 12 x 4
#>    chr_x pos_x chr_y pos_some
#>    <dbl> <dbl> <dbl>    <dbl>
#>  1    NA     1     1        1
#>  2    NA     2     2        2
#>  3     1     3     3        3
#>  4     1     4     3        4
#>  5     1     5     3        5
#>  6     4     6     2        6
#>  7     4     7     1        7
#>  8     4     8     1        8
#>  9     5     9    NA        9
#> 10     5     0    NA        0
#> 11     6     1     1        1
#> 12     7     2     5        2

The next part is where I am stuck at. Now I need to replace the values in the succeeding columns to NA where the values are NA in the above columns. How can I do that? The resulting df should look like this

#> # A tibble: 12 x 4
#>    chr_x pos_x chr_y pos_some
#>    <dbl> <dbl> <dbl>    <dbl>
#>  1    NA     NA     1        1
#>  2    NA     NA     2        2
#>  3     1     3     3        3
#>  4     1     4     3        4
#>  5     1     5     3        5
#>  6     4     6     2        6
#>  7     4     7     1        7
#>  8     4     8     1        8
#>  9     5     9    NA        NA
#> 10     5     0    NA        NA
#> 11     6     1     1        1
#> 12     7     2     5        2

^{Created on 2020-05-21 by the reprex package (v0.3.0)}

tmfmnk · Accepted Answer · 2020-05-21 19:25:34Z

4

One option utilizing dplyr and purrr could be:

bind_cols(df %>%
           select(1) %>%
           mutate_all(~ na_if(., 0)),
          map_dfc(.x = 2:length(df),
                  ~ df %>% 
                   mutate_at(vars(starts_with("chr")), ~ na_if(., 0)) %>%
                   transmute_at(vars(.x), ~ replace(., !!is.na(select(., .x - 1)), NA))))

   chr_x pos_x chr_y pos_some
   <dbl> <dbl> <dbl>    <dbl>
 1    NA    NA     1        1
 2    NA    NA     2        2
 3     1     3     3        3
 4     1     4     3        4
 5     1     5     3        5
 6     4     6     2        6
 7     4     7     1        7
 8     4     8     1        8
 9     5     9    NA       NA
10     5     0    NA       NA
11     6     1     1        1
12     7     2     5        2

edited May 21, 2020 at 19:25

answered May 21, 2020 at 19:11

tmfmnk

40.4k4 gold badges54 silver badges73 bronze badges

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

1 Comment

nsinghphd Over a year ago

Thank you for the solution, it works but it seems a bit more complicated.

akrun · Accepted Answer · 2020-05-21 20:07:53Z

3

We can use base R. Create an index of the 'chr' columns ('i1'), subset the columns 'chr', convert the values corresponding to 0 to NA ('i2') and update the 'i1' as well as positions that are next ('i2') by multiplying with i2

i1 <- which(startsWith(names(df), 'chr'))
i2 <- NA^(df[i1] == 0)
df[i1] <- df[i1] * i2
df[i1 + 1] <- df[i1 + 1] * i2
df
# A tibble: 12 x 4
#   chr_x pos_x chr_y pos_some
#   <dbl> <dbl> <dbl>    <dbl>
# 1    NA    NA     1        1
# 2    NA    NA     2        2
# 3     1     3     3        3
# 4     1     4     3        4
# 5     1     5     3        5
# 6     4     6     2        6
# 7     4     7     1        7
# 8     4     8     1        8
# 9     5     9    NA       NA
#10     5     0    NA       NA
#11     6     1     1        1
#12     7     2     5        2

edited May 21, 2020 at 20:07

answered May 21, 2020 at 20:02

akrun

891k38 gold badges590 silver badges700 bronze badges

3 Comments

Shan R Over a year ago

Such a beautiful solution.

nsinghphd Over a year ago

This is very nice. I have not seen this notation before NA^(df[i1] == 0) can you explain a bit? It seems it replaces the matching values with NA and everything else to 1.

akrun Over a year ago

@cropgen It is based on the NA^0 and NA^1 which returns 1 and NA respectively. THe logical vector TRUE/FALSE (df[i1] == 0) is internally 1/0 and thus gets the behaviour you noticed

Collectives™ on Stack Overflow

Replace values in multiple columns based on values from preceding column

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related