tidyverse - Joining multiple dataframes by different columns using a function and map() in R?

Question

Given three data frames:

df1 <- data.frame(
  id = c(1:3),
  ctry1 = c("us", "es", "fr"),
  ctry2 = c("ve", "pa", "us"),
  ctry3 = c("co", "co", "es")
)

df2 <- data.frame(
  id = c(1:3),
  ctry1 = c("ve", "ve", "us"),
  ctry2 = c("es", "pa", "us"),
  ctry3 = c("pa", "co", "es")
)

iso <- data.frame(
  ctry = c("us", "es", "fr", "ve", "pa", "ar", "co"),
  iso_ctry = c("840", "724", "250", "862", "591", "032", "170")
)

** How can I join the variable iso_ctry from the iso data frame with all three columns of the df1 and df2 data frames?**

It works with the following function and map() from purrr, but I suspect there should be a faster way. Perhaps with a loop or a two arguments function.

join_iso <- function(df) { 
  left_join(df, iso, by = c("ctry1" = "ctry")) %>% 
    left_join(iso, by = c("ctry2" = "ctry")) %>% 
    left_join(iso, by = c("ctry3" = "ctry")) %>% 
    rename(iso_ctry1 = iso_ctry.x, iso_ctry2 = iso_ctry.y, iso_ctry3 = iso_ctry)
}

df_list <- list(df1, df2)

(df1_new <- map(df_list, join_iso))  

question from:https://stackoverflow.com/questions/65906682/joining-multiple-dataframes-by-different-columns-using-a-function-and-map-in-r

Answer 1

You can use :

library(tidyverse)

join_iso <- function(df){
  df %>% 
    pivot_longer(-id) %>% 
    left_join(iso, by = c("value" = "ctry")) %>% 
    pivot_wider(id, names_from = name, values_from = c(value, iso_ctry), names_glue = "{name}_{.value}")
}

df_list <- list(df1, df2)

map(df_list, join_iso)
#> [[1]]
#> # A tibble: 3 x 7
#>      id ctry1_value ctry2_value ctry3_value ctry1_iso_ctry ctry2_iso_ctry
#>   <int> <chr>       <chr>       <chr>       <chr>          <chr>         
#> 1     1 us          ve          co          840            862           
#> 2     2 es          pa          co          724            591           
#> 3     3 fr          us          es          250            840           
#> # ... with 1 more variable: ctry3_iso_ctry <chr>
#> 
#> [[2]]
#> # A tibble: 3 x 7
#>      id ctry1_value ctry2_value ctry3_value ctry1_iso_ctry ctry2_iso_ctry
#>   <int> <chr>       <chr>       <chr>       <chr>          <chr>         
#> 1     1 ve          es          pa          862            724           
#> 2     2 ve          pa          co          862            591           
#> 3     3 us          us          es          840            840           
#> # ... with 1 more variable: ctry3_iso_ctry <chr>

^{Created on 2021-01-26 by the reprex package (v0.3.0)}