pandas: Clean and Analyse Excel Data

pandas is a free Python library (a ready-made toolbox of code) for working with tables of data, the same kind of rows and columns you use in Excel. Finance teams use it to clean messy sales and expense files, total them by region or customer, build pivot tables and look up values from a second file, all in a few lines that you can run again next month. In this lesson we take one small, messy sales file and clean and analyse it step by step, and for every step you will see the Excel way next to the pandas way.

What you will learn

• How to open CSV and Excel files with read_csv and read_excel
• How to look at your data with head, info and describe
• How to select columns, filter rows, fix text and dates, remove duplicates and handle blanks
• How to total data with groupby and build a pivot table with pivot_table
• How to look up values from another file with merge, the pandas version of VLOOKUP
• How to save your results back to an Excel workbook with several sheets

Before you start

You should know the basics of Python: variables, lists and running a cell in a notebook. If you are new, start with Python for Finance: Your First Steps.

pandas is already installed in Google Colab and in Anaconda. pandas uses another library called openpyxl to read and write .xlsx files, and that comes with both too. If you use plain Python, install them once from the command prompt with pip install pandas openpyxl.

At the top of every notebook, run this line first:

import pandas as pd

This loads pandas and gives it the short name pd. Everyone uses this short name, so you will see pd. in all pandas code.

The sample data

We will work with the April sales of a small stationery supplier. Here is the data:

InvoiceNo	Date	Customer	Region	Product	Qty	Amount
INV-101	03-04-2026	Sharma Traders	North	Printer Paper	20	6000
INV-102	05-04-2026	Gupta Stores	West	Ink Cartridge	5	7500
INV-103	05-04-2026	sharma traders	North	Ink Cartridge	2	3000
INV-104	09-04-2026	Mehta & Sons	South	Printer Paper	50	15000
INV-105	12-04-2026	Gupta Stores	West	Stapler	10
INV-105	12-04-2026	Gupta Stores	West	Stapler	10
INV-106	15-04-2026	Rao Enterprises	South	Printer Paper	30	9000
INV-107	18-04-2026	Mehta & Sons	South	Ink Cartridge	4	6000
INV-108	22-04-2026	Iyer Agencies		Stapler	8	2400
INV-109	28-04-2026	Rao Enterprises	South	Ink Cartridge	6	9000

This file has the problems you meet in real office data:

• INV-103 has the customer name in small letters with an extra space at the end.
• INV-105 appears twice, and its Amount is blank.
• INV-108 has no Region.
• The dates are written as day-month-year text, which a computer cannot sort or add days to until we fix them.

To make the file, open Notepad (or TextEdit on a Mac in plain text mode), paste the lines below exactly, and save it as sales.csv. A CSV file (comma separated values) is a plain text table where commas separate the columns. Note the space after sharma traders and the empty spots at the end of the INV-105 lines.

InvoiceNo,Date,Customer,Region,Product,Qty,Amount
INV-101,03-04-2026,Sharma Traders,North,Printer Paper,20,6000
INV-102,05-04-2026,Gupta Stores,West,Ink Cartridge,5,7500
INV-103,05-04-2026,sharma traders ,North,Ink Cartridge,2,3000
INV-104,09-04-2026,Mehta & Sons,South,Printer Paper,50,15000
INV-105,12-04-2026,Gupta Stores,West,Stapler,10,
INV-105,12-04-2026,Gupta Stores,West,Stapler,10,
INV-106,15-04-2026,Rao Enterprises,South,Printer Paper,30,9000
INV-107,18-04-2026,Mehta & Sons,South,Ink Cartridge,4,6000
INV-108,22-04-2026,Iyer Agencies,,Stapler,8,2400
INV-109,28-04-2026,Rao Enterprises,South,Ink Cartridge,6,9000

We also need a second file with customer details, for the lookup step. Type this into a new Excel workbook, starting in cell A1 of the first sheet, and save it as customers.xlsx in the same folder:

