How to replace values in differents columns with values in another column? (R)

Question

library(tidyverse)

reprex for you to reproduce:

library(tidyverse)

tibble(
  x1 = c(1, 2, NA, NA, 5),
  y1 = c(4, 3, NA, NA, 7),
  x2 = c(NA, NA, 6, 7, NA),
  y2 = c(NA, NA, 2, 4, NA),
  replace1 = c("A", "B", "C", "D", "E"),
  replace2 = c("F", "G", "H", "I", "J")
)

I have this dataframe:

# A tibble: 5 x 6
     x1    y1    x2    y2 replace1 replace2
  <dbl> <dbl> <dbl> <dbl> <chr>    <chr>   
1     1     4    NA    NA A        F       
2     2     3    NA    NA B        G       
3    NA    NA     6     2 C        H       
4    NA    NA     7     4 D        I       
5     5     7    NA    NA E        J

I need the dataframe to be like this, which tidyverse pipeline will get me that?.

# A tibble: 5 x 6
  x1    y1    x2    y2    replace1 replace2
  <chr> <chr> <chr> <chr> <chr>    <chr>   
1 1     4     A     F     A        F       
2 2     3     B     G     B        G       
3 C     H     6     2     C        H       
4 D     I     7     4     D        I       
5 5     7     E     J     E        J

akrun · Accepted Answer · 2021-02-05 21:54:37Z

We can use

library(dplyr)
library(stringr)
df1 %>% 
     mutate(across(1:4, ~ coalesce(as.character(.), 
        get(str_replace(cur_column(), "\\D+", "replace")))))

-output

# A tibble: 5 x 6
#  x1    y1    x2    y2    replace1 replace2
#  <chr> <chr> <chr> <chr> <chr>    <chr>   
#1 1     4     F     F     A        F       
#2 2     3     G     G     B        G       
#3 C     C     6     2     C        H       
#4 D     D     7     4     D        I       
#5 5     7     J     J     E        J

Or if it is based on 'x', 'y'

 df1 %>% 
     mutate(replace_x = replace1, replace_y = replace2) %>% 
     mutate(across(1:4, ~ coalesce(as.character(.), 
         get(str_replace(cur_column(), "(\\D+)\\d+", "replace_\\1"))))) %>%   
     select(-matches('replace_[xy]'))
# A tibble: 5 x 6
#  x1    y1    x2    y2    replace1 replace2
#  <chr> <chr> <chr> <chr> <chr>    <chr>   
#1 1     4     A     F     A        F       
#2 2     3     B     G     B        G       
#3 C     H     6     2     C        H       
#4 D     I     7     4     D        I       
#5 5     7     E     J     E        J

Ronak Shah · Accepted Answer · 2021-02-06 08:54:06Z

1

Not tidy but a base R option with apply.

cols <- grep('replace', names(df))
df[] <- trimws(t(apply(df, 1, function(x) {x[is.na(x)] <- x[cols];x})))

#   x1    y1    x2    y2  replace1 replace2
#  <chr> <chr> <chr> <chr> <chr>    <chr>   
#1 1     4     A     F     A        F       
#2 2     3     B     G     B        G       
#3 C     H     6     2     C        H       
#4 D     I     7     4     D        I       
#5 5     7     E     J     E        J

answered Feb 6, 2021 at 8:54

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Collectives™ on Stack Overflow

How to replace values in differents columns with values in another column? (R)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related