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 theisin()
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.
Leave a Reply