python - vlookup in Pandas using join

Question

I have the following 2 dataframes

Example1
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 
sku dept 
113 a
122 b
123 b
301 c 

I want to perform a merge, or join opertation using Pandas (or whichever Python operator is best) to produce the below data frame.

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

Both 
df_Example1.join(df_Example2,lsuffix='_ProdHier')
df_Example1.join(df_Example2,how='outer',lsuffix='_ProdHier')

Aren't working. What am I doing wrong?

Answer 1

Perform a left merge, this will use sku column as the column to join on:

In [26]:

df.merge(df1, on='sku', how='left')
Out[26]:
   sku  loc   flag dept
0  122   61   True    b
1  122   62   True    b
2  122   63  False    b
3  123   61   True    b
4  123   62  False    b
5  113   62   True    a
6  301   63   True    c

If sku is in fact your index then do this:

In [28]:

df.merge(df1, left_index=True, right_index=True, how='left')
Out[28]:
     loc   flag dept
sku                 
113   62   True    a
122   61   True    b
122   62   True    b
122   63  False    b
123   61   True    b
123   62  False    b
301   63   True    c

Another method is to use map, if you set sku as the index on your second df, so in effect it becomes a Series then the code simplifies to this:

In [19]:

df['dept']=df.sku.map(df1.dept)
df
Out[19]:
   sku  loc   flag dept
0  122   61   True    b
1  123   61   True    b
2  113   62   True    a
3  122   62   True    b
4  123   62  False    b
5  122   63  False    b
6  301   63   True    c