In dplyr, how to join dataframes by columns that may or may not exist?

Viewed 170

I have two datafrmaes that I want to join. Whereas I always have a main mutual column to join by, I sometimes might have another column in the data that I will want to join by, in addition to the main one.

How could I specify a possible column to join by?

Example

I demonstrate my question with two datasets derived from mtcars. Both have a "main" column (cars) that I will always join by, and sometimes there might be another mutual column (some_letters) in either one or both datasets.

library(tidyverse)

create_df <- function(columns_to_include) {
  
  mtcars %>%
    rownames_to_column("cars") %>%
    select(cars, {{ columns_to_include }}) %>%
    slice_sample(n = 15) %>%
    {if (sample(c(TRUE, FALSE), size = 1)) add_column(., some_letters = letters[1:15]) else .}
}

# both dataframes have "some_letters"
set.seed(123)
df_a1 <- create_df(carb)
df_a2 <- create_df(gear)
scenario_a <- inner_join(df_a1, df_a2, by = c("cars", "some_letters"))
scenario_a
#>             cars carb some_letters gear
#> 1 Ford Pantera L    4            l    5

# neither dataframe has "some_letters"
set.seed(111)
df_b1 <- create_df(carb)
df_b2 <- create_df(gear)
scenario_b <- inner_join(df_b1, df_b2, by = c("cars", "some_letters"))
#> Error: Join columns must be present in data.
#> x Problem with `some_letters`.

# one dataframe has "some_letters" but the other doesn't
set.seed(737)
df_c1 <- create_df(carb)
df_c2 <- create_df(gear)
scenario_c <- inner_join(df_c1, df_c2, by = c("cars", "some_letters"))
#> Error: Join columns must be present in data.
#> x Problem with `some_letters`.

Created on 2021-02-20 by the reprex package (v0.3.0)

We can see that in scenario_a the join works because both df_a1 and df_a2 include some_letters. However, in scenario_b we see that the join fails because some_letters doesn't exist (in either data). Similarly, scenario_c shows a situation where some_letters appears in one dataset but not in the other and therefore the join fails.

When joining data, can I specify that some_letters is possible, but not guaranteed to appear, such that when it appears in both data it will become an additional join-by column, but otherwise it would be ignored from the by argument?

Desired Output

inner_join(df_b1, df_b2, by = c("cars", "some_letters"))

# as if we joined by `cars` only:

##                 cars carb gear
## 1      Porsche 914-2    2    5
## 2 Cadillac Fleetwood    4    3
## 3   Pontiac Firebird    2    3
## 4         Datsun 710    1    4
## 5          Merc 240D    2    4
## 6  Chrysler Imperial    4    3
## 7     Hornet 4 Drive    1    3
## 8         Camaro Z28    4    3

1 Answers

Create a vector of intersecting names

library(dplyr)
library(purrr)
nm1 <- reduce(list(names(df_b1), names(df_b2),
             c("cars", "some_letters")), intersect)

and then do the join

inner_join(df_b1, df_b2, by =  nm1)

-output

#                cars carb gear
#1      Porsche 914-2    2    5
#2 Cadillac Fleetwood    4    3
#3   Pontiac Firebird    2    3
#4         Datsun 710    1    4
#5          Merc 240D    2    4
#6  Chrysler Imperial    4    3
#7     Hornet 4 Drive    1    3
#8         Camaro Z28    4    3
Related