VLOOKUP

11/12/16

The VLOOKUP function helps you find data in another table, based on a common lookup value.

In [3]:
import pandas as pd
In [4]:
df = pd.read_csv('sample_data.csv')
In [5]:
df
Out[5]:
Name Date Item Product Category Time (hrs) Amt Picked (lbs) Sale Value ($/lb)
0 Lucas 1/5/16 Green Apples Apples 2 5 2.5
1 Valter 1/5/16 European Pear Pears 4 10 2.0
2 Erik 1/8/16 Red Apples Apples 2 3 2.1
3 Georg 2/1/16 Asian Pear Pears 8 15 3.8
4 Lucas 2/10/16 Red Apples Apples 6 6 3.6

Creating a mock lookup table

In [33]:
df_subset = df.groupby('Name')['Sale Value ($/lb)'].mean().reset_index()
df_subset.rename(index=str, columns = {'Sale Value ($/lb)': 'Avg Sale Value ($/lb)'}, inplace=True)
df_subset.sort_values(by='Avg Sale Value ($/lb)')
Out[33]:
Name Avg Sale Value ($/lb)
3 Valter 2.00
0 Erik 2.10
2 Lucas 3.05
1 Georg 3.80

Referencing lookup table


In this example, the reference column and lookup column are identically named. If this is not the case, pass left_on = and right_on = arguments within .merge( )

In [37]:
df[['Name','Amt Picked (lbs)']].merge(df_subset, how='left', on='Name')
Out[37]:
Name Amt Picked (lbs) Avg Sale Value ($/lb)
0 Lucas 5 3.05
1 Valter 10 2.00
2 Erik 3 2.10
3 Georg 15 3.80
4 Lucas 6 3.05