Pandas for Beginners: Clean a Messy Sales Sheet in Python (2026)

Try this quick self-test before you read on. Open the last sales or fee sheet you handled and ask three questions. Does the same order appear twice? Are some amounts typed as text, like "Rs. 1,200"? Do the dates follow two different formats? If you answered yes even once, pandas for beginners is the right next step for you, because pandas is the Python library built for exactly this kind of cleanup.

In this pandas for beginners guide, we clean one small, realistic sales export from start to finish. Every line of code was run on pandas 3.1.1, the current release, so the output you see is the output you will get.

Work through it and you'll come away able to:

• Load an Excel export into pandas and inspect it before changing anything
• Remove duplicate orders and convert text prices into real numbers
• Fix mixed date formats without silently swapping days and months
• Answer a shop owner's questions with groupby and pivot_table
• Avoid the pandas 3.0 changes that break older tutorial code

What Is Pandas, and When Is It Worth Learning?

Pandas is a free, open-source Python library for working with tables. Its main object is the pandas DataFrame: rows and named columns, much like a worksheet, except every step you take is written as code. That code becomes a record you can rerun next month on a fresh export.

Python use is still growing. Of everyone who answered the 2025 developer survey run by Stack Overflow, 57.9% reported using it, 7 percentage points more than in 2024. If you want to use Python for data, pandas is the natural first library to learn.

Pandas for beginners isn't about replacing Excel. You don't need it for a one-off sheet of 40 rows. Excel handles that well. Pandas starts paying off when one of these is true:

• You repeat the same cleanup every week or month.
• The file has tens of thousands of rows and Excel slows down.
• You combine several exports, such as one file per branch.
• Someone else needs to check exactly what you changed.

What Do You Need Before You Start?

You need Python 3.11 or newer. The pandas page on PyPI lists 3.1.1 as the latest version and Python 3.11 as the minimum. The python.org downloads page shows Python 3.15.2 as the newest release, so any fresh install will be new enough.

There are two simple ways to set up:

1. On your own laptop. Install Python, then open Command Prompt and run pip install pandas openpyxl. The second package lets pandas read .xlsx files.
2. In your browser. Google Colab runs Python notebooks online. Its official FAQ says Colab is free and needs no setup, though free sessions run for at most 12 hours. This route suits students on a low-spec or shared computer.

If you are new to Python syntax itself, read our guide to Python lists, tuples, sets and dictionaries first. Pandas makes more sense once those feel familiar.

The Example Scenario: A Shop's Messy Sales Export

This is an example scenario built for teaching, not a real business. A mobile accessories shop sells chargers, earbuds, covers and power banks to customers in Abbottabad, Mansehra and Haripur. The owner exports three months of orders from his billing app and asks two things: which city brings the most revenue, and how sales moved month by month.

The export has 12 rows and six columns: order_id, date, city, category, price and qty. It looks fine at a glance. Look closer and you find five problems that would give the owner wrong answers.

• Two orders were exported twice.
• Prices are text, written as "Rs. 1,200" in most rows and "650" in one.
• Some dates are 03/09/2026 (day first) and others 2026-09-03.
• Abbottabad also appears as "abbottabad " with a trailing space and as "ATD".
• Two rows have no quantity.

A pivot table built on this raw file would list Abbottabad under three different names and fail to add up the prices at all. That is why cleaning always comes before analysis.

Pandas for Beginners: The Seven-Step Cleaning Walkthrough

Most pandas for beginners lessons jump straight to charts. Here, you run each step in order and check the result before moving on. Python data cleaning goes wrong when you change five things at once and can't tell which change caused a problem.

Step 1: Load the File and Look Before You Touch It

import pandas as pd

df = pd.read_excel("sales.xlsx", engine="openpyxl", dtype={"order_id": str})
print(df.shape)
df.info()
print(df.head())

shape returns (12, 6): 12 rows, 6 columns. info() is the most useful first look. It shows that price is stored as str, not a number, and that qty has only 10 non-null values out of 12. Two problems found before writing a single fix. That one line is also all it takes to read Excel in Python.

Why pass engine="openpyxl"? The read_excel documentation notes that the default reader for .xlsx files will change from openpyxl to calamine in a future version. Naming the engine keeps your script behaving the same after an upgrade. The dtype argument keeps order IDs as text, so an ID like 0042 never loses its leading zeros.

Step 2: Remove Duplicate Rows

print(df.duplicated().sum())   # 2
df = df.drop_duplicates()
print(df.shape)                # (10, 6)

Count first, then drop. If the count had been 300 instead of 2, you would want to know why before deleting anything. Here, orders 1002 and 1010 were exported twice, so 10 real orders remain.

Step 3: Turn "Rs. 1,200" Into a Number

df["price"] = (
    df["price"]
    .str.replace("Rs.", "", regex=False)
    .str.replace(",", "", regex=False)
    .str.strip()
    .astype(int)
)

Each line does one small job: remove the currency label, remove the thousands comma, trim spaces, then convert to whole numbers. Setting regex=False matters, because in a regular expression the dot in "Rs." would match any character.

Step 4: Fix Mixed Dates Without Swapping Day and Month

This is the step where most beginners get a wrong answer without noticing. Our first attempt used one line with format="mixed", dayfirst=True. It ran without an error, but it read 2026-09-03 as 9 March instead of 3 September, and two September orders landed in March and May.

The safer fix is to parse each format on purpose and combine the results:

