A coalescing join using the tidyverse

coalesing_join.R

library(tidyverse)

# Create fake data --------------------------------------------------------

t1 <- tibble(
  x = 1:5,
  y = c("A", NA, "C", "D", "E"),
  z = rnorm(5)
)

t2 <- tibble(
  x = 1:5,
  y = c(NA, "B", "C", "D2", NA),
  q = rnorm(5)
)

# Example -----------------------------------------------------------------

#t1 and t2 both have incomplete information for variable `y`, but can be joined by common column `x`

combined <- full_join(t1, t2, by = join_by(x), suffix = c("_t1", "_t2"))
combined
#this creates a single dataset with two columns for y that we need to combine or "coalesce"

coalesced <- 
  combined |> 
  mutate(y = coalesce(y_t1, y_t2))
coalesced
#notice that the second column provided to `coalesce()` takes precedence when they both have a value

#just need to remove the original columns and we're done
done <- coalesced |> 
  select(-y_t1, -y_t2)
done