python/nhs_sitreps.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NHS SitReps\n",
    "\n",
    "Created by Michael George (AKA Logiqx)\n",
    "\n",
    "Website: https://logiqx.github.io/covid-stats/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports\n",
    "\n",
    "Standard python libraries plus determination of projdir, basic printable class, etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "import csv\n",
    "from xlrd import open_workbook, xldate_as_tuple\n",
    "\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as tck\n",
    "\n",
    "import common_core\n",
    "import nhs_core\n",
    "import nhs_download"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Python 3.9 Hack\n",
    "\n",
    "Avoid `AttributeError: 'ElementTree' object has no attribute 'getiterator'`\n",
    "\n",
    "Solution found on [Stack Overflow](https://stackoverflow.com/questions/64264563/attributeerror-elementtree-object-has-no-attribute-getiterator-when-trying)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd\n",
    "\n",
    "xlrd.xlsx.ensure_elementtree_imported(False, None)\n",
    "xlrd.xlsx.Element_has_iter = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configuration\n",
    "\n",
    "Data to download from the NHS statistical work area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "MAX_COLS_WITH_HEADERS = 6\n",
    "\n",
    "REGION_HEADING = \"NHS England Region\"\n",
    "CODE_HEADING = \"Code\"\n",
    "NAME_HEADING = \"Name\"\n",
    "\n",
    "CC_SHEET_NAME = \"Adult critical care\"\n",
    "CC_CHART_TITLE = \"Adult Critical Care Beds\"\n",
    "\n",
    "CC_OPEN_HEADING = \"CC Adult Open\"\n",
    "CC_AVAIL_HEADING = \"CC Adult avail\"\n",
    "CC_OCC_HEADING = \"CC Adult Occ\"\n",
    "\n",
    "CC_OPEN_FIELD = \"cc_open\"\n",
    "CC_OCC_FIELD = \"cc_occupied\"\n",
    "\n",
    "DATE_FIELD = \"date\"\n",
    "\n",
    "Y_MIN_FACTOR = 0\n",
    "Y_MAX_FACTOR = 1.1\n",
    "\n",
    "MIN_YEAR = 2013\n",
    "MAX_YEAR = 2022\n",
    "\n",
    "UEC_SITREPS = \"uec-sitreps\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "legacyTrustCodes = \\\n",
    "{\n",
    "    \"RDD\": {\"name\": \"Basildon And Thurrock University Hospitals NHS Foundation Trust\", \"nhsregion\": \"East of England\"},\n",
    "    \"RC1\": {\"name\": \"Bedford Hospital NHS Trust\", \"nhsregion\": \"East of England\"},\n",
    "    \"RLU\": {\"name\": \"Birmingham Women's NHS Foundation Trust\", \"nhsregion\": \"Midlands\"},\n",
    "    \"RJF\": {\"name\": \"Burton Hospitals NHS Foundation Trust\", \"nhsregion\": \"Midlands\"},\n",
    "    \"RR1\": {\"name\": \"Heart Of England NHS Foundation Trust\", \"nhsregion\": \"Midlands\"},\n",
    "    \"RQQ\": {\"name\": \"Hinchingbrooke Health Care NHS Trust\", \"nhsregion\": \"East of England\"},\n",
    "    \"RGQ\": {\"name\": \"Ipswich Hospital NHS Trust\", \"nhsregion\": \"East of England\"},\n",
    "    \"RQ8\": {\"name\": \"Mid Essex Hospital Services NHS Trust\", \"nhsregion\": \"East of England\"},\n",
    "    \"RW3\": {\"name\": \"Central Manchester University Hospitals NHS Foundation Trust\", \"nhsregion\": \"North West\"},\n",
    "    \"RLN\": {\"name\": \"City Hospitals Sunderland NHS Foundation Trust\", \"nhsregion\": \"North East and Yorkshire\"},\n",
    "    \"RNL\": {\"name\": \"North Cumbria University Hospitals NHS Trust\", \"nhsregion\": \"North East and Yorkshire\"},\n",
    "    \"RQ6\": {\"name\": \"Royal Liverpool And Broadgreen University Hospitals NHS Trust\", \"nhsregion\": \"North West\"},\n",
    "    \"RE9\": {\"name\": \"South Tyneside NHS Foundation Trust\", \"nhsregion\": \"North East and Yorkshire\"},\n",
    "    \"RM2\": {\"name\": \"University Hospital Of South Manchester NHS Foundation Trust\", \"nhsregion\": \"North West\"},\n",
    "    \"RD3\": {\"name\": \"Poole Hospital NHS Foundation Trust\", \"nhsregion\": \"South West\"},\n",
    "    \"RBA\": {\"name\": \"Taunton And Somerset NHS Foundation Trust\", \"nhsregion\": \"South West\"},\n",
    "    \"RDZ\": {\"name\": \"The Royal Bournemouth And Christchurch Hospitals NHS Foundation Trust\", \"nhsregion\": \"South West\"},\n",
    "    \"RA3\": {\"name\": \"Weston Area Health NHS Trust\", \"nhsregion\": \"South West\"},\n",
    "    \n",
    "    \"RFW\": {\"name\": \"West Middlesex University Hospital NHS Trust\", \"nhsregion\": \"London\"},\n",
    "    \"RVL\": {\"name\": \"Barnet And Chase Farm Hospitals NHS Trust\", \"nhsregion\": \"London\"},\n",
    "    \"RC3\": {\"name\": \"Ealing Hospital NHS Trust\", \"nhsregion\": \"London\"},\n",
    "    \"RV8\": {\"name\": \"North West London Hospitals NHS Trust\", \"nhsregion\": \"London\"},\n",
    "    \"RJD\": {\"name\": \"Mid Staffordshire NHS Foundation Trust\", \"nhsregion\": \"Midlands\"},\n",
    "    \"RD7\": {\"name\": \"Heatherwood And Wexham Park Hospitals NHS Foundation Trust\", \"nhsregion\": \"South East\"},\n",
    "    \"RYQ\": {\"name\": \"SOUTH LONDON HEALTHCARE NHS TRUST\", \"nhsregion\": \"London\"},\n",
    "    \"RM4\": {\"name\": \"TRAFFORD HEALTHCARE NHS TRUST\", \"nhsregion\": \"North West\"},\n",
    "    \"RCC\": {\"name\": \"SCARBOROUGH AND NORTH EAST YORKSHIRE HEALTH CARE NHS TRUST\", \"nhsregion\": \"North East and Yorkshire\"},\n",
    "    \"RGC\": {\"name\": \"WHIPPS CROSS UNIVERSITY HOSPITAL NHS TRUST\", \"nhsregion\": \"London\"},\n",
    "    \"RNH\": {\"name\": \"NEWHAM UNIVERSITY HOSPITAL NHS TRUST\", \"nhsregion\": \"London\"},\n",
    "    \"RNJ\": {\"name\": \"BARTS AND THE LONDON NHS TRUST\", \"nhsregion\": \"London\"},\n",
    "    \"5QT\": {\"name\": \"ISLE OF WIGHT NHS PCT\", \"nhsregion\": \"South East\"},\n",
    "    \"RN1\": {\"name\": \"WINCHESTER AND EASTLEIGH HEALTHCARE NHS TRUST\", \"nhsregion\": \"South East\"},\n",
    "    \"5CQ\": {\"name\": \"MILTON KEYNES PCT\", \"nhsregion\": \"East of England\"},\n",
    "    \"5QF\": {\"name\": \"BERKSHIRE WEST PCT\", \"nhsregion\": \"South East\"},\n",
    "    \"RBF\": {\"name\": \"NUFFIELD ORTHOPAEDIC CENTRE NHS TRUST\", \"nhsregion\": \"South East\"}\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   ## Common Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getCsvPath(year, areaType):\n",
    "    \"\"\"Determine CSV path for SitRep\"\"\"\n",
    "\n",
    "    csvPath = os.path.join(common_core.dataDir, nhs_core.NHS_STATISTICS, \"csv\", \"weekly\", \"sitreps\", str(year), areaType)\n",
    "\n",
    "    return csvPath"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   ## XLSX to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def findRowNos(sheet, heading):\n",
    "    '''Find rows with the specified heading.'''\n",
    "\n",
    "    rowNos = []\n",
    "\n",
    "    for rowNo in range(sheet.nrows):\n",
    "        for colNo in range(MAX_COLS_WITH_HEADERS):\n",
    "            cellValue = sheet.cell(rowNo, colNo).value\n",
    "\n",
    "            if isinstance(cellValue, str) and cellValue.startswith(heading):\n",
    "                rowNos.append(rowNo)\n",
    "\n",
    "    return rowNos\n",
    "\n",
    "\n",
    "def findColNos(sheet, rowNo, heading, aliases = []):\n",
    "    '''Determine the columns of headings from the cells in the specified row.'''\n",
    "\n",
    "    colNos = []\n",
    "\n",
    "    for colNo in range(sheet.ncols):\n",
    "        cellValue = sheet.cell(rowNo, colNo).value\n",
    "\n",
    "        if cellValue == heading:\n",
    "            colNos.append(colNo)\n",
    "        else:\n",
    "            for alias in aliases:\n",
    "                if cellValue == alias:\n",
    "                    colNos.append(colNo)\n",
    "                    break\n",
    "\n",
    "    return colNos\n",
    "\n",
    "\n",
    "def findReportDates(sheet, rowNo, dateMode):\n",
    "    \"\"\"Find all of the dates in the sheet\"\"\"\n",
    "    \n",
    "    reportDates = []\n",
    "\n",
    "    for colNo in range(sheet.ncols):\n",
    "        cellValue = sheet.cell(rowNo, colNo).value\n",
    "\n",
    "        if isinstance(cellValue, str):\n",
    "            if len(cellValue) > 0:\n",
    "                try:\n",
    "                    valueLen = len(cellValue)\n",
    "                    if valueLen == 11:\n",
    "                        # e.g. \"01-NOV-2010\"\n",
    "                        dt = datetime.strptime(cellValue, '%d-%b-%Y')\n",
    "                    elif valueLen == 12:\n",
    "                        # e.g. \"04-06-Dec-15\"\n",
    "                        dt = datetime.strptime(cellValue[:2] + cellValue[5:12], '%d-%b-%y')\n",
    "                    elif valueLen == 16:\n",
    "                        # Two possible formats to handle\n",
    "                        if \" \" in cellValue:\n",
    "                            # e.g. \"16 to 18/11/2012\" in 2012\n",
    "                            dt = datetime.strptime(cellValue[:2] + cellValue[8:], '%d/%m/%Y')\n",
    "                        else:\n",
    "                            # e.g. \"30-Jan-01-Feb-15\"\n",
    "                            dt = datetime.strptime(cellValue[:6] + cellValue[-3:], '%d-%b-%y')\n",
    "                    elif valueLen == 19 or valueLen == 21:\n",
    "                        # e.g. \"31-Dec-15-03-Jan-16\" or \"29-Nov-13 - 01-Dec-13\"\n",
    "                        dt = datetime.strptime(cellValue[:9], '%d-%b-%y')\n",
    "                    else:\n",
    "                        raise RuntimeError(f\"Unrecognised date format for {cellValue}\")\n",
    "                except:\n",
    "                    print(f\"Error converting {cellValue} to date\")\n",
    "                    raise\n",
    "\n",
    "                reportDate = dt.strftime(\"%Y-%m-%d\")\n",
    "                reportDates.append(reportDate)\n",
    "        else:\n",
    "            if int(cellValue) > 0:\n",
    "                year, month, day, hour, minute, second = xldate_as_tuple(cellValue, dateMode)\n",
    "                reportDate = f\"{year:04}-{month:02}-{day:02}\"\n",
    "                reportDates.append(reportDate)\n",
    "\n",
    "    return reportDates\n",
    "    \n",
    "\n",
    "def scanSheet(trusts, sheet, dateMode=0, latest=False):\n",
    "    \"\"\"Scan all rows to detect regions, codes and names\"\"\"\n",
    "    \n",
    "    rowNo = findRowNos(sheet, CODE_HEADING)[0]\n",
    "\n",
    "    regionColNos = findColNos(sheet, rowNo, REGION_HEADING)\n",
    "    if len(regionColNos) > 0:\n",
    "        regionColNo = regionColNos[0]\n",
    "    else:\n",
    "        regionColNo = None\n",
    "        \n",
    "    codeColNo = findColNos(sheet, rowNo, CODE_HEADING)[0]\n",
    "    nameColNo = findColNos(sheet, rowNo, NAME_HEADING)[0]\n",
    "\n",
    "    reportDates = findReportDates(sheet, rowNo - 1, dateMode)\n",
    "    \n",
    "    ccOpenColNos = findColNos(sheet, rowNo, CC_OPEN_HEADING, [CC_AVAIL_HEADING, CC_AVAIL_HEADING + \"1\"])\n",
    "    assert len(reportDates) == len(ccOpenColNos), f\"Mismatch of dates and CC beds open - {len(reportDates)} vs {len(ccOpenColNos)}\"\n",
    "\n",
    "    ccOccColNos = findColNos(sheet, rowNo, CC_OCC_HEADING, [CC_OCC_HEADING + \"2\"])\n",
    "    assert len(reportDates) == len(ccOccColNos), f\"Mismatch of dates and CC beds occupied - {len(reportDates)} vs {len(ccOccColNos)}\"\n",
    "\n",
    "    for rowNo in range(rowNo, sheet.nrows):\n",
    "        code = sheet.cell(rowNo, codeColNo).value\n",
    "        \n",
    "        if len(code) == 3:\n",
    "            name = sheet.cell(rowNo, nameColNo).value\n",
    "            if regionColNo is not None:\n",
    "                nhsRegion = sheet.cell(rowNo, regionColNo).value\n",
    "            else:\n",
    "                nhsRegion = None\n",
    "\n",
    "            if code not in trusts:\n",
    "                if latest == True:\n",
    "                    trusts[code] = {\"name\": name, \"nhsregion\": nhsRegion}\n",
    "                else:\n",
    "                    if code in legacyTrustCodes:\n",
    "                        trusts[code] = {\"name\": name, \"nhsregion\": legacyTrustCodes[code][\"nhsregion\"]}\n",
    "                    else:\n",
    "                        print(f\"WARNING: Unrecognised code {code} for {name} - {sheet.cell(rowNo, 1).value}\")\n",
    "                        trusts[code] = {\"name\": name, \"nhsregion\": \"unknown\"}\n",
    "                \n",
    "    return codeColNo, reportDates, ccOpenColNos, ccOccColNos\n",
    "   \n",
    "\n",
    "def saveCsv(year, areaType, areaName, reportDates, ccOpen, ccOcc):\n",
    "    \"\"\"Save area to CSV\"\"\"\n",
    "\n",
    "    # Ensure CSV path exists\n",
    "    csvPath = getCsvPath(year, areaType)\n",
    "    if not os.path.exists(csvPath):\n",
    "        os.makedirs(csvPath)\n",
    "\n",
    "    # Determine safe filename\n",
    "    csvFn = os.path.join(csvPath, common_core.getSafeName(areaName) + \".csv\")\n",
    "\n",
    "    # Save data to CSV\n",
    "    header = f\"{DATE_FIELD},{CC_OPEN_FIELD},{CC_OCC_FIELD}\"\n",
    "    data = np.column_stack((reportDates, ccOpen, ccOcc))   \n",
    "    np.savetxt(csvFn, data, fmt='%s', delimiter=',', header=header, comments='')\n",
    "    \n",
    "    \n",
    "def loadSitRep(trusts, year, fileName, latest = False):\n",
    "    \"\"\"Load SitReps data into trusts\"\"\"\n",
    "\n",
    "    partName = common_core.getPartName(fileName)\n",
    "\n",
    "    print(f\"Processing {year} - {partName}...\")\n",
    "    \n",
    "    workbook = open_workbook(fileName)\n",
    "    \n",
    "    for sheet in workbook.sheets():           \n",
    "        if sheet.name == CC_SHEET_NAME:\n",
    "            codeColNo, reportDates, ccOpenColNos, ccOccColNos = scanSheet(trusts, sheet, workbook.datemode, latest = latest)\n",
    "            \n",
    "            numDates = len(reportDates)\n",
    "            totalOpen = np.zeros(numDates, dtype=int)\n",
    "            totalOcc = np.zeros(numDates, dtype=int)\n",
    "\n",
    "            for nhsRegion in common_core.nhsRegionNames:\n",
    "                regionOpen = np.zeros(numDates, dtype=int)\n",
    "                regionOcc = np.zeros(numDates, dtype=int)\n",
    "                \n",
    "                for rowNo in range(sheet.nrows):\n",
    "                    trustCode = sheet.cell(rowNo, codeColNo).value\n",
    "                    \n",
    "                    if trustCode in trusts:\n",
    "                        trust = trusts[trustCode]\n",
    "\n",
    "                        if trust[\"nhsregion\"] == nhsRegion:\n",
    "                            for i in range(len(ccOpenColNos)):\n",
    "                                ccOpen = sheet.cell(rowNo, ccOpenColNos[i]).value\n",
    "                                if ccOpen not in [\"-\", \"N/A\", \"\"]:\n",
    "                                    ccOpen = int(ccOpen)\n",
    "\n",
    "                                    # Check for ridiculous jumps in beds open - e.g. 18 to 547 for RWW in 2014/15\n",
    "                                    if i > 0 and i < len(ccOpenColNos) - 1:\n",
    "                                        ccOpenPrev = sheet.cell(rowNo, ccOpenColNos[i - 1]).value\n",
    "                                        ccOpenNext = sheet.cell(rowNo, ccOpenColNos[i + 1]).value\n",
    "                                        if ccOpenPrev not in [\"-\", \"N/A\", \"\"] and ccOpenNext not in [\"-\", \"N/A\", \"\"]:\n",
    "                                            ccOpenPrev = int(ccOpenPrev)\n",
    "                                            ccOpenNext = int(ccOpenNext)\n",
    "                                            # Is the number of open beds is greater than the week before and the week after?\n",
    "                                            if ccOpenPrev > 0 and ccOpen > ccOpenPrev * 4 and ccOpen > ccOpenNext * 4:\n",
    "                                                print(f\"WARNING: Patched {trustCode} open in {year} where {ccOpenPrev} -> {ccOpen}\")\n",
    "                                                ccOpen = (ccOpenPrev + ccOpenNext) // 2\n",
    "\n",
    "                                    regionOpen[i] += ccOpen\n",
    "                               \n",
    "                                ccOcc = sheet.cell(rowNo, ccOccColNos[i]).value\n",
    "                                if ccOcc not in [\"-\", \"N/A\", \"\"]:\n",
    "                                    ccOcc = int(ccOcc)\n",
    "                                    regionOcc[i] += ccOcc\n",
    "\n",
    "                                try:\n",
    "                                    # Report exceptionally high occupancy - over 5% capacity\n",
    "                                    # Note: 2016 has a few anomalies but relatively minor data entry issues\n",
    "                                    if year <= 2016 and ccOcc > ccOpen + 24 or year > 2016 and ccOcc > ccOpen * 1.05:\n",
    "                                        # 2017 has massive data issue for St. Barts on 10 Dec 2017 - 490/120 beds occupied\n",
    "                                        if year == 2017 and trustCode == \"R1H\":\n",
    "                                            # Adjust to assume full occupancy - e.g. 120/120 beds\n",
    "                                            regionOcc[i] -= regionOcc[i] - regionOpen[i]\n",
    "                                            print(f\"WARNING: Patched {trustCode} occ in {year} where {ccOcc} > {ccOpen}\")\n",
    "                                        else:\n",
    "                                            print(f\"WARNING: Ignored {trustCode} occ in {year} where {ccOcc} > {ccOpen}\")\n",
    "                                except:\n",
    "                                    pass\n",
    "\n",
    "                totalOpen += regionOpen\n",
    "                totalOcc += regionOcc\n",
    "\n",
    "                saveCsv(year, \"nhsregion\", nhsRegion, reportDates, regionOpen, regionOcc)\n",
    "\n",
    "            # Now do England\n",
    "            \n",
    "            nationOpen = np.zeros(numDates, dtype=int)\n",
    "            nationOcc = np.zeros(numDates, dtype=int)\n",
    "            \n",
    "            for nation in nhs_core.nationNames:\n",
    "                rowNo = findRowNos(sheet, nation.upper())[0]\n",
    "\n",
    "                for i in range(len(ccOpenColNos)):\n",
    "                    cellValue = sheet.cell(rowNo, ccOpenColNos[i]).value\n",
    "                    if cellValue not in [\"-\", \"N/A\"]:\n",
    "                        nationOpen[i] += cellValue\n",
    "\n",
    "                    cellValue = sheet.cell(rowNo, ccOccColNos[i]).value\n",
    "                    if cellValue not in [\"-\", \"N/A\"]:\n",
    "                        nationOcc[i] += cellValue\n",
    "\n",
    "                # 2016 totals for England are incorrect in a few places\n",
    "                # 2017 needed to be patched for St. Barts - 10 Dec 2017\n",
    "                if year > 2017:\n",
    "                    assert (nationOpen == totalOpen).all(), \"Regions do not add up to nation\"\n",
    "                    assert (nationOcc == totalOcc).all(), \"Regions do not add up to nation\"\n",
    "\n",
    "                saveCsv(year, \"nation\", nation, reportDates, totalOpen, totalOcc)\n",
    "\n",
    "\n",
    "def convertSitReps(skipHistory = False):\n",
    "    \"\"\"Process all available SitReps\"\"\"\n",
    "\n",
    "    trusts = {}\n",
    "\n",
    "    partNames = nhs_download.downloadSitReps(skipExisting=False)\n",
    "    for partName in partNames:\n",
    "        year = int(nhs_core.SITREPS_YEAR[:4])\n",
    "        xlsxFn = os.path.join(common_core.dataDir, partName)\n",
    "        loadSitRep(trusts, year, xlsxFn, latest = True)\n",
    "\n",
    "    if not skipHistory:\n",
    "        for sitRepsFile in nhs_download.sitRepsFiles:\n",
    "            for year in range(MAX_YEAR - 1, MIN_YEAR - 1, -1):\n",
    "                xlsxPath = os.path.join(nhs_core.rawPath, sitRepsFile[0], nhs_core.SITREPS_CATEGORY, f\"{year}-{year + 1}\")\n",
    "                for basename in sorted(os.listdir(xlsxPath)):\n",
    "                    if not basename.startswith(\"~$\"):\n",
    "                        fileName = os.path.join(xlsxPath, basename)\n",
    "                        loadSitRep(trusts, year, fileName, latest = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Charts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def loadArea(areaType, areaName):\n",
    "    \"\"\"Load CSV for single area\"\"\"\n",
    "    \n",
    "    data = {}\n",
    "    \n",
    "    for year in range(MIN_YEAR, MAX_YEAR + 1):\n",
    "        csvPath = getCsvPath(year, areaType)\n",
    "        csvFn = os.path.join(csvPath, common_core.getSafeName(areaName) + \".csv\")\n",
    "\n",
    "        try:\n",
    "            with open(csvFn, 'r') as f:\n",
    "                reader = csv.reader(f, delimiter = ',')\n",
    "\n",
    "                dtype = []\n",
    "                colNames = next(reader)\n",
    "\n",
    "                for i in range(len(colNames)):\n",
    "                    colName = colNames[i]\n",
    "                    if colName == DATE_FIELD:\n",
    "                        dtype.append((colName, \"U10\"))\n",
    "                    else:\n",
    "                        dtype.append((colName, \"u4\"))\n",
    "\n",
    "                yearData = np.genfromtxt(f, dtype=dtype, delimiter=\",\")\n",
    "                \n",
    "                data[year] = yearData\n",
    "\n",
    "        except:\n",
    "            print(f\"Failed to load CSV data for {areaName}\")\n",
    "            raise\n",
    "\n",
    "    return data\n",
    "\n",
    "\n",
    "def getStartDay(data):\n",
    "    \"\"\"Determine the earliest date of the year\"\"\"\n",
    "    \n",
    "    minDay = 365\n",
    "    \n",
    "    for year in data:\n",
    "        day = datetime.strptime(data[year][0][\"date\"], '%Y-%m-%d').timetuple().tm_yday\n",
    "        if day < minDay:\n",
    "            minDay = day\n",
    "            \n",
    "    return minDay\n",
    "    \n",
    "\n",
    "def plotRegion(fig, ax, areaName, areaData, minDay, field=CC_OPEN_FIELD):\n",
    "    \"\"\"Plot a single region\"\"\"\n",
    "\n",
    "    if field == CC_OPEN_FIELD:\n",
    "        category = \"Open\"\n",
    "    elif field == CC_OCC_FIELD:\n",
    "        category = \"Occupied\"\n",
    "    else:\n",
    "        category = \"???\"\n",
    "\n",
    "    title = f'{CC_CHART_TITLE} {category} in {areaName} {min(areaData)}-{max(areaData) + 1}'\n",
    "    ax.set_title(title)\n",
    "\n",
    "    ax.set_ylabel('Number of beds')\n",
    "    ax.get_yaxis().set_major_formatter(tck.FuncFormatter(lambda x, p: format(int(x), ',')))\n",
    "\n",
    "    ax.set_prop_cycle(plt.cycler(\"color\", plt.cm.viridis(np.linspace(1, 0, MAX_YEAR - MIN_YEAR + 1))))\n",
    "\n",
    "    minValues = {}\n",
    "    maxValues = {}\n",
    "    xMin = xMax = 0\n",
    "    for year in areaData:\n",
    "\n",
    "        # Calculate the 7 day MA using convolve - n.b. years prior to 2016 only reported Mon-Fri, hence 5 day average\n",
    "        if year >= 2016:\n",
    "            y_points = np.convolve(areaData[year][field], np.ones(7)/7, mode=\"valid\")\n",
    "        else:\n",
    "            y_points = np.convolve(areaData[year][field], np.ones(5)/5, mode=\"valid\")\n",
    "\n",
    "        # Save min and max value\n",
    "        minValues[year] = round(min(y_points))\n",
    "        maxValues[year] = round(max(y_points))\n",
    "\n",
    "        # Calculate x_points using difference between dates\n",
    "        startDate = datetime.strptime(areaData[year][0][DATE_FIELD], '%Y-%m-%d')\n",
    "        offset = startDate.timetuple().tm_yday - minDay\n",
    "        x_points = []\n",
    "        \n",
    "        for currDate in areaData[year][DATE_FIELD]:          \n",
    "            diff = datetime.strptime(currDate, '%Y-%m-%d') - startDate\n",
    "            x_points.append(offset + diff.days)\n",
    "        \n",
    "        # Drop the first and last dates that aren't included in the MA - 3 days or 2 days at each end\n",
    "        if year >= 2016:\n",
    "            x_points = x_points[3:-3]\n",
    "        else:\n",
    "            x_points = x_points[2:-2]\n",
    "\n",
    "        if min(x_points) < xMin:\n",
    "            xMin = min(x_points)\n",
    "        if max(x_points) > xMax:\n",
    "            xMax = max(x_points)\n",
    "\n",
    "        # Plot the result\n",
    "        ax.plot(x_points, y_points, label = f\"Beds {category.lower()} {year}/{year % 100 + 1}\") #, color='green')\n",
    "\n",
    "    ax.set_ylim(ymin=min(minValues.values()) * Y_MIN_FACTOR, ymax=max(maxValues.values()) * Y_MAX_FACTOR)\n",
    "\n",
    "    # Generate date labels\n",
    "    dateLabels = []\n",
    "    minDate = datetime.strptime(f\"{MIN_YEAR}-{minDay}\", '%Y-%j')\n",
    "    for i in range(xMax + 3):\n",
    "        newDate = minDate + timedelta(days=i)\n",
    "        dateLabels.append(newDate.strftime(\"%-d %b\"))\n",
    "\n",
    "    # Update x-axis to use desired interval\n",
    "    ax.set_xlim(xmin=xMin - 2, xmax=xMax + 3)\n",
    "    ax.set_xticks(np.arange(0, len(dateLabels), step=7))\n",
    "    ax.set_xticklabels(dateLabels[::7])\n",
    "    \n",
    "    textStr = 'All plots are shown as a 7 day centered moving average'\n",
    "    textStr += '\\n\\nSitRep reporting periods vary each year, typically starting in Nov or Dec'\n",
    "    ax.text(0.02, 0.95, textStr, transform=ax.transAxes, horizontalalignment='left', verticalalignment='top')\n",
    "\n",
    "    textStr = f'Created {datetime.now().strftime(\"%-d %b %y\")}\\n@Mike_aka_Logiqx'\n",
    "    ax.text(0.98, 0.95, textStr, transform=ax.transAxes, horizontalalignment='right', verticalalignment='top', fontsize='small')\n",
    "\n",
    "    textStr = 'Source: NHS Statistical Work Areas\\nUrgent and Emergency Care Daily Situation Reports'\n",
    "    ax.text(0.98, 0.05, textStr, transform=ax.transAxes, horizontalalignment='right', verticalalignment='bottom', fontsize='small')\n",
    "\n",
    "    textStr = 'Maximum 7d MA\\n'\n",
    "    for year in [*maxValues.keys()][:5]:\n",
    "        textStr += f'\\n{year}/{year % 100 + 1} = {maxValues[year]:,}'\n",
    "    ax.text(0.02, 0.05, textStr, transform=ax.transAxes, horizontalalignment='left', verticalalignment='bottom')\n",
    "    \n",
    "    textStr = '\\n'\n",
    "    for year in [*maxValues.keys()][5:]:\n",
    "        textStr += f'\\n{year}/{year % 100 + 1} = {maxValues[year]:,}'\n",
    "    ax.text(0.13, 0.05, textStr, transform=ax.transAxes, horizontalalignment='left', verticalalignment='bottom')\n",
    "        \n",
    "    ax.legend(loc = 'lower center', borderaxespad = 1.5, ncol=2)\n",
    "    \n",
    "\n",
    "def plotRegions():\n",
    "    \"\"\"Plot all of the regions\"\"\"\n",
    "\n",
    "    data = {}\n",
    "    minDay = 365\n",
    "    \n",
    "    areas = [(\"nation\", nhs_core.nationNames), (\"nhsregion\", common_core.nhsRegionNames)]\n",
    "    \n",
    "    for area in areas:\n",
    "        areaType = area[0]\n",
    "        for areaName in area[1]:\n",
    "            areaData = loadArea(areaType, areaName)\n",
    "        \n",
    "            areaStartDay = getStartDay(areaData)\n",
    "            if areaStartDay < minDay:\n",
    "                minDay = areaStartDay\n",
    "\n",
    "            data[areaName] = areaData\n",
    "        \n",
    "    nhsRegions = [common_core.ENGLAND, \"London\", \"South East\", \"East of England\",\n",
    "         \"Midlands\", \"South West\", \"North West\", \"North East and Yorkshire\"]\n",
    "\n",
    "    for nhsRegion in nhsRegions:\n",
    "        fields = [CC_OPEN_FIELD, CC_OCC_FIELD]\n",
    "        fig, axs = plt.subplots(len(fields), figsize=(16, 12), dpi=100)\n",
    "\n",
    "        for i in range(len(fields)):\n",
    "            field = fields[i]\n",
    "            plotRegion(fig, axs[i], nhsRegion, data[nhsRegion], minDay, field=field)\n",
    "\n",
    "        plt.subplots_adjust(hspace=0.2)\n",
    "        plt.show()\n",
    "\n",
    "        partName = os.path.join(\"docs\", UEC_SITREPS, common_core.getSafeName(nhsRegion) + \".png\")\n",
    "        fileName = os.path.join(common_core.projdir, partName)\n",
    "        \n",
    "        print(f\"Saving {partName}...\")\n",
    "\n",
    "        fig.savefig(fileName, bbox_inches='tight', facecolor='w')\n",
    "        plt.close(fig)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def saveIndexPage():\n",
    "    \"\"\"Save HTML page as index to images\"\"\"\n",
    "\n",
    "    indexPageDetails = \\\n",
    "    {\n",
    "        \"TITLE_TEXT\": \"UEC SitReps\",\n",
    "        \"H1_TEXT\": \"Urgent and Emergency Care Daily Situation Reports\",\n",
    "        \"H2_TEXT\": \"Adult Critical Care Beds Open + Occupied in England\",\n",
    "        \"CUSTOM_CSS\": \"css/custom.css\",\n",
    "        \"FURTHER_HREF\": \".\"\n",
    "    }\n",
    "    fileName = os.path.join(common_core.projdir, \"docs\", UEC_SITREPS, \"gallery.html\")\n",
    "    indexPage = common_core.IndexPage(fileName, indexPageDetails)\n",
    "\n",
    "    areaGroups = \\\n",
    "    [\n",
    "        [\"England\", \"London\"],\n",
    "        [\"South East\", \"East of England\"],\n",
    "        [\"North West\", \"North East and Yorkshire\"],\n",
    "        [\"Midlands\", \"South West\"]\n",
    "    ]\n",
    "\n",
    "    for areaNames in areaGroups:\n",
    "        galleryName = \" + \".join(areaNames)\n",
    "        galleryDetails = \\\n",
    "        {\n",
    "            \"P_TEXT\": galleryName\n",
    "        }\n",
    "        gallery = indexPage.addGallery(galleryName, galleryDetails)\n",
    "\n",
    "        for areaName in areaNames:\n",
    "            fileName = os.path.join(common_core.projdir, \"docs\", UEC_SITREPS, common_core.getSafeName(areaName) + \".png\")\n",
    "\n",
    "            figureDetails = \\\n",
    "            {\n",
    "                \"ALT_TEXT\": areaName,\n",
    "                \"CAPTION_TEXT\": areaName\n",
    "            }\n",
    "            figure = gallery.addFigure(areaName, \".\", fileName, figureDetails)\n",
    "            figure.createThumb(suffix=\"_thumb\")\n",
    "\n",
    "    html = indexPage.saveHtml()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Running Interactively"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Downloading nhs-statistics/raw/weekly/sitreps/2022-2023/UEC-Daily-SitRep-Web-File-Timeseries.xlsx...\n",
      "Processing 2022 - nhs-statistics/raw/weekly/sitreps/2022-2023/UEC-Daily-SitRep-Web-File-Timeseries.xlsx...\n"
     ]
    }
   ],
   "source": [
    "if __name__ == '__main__':\n",
    "\n",
    "    convertSitReps(skipHistory = True)\n",
    "\n",
    "    figures = plotRegions()\n",
    "    \n",
    "    saveIndexPage()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}