iso = pd.to_datetime(df["date"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(df["date"], format="%d/%m/%Y", errors="coerce")
df["date"] = iso.fillna(dmy)
print(df["date"].isna().sum())   # 0

With errors="coerce", a value that doesn't match becomes missing instead of crashing. fillna then takes the day-first version wherever the ISO version failed. The final count of 0 confirms that every date was understood.

Step 5: Clean City Names

df["city"] = (
    df["city"].str.strip().str.title()
    .replace({"Atd": "Abbottabad"})
)
print(df["city"].value_counts())

str.strip() removes stray spaces and str.title() fixes capitals. The short form "ATD" becomes "Atd" after title case, so the replace dictionary maps that spelling to the full name. value_counts() now shows just three cities: Abbottabad 5, Mansehra 3 and Haripur 2.

Step 6: Decide What Missing Quantities Mean

Pandas can fill gaps, but it can't tell you what a gap means. The pandas guide to missing data covers isna() to find gaps, dropna() to remove rows and fillna() to replace values. Which one is right is a business decision.

In our example, the owner confirms that a blank quantity means a single item. Only then do we fill it:

df["qty"] = df["qty"].fillna(1).astype(int)
df["revenue"] = df["price"] * df["qty"]
df["month"] = df["date"].dt.to_period("M")

Without that confirmation, the honest choice is to leave the two rows out and say so in your report.

Step 7: Save a Clean Copy and a Summary

report = df.pivot_table(index="month", columns="category",
                        values="revenue", aggfunc="sum", fill_value=0)

with pd.ExcelWriter("sales_clean.xlsx") as writer:
    df.to_excel(writer, sheet_name="clean", index=False)
    report.to_excel(writer, sheet_name="monthly")

Never overwrite the original export. Keeping the raw file and the script side by side means anyone can rerun the cleanup and get the same result.

What the Cleaned Data Tells the Owner

With clean columns, the owner's two questions take one line each. The pandas groupby user guide describes the idea as split, apply, combine: split rows into groups, run a calculation on each group, and join the answers.

df.groupby("city")["revenue"].sum().sort_values(ascending=False)

For the category line, named aggregation gives readable column names:

df.groupby("category").agg(
    orders=("order_id", "count"),
    revenue=("revenue", "sum"),
).sort_values("revenue", ascending=False)

Notice what the raw file would have reported. With "ATD" and "abbottabad " counted separately, the leading city would look smaller than it is. With the date bug from Step 4, September would have lost two orders. Small errors in cleaning turn into wrong business decisions.

What Changed in Pandas 3.0, and Does It Affect You?

Pandas 3.0.0 was released on January 21, 2026, according to its release notes. If a pandas tutorial you find online was written before 2026, three changes are worth knowing.

1. Text columns now use the str dtype. In Step 1, info() printed str rather than object. The pandas 3.0 announcement recommends installing pyarrow alongside pandas for the best performance with this type.
2. Copy-on-Write is the only mode. Any result you take from a DataFrame now behaves like a separate copy. Old advice to sprinkle .copy() everywhere to silence warnings no longer applies.
3. Chained assignment no longer works. A line like df["qty"][df["city"] == "Haripur"] = 5 now changes nothing and shows a ChainedAssignmentError warning. Write it in one step instead: df.loc[df["city"] == "Haripur", "qty"] = 5.

If an old tutorial's code gives a different result on your laptop, check it against these three points first.

The 12 Pandas Methods to Learn First

Pandas has hundreds of methods, but pandas for beginners comes down to a short list for your first month. These 12 did nearly all the work in the walkthrough above.

Once these feel natural, add merge for joining two tables and loc for filtering and updating rows. Those two open up most real reporting tasks.

Pandas for Beginners: Mistakes to Avoid With Messy Data

• Cleaning without looking first. Skipping info() means you discover a text price column only when a sum fails.
• Trusting a date parse because it didn't crash. Step 4 ran without an error and was still wrong. Always check a few converted dates by eye.
• Filling gaps with zero by habit. A zero quantity and an unknown quantity are different facts.
• Overwriting the raw export. Keep the original. Write cleaned data to a new file.
• Copying pre-2026 code blindly. Chained assignment changes nothing in pandas 3.

When an error does appear, our list of common Python errors and their fixes explains the messages beginners see most.

Pandas, Excel or Power BI: Which Tool for Which Task?

These tools work together; they don't compete. Excel is quickest for a small sheet you look at once; our walkthrough of a pivot table in Excel shows that route. Power BI is strongest when a manager wants an interactive dashboard that refreshes. Pandas fits the middle: repeatable cleaning, many files at once, and logic you can read line by line.

One practical pattern is to clean data with pandas, save a tidy file, then build the dashboard in Power BI. If that appeals to you, PSTA's Power BI course is a sensible next step after Python.

Learning Pandas Step by Step at PSTA

Pandas is taught inside PSTA's Advanced Python Programming Course, in the Data Science Essentials module with NumPy and Matplotlib. The course page lists these details:

• Duration: 12 weeks, with a new batch every month
• Starting point: no prior coding; the course begins with installing Python
• Fee: Rs. 30,000 against a listed regular fee of Rs. 40,000; you can pay it in 2 parts
• Related project: a data analysis report that loads a real dataset with pandas, summarizes it and plots charts
• Study options: classes at the Mandian campus in Abbottabad, live online from any city, or a mix of both

Before you start, the list of Python projects for beginners gives you a sense of the practice work. Programs that pair well with Python sit on the courses page. When you're ready, the admission form and process are one click away, or you can message +92 314 5424399 on WhatsApp if you'd like to check which time slots are open.

Your Weekend Exercise: Clean One Real Sheet

The best pandas for beginners practice is your own data. Pick one export you already have: a fee register, a stock list or a month of sales. Make a copy, load it with read_excel, and run info() and value_counts() on two columns. Write down every problem you spot before fixing any of them. That list is your first cleaning plan, and working through it will teach you more pandas than a week of reading.