Clean a Messy Sales CSV with Pandas: A Step-by-Step Guide

Tobiloba Oluwayemi — Sat, 26 Sep 2026 14:39:23 +0000

Learn how to turn a broken sales export into a trustworthy dataset—and how to decide what to do with the parts you can't fix.

Real-world CSV files rarely arrive perfectly clean. You may encounter inconsistent column names, extra spaces, mixed date formats, missing values, invalid quantities, malformed emails, and duplicate transactions.

In this tutorial, you'll take a messy sales CSV and clean it systematically with pandas. You'll also learn an important principle of data cleaning: don't automatically "fix" data when you don't have enough information to know what the correct value should be.

By the end, you'll have a cleaner dataset saved as clean_sales.csv, explicit data-quality flags, and a repeatable script you can reuse.

Prerequisites

You'll need:

Python 3.x
pandas 2.2.3 or later
A CSV file named messy_sales.csv

Install pandas if necessary:

bash
pip install pandas

The examples in this article were tested with pandas 2.2.3 and 3.0.2. One small difference is that newer pandas versions may display datetime precision as datetime64[us], while pandas 2.2.3 displays datetime64[ns]. The cleaning logic remains the same.

Step 1: Load and Inspect the Data

Start by importing pandas and reading the CSV file.

python
import pandas as pd

df = pd.read_csv("messy_sales.csv")

print(df.head())
print(df.shape)
print(df.columns.tolist())
Why inspect first?

Before changing anything, you need to understand what you're working with.

The original file contains 30 rows and 9 columns. The column names also contain inconsistent spacing and capitalization:

text
['Order ID', ' customer name ', 'EMAIL', 'Order Date',
'Product Category ', 'Unit Price ($)', 'Qty', 'country', 'Status']

The data also contains issues such as:

inconsistent text formatting
mixed date formats
missing prices and quantities
"two" instead of 2
a negative quantity
malformed email data
duplicate transactions

Inspecting the raw file first gives you a baseline for the cleaning process.

Step 2: Standardize Column Names and Text

Start by making the column names consistent.

python
df.columns = (
df.columns
.str.strip()
.str.replace(r"\s+", "_", regex=True)
.str.lower()
)

print(df.columns.tolist())

You should now have:

text
['order_id', 'customer_name', 'email', 'order_date',
'product_category', 'unit_price_($)', 'qty', 'country', 'status']

Next, clean the text fields.

python
text_columns = [
"customer_name",
"email",
"product_category",
"country",
"status"
]

for column in text_columns:
df[column] = df[column].str.strip()

df["customer_name"] = df["customer_name"].str.title()

for column in ["email", "product_category", "country", "status"]:
df[column] = df[column].str.lower()

This removes unnecessary whitespace and gives similar values a consistent format.

For example:

text
" Amaka Obi " → "Amaka Obi"
"Delivered" → "delivered"
"NIGERIA" → "nigeria"

Standardization makes later filtering, grouping, and duplicate detection much more reliable.

Step 3: Convert Dates and Prices to Numeric Types
Clean the dates

First, inspect the raw dates:

python
print(df[["order_id", "order_date"]].head(12))

Notice these are still raw strings. You can see different formats, including an ambiguous value such as:

text
01/07/2026

Now convert the column:

python
df["order_date"] = pd.to_datetime(
df["order_date"],
format="mixed",
errors="coerce"
)

Check the result:

python
print(df[["order_id", "order_date"]].head(12))

Using format="mixed" allows pandas to handle different date representations within the same column.

The value 01/07/2026 is interpreted as January 7, 2026 by the pandas version used for this tutorial. However, this is exactly why ambiguous dates deserve attention: another dataset may use day-first formatting.

The errors="coerce" argument is also important. If pandas cannot interpret a value as a date, it converts that value to NaT rather than stopping the entire cleaning process.

You should therefore check for invalid dates:

python
print(df["order_date"].isna().sum())

If the result is greater than zero, investigate those rows before using the dates for analysis.

Clean the prices

The price column contains dollar signs, commas, and missing values.

python
df["unit_price"] = (
df["unit_price_($)"]
.str.replace("$", "", regex=False)
.str.replace(",", "", regex=False)
.astype(float)
)

Now unit_price is numeric, making calculations possible.

For example:

text
"$1,200.00" → 1200.0
"$199.99" → 199.99

