Using Join in python

Question

I am converting an SQL code, that has a join function, to Python.

This is the SQL code:

INSERT INTO tropical_fruits
SELECT DISTINCT A.* 
FROM fruits A LEFT OUTER JOIN tropical_fruits B 
ON A.[fruit1] = B.[fruit1] AND A.[fruit2] = B.[fruit2];

Python conversion code is:

data = pd.merge(
    fruits, tropical_fruits,
    left_on=['fruit1','fruit2'], 
    right_on=['fruit1','fruit2'], 
    how='left'
)

But I haven't got the desired result. Is the Python code correct?

Answer 1

Try this:

data = fruits.merge(tropical_fruits, on=['fruit1','fruit2'], how='left')

Answer 2

if you want to avoid _x and _y suffix you should determine the columns that you want in merge for each dataframe.

import pandas as pd
fruits = pd.DataFrame({'fruit1':['apple', 'banana', 'cherry'],'fruit2':['d','f','h'],'id':[1,2,3]  })
tropical_fruits = pd.DataFrame({'fruit1':['apple', 'banana', 'mango'],'fruit2':['d','n','h'],'id':[1,2,4]})
result=pd.merge(fruits[['fruit1','fruit2','id']], tropical_fruits[['fruit1','fruit2']],  how='left', left_on=['fruit1','fruit2'],right_on=['fruit1','fruit2'])