Replace values in multiple columns based on values from preceding column

user_3746427 0
Viewed 79

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)

r
2 Answers

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
user_59645570

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
user_37322710
Related