Inner_join multiple data frames based on multiple columns with different names in R

Question

I have two data frames that look like this

library(tidyverse)

df1 <- tibble(.x=c(334,335,395),
              .y=c(574,600,466))

df1
#> # A tibble: 3 × 2
#>      .x    .y
#>   <dbl> <dbl>
#> 1   334   574
#> 2   335   600
#> 3   395   466


df2 <- tibble(id=c(334,335,395,466,574,600),
              fruits=c("apple","banana","ananas","pear","cherry","orange"))

df2
#> # A tibble: 6 × 2
#>      id fruits
#>   <dbl> <chr> 
#> 1   334 apple 
#> 2   335 banana
#> 3   395 ananas
#> 4   466 pear  
#> 5   574 cherry
#> 6   600 orange

^{Created on 2022-03-02 by the reprex package (v2.0.1)}

Each fruit has an id, as it is showed in df2. df1 has the code of the these fruits. I want to join df1 and df2 and my data look like this

.x   .y    fruits.x  fruits.y
334. 574    apple     cherry
335  600    banana    orange
395  466    ananas    pear

I can use inner_join two different times and then bind the data.frames but I was wondering if there is an elegant way that I am missing

thank you for your time

Answer 1

What you probably want is match

library(tidyverse)
df1 %>%
  mutate(across(everything(), ~df2$fruits[match(., df2$id)]))

# A tibble: 3 x 2
  .x     .y    
  <chr>  <chr> 
1 apple  cherry
2 banana orange
3 ananas pear 

If you want to ADD this info to your df1 instead of replacing it, check the .names argument in across.

---

Solution to add columns:

df1 %>%
  mutate(across(everything(), ~df2$fruits[match(., df2$id)], .names = "{.col}_fruits"))

# A tibble: 3 x 4
     .x    .y .x_fruits .y_fruits
  <dbl> <dbl> <chr>     <chr>    
1   334   574 apple     cherry   
2   335   600 banana    orange   
3   395   466 ananas    pear