Customer	City	CreditDays
Sharma Traders	Delhi	30
Gupta Stores	Mumbai	45
Mehta & Sons	Chennai	30
Rao Enterprises	Hyderabad	60
Iyer Agencies	Bengaluru	30

Here is the plan for the whole lesson. Read it from top to bottom. Every cleaning job, big or small, follows these same stages.

Step 1: Read the file

sales = pd.read_csv("sales.csv")

pd.read_csv() opens the CSV file and turns it into a DataFrame, which is pandas’ word for a table with rows and columns, just like a worksheet. We store it in a variable named sales.

If your data is in an Excel workbook instead, use read_excel and say which sheet you want:

sales = pd.read_excel("sales.xlsx", sheet_name="Sheet1")

Step 2: Look at the data first

Never start cleaning before you look. pandas gives you three quick ways.

head() shows the first 5 rows:

print(sales.head())

  InvoiceNo        Date         Customer Region        Product  Qty   Amount
0   INV-101  03-04-2026   Sharma Traders  North  Printer Paper   20   6000.0
1   INV-102  05-04-2026     Gupta Stores   West  Ink Cartridge    5   7500.0
2   INV-103  05-04-2026  sharma traders   North  Ink Cartridge    2   3000.0
3   INV-104  09-04-2026     Mehta & Sons  South  Printer Paper   50  15000.0
4   INV-105  12-04-2026     Gupta Stores   West        Stapler   10      NaN

The numbers 0, 1, 2 on the left are the index, pandas’ own row numbers, which start from 0. NaN (not a number) is how pandas shows a blank cell.

info() lists every column, how many cells are filled, and the data type, meaning what kind of values the column holds:

sales.info()

<class 'pandas.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 7 columns):
 #   Column     Non-Null Count  Dtype
---  ------     --------------  -----
 0   InvoiceNo  10 non-null     str
 1   Date       10 non-null     str
 2   Customer   10 non-null     str
 3   Region     9 non-null      str
 4   Product    10 non-null     str
 5   Qty        10 non-null     int64
 6   Amount     8 non-null      float64
dtypes: float64(1), int64(1), str(5)
memory usage: 692.0 bytes

There are 10 rows. Region has only 9 filled cells and Amount only 8, so there are blanks. And Date is str (text), not a real date. int64 means whole numbers and float64 means decimal numbers.

describe() gives quick statistics for the number columns, like a mini summary sheet:

print(sales.describe())

             Qty        Amount
count  10.000000      8.000000
mean   14.500000   7237.500000
std    15.064675   3981.362832
min     2.000000   2400.000000
25%     5.250000   5250.000000
50%     9.000000   6750.000000
75%    17.500000   9000.000000
max    50.000000  15000.000000

count is the number of filled cells, mean is the average, min and max are the smallest and biggest values, and 50% is the median (the middle value). A quick look at min and max often catches a wrong entry, like a negative quantity or an extra zero.

Step 3: Select columns

To pick some columns, put their names in a list inside the square brackets. That is why you see two pairs of brackets:

print(sales[["Customer", "Amount"]].head(3))

          Customer  Amount
0   Sharma Traders  6000.0
1     Gupta Stores  7500.0
2  sharma traders   3000.0

For one column, use single brackets: sales["Amount"]. This gives you one column on its own, which you can add up with sales["Amount"].sum().

Step 4: Filter rows

Filtering in pandas works like the filter arrow in Excel. You write a condition inside the brackets, and pandas keeps only the rows where it is true.

Invoices above Rs 8,000:

print(sales[sales["Amount"] > 8000])

  InvoiceNo        Date         Customer Region        Product  Qty   Amount
3   INV-104  09-04-2026     Mehta & Sons  South  Printer Paper   50  15000.0
6   INV-106  15-04-2026  Rao Enterprises  South  Printer Paper   30   9000.0
9   INV-109  28-04-2026  Rao Enterprises  South  Ink Cartridge    6   9000.0

For two conditions together, put each one in round brackets and join them with & (and). Use | for or.

print(sales[(sales["Region"] == "South") & (sales["Product"] == "Printer Paper")])

  InvoiceNo        Date         Customer Region        Product  Qty   Amount
