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

Question

This question already has answers here:

Replace values in a dataframe based on lookup table (8 answers)

Closed 3 years ago.

The community reviewed whether to reopen this question 3 years ago and left it closed:

Duplicate This question has been answered, is not unique, and doesn’t differentiate itself from another 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

deschen · Accepted Answer · 2022-03-02 09:49:47Z

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

edited Mar 2, 2022 at 9:49

answered Mar 2, 2022 at 9:12

deschen

11.6k5 gold badges32 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

LDT Over a year ago

Holly molly, I need to learn match functions. This is so neat deschen! Yes I want to add the .x and .y as well!

Donald Seinen Over a year ago

add an argument .names = "fruits{col}" to the across part to get the desired output as in the post

LDT Over a year ago

Excatly Donald, Thank you so much. If I add this, then it works as I like df1 %>% mutate(across(everything(), .names="fruits_{col}", ~df2$fruits[match(., df2$id)]))

deschen Over a year ago

See my updated answer, you can of course play around with the .names argument to glue together the name as you like (e.g. fruits first, then column names).

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related