Skip to content
Tutorials Center
My learning

Topics

Lesson 44 of 64 ยท Statistics and Analysis

Data Cleaning in Practice

Clean messy data with pandas: fix headers, whitespace, inconsistent categories, mixed types, bad dates and units, then chain the steps into a pipe() pipeline.

Real data is messy. Column names carry stray spaces and units, the same plan is spelled Pro, pro and PRO , prices arrive as "$29.00", a date column contains the words "not available", and weights are in pounds in one row and kilograms in the next. None of this is exotic; it is the normal state of a CSV exported by a person or a legacy system.

Data cleaning is the work of turning such a table into one you can trust. Good cleaning has three properties: it is explicit (every change is a line of code you can read), repeatable (rerunning it on next month's file gives the same treatment) and checked (assertions fail loudly when something unexpected appears). This lesson walks through the typical problems one by one and then assembles them into a pipe() pipeline. It assumes you have already explored the data, as in Exploratory Data Analysis Workflow, and uses the text tools from Working with Text.

flowchart LR
    R["raw CSV"] --> H["tidy_columns"]
    H --> T["clean_text"]
    T --> Y["fix_types"]
    Y --> D["drop_bad_rows"]
    D --> C["check (assertions)"]
    C --> OK["clean DataFrame"]
    C -. "fails" .-> E["AssertionError: fix the data or the rules"]

A cleaning pipeline: small functions that each take a DataFrame and return one, ending with checks that stop bad data from passing.

Messy headers and text values

Start with the headers, because every later line uses them. The file below has spaces around names, mixed capitals, units in parentheses and a $ sign. We normalize to snake_case: lowercase, no units, words joined by underscores.

Python Headers, whitespace and categories

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same three moves clean almost any text column: strip() removes leading and trailing whitespace, replace(r"\s+", " ") collapses runs of spaces, and a case method (lower(), title()) makes spellings comparable. After that, value_counts() should show no more near-duplicates: pro appears three times instead of being split over Pro, pro and PRO .

Some categories need more than formatting, for example "U.S.", "USA" and "United States". Handle those with an explicit mapping dictionary and replace() or map(), and then check with value_counts() that nothing slipped through. See apply, map and replace for the details.

Prefer an explicit "allowed values" list to guessing. After standardizing, df["plan"].isin(["basic", "pro", "premium"]) tells you exactly which rows still do not fit, and it becomes one of your assertions later.

Mixed types: numbers hidden in text

When a numeric column contains "$29.00" next to 29, pandas reads the whole column as text. pd.to_numeric() converts it, and its errors argument decides what happens with values it cannot convert: "raise" (the default) stops, "coerce" turns them into NaN. Strip the symbols first.

Python Numbers, dates and units

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Three habits protect you here:

  • Coerce, then count. errors="coerce" hides problems unless you look. Compare the missing values before and after (or select the original strings of the rows that became NaT, as above), so you know which values failed and can fix them or report them.
  • Never rely on guessing for dates. "05/02/2026" is the fifth of February in much of the world and the second of May in the United States. Passing format= makes your intent visible, and the second line above shows how the wrong guess either shifts a date or produces NaT.
  • Make units explicit. Convert everything to one unit in one place (here kilograms), keep the conversion factor as a named constant, and document it. where(condition, other) keeps values where the condition is true and takes other elsewhere, which turns an if/else into one line.

Other common repairs, briefly: replace placeholder codes (-999, "N/A", "?") with real missing values using na_values= in read_csv or replace(..., np.nan); split a column that holds two facts with str.split(expand=True); convert low-cardinality text to category; and deal with duplicates and outliers using the techniques from Duplicates and Outliers.

A reusable cleaning pipeline

When the cleaning steps live in a notebook cell by cell, they are hard to rerun and easy to apply out of order. Instead, write each step as a small function that takes a DataFrame and returns a new one, and chain them with DataFrame.pipe(). Each function does one job, can be tested alone and is named so the chain reads like a description of the process.

Python A cleaning pipeline with pipe()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Study the design, because it is the real lesson:

  • Each step copies (df.copy() or assign) and returns a new frame, so the raw data stays untouched and you can inspect any intermediate result by running the chain up to that step. With Copy-on-Write in pandas 3 the copies are cheap.
  • Order matters. Headers are normalized first so later steps can use the clean names; types are fixed before range checks.
  • Impossible values become missing, not invented. The weight of -5 turned into NaN rather than being "corrected" to some guess. Missing is honest; a made-up number is a hidden error.
  • check() encodes your expectations as assertions: unique names, allowed plans, positive fees. When next month's file breaks a rule, the pipeline stops with a readable message instead of producing a quietly wrong report. Assertions are a first step toward testing and validating DataFrames.
  • Report what changed: row counts before and after, and counts of values turned into missing. A cleaning step that silently removes 30% of the rows is a bug.

For arguments that vary, use .pipe(func, arg) or write a small factory function; for the complete production picture, including logging and configuration, see Building Data Pipelines.

Cleaning is not neutral. Every decision (dropping a duplicate, treating -5 as missing, picking day-first dates) changes the answers you will get. Keep the rules in code, keep the original file, and write down the decisions so that someone else can disagree with them.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A price column contains the strings "$29.00" and "9.50". Which approach converts it to numbers?

    Remove the currency symbol with str.replace and then use to_numeric. errors='coerce' turns anything still unreadable into NaN so you can count and inspect it.
  2. 2. Why pass format='%d/%m/%Y' to pd.to_datetime for dates like 05/02/2026?

    05/02/2026 is 5 February or 2 May depending on the convention. An explicit format removes the guess and makes bad values fail visibly.
  3. 3. What does errors='coerce' do in pd.to_numeric?

    Coerce makes unreadable values missing. Because that can hide problems, compare missing counts before and after.
  4. 4. Why write each cleaning step as a function that returns a new DataFrame and chain them with pipe()?

    Small named functions can be tested separately, reordered consciously and rerun on next month's data. The raw DataFrame stays untouched.
  5. 5. A cleaned table must never contain a non-positive fee. Where does this rule belong?

    An assert (or a validation function) makes the rule executable, so a violation stops the pipeline with a clear message.

Summary

  • Normalize headers first (strip, lower, regex replace), then text values (strip, collapse spaces, case) and check with value_counts().
  • Use pd.to_numeric and pd.to_datetime with explicit formats and errors="coerce", and always inspect what failed.
  • Convert units in one place and make them explicit; turn impossible values into missing instead of guessing.
  • Structure cleaning as small functions that take and return a DataFrame, chained with pipe().
  • End with assertions that encode your expectations, and report row counts and missing counts.
  • Cleaning involves decisions: keep the raw data and document the rules.

Next: Plotting with pandas shows how to turn cleaned data into charts.