Automate Excel Data Cleaning with Python pandas: Step-by-Step Guide

Many professionals spend a lot of time manually cleaning and reorganizing Excel spreadsheets. This guide shows how to automate that process using Python and the powerful pandas library.

We start with a source CSV file (

) and a grouping XLS file (

). The goal is to produce a new spreadsheet that contains selected columns, merges rows according to the group list, and adds a calculated column "K/60".

pandas is a NumPy‑based tool designed for data analysis, offering high‑performance functions for manipulating large datasets.

First, import pandas and read the files:

import pandas as pd
# Read group.xls
group = pd.read_excel("group.xls", header=None)
group.columns = ["分组", "角色"]
print(group)
# Read source.csv
source = pd.read_csv("source.csv")
print(source)

Next, select the required columns from source ("角色", "编号", "数据B", "数据C", "数据D", "数据K"):

filter_merge = source.iloc[:, [0, 2, 4, 5, 6, 13]]
print(filter_merge)

Merge the two tables on the common "角色" column:

combine = pd.merge(group, filter_merge, on="角色")

Insert the calculated column "数据K/60" (rounded to two decimals):

combine.insert(1, "数据K/60", round(filter_merge["数据K"] / 60, 2))

Finally, write the result to a new Excel file:

combine.to_excel(excel_writer="result.xlsx", index=False)

The generated spreadsheet looks like this:

Common issues include import errors (resolve by installing missing modules) and offline installation on corporate machines (download packages elsewhere and transfer them). The example can be adapted to any similar Excel‑based reporting task.