The CSV reader automatically interprets blank fields and the file's N/A values as missing values, so the conversion works with this dataset. If literal text such as "N/A" remains a string in another CSV, .astype(float) may fail; in that case, use pd.to_numeric(..., errors="coerce") instead.

Step 4: Clean and Inspect Quantities

The quantity column contains a mixture of numbers, text, missing values, and a negative value.

Start by converting the obvious text value:

python
df["qty"] = df["qty"].replace({"two": "2"})
df["qty"] = pd.to_numeric(df["qty"], errors="coerce")

Now inspect unusual quantities:

python
print(
df.loc[
df["qty"].isna() | (df["qty"] < 0),
["order_id", "qty", "status"]
]
)

Expected output:

text
order_id qty status
1005 NaN pending
1021 -1.0 cancelled
1027 NaN shipped

There are three cases here:

1005: quantity is missing.
1021: quantity is negative, but the order is cancelled.
1027: quantity is missing.

You should not automatically replace these values with 0. A missing quantity and a zero quantity mean different things, while the negative value may have meaning because the transaction was cancelled.

Instead, keep the original information and create a flag later.

Step 5: Find and Handle Duplicate Transactions

Duplicate detection should happen before you start filling or modifying missing values. Otherwise, changes made during cleaning can affect your ability to recognize duplicate records.

Create a list of fields that describe the transaction:

python
duplicate_columns = [
"customer_name",
"order_date",
"product_category",
"unit_price",
"qty",
"country",
"status"
]

duplicates = df[
df.duplicated(
subset=duplicate_columns,
keep=False
)
]

print(
duplicates[
["order_id", "customer_name", "order_date",
"product_category", "unit_price", "qty",
"country", "status"]
].sort_values(
["customer_name", "order_date", "order_id"]
)
)

The result identifies these duplicate pairs:

text
order_id customer_name order_date product_category unit_price qty country status
1001 Amaka Obi 2026-01-05 electronics 199.99 2.0 nigeria delivered
1007 Amaka Obi 2026-01-05 electronics 199.99 2.0 nigeria delivered
1004 Chidi Eze 2026-01-10 books 1200.00 1.0 nigeria delivered
1020 Chidi Eze 2026-01-10 books 1200.00 1.0 nigeria delivered

The email column is deliberately not part of duplicate_columns. Email differences or missing emails should not automatically prevent two otherwise identical transactions from being identified as duplicates.

For the first pair, keep 1001 and remove 1007.

For the second pair, keep 1020 because it contains the available customer email information, while 1004 does not.

Check for possible near-duplicates

You should also investigate records that look similar but may represent legitimate repeat purchases.

python
near_duplicates = df[
df.duplicated(
subset=["customer_name", "product_category"],
keep=False
)
]

print(
near_duplicates[
["order_id", "customer_name", "order_date",
"product_category"]
].sort_values(
["customer_name", "order_date", "order_id"]
)
)

This produces:

text
order_id customer_name order_date product_category
1001 Amaka Obi 2026-01-05 electronics
1007 Amaka Obi 2026-01-05 electronics
1004 Chidi Eze 2026-01-10 books
1020 Chidi Eze 2026-01-10 books
1003 Sarah Connor 2026-01-09 home & kitchen
1013 Sarah Connor 2026-01-20 home & kitchen

The first two pairs are the true duplicates you already found. The third pair, 1003 and 1013, has different dates, so you should keep both because they may represent separate purchases.

Now remove only the confirmed duplicate records:

python
df = df[~df["order_id"].isin([1007, 1004])].copy()

print(df.shape)

Expected result:

text
(28, 10)

The dataset now contains 28 transactions.

Step 6: Create Data-Quality Flags

Instead of silently changing questionable values, create flags that make the problems visible.

Missing customer names
python
df["missing_customer_name"] = df["customer_name"].isna()
Invalid emails

A simple email pattern can identify obviously malformed addresses:

python
email_pattern = r"^[^@\s]+@[^@\s]+.[^@\s]+$"

df["invalid_email"] = (
df["email"].notna()
& ~df["email"].str.match(email_pattern)
)

The malformed tom.baker@example address will be flagged.

The missing email associated with order 1004 no longer needs a flag because that duplicate transaction was removed in Step 5. The retained record, 1020, contains an email.

You can confirm that there are no missing emails:

python
print(df["email"].isna().sum())

Expected output:

text
0
Missing prices
python
df["missing_price"] = df["unit_price"].isna()

Don't replace missing prices with 0. A missing price does not mean the product was free.

