Using coalesce function with many variables

user_13734451 0
Viewed 36

I have two datasets with similar variables. dataset2 has values of of variables that were not captured in dataset2. My aim is to use the dataset2 variables to fill the corresponding values in variables in dataset1. Is there a way to achieve this. It is possible to use coalesce but listing all the variables is a bit cumbersome.

library(dplyr)


dat1 <- tibble(
  id = c("soo1", "soo2", "soo3", "soo4"),
  a1= c("Test", "Tested", "Testing", NA),
  a2= c("Math", "Eng", NA, "French"),
  a3= c("Science", NA, "Biology", "Chem"))


dat2 <- tibble(
  id = c("soo1", "soo2", "soo3", "soo4"),
  a1= c(NA, NA, NA, "Tested"),
  a2= c("Math", NA, "UK", NA),
  a3= c("Science", "Physic", NA, NA))

dat1 %>%
  inner_join(dat2, by = "id") %>%
  mutate(a1 = coalesce(a1.x, a1.y),
         a2 = coalesce(a2.x, a2.y))
dplyr r
3 Answers

You could also fill your values "downup" per group for every column like this:

library(dplyr)
library(tidyr)
dat1 <- tibble(
  id = c("soo1", "soo2", "soo3", "soo4"),
  a1= c("Test", "Tested", "Testing", NA),
  a2= c("Math", "Eng", NA, "French"),
  a3= c("Science", NA, "Biology", "Chem"))


dat2 <- tibble(
  id = c("soo1", "soo2", "soo3", "soo4"),
  a1= c(NA, NA, NA, "Tested"),
  a2= c("Math", NA, "UK", NA),
  a3= c("Science", "Physic", NA, NA))

dat1 %>%
  bind_rows(dat2) %>%
  group_by(id) %>%
  fill(everything(), .direction = "downup") %>%
  slice(1)
#> # A tibble: 4 × 4
#> # Groups:   id [4]
#>   id    a1      a2     a3     
#>   <chr> <chr>   <chr>  <chr>  
#> 1 soo1  Test    Math   Science
#> 2 soo2  Tested  Eng    Physic 
#> 3 soo3  Testing UK     Biology
#> 4 soo4  Tested  French Chem

Created on 2022-07-18 by the reprex package (v2.0.1)

user_142827140

Another possible solution, based on powerjoin:

library(powerjoin)
library(tibble)

power_inner_join(dat1, dat2, by = "id", conflict = coalesce_xy)

#> # A tibble: 4 × 4
#>   id    a1      a2     a3     
#>   <chr> <chr>   <chr>  <chr>  
#> 1 soo1  Test    Math   Science
#> 2 soo2  Tested  Eng    Physic 
#> 3 soo3  Testing UK     Biology
#> 4 soo4  Tested  French Chem
user_115644870

In dplyr, we may use rows_patch

library(dplyr)
rows_patch(dat1, dat2, by = 'id')

-output

# A tibble: 4 × 4
  id    a1      a2     a3     
  <chr> <chr>   <chr>  <chr>  
1 soo1  Test    Math   Science
2 soo2  Tested  Eng    Physic 
3 soo3  Testing UK     Biology
4 soo4  Tested  French Chem
user_37322710
Related