Skip to content
Tutorials Center
My learning

Topics

Lesson 56 of 64 ยท Projects

Project: Cleaning a Messy Survey

Clean a messy survey with pandas: fix typos in categories, parse mixed dates and money, handle impossible ages, derive columns and print a validation report.

Survey data is where tidy tutorials end and real work begins. People type "USA", "U.S.A." and "united states" into the same box. They write ages as words, dates in half a dozen formats, incomes with dollar signs and a trailing "k". Some skip questions, some submit twice.

In this project you clean a small, deliberately messy survey from start to finish. The rows are few enough to read with your own eyes, but every problem in it is one you will meet in real exports with a million rows. By the end you will have a reusable clean_survey function and a validation report that tells a reader exactly what you changed and why.

The plan

flowchart TD
    A["raw text<br/>(everything read as str)"] --> B["profile<br/>find every kind of mess"]
    B --> C["categories<br/>country, satisfaction, yes/no"]
    B --> D["numbers and dates<br/>age, income, signup"]
    C --> E["derive new columns"]
    D --> E
    E --> F["validate and report"]

Profile first, then fix categories and numbers separately, derive columns, and finish with a report.

Two principles guide every step:

  1. Read everything as text first. pd.read_csv(..., dtype="str") stops pandas from guessing, so nothing is lost or half-converted. You convert each column deliberately.
  2. Never fix silently. Every rule either maps a value to a known good one, or turns it into a missing value (NaN, NaT, <NA>) that you can count. Nothing invented, nothing deleted without a record.

Step 1: profile the mess

The raw file has twelve rows and seven columns. Before writing any cleaning rule, list what is wrong. Printing the distinct spellings of each categorical column is the fastest way:

Python Step 1: profile the raw survey

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 profile gives you a to-do list:

  • country: capitalisation, trailing spaces, dots, aliases (USA, UK) and a typo (Germny).
  • satisfaction: case differences and a typo (satisfed).
  • recommend: eight spellings for yes/no, plus maybe, which is neither.
  • age: a number written as a word, a missing value, 230 and -3.
  • signup: six date layouts and a not a date. Look at row 6, 10-02-2025: pandas' guess is October 2nd. If the survey comes from a region that writes day first, it is really 10 February. Guessing silently is how dates get corrupted, so we will state a rule instead.
  • income: dollar signs, thousands separators, a trailing k and missing values.
  • id: respondent 10 appears twice with identical answers.

Step 2: standardise the categories

For text categories the strategy is: normalise (strip, lower-case, remove noise), apply an explicit alias table for known synonyms, then snap what is left to the closest valid spelling with difflib. Snapping handles typos, but a cutoff keeps it honest: if nothing is close enough the value becomes missing instead of being forced into a wrong category.

For a yes/no question use a dictionary of every accepted spelling. Anything not in the dictionary (here, maybe) maps to a missing value. Combined with the nullable "boolean" dtype, you get True, False and <NA>.

Python Step 2: country, satisfaction and yes/no

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.

Every helper is a plain function that takes a Series and returns a Series, so each is easy to test on its own (see Testing and Validating DataFrames). The lists COUNTRIES and SATISFACTION are the single source of truth for valid answers. When a new typo appears, you fix the data in one place.

Step 3: numbers and dates

Numbers that arrive as text need a parser that is strict about what it accepts. For income the rules are: strip $, commas and spaces, then interpret a trailing k as thousands; anything still unreadable becomes NaN.

For age we convert with pd.to_numeric(errors="coerce") after replacing the one known word, then apply a plausibility range (here 16 to 100). Values outside the range are not "corrected": 230 could be 23 or 30 or 2.30 typed wrong. The honest action is to blank them and count them in the report.

For dates, the dangerous option is a blind dayfirst or format="mixed". Instead, list the exact formats you accept and try them one after another with errors="coerce", filling the gaps left by the previous attempt. Order matters only for ambiguous layouts like 10-02-2025; by listing %d-%m-%Y we declare these day-first.

Python Step 3: age, income and signup date

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 nullable Int64 dtype keeps ages as whole numbers even with gaps, instead of turning the column into floats (see Data Types and Conversion).

Step 4: derive columns, validate and report

With clean inputs, derived columns are one-liners:

  • satisfaction_score: the position of the answer in the ordered list, from 1 (very dissatisfied) to 5 (very satisfied).
  • age_group: pd.cut into bands, which keeps missing ages missing.
  • signup_month: the month of the signup date, for later counts per month.
  • promoter: satisfied or very satisfied and would recommend.

Then comes the part reviewers care about most: the report. It records, for each rule, how many rows it touched. A cleaning script that cannot say what it changed is not finished. Below, everything is combined in one clean_survey function that returns the clean table together with the report, and a last block summarises the result by country.

Python Step 4: the full cleaning function and report

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.

Read the result like a reviewer. Eleven respondents remain, with several missing answers that are real gaps (nobody can recover an age that was never given, and 230 was blanked, not fixed). Averages such as avg_satisfaction ignore missing values by default, so each country's mean is computed from the answers that exist. With only two or three people per country, though, none of these numbers would justify a conclusion; the point here is the method.

Habits worth keeping

  • Keep the raw data untouched and write the cleaned table to a new variable or file, as in the pipelines lesson.
  • Keep valid-value lists, alias tables and plausibility limits at the top of the script as constants.
  • Prefer rules that turn doubtful data into missing over rules that guess.
  • Review what fuzzy matching changed. A cutoff of 0.8 fixed Germny, but a looser one could turn Guinea into Guyana.
  • End with assertions and a report. The report is also a ready-made message to whoever owns the data source: "ages like 230 and -3 are being entered, please add validation to the form".

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why read the CSV with dtype='str' before cleaning?

    With every column as text, odd values such as 'twenty-nine' or '48k' survive until your own parser handles them, instead of making pandas guess a dtype.
  2. 2. An age of 230 appears in the data. What does the project do with it?

    The correct value is unknowable, so inventing one would add false data. Blanking and counting it is honest and reversible.
  3. 3. Why does the project list exact date formats instead of using format='mixed' with dayfirst?

    Ambiguous layouts like 10-02-2025 can be read two ways. Stating the formats makes the rule explicit and reviewable.
  4. 4. What is the purpose of difflib.get_close_matches with a cutoff in the category cleaning?

    The cutoff stops far-off values from being forced into a wrong category; those become missing and show up in the report.
  5. 5. Which dtype lets the age column keep whole numbers while still holding missing values?

    The capital-I Int64 dtype stores integers and <NA>, whereas int64 cannot represent a missing value.

Summary

  • Read messy files as text, profile every column, and write down each kind of mess before fixing it.
  • Normalise categories, apply an alias table, snap typos with difflib using a cutoff, and let the rest become missing.
  • Map yes/no spellings through a dictionary into the nullable boolean dtype.
  • Parse money with a strict function, apply plausibility ranges to ages, and parse dates with explicit formats.
  • Derive scores, bands and flags only after the inputs are clean, and check key invariants with assertions.
  • Always produce a validation report that says how many rows each rule changed or blanked.

Next: Project: Sensor and Time-Series Report