R::dplyr combine tibbles on matching columns and replace NA values

Question

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!

akrun · Answer 1 · 2021-09-28T17:42:45.923

2

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

edited Sep 28 '21 at 17:42

answered Sep 28 '21 at 17:26

akrun

874,273
37
540
662

Thank you! Quick q - what did you write in the stackoverflow code to get the output tibble to print out in full? – Coco Newton Sep 28 '21 at 17:41
@CocoNewton it is just a `left_join` and then coalesce the .x, .y columns created from location so that if there is NA in .x, it gets replaced by the corresponding .y value – akrun Sep 28 '21 at 17:43

score 1 · Accepted Answer · answered Sep 28 '21 at 17:34

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)}

R::dplyr combine tibbles on matching columns and replace NA values

2 Answers2