3   INV-104  09-04-2026     Mehta & Sons  South  Printer Paper   50  15000.0
6   INV-106  15-04-2026  Rao Enterprises  South  Printer Paper   30   9000.0

Step 5: Clean the text

sharma traders (small letters and a space at the end) and Sharma Traders look the same to you, but to a computer they are two different customers. Totals and lookups will split them. Let us fix the Customer column:

sales["Customer"] = sales["Customer"].str.strip().str.title()

• .str tells pandas “treat this column as text”.
• .strip() removes spaces at the start and end, like Excel’s =TRIM().
• .title() makes the first letter of every word a capital, like Excel’s =PROPER().
• sales["Customer"] = ... writes the cleaned values back into the same column.

Step 6: Fix the dates

Is 05-04-2026 the 5th of April or the 4th of May? A person in India reads it as 5 April. Some software reads it as May 4. So we tell pandas the exact pattern:

sales["Date"] = pd.to_datetime(sales["Date"], format="%d-%m-%Y")

In the pattern, %d is the day, %m is the month and %Y is the four-digit year, with dashes between them as in the file. Now the column holds real dates that you can sort, group by month or add days to.

Step 7: Remove duplicate rows

print(sales.duplicated().sum())
sales = sales.drop_duplicates()
print(len(sales))

1
9

duplicated() marks every row that is an exact copy of an earlier row, and .sum() counts them. We have 1 duplicate (the second INV-105). drop_duplicates() removes it, and len() confirms we now have 9 rows. This is the same as Data > Remove Duplicates in Excel.

Step 8: Handle the blanks

First, count the blanks in every column:

print(sales.isna().sum())

InvoiceNo    0
Date         0
Customer     0
Region       1
Product      0
Qty          0
Amount       1
dtype: int64

isna() marks blank cells and .sum() counts them per column. Region has 1 blank and Amount has 1. Now we fill them:

sales["Region"] = sales["Region"].fillna("Unknown")
sales["Amount"] = sales["Amount"].fillna(0)

fillna() puts a value into the blank cells. We use “Unknown” for the missing region so that row is still counted, and 0 for the missing amount.

Filling with 0 is a choice, not always the right one. A blank amount on a real invoice is a question for the sales team, not a zero. This chart shows how to think about every blank.

Here is the clean table:

print(sales)

  InvoiceNo       Date         Customer   Region        Product  Qty   Amount
0   INV-101 2026-04-03   Sharma Traders    North  Printer Paper   20   6000.0
1   INV-102 2026-04-05     Gupta Stores     West  Ink Cartridge    5   7500.0
2   INV-103 2026-04-05   Sharma Traders    North  Ink Cartridge    2   3000.0
3   INV-104 2026-04-09     Mehta & Sons    South  Printer Paper   50  15000.0
4   INV-105 2026-04-12     Gupta Stores     West        Stapler   10      0.0
6   INV-106 2026-04-15  Rao Enterprises    South  Printer Paper   30   9000.0
7   INV-107 2026-04-18     Mehta & Sons    South  Ink Cartridge    4   6000.0
8   INV-108 2026-04-22    Iyer Agencies  Unknown        Stapler    8   2400.0
9   INV-109 2026-04-28  Rao Enterprises    South  Ink Cartridge    6   9000.0

There are no blanks left, the dates now show as real dates in year-month-day order, and both Sharma Traders rows match. If you run sales.info() again, Date shows the type datetime64. Row 5 is missing from the index because that was the duplicate we removed. That is fine.

Step 9: Total by group with groupby

groupby is how you get “total sales per region”, like SUMIF for every region at once:

by_region = sales.groupby("Region")["Amount"].sum()
print(by_region)

Region
North       9000.0
South      39000.0
Unknown     2400.0
West        7500.0
Name: Amount, dtype: float64

Read it as: “group the rows by Region, take the Amount column, and add it up in each group”.

To get more than one result per group, use agg() (short for aggregate). Here we count the invoices and add the amounts, then sort with the biggest region first:

summary = sales.groupby("Region").agg(Invoices=("InvoiceNo", "count"), Total=("Amount", "sum"))
summary = summary.sort_values("Total", ascending=False)
print(summary)

         Invoices    Total
Region
South           4  39000.0
North           2   9000.0
West            2   7500.0
Unknown         1   2400.0

Inside agg(), Invoices=("InvoiceNo", "count") means “make a new column named Invoices that counts the InvoiceNo values”. You can also use "mean", "min" and "max".

Step 10: Build a pivot table

A pivot table puts one field down the side and another across the top. Here we put Region down the side and Product across:

pivot = pd.pivot_table(sales, values="Amount", index="Region", columns="Product",
                       aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
print(pivot)

Product  Ink Cartridge  Printer Paper  Stapler    Total
Region
North           3000.0         6000.0      0.0   9000.0
South          15000.0        24000.0      0.0  39000.0
Unknown            0.0            0.0   2400.0   2400.0
West            7500.0            0.0      0.0   7500.0
Total          25500.0        30000.0   2400.0  57900.0

Each part matches a piece of the Excel PivotTable Fields pane:

pandas	Excel PivotTable
values="Amount"	Amount in the Values box
index="Region"	Region in the Rows box
columns="Product"	Product in the Columns box
aggfunc="sum"	Sum of Amount
fill_value=0	Show 0 in empty cells
margins=True	Grand totals, here named Total

The grand total, 57,900, is a good check figure. It must equal sales["Amount"].sum().

Step 11: Look up details from another file with merge

In Excel you would use VLOOKUP or XLOOKUP to bring each customer’s city and credit days from customers.xlsx into the sales sheet. In pandas you use merge:

customers = pd.read_excel("customers.xlsx")
merged = sales.merge(customers, on="Customer", how="left")
print(merged[["InvoiceNo", "Customer", "City", "CreditDays"]])

  InvoiceNo         Customer       City  CreditDays
0   INV-101   Sharma Traders      Delhi          30
1   INV-102     Gupta Stores     Mumbai          45
2   INV-103   Sharma Traders      Delhi          30
3   INV-104     Mehta & Sons    Chennai          30
4   INV-105     Gupta Stores     Mumbai          45
5   INV-106  Rao Enterprises  Hyderabad          60
6   INV-107     Mehta & Sons    Chennai          30
7   INV-108    Iyer Agencies  Bengaluru          30
8   INV-109  Rao Enterprises  Hyderabad          60

• on="Customer" is the column that both tables share, like the lookup value in VLOOKUP.
• how="left" keeps every row of the sales table, even if a customer is missing from the customers file. A missing match shows as NaN, where VLOOKUP would show #N/A.

Now that we have the credit days, we can work out each invoice’s due date:

merged["DueDate"] = merged["Date"] + pd.to_timedelta(merged["CreditDays"], unit="D")
print(merged[["InvoiceNo", "Date", "CreditDays", "DueDate"]])

  InvoiceNo       Date  CreditDays    DueDate
0   INV-101 2026-04-03          30 2026-05-03
1   INV-102 2026-04-05          45 2026-05-20
2   INV-103 2026-04-05          30 2026-05-05
3   INV-104 2026-04-09          30 2026-05-09
4   INV-105 2026-04-12          45 2026-05-27
5   INV-106 2026-04-15          60 2026-06-14
6   INV-107 2026-04-18          30 2026-05-18
7   INV-108 2026-04-22          30 2026-05-22
8   INV-109 2026-04-28          60 2026-06-27

pd.to_timedelta(..., unit="D") turns the credit days into a length of time in days, which can be added to a date. This only works because we fixed the Date column in Step 6.

Step 12: Write the results back to Excel

Finally, save everything in one workbook with three sheets:

with pd.ExcelWriter("sales_report.xlsx") as writer:
    merged.to_excel(writer, sheet_name="Clean data", index=False)
    summary.to_excel(writer, sheet_name="By region")
    pivot.to_excel(writer, sheet_name="Pivot")
print("Saved sales_report.xlsx")

Saved sales_report.xlsx

• pd.ExcelWriter opens a new workbook. The with block closes and saves it for you at the end.
• Each to_excel writes one table to its own sheet.
• index=False leaves out pandas’ row numbers 0, 1, 2, which nobody needs in the clean data. For the summary and pivot we keep the index, because there it holds the region names.

Open sales_report.xlsx in Excel and you will find the sheets Clean data, By region and Pivot.

Excel and pandas side by side

What you want	In Excel	In pandas
Open a file	File > Open	pd.read_excel("sales.xlsx")
See the first rows	Scroll to the top	sales.head()
Pick columns	Hide the other columns	sales[["Customer", "Amount"]]
Filter rows	Filter arrow on the header	sales[sales["Amount"] > 8000]
Remove extra spaces	=TRIM(C2)	.str.strip()
Proper case	=PROPER(C2)	.str.title()
Text to real dates	Data > Text to Columns	pd.to_datetime(..., format="%d-%m-%Y")
Remove duplicates	Data > Remove Duplicates	sales.drop_duplicates()
Fill blanks	Go To Special > Blanks	.fillna(0)
Total per group	=SUMIF(D:D,"South",G:G)	sales.groupby("Region")["Amount"].sum()
Pivot table	Insert > PivotTable	pd.pivot_table(...)
Look up a value	=VLOOKUP(C2,Customers!A:C,2,FALSE)	sales.merge(customers, on="Customer", how="left")
Save as Excel	File > Save As	df.to_excel("file.xlsx", index=False)

Common mistakes

• Merging before cleaning the text. If you skip Step 5, sharma traders with a space does not match Sharma Traders in the customers file, and its city comes back blank:

  InvoiceNo         Customer     City
0   INV-101   Sharma Traders    Delhi
1   INV-102     Gupta Stores   Mumbai
2   INV-103  sharma traders       NaN
3   INV-104     Mehta & Sons  Chennai

• Using and instead of & in a filter, or forgetting the round brackets around each condition. Write (sales["Region"] == "South") & (sales["Qty"] > 5).
• One pair of brackets for many columns. sales["Customer", "Amount"] gives an error. A list of columns needs two pairs: sales[["Customer", "Amount"]].
• Letting pandas guess the date format. Always give format="%d-%m-%Y" (or whatever your file uses), or 05-04-2026 may become 4 May.
• Forgetting to save the result. sales.drop_duplicates() on its own only shows a new table. Write sales = sales.drop_duplicates() to keep it.
• The output file is open in Excel. On Windows, saving over a workbook that is open gives a PermissionError. Close it in Excel and run the cell again.

Quick recap

• pd.read_csv() and pd.read_excel() turn a file into a DataFrame, pandas’ version of a worksheet.
• Look before you clean: head(), info() and describe() show the blanks, the types and odd values.
• Clean with str.strip(), str.title(), pd.to_datetime(), drop_duplicates() and fillna(), and count rows before and after.
• Analyse with groupby (like SUMIF), pivot_table (like a PivotTable) and merge (like VLOOKUP).
• Save several tables to one workbook with pd.ExcelWriter and to_excel.

Try it yourself

Use the same sales.csv and customers.xlsx files.

1. Find the total quantity sold per product with groupby. You should get Ink Cartridge 17, Printer Paper 100 and Stapler 18.
2. Filter the clean data to show only the invoices of Mehta & Sons. There should be two, INV-104 and INV-107.
3. Build a pivot table with Customer down the side and Region across, showing the sum of Amount.
4. Change the blank Amount of INV-105 to 3000 instead of 0, run all the steps again and check that the grand total in the pivot is now 60,900.

Where to go next

• Python for Finance: Your First Steps: variables, lists, loops and functions, if you need a refresher.
• Automate a Monthly Excel Report with Python: read a whole folder of monthly files and write a formatted summary workbook.
• Create a PivotTable to analyze worksheet data: the Excel side of the pivot tables you built here.
• Power BI Power Query: Import and Clean Data: the same cleaning steps without code, inside Power BI.