Week 2 · Clean and trust the data › Day 10

Day 10. The messy-data field guide

⏱ 4h · learn 75m · lab 125m · ship 25m · review 15m🏷 QSR, retail, marketingBefore: Day 9

By the end of today you can

Fix day-first dates, time zones and business days that cross midnight
Repair encodings and read awkward Excel files
Normalise money (micros, currencies, refunds, tax)
Strip footers, totals and blank rows safely

1. Why this matters

Every sector has the same dozen traps. Recognising them on sight is a large part of what "experienced" means. Today is a catalogue with a fix for each, all of them present in the course data.

2. Dates and time

Day-first vs month-first. 04/09/2026 is 4 Sept in Canada-UK style and 9 April in US style. Never let a parser guess. Specify the format per source and test with a date where day > 12.

pl.col("sold_at").str.strptime(pl.Datetime, "%d/%m/%Y %H:%M")      # retail emails: day first

Time zones. Store timestamps in UTC with the zone recorded, or as local time with an explicit zone column. Ontario switches between EST (UTC−5) and EDT (UTC−4); one night a year has 23 hours, one has 25.

Business day. Restaurants and hotels close after midnight. The QSR data's business day runs 05:00 to 04:59. An order at 01:30 on the 15th belongs to the 14th.

.with_columns(business_date=(pl.col("order_ts") - pl.duration(hours=5)).dt.date())

Maths and principle

Principle: a business day is the calendar day of a shifted clock.

Equation: $\text{bdate}(t) = \lfloor (t - c) \rfloor_{\text{day}}$, where $c$ is the cut-off (5 h for this QSR chain; often 3–6 h in hospitality, set by the night audit).

Analogy: a phase shift. The function is still periodic with a 24 h period; only the origin moves. Where it breaks: DST nights. A 23-hour business day has fewer hours of sales, so per-hour comparisons need the real length.

Try it: order at 2026-09-15 04:20 with $c$ = 5 h. Business date? (2026-09-14.) At 05:01? (2026-09-15.)

3. Text and encoding

Windows-1252 vs UTF-8. Jalapeño written in cp1252 and read as UTF-8 becomes Jalape�o. Detect per file (charset-normalizer), or know it per source, and decode explicitly. The course TacoTrail files are cp1252.
Byte-order marks ( at the start of the first column name). Use encoding="utf-8-sig".
Whitespace and case in keys: " r104", "R104 ". Trim and upper-case codes in staging, once.
Numbers as text: "1,234.50", "(12.00)" for negatives, "$" signs, "-" for zero.

4. Excel

Excel is a presentation format pretending to be a data format. Watch for: header rows not on row 1, merged cells, multiple sheets, hidden rows, dates stored as serial numbers (days since 1899-12-30), and formulas whose cached value is stale.

import polars as pl
df = pl.read_excel("sales_R104_20260914.xlsx", sheet_name="Sales", engine="calamine")  # fast Rust reader

5. Money

Trap	Example in course data	Fix
Micros	`spend_micros = 152340000`	divide by 1,000,000 in staging, name it `spend`
Mixed currency	some ad rows `currency: USD`	convert with a dated FX table, keep both original and reporting currency
Refunds as negatives	retail `qty < 0` in some files	split into `sales` and `refunds`; never let them silently net
Tax in or out	unknown until you ask	contract comment; one rule per source
Rounding	line totals vs receipt totals differ by cents	round at the grain the source rounds at

6. Footers and junk rows

CSV exports from POS and accounting systems often end with blank rows and a TOTAL row. Drop rows where the key is null, but first check the total row against your sum. It's a free reconciliation the source gave you.

The 5-year lens

Write every fix as a named, tested function in one module (clean/rules.py) with a docstring that says which source and why: def qsr_business_date(ts): """POS day ends 04:59 (confirmed with ops, 2026-09-02).""". In a year, the question won't be "how", it will be "why is this here and can I remove it?". The docstring is the answer. Every rule also gets a test with the nasty example.

Lab

Create clean/rules.py with parse_day_first, business_date(ts, cutoff_hours), decode_bytes(b, source), parse_money_text, micros_to_units, drop_footer_rows (returns the dropped rows too).
Write a test for each with the nasty case: 13/09 vs 09/13, 04:59 vs 05:00, cp1252 ñ, "(12.00)", a TOTAL row.
Apply them to the QSR staging from Day 9: add business_date, fix TacoTrail encoding (read bytes, decode cp1252), drop 00/00/0000 rows into a rejects frame.
Apply to the retail email attachments: day-first dates, xlsx reading, refunds split out, footer rows checked against their totals where present.
Compare revenue by calendar date vs business date for one restaurant. How much moves? Put the chart in docs/.

Ship

feat(clean): tested cleaning rules for dates, encodings, money and footers.