How to do a left join with multi criteria?

Question

I tried to do a left join on two dataframe with Python Pandas like this SQL request:

SELECT df1.Name, df2.CP

FROMdf1

LEFT JOIN df2 ON (df1.City1 = df2.City) OR (df1.City2 = df2.City) OR (df1.City3 = df2.City)

Could you please help me to do that?

I have 2 Dataframes for example:

df1 = pd.DataFrame({'Name':['toto', 'titi', 'tutu'], 'City1':['NY', 'Chic', 'Paris'], 'City2':['N-Y', 'Chicago', 'Paries'], 'City3':['New-York', 'chicagooo', 'Pari']}) 
df2 = pd.DataFrame({'City':['New-York', 'Chicago', 'Paris'], 'CP':['1', '2', '3']})

The result expected is like that:

Name    CP
toto    1
titi    2
tutu    3

score 1 · Accepted Answer · answered May 21 '19 at 07:46

Use df.melt() to melt the City columns into one single column and do a merge:

m=df1.melt('Name').merge(df2,left_on='value',right_on='City')
#or if you have more columns and need only city: df1.set_index('Name').filter(like='City').reset_index().melt('Name')
m[['Name','CP']]

   Name CP
0  tutu  3
1  titi  2
2  toto  1

How to do a left join with multi criteria?

1 Answers1