Merging dataframes with multiple key columns-CodePudding

I'd like to merge this dataframe:

import pandas as pd
import numpy as np

df1 = pd.DataFrame([[1,10,100],[2,20,np.nan],[3,30,300]], columns=["A","B","C"])
df1
   A   B    C
0  1  10  100
1  2  20  NaN
2  3  30  300

with this one:

df2 = pd.DataFrame([[1,422],[10,72],[2,278],[300,198]], columns=["ID","Value"])
df2
    ID  Value
0    1    422
1   10     72
2    2    278
3  300    198

to get an output:

df_output = pd.DataFrame([[1,10,100,422],[1,10,100,72],[2,20,200,278],[3,30,300,198]], columns=["A","B","C","Value"])
df_output 
   A   B    C  Value
0  1  10  100    422
1  1  10  100     72
2  2  20  NaN    278
3  3  30  300    198

The idea is that for df2 the key column is "ID", while for df1 we have 3 possible key columns ["A","B","C"].

Please notice that the numbers in df2 are chosen to be like this for simplicity, and they can include random numbers in practice.

How do I perform such a merge? Thanks!

CodePudding user response：

IIUC, you need a double merge/join.

First, melt df1 to get a single column, while keeping the index. Then merge to get the matches. Finally join to the original DataFrame.

s = (df1
     .reset_index().melt(id_vars='index')
     .merge(df2, left_on='value', right_on='ID')
     .set_index('index')['Value']
    )

# index
# 0    422
# 1    278
# 0     72
# 2    198
# Name: Value, dtype: int64

df_output = df1.join(s)

output:

   A   B      C  Value
0  1  10  100.0    422
0  1  10  100.0     72
1  2  20    NaN    278
2  3  30  300.0    198

Alternative with stack map:

s = df1.stack().droplevel(1).map(df2.set_index('ID')['Value']).dropna()
df_output = df1.join(s.rename('Value'))