vlookup in Pandas using join

A more generic application would be to use apply and lambda as follows:

dict1 = {113:'a',
         122:'b',
         123:'b',
         301:'c'}

df = pd.DataFrame([['1', 113],
                   ['2', 113],
                   ['3', 301],
                   ['4', 122],
                   ['5', 113]], columns=['num', 'num_letter'])

Add as a new dataframe column

 **df['letter'] = df['num_letter'].apply(lambda x: dict1[x])**

  num  num_letter letter
0   1         113      a
1   2         113      a
2   3         301      c
3   4         122      b
4   5         113      a

OR replace the existing ('num_letter') column

 **df['num_letter'] = df['num_letter'].apply(lambda x: dict1[x])**

  num num_letter
0   1          a
1   2          a
2   3          c
3   4          b
4   5          a

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