Open In App

Find rows which are not in other dataframe in R

Improve
Improve
Like Article
Like
Save
Share
Report

To find rows present in one dataframe that are not present in the other is known as set-difference. In this article, we will see different ways to do the same.

Method 1: Using sqldf()

In this method simply the sql query to find set-difference is passed

Syntax:

sqldf(“sql query”)

Our query will be sqldf(‘SELECT * FROM df1 EXCEPT SELECT * FROM df2’). It will exclude all the rows from df1 that are also present in df2 and will return only rows that are only present in df1.

Example 1:

R




require(sqldf)
df1 <- data.frame(a = 1:5, b=letters[1:5])
df2 <- data.frame(a = 1:3, b=letters[1:3])
  
print("df1 is ")
print(df1)
  
print("df2 is ")
print(df2)
  
res <- sqldf('SELECT * FROM df1 EXCEPT SELECT * FROM df2')
print("rows from df1 which are not in df2")
print(res)


Example 2:

R




require(sqldf)
df1 <- data.frame(name = c("kapil","sachin","rahul"), age=c(23,22,26))
df2 <- data.frame(name = c("kapil"), age = c(23))
  
print("df1 is ")
print(df1)
  
print("df2 is ")
print(df2)
  
res <- sqldf('SELECT * FROM df1 EXCEPT SELECT * FROM a2')
print("rows from df1 which are not in df2")
print(res)


Method 2: Using setdiff()

This is an R built-in function to find the set difference of two dataframes.

Syntax:

setdiff(df1,df2)

It will return rows in df1 that are not present in df2.

Example 1:

R




df1 <- data.frame(a = 1:5, b=letters[1:5], c= c(1,3,5,7,9))
df2 <- data.frame(a = 1:5, b=letters[1:5], c = c(2,4,6,8,10))
  
print("df1 is ")
print(df1)
  
print("df2 is ")
print(df2)
  
res <-setdiff(df1, df2)
print("rows from df1 which are not in df2")
print(res)


Output:

Example 2:

R




df1 <- data.frame(name = c("kapil","sachin","rahul"), age=c(23,22,26))
df2 <- data.frame(name = c("kapil","rahul", "sachin"), age = c(23, 22, 26))
  
print("df1 is ")
print(df1)
  
print("df2 is ")
print(df2)
  
res <- setdiff(df1, df2)
print("rows from df1 which are not in df2")
print(res)


Output:



Last Updated : 07 Apr, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads