dspt_data_cuts_curation_22_23.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.9/site-packages/geopandas/_compat.py:111: UserWarning: The Shapely GEOS version (3.10.2-CAPI-1.16.0) is incompatible with the GEOS version PyGEOS was compiled with (3.10.3-CAPI-1.16.1). Conversions between both will be slow.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import geopandas\n",
    "from geopy.geocoders import Nominatim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Organisation Name</th>\n",
       "      <th>Status</th>\n",
       "      <th>Date Of Publication</th>\n",
       "      <th>Primary Sector</th>\n",
       "      <th>Integrated Care Board (where available, from ODS)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00L</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA ICB - 00L (CCG)</td>\n",
       "      <td>21/22 Standards Met</td>\n",
       "      <td>30/06/2022</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00N</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA ICB - 00N (CCG)</td>\n",
       "      <td>21/22 Standards Met</td>\n",
       "      <td>30/06/2022</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00P</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA ICB - 00P (CCG)</td>\n",
       "      <td>21/22 Standards Met</td>\n",
       "      <td>28/06/2022</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00Q</td>\n",
       "      <td>NHS LANCASHIRE AND SOUTH CUMBRIA ICB - 00Q (CCG)</td>\n",
       "      <td>21/22 Standards Met</td>\n",
       "      <td>22/06/2022</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NHS LANCASHIRE AND SOUTH CUMBRIA INTEGRATED CA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00R</td>\n",
       "      <td>NHS LANCASHIRE AND SOUTH CUMBRIA ICB - 00R (CCG)</td>\n",
       "      <td>21/22 Standards Met</td>\n",
       "      <td>29/06/2022</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NHS LANCASHIRE AND SOUTH CUMBRIA INTEGRATED CA...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Code                                 Organisation Name               Status  \\\n",
       "0  00L  NHS NORTH EAST AND NORTH CUMBRIA ICB - 00L (CCG)  21/22 Standards Met   \n",
       "1  00N  NHS NORTH EAST AND NORTH CUMBRIA ICB - 00N (CCG)  21/22 Standards Met   \n",
       "2  00P  NHS NORTH EAST AND NORTH CUMBRIA ICB - 00P (CCG)  21/22 Standards Met   \n",
       "3  00Q  NHS LANCASHIRE AND SOUTH CUMBRIA ICB - 00Q (CCG)  21/22 Standards Met   \n",
       "4  00R  NHS LANCASHIRE AND SOUTH CUMBRIA ICB - 00R (CCG)  21/22 Standards Met   \n",
       "\n",
       "  Date Of Publication                     Primary Sector  \\\n",
       "0          30/06/2022  CCG / Integrated Care Board (ICB)   \n",
       "1          30/06/2022  CCG / Integrated Care Board (ICB)   \n",
       "2          28/06/2022  CCG / Integrated Care Board (ICB)   \n",
       "3          22/06/2022  CCG / Integrated Care Board (ICB)   \n",
       "4          29/06/2022  CCG / Integrated Care Board (ICB)   \n",
       "\n",
       "   Integrated Care Board (where available, from ODS)  \n",
       "0  NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...  \n",
       "1  NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...  \n",
       "2  NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...  \n",
       "3  NHS LANCASHIRE AND SOUTH CUMBRIA INTEGRATED CA...  \n",
       "4  NHS LANCASHIRE AND SOUTH CUMBRIA INTEGRATED CA...  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#load in latest dspt snapshot\n",
    "dspt_snapshot = pd.read_csv('data/DSPT Snapshots/22_23/DSPT search results 17_10_2023 08_55_43.csv')\n",
    "dspt_snapshot.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>R0A</td>\n",
       "      <td>MANCHESTER UNIVERSITY NHS FOUNDATION TRUST</td>\n",
       "      <td>Y62</td>\n",
       "      <td>QOP</td>\n",
       "      <td>COBBETT HOUSE</td>\n",
       "      <td>OXFORD ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MANCHESTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M13 9WL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>R0B</td>\n",
       "      <td>SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QHM</td>\n",
       "      <td>SUNDERLAND ROYAL HOSPITAL</td>\n",
       "      <td>KAYLL ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SUNDERLAND</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SR4 7TP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R0D</td>\n",
       "      <td>UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST</td>\n",
       "      <td>Y58</td>\n",
       "      <td>QVV</td>\n",
       "      <td>MANAGEMENT OFFICES</td>\n",
       "      <td>POOLE HOSPITAL</td>\n",
       "      <td>LONGFLEET ROAD</td>\n",
       "      <td>POOLE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BH15 2JB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R1A</td>\n",
       "      <td>HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QGH</td>\n",
       "      <td>UNIT 2 KINGS COURT</td>\n",
       "      <td>CHARLES HASTINGS WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WORCESTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WR5 1JR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R1C</td>\n",
       "      <td>SOLENT NHS TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QRL</td>\n",
       "      <td>SOLENT NHS TRUST HEADQUARTERS</td>\n",
       "      <td>HIGHPOINT VENUE</td>\n",
       "      <td>BURSLEDON ROAD</td>\n",
       "      <td>SOUTHAMPTON</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SO19 8BR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>RYY</td>\n",
       "      <td>KENT COMMUNITY HEALTH NHS FOUNDATION TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QKS</td>\n",
       "      <td>TRINITY HOUSE</td>\n",
       "      <td>110-120 EUREKA PARK</td>\n",
       "      <td>EUREKA BUSINESS PARK</td>\n",
       "      <td>ASHFORD</td>\n",
       "      <td>KENT</td>\n",
       "      <td>TN25 4AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TAD</td>\n",
       "      <td>BRADFORD DISTRICT CARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QWO</td>\n",
       "      <td>NEW MILL</td>\n",
       "      <td>VICTORIA ROAD</td>\n",
       "      <td>SALTAIRE</td>\n",
       "      <td>SHIPLEY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BD18 3LD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TAF</td>\n",
       "      <td>CAMDEN AND ISLINGTON NHS FOUNDATION TRUST</td>\n",
       "      <td>Y56</td>\n",
       "      <td>QMJ</td>\n",
       "      <td>ST PANCRAS HOSPITAL</td>\n",
       "      <td>4 ST PANCRAS WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NW1 0PE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TAH</td>\n",
       "      <td>SHEFFIELD HEALTH &amp; SOCIAL CARE NHS FOUNDATION ...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QF7</td>\n",
       "      <td>CENTRE COURT</td>\n",
       "      <td>ATLAS WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SHEFFIELD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S4 7QQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TAJ</td>\n",
       "      <td>BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QUA</td>\n",
       "      <td>TRAFALGAR HOUSE</td>\n",
       "      <td>47-49 KING STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DUDLEY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DY2 8PS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>215 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       0                                                  1    2    3  \\\n",
       "0    R0A         MANCHESTER UNIVERSITY NHS FOUNDATION TRUST  Y62  QOP   \n",
       "1    R0B  SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...  Y63  QHM   \n",
       "2    R0D   UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST  Y58  QVV   \n",
       "3    R1A  HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...  Y60  QGH   \n",
       "4    R1C                                   SOLENT NHS TRUST  Y59  QRL   \n",
       "..   ...                                                ...  ...  ...   \n",
       "210  RYY         KENT COMMUNITY HEALTH NHS FOUNDATION TRUST  Y59  QKS   \n",
       "0    TAD        BRADFORD DISTRICT CARE NHS FOUNDATION TRUST  Y63  QWO   \n",
       "1    TAF          CAMDEN AND ISLINGTON NHS FOUNDATION TRUST  Y56  QMJ   \n",
       "2    TAH  SHEFFIELD HEALTH & SOCIAL CARE NHS FOUNDATION ...  Y63  QF7   \n",
       "3    TAJ      BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST  Y60  QUA   \n",
       "\n",
       "                                 4                     5  \\\n",
       "0                    COBBETT HOUSE           OXFORD ROAD   \n",
       "1        SUNDERLAND ROYAL HOSPITAL            KAYLL ROAD   \n",
       "2               MANAGEMENT OFFICES        POOLE HOSPITAL   \n",
       "3               UNIT 2 KINGS COURT  CHARLES HASTINGS WAY   \n",
       "4    SOLENT NHS TRUST HEADQUARTERS       HIGHPOINT VENUE   \n",
       "..                             ...                   ...   \n",
       "210                  TRINITY HOUSE   110-120 EUREKA PARK   \n",
       "0                         NEW MILL         VICTORIA ROAD   \n",
       "1              ST PANCRAS HOSPITAL      4 ST PANCRAS WAY   \n",
       "2                     CENTRE COURT             ATLAS WAY   \n",
       "3                  TRAFALGAR HOUSE     47-49 KING STREET   \n",
       "\n",
       "                        6            7     8         9  \n",
       "0                     NaN   MANCHESTER   NaN   M13 9WL  \n",
       "1                     NaN   SUNDERLAND   NaN   SR4 7TP  \n",
       "2          LONGFLEET ROAD        POOLE   NaN  BH15 2JB  \n",
       "3                     NaN    WORCESTER   NaN   WR5 1JR  \n",
       "4          BURSLEDON ROAD  SOUTHAMPTON   NaN  SO19 8BR  \n",
       "..                    ...          ...   ...       ...  \n",
       "210  EUREKA BUSINESS PARK      ASHFORD  KENT  TN25 4AZ  \n",
       "0                SALTAIRE      SHIPLEY   NaN  BD18 3LD  \n",
       "1                     NaN       LONDON   NaN   NW1 0PE  \n",
       "2                     NaN    SHEFFIELD   NaN    S4 7QQ  \n",
       "3                     NaN       DUDLEY   NaN   DY2 8PS  \n",
       "\n",
       "[215 rows x 10 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#load in auxillary trust data (etr and ect and merge)\n",
    "etr = pd.read_csv('data/aux/22_23/etr.csv', header=None)\n",
    "ect = pd.read_csv('data/aux/22_23/ect.csv', header=None)\n",
    "trusts_combined = pd.concat([etr, ect])[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]]\n",
    "trusts_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Organisation Name</th>\n",
       "      <th>National Grouping</th>\n",
       "      <th>High Level Health Geography</th>\n",
       "      <th>Address Line 1</th>\n",
       "      <th>Address Line 2</th>\n",
       "      <th>Address Line 3</th>\n",
       "      <th>Address Line 4</th>\n",
       "      <th>Address Line 5</th>\n",
       "      <th>Post Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>R0A</td>\n",
       "      <td>MANCHESTER UNIVERSITY NHS FOUNDATION TRUST</td>\n",
       "      <td>Y62</td>\n",
       "      <td>QOP</td>\n",
       "      <td>COBBETT HOUSE</td>\n",
       "      <td>OXFORD ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MANCHESTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M13 9WL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>R0B</td>\n",
       "      <td>SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QHM</td>\n",
       "      <td>SUNDERLAND ROYAL HOSPITAL</td>\n",
       "      <td>KAYLL ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SUNDERLAND</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SR4 7TP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R0D</td>\n",
       "      <td>UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST</td>\n",
       "      <td>Y58</td>\n",
       "      <td>QVV</td>\n",
       "      <td>MANAGEMENT OFFICES</td>\n",
       "      <td>POOLE HOSPITAL</td>\n",
       "      <td>LONGFLEET ROAD</td>\n",
       "      <td>POOLE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BH15 2JB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R1A</td>\n",
       "      <td>HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QGH</td>\n",
       "      <td>UNIT 2 KINGS COURT</td>\n",
       "      <td>CHARLES HASTINGS WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WORCESTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WR5 1JR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R1C</td>\n",
       "      <td>SOLENT NHS TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QRL</td>\n",
       "      <td>SOLENT NHS TRUST HEADQUARTERS</td>\n",
       "      <td>HIGHPOINT VENUE</td>\n",
       "      <td>BURSLEDON ROAD</td>\n",
       "      <td>SOUTHAMPTON</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SO19 8BR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>RYY</td>\n",
       "      <td>KENT COMMUNITY HEALTH NHS FOUNDATION TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QKS</td>\n",
       "      <td>TRINITY HOUSE</td>\n",
       "      <td>110-120 EUREKA PARK</td>\n",
       "      <td>EUREKA BUSINESS PARK</td>\n",
       "      <td>ASHFORD</td>\n",
       "      <td>KENT</td>\n",
       "      <td>TN25 4AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>TAD</td>\n",
       "      <td>BRADFORD DISTRICT CARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QWO</td>\n",
       "      <td>NEW MILL</td>\n",
       "      <td>VICTORIA ROAD</td>\n",
       "      <td>SALTAIRE</td>\n",
       "      <td>SHIPLEY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BD18 3LD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>TAF</td>\n",
       "      <td>CAMDEN AND ISLINGTON NHS FOUNDATION TRUST</td>\n",
       "      <td>Y56</td>\n",
       "      <td>QMJ</td>\n",
       "      <td>ST PANCRAS HOSPITAL</td>\n",
       "      <td>4 ST PANCRAS WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NW1 0PE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>TAH</td>\n",
       "      <td>SHEFFIELD HEALTH &amp; SOCIAL CARE NHS FOUNDATION ...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QF7</td>\n",
       "      <td>CENTRE COURT</td>\n",
       "      <td>ATLAS WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SHEFFIELD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S4 7QQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>TAJ</td>\n",
       "      <td>BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QUA</td>\n",
       "      <td>TRAFALGAR HOUSE</td>\n",
       "      <td>47-49 KING STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DUDLEY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DY2 8PS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>215 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Code                                  Organisation Name National Grouping  \\\n",
       "0    R0A         MANCHESTER UNIVERSITY NHS FOUNDATION TRUST               Y62   \n",
       "1    R0B  SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...               Y63   \n",
       "2    R0D   UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST               Y58   \n",
       "3    R1A  HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...               Y60   \n",
       "4    R1C                                   SOLENT NHS TRUST               Y59   \n",
       "..   ...                                                ...               ...   \n",
       "210  RYY         KENT COMMUNITY HEALTH NHS FOUNDATION TRUST               Y59   \n",
       "211  TAD        BRADFORD DISTRICT CARE NHS FOUNDATION TRUST               Y63   \n",
       "212  TAF          CAMDEN AND ISLINGTON NHS FOUNDATION TRUST               Y56   \n",
       "213  TAH  SHEFFIELD HEALTH & SOCIAL CARE NHS FOUNDATION ...               Y63   \n",
       "214  TAJ      BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST               Y60   \n",
       "\n",
       "    High Level Health Geography                 Address Line 1  \\\n",
       "0                           QOP                  COBBETT HOUSE   \n",
       "1                           QHM      SUNDERLAND ROYAL HOSPITAL   \n",
       "2                           QVV             MANAGEMENT OFFICES   \n",
       "3                           QGH             UNIT 2 KINGS COURT   \n",
       "4                           QRL  SOLENT NHS TRUST HEADQUARTERS   \n",
       "..                          ...                            ...   \n",
       "210                         QKS                  TRINITY HOUSE   \n",
       "211                         QWO                       NEW MILL   \n",
       "212                         QMJ            ST PANCRAS HOSPITAL   \n",
       "213                         QF7                   CENTRE COURT   \n",
       "214                         QUA                TRAFALGAR HOUSE   \n",
       "\n",
       "           Address Line 2        Address Line 3 Address Line 4 Address Line 5  \\\n",
       "0             OXFORD ROAD                   NaN     MANCHESTER            NaN   \n",
       "1              KAYLL ROAD                   NaN     SUNDERLAND            NaN   \n",
       "2          POOLE HOSPITAL        LONGFLEET ROAD          POOLE            NaN   \n",
       "3    CHARLES HASTINGS WAY                   NaN      WORCESTER            NaN   \n",
       "4         HIGHPOINT VENUE        BURSLEDON ROAD    SOUTHAMPTON            NaN   \n",
       "..                    ...                   ...            ...            ...   \n",
       "210   110-120 EUREKA PARK  EUREKA BUSINESS PARK        ASHFORD           KENT   \n",
       "211         VICTORIA ROAD              SALTAIRE        SHIPLEY            NaN   \n",
       "212      4 ST PANCRAS WAY                   NaN         LONDON            NaN   \n",
       "213             ATLAS WAY                   NaN      SHEFFIELD            NaN   \n",
       "214     47-49 KING STREET                   NaN         DUDLEY            NaN   \n",
       "\n",
       "    Post Code  \n",
       "0     M13 9WL  \n",
       "1     SR4 7TP  \n",
       "2    BH15 2JB  \n",
       "3     WR5 1JR  \n",
       "4    SO19 8BR  \n",
       "..        ...  \n",
       "210  TN25 4AZ  \n",
       "211  BD18 3LD  \n",
       "212   NW1 0PE  \n",
       "213    S4 7QQ  \n",
       "214   DY2 8PS  \n",
       "\n",
       "[215 rows x 10 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trusts_combined.columns = ['Code', 'Organisation Name', 'National Grouping', 'High Level Health Geography', 'Address Line 1', 'Address Line 2', 'Address Line 3', 'Address Line 4', 'Address Line 5', 'Post Code']\n",
    "trusts_combined = trusts_combined.reset_index(drop=True)\n",
    "trusts_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "locator = Nominatim(user_agent = 'myGeocoder')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lat = []\n",
    "long = []\n",
    "for i in list(trusts_combined['Post Code']):\n",
    "    lat.append(locator.geocode(i).latitude)\n",
    "    long.append(locator.geocode(i).longitude)\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/kt/92wnzvcj6qq_r6j3j19yp5lw0000gp/T/ipykernel_16494/1499399131.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  trusts_combined['lat'] = lat\n",
      "/var/folders/kt/92wnzvcj6qq_r6j3j19yp5lw0000gp/T/ipykernel_16494/1499399131.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  trusts_combined['long'] = long\n"
     ]
    }
   ],
   "source": [
    "trusts_combined = trusts_combined[['Code', 'Organisation Name', 'National Grouping', 'High Level Health Geography']]\n",
    "trusts_combined['lat'] = lat\n",
    "trusts_combined['long'] = long"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Organisation Name</th>\n",
       "      <th>National Grouping</th>\n",
       "      <th>High Level Health Geography</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>R0A</td>\n",
       "      <td>MANCHESTER UNIVERSITY NHS FOUNDATION TRUST</td>\n",
       "      <td>Y62</td>\n",
       "      <td>QOP</td>\n",
       "      <td>53.462440</td>\n",
       "      <td>-2.227710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>R0B</td>\n",
       "      <td>SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QHM</td>\n",
       "      <td>54.902770</td>\n",
       "      <td>-1.408705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R0D</td>\n",
       "      <td>UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST</td>\n",
       "      <td>Y58</td>\n",
       "      <td>QVV</td>\n",
       "      <td>50.721389</td>\n",
       "      <td>-1.972792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R1A</td>\n",
       "      <td>HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QGH</td>\n",
       "      <td>52.192162</td>\n",
       "      <td>-2.179038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R1C</td>\n",
       "      <td>SOLENT NHS TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QRL</td>\n",
       "      <td>50.901607</td>\n",
       "      <td>-1.329799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>RYY</td>\n",
       "      <td>KENT COMMUNITY HEALTH NHS FOUNDATION TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QKS</td>\n",
       "      <td>51.171653</td>\n",
       "      <td>0.870975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>TAD</td>\n",
       "      <td>BRADFORD DISTRICT CARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QWO</td>\n",
       "      <td>53.839670</td>\n",
       "      <td>-1.788610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>TAF</td>\n",
       "      <td>CAMDEN AND ISLINGTON NHS FOUNDATION TRUST</td>\n",
       "      <td>Y56</td>\n",
       "      <td>QMJ</td>\n",
       "      <td>51.535866</td>\n",
       "      <td>-0.131679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>TAH</td>\n",
       "      <td>SHEFFIELD HEALTH &amp; SOCIAL CARE NHS FOUNDATION ...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QF7</td>\n",
       "      <td>53.399947</td>\n",
       "      <td>-1.440936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>TAJ</td>\n",
       "      <td>BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QUA</td>\n",
       "      <td>52.508420</td>\n",
       "      <td>-2.084500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>215 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Code                                  Organisation Name National Grouping  \\\n",
       "0    R0A         MANCHESTER UNIVERSITY NHS FOUNDATION TRUST               Y62   \n",
       "1    R0B  SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...               Y63   \n",
       "2    R0D   UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST               Y58   \n",
       "3    R1A  HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...               Y60   \n",
       "4    R1C                                   SOLENT NHS TRUST               Y59   \n",
       "..   ...                                                ...               ...   \n",
       "210  RYY         KENT COMMUNITY HEALTH NHS FOUNDATION TRUST               Y59   \n",
       "211  TAD        BRADFORD DISTRICT CARE NHS FOUNDATION TRUST               Y63   \n",
       "212  TAF          CAMDEN AND ISLINGTON NHS FOUNDATION TRUST               Y56   \n",
       "213  TAH  SHEFFIELD HEALTH & SOCIAL CARE NHS FOUNDATION ...               Y63   \n",
       "214  TAJ      BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST               Y60   \n",
       "\n",
       "    High Level Health Geography        lat      long  \n",
       "0                           QOP  53.462440 -2.227710  \n",
       "1                           QHM  54.902770 -1.408705  \n",
       "2                           QVV  50.721389 -1.972792  \n",
       "3                           QGH  52.192162 -2.179038  \n",
       "4                           QRL  50.901607 -1.329799  \n",
       "..                          ...        ...       ...  \n",
       "210                         QKS  51.171653  0.870975  \n",
       "211                         QWO  53.839670 -1.788610  \n",
       "212                         QMJ  51.535866 -0.131679  \n",
       "213                         QF7  53.399947 -1.440936  \n",
       "214                         QUA  52.508420 -2.084500  \n",
       "\n",
       "[215 rows x 6 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trusts_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Organisation Name_x</th>\n",
       "      <th>National Grouping</th>\n",
       "      <th>High Level Health Geography</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>Organisation Name_y</th>\n",
       "      <th>Status</th>\n",
       "      <th>Date Of Publication</th>\n",
       "      <th>Primary Sector</th>\n",
       "      <th>Integrated Care Board (where available, from ODS)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>R0A</td>\n",
       "      <td>MANCHESTER UNIVERSITY NHS FOUNDATION TRUST</td>\n",
       "      <td>Y62</td>\n",
       "      <td>QOP</td>\n",
       "      <td>53.462440</td>\n",
       "      <td>-2.227710</td>\n",
       "      <td>MANCHESTER UNIVERSITY NHS FOUNDATION TRUST</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS GREATER MANCHESTER INTEGRATED CARE BOARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>R0B</td>\n",
       "      <td>SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QHM</td>\n",
       "      <td>54.902770</td>\n",
       "      <td>-1.408705</td>\n",
       "      <td>SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>15/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R0D</td>\n",
       "      <td>UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST</td>\n",
       "      <td>Y58</td>\n",
       "      <td>QVV</td>\n",
       "      <td>50.721389</td>\n",
       "      <td>-1.972792</td>\n",
       "      <td>UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST</td>\n",
       "      <td>22/23 Approaching Standards</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS DORSET INTEGRATED CARE BOARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R1A</td>\n",
       "      <td>HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QGH</td>\n",
       "      <td>52.192162</td>\n",
       "      <td>-2.179038</td>\n",
       "      <td>Herefordshire and Worcestershire Health and Ca...</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>05/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS HEREFORDSHIRE AND WORCESTERSHIRE INTEGRATE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R1C</td>\n",
       "      <td>SOLENT NHS TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QRL</td>\n",
       "      <td>50.901607</td>\n",
       "      <td>-1.329799</td>\n",
       "      <td>Solent NHS Trust</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS HAMPSHIRE AND ISLE OF WIGHT INTEGRATED CAR...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>RYY</td>\n",
       "      <td>KENT COMMUNITY HEALTH NHS FOUNDATION TRUST</td>\n",
       "      <td>Y59</td>\n",
       "      <td>QKS</td>\n",
       "      <td>51.171653</td>\n",
       "      <td>0.870975</td>\n",
       "      <td>Kent Community Health NHS Foundation Trust</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>27/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS KENT AND MEDWAY INTEGRATED CARE BOARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>TAD</td>\n",
       "      <td>BRADFORD DISTRICT CARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QWO</td>\n",
       "      <td>53.839670</td>\n",
       "      <td>-1.788610</td>\n",
       "      <td>BRADFORD DISTRICT CARE NHS FOUNDATION TRUST</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS WEST YORKSHIRE INTEGRATED CARE BOARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>TAF</td>\n",
       "      <td>CAMDEN AND ISLINGTON NHS FOUNDATION TRUST</td>\n",
       "      <td>Y56</td>\n",
       "      <td>QMJ</td>\n",
       "      <td>51.535866</td>\n",
       "      <td>-0.131679</td>\n",
       "      <td>CAMDEN AND ISLINGTON NHS FOUNDATION TRUST</td>\n",
       "      <td>22/23 Approaching Standards</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS NORTH CENTRAL LONDON INTEGRATED CARE BOARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>TAH</td>\n",
       "      <td>SHEFFIELD HEALTH &amp; SOCIAL CARE NHS FOUNDATION ...</td>\n",
       "      <td>Y63</td>\n",
       "      <td>QF7</td>\n",
       "      <td>53.399947</td>\n",
       "      <td>-1.440936</td>\n",
       "      <td>Sheffield Health &amp; Social Care NHS Foundation ...</td>\n",
       "      <td>22/23 Approaching Standards</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS SOUTH YORKSHIRE INTEGRATED CARE BOARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>TAJ</td>\n",
       "      <td>BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST</td>\n",
       "      <td>Y60</td>\n",
       "      <td>QUA</td>\n",
       "      <td>52.508420</td>\n",
       "      <td>-2.084500</td>\n",
       "      <td>Black Country Healthcare NHS Foundation Trust</td>\n",
       "      <td>22/23 Approaching Standards</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>NHS Trust</td>\n",
       "      <td>NHS BLACK COUNTRY INTEGRATED CARE BOARD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>213 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Code                                Organisation Name_x National Grouping  \\\n",
       "0    R0A         MANCHESTER UNIVERSITY NHS FOUNDATION TRUST               Y62   \n",
       "1    R0B  SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...               Y63   \n",
       "2    R0D   UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST               Y58   \n",
       "3    R1A  HEREFORDSHIRE AND WORCESTERSHIRE HEALTH AND CA...               Y60   \n",
       "4    R1C                                   SOLENT NHS TRUST               Y59   \n",
       "..   ...                                                ...               ...   \n",
       "208  RYY         KENT COMMUNITY HEALTH NHS FOUNDATION TRUST               Y59   \n",
       "209  TAD        BRADFORD DISTRICT CARE NHS FOUNDATION TRUST               Y63   \n",
       "210  TAF          CAMDEN AND ISLINGTON NHS FOUNDATION TRUST               Y56   \n",
       "211  TAH  SHEFFIELD HEALTH & SOCIAL CARE NHS FOUNDATION ...               Y63   \n",
       "212  TAJ      BLACK COUNTRY HEALTHCARE NHS FOUNDATION TRUST               Y60   \n",
       "\n",
       "    High Level Health Geography        lat      long  \\\n",
       "0                           QOP  53.462440 -2.227710   \n",
       "1                           QHM  54.902770 -1.408705   \n",
       "2                           QVV  50.721389 -1.972792   \n",
       "3                           QGH  52.192162 -2.179038   \n",
       "4                           QRL  50.901607 -1.329799   \n",
       "..                          ...        ...       ...   \n",
       "208                         QKS  51.171653  0.870975   \n",
       "209                         QWO  53.839670 -1.788610   \n",
       "210                         QMJ  51.535866 -0.131679   \n",
       "211                         QF7  53.399947 -1.440936   \n",
       "212                         QUA  52.508420 -2.084500   \n",
       "\n",
       "                                   Organisation Name_y  \\\n",
       "0           MANCHESTER UNIVERSITY NHS FOUNDATION TRUST   \n",
       "1    SOUTH TYNESIDE AND SUNDERLAND NHS FOUNDATION T...   \n",
       "2     UNIVERSITY HOSPITALS DORSET NHS FOUNDATION TRUST   \n",
       "3    Herefordshire and Worcestershire Health and Ca...   \n",
       "4                                     Solent NHS Trust   \n",
       "..                                                 ...   \n",
       "208         Kent Community Health NHS Foundation Trust   \n",
       "209        BRADFORD DISTRICT CARE NHS FOUNDATION TRUST   \n",
       "210          CAMDEN AND ISLINGTON NHS FOUNDATION TRUST   \n",
       "211  Sheffield Health & Social Care NHS Foundation ...   \n",
       "212      Black Country Healthcare NHS Foundation Trust   \n",
       "\n",
       "                          Status Date Of Publication Primary Sector  \\\n",
       "0            22/23 Standards Met          30/06/2023      NHS Trust   \n",
       "1       22/23 Standards Exceeded          15/06/2023      NHS Trust   \n",
       "2    22/23 Approaching Standards          30/06/2023      NHS Trust   \n",
       "3       22/23 Standards Exceeded          05/06/2023      NHS Trust   \n",
       "4            22/23 Standards Met          30/06/2023      NHS Trust   \n",
       "..                           ...                 ...            ...   \n",
       "208          22/23 Standards Met          27/06/2023      NHS Trust   \n",
       "209          22/23 Standards Met          29/06/2023      NHS Trust   \n",
       "210  22/23 Approaching Standards          29/06/2023      NHS Trust   \n",
       "211  22/23 Approaching Standards          30/06/2023      NHS Trust   \n",
       "212  22/23 Approaching Standards          30/06/2023      NHS Trust   \n",
       "\n",
       "     Integrated Care Board (where available, from ODS)  \n",
       "0         NHS GREATER MANCHESTER INTEGRATED CARE BOARD  \n",
       "1    NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...  \n",
       "2                     NHS DORSET INTEGRATED CARE BOARD  \n",
       "3    NHS HEREFORDSHIRE AND WORCESTERSHIRE INTEGRATE...  \n",
       "4    NHS HAMPSHIRE AND ISLE OF WIGHT INTEGRATED CAR...  \n",
       "..                                                 ...  \n",
       "208          NHS KENT AND MEDWAY INTEGRATED CARE BOARD  \n",
       "209           NHS WEST YORKSHIRE INTEGRATED CARE BOARD  \n",
       "210     NHS NORTH CENTRAL LONDON INTEGRATED CARE BOARD  \n",
       "211          NHS SOUTH YORKSHIRE INTEGRATED CARE BOARD  \n",
       "212            NHS BLACK COUNTRY INTEGRATED CARE BOARD  \n",
       "\n",
       "[213 rows x 11 columns]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trusts_dspt = trusts_combined.merge(dspt_snapshot, on = 'Code').reset_index(drop = True)\n",
    "trusts_dspt\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#check which Trusts are not in the dataframe\n",
    "#set(trusts_combined['Code']) - set(trusts_dspt['Code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Organisation Name</th>\n",
       "      <th>National Grouping</th>\n",
       "      <th>High Level Health Geography</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>RVY</td>\n",
       "      <td>SOUTHPORT AND ORMSKIRK HOSPITAL NHS TRUST</td>\n",
       "      <td>Y62</td>\n",
       "      <td>QYG</td>\n",
       "      <td>53.63355</td>\n",
       "      <td>-2.97882</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Code                          Organisation Name National Grouping  \\\n",
       "146  RVY  SOUTHPORT AND ORMSKIRK HOSPITAL NHS TRUST               Y62   \n",
       "\n",
       "    High Level Health Geography       lat     long  \n",
       "146                         QYG  53.63355 -2.97882  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trusts_combined.loc[trusts_combined['Code'] == 'RVY']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Organisation Name</th>\n",
       "      <th>National Grouping</th>\n",
       "      <th>High Level Health Geography</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>RYT</td>\n",
       "      <td>PUBLIC HEALTH WALES NHS TRUST</td>\n",
       "      <td>W00</td>\n",
       "      <td>Q99</td>\n",
       "      <td>51.476792</td>\n",
       "      <td>-3.169914</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Code              Organisation Name National Grouping  \\\n",
       "206  RYT  PUBLIC HEALTH WALES NHS TRUST               W00   \n",
       "\n",
       "    High Level Health Geography        lat      long  \n",
       "206                         Q99  51.476792 -3.169914  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trusts_combined.loc[trusts_combined['Code'] == 'RYT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "trusts_dspt.to_csv('data/DSPT Snapshots/dspt_trusts_snapshot_17_10_23.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ICB23CD</th>\n",
       "      <th>ICB23CDH</th>\n",
       "      <th>ICB23NM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E54000027</td>\n",
       "      <td>QRV</td>\n",
       "      <td>NHS North West London Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E54000028</td>\n",
       "      <td>QMJ</td>\n",
       "      <td>NHS North Central London Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E54000029</td>\n",
       "      <td>QMF</td>\n",
       "      <td>NHS North East London Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E54000030</td>\n",
       "      <td>QKK</td>\n",
       "      <td>NHS South East London Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E54000031</td>\n",
       "      <td>QWE</td>\n",
       "      <td>NHS South West London Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>E54000032</td>\n",
       "      <td>QKS</td>\n",
       "      <td>NHS Kent and Medway Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>E54000034</td>\n",
       "      <td>QNQ</td>\n",
       "      <td>NHS Frimley Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>E54000042</td>\n",
       "      <td>QRL</td>\n",
       "      <td>NHS Hampshire and Isle of Wight Integrated Car...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>E54000044</td>\n",
       "      <td>QU9</td>\n",
       "      <td>NHS Buckinghamshire, Oxfordshire and Berkshire...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>E54000063</td>\n",
       "      <td>QXU</td>\n",
       "      <td>NHS Surrey Heartlands Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>E54000064</td>\n",
       "      <td>QNX</td>\n",
       "      <td>NHS Sussex Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>E54000036</td>\n",
       "      <td>QT6</td>\n",
       "      <td>NHS Cornwall and the Isles of Scilly Integrate...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>E54000037</td>\n",
       "      <td>QJK</td>\n",
       "      <td>NHS Devon Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>E54000038</td>\n",
       "      <td>QSL</td>\n",
       "      <td>NHS Somerset Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>E54000039</td>\n",
       "      <td>QUY</td>\n",
       "      <td>NHS Bristol, North Somerset and South Gloucest...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>E54000040</td>\n",
       "      <td>QOX</td>\n",
       "      <td>NHS Bath and North East Somerset, Swindon and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>E54000041</td>\n",
       "      <td>QVV</td>\n",
       "      <td>NHS Dorset Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>E54000043</td>\n",
       "      <td>QR1</td>\n",
       "      <td>NHS Gloucestershire Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>E54000022</td>\n",
       "      <td>QMM</td>\n",
       "      <td>NHS Norfolk and Waveney Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>E54000023</td>\n",
       "      <td>QJG</td>\n",
       "      <td>NHS Suffolk and North East Essex Integrated Ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>E54000024</td>\n",
       "      <td>QHG</td>\n",
       "      <td>NHS Bedfordshire, Luton and Milton Keynes Inte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>E54000025</td>\n",
       "      <td>QM7</td>\n",
       "      <td>NHS Hertfordshire and West Essex Integrated Ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>E54000026</td>\n",
       "      <td>QH8</td>\n",
       "      <td>NHS Mid and South Essex Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>E54000056</td>\n",
       "      <td>QUE</td>\n",
       "      <td>NHS Cambridgeshire and Peterborough Integrated...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>E54000008</td>\n",
       "      <td>QYG</td>\n",
       "      <td>NHS Cheshire and Merseyside Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>E54000048</td>\n",
       "      <td>QE1</td>\n",
       "      <td>NHS Lancashire and South Cumbria Integrated Ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>E54000057</td>\n",
       "      <td>QOP</td>\n",
       "      <td>NHS Greater Manchester Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>E54000010</td>\n",
       "      <td>QNC</td>\n",
       "      <td>NHS Staffordshire and Stoke-on-Trent Integrate...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>E54000011</td>\n",
       "      <td>QOC</td>\n",
       "      <td>NHS Shropshire, Telford and Wrekin Integrated ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>E54000013</td>\n",
       "      <td>QJM</td>\n",
       "      <td>NHS Lincolnshire Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>E54000015</td>\n",
       "      <td>QK1</td>\n",
       "      <td>NHS Leicester, Leicestershire and Rutland Inte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>E54000018</td>\n",
       "      <td>QWU</td>\n",
       "      <td>NHS Coventry and Warwickshire Integrated Care ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>E54000019</td>\n",
       "      <td>QGH</td>\n",
       "      <td>NHS Herefordshire and Worcestershire Integrate...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>E54000055</td>\n",
       "      <td>QHL</td>\n",
       "      <td>NHS Birmingham and Solihull Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>E54000058</td>\n",
       "      <td>QJ2</td>\n",
       "      <td>NHS Derby and Derbyshire Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>E54000059</td>\n",
       "      <td>QPM</td>\n",
       "      <td>NHS Northamptonshire Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>E54000060</td>\n",
       "      <td>QT1</td>\n",
       "      <td>NHS Nottingham and Nottinghamshire Integrated ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>E54000062</td>\n",
       "      <td>QUA</td>\n",
       "      <td>NHS Black Country Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>E54000050</td>\n",
       "      <td>QHM</td>\n",
       "      <td>NHS North East and North Cumbria Integrated Ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>E54000051</td>\n",
       "      <td>QOQ</td>\n",
       "      <td>NHS Humber and North Yorkshire Integrated Care...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>E54000054</td>\n",
       "      <td>QWO</td>\n",
       "      <td>NHS West Yorkshire Integrated Care Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>E54000061</td>\n",
       "      <td>QF7</td>\n",
       "      <td>NHS South Yorkshire Integrated Care Board</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ICB23CD ICB23CDH                                            ICB23NM\n",
       "0   E54000027      QRV        NHS North West London Integrated Care Board\n",
       "1   E54000028      QMJ     NHS North Central London Integrated Care Board\n",
       "2   E54000029      QMF        NHS North East London Integrated Care Board\n",
       "3   E54000030      QKK        NHS South East London Integrated Care Board\n",
       "4   E54000031      QWE        NHS South West London Integrated Care Board\n",
       "5   E54000032      QKS          NHS Kent and Medway Integrated Care Board\n",
       "6   E54000034      QNQ                  NHS Frimley Integrated Care Board\n",
       "7   E54000042      QRL  NHS Hampshire and Isle of Wight Integrated Car...\n",
       "8   E54000044      QU9  NHS Buckinghamshire, Oxfordshire and Berkshire...\n",
       "9   E54000063      QXU        NHS Surrey Heartlands Integrated Care Board\n",
       "10  E54000064      QNX                   NHS Sussex Integrated Care Board\n",
       "11  E54000036      QT6  NHS Cornwall and the Isles of Scilly Integrate...\n",
       "12  E54000037      QJK                    NHS Devon Integrated Care Board\n",
       "13  E54000038      QSL                 NHS Somerset Integrated Care Board\n",
       "14  E54000039      QUY  NHS Bristol, North Somerset and South Gloucest...\n",
       "15  E54000040      QOX  NHS Bath and North East Somerset, Swindon and ...\n",
       "16  E54000041      QVV                   NHS Dorset Integrated Care Board\n",
       "17  E54000043      QR1          NHS Gloucestershire Integrated Care Board\n",
       "18  E54000022      QMM      NHS Norfolk and Waveney Integrated Care Board\n",
       "19  E54000023      QJG  NHS Suffolk and North East Essex Integrated Ca...\n",
       "20  E54000024      QHG  NHS Bedfordshire, Luton and Milton Keynes Inte...\n",
       "21  E54000025      QM7  NHS Hertfordshire and West Essex Integrated Ca...\n",
       "22  E54000026      QH8      NHS Mid and South Essex Integrated Care Board\n",
       "23  E54000056      QUE  NHS Cambridgeshire and Peterborough Integrated...\n",
       "24  E54000008      QYG  NHS Cheshire and Merseyside Integrated Care Board\n",
       "25  E54000048      QE1  NHS Lancashire and South Cumbria Integrated Ca...\n",
       "26  E54000057      QOP       NHS Greater Manchester Integrated Care Board\n",
       "27  E54000010      QNC  NHS Staffordshire and Stoke-on-Trent Integrate...\n",
       "28  E54000011      QOC  NHS Shropshire, Telford and Wrekin Integrated ...\n",
       "29  E54000013      QJM             NHS Lincolnshire Integrated Care Board\n",
       "30  E54000015      QK1  NHS Leicester, Leicestershire and Rutland Inte...\n",
       "31  E54000018      QWU  NHS Coventry and Warwickshire Integrated Care ...\n",
       "32  E54000019      QGH  NHS Herefordshire and Worcestershire Integrate...\n",
       "33  E54000055      QHL  NHS Birmingham and Solihull Integrated Care Board\n",
       "34  E54000058      QJ2     NHS Derby and Derbyshire Integrated Care Board\n",
       "35  E54000059      QPM         NHS Northamptonshire Integrated Care Board\n",
       "36  E54000060      QT1  NHS Nottingham and Nottinghamshire Integrated ...\n",
       "37  E54000062      QUA            NHS Black Country Integrated Care Board\n",
       "38  E54000050      QHM  NHS North East and North Cumbria Integrated Ca...\n",
       "39  E54000051      QOQ  NHS Humber and North Yorkshire Integrated Care...\n",
       "40  E54000054      QWO           NHS West Yorkshire Integrated Care Board\n",
       "41  E54000061      QF7          NHS South Yorkshire Integrated Care Board"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup_table = pd.read_csv('data/aux/22_23/Sub_ICB_Locations_to_Integrated_Care_Boards_to_NHS_England_(Region)_(April_2023)_Lookup_in_England (1).csv')\n",
    "icb_table = lookup_table.drop_duplicates('ICB23CDH').reset_index(drop=True)[['ICB23CD', 'ICB23CDH', 'ICB23NM']]\n",
    "icb_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ICB23CD</th>\n",
       "      <th>ICB23CDH</th>\n",
       "      <th>ICB23NM</th>\n",
       "      <th>Code</th>\n",
       "      <th>Organisation Name</th>\n",
       "      <th>Status</th>\n",
       "      <th>Date Of Publication</th>\n",
       "      <th>Primary Sector</th>\n",
       "      <th>Integrated Care Board (where available, from ODS)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E54000027</td>\n",
       "      <td>QRV</td>\n",
       "      <td>NHS North West London Integrated Care Board</td>\n",
       "      <td>QRV</td>\n",
       "      <td>NHS NORTH WEST LONDON INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>26/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E54000028</td>\n",
       "      <td>QMJ</td>\n",
       "      <td>NHS North Central London Integrated Care Board</td>\n",
       "      <td>QMJ</td>\n",
       "      <td>NHS NORTH CENTRAL LONDON INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E54000029</td>\n",
       "      <td>QMF</td>\n",
       "      <td>NHS North East London Integrated Care Board</td>\n",
       "      <td>QMF</td>\n",
       "      <td>NHS NORTH EAST LONDON INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E54000030</td>\n",
       "      <td>QKK</td>\n",
       "      <td>NHS South East London Integrated Care Board</td>\n",
       "      <td>QKK</td>\n",
       "      <td>NHS SOUTH EAST LONDON INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E54000031</td>\n",
       "      <td>QWE</td>\n",
       "      <td>NHS South West London Integrated Care Board</td>\n",
       "      <td>QWE</td>\n",
       "      <td>NHS SOUTH WEST LONDON INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>27/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>E54000032</td>\n",
       "      <td>QKS</td>\n",
       "      <td>NHS Kent and Medway Integrated Care Board</td>\n",
       "      <td>QKS</td>\n",
       "      <td>NHS KENT AND MEDWAY INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>18/07/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>E54000034</td>\n",
       "      <td>QNQ</td>\n",
       "      <td>NHS Frimley Integrated Care Board</td>\n",
       "      <td>QNQ</td>\n",
       "      <td>NHS FRIMLEY INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>E54000042</td>\n",
       "      <td>QRL</td>\n",
       "      <td>NHS Hampshire and Isle of Wight Integrated Car...</td>\n",
       "      <td>QRL</td>\n",
       "      <td>NHS HAMPSHIRE AND ISLE OF WIGHT INTEGRATED CAR...</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>E54000044</td>\n",
       "      <td>QU9</td>\n",
       "      <td>NHS Buckinghamshire, Oxfordshire and Berkshire...</td>\n",
       "      <td>QU9</td>\n",
       "      <td>NHS BUCKINGHAMSHIRE, OXFORDSHIRE AND BERKSHIRE...</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>27/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>E54000063</td>\n",
       "      <td>QXU</td>\n",
       "      <td>NHS Surrey Heartlands Integrated Care Board</td>\n",
       "      <td>QXU</td>\n",
       "      <td>NHS SURREY HEARTLANDS INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>E54000064</td>\n",
       "      <td>QNX</td>\n",
       "      <td>NHS Sussex Integrated Care Board</td>\n",
       "      <td>QNX</td>\n",
       "      <td>NHS SUSSEX INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Exceeded</td>\n",
       "      <td>26/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>E54000036</td>\n",
       "      <td>QT6</td>\n",
       "      <td>NHS Cornwall and the Isles of Scilly Integrate...</td>\n",
       "      <td>QT6</td>\n",
       "      <td>NHS CORNWALL AND THE ISLES OF SCILLY INTEGRATE...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>E54000037</td>\n",
       "      <td>QJK</td>\n",
       "      <td>NHS Devon Integrated Care Board</td>\n",
       "      <td>QJK</td>\n",
       "      <td>NHS DEVON INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>28/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>E54000038</td>\n",
       "      <td>QSL</td>\n",
       "      <td>NHS Somerset Integrated Care Board</td>\n",
       "      <td>QSL</td>\n",
       "      <td>NHS SOMERSET INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>E54000039</td>\n",
       "      <td>QUY</td>\n",
       "      <td>NHS Bristol, North Somerset and South Gloucest...</td>\n",
       "      <td>QUY</td>\n",
       "      <td>BRISTOL, NORTH SOMERSET AND SOUTH GLOUCESTERSH...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>E54000040</td>\n",
       "      <td>QOX</td>\n",
       "      <td>NHS Bath and North East Somerset, Swindon and ...</td>\n",
       "      <td>QOX</td>\n",
       "      <td>NHS BATH AND NORTH EAST SOMERSET, SWINDON AND ...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>09/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>E54000041</td>\n",
       "      <td>QVV</td>\n",
       "      <td>NHS Dorset Integrated Care Board</td>\n",
       "      <td>QVV</td>\n",
       "      <td>NHS DORSET INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>20/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>E54000043</td>\n",
       "      <td>QR1</td>\n",
       "      <td>NHS Gloucestershire Integrated Care Board</td>\n",
       "      <td>QR1</td>\n",
       "      <td>NHS GLOUCESTERSHIRE INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>28/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>E54000022</td>\n",
       "      <td>QMM</td>\n",
       "      <td>NHS Norfolk and Waveney Integrated Care Board</td>\n",
       "      <td>QMM</td>\n",
       "      <td>NHS NORFOLK AND WAVENEY INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>26/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>E54000023</td>\n",
       "      <td>QJG</td>\n",
       "      <td>NHS Suffolk and North East Essex Integrated Ca...</td>\n",
       "      <td>QJG</td>\n",
       "      <td>NHS SUFFOLK AND NORTH EAST ESSEX INTEGRATED CA...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>E54000024</td>\n",
       "      <td>QHG</td>\n",
       "      <td>NHS Bedfordshire, Luton and Milton Keynes Inte...</td>\n",
       "      <td>QHG</td>\n",
       "      <td>NHS BEDFORDSHIRE, LUTON AND MILTON KEYNES INTE...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>E54000025</td>\n",
       "      <td>QM7</td>\n",
       "      <td>NHS Hertfordshire and West Essex Integrated Ca...</td>\n",
       "      <td>QM7</td>\n",
       "      <td>NHS HERTFORDSHIRE AND WEST ESSEX INTEGRATED CA...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>E54000026</td>\n",
       "      <td>QH8</td>\n",
       "      <td>NHS Mid and South Essex Integrated Care Board</td>\n",
       "      <td>QH8</td>\n",
       "      <td>NHS MID AND SOUTH ESSEX INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>E54000056</td>\n",
       "      <td>QUE</td>\n",
       "      <td>NHS Cambridgeshire and Peterborough Integrated...</td>\n",
       "      <td>QUE</td>\n",
       "      <td>NHS CAMBRIDGESHIRE AND PETERBOROUGH INTEGRATED...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>E54000008</td>\n",
       "      <td>QYG</td>\n",
       "      <td>NHS Cheshire and Merseyside Integrated Care Board</td>\n",
       "      <td>QYG</td>\n",
       "      <td>NHS CHESHIRE AND MERSEYSIDE INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>E54000048</td>\n",
       "      <td>QE1</td>\n",
       "      <td>NHS Lancashire and South Cumbria Integrated Ca...</td>\n",
       "      <td>QE1</td>\n",
       "      <td>NHS LANCASHIRE AND SOUTH CUMBRIA INTEGRATED CA...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>E54000057</td>\n",
       "      <td>QOP</td>\n",
       "      <td>NHS Greater Manchester Integrated Care Board</td>\n",
       "      <td>QOP</td>\n",
       "      <td>NHS GREATER MANCHESTER INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Approaching Standards</td>\n",
       "      <td>24/07/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>E54000010</td>\n",
       "      <td>QNC</td>\n",
       "      <td>NHS Staffordshire and Stoke-on-Trent Integrate...</td>\n",
       "      <td>QNC</td>\n",
       "      <td>NHS STAFFORDSHIRE AND STOKE-ON-TRENT INTEGRATE...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>E54000011</td>\n",
       "      <td>QOC</td>\n",
       "      <td>NHS Shropshire, Telford and Wrekin Integrated ...</td>\n",
       "      <td>QOC</td>\n",
       "      <td>NHS SHROPSHIRE, TELFORD AND WREKIN INTEGRATED ...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>E54000013</td>\n",
       "      <td>QJM</td>\n",
       "      <td>NHS Lincolnshire Integrated Care Board</td>\n",
       "      <td>QJM</td>\n",
       "      <td>NHS LINCOLNSHIRE INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>27/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>E54000015</td>\n",
       "      <td>QK1</td>\n",
       "      <td>NHS Leicester, Leicestershire and Rutland Inte...</td>\n",
       "      <td>QK1</td>\n",
       "      <td>NHS LEICESTER, LEICESTERSHIRE AND RUTLAND INTE...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>E54000018</td>\n",
       "      <td>QWU</td>\n",
       "      <td>NHS Coventry and Warwickshire Integrated Care ...</td>\n",
       "      <td>QWU</td>\n",
       "      <td>NHS COVENTRY AND WARWICKSHIRE INTEGRATED CARE ...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>E54000019</td>\n",
       "      <td>QGH</td>\n",
       "      <td>NHS Herefordshire and Worcestershire Integrate...</td>\n",
       "      <td>QGH</td>\n",
       "      <td>NHS HEREFORDSHIRE AND WORCESTERSHIRE INTEGRATE...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>E54000055</td>\n",
       "      <td>QHL</td>\n",
       "      <td>NHS Birmingham and Solihull Integrated Care Board</td>\n",
       "      <td>QHL</td>\n",
       "      <td>NHS BIRMINGHAM AND SOLIHULL INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>27/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>E54000058</td>\n",
       "      <td>QJ2</td>\n",
       "      <td>NHS Derby and Derbyshire Integrated Care Board</td>\n",
       "      <td>QJ2</td>\n",
       "      <td>NHS DERBY AND DERBYSHIRE INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>29/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>E54000059</td>\n",
       "      <td>QPM</td>\n",
       "      <td>NHS Northamptonshire Integrated Care Board</td>\n",
       "      <td>QPM</td>\n",
       "      <td>NHS NORTHAMPTONSHIRE INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>28/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>E54000060</td>\n",
       "      <td>QT1</td>\n",
       "      <td>NHS Nottingham and Nottinghamshire Integrated ...</td>\n",
       "      <td>QT1</td>\n",
       "      <td>NHS NOTTINGHAM AND NOTTINGHAMSHIRE INTEGRATED ...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>23/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>E54000062</td>\n",
       "      <td>QUA</td>\n",
       "      <td>NHS Black Country Integrated Care Board</td>\n",
       "      <td>QUA</td>\n",
       "      <td>NHS BLACK COUNTRY INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>27/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>E54000050</td>\n",
       "      <td>QHM</td>\n",
       "      <td>NHS North East and North Cumbria Integrated Ca...</td>\n",
       "      <td>QHM</td>\n",
       "      <td>NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>12/07/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>E54000051</td>\n",
       "      <td>QOQ</td>\n",
       "      <td>NHS Humber and North Yorkshire Integrated Care...</td>\n",
       "      <td>QOQ</td>\n",
       "      <td>NHS HUMBER AND NORTH YORKSHIRE INTEGRATED CARE...</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>28/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>E54000054</td>\n",
       "      <td>QWO</td>\n",
       "      <td>NHS West Yorkshire Integrated Care Board</td>\n",
       "      <td>QWO</td>\n",
       "      <td>NHS WEST YORKSHIRE INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>30/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>E54000061</td>\n",
       "      <td>QF7</td>\n",
       "      <td>NHS South Yorkshire Integrated Care Board</td>\n",
       "      <td>QF7</td>\n",
       "      <td>NHS SOUTH YORKSHIRE INTEGRATED CARE BOARD</td>\n",
       "      <td>22/23 Standards Met</td>\n",
       "      <td>28/06/2023</td>\n",
       "      <td>CCG / Integrated Care Board (ICB)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ICB23CD ICB23CDH                                            ICB23NM  \\\n",
       "0   E54000027      QRV        NHS North West London Integrated Care Board   \n",
       "1   E54000028      QMJ     NHS North Central London Integrated Care Board   \n",
       "2   E54000029      QMF        NHS North East London Integrated Care Board   \n",
       "3   E54000030      QKK        NHS South East London Integrated Care Board   \n",
       "4   E54000031      QWE        NHS South West London Integrated Care Board   \n",
       "5   E54000032      QKS          NHS Kent and Medway Integrated Care Board   \n",
       "6   E54000034      QNQ                  NHS Frimley Integrated Care Board   \n",
       "7   E54000042      QRL  NHS Hampshire and Isle of Wight Integrated Car...   \n",
       "8   E54000044      QU9  NHS Buckinghamshire, Oxfordshire and Berkshire...   \n",
       "9   E54000063      QXU        NHS Surrey Heartlands Integrated Care Board   \n",
       "10  E54000064      QNX                   NHS Sussex Integrated Care Board   \n",
       "11  E54000036      QT6  NHS Cornwall and the Isles of Scilly Integrate...   \n",
       "12  E54000037      QJK                    NHS Devon Integrated Care Board   \n",
       "13  E54000038      QSL                 NHS Somerset Integrated Care Board   \n",
       "14  E54000039      QUY  NHS Bristol, North Somerset and South Gloucest...   \n",
       "15  E54000040      QOX  NHS Bath and North East Somerset, Swindon and ...   \n",
       "16  E54000041      QVV                   NHS Dorset Integrated Care Board   \n",
       "17  E54000043      QR1          NHS Gloucestershire Integrated Care Board   \n",
       "18  E54000022      QMM      NHS Norfolk and Waveney Integrated Care Board   \n",
       "19  E54000023      QJG  NHS Suffolk and North East Essex Integrated Ca...   \n",
       "20  E54000024      QHG  NHS Bedfordshire, Luton and Milton Keynes Inte...   \n",
       "21  E54000025      QM7  NHS Hertfordshire and West Essex Integrated Ca...   \n",
       "22  E54000026      QH8      NHS Mid and South Essex Integrated Care Board   \n",
       "23  E54000056      QUE  NHS Cambridgeshire and Peterborough Integrated...   \n",
       "24  E54000008      QYG  NHS Cheshire and Merseyside Integrated Care Board   \n",
       "25  E54000048      QE1  NHS Lancashire and South Cumbria Integrated Ca...   \n",
       "26  E54000057      QOP       NHS Greater Manchester Integrated Care Board   \n",
       "27  E54000010      QNC  NHS Staffordshire and Stoke-on-Trent Integrate...   \n",
       "28  E54000011      QOC  NHS Shropshire, Telford and Wrekin Integrated ...   \n",
       "29  E54000013      QJM             NHS Lincolnshire Integrated Care Board   \n",
       "30  E54000015      QK1  NHS Leicester, Leicestershire and Rutland Inte...   \n",
       "31  E54000018      QWU  NHS Coventry and Warwickshire Integrated Care ...   \n",
       "32  E54000019      QGH  NHS Herefordshire and Worcestershire Integrate...   \n",
       "33  E54000055      QHL  NHS Birmingham and Solihull Integrated Care Board   \n",
       "34  E54000058      QJ2     NHS Derby and Derbyshire Integrated Care Board   \n",
       "35  E54000059      QPM         NHS Northamptonshire Integrated Care Board   \n",
       "36  E54000060      QT1  NHS Nottingham and Nottinghamshire Integrated ...   \n",
       "37  E54000062      QUA            NHS Black Country Integrated Care Board   \n",
       "38  E54000050      QHM  NHS North East and North Cumbria Integrated Ca...   \n",
       "39  E54000051      QOQ  NHS Humber and North Yorkshire Integrated Care...   \n",
       "40  E54000054      QWO           NHS West Yorkshire Integrated Care Board   \n",
       "41  E54000061      QF7          NHS South Yorkshire Integrated Care Board   \n",
       "\n",
       "   Code                                  Organisation Name  \\\n",
       "0   QRV        NHS NORTH WEST LONDON INTEGRATED CARE BOARD   \n",
       "1   QMJ     NHS NORTH CENTRAL LONDON INTEGRATED CARE BOARD   \n",
       "2   QMF        NHS NORTH EAST LONDON INTEGRATED CARE BOARD   \n",
       "3   QKK        NHS SOUTH EAST LONDON INTEGRATED CARE BOARD   \n",
       "4   QWE        NHS SOUTH WEST LONDON INTEGRATED CARE BOARD   \n",
       "5   QKS          NHS KENT AND MEDWAY INTEGRATED CARE BOARD   \n",
       "6   QNQ                  NHS FRIMLEY INTEGRATED CARE BOARD   \n",
       "7   QRL  NHS HAMPSHIRE AND ISLE OF WIGHT INTEGRATED CAR...   \n",
       "8   QU9  NHS BUCKINGHAMSHIRE, OXFORDSHIRE AND BERKSHIRE...   \n",
       "9   QXU        NHS SURREY HEARTLANDS INTEGRATED CARE BOARD   \n",
       "10  QNX                   NHS SUSSEX INTEGRATED CARE BOARD   \n",
       "11  QT6  NHS CORNWALL AND THE ISLES OF SCILLY INTEGRATE...   \n",
       "12  QJK                    NHS DEVON INTEGRATED CARE BOARD   \n",
       "13  QSL                 NHS SOMERSET INTEGRATED CARE BOARD   \n",
       "14  QUY  BRISTOL, NORTH SOMERSET AND SOUTH GLOUCESTERSH...   \n",
       "15  QOX  NHS BATH AND NORTH EAST SOMERSET, SWINDON AND ...   \n",
       "16  QVV                   NHS DORSET INTEGRATED CARE BOARD   \n",
       "17  QR1          NHS GLOUCESTERSHIRE INTEGRATED CARE BOARD   \n",
       "18  QMM      NHS NORFOLK AND WAVENEY INTEGRATED CARE BOARD   \n",
       "19  QJG  NHS SUFFOLK AND NORTH EAST ESSEX INTEGRATED CA...   \n",
       "20  QHG  NHS BEDFORDSHIRE, LUTON AND MILTON KEYNES INTE...   \n",
       "21  QM7  NHS HERTFORDSHIRE AND WEST ESSEX INTEGRATED CA...   \n",
       "22  QH8      NHS MID AND SOUTH ESSEX INTEGRATED CARE BOARD   \n",
       "23  QUE  NHS CAMBRIDGESHIRE AND PETERBOROUGH INTEGRATED...   \n",
       "24  QYG  NHS CHESHIRE AND MERSEYSIDE INTEGRATED CARE BOARD   \n",
       "25  QE1  NHS LANCASHIRE AND SOUTH CUMBRIA INTEGRATED CA...   \n",
       "26  QOP       NHS GREATER MANCHESTER INTEGRATED CARE BOARD   \n",
       "27  QNC  NHS STAFFORDSHIRE AND STOKE-ON-TRENT INTEGRATE...   \n",
       "28  QOC  NHS SHROPSHIRE, TELFORD AND WREKIN INTEGRATED ...   \n",
       "29  QJM             NHS LINCOLNSHIRE INTEGRATED CARE BOARD   \n",
       "30  QK1  NHS LEICESTER, LEICESTERSHIRE AND RUTLAND INTE...   \n",
       "31  QWU  NHS COVENTRY AND WARWICKSHIRE INTEGRATED CARE ...   \n",
       "32  QGH  NHS HEREFORDSHIRE AND WORCESTERSHIRE INTEGRATE...   \n",
       "33  QHL  NHS BIRMINGHAM AND SOLIHULL INTEGRATED CARE BOARD   \n",
       "34  QJ2     NHS DERBY AND DERBYSHIRE INTEGRATED CARE BOARD   \n",
       "35  QPM         NHS NORTHAMPTONSHIRE INTEGRATED CARE BOARD   \n",
       "36  QT1  NHS NOTTINGHAM AND NOTTINGHAMSHIRE INTEGRATED ...   \n",
       "37  QUA            NHS BLACK COUNTRY INTEGRATED CARE BOARD   \n",
       "38  QHM  NHS NORTH EAST AND NORTH CUMBRIA INTEGRATED CA...   \n",
       "39  QOQ  NHS HUMBER AND NORTH YORKSHIRE INTEGRATED CARE...   \n",
       "40  QWO           NHS WEST YORKSHIRE INTEGRATED CARE BOARD   \n",
       "41  QF7          NHS SOUTH YORKSHIRE INTEGRATED CARE BOARD   \n",
       "\n",
       "                         Status Date Of Publication  \\\n",
       "0      22/23 Standards Exceeded          26/06/2023   \n",
       "1           22/23 Standards Met          30/06/2023   \n",
       "2      22/23 Standards Exceeded          30/06/2023   \n",
       "3      22/23 Standards Exceeded          30/06/2023   \n",
       "4           22/23 Standards Met          27/06/2023   \n",
       "5           22/23 Standards Met          18/07/2023   \n",
       "6           22/23 Standards Met          30/06/2023   \n",
       "7      22/23 Standards Exceeded          30/06/2023   \n",
       "8      22/23 Standards Exceeded          27/06/2023   \n",
       "9      22/23 Standards Exceeded          29/06/2023   \n",
       "10     22/23 Standards Exceeded          26/06/2023   \n",
       "11          22/23 Standards Met          30/06/2023   \n",
       "12          22/23 Standards Met          28/06/2023   \n",
       "13          22/23 Standards Met          30/06/2023   \n",
       "14          22/23 Standards Met          30/06/2023   \n",
       "15          22/23 Standards Met          09/06/2023   \n",
       "16          22/23 Standards Met          20/06/2023   \n",
       "17          22/23 Standards Met          28/06/2023   \n",
       "18          22/23 Standards Met          26/06/2023   \n",
       "19          22/23 Standards Met          29/06/2023   \n",
       "20          22/23 Standards Met          30/06/2023   \n",
       "21          22/23 Standards Met          29/06/2023   \n",
       "22          22/23 Standards Met          29/06/2023   \n",
       "23          22/23 Standards Met          29/06/2023   \n",
       "24          22/23 Standards Met          30/06/2023   \n",
       "25          22/23 Standards Met          30/06/2023   \n",
       "26  22/23 Approaching Standards          24/07/2023   \n",
       "27          22/23 Standards Met          30/06/2023   \n",
       "28          22/23 Standards Met          30/06/2023   \n",
       "29          22/23 Standards Met          27/06/2023   \n",
       "30          22/23 Standards Met          29/06/2023   \n",
       "31          22/23 Standards Met          29/06/2023   \n",
       "32          22/23 Standards Met          30/06/2023   \n",
       "33          22/23 Standards Met          27/06/2023   \n",
       "34          22/23 Standards Met          29/06/2023   \n",
       "35          22/23 Standards Met          28/06/2023   \n",
       "36          22/23 Standards Met          23/06/2023   \n",
       "37          22/23 Standards Met          27/06/2023   \n",
       "38          22/23 Standards Met          12/07/2023   \n",
       "39          22/23 Standards Met          28/06/2023   \n",
       "40          22/23 Standards Met          30/06/2023   \n",
       "41          22/23 Standards Met          28/06/2023   \n",
       "\n",
       "                       Primary Sector  \\\n",
       "0   CCG / Integrated Care Board (ICB)   \n",
       "1   CCG / Integrated Care Board (ICB)   \n",
       "2   CCG / Integrated Care Board (ICB)   \n",
       "3   CCG / Integrated Care Board (ICB)   \n",
       "4   CCG / Integrated Care Board (ICB)   \n",
       "5   CCG / Integrated Care Board (ICB)   \n",
       "6   CCG / Integrated Care Board (ICB)   \n",
       "7   CCG / Integrated Care Board (ICB)   \n",
       "8   CCG / Integrated Care Board (ICB)   \n",
       "9   CCG / Integrated Care Board (ICB)   \n",
       "10  CCG / Integrated Care Board (ICB)   \n",
       "11  CCG / Integrated Care Board (ICB)   \n",
       "12  CCG / Integrated Care Board (ICB)   \n",
       "13  CCG / Integrated Care Board (ICB)   \n",
       "14  CCG / Integrated Care Board (ICB)   \n",
       "15  CCG / Integrated Care Board (ICB)   \n",
       "16  CCG / Integrated Care Board (ICB)   \n",
       "17  CCG / Integrated Care Board (ICB)   \n",
       "18  CCG / Integrated Care Board (ICB)   \n",
       "19  CCG / Integrated Care Board (ICB)   \n",
       "20  CCG / Integrated Care Board (ICB)   \n",
       "21  CCG / Integrated Care Board (ICB)   \n",
       "22  CCG / Integrated Care Board (ICB)   \n",
       "23  CCG / Integrated Care Board (ICB)   \n",
       "24  CCG / Integrated Care Board (ICB)   \n",
       "25  CCG / Integrated Care Board (ICB)   \n",
       "26  CCG / Integrated Care Board (ICB)   \n",
       "27  CCG / Integrated Care Board (ICB)   \n",
       "28  CCG / Integrated Care Board (ICB)   \n",
       "29  CCG / Integrated Care Board (ICB)   \n",
       "30  CCG / Integrated Care Board (ICB)   \n",
       "31  CCG / Integrated Care Board (ICB)   \n",
       "32  CCG / Integrated Care Board (ICB)   \n",
       "33  CCG / Integrated Care Board (ICB)   \n",
       "34  CCG / Integrated Care Board (ICB)   \n",
       "35  CCG / Integrated Care Board (ICB)   \n",
       "36  CCG / Integrated Care Board (ICB)   \n",
       "37  CCG / Integrated Care Board (ICB)   \n",
       "38  CCG / Integrated Care Board (ICB)   \n",
       "39  CCG / Integrated Care Board (ICB)   \n",
       "40  CCG / Integrated Care Board (ICB)   \n",
       "41  CCG / Integrated Care Board (ICB)   \n",
       "\n",
       "   Integrated Care Board (where available, from ODS)  \n",
       "0                                                NaN  \n",
       "1                                                NaN  \n",
       "2                                                NaN  \n",
       "3                                                NaN  \n",
       "4                                                NaN  \n",
       "5                                                NaN  \n",
       "6                                                NaN  \n",
       "7                                                NaN  \n",
       "8                                                NaN  \n",
       "9                                                NaN  \n",
       "10                                               NaN  \n",
       "11                                               NaN  \n",
       "12                                               NaN  \n",
       "13                                               NaN  \n",
       "14                                               NaN  \n",
       "15                                               NaN  \n",
       "16                                               NaN  \n",
       "17                                               NaN  \n",
       "18                                               NaN  \n",
       "19                                               NaN  \n",
       "20                                               NaN  \n",
       "21                                               NaN  \n",
       "22                                               NaN  \n",
       "23                                               NaN  \n",
       "24                                               NaN  \n",
       "25                                               NaN  \n",
       "26                                               NaN  \n",
       "27                                               NaN  \n",
       "28                                               NaN  \n",
       "29                                               NaN  \n",
       "30                                               NaN  \n",
       "31                                               NaN  \n",
       "32                                               NaN  \n",
       "33                                               NaN  \n",
       "34                                               NaN  \n",
       "35                                               NaN  \n",
       "36                                               NaN  \n",
       "37                                               NaN  \n",
       "38                                               NaN  \n",
       "39                                               NaN  \n",
       "40                                               NaN  \n",
       "41                                               NaN  "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "icb_dspt = icb_table.merge(dspt_snapshot, left_on = 'ICB23CDH', right_on = 'Code')\n",
    "icb_dspt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "icb_dspt.to_csv('data/Dspt Snapshots/ICB_dspt_snapshot_17_10_23.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "397704579725e15f5c7cb49fe5f0341eb7531c82d19f2c29d197e8b64ab5776b"
  },
  "kernelspec": {
   "display_name": "Python 3.9.18 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}