I have two tables:

df1:

df2:

I want to map the Code column in df1 to the Code column in df2. The condition is in each combination of Branch and Dept will have a code, and sometimes, one Code can have multiple Branch-Dept.

I have tried to convert to dictionary for comparison:

foo = {}
for i in df1["Code"].unique():
    foo[i] = [{df1["Branch"][j]: df1["Dept"][j]} for j in mapping_table[df1["Code"] == i].index]
    
bar = dict(zip(df2["Branch"], df2["Dept"]))

My target table (df2) have around 10000 rows, if possible, is there any way to get all values faster.

CodePudding user response：

You could map:

cols = ['Branch','Dept']
df2['Code'] = df2.set_index(cols).index.map(df1.set_index(cols)['Code'])

or you could merge:

df2 = df2.merge(df1, on=cols)

Output:

  Branch  Dept   Code
0      A     1  10000
1      B     2  20001
2      A     3  10003
3      B     1  20000
4      A     2  10001