# NHS England A&E Performance Analytics **Analysing 17 months of NHS England A&E data to find where emergency care performance is actually failing, and why the national headline figure hides it.** ![A&E performance dashboard](dashboard/nhs_ae_dashboard.png) --- ## Executive summary NHS England reports that **74.6%** of A&E attendances were seen within four hours in August 2026. That number is misleading. Splitting the data by department type shows that **major (Type 1) A&E departments achieved only 60.9%.** The national headline is lifted by walk-in centres and minor-injury units that routinely score above 96%. The gap between the two has averaged **36.1 percentage points** across every one of the 17 months analysed. Three further findings came out of the analysis: 1. **The 2026 recovery has reversed.** Performance peaked at 76.6% in March 2026 and has fallen every month since. Year-on-year change turned negative in June 2026 and has stayed negative for three consecutive months. 2. **Winter pressure does not track volume.** Winter performance is 2.1 points worse and 12-hour trolley waits run **35% higher per admission**, yet winter actually sees *fewer* attendances per day than the rest of the year (72,453 vs 75,078). That pattern is more consistent with an admission-flow and capacity constraint than with footfall, though this analysis doesn't isolate the mechanism. 3. **Busier departments are not the worse performers.** Across 120 acute providers, the correlation between attendances per day and four-hour performance is **r = −0.15**: volume explains barely 2% of the variation between trusts. Performance differences are local, not demand-driven. **118 of the 120** major A&E providers analysed did not meet the 95% constitutional standard in a single month out of 17. --- ## Business / operational problem The four-hour A&E standard is one of the most politically and operationally scrutinised measures in the NHS. But the published national figure blends fundamentally different services together, and a single month in isolation says nothing about whether a trust is improving or deteriorating. This project answers questions an NHS performance or information analyst would actually be asked: - What is the real performance position for major A&E departments, separated from minor-injury activity? - Which providers are sustainably improving, and which are deteriorating? - Is deterioration explained by rising demand, or by something else? - Where are 12-hour trolley waits concentrated? - Which providers are genuine outliers rather than just below average? --- ## Dataset | | | |---|---| | **Source** | NHS England, [A&E Attendances and Emergency Admissions](https://www.england.nhs.uk/statistics/statistical-work-areas/ae-waiting-times-and-activity/) | | **Files** | 17 monthly provider-level CSV releases, downloaded programmatically | | **Period** | April 2025 – August 2026 (17 consecutive months) | | **Grain** | One row per organisation per month | | **Raw rows** | 3,359 | | **Organisations** | 205 (121 with Type 1 major A&E activity) | | **Attendances analysed** | 38,658,930 (of which 23,973,798 Type 1) | | **Emergency admissions** | 6,812,314 | | **Licence** | Open Government Licence v3.0 | Download URLs, byte counts and SHA-256 hashes for every file are recorded in `data/raw/_download_manifest.json`. --- ## Tools **SQL (SQLite)** · **Excel** · **Python (pandas)** · **Power BI** Python handles ingestion and reproducible cleaning; SQL is the analytical layer; Excel provides an independent reconciliation; Power BI carries the report. --- ## Process ``` 17 NHS CSVs → profile → clean & conform → star schema (SQLite) → 12 analytical queries ↓ Excel reconciliation · dashboard · Power BI model ``` 1. **Acquire**: download 17 monthly releases, record URL + hash for each. 2. **Profile**: quantify defects *before* cleaning, so every rule answers an observed problem. 3. **Clean**: 8 documented rules (below). 4. **Model**: star schema, one fact table, two conformed dimensions, three analytical views. 5. **Validate**: 17 automated checks; the build fails if any returns non-zero. 6. **Analyse**: 12 SQL queries using CTEs, window functions and self-joins. 7. **Reconcile**: Excel independently recalculates the headline measures and compares them to the SQL output. 8. **Present**: six-panel dashboard plus this write-up. --- ## Data-quality considerations Profiling found real defects. The most serious one would have silently corrupted every national figure: | Defect found | Volume | How it was handled | |---|---|---| | **Embedded grand-total row in every monthly file** | 17 rows | **Removed.** Each file contains an `Org Code = TOTAL` row summarising the providers listed beneath it. Summing the file without removing it **doubles every national total**, verified at exactly 2.0000×. Casing is also inconsistent (`TOTAL` in 15 files, `Total` in 2), so the match must be case-insensitive. | | Trailing whitespace in text fields | 2,919 rows | Stripped. Before stripping, `Org Code` produced 207 distinct values where only 205 organisations exist. | | Org codes carrying multiple names | 8 codes | Conformed to the most recent name (trust renames and mergers, e.g. "Royal Surrey County Hospital NHS FT" → "Royal Surrey NHS FT"). | | Rows with zero attendances | 206 rows | Flagged, not deleted: specialist trusts reporting admissions without A&E attendances. | | Organisations not reporting in all months | 22 orgs | Flagged. Trend and ranking queries use a like-for-like panel so service openings/closures don't masquerade as performance change. | | February short-month distortion | n/a | Added a per-day rate; 28-day February is otherwise not comparable to 31-day months. | **This is the single most important thing in the project.** The total-row defect was not caught by inspection. It surfaced because a regional breakdown produced a region called "Total" holding exactly half the national attendances. Two permanent guards now exist: a validation check rejecting any `TOTAL` row, and a check failing the build if any single provider exceeds 15% of national attendances. Remaining limitations are documented in [`docs/METHODOLOGY.md`](docs/METHODOLOGY.md). --- ## Key findings ### 1. The national headline conceals major A&E performance | | Aug 2026 | |---|---| | Published all-type figure | **74.6%** | | Type 1 (major A&E) | **60.9%** | | Type 2 / Other (walk-in, MIU, UTC) | **97.0%** | | Gap | **36.2 pp** | Type 1 attendances are 62% of volume but account for almost all of the breaches. The gap has been stable across all 17 months (mean 36.1 pp), so this is structural, not a one-month artefact. ### 2. The 2026 recovery reversed Performance rose to 76.6% in March 2026, then fell in four of the following five months to 74.6%. Year-on-year comparison turned negative in June 2026 (−0.44 pp) and remained negative through July (−0.93 pp) and August (−0.75 pp), while attendances per day reached a 17-month high of 78,742 in June 2026. ### 3. Winter pressure is a flow problem, not a demand problem | | Winter (Dec–Feb) | Rest of year | |---|---|---| | Four-hour performance | 72.8% | 75.0% | | 12-hour DTA waits per 1,000 admissions | **151.3** | 112.3 | | Attendances per day | **72,453** | 75,078 | Winter is *quieter* by attendance volume yet substantially worse on both performance and trolley waits. That points at admission capacity and patient flow rather than front-door demand. ### 4. Volume does not explain provider performance Across 120 acute providers in August 2026, attendances per day vs four-hour performance gives **r = −0.15 (r² = 0.02)**. The three best performers are all children's hospitals (Sheffield Children's 92.3%, Birmingham Women's and Children's 92.3%, Alder Hey 89.4%): a case-mix effect, not quiet departments. ### 5. Performance is diverging between providers Comparing the first three months to the most recent three, on a like-for-like Type 1 panel: - **41 providers improved materially** (≥3 pp), best: George Eliot Hospital **+23.6 pp** - **51 were broadly stable** - **28 deteriorated materially**, worst: Milton Keynes University Hospital **−15.2 pp**, University Hospitals Plymouth **−14.8 pp** Plymouth is both the largest deterioration and the only negative statistical outlier in the latest month (33.6%, z = −2.47). --- ## Recommendations 1. **Report Type 1 performance as the primary operational measure.** The blended figure is not actionable: a 36-point internal gap means the headline can improve while major A&E gets worse. 2. **Target the deteriorating 28, not the lowest 28.** Several low absolute performers are improving fast; several mid-table trusts are falling. Trajectory identifies emerging problems earlier than rank. 3. **Treat winter as an admission-flow problem.** Winter attendances are lower, yet trolley waits rise 35% per admission. Interventions aimed at front-door demand address the wrong constraint. 4. **Investigate the improvers.** George Eliot (+23.6 pp) and Princess Alexandra (+15.3 pp) achieved large gains without large volume changes, which suggests transferable operational practice. 5. **Stop using volume to explain performance.** With r² = 0.02, "we were busy" is not supported by the data. --- ## Technical implementation ### Star schema ``` dim_provider (205) fact_ae_monthly (3,342) dim_month (17) org_code ◄────────── org_code month_key provider_name month_key ──────────────────► month_name region attendances_total financial_year provider_type breaches_total days_in_month in_complete_panel admissions_via_ae is_winter dta_wait_12h_plus ``` Additive base measures are stored; ratios are recalculated from summed bases at every level. **Averaging per-provider percentages would weight a 3,000-attendance unit the same as a 30,000-attendance major department.** ### SQL `sql/analysis.sql` holds **12 analytical queries** (547 lines of SQL across three files) using CTEs, `LAG`, `RANK`, `ROW_NUMBER`, windowed running totals, self-joins and a Pearson correlation computed in SQL. `sql/validation.sql` holds **17 checks, all passing**: grain uniqueness, referential integrity, measure reconciliation, null-handling, and two guards against the total-row defect. ### Excel `excel/NHS_AE_Performance_Analysis.xlsx` has 5 sheets. Not a data dump: the `QA_Reconciliation` sheet recalculates attendances and performance with native Excel formulas and compares them to the SQL output for all 17 months, flagging any disagreement as FAIL. `Provider_Summary` builds a 120-provider summary with `SUMIFS`, `COUNTIFS` and `XLOOKUP`. ### Power BI `powerbi/NHS_AE_Performance.pbix` is a built report on the star schema, with **17 documented DAX measures** (`powerbi/measures.dax`), including correctly context-independent ratios and time-intelligence (`DATEADD`, `DATESINPERIOD`). The report page carries four headline cards, a 17-month trend line, a regional bar chart and a bottom-10 provider chart, filterable by financial year and region. --- ## Files / reproduction ```bash python scripts/01_profile_raw.py # profile raw data quality python scripts/02_clean_transform.py # apply 8 cleaning rules python scripts/03_build_database.py # build + validate SQLite (17 checks) python scripts/04_run_analysis.py # execute 12 analytical queries python scripts/05_build_excel.py # build Excel workbook python scripts/06_build_dashboard.py # render dashboard python scripts/07_export_powerbi.py # export Power BI model + DAX ``` | Path | Contents | |---|---| | `data/raw/` | 17 source CSVs + download manifest with hashes | | `data/processed/` | Cleaned fact and dimension tables | | `sql/` | `schema.sql`, `analysis.sql`, `validation.sql` | | `reports/query_outputs/` | CSV output of all 12 queries, evidence they ran | | `excel/` | Excel workbook with reconciliation | | `dashboard/` | Dashboard PNG | | `powerbi/` | `NHS_AE_Performance.pbix`, model-ready CSVs + `measures.dax` | | `PROJECT_EVIDENCE.json` | Every metric in this README, machine-derived | | `INTERVIEW_DEFENCE.md` | 15 interview questions with answers | Requires Python 3 with `pandas`, `matplotlib`, `openpyxl`. No database server needed.