How to do a vlookup in pandas with different primary key and foreign key column names in two dataframes?

Question

Lets say we have two dataframes df and df1 with different column names like sku as primary key and sno as its foreign key:

Example1(df)
sku loc flag  
122  61 True 
123  61 True
113  62 True 
122  62 True 
123  62 False
122  63 False
301  63 True 

Example2(df1)
sno dept 
113 a
122 b
123 b
301 c

I want to do a join or merge which looks like this:

Example3
sku loc flag   dept  
122  61 True   b
123  61 True   b
113  62 True   a
122  62 True   b
123  62 False  b
122  63 False  b
301  63 True   c

Unfortunately df.merge(df1, on='sku', how='left') and df['dept']=df.sku.map(df1.dept) doesn't work.

Example modified from : vlookup in Pandas using join

I blieve `df.merge` will work if you just change the `snow` column to be named `sku`. — Ed Kloczko, Jan 14 '21 at 15:41
Yes it will work but I wanted to know if we can do without changing column names. Anyways thanks. — sushk, Jan 14 '21 at 15:54

score 1 · Answer 1 · answered Jan 14 '21 at 15:42

you have to set the left and right key

df1.merge(df2, right_on='sno',left_on='sku',how='left')


   sku  loc   flag  sno dept
0  122   61   True  122    b
1  123   61   True  123    b
2  113   62   True  113    a
3  122   62   True  122    b
4  123   62  False  123    b
5  122   63  False  122    b
6  301   63   True  301    c

How to do a vlookup in pandas with different primary key and foreign key column names in two dataframes?

1 Answers1