Passing a function variable to the by option in `inner_join()` to join two tables by different columns

user_11891276 0
Viewed 447

My colleague and I are working on a function that takes two tables, does some simple data processing and then uses inner_join() to join them together. One of the tables will come from an upstream portion of a workflow and will have a column of ids named in a specific way. The other table is provided by the user and while it will have the same column of ids, it might not be named the same. Therefore we used the by = c("table1colname" = "table2colname") option in inner_join() but we got an error saying that there is a problem with column.name and that the "Join" columns must be present in the data... both of which are. We've checked syntax and tried a couple different ways of defining column.name in the function but have yet to figure out the problem.

The following is a simple example that replicates the error:

library(tidyverse)

X <- tribble(
  ~chickens, ~ducks, ~town,
  12, 62, "Burlington",
  18, 83, "Houston",
  34, 65, "Cincinatti"
)

Y <- tribble(
  ~chickens, ~ducks, ~city,
  34, 54, "Burlington",
  54, 29, "Cincinatti",
  12, 89, "Albuquerque"
)

join_fowl <- function(tab_1 = NULL, tab_2 = NULL, column.name = c("join_by")) {
  
  joined_fowl <- tab_1 %>% inner_join(tab_2, by = c(column.name = "city"))

}

join_fowl(X, Y, column.name = "town")
#> Error: Join columns must be present in data.
#> x Problem with `column.name`.

Created on 2020-06-23 by the reprex package (v0.3.0)

Is this an issue with the way dplyr is evaluating column.name or something else entirely? Any help is much appreciated!

dplyr r
2 Answers

The by argument uses a named vector so you could use setNames():

join_fowl <- function(tab_1 = NULL, tab_2 = NULL, column.name = c("join_by")) {
  
  joined_fowl <- tab_1 %>% inner_join(tab_2, by = setNames("city", column.name))
  return(joined_fowl)
  
}
    
join_fowl(X, Y, column.name = "town")

# A tibble: 2 x 5
  chickens.x ducks.x town       chickens.y ducks.y
       <dbl>   <dbl> <chr>           <dbl>   <dbl>
1         12      62 Burlington         34      54
2         34      65 Cincinatti         54      29
user_28352610

We can also rename the dataset column name

library(dplyr)
join_fowl <- function(tab_1 = NULL, tab_2 = NULL, column.name = c("join_by")) {
     tab_1 %>%
     inner_join(tab_2 %>% rename(!! column.name := "city"), 
         by =  column.name)

  }

join_fowl(X, Y, column.name = "town")
# A tibble: 2 x 5
#  chickens.x ducks.x town       chickens.y ducks.y
#       <dbl>   <dbl> <chr>           <dbl>   <dbl>
#1         12      62 Burlington         34      54
#2         34      65 Cincinatti         54      29

Or we can create a named list in by with := using dplyr::lst and then unlist it

join_fowl <- function(tab_1 = NULL, tab_2 = NULL, column.name = c("join_by")) {
       tab_1 %>%
       inner_join(tab_2, by = unlist(lst(!! column.name := "city")))
 
 }

join_fowl(X, Y, column.name = "town")
# A tibble: 2 x 5
#  chickens.x ducks.x town       chickens.y ducks.y
#       <dbl>   <dbl> <chr>           <dbl>   <dbl>
#1         12      62 Burlington         34      54
#2         34      65 Cincinatti         54      29
user_37322710
Related