NHS RxInsight

An end-to-end analytics portfolio project for exploring NHS primary-care prescribing cost, volume, variation and area-deprivation associations using the NHS Business Services Authority (NHSBSA) English Prescribing Dataset (EPD).

Safety and interpretation: This repository analyses aggregate prescribing activity. It does not assess individual patients, judge clinical appropriateness, recommend treatments or claim that deprivation causes prescribing outcomes.

Why this project matters

The official EPD is published monthly and is large enough to justify distributed processing. NHSBSA describes it as a practice-level dataset with roughly 17 million records added each month. This project demonstrates how an analyst can turn that data into auditable finance, utilisation and benchmarking outputs without over-interpreting clinical meaning.

Questions answered

1. How are total actual cost and item volume changing over time?
2. Which substances and practices contribute most to expenditure?
3. How does weighted cost per item vary between practices and ICBs?
4. What does prescribing look like after normalising by an estimated registered population?
5. Which practice-month observations should be prioritised for analytical review?
6. Is there an association between area deprivation decile and aggregate prescribing indicators?
7. Does a simple forecasting model improve on a transparent previous-month baseline?

Repository evidence

Component	Evidence included
Python / pandas	Official-schema normalisation, validation, feature engineering, summaries and forecasting
Scikit-learn	Isolation Forest review flags and a Random Forest forecast benchmark
PySpark	Distributed official-schema ETL and partitioned Parquet output
Hadoop / HDFS	Raw, reference and gold path commands
Hive / SQL	External table and decision-focused analytical queries
R	Spearman association test and adjusted linear model
Excel	Formatted management workbook with charts and source notes
Power BI / Tableau	Detailed build specifications; binary dashboard files are not claimed
pytest / GitHub Actions	Automated tests and continuous integration

Architecture

NHSBSA monthly EPD CSV/ZIP
        +
practice-to-deprivation reference
        |
        v
HDFS raw and reference zones
        |
        v
PySpark normalisation + quality checks + feature engineering
        |
        v
Partitioned Parquet / Hive analytical table
        |
        +----------------+-------------------+
        |                |                   |
      SQL              Python / R       Power BI / Tableau
        |                |                   |
        +----------------+-------------------+
                         |
                         v
                 Excel management report

Official schema support

The ETL maps NHSBSA fields such as:

• YEAR_MONTH → year_month
• REGIONAL_OFFICE_NAME → regional_office
• ICB_NAME → icb_name
• PRACTICE_CODE → practice_code
• BNF_CHEMICAL_SUBSTANCE → chemical_substance
• BNF_PRESENTATION_NAME → bnf_description
• ITEMS, TOTAL_QUANTITY, NIC, ACTUAL_COST
• SNOMED_CODE is treated as text, not a numeric measure

See docs/official_source_mapping.md.

Quick start

python -m venv .venv
# Windows: .venv\Scripts\activate
# macOS/Linux: source .venv/bin/activate
pip install -r requirements.txt
python run_demo.py
python -m pytest

The demo uses synthetic data so the repository runs without downloading millions of rows. It writes CSV outputs and charts to reports/demo_outputs/ and refreshes the analysis used by the workbook.

Report preview

The workbook separates decision summaries, monthly trends, practice benchmarks, deprivation associations, statistical review flags, forecast evidence, KPI definitions and official sources.

Main outputs

• reports/NHS_RxInsight_Management_Report.xlsx
• reports/demo_outputs/monthly_prescribing_summary.csv
• reports/demo_outputs/practice_month_benchmark.csv
• reports/demo_outputs/deprivation_summary.csv
• reports/demo_outputs/review_flags.csv
• reports/demo_outputs/forecast_metrics.csv
• reports/images/monthly_cost_and_items.png
• reports/images/deprivation_cost_per_1000.png
• reports/images/forecast_comparison.png

Run with official EPD data

Download one or more monthly files:

python src/download_data.py --months "Jan 2025" "Feb 2025" --output data/raw/epd

Install the optional big-data dependencies, then run distributed ETL:

pip install -r requirements-bigdata.txt
spark-submit src/spark_etl.py \
  --epd-input data/raw/epd \
  --deprivation-input data/reference/practice_deprivation.csv \
  --output hdfs:///projects/nhs_rxinsight/gold/fact_prescribing

A production deprivation reference is not included. Build it by linking practice postcodes to LSOAs and then to the updated English Indices of Deprivation file. See the mapping guide before interpreting any deprivation analysis.

KPI definitions

• Weighted cost per item: sum(actual_cost) / sum(items)
• Cost per 1,000 registered patients: sum(actual_cost) / population × 1,000
• Items per 1,000 registered patients: sum(items) / population × 1,000
• Month-on-month cost change: current practice-substance cost versus its previous month
• Review flag: a statistical prioritisation signal, not evidence of inappropriate prescribing

Limitations

• Bundled records are synthetic and exist only to demonstrate the workflow.
• Practice population and deprivation values in the sample are synthetic.
• IMD is an area-level relative measure and should not be treated as an individual patient characteristic.
• Cost differences can reflect population size, case mix, medicine mix, availability and coding changes.
• Forecast scores from the sample are illustrative and not evidence of operational accuracy.
• Power BI and Tableau guides are supplied, but no .pbix or .twbx file is claimed.

Data sources and licence

• NHSBSA English Prescribing Dataset with SNOMED code
• NHSBSA Prescriber Details
• English Indices of Deprivation 2025

The NHSBSA EPD is published under the Open Government Licence 3.0. Repository code is released under the MIT Licence. Preserve source attribution when using official data.