Compare and map values in two table with condition

Question

I have two tables:

df1:

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

df2:

Branch	Dept	Code	...
A	1		...
B	2		...
A	3		...
B	1		...
A	2		...
...	...	...	...

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.

user7864386 · Accepted Answer · 2022-03-01 20:00:53Z

2

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

answered Mar 1, 2022 at 20:00

user7864386

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

5 Comments

Andrew Hoang Over a year ago

I have tried the map method and I have an error "Reindexing only valid with uniquely valued Index objects". I have checked duplicated columns on both tables and used reset_index also but still have that error.

user7864386 Over a year ago

@AndrewHoang That's weird; can the same Branch-Dept pair have multiple codes?

Andrew Hoang Over a year ago

Nope, the Branch-Dept pair is unique and 1 code can be used for multiple pairs.

user7864386 Over a year ago

@AndrewHoang df2.merge(df1, on=cols, how='left') matches all branch-dept pairs in df2 with existing pairs in df1. If you're getting NaN values, then it means for some pairs, the corresponding code doesn't exist in df1.

Andrew Hoang Over a year ago

I want to ask if it is possible that I can merge some rows based on their value. For example, if Branch == 'A' then merge on 2 columns (on='cols' have 2 columns), else merge on 3 columns (on='cols' have 3 columns).

Collectives™ on Stack Overflow

Compare and map values in two table with condition

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related