Module 3: Cleaning data · Lesson 3.7 · about 30 min
Cleaning a messy export, start to finish
Put Module 3 together: turn a messy 412-row till export into a clean, checked table with one function, in the order that works: names, duplicates, text, numbers, dates, missing values, rules, then a cleaning log.
Module 3 · about 30 minutes · uses all of Module 3. You’ll turn a messy export into a clean, validated table with a repeatable function and a log of what changed.
In everyday terms: a car wash runs in a set order: rinse, soap, scrub, rinse, dry. Drying first would be pointless. Cleaning data has the same kind of order.
1. The file
Problems spotted: awkward column names, exact duplicate rows, store names with spaces and mixed capitals, prices with $, discounts as 10% text, three date formats, missing quantities, discounts and customers, and a few negative quantities.
2. The order that works
- Names first, so the rest of the code is readable.
- Text (strip, case), because it affects duplicates.
- Duplicates.
- Numbers (remove symbols, convert).
- Dates.
- Missing values, with a rule per column.
- Rules: drop or flag rows that break them.
- Check with assertions, and log every step.
3. One cleaning function
Every step is in the function, so next month’s export is one line: clean, log = clean_orders(pd.read_csv('next_month.csv')).
- Column names
- Fix first, so every later step can use simple names.
- Types
- Next: numbers, dates and text in the right types.
- Values
- Then text variants, duplicates and gaps.
- Rules
- Last: asserts that the result is fit to use.
- Physical meaning
- Each step relies on the one before: you cannot drop duplicate orders before order IDs are the same type, or fill gaps per store before store names are consistent.
“Clean it once by hand and save the file.” Next month the same mess comes back. A cleaning function turns an afternoon into one line and makes the cleaning itself reviewable.
Watch it happen, one step at a time:
4. Your turn: extend the function
How it works
Principle: cleaning is part of the analysis, so it should be code, not manual edits. A function makes it repeatable; assertions make it safe; a log makes it honest.
Keep the raw file untouched. Read it, clean a copy, and save the result under a new name (orders_clean.csv). If a rule turns out wrong, change the function and rerun from the raw file.
Returns are a decision: here negative quantities are kept and flagged (is_return), so net sales stay right. Another analysis might separate them. Say which you did.
5. Practice
- Why use
raw.copy()at the start? - Your log shows the “drop missing qty” step removed 30 % of rows. What do you do?
- Where would you save the cleaned table, and in what format?
Answers
- So the function never changes the caller’s DataFrame; the raw data stays as read.
- Stop and investigate: that’s too many to drop silently. Find out why quantities are missing (a till fault, a store) before continuing.
- A new file, such as
orders_clean.csv(or Parquet for big data), next to the raw file, never over it.
New words: cleaning function, cleaning log, pipeline, raw vs clean data.
- Clean in order: names, types, text, dates, duplicates, missing, rules.
- Wrap the steps in one function and rerun it on each new file.
- Log what each step changed (rows dropped, values filled).
- Finish with asserts so a bad file stops the pipeline.
Check yourself
Sources
- Wickham, H. (2014), Tidy Data, Journal of Statistical Software 59(10) https://doi.org/10.18637/jss.v059.i10
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.