pandas insert or join or merge two tables by comparing multiple conditions?-CodePudding

I am trying to merge two tables.
As figure below, I have two tables that table 1 has x,y,z,id1,id2 and table 2 has id, a,b

I want to merge these two tables into one final table(the last one)

if table 1 has one id columns, I think I can merge like below code

df_2.insert(0, 'x',df_2['id'].map(df_1.set_index('id')['x']))
df_2.insert(0, 'y',df_2['id'].map(df_1.set_index('id')['y']))
df_2.insert(0, 'y',df_2['id'].map(df_1.set_index('id')['z']))

but I am having trouble because table 1 has multiple 'ID' columns...

Please help me.

ps. sorry for my Poor English skills.

Table 1

Table 2

Final Table

CodePudding user response：

Use merge after reshaping your dataframe with melt:

out = (df2.merge(df1.melt(['x', 'y', 'z'], value_name='id'), on='id')
          .drop(columns='variable'))
print(out)

# Output
   id   a   b   x   y   z
0   1  a1  b1  10  40  70
1   2  a2  b2  10  40  70
2   3  a3  b3  20  50  80
3   4  a4  b4  20  50  80
4   5  a5  b5  30  60  90
5   6  a6  b6  30  60  90