Home > Software design >  R::dplyr combine tibbles on matching columns and replace NA values [duplicate]
R::dplyr combine tibbles on matching columns and replace NA values [duplicate]

Time:09-29

I have two tibbles x and y that I wish to combine into out such that NA columns in x are replaced by matching columns in y

library(tidyverse)
x <- tibble(name = c("hans", "dieter", "bohlen", "alf"), 
        location = c('NA','NA',1,2), 
        val1 = 1:4)
y <- tibble(name = c("hans", "dieter"), 
        location = c(2,2), 
        val1 = 1:2)

x
y

My desired output:

out <- tibble(name = c("hans", "dieter", "bohlen", "alf"), 
    location = c(2,2,1,2), 
    val1 = 1:4)
out

But using left_join or similar functions doesn't overwrite the NA values in X. I can't work out a solution from this question Overwrite left_join dplyr to update data

p.s. I can't seem to get the variables to print - any help on how to format the code to do that? Many thanks!

CodePudding user response:

We could coalesce the 'location' columns after doing a left_join

library(dplyr)
x %>% 
   left_join(y %>%
          select(-val1), by = 'name') %>% 
    transmute(name, location = coalesce(as.numeric(location.x), location.y), val1)

-output

# A tibble: 4 × 3
  name   location  val1
  <chr>     <dbl> <int>
1 hans          2     1
2 dieter        2     2
3 bohlen        1     3
4 alf           2     4

CodePudding user response:

library(tidyverse)
x <- tibble(name = c("hans", "dieter", "bohlen", "alf"), 
            location = c(NA,NA,1,2), 
            val1 = 1:4)
y <- tibble(name = c("hans", "dieter"), 
            location = c(2,2), 
            val1 = 1:2)


out <- tibble(name = c("hans", "dieter", "bohlen", "alf"), 
              location = c(2,2,1,2), 
              val1 = 1:4)

rows_update(x = x, y = y, by = "name")
#> # A tibble: 4 x 3
#>   name   location  val1
#>   <chr>     <dbl> <int>
#> 1 hans          2     1
#> 2 dieter        2     2
#> 3 bohlen        1     3
#> 4 alf           2     4

Created on 2021-09-28 by the reprex package (v2.0.1)

  • Related