Skip to main content

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:

Bash
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:

Bash
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
CSV

Check it worked

Bash
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 ...:

Bash
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)
PY

Look at what pandas made of the file, the column types and the missing values:

Bash
uv run python - <<'PY'
import clean

df = clean.load()
print(df)
df.info()
print(df.isna().sum())
PY

The 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:

Bash
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(),
    )
PY

df.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:

Bash
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())
PY

The 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:

Bash
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"])
PY

Eight 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:

Bash
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"])
PY

The 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":

Bash
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"])
PY

Every 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:

Bash
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)
PY

The 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:

Bash
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())
PY

The 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:

Bash
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)
PY

The per-country table is:

Text
                orders  revenue  average
country                                 
Germany              2   385.25   192.62
United Kingdom       2  1450.50   725.25
United States        3  1139.99   570.00

And 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:

Bash
uv run python - <<'PY'
import clean

df = clean.clean_orders()
df.to_csv("orders_clean.csv", index=False)
PY
cat orders_clean.csv

The 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:

Bash
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())
PY

The 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 is NaN, and .str methods keep working as before.

Try the wrong and the right way on a copy of your clean table, to see the difference:

Bash
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())
PY

The 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. Print df.columns between steps.
  • ValueError from to_datetime mentioning a format: you are on format="mixed" and a value broke its rules. Add errors="coerce" and look at the rows that became NaT.
  • 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:

Bash
cd ~
rm -rf ~/csv-demo

Next steps

Need this done for you?

Padding does this work for growing companies, from the first diagnosis to a system that keeps working.

Platform Engineering

Get new tutorials

New tutorials, about twice a month.