Dataframe Left Merge with Matching Keys In Different Columns

user_13636147 0
Viewed 33

I have to merge two DataFrame, with a Left Join - Illustration below.

Problem is that the matching key is spread across 3 columns. To further complicate the challenge, some rows (#4) will have the same matching key twice! I was advised to use Melt but it only work for a Right Join.

What is the best approach?

import pandas as pd

data1 = {'key1' : ['abc','aa','aa','sdf'],
         'key2' : ['aa','efg','aa', 'sdf'],
         'key3' : ['aa','aa','xyz', 'aa']
        }

data2 = {'key': ['abc','efg', 'xyz', 'sdf'], 
        'msg' : ['happy','mad','smile','great']}

df1= pd.DataFrame(data1)
df2= pd.DataFrame(data2)

enter image description here

pandas dataframe merge python
2 Answers

Let's try stack to reshape df1 then map the keys with the corresponding msg from df2, finally groupby on level=0 and aggregate using first:

df1['msg'] = df1.stack().map(df2.set_index('key')['msg']).groupby(level=0).first()

  key1 key2 key3    msg
0  abc   aa   aa  happy
1   aa  efg   aa    mad
2   aa   aa  xyz  smile
3  sdf  sdf   aa  great
user_128331660

How about this ? You can recreate a temporary dataframe where all keys are on the same columns, make your join, then drop any duplicates (and re-merge to your first dataframe) :

df1.reset_index(drop=True, inplace=True)

df3 = pd.DataFrame(
  df1[["index", "key1"]].values.tolist()
  + df1[["index", "key2"]].values.tolist()
  + df1[["index", "key3"]].values.tolist(),
  columns=['index', 'key'])
df4 = df3.merge(df2, on="key", how="left")
df4.sort_values('index', inplace=True)
df4.drop_duplicates('index', keep='first')

df = df1.merge(df4[['index', 'msg']], on="index", how='left')
user_69364890
Related