Menu

Replace Excel Vlookup in Python Using Pandas (fast & easy) | Pandas Merge Tutorial

Language:

In this tutorial, I will walk you through how to replace the Excel VlookUp in Python by using Pandas. The Excel VlookUp is one of the most often & useful formulas. However, on larger datasets, a VlookUp might slow down the Excel Workbook. Also, Python opens the door to entirely automate Excel processes including VlookUps.

 

📝 Resources:

You can download the Jupyter Notebook & Excel file here:

Download Here

 

👩‍💻 Source Code:

import pandas as pd
from pathlib import Path

DATA_DIR = Path.cwd() / "data"
excel_file_path = DATA_DIR / "data.xlsx"


# ## Read Excel Data
orders = pd.read_excel(excel_file_path, sheet_name="Orders")

returns = pd.read_excel(excel_file_path, sheet_name="Returns")

shipping = pd.read_excel(excel_file_path, sheet_name="Shipping")


# #### Alternative Solution (create dataframe for each worksheet)
excel_file = pd.ExcelFile(excel_file_path)
sheet_names = excel_file.sheet_names

dataframes = {}
for sheet_name in sheet_names:
    dataframes[sheet_name] = pd.read_excel(excel_file, sheet_name=sheet_name)

# Example: Dict Comprehensions
# dataframes_compr = {sheet_name: pd.read_excel(excel_file,sheet_name = sheet_name) for sheet_name in sheet_names}


# ## VlookUp (pd.merge)
df1 = orders.merge(returns, left_on="Order ID", right_on="ID", how="left")

df2 = df1.merge(shipping, left_on="Ship Mode", right_on="Ship Mode", how="left")


# ## Export to `new` Excel workbook
excel_output_path = DATA_DIR / "output.xlsx"
df2.to_excel(excel_output_path, sheet_name="Output", index=False)


# ## Export to `same` Excel workbook
import xlwings as xw  # pip install xlwings

wb = xw.Book(excel_file_path)

# ###  Add DataFrame to `new` worksheet
new_sht = wb.sheets.add("Output")
new_sht.range("A1").options(index=False).value = df2

# ###  Add DataFrame to `existing` worksheet
sht = wb.sheets("Orders")
columns = ["Returned", "Reason", "Ship Mode No."]
sht.range("D1").options(index=False).value = df2[columns]
sht.range("D1:F1").color = (253, 233, 217)