Missing quantities
python
df["missing_qty"] = df["qty"].isna()
Negative quantities
python
df["negative_qty"] = df["qty"] < 0

This preserves the negative quantity on the cancelled order while making it easy to exclude or investigate later.

Step 7: Remove Redundant Columns and Verify the Result

The original unit_price_($) column has already been cleaned into unit_price, so keeping both would create unnecessary duplication.

Remove the raw version:

python
df = df.drop(columns="unit_price_($)")

Now verify the final structure:

python
print(df.shape)
print(df.columns.tolist())
print(df.dtypes)

Expected shape:

text
(28, 14)

The count is:

8 original columns remaining after dropping unit_price_($)
1 cleaned unit_price column
5 data-quality flags

That gives:

text
8 + 1 + 5 = 14 columns

You can also inspect the final records:

python
print(df.head())

At this point, the dataset has:

standardized column names
cleaned text fields
parsed dates
numeric prices
numeric quantities
confirmed duplicates removed
potential near-duplicates investigated
missing and suspicious values explicitly flagged
the redundant raw price column removed

Finally, save the cleaned dataset:

python
df.to_csv("clean_sales.csv", index=False)
Complete Script

Once you've followed the individual steps, you can combine everything into one reusable script:

python
import pandas as pd

1. Load data

df = pd.read_csv("messy_sales.csv")

2. Standardize column names

df.columns = (
df.columns
.str.strip()
.str.replace(r"\s+", "_", regex=True)
.str.lower()
)

3. Clean text fields

text_columns = [
"customer_name",
"email",
"product_category",
"country",
"status"
]

for column in text_columns:
df[column] = df[column].str.strip()

df["customer_name"] = df["customer_name"].str.title()

for column in ["email", "product_category", "country", "status"]:
df[column] = df[column].str.lower()

4. Parse dates

df["order_date"] = pd.to_datetime(
df["order_date"],
format="mixed",
errors="coerce"
)

5. Clean prices

df["unit_price"] = (
df["unit_price_($)"]
.str.replace("$", "", regex=False)
.str.replace(",", "", regex=False)
.astype(float)
)

6. Clean quantities

df["qty"] = df["qty"].replace({"two": "2"})
df["qty"] = pd.to_numeric(df["qty"], errors="coerce")

7. Find confirmed duplicates

duplicate_columns = [
"customer_name",
"order_date",
"product_category",
"unit_price",
"qty",
"country",
"status"
]

duplicates = df[
df.duplicated(
subset=duplicate_columns,
keep=False
)
]

print("Confirmed duplicates:")
print(
duplicates[
["order_id", "customer_name", "order_date",
"product_category", "unit_price", "qty",
"country", "status"]
].sort_values(
["customer_name", "order_date", "order_id"]
)
)

8. Remove confirmed duplicates

df = df[~df["order_id"].isin([1007, 1004])].copy()

9. Create data-quality flags

df["missing_customer_name"] = df["customer_name"].isna()

email_pattern = r"^[^@\s]+@[^@\s]+.[^@\s]+$"

df["invalid_email"] = (
df["email"].notna()
& ~df["email"].str.match(email_pattern)
)

df["missing_price"] = df["unit_price"].isna()
df["missing_qty"] = df["qty"].isna()
df["negative_qty"] = df["qty"] < 0

10. Remove redundant raw price column

df = df.drop(columns="unit_price_($)")

11. Verify and export

print("Final shape:", df.shape)
print(df.columns.tolist())

df.to_csv("clean_sales.csv", index=False)
Final Takeaway

Good data cleaning is not about forcing every value into a convenient format. It is about making the data consistent, usable, and transparent.

For this dataset, you:

standardized names and text
converted dates and numeric fields
handled obvious formatting errors
identified suspicious quantities
detected and removed confirmed duplicates
investigated near-duplicates instead of deleting them blindly
created flags for unresolved data-quality problems
verified the final structure before exporting

Most importantly, the process preserves information you cannot confidently repair. A missing price remains identifiable as missing, and a negative quantity remains visible rather than being silently changed.

Next Steps

Once the dataset is clean, you can build on it by:

calculating revenue by product category while excluding incomplete records with the quality flags
writing automated tests that assert your cleaning rules and expected row counts
validating the final DataFrame with a library such as pandera

The goal is not just a cleaner CSV. It is a repeatable data-cleaning process you can trust.

DEV Community: Tobiloba Oluwayemi