Merging dataframes with multiple key columns

Question

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!

could you explain how you generate the the C value for row 1, have they the same number of rows? — Moun
– Moun, Commented Mar 9, 2022 at 15:13

mozway · Accepted Answer · 2022-03-09 15:24:26Z

3

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'))

edited Mar 9, 2022 at 15:24

answered Mar 9, 2022 at 15:12

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging dataframes with multiple key columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related