VLOOKUP in Python – A Brief Guide

Filed Under: Pandas
VLOOKUP In Python

In data analytics, people spend the majority of their time in data processing. Unfortunately, many times, people undervalue the importance of data quality and wrangling steps. Some of the fancier names such as modeling, deployment will attract them more. But, with some experience, I can say that you will only spend 20% of your project time on modeling and deployment.

All 80% of the time will go into data quality checking, wrangling, and reporting. For the analysis, we have tons of functions and libraries in python. But, if you are an Excel user, you will be in a great relationship with LOOKUP tables. If Yes, Perfect!. So, now let’s see how we can implement VLOOKUP in python.

You may think, you need a lot of code for this. No, Never!

VLOOKUP in python is way easier than you think. Your VLOOKUP table will be ready in just 3 steps.

  • Define the columns.
  • Set the Index
  • Map the data.

What is VLOOKUP

  • VLOOKUP stands for Vertical look and a part of LOOK UP table family.
  • In layman terms, when you use VLOOKUP, is simple mean that, you will need a particular data from your dataset with it’s corresponding information.
  • Suppose, you are looking for a Car in the dataset, you may also need it’s corresponding information such as color, brand, mpg, gears etc.
  • It will start from Left and move towards right. So, you have to index the values that you are looking for.
  • I hope you got enough information about it. Let’s see how it works!

Load the Data

well, we can make use of mtcars dataset for this purpose. You can download the data here.

#Load the data

import pandas as pd

df = pd.read_csv('mtcars.csv')
Mtcars

You can see the mtcars dataset in the above picture. Now, we can move to the next step.


Step 1: Create Subset of the Data

In the first step, we will create a target data frame or the subset of the data along with a couple of empty columns for corresponding information.

Here, I chose only 5 cars which I am interested in. In other words, these are the 5 cars which we are looking for.

#Cars

df_Target = pd.DataFrame({"model":['Duster 360','Ferrari Dino','Honda Civic','Lotus Europa','Volvo 142E']})
df_Target
Vlookup Subset

After this, we will be creating new attributes to store the values as shown below.

#Create a dataframe for to store subset

df_Target['kmpl'] = ""
df_Target['cyl'] = ""
df_Target['hp'] = ""
df_Target
Vlookup Dataframe
  • Here I have created 3 attributes in the new dataframe.
  • kmpl (per liter)– This attribute hold the values in liter. Initially its in miles per gallon. But, I will store kmpl values here as a part of VLOOKUP table.
  • cyl – Number of cylinders.
  • hp – Horse power.

Step 2: Set the Index Column

As I already told you, VLOOKUP reads from right to left. Hence, it is necessary to set the index column. In this case, I have added Model names as my Index column. It is because I am looking for corresponding information on particular models.

#Set the index

df_Target.set_index('model',inplace= True)
df_Target
Vlookup Index

That’s perfect!

In the next and last step, I will perform some calculations on converting mpg to kmpl. I will be using hp and cyl as it is.


Step 3: Data Mapping

In this final step, we need to map the data from the original dataset to the new data frame along with some calculations as I told before.

#Data mapping

df_Target["kmpl"] = df_Target.index.map(data["mpg"]) * 0.4
df_Target["hp"] = df_Target.index.map(data["hp"])
df_Target["cyl"] = df_Target.index.map(data["cyl"])

df_Target
Vllokup Final
Vllokup Final

Awesome 馃檪

In the above code, I have mapped the data from original data to newly created lookup table / dataframe.

  • I have converted mpg values to kmpl values. (1 mpg = 0.4 kmpl).
  • The cyl and hp attributes were just mapped from the original data.

Ending Note – VLOOKUP in Python

VLOOKUP is one of the most useful data operations which helps you to easily analyze the data. It will give you quick insights into the patterns in the data if any.

As I already told you, VLOOKUP in python is way easy than you think. It takes only 3 steps to implement as shown here. From now on, you can enjoy the excel vibes in python as well.

That’s all for now. Happy Python 馃槢

More read: More about VLOOKUP function

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content