My Boss Needed Sales Numbers ASAP: Cleaning a CSV with Python and Pandas
My Boss Needed Sales Numbers ASAP: Cleaning a CSV with Python and Pandas
The email arrived at 4:55 on a Friday afternoon. My boss needed the quarter’s sales numbers for a report the next morning.
There was just one problem. The sales export was a mess.
Dates appeared in three different formats. Dollar amounts included commas and dollar signs. Region names had inconsistent capitalization and extra spaces. There was a blank row, and one order appeared twice.
I could have cleaned the file manually. But that would solve the problem once. A small Python pipeline could solve it again tomorrow.
The idea was simple:
Dirty sales data → Python + pandas → Clean CSV
The program is just the pipe. What matters is what happens to the data as it moves through it.
The Data Going In
Here is a small version of the raw sales export:
order_id,region,amount,order_date
0042, north ,"$1,200.50",03/14/2026
0043,NORTH,"$899.00",2026-03-14
0043,NORTH,"$899.00",2026-03-14
0044,south,"$1,050.25",March 15 2026
,,,
0045,East ,-$40.00,03/16/2026
Even in six rows, we can see most of the problems.
The duplicate
0043
order needs to go. So does the blank row. The regions should follow one naming
convention, the amounts need to become real numbers, and the dates need a
consistent format.
One detail is easy to overlook: the order IDs begin with zero. We want
0042
to stay
0042, not become
42.
So before writing any code, we already know what our pipeline needs to accomplish.
The Program
We can handle the entire job with pandas. Rather than treating every operation as a separate programming lesson, let's keep the pipeline together so we can see the complete path the data takes.
import pandas as pd
# Input: Read the raw sales data as text.
sales = pd.read_csv("sales_export.csv", dtype=str)
# Process: Remove empty rows and duplicate records.
sales = sales.dropna(how="all")
sales = sales.drop_duplicates()
# Process: Standardize region names.
sales["region"] = sales["region"].str.strip().str.title()
# Process: Convert sales amounts into numbers.
sales["amount"] = (
sales["amount"]
.str.replace(r"[$,]", "", regex=True)
.astype(float)
)
# Process: Convert the different date formats into dates.
sales["order_date"] = pd.to_datetime(
sales["order_date"],
format="mixed"
)
# Check the cleaned data before writing the output.
assert sales["amount"].notna().all()
assert sales["order_date"].notna().all()
# Format dates consistently for the output file.
sales["order_date"] = sales["order_date"].dt.strftime("%Y-%m-%d")
# Output: Write the cleaned data to a new CSV file.
sales.to_csv("sales_clean.csv", index=False)
Read that program from top to bottom and the data flow becomes pretty easy to follow.
The raw CSV comes in as text. Empty and duplicate records disappear. Region names are standardized. Dollar amounts become numbers. Dates become consistent dates. We make a couple of simple checks and then write everything to a new CSV.
That's the pipeline.
The Data Coming Out
Our original data looked inconsistent and required somebody to interpret it.
The new
sales_clean.csv
looks like this:
order_id,region,amount,order_date
0042,North,1200.5,2026-03-14
0043,North,899.0,2026-03-14
0044,South,1050.25,2026-03-15
0045,East,-40.0,2026-03-16
Notice what changed.
The blank row is gone. The duplicate order is gone.
north,
NORTH, and
south
have been normalized. The dollar signs and commas are gone from the amounts.
Three different date styles now use the same format.
And
0042
is still
0042.
That's our real result. We didn't merely run some pandas methods. We transformed one data artifact into another:
sales_export.csv
→ Python + pandas →
sales_clean.csv
Now It's the Manager's Data
At this point, our Python program has done its job.
The manager can open
sales_clean.csv
in Excel, create the chart she needs, add it to the quarterly report, and send
the finished report to her boss. She doesn't need to know how pandas
standardized a date or removed a duplicate. She needs data she can trust.
That distinction is important.
A useful data pipeline doesn't exist to show off the code in the middle. It exists to take data that isn't ready for the next person and turn it into data that is.
The code is the pipe.
The data, and where it's going, is the story.
