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)