Real columns arrive with inconsistent labels, stray whitespace, mixed date formats, and numbers stored as text. Below is a survey export with all four problems. You will clean it yourself, one step at a time, and watch every count and percentage respond.
Cleaning is the job, not the chore before the job
Practitioners routinely spend more time preparing data than modeling it, and entire courses exist just for this craft. The reason is simple: every join, filter, groupby, and chart trusts the values underneath it. A country column where UK, U.K., and uk are three different strings will quietly produce three different countries in every result downstream. What counts as good data has its own vocabulary, covered in the six faces of bad data. This guide is the hands-on half: you fix a real export.
How this playground stays honest: the 24-row export below is a fixed dataset shipped verbatim with the page. Every unique-value count, parse rate, and bar you see is computed live from those rows by the same cleaning functions your toggles switch on and off. Nothing is faked.
A product team merged survey responses from three source systems into one CSV. Scroll the table: the same three countries hide behind 9 different spellings, the dates speak four dialects, and the hours column is text pretending to be numbers. As you enable steps in section 2, this table updates in place; changed cells show their original value struck through.
survey_export.csv: 24 rows, exactly as they arrived
| # | country | signup_date | hours_per_week | row status |
|---|---|---|---|---|
| 1 | UK | 2026-01-15 | 12 | clean ✓ |
| 2 | uk | 03/15/2026 | 8 | needs work |
| 3 | ␣UK␣ | 2026-02-28 | ␣8␣ | needs work |
| 4 | United Kingdom | Mar 5, 2026 | 30 | needs work |
| 5 | Germany | 2025-11-02 | 5 | clean ✓ |
| 6 | Norway | 1/8/2026 | 7,5 | needs work |
| 7 | UK | 2025-12-09 | 14 | clean ✓ |
| 8 | U.K. | January 12, 2026 | 21 | needs work |
| 9 | Germany | 2026-03-01 | 10 | clean ✓ |
| 10 | Deutschland | 11/30/2025 | 2 | needs work |
| 11 | Germany | 2026.03.22 | 16 | needs work |
| 12 | Norway | 2025-10-21 | 12h | needs work |
| 13 | UK | 4 Feb 2026 | 7 | needs work |
| 14 | ␣UK␣ | 2026-04-11 | 25 | needs work |
| 15 | United Kingdom | ␣2026-02-14␣ | 9 | needs work |
| 16 | Germany | 07/04/2025 | 15␣ | needs work |
| 17 | Norway | 2026-01-30 | 11 | clean ✓ |
| 18 | norway␣ | 2025.08.07 | ten | needs work |
| 19 | UK | 19 Dec 2025 | N/A | needs work |
| 20 | uk | 2025-09-18 | 20 hrs | needs work |
| 21 | U.K. | 12/05/2025 | N/A | needs work |
| 22 | Germany | last spring | 18 | needs work |
| 23 | Deutschland | n/a | 12,5 | needs work |
| 24 | norway␣ | 2026-13-40 | - | needs work |
The ␣ mark makes leading and trailing spaces visible; they are real characters in the data. A row counts as clean when its country is one of the three canonical labels, its date parses, and its hours value is a real number: right now that is 5 of 24 rows, before any cleaning.
Apply all five steps to earn the guide. Each card shows, live, what the step changes given everything else you have already applied. The scoreboard and the category chart never lie: they are recomputed from the table on every toggle.
Distinct country values
9
goal: 3 real countries
Dates parsed
37.5%
9 of 24; ceiling 21: 3 rows are junk
Hours numeric
58.3%
14 of 24; ceiling 20: 4 stay missing or junk
Rows fully clean
20.8%
5 of 24 rows pass all three columns
The cleaning pipeline
Toggle steps in any order you like; the pipeline always executes top to bottom. Watch what each one does to the table and the scoreboard, including what happens when you map synonyms before trimming.
What the country column believes right now
9 distinct values
The raw export claims 9 categories. Only 3 countries actually answered this survey. Every bar is a live count over the 24 rows.
Colored bars marked ✓ are canonical labels (UK, Germany, Norway); gray bars are impostors created by whitespace, capitalization, or synonyms. Any chart, filter, or groupby built on this column right now would treat each bar as its own country.
Three habits this table just taught you
Order matters
Try mapping with everything else off: the exact-match dictionary hits only 2 of 24 rows. After trim and casefold it hits 24 of 24. Normalize first, then map; a cleaning pipeline is a sequence, not a bag of tricks.
Formats are decisions
Row 6 says 1/8/2026. The parser here reads it as January 8 because it assumes month-first slash dates; a day-first reader would see August 1. No code can tell you which is true. Flexible parsing embeds assumptions, so document them where the next analyst will look.
Missing is not dirty
N/A and a lone dash are not parse failures; they are answers that do not exist. Coercing them to 0 would invent data. What to do with them instead is its own craft: missing data: why it matters.