You can perform anti-join or left anti-join on R data frames using the dplyr anti_join() function and reduce() function from the tidyverse package. You can use various functions of the dplyr package to perform join operations on the data frame in R.
R Anti-join is the opposite of the semi-join and returns only the rows from the first data frame that do not have a matching entry in the second data frame, based on specified columns.
Let’s create two Data Frames named emp_df and dept_df and having common columns named dept_id and dept_branch_id.
# 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. Using dplyr anti_join() in R
The anti_join() function of the R dplyr package is the best approach to perform the anti-join operations between two data frames. Before going to use the dplyr package you need to install it using install.packages(‘dplyr’) and load it using library(dplyr).
All functions in the dplyr package require the data frame as the first argument. When using the dplyr package, we mostly use the infix operator %>% which takes the output from one function and uses it as the first argument to the next function in the chain. This allows us to perform a sequence of operations on data frames with ease.
2.1 Anti-Join on Multiple Columns
An anti-join returns all the rows from the left data frame that are not present in the right data frame, based on specified columns. When we perform an anti-join operation on two data frames having the same multiple column names, you can pass a list of specified column names into by parameter it will return the new data frame containing all rows from the left data frame that do not have matching rows in the right data frame based on the specified columns.
# Load dplyr package
library(dplyr)
# Using dplyr - anti join multiple columns
df2 <- emp_df %>% anti_join( dept_df,
by=c('dept_id','dept_branch_id'))
df2
Yields below output.
2.2 Anti Join on Different Column Names
An alternative method to get the desired output is performing an anti-join operation on different column names of two data frames. To do this, we need to specify a by parameter with a named vector where the names correspond to columns from the first data frame and the values correspond to columns from the second data frame. This will return a new data frame containing all rows from the left data frame that do not have matching rows in the right data frame based on the specified columns.
# Load dplyr package
library(dplyr)
# Join on different column names
df2 <- emp_df %>% anti_join( dept_df,
by=c('dept_id'='dept_id',
'dept_branch_id'='dept_branch_id'))
df2
Yields the same output as above.
3. Using tidyverse Package
You can use the anti_join() function from the tidyverse package to perform an anti-join operation on multiple data frames in R. To do this, you can pass the anti_join keyword and a by parameter(that specifies a column name to join on) into the reduce() function. The resulting data frame will contain all the rows from the first data frame that do not have matching rows in the second data frame.
Before going to use the dplyr package you need to install it using install.packages(‘tidyverse’) and load it using library(tidyverse).
# Load tidyverse package
library(tidyverse)
# Anti Join data.frames
list_df = list(emp_df,dept_df)
df2 <- list_df %>% reduce(anti_join, by='dept_id')
df2
Yields the same output as above.
4. Conclusion
In this article, I have explained how to use anti_join() and reduce() functions to perform anti-join operations on two data frames in R using the dplyr and tidyverse packages.
Related Articles
- R Join on Different Column Names
- R Join (Merge) on Multiple Columns
- R Join Multiple Data Frames
- R Semi Join
- R Outer Join
- R Right Join
- R Left Join
- R Inner Join
