Week 02: Exercises & Checklist

Graded exercises

1. Easy: Run notebooks/01-pandas-cleaning.ipynb to completion. It prints, at the end, the cleaned row count and the number of duplicate rows removed. Record both numbers in the Week 2 tracker sheet.

2. Standard: Profile the raw dataset and document five planted issues: (1) duplicate rows, (2) NaN weights, (3) NaN lane distances, and two more you find yourself (look at delay_hours for impossible values, weight_kg for non-positive values, or status for rows whose actual_arrival contradicts the label). For each, write the finding, the evidence (a count or a sample row), and the fix in your profile report.

3. Stretch: In notebooks/02-sql-with-duckdb.ipynb, rewrite the "on-time rate by carrier/lane/month" query using a window function (e.g. AVG(...) OVER (PARTITION BY carrier_id, lane_id, month ORDER BY month)) instead of a plain GROUP BY, and add a cell asserting the two approaches agree within floating-point tolerance.

4. Portfolio: Advance the ZoroLogistics data generator + silver dataset milestone (see curriculum/projects/README.md): turn the cleaning workflow into a runnable script data/make_silver.py that regenerates (or loads) the raw data, cleans it, runs the validation suite, and writes data/silver/. One command, one reproducible silver table.

Hints

1. Easy: The final cell prints two numbers: CHECKS_PASSED and SILVER_ROW_COUNT. Record both; if CHECKS_PASSED is not 12, scroll up to the first FAIL line and fix that decision before re-running.
2. Standard: For the two unplanted issues, profile delay_hours (look at its describe() tail for extreme values) and check the relationship between planned_arrival and planned_departure, a row where arrival precedes departure is a structural defect.
3. Stretch: A window function keeps every row, so wrap the per-carrier monthly rate in a CTE and add AVG(...) OVER (PARTITION BY carrier_id); then assert the two approaches agree with np.allclose.
4. Portfolio: Structure make_silver.py as three functions, load_or_generate(), clean(df), validate(df) -> bool, so the script reads as the same raw → clean → validate → save flow the notebook does, but in one command.

Checklist (mirrors manifest.json + Excel tracker)

• [ ] Mon: Study data quality concepts; run the pandas refresher exercises.
• [ ] Tue: Run the pandas cleaning notebook; find and document 5 data issues in the generator output.
• [ ] Wed: Write SQL queries in DuckDB: on-time rate by carrier/lane/month, windows, top-lane analysis.
• [ ] Thu: Add a data validation suite (pandera) with at least 10 checks.
• [ ] Fri: Use case: deliver the cleaned silver dataset + profile report; document every cleaning decision.
• [ ] Sat: Take the Week 2 quiz (quiz.md), pass with 8/10; record the score in Notes.
• [ ] Milestone: Update the Excel tracker; commit the silver pipeline script.