VLOOKUP in Python – Quick and Easy Pandas Script


Learn how you can replace using VLOOKUP with Python script using Pandas. You will learn the basic of table joins that are more effective way of performing lookups than VLOOKUP. Please remember to subscribe if you have any question or concerns.
You will learn the following steps:

1. Loading Multiple Excel Sheets
2. Saving variable
3. Merging Sheets

The dataset and notebook will be on my Github

Choose the dataset hotel_data.xlsx and Merging Files Jupyter Notebook
#Python, #Pandas, #VLookup #excel



One Comment

  • Absent Data Channel
    January 23, 2021

    Very good tutorial. This is my solution which includes moving the lookup values to appropriate column positions in the main_table dataframe.

    import pandas as pd

    with pd.ExcelFile('inputs/hotel_data.xlsx') as xl:
    dataframes = [xl.parse(sheet_name=sheet) for sheet in xl.sheet_names]

    main_table, market_table, meal_table = dataframes

    merged_table = main_table.merge(meal_table, on='meal')
    merged_table.insert(13, 'Cost', merged_table.pop('Cost'))

    merged_table = merged_table.merge(market_table)
    merged_table.insert(16, 'Discount', merged_table.pop('Discount'))

    merged_table.loc[:, 'meal':].head()

Write a comment