LEFT ANTI join under some matching condition in Pandas

Hello programmers, in this tutorial, we will learn how to join LEFT ANTI join under some matching conditions in Pandas Python.

LEFT ANTI join is a part of data that is only present on the only left and not common in both the table. So basically, we have to extract that information only present in the left table.

We can do this by using pandas pd.merge() function along with isin() function

pd.merge() along with isin() function in Python

• Using pd.merge() function, we can merge two DataFrame, and with the help of the isin() function, we can extract the information which is only present in the left DataFrame and not common between two DataFrame.

Coding:

• 1st we have to import the Pandas library and then create two DataFrame as shown in the code.
• Now we join these two DataFrame with the help of pd.merge() function, and we merge it based on the ‘c1’ column, and join is the left join.
• In this, we use a parameter  ‘indicator=True’, which means it creates an extra column ‘_merge’, which tells us whether the data is present in the left DataFrame only, in both DataFrame and in the right DataFrame only.
• Now with the help of the iloc[] function, we extract the data which are only present in the left Dataframe and assign this to the new DataFrame ‘df4’.
•  Now we pass that ‘df4’ DataFrame value in isin() function, which matches some condition between the left DataFrame and ‘df4’ and returns the information of the LEFT ANTI join from the left table.

# import pandas package
import pandas as pd
# creating dataframes using pd.DataFrame() method.
df1 = pd.DataFrame({
"c1": ["a", "b", "c", 'd'],
"c2": [1, 1, 3, 4],
})
df2 = pd.DataFrame({
"c1": ["a", "b", "c","e"],
"c2": [1, 2,3,5]
})
print("1st DataFrame df1:","\n",df1)
print("2nd DataFrame df2:","\n",df2)
# join using pd.merge() function
df3 = df1.merge(df2, on='c1', how='left', indicator=True) 
print("DataFrame df3:","\n",df3)
df4=df3.loc[df3['_merge'] == 'left_only', 'c1'] 
print("df4:","\n",df4)
#isin() function
df = df1[df1['c1'].isin(df4)]
print("ANTI-Left Join DataFrame:","\n",df)

output:

1st DataFrame df1: 
   c1  c2
0  a   1
1  b   1
2  c   3
3  d   4
2nd DataFrame df2: 
   c1  c2
0  a   1
1  b   2
2  c   3
3  e   5
DataFrame df3: 
   c1  c2_x  c2_y     _merge
0  a     1   1.0       both
1  b     1   2.0       both
2  c     3   3.0       both
3  d     4   NaN  left_only
df4: 
 3    d
Name: c1, dtype: object
ANTI-Left Join DataFrame: 
   c1  c2
3  d   4

 

Hopefully, you have learned how to join LEFT ANTI join under some matching conditions in Pandas.