AI Skill Report Card

Generating Contract Performance Report Cards

A-83·Nov 16, 2026·Source: Web
14 / 15

Given a monthly transportation data export (CSV/Excel with columns including Location Name, OTP, and a trip-status/type column), generate:

  1. One system rollup report per health system (Bon Secours, HCA) aggregating all mapped facilities
  2. One individual facility PDF report per facility within each system
Python
import pandas as pd df = pd.read_excel("monthly_transport_data.xlsx") FACILITY_MAP = { "Bon Secours": [ "Bon Secours St Mary's Hospital", "Memorial Regional Medical Center", "St Francis Hospital", "Chester ER", "Short Pump ER", "Ashland ER", "Richmond Community Hospital" ], "HCA": [ "Reston Hospital Center", "StoneSprings Hospital Center", "Tysons Corner ER", "Leesburg ER", "Spotsylvania Regional Medical Center", "Fredericksburg ER", "Henrico Doctors Hospital", "Parham Doctors Hospital", "Hanover ER", "Retreat Doctors Hospital", "Scotts Walk ER", "Johnston Willis Hospital", "Chippenham Hospital", "Swift Creek ER", "Chesterfield ER", "TriCities Medical Center", "Prince George ER", "LewisGale Medical Center", "Cave Spring ER", "Blue Hills ER", "LewisGale Hospital Montgomery", "Christiansburg ER", "LewisGale Hospital Alleghany", "LewisGale Hospital Pulaski" ] } EXCLUDE_STATUSES = ["Dry Run", "Cancel", "Assist"]
Recommendation▾
Add an actual PDF-generation code snippet (e.g., using reportlab/weasyprint) rather than only describing the PDF structure prose-wise, since that's the final deliverable.
14 / 15

Progress:

  • Step 1: Load and validate raw data
  • Step 2: Filter to completed transports only
  • Step 3: Compute facility-level metrics
  • Step 4: Build system rollups
  • Step 5: Generate PDFs (rollup + individual facility)
  • Step 6: QA check totals before distribution

Step 1: Load and validate data

  • Confirm required columns exist: Location Name, OTP, and the trip status/type column (e.g., Call Type, Trip Status — identify whatever column flags Dry Run/Cancel/Assist in the actual export).
  • Normalize Location Name values (trim whitespace, fix casing) before matching to FACILITY_MAP to avoid silent mismatches.
  • Flag any Location Name values present in the data that are not in either facility map — these need review (new facility? naming variant?) before finalizing reports.

Step 2: Filter to completed transports

Python
df_completed = df[~df["Trip Status"].isin(EXCLUDE_STATUSES)]

Only completed transports count toward Total Trips. Dry Runs, Cancels, and Assists are excluded entirely from volume and OTP calculations.

Step 3: Compute facility-level metrics

For each facility:

  • Total Trips = count of completed-transport rows where Location Name matches the facility
  • Late Trips = count where OTP == "Late" (case-insensitive match)
  • On-Time Trips = Total Trips − Late Trips (blank/null OTP = on time)
  • OTP % = On-Time Trips / Total Trips
Python
def facility_metrics(facility_df): total = len(facility_df) late = facility_df["OTP"].astype(str).str.strip().str.lower().eq("late").sum() on_time = total - late otp_pct = on_time / total if total > 0 else None return {"Total Trips": total, "Late Trips": late, "On-Time Trips": on_time, "OTP %": otp_pct}

Step 4: Build system rollups

For each health system, aggregate across all its mapped facilities present in the data:

  • Total Contract Volume = sum of Total Trips across all facilities in the system
  • System OTP % = total on-time trips / total contract volume (weighted average, not average of percentages)
  • Include a facility-by-facility breakdown table within the rollup report for context

Step 5: Generate PDFs

Produce two report tiers per system:

  1. System Rollup Report ({System}_Contract_Report_{MonthYear}.pdf)
    • Header: System name, reporting month, total contract volume, system-wide OTP %
    • Table: each facility with Total Trips, On-Time Trips, Late Trips, OTP %
  2. Individual Facility Reports ({Facility}_Report_{MonthYear}.pdf)
    • Header: Facility name, parent system, reporting month
    • Metrics: Total Trips, On-Time Trips, Late Trips, OTP %
    • Optional: trend line vs. prior month if historical data available

Use a consistent template/branding across all PDFs in the package so stakeholders receive a uniform look.

Step 6: QA check

  • Sum of individual facility Total Trips must equal the system rollup's Total Contract Volume.
  • Spot-check 2-3 raw records per facility against the final PDF numbers.
  • Confirm no facility was dropped due to naming mismatch (cross-check Step 1 flags).
Recommendation▾
Include a bad-output example (e.g., what happens if facility names aren't normalized or OTP column has unexpected values) to show failure modes alongside successes.
15 / 20

Example 1: Input: Raw data row — Location Name: "Chippenham Hospital", Trip Status: "Completed", OTP: ""(blank) Output: Counts toward Chippenham Hospital's Total Trips and On-Time Trips; rolls up into HCA system volume and on-time count.

Example 2: Input: Raw data row — Location Name: "Short Pump ER", Trip Status: "Dry Run", OTP: "Late" Output: Excluded entirely — does not count toward Total Trips, On-Time, or Late for Short Pump ER or the Bon Secours rollup.

Example 3: Input: 120 completed trips for Reston Hospital Center, 9 marked "Late" Output: Reston Hospital Center report shows Total Trips: 120, On-Time: 111, Late: 9, OTP %: 92.5%. These values feed into the HCA system rollup totals.

Recommendation▾
Clarify how prior-month historical data should be sourced/stored for the trend-line feature mentioned as optional.
  • Treat blank/null OTP strictly as on-time — don't treat other non-"Late" values (e.g., typos) as automatically on-time without a quick distinct-value check on the OTP column first.
  • Always reconcile facility name variants (e.g., "St. Mary's" vs "St Marys") against the master FACILITY_MAP before running totals.
  • Weight system-level OTP % by trip volume, not a simple average of each facility's OTP %.
  • Keep the exclusion list (Dry Run/Cancel/Assist) centralized and confirm actual status column values match expected strings each month — naming in source data can drift.
  • Archive the raw filtered dataset alongside each month's PDFs for auditability.
  • Don't average facility OTP percentages to get the system OTP — low-volume facilities will skew it. Always use trip-weighted totals.
  • Don't assume blank OTP means missing data — per spec, blank explicitly means on-time.
  • Don't silently drop unmatched Location Name values — this undercounts contract volume and must be surfaced for review.
  • Don't include Dry Runs/Cancels/Assists anywhere in Total Trips, even if they have a non-blank OTP value.
  • Don't regenerate reports without re-running the QA reconciliation (Step 6) — totals mismatches are often caused by late data corrections or status re-mapping.
0
Grade A-AI Skill Framework
Scorecard
Criteria Breakdown
Quick Start
14/15
Workflow
14/15
Examples
15/20
Completeness
17/20
Format
15/15
Conciseness
13/15