How to merge two pandas dataframes based on a dictionary?-CodePudding

There are two pandas dataframes as follows:

df1:

col11  col22  col33

abc    25     36
bcd    55     96
cdf    15     19
abc    74     26

and

df2:
        
col01  col02  col03
    
name1    x     346
name2    g     926
name3    t     179
name1    k     286

I want to merge df1 and df2 based on a dictionary that this dictionary's keys are col11 and its values are col01 as follows:

mydict = {'abc': 'name1', 'bcd': 'name2', 'cdf': 'name3'}

My expected is:

df1:

col11  col22  col33 col01  col02  col03

abc    25     36    name1    x     346
bcd    55     96    name2    g     926
cdf    15     19    name3    t     179
abc    74     26    name1    x     346

How can I merge this two dataframes?

CodePudding user response：

Use a mapped Series as key in merge:

df1.merge(df2, left_on=df1['col11'].map(mydict), right_on='col01')

Output:

  col11  col22  col33  col01 col02  col03
0   abc     25     36  name1     x    346
1   abc     25     36  name1     k    286
2   abc     74     26  name1     x    346
3   abc     74     26  name1     k    286
4   bcd     55     96  name2     g    926
5   cdf     15     19  name3     t    179

For a one-to-one merge in order:

(df1.assign(n=df1.groupby('col11').cumcount())
 .merge(df2.assign(n=df2.groupby('col01').cumcount()),
        left_on=[df1['col11'].map(mydict), 'n'],
        right_on=['col01', 'n'])
)

Output:

  col11  col22  col33  n  col01 col02  col03
0   abc     25     36  0  name1     x    346
1   bcd     55     96  0  name2     g    926
2   cdf     15     19  0  name3     t    179
3   abc     74     26  1  name1     k    286