All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

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.

Clean a messy export in a fixed order (names, types, text, dates, duplicates, missing, rules) inside one function you can rerun on next month's file.

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

Pythonorders_messy.csv

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

  1. Names first, so the rest of the code is readable.
  2. Text (strip, case), because it affects duplicates.
  3. Duplicates.
  4. Numbers (remove symbols, convert).
  5. Dates.
  6. Missing values, with a rule per column.
  7. Rules: drop or flag rows that break them.
  8. Check with assertions, and log every step.

3. One cleaning function

Pythonorders_messy.csv

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:

Names. 'Customer ID' → customer_id; 'Unit Price' → unit_price.
Types. '$12.50' → 12.5; '10%' → 0.10; three date formats → datetime.
Text. ' harbourview' → 'Harbourview': 7 stores, not 12.
Rows. 12 duplicate lines removed; 22 missing qty filled from the product's median and flagged.
Rules. asserts pass: positive qty, discounts 0 to 0.5, known stores. 400 clean rows.

4. Your turn: extend the function

Your turnorders_messy.csv
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

  1. Why use raw.copy() at the start?
  2. Your log shows the “drop missing qty” step removed 30 % of rows. What do you do?
  3. Where would you save the cleaned table, and in what format?
Answers
  1. So the function never changes the caller’s DataFrame; the raw data stays as read.
  2. Stop and investigate: that’s too many to drop silently. Find out why quantities are missing (a till fault, a store) before continuing.
  3. 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

1Why clean text before removing duplicates?
2Why wrap the steps in a function?
3What belongs in a cleaning log?

Sources

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.