How to do right join on data frames in R? To perform right join or right outer join use either merge() function, dplyr right_join() function, or use reduce() from tidyverse. Using the dplyr function is the best approach as it runs faster than the R base approach. dplyr package provides several functions to join data frames in R.
In R, the right join is the opposite of right join, here it returns all rows from the right data.frame regardless of math found on the right dataset, when join expression doesn’t match, it assigns NA for that record and drops records from right where match not found.
1. Quick Examples of Right Join
Following are quick examples of performing the right join on data frames.
# Quick Examples
# Right join
df2 <- merge(x=emp_df,y=dept_df,
by="dept_id", all.y=TRUE)
# Right join on multiple columns
df2 <- merge(x=emp_df,y=dept_df,
by=c("dept_id","dept_branch_id"), all.y=TRUE)
# Right join on different columns
df2 <- merge(x=emp_df,y=dept_df,
by.x=c("dept_id","dept_branch_id"),
by.y=c("dept_id","dept_branch_id"),
all.y=TRUE)
# Load dplyr package
library(dplyr)
# Using dplyr - right join multiple columns
df2 <- emp_df %>% right_join( dept_df,
by=c('dept_id','dept_branch_id'))
# Using dplyr - right join on different columns
df2 <- emp_df %>% right_join( dept_df,
by=c('dept_id'='dept_id',
'dept_branch_id'='dept_branch_id'))
# Load tidyverse package
library(tidyverse)
# Right outer Join data.frames
list_df = list(emp_df,dept_df)
df2 <- list_df %>% reduce(right_join, by='dept_id')
df2
Let’s create two Data Frames, in the below example dept_id and dept_branch_id columns exists on both emp_df and dept_df data frames.
# 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"),
superior_emp_id=c(-1,1,1,2,2,2),
dept_id=c(10,20,10,10,40,50),
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. Right Join using merge()
R base provides a merge() function that is used to perform a right join on two, three or more (multiple) data frames. This function takes x and y data frames as left and right respectively and finally specify the by param with the column name you wanted to join.
The following example performs a right join on the column dept_id on emp_df and dept_df column. To perform the right join use all.y=TRUE.
# Right join
df2 <- merge(x=emp_df,y=dept_df,
by="dept_id", all.y=TRUE)
df2
Yields below output. if you have the same column names that are not used in the join condition, it suffixes the x and y to the columns on the result. In the below example check dept_branch_id.
From our example, the right dataset dept_id 30 doesn’t have it on the right dataset emp hence, this record contains NA on emp columns. and dept_id 50 dropped as a match not found on the right. Below is the result of the above Join expression.
2.1 Right Join on Multiple Columns
To perform a right join on multiple columns with the same names on both data frames, use all the column names as a list to by param. I have also created a dedicated article where I have explained how to perform join on multiple columns using several ways.
# Using merge with same column names
df2 <- merge(x=emp_df,y=dept_df,
by=c("dept_id","dept_branch_id"),
all.y=TRUE)
df2
Yields below output.
2.2 Right Join Different Column Names
Sometimes you will have data frames with different column names and you wanted to perform an right join on these columns, to do so specify the column names from both data frames with params by.x and by.y.
# R right join multiple columns
df2 <- merge(x=emp_df,y=dept_df,
by.x=c("dept_id","dept_branch_id"),
by.y=c("dept_id","dept_branch_id"),
all.y=TRUE)
df2
Since our data frame has the same column names, it results in the same output as above, I have created another article where I have explained how to perform join on different column names.
3. Using dplyr to Perform Right Join in R
Using the right_join() function from the dplyr package is the best approach to performing the right join on two data frames. In order to use dplyr, you have to install it first using install.packages(‘dplyr’) and load it using library(dplyr).
All functions in dplyr package take data.frame as a first argument. When we use dplyr package, we mostly use the infix operator %>% from 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.
3.1 Multiple Columns with dplyr package
# Load dplyr package
library(dplyr)
# Using dplyr - right join multiple columns
df2 <- emp_df %>% right_join( dept_df,
by=c('dept_id','dept_branch_id'))
df2
Yields below output. Since we are using both columns on the join we are not seeing duplicate columns.
3.2 Using Different Column Names with dplyr package
# Load dplyr package
library(dplyr)
# join on multiple columns
df2 <- emp_df %>% right_join( dept_df,
by=c('dept_id'='dept_id',
'dept_branch_id'='dept_branch_id'))
df2
Yields the same output as above.
4. Using tidyverse Package
By using reduce() function from tidyverse package you can perform join on multiple data frames, to perform right join use right_join keyword. If you wanted to right join multiple data frames, pass all dataframes as a list to reduce() function.
# Load tidyverse package
library(tidyverse)
# Right Join data.frames
list_df = list(emp_df,dept_df)
df2 <- list_df %>% reduce(right_join, by='dept_id')
df2
5. Conclusion
In this article, you have learned how to perform a right join on two data frames using the R base merge() function, right_join() functions from the dplyr package, and reduce() from the tidyverse package. Using dplyr approach is the best to use when you are joining on larger datasets as it performs efficiently over the R base.
