A Beginner's Guide to Data Cleaning with Python and pandas

September 25, 2026

Every analyst has had the same conversation. A file lands in your inbox, you load it into pandas, run a groupby on region, and the totals are wrong. Not wrong by a little — wrong by enough to be awkward in a meeting. Most of the time the culprit isn't the analysis. It's what sits underneath: blank cells, duplicated rows, a postcode with a stray space, one invoice entered as 1,25000.

Cleaning is the least glamorous part of analytics and the part that decides whether anyone trusts your numbers. pandas gives you a small set of tools that cover most of what you'll meet in a typical UK dataset. Here's how to use them without over-engineering the job.

Look before you clean

Resist the urge to start deleting rows. Spend five minutes finding out what you actually have.

df.shape tells you how many rows and columns are in play.
df.info() shows data types and where nulls sit. A column of numbers stored as object is a warning sign.
df.isna().sum() gives a missing-value count per column.
df.describe() reveals ranges — a minimum age of -3, or a maximum order value that is suspiciously round.
df['column'].value_counts(dropna=False) exposes the messy categories hiding in plain sight: "London", "london ", "LONDON".

Keep your raw file untouched. Read it into one variable, copy it into another, and clean the copy. If something goes wrong you can start again in seconds instead of re-downloading the CSV. And set na_values when you read the file: pandas treats blank cells and "NA" as missing by default, but not "-", "N/A" or "Not known", which are common in exported spreadsheets.

Handling missing values

There is no single correct answer here, only a correct answer for your question. Start by asking why the value is missing. A blank delivery date might mean the order hasn't shipped; a blank date of birth might mean the customer declined to give one. Those are different problems wearing the same costume.

Deciding what to drop

If a column is missing in more than half its rows, question whether it belongs in the analysis at all. Dropping a row with df.dropna(subset=['postcode']) is reasonable when a postcode is essential to the question. Dropping rows across the whole frame with plain df.dropna() is usually too aggressive — you can lose a third of your data because one minor field is patchy.

Filling values sensibly

For numeric columns, the median is a safer default than the mean because it shrugs off extreme values. Use the mode for categories, and a constant like "Unknown" when missingness itself is informative. For time series, sort by date first and then use df['value'].ffill() to carry the last known reading forward — appropriate for something like a monthly meter reading, misleading for a one-off transaction amount.

Whatever you choose, record it. A one-line comment or a note in your notebook explaining that you filled 40 missing weights with the median will save you an hour of confusion in three months' time.

Removing duplicates

Check first: df.duplicated().sum() gives you a count. Full-row duplicates are easy — df.drop_duplicates() handles them, and keep='first' or 'last' lets you decide which survives.

Near-duplicates are harder. Two rows for the same customer might differ only by a trailing space or a change of case, so normalise text before comparing:

df['name'] = df['name'].str.strip().str.lower()

Be careful with subset. Dropping duplicates on columns like amount and date will also delete legitimate repeat transactions — two customers really can spend £25 on the same day. Always include a genuine identifier when you have one, and if you don't, treat the duplicate flag as something to investigate rather than something to delete.

Spotting outliers

An outlier is not automatically an error. A council contract worth six figures in a dataset of household bills is unusual but real. What you're looking for is a value that couldn't plausibly exist.

A quick way in is the interquartile range. Work out the 25th and 75th percentiles with df['amount'].quantile([0.25, 0.75]), take the difference, and flag anything beyond 1.5 times that range below the lower quartile or above the upper one. Then look at the flagged rows before deciding anything.

Common causes worth checking:

Unit mix-ups — pence entered as pounds, kilograms recorded as grams.
Extra or missing digits from manual keying.
Test rows that leaked out of a staging system, often with names like "test" or "delete me".
Genuine extremes that should be kept but handled with a log transform or df['amount'].clip(lower, upper).

Clipping changes your data, so use it as a deliberate decision rather than a reflex, and note the thresholds you picked.

UK-specific tidying

British data brings its own quirks. Dates are the big one: 03/04/2024 means the third of April here and the fourth of March in a US export. Pass dayfirst=True to pd.to_datetime, or better, specify the exact format so ambiguous values raise an error rather than quietly becoming wrong.

Postcodes and place names often arrive with inconsistent spacing and capitalisation — strip them, upper-case the postcodes, and check that the outward code matches a known area. Currency columns frequently contain pound signs and thousands separators, so remove them before converting: df['amount'].str.replace(r'[£,]', '', regex=True), then pd.to_numeric(..., errors='coerce').

That errors='coerce' argument is a small habit with a big payoff. Instead of crashing on a bad value, it turns it into a missing one, which you can then count and inspect. If ten values fail to convert, you want to see them, not lose them.

Make it a routine, not a rescue mission

Write your cleaning steps as a short, repeatable sequence — read, audit, fix types, handle nulls, remove duplicates, review outliers, save — and run the whole thing from the raw file each time. Next month's extract will have the same problems, and re-running a script is faster and far less error-prone than repeating the clicks.

Before you finish, save the cleaned output with a dated filename, keep the original alongside it, and add a couple of assertions: row counts within an expected range, no negative values in a price column, no dates in the future. If a check fails, you'll know immediately rather than during a presentation. And remember that clean data is a judgement call, not a fact — the aim is a dataset you can defend, not a perfect one.

Photo: Mylene2401 / Pixabay