Cleaning Messy Data

What you'll build

A cleaning pipeline for a customer export with every common problem: inconsistent capitalisation and spaces, duplicate rows, missing values, numbers stored as text with currency symbols, and invalid dates.

The idea

• Missing values (NaN, NaT, None): find with isna(), fill with fillna(), or drop with dropna(); decide per column.
• Text cleanup with the .str accessor: strip(), lower(), replace().
• Types: pd.to_numeric(..., errors="coerce") and pd.to_datetime(..., errors="coerce") turn bad values into missing values instead of crashing.
• Duplicates: duplicated() and drop_duplicates(subset=...).

The code

from io import StringIO
import pandas as pd

raw = pd.read_csv(StringIO('''email,country,spent,signup
 [email protected] ,de,€120,2026-01-03
[email protected],US,€45.5,2026-01-05
[email protected],DE,€120,2026-01-03
[email protected],,unknown,2026-02-30
[email protected],fr,€80,
'''))
print("missing per column:", raw.isna().sum().to_dict())

clean = (
    raw.assign(
        email=raw["email"].str.strip().str.lower(),
        country=raw["country"].str.upper().fillna("UNKNOWN"),
        spent=pd.to_numeric(raw["spent"].str.replace("€", ""), errors="coerce"),
        signup=pd.to_datetime(raw["signup"], errors="coerce"),
    )
    .drop_duplicates(subset="email")
    .reset_index(drop=True)
)
print(clean)
print(clean.dtypes)

missing per column: {'email': 0, 'country': 1, 'spent': 0, 'signup': 1}
              email  country  spent     signup
0   [email protected]       DE  120.0 2026-01-03
1   [email protected]       US   45.5 2026-01-05
2  [email protected]  UNKNOWN    NaN        NaT
3  [email protected]       FR   80.0        NaT
email                 str
country               str
spent             float64
signup     datetime64[us]
dtype: object

Common mistakes

• dropna() on the whole table, silently deleting rows that are only missing an unimportant column.
• Filling missing numbers with 0 when "unknown" isn't the same as "zero"; it skews averages.
• Removing duplicates before normalising text, so [email protected] and [email protected] both survive.
• Using errors="ignore", which leaves the column as text; use "coerce" and inspect the resulting NaNs.

Key points

• Count missing values per column and decide how to handle each.
• Normalise text before deduplicating.
• Convert types with errors="coerce" and check what became missing.

Further reading: Working with missing data · Working with text data