Python Core Portfolio Trilogy

## The scenario You have just joined **CubedNet Health**, a fictional NHS commissioning support unit, as a junior data analyst on the Activity team. CubedNet Health commissions admitted-patient care for a regional population from three NHS trusts: a major London acute trust, a smaller South East acute trust, and a North West community trust. Every month each trust sends CubedNet Health a SUS+ activity extract — a CSV containing one row per spell (a continuous period of admitted care, from admission to discharge). Each row has the patient, the admission and discharge dates, the primary diagnosis (ICD-10), the primary procedure (OPCS-4), the length of stay, and the HRG code (Healthcare Resource Group) the trust's grouper has assigned to the spell. Your manager has asked you to build a **monthly activity reconciliation report** that looks up each spell's HRG against the NHS Payment Scheme tariff, computes the expected payment per spell using the right price (elective / non-elective / day case), aggregates spend and activity by provider, and flags any spell that fails an audit rule. ## Deliverables A Python project that runs end-to-end with `python healthpy_report.py` and produces two output CSVs in `./output/`: `provider_summary.csv` (one row per provider with total spells, total bed-days, total spend) and `flagged_spells.csv` (one row per audit-failing spell with `flag_type` ∈ {`unknown_hrg`, `backward_dates`, `missing_los`, `no_applicable_price`}). **Stdlib only** — no pandas, no numpy. Use `csv.DictReader`, `pathlib`, `datetime`, `collections`. ## Payment lookup rules 1. If `length_of_stay_days = 0` AND HRG has a `day_case_price_pence`, use that. 2. Else if admission method is `Emergency` / `Maternity` / `Transfer` AND HRG has a `non_elective_price_pence`, use that. 3. Else if HRG has an `elective_price_pence`, use that. 4. Else flag the spell as `no_applicable_price`. ## Acceptance criteria (summary) Runs in one command · stdlib only · both output CSVs correct · bad rows logged not fatal · HRG codes cleaned before lookup · ≥3 named functions with docstrings · PEP 8 compliant · ≥8 conventional commits. Full brief and dataset orientation appear inside the lesson once enrolled.

## The scenario You have joined **CubedNet Insurance**, a fictional UK motor underwriter, as a junior data analyst on the Claims team. About half of CubedNet's policies are sourced through aggregators (Compare the Market, Confused.com style), a quarter direct, the rest through brokers. Every month, a CSV of **First Notification of Loss (FNOL) events** lands on your desk — each FNOL is one report from a policyholder: a collision, a stolen car, a cracked windscreen, vandalism, flood. Your manager has asked you to build a monthly claims reconciliation report that joins each FNOL to its policy (which carries the cover type and excess), looks up the appropriate payment rule from `cover_rules.csv`, computes the payable amount (`estimate × multiplier − excess`), aggregates by cover type, and flags audit-failing FNOLs. ## Deliverables A Python project that runs end-to-end with `python insurancepy_report.py` and produces two output CSVs: `cover_summary.csv` (one row per cover type with FNOL counts and total payable) and `flagged_fnols.csv` (one row per audit-failing FNOL with `flag_type` ∈ {`uncategorised_incident`, `backward_dates`, `missing_estimate`, `unknown_policy`}). **Stdlib only.** No pandas, no numpy. ## Cover-rule lookup Build a `(cover_type, incident_type) → rule` dict ONCE outside the FNOL loop. Linear scanning `cover_rules.csv` for every FNOL is a fail. If `is_payable = N`: payable amount is `0`. Still COUNT the FNOL — non-payable is not an error. Otherwise: `payable = estimate × payment_multiplier`. If `excess_applies = Y`, subtract policy's `excess_pence` (floor at 0). ## Acceptance criteria (summary) Runs in one command · stdlib only · both output CSVs correct · bad rows logged · incident types cleaned before lookup · cover-rule lookup is a real dict (no linear scan) · ≥3 named functions with docstrings · PEP 8 compliant · ≥8 conventional commits. Full brief and dataset orientation appear inside the lesson once enrolled.

## The scenario You have joined **CubedNet Logistics**, a fictional UK parcel carrier, as a junior data analyst on the Network Performance team. CubedNet Logistics dispatches consignments from eight hub depots and delivers them via van-shift routes to UK addresses within a published service-level promise. Every Monday, a CSV of the prior week's delivery attempts lands on your desk. Each attempt is one row: which consignment was attempted, which route delivered it, the timestamp, the attempt number (1/2/3), the outcome code, time-of-day, and whether a signature was obtained. Your manager has asked you to build a **weekly SLA reconciliation report** that joins each delivery attempt to its consignment, derives an outcome category for each consignment, looks up the SLA rule per service × outcome combination, aggregates breach counts by service level, and computes customer compensation owed. ## Outcome categories For each consignment, derive one of: `SUCCESS_FIRST` (DELIVERED on attempt 1, within breach window), `SUCCESS_LATER` (DELIVERED on attempt 2 or 3, within window), `BREACH_LATE` (DELIVERED but past window), or `BREACH_FAILED` (no successful delivery). **Datetime arithmetic** matters here — `cutoff = promised_delivery_datetime + breach_window_minutes`. Use `datetime.timedelta`. ## Deliverables A Python project that runs end-to-end with `python logisticspy_report.py` and produces two output CSVs: `sla_summary.csv` (per service-level breakdown of category counts + total compensation + sla_met_pct) and `flagged_attempts.csv` (per audit-failing attempt with `flag_type` ∈ {`uncategorised_outcome`, `missing_datetime`, `bad_datetime`, `unknown_consignment`, `unknown_route`}). **Stdlib only.** No pandas, no numpy. ## Acceptance criteria (summary) Runs in one command · stdlib only · both output CSVs correct · bad rows logged · outcome codes cleaned before lookup · `(service_level, outcome_category) → rule` composite-key dict built once · ≥3 named functions with docstrings · PEP 8 compliant · ≥8 conventional commits. Full brief and dataset orientation appear inside the lesson once enrolled.