Using Pandas to Replicate Advanced Excel Tasks in Python

Excel is a ubiquitous tool for data processing, but many tasks can be performed more efficiently with Python and Pandas. This article walks through three common Excel operations—IF statements, VLOOKUP, and pivot tables—and shows how to implement them in Pandas.

First, the required libraries are imported and the two worksheets (sales and states) are loaded into Pandas dataframes:

import pandas as pd

sales = pd.read_excel('https://github.com/datagy/mediumdata/raw/master/pythonexcel.xlsx', sheet_name = 'sales')

states = pd.read_excel('https://github.com/datagy/mediumdata/raw/master/pythonexcel.xlsx', sheet_name = 'states')

Running sales.head() displays the first rows, which can be compared with the Excel view; note that Pandas indexes start at 0 while Excel starts at row 1, and column labels differ.

IF Function : In Excel the formula =IF([@Sales]>500, "Yes", "No") creates a new column indicating whether sales exceed 500. The same logic in Pandas uses a list comprehension:

df['MoreThan500'] = ['Yes' if x > 500 else 'No' for x in df['Sales']]

List comprehensions provide a concise way to apply conditional logic without writing verbose if/else blocks.

VLOOKUP Equivalent : Excel’s VLOOKUP joins the sales sheet with the states sheet on the city column. In Pandas the merge function achieves the same left join:

sales = pd.merge(sales, states, how='left', on='City')

The parameters are: left dataframe, right dataframe, join type, and the key column(s) to match.

Pivot Table : Excel’s pivot tables summarise data quickly. In Pandas the pivot_table method reproduces this functionality:

sales.pivot_table(index='City', values='Sales', aggfunc='sum')

The call specifies the index (rows), the values to aggregate, and the aggregation function (e.g., sum, mean, max).

Overall, the article shows how to import Excel data into Pandas, replace IF and VLOOKUP with Python code, and generate pivot tables, emphasizing that Python enables reusable, traceable, and scalable analysis compared to manual Excel operations.