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)