How to perform join/merge on different column names in R? To join data frames on the different columns in R use either base merge() function or use dplyr functions. Using the dplyr functions is the best approach as it runs faster than the R base approach. dplyr package provides several functions to join R data frames and all these supports merge on the different column names.
1. Quick Examples of Joining on Different Columns
Following are quick examples of joining/merging data frames on different column names.
# Quick Examples # Using dplyr library(dplyr) df2 <- emp_df %>% inner_join( dept_df, by=c('emp_dept_id'='dept_id', 'emp_dept_branch_id'='dept_branch_id')) df2 # Using merge df2 <- merge(x=emp_df,y=dept_df, by.x=c("emp_dept_id","emp_dept_branch_id"), by.y=c("dept_id","dept_branch_id")) df2
Let’s create two Data Frames with column names different on both and will use these to perform the merge operation. In the below example, I will be joining
# Create emp Data Frame emp_df=data.frame( emp_id=c(1,2,3,4,5,6), name=c("Smith","Rose","Williams","Jones","Brown","Brown"), emp_dept_id=c(10,20,10,10,40,50), emp_dept_branch_id= c(101,102,101,101,104,105) ) # Create dept Data Frame dept_df=data.frame( dept_id=c(10,20,30,40), dept_name=c("Finance","Marketing","Sales","IT"), dept_branch_id= c(101,102,103,104) ) emp_df dept_df
Yields below output.
2. Using dplyr to Join Different Column Names in R
Using join functions from dplyr package is the best approach to joining data frames on different column names in R, all dplyr functions like inner_join(), left_join(), right_join(), full_join(), anti_join(), semi_join() support joining on different columns. In the below example I will cover using the inner_join().
Following is the syntax of inner_join() and a similar syntax is used for other joins in the dplyr package.
# Syntax inner_join(df1, df2, by=c('x1'='y1', 'x2'='y2'))
- The value in the x1 column of df1 matches the value in the y1 column of df2.
- The value in the x2 column of df1 matches the value in the y2 column of df2.
2.2 Join Different Column Names Example
In order to use dplyr, you have to install it first using install.packages(‘dplyr’) and load it using
All functions in dplyr package take
data.frame as a first argument. When we use
dplyr package, we mostly use the infix operator
magrittr, it passes the left-hand side of the operator to the first argument of the right-hand side of the operator. For example,
x %>% f(y) converted into
f(x, y) so the result from the left-hand side is then “piped” into the right-hand side.
# Load dplyr package library(dplyr) # Join on different column names df2 <- emp_df %>% inner_join( dept_df, by=c('emp_dept_id'='dept_id', 'emp_dept_branch_id'='dept_branch_id')) df2
Yields below output.
3. Using merge() to Join Different Column Names
Using merge() function from the R base can also be used to perform joining on different column names. To do so you need to create a vector for
by.x with the columns you wanted to join on and create a similar vector for
# Syntax of merge(x=df1,y=df2, by.x=c("x_col1","x_col2"), by.y=c("y_col1","y_col2"))
The value in the x_col1 column of df1 matches the value in the y_col1 column of df2.
The value in the x_col2 column of df1 matches the value in the y_col2 column of df2.
3.2 Merge Different Column Names Example
In this example, emp_df is considered a left table, and dept_df is considered a right table and this performs the inner join on these tables, in case you wanted to use other joins with merge() refer to R join data frames.
# Using merge on different column names df2 <- merge(x=emp_df,y=dept_df, by.x=c("emp_dept_id","emp_dept_branch_id"), by.y=c("dept_id","dept_branch_id")) df2
Yields the same output as above.
In this article, you have learned how to join/merge data frames on different column names using R base merge() function and join functions from dplyr package. Using dplyr approach is the best to use when you are joining on larger datasets as it performs efficiently over the R base.
- R Join (Merge) on Multiple Columns
- R Join Multiple Data Frames
- R Semi Join
- R Anti Join
- R Outer Join
- R Right Join
- R Left Join
- R Inner Join