Data and monitoring · Beginner · 22 min
How to Clean and Summarise a CSV File with pandas
By Padding · Updated 16 Nov 2026
Python 3.15 · pandas 3.1
You will take a messy orders CSV with stray spaces, mixed spellings, money written as text, odd dates, missing values and a duplicate row, and clean it with pandas 3.1 on Python 3.15 into a tidy file plus per-country and per-month summaries.
On this page
Real data is rarely tidy. A spreadsheet export has names with trailing spaces, countries spelled three ways, amounts like $1,200.50 that are text and not numbers, dates in two formats, blanks, and the same row twice. Add those up wrongly and the report is wrong. pandas is the Python library for this work. It loads a table into a DataFrame and gives you short, readable operations on whole columns.
You will work on a nine-row orders file with every one of those problems. Each step fixes one kind of mess in a small function, and you check the result after each. At the end you save a clean copy, leaving the original untouched, and produce two summaries. The steps use pandas 3.1. It changed some behaviour from older versions, and the places where that matters are marked. The full list is in the pandas 3.0 release notes, https://pandas.pydata.org/docs/whatsnew/v3.0.0.html.
Step 1 · Create the project and the messy file
Make a folder, a project with pandas 3.1, and the CSV. The version range accepts patch releases of 3.1 only:
mkdir -p ~/csv-demo
cd ~/csv-demo
uv init --no-package
uv add "pandas>=3.1,<3.2"Save the practice data. It is on purpose a mess: padded names, UK and uk and United Kingdom, an upper-case email, amounts with $ and commas, two date formats and one that is not a date, a missing email, a missing order id, an amount of n/a, and order 1002 twice:
cat > orders.csv <<'CSV'
order_id,customer,email,country,order_date,amount,status
1001, Ada Lovelace ,ADA@Example.com,UK,2026-10-03,"$1,200.50",shipped
1002,Grace Hopper,grace@example.com,usa,"Oct 5, 2026",89.99,Shipped
1003,Linus T,linus@example.com,United Kingdom,2026-10-12,250,SHIPPED
1004,Margaret H,,U.S.A.,2026-11-01,"1,050",pending
1002,Grace Hopper,grace@example.com,usa,"Oct 5, 2026",89.99,Shipped
,Alan Turing,alan@example.com,uk,2026-11-03,40,shipped
1005,Katherine J,katherine@example.com,Usa,2026-11-07,n/a,cancelled
1006,Dennis R,dennis@example.com,Germany,2026-11-09,310.00,shipped
1007,Barbara L,barbara@example.com,germany ,not a date,75.25,pending
CSVCheck it worked
uv run python -c "import pandas as pd; print(pd.__version__)"The output is 3.1.1, or a later 3.1 patch release.
Step 2 · Load the file and look before you touch it
Start the module that will hold your cleaning functions. For now it has a loader and two display settings, which make pandas print all columns instead of hiding the middle ones with ...:
cat > clean.py <<'PY'
import pandas as pd
pd.set_option("display.width", 200)
pd.set_option("display.max_columns", 20)
def load(path="orders.csv"):
return pd.read_csv(path)
PYLook at what pandas made of the file, the column types and the missing values:
uv run python - <<'PY'
import clean
df = clean.load()
print(df)
df.info()
print(df.isna().sum())
PYThe table shows the nine rows exactly as they are in the file, with order_id as 1001.0. df.info() is the most useful first look. Its Dtype column shows float64 for order_id and str for the six text columns. The order ids are floats because one is missing, and pandas stores missing numbers as NaN (not a number), which is a float. The Non-Null Count column shows 8 of 9 for order_id, email and amount. The amount column is text (str), so you cannot add it up yet. Notice too that the n/a in the amount of order 1005 already counts as missing: read_csv treats n/a, NA, NaN and empty cells as missing values by default.
str is new in pandas 3. Before, text columns had the generic type object. Since pandas 3.0 they use a dedicated string type by default, which is faster and uses less memory. Code that tested for dtype == object needs dtype == "str" or pd.api.types.is_string_dtype.
Step 3 · Tidy the text columns
Names have spaces, emails have mixed case, countries have many spellings, and statuses differ in case. Add a lookup table for countries and a function that cleans all four columns. The string methods live under .str, and work on the whole column at once:
cat >> clean.py <<'PY'
COUNTRIES = {
"uk": "United Kingdom",
"united kingdom": "United Kingdom",
"usa": "United States",
"u.s.a.": "United States",
"germany": "Germany",
}
def tidy_text(df):
return df.assign(
customer=df["customer"].str.strip(),
email=df["email"].str.strip().str.lower(),
country=df["country"].str.strip().str.lower().map(COUNTRIES),
status=df["status"].str.strip().str.lower(),
)
PYdf.assign(...) returns a new table with those columns replaced, so the table you passed in is not changed. The country is lower-cased and then looked up with .map(COUNTRIES). A value that is not in the dictionary becomes missing, which is useful: you find the spellings you forgot. Check it:
uv run python - <<'PY'
import clean
df = clean.tidy_text(clean.load())
print(df[["customer", "email", "country", "status"]])
print("unknown countries:", df["country"].isna().sum())
PYThe customer column has no stray spaces, the emails are lower case, every country is one of United Kingdom, United States or Germany, the statuses are all lower case, and unknown countries: 0. If your own data gives a number above zero, print the rows where df["country"].isna() is true and add their spellings to COUNTRIES.
Step 4 · Deal with missing values
Decide for each column what a gap means. Here, a row without an order_id cannot be identified, so drop it. A missing email is allowed but should be explicit, so fill it with unknown. The order id also becomes a whole number now that nothing is missing:
cat >> clean.py <<'PY'
def handle_missing(df):
df = df.dropna(subset=["order_id"])
return df.assign(
order_id=df["order_id"].astype("int64"),
email=df["email"].fillna("unknown"),
)
PY
uv run python - <<'PY'
import clean
df = clean.handle_missing(clean.tidy_text(clean.load()))
print(df[["order_id", "customer", "email"]])
print(df.dtypes["order_id"])
PYEight rows remain: the row for Alan Turing is gone, Margaret H has unknown as her email, and the id column prints as int64 and shows 1001 rather than 1001.0. Dropping is a decision, not a default. Whether to drop, fill or keep a gap depends on what you will do with the data, and it is worth writing the reason in a comment in your own scripts. The amount of order 1005 stays missing on purpose: it is a cancelled order, and a missing amount is more honest than 0.
Step 5 · Turn amounts into numbers
An amount like $1,200.50 is text. Remove the dollar sign and the thousands comma, then convert to numbers. errors="coerce" turns anything that still is not a number into NaN instead of stopping with an error:
cat >> clean.py <<'PY'
def fix_amounts(df):
text = df["amount"].str.replace(r"[$,]", "", regex=True)
return df.assign(amount=pd.to_numeric(text, errors="coerce"))
PY
uv run python - <<'PY'
import clean
df = clean.fix_amounts(clean.handle_missing(clean.tidy_text(clean.load())))
print(df[["order_id", "amount"]])
print(df.dtypes["amount"])
PYThe amounts print as plain numbers (1200.50, 89.99, 250.00, 1050.00, NaN, 310.00, 75.25) and the type is float64. Use errors="coerce" knowingly: it hides bad values by making them NaN. After a conversion, count the missing values, and compare the number to what you expect, so a typo in the source does not quietly vanish. Rewrite the pattern in str.replace for other currencies, or use a thousands="," argument in read_csv when the column is clean enough.
Step 6 · Parse the dates
Dates arrive as 2026-10-03 and as Oct 5, 2026. pd.to_datetime with format="mixed" works out each value's format on its own. Anything that is not a date becomes NaT, "not a time":
cat >> clean.py <<'PY'
def fix_dates(df):
return df.assign(order_date=pd.to_datetime(df["order_date"], format="mixed", errors="coerce"))
PY
uv run python - <<'PY'
import clean
df = clean.fix_dates(clean.fix_amounts(clean.handle_missing(clean.tidy_text(clean.load()))))
print(df[["order_id", "order_date"]])
print(df.dtypes["order_date"])
PYEvery date prints as 2026-10-05 style, and order 1007's not a date is NaT. The type is datetime64[us]: pandas 3 picks the resolution (here microseconds) from the data instead of always using nanoseconds. Be careful with dates such as 03/04/2026, which could mean 3 April or 4 March. format="mixed" guesses month first, and dayfirst=True flips it. When your file has one format, pass it explicitly (format="%d/%m/%Y"), which is faster and removes the guess.
Step 7 · Remove duplicates
Order 1002 appears twice. After the cleaning above, the two rows are identical, which is why tidying comes first: "Grace Hopper" and "Grace Hopper " would not have matched. Keep the first row for each order id:
cat >> clean.py <<'PY'
def dedupe(df):
return df.drop_duplicates(subset="order_id", keep="first")
def clean_orders(path="orders.csv"):
df = load(path)
for step in (tidy_text, handle_missing, fix_amounts, fix_dates, dedupe):
df = step(df)
return df.reset_index(drop=True)
PY
uv run python - <<'PY'
import clean
raw = clean.load()
df = clean.clean_orders()
print(f"{len(raw)} rows in, {len(df)} rows out")
print(df)
PYThe output starts with 9 rows in, 7 rows out: one duplicate and the row with no id are gone. clean_orders runs the five functions in order and renumbers the rows with reset_index(drop=True). Given the same file it always gives the same result, which is what you want from a cleaning script.
Check it worked
Every column should now have the type you expect, and the clean table should hold no surprises:
uv run python - <<'PY'
import clean
df = clean.clean_orders()
print(df.dtypes)
print("duplicate ids:", df["order_id"].duplicated().sum())
print("missing:", df.isna().sum().to_dict())
PYThe types are int64 for order_id, str for the text columns, datetime64[us] for order_date and float64 for amount. There are no duplicate ids, and the only missing values are one amount (the cancelled order) and one order_date (order 1007).
Step 8 · Summarise
Now that the data is tidy, the questions are one-liners. groupby splits the rows by a column and agg computes named results for each group. Orders per country with total and average amount, then revenue per month:
uv run python - <<'PY'
import clean
df = clean.clean_orders()
by_country = df.groupby("country").agg(
orders=("order_id", "count"),
revenue=("amount", "sum"),
average=("amount", "mean"),
).round(2)
print(by_country)
by_month = df.groupby(df["order_date"].dt.to_period("M"))["amount"].sum()
print(by_month)
PYThe per-country table is:
orders revenue average
country
Germany 2 385.25 192.62
United Kingdom 2 1450.50 725.25
United States 3 1139.99 570.00And the monthly one has 2026-10 at 1540.49 and 2026-11 at 1360.00. Two things to notice. sum and mean skip missing values, so the cancelled order adds nothing to the United States total and is left out of its average (570.00 is the mean of 89.99 and 1050.00). count on order_id still counts it as an order. And order 1007, with no date, is left out of the monthly totals because its date is NaT: its 75.25 appears in the Germany row but in no month. Seeing that difference between two summaries is exactly why you count and compare.
Step 9 · Write the clean file and keep the original
Save the result with to_csv. index=False keeps pandas from writing its row numbers as an extra column. Write to a new file, never over the source, and the original stays as evidence:
uv run python - <<'PY'
import clean
df = clean.clean_orders()
df.to_csv("orders_clean.csv", index=False)
PY
cat orders_clean.csvThe file has a header and seven rows. Amounts are plain numbers such as 1200.5, dates are 2026-10-03, and the empty cells are the two missing values. Read it back to prove it round-trips:
uv run python - <<'PY'
import pandas as pd
back = pd.read_csv("orders_clean.csv", parse_dates=["order_date"])
print(len(back), back["amount"].sum())
PYThe output is 7 2975.74. Putting the cleaning in clean.py rather than in a notebook or by hand means you can run it again when next month's export arrives.
Step 10 · Know the pandas 3 changes that matter here
Two of the pandas 3.0 changes affect how you write cleaning code:
- Copy-on-write. Every result of an indexing step or method behaves as its own copy, so changing it never changes another table. The practical effect is that chained assignment, the pattern
df["status"][mask] = "x", no longer works and pandas warns about it. Update values in one step with.loc[row_condition, column] = value. - A dedicated string type. Text columns are
str, as you saw. Missing text isNaN, and.strmethods keep working as before.
Try the wrong and the right way on a copy of your clean table, to see the difference:
uv run python - <<'PY' 2>&1 | grep -E "ChainedAssignmentError|^\[" | cut -c1-90
import clean
df = clean.clean_orders()
wrong = df.copy()
wrong["status"][wrong["country"] == "Germany"] = "checked"
print(wrong["status"].tolist())
right = df.copy()
right.loc[right["country"] == "Germany", "status"] = "checked"
print(right["status"].tolist())
PYThe first assignment prints a ChainedAssignmentError warning and leaves the list unchanged: ['shipped', 'shipped', 'shipped', 'pending', 'cancelled', 'shipped', 'pending']. The .loc version changes the two German orders: ['shipped', 'shipped', 'shipped', 'pending', 'cancelled', 'checked', 'checked']. The same advice is in the migration guide, https://pandas.pydata.org/docs/user_guide/migration.html.
Troubleshooting
KeyError: 'amount'after cleaning: a function replaced or dropped a column. Printdf.columnsbetween steps.ValueErrorfromto_datetimementioning a format: you are onformat="mixed"and a value broke its rules. Adderrors="coerce"and look at the rows that becameNaT.- Text functions fail with
Can only use .str accessor with string values: the column was already numbers or dates. Run the text function before converting types. FileNotFoundError: orders.csv: run the commands from~/csv-demo.
Step 11 · Clean up
The folder holds only what you made here:
cd ~
rm -rf ~/csv-demoNext steps
- Handle the files you read and write: read and write files and JSON in Python.
- Keep your project's packages tidy: Python virtual environments and packages.
- Test your cleaning functions: test Python code with pytest.
- Pull data from an API into a table: call a web API from Python with httpx.
Related tutorials
- How to Install Python 3.15 and Run Your First ScriptYou will install uv 0.13 from a checksum-verified release on Ubuntu 26.04, use it to install Python 3.15 next to the system Python, and write and run your first script.
- How to Use Virtual Environments and Install Packages in PythonYou will create an isolated Python 3.15 environment for a project, install a package into it with pip, record the exact versions, rebuild the environment from that record, and then manage the same project with uv.
- How to Read and Write Files and JSON in PythonYou will read and write text files with pathlib, convert Python data to JSON and back, and use both to build a small task list that keeps its data in a JSON file and survives a damaged file.
Need this done for you?
Padding does this work for growing companies, from the first diagnosis to a system that keeps working.
Get new tutorials