How are the TPMAs generated?

This should include a map of all the different data layers and what gets cleaned/added at each stage from hes_apc > raw_data > default > inputs-data / model-data parquet files, with links to the specific files where the cleaning happens at each stage.

Step (1): UDAL HES data to hes_* tables

• Raw data is recieved from NHS England via UDAL (a secure NHS data platform), and is accessed for pipeline development via Databricks.
• Raw NHSE HES data requires restructuring before it can be used, and this step normalises it into a structured table called HES_apc.
• The resulting HES_apc tables are only available to Strategy Unit (SU) colleagues and sits upstream of the open GitHub repo.

Link: https://github.com/The-Strategy-Unit/hes_processing [outdated - @tomjemmett to update]

Step (2): hes_* tables to raw_data tables

• Still at episode level, but many useful columns are added to support downstream processing.
• There are many new columns, all detailed in the scripts. These include:

1. Maternity episode type (re-derived, as UDAL does not include the HES-derived version)
2. Primary diagnosis
3. Primary procedure
4. Treatment specialty groupings (e.g. tretspef_grouped)
5. Delivery/Birth flags (e.g. maternity_delivery_in_spell)

• There is also a simple true/false flag for whether a procedure was administered in the episode or not, which makes it easier to filter activity later on in the pipeline.
• Key filters applied at this step are:

1. Mental health providers are removed (using the ERIC dataset) to prevent extremely long Length-of-Stay (LoS) records skewing results as some TPMAs are calculated using the mean LoS
2. Well baby episodes are removed (minimal medical intervention).
3. Unfinished episodes are removed (patient still admitted at the time the data was submitted to SUS).

• Independent sector providers are retained at this step.

ℹ️ This is also where Types of Potentially Mitigatable Activity (TPMAs) are flagged on individual rows.

Episode vs. Spell explained

• A spell= full hospital stay from admission to discharge. But a spell can contain multiple episodes (one per consultant/care change)
• The pipeline uses the last episode in the spell because:

1. It should contain the most complete ICD-10 diagnosis coding.
2. Length-of-Stay (LoS) is only known at discharge.
3. Modelling at admission-avoidance level requires spell-level thinking, not individual episode-level.
4. Data integrity issues (e.g. hospitals changing EPR systems, breaking spell ID continuity) making the joining of first and last episodes unreliable.
5. Known limitation: primary diagnosis at last episode may differ from the reason for original admission. This is acknowledged as a known trade-off.
6. Inpatients remain at individual record (unaggregated) level throughout.

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/raw_data

Step (3): raw_data tables to aggregated_ tables

• Outpatients and A&E data are aggregated (grouped by characteristics such as age, sex, ethinicty, ICB, and with activity counts summed). This is to reduce data volume and memory requirements.
• Individual-level detail is lost at this point (meaning things like appointment dates).
• Inpatients are never aggregated, and always remain at record level.

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/aggregated_data

Step (4): raw_data tables to default tables

• Filters to acute NHS providers only, and excludes independent sector providers.
• This is the recommended table for most TPMA-related analysis work.
• The upstream raw data tables (which retain the independent sector providers) are available for more granular (in-depth) or research use cases.

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/default

Step (5): default tables to inputs data parquet files

• Data is extracted from UDAL into a Parquet file format so it can be used outside of UDAL.
• The data is separated into two outputs, Inputs Data and Model Data. Step 5 focuses on Inputs Data.
• The pipeline is run twice, (1) grouped at provider level, (2) grouped at local authority level.
• Granularity (in-depthness) note: ICB level is not included in the pipeline, with the smallest geography currently being LSOA11 (2011 census boundaries), as the updated 2021 LSOA census boundaries have not yet been adopted.
• These are accessible on UDAL Databricks or via MLCSU Azure Blob Storage (see https://github.com/The-Strategy-Unit/TPMAs/wiki/Where-does-TPMA-data-live%3F)

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/inputs_data

Step (6): default tables to model data parquet files

• Data is extracted from UDAL into a Parquet file format so it can be used outside of UDAL.
• The data is separated into two outputs, Inputs Data and Model Data. Step 6 focuses on Model Data.
• Unaggregated counts in the default table format (which is by provider), are used directly by the NHP model.
• There is not a conversion into age-sex standardised rates, as this table retains the defauly table structure.
• These are accessible on UDAL Databricks or via MLCSU Azure Blob Storage (see https://github.com/The-Strategy-Unit/TPMAs/wiki/Where-does-TPMA-data-live%3F)

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/model_data

Additional Information

Pipeline Overview

• All scripts (except the very first HES cleaning step- Step (1)), are hosted in the open NHP GitHub repo.
• Scripts are linked together and run in a defined order via configuration files on UDAL.
• The pipeline is automated, which means that it is not designed for ad-hoc manual execution (doing it yourself). This means that if you simply copy and paste the scripts into a notebook, the likelihood is that this will not work.

Discussion Points

• Clinical coding nuance: Although the last episode in a spell should contain all dignoses from across the spell, the ordering is determined by clinical coding rules. This means that primary diagnosis= main reason for stay, which may differ from the original admission reason.
• Mental health exclusion: The NHP model is designed for acute activity, which means that mental health patients (including forensic units with decade-long stays) would skew Length-of-Stay (LoS) calculations. This is why they are removed.
• Independent sector data: NHS commissioned activity completed by independent sector providers is included, but privately funded activity is not.
• Geography: The minimum geography is LSOA level as age-standardisation becomes unreliable at very small geographies.