[FIXED] How to use left_join on several data frames?

Issue

How to combines dataframe more easily?

I have three dataframes (table_base / table_a / table_b). I want to combine them by row to obtain the result as 'table_final'. Below is the code I have, it works, but it is a little bit complicated. How can I simplify it ? Actually, I will have more tables to join than just table_a and table_b.

    library(dplyr)
    table_base <- data.frame(cat=c("a","b","c","d"))
    
    table_a <- data.frame(cat=c("a","b"),
                          value=c(1,2))
    
    table_b <- data.frame(cat=c("a","c","d"),
                          value=c(7,9,10))


table_final <- table_base %>% 
  left_join(table_a,by='cat',fill=0) %>% 
  left_join(table_b,by='cat') %>% 
  mutate(value=if_else(!is.na(value.x),value.x,value.y)) %>% 
  select(cat,value)

Solution

Using purrr::reduce to merge multiple dataframes, then use dplyr::coalesce to get first non-na value:

library(dplyr)
library(purrr)

list(table_base, table_a, table_b) %>% 
  reduce(left_join, by = "cat") %>% 
  mutate(value = coalesce(!!!select(., starts_with("value")))) %>% 
  select(cat, value)

#   cat value
# 1   a     1
# 2   b     2
# 3   c     9
# 4   d    10

Answered By - zx8754

Answer Checked By - David Goodson (PHPFixing Volunteer)