coronavirus_covid-19_england_cases_scraper.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Coronavirus (COVID-19) England Cases Scraper\n",
    "## David Beavan @DavidBeavan\n",
    "## Licence: MIT. Sources: see below\n",
    "## Notes\n",
    "* This is liable to break, lots if the official sources change (and they do)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load libraries\n",
    "from pathlib import Path\n",
    "import requests\n",
    "from lxml import etree\n",
    "import re\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import shutil\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetch the raw rest response from listing azure container\n",
    "\n",
    "\n",
    "def blob_container_list_rest_response(blob_service_endpoint, container_name):\n",
    "    base_uri = blob_service_endpoint + container_name\n",
    "    uri = base_uri + '?restype=container&comp=list'\n",
    "    r = requests.get(uri)\n",
    "\n",
    "    return r.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List all blobs in azure container\n",
    "\n",
    "\n",
    "def blob_container_list(blob_service_endpoint, container_name):\n",
    "    content = blob_container_list_rest_response(\n",
    "        blob_service_endpoint, container_name)\n",
    "    root = etree.XML(content)\n",
    "\n",
    "    blobs = []\n",
    "\n",
    "    for name in root.xpath('/EnumerationResults/Blobs/Blob/Name'):\n",
    "        blobs.append(blob_service_endpoint + container_name + name.text)\n",
    "\n",
    "    return blobs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return most recent blob given dated list\n",
    "\n",
    "\n",
    "def most_recent_data(blobs):\n",
    "    data_blobs = [blob for blob in blobs if 'data_' in blob]\n",
    "\n",
    "    max_date = 0\n",
    "\n",
    "    for blob in data_blobs:\n",
    "        date = re.match(r'.*?data_(\\d+)\\.json', blob)[1]\n",
    "        date = int(date)\n",
    "\n",
    "        if date > max_date:\n",
    "            max_date = date\n",
    "            max_blob = blob\n",
    "\n",
    "    return max_blob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get uri of most recent PHE Covid-19 data\n",
    "\n",
    "blobs = blob_container_list(\n",
    "    'https://publicdashacc.blob.core.windows.net/',\n",
    "    'publicdata/')\n",
    "most_recent_data_uri = most_recent_data(blobs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preview data\n",
    "# most_recent_data_uri"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prep data\n",
    "# Data is not packaged with code, Will download from sources and save for future runs\n",
    "data_base_dir = Path('data/secondary_sources')\n",
    "data_base_dir.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "data_sub_dir = data_base_dir.joinpath('utla_cases_table')\n",
    "data_sub_dir.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "utla_cases_file = data_sub_dir.joinpath('utla_cases.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetch most recent cases data\n",
    "r = requests.get(most_recent_data_uri)\n",
    "# Parse as json\n",
    "content = json.loads(r.content)\n",
    "# select utla cases\n",
    "utlas = content['utlas']\n",
    "\n",
    "utla_cases_df = pd.DataFrame()\n",
    "\n",
    "# Go through each area and output its cases\n",
    "for key, value in utlas.items():\n",
    "    entry = {}\n",
    "\n",
    "    # Select area name\n",
    "    entry['GSS_NM'] = value['name']['value']\n",
    "\n",
    "    # Select each daily report\n",
    "    for k in value['dailyTotalConfirmedCases']:\n",
    "        entry[k['date']] = int(k['value'])\n",
    "\n",
    "    # Insert into a single row dataframe\n",
    "    mini_df = pd.DataFrame(entry, index=[key])\n",
    "\n",
    "    # Merge new single row to list\n",
    "    utla_cases_df = pd.concat([utla_cases_df, mini_df], axis=0)\n",
    "\n",
    "utla_cases_df.index.name = 'utla'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Put date columns in chronological order\n",
    "cols = utla_cases_df.columns.tolist()\n",
    "\n",
    "non_date_cols = cols[:1]\n",
    "date_cols = cols[1:]\n",
    "\n",
    "utla_cases_non_date_cols_df = utla_cases_df[non_date_cols]\n",
    "\n",
    "date_cols.sort()\n",
    "cols = non_date_cols + date_cols\n",
    "utla_cases_date_cols_df = utla_cases_df[date_cols]\n",
    "\n",
    "\n",
    "# Insert new columns even if no case reports were mad eon tthat day (e.g.\n",
    "# early on in the pandemic)\n",
    "min_date = min(date_cols)\n",
    "max_date = max(date_cols)\n",
    "date_range = pd.date_range(min_date, max_date).strftime('%Y-%m-%d')\n",
    "utla_cases_date_cols_df = utla_cases_date_cols_df.reindex(columns=date_range)\n",
    "\n",
    "# Forward fill cases, duplicating case numbers if no new reports were made to made a full, non-sparse dataframe\n",
    "utla_cases_df = pd.concat(\n",
    "    [utla_cases_non_date_cols_df, utla_cases_date_cols_df.ffill(axis=1)], axis=1)\n",
    "\n",
    "# Start all cases at zero, unless otherwise given\n",
    "utla_cases_df.fillna(0, inplace=True)\n",
    "\n",
    "# Set cases back to int from float, as int could not have handeled the NaNs we just removed\n",
    "for col in date_range:\n",
    "    utla_cases_df = utla_cases_df.astype({col: int})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>GSS_NM</th>\n",
       "      <th>2020-01-30</th>\n",
       "      <th>2020-01-31</th>\n",
       "      <th>2020-02-01</th>\n",
       "      <th>2020-02-02</th>\n",
       "      <th>2020-02-03</th>\n",
       "      <th>2020-02-04</th>\n",
       "      <th>2020-02-05</th>\n",
       "      <th>2020-02-06</th>\n",
       "      <th>2020-02-07</th>\n",
       "      <th>...</th>\n",
       "      <th>2020-04-30</th>\n",
       "      <th>2020-05-01</th>\n",
       "      <th>2020-05-02</th>\n",
       "      <th>2020-05-03</th>\n",
       "      <th>2020-05-04</th>\n",
       "      <th>2020-05-05</th>\n",
       "      <th>2020-05-06</th>\n",
       "      <th>2020-05-07</th>\n",
       "      <th>2020-05-08</th>\n",
       "      <th>2020-05-09</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>utla</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000002</th>\n",
       "      <td>Barking and Dagenham</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>454</td>\n",
       "      <td>459</td>\n",
       "      <td>460</td>\n",
       "      <td>462</td>\n",
       "      <td>465</td>\n",
       "      <td>469</td>\n",
       "      <td>471</td>\n",
       "      <td>476</td>\n",
       "      <td>476</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000003</th>\n",
       "      <td>Barnet</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1217</td>\n",
       "      <td>1221</td>\n",
       "      <td>1225</td>\n",
       "      <td>1229</td>\n",
       "      <td>1236</td>\n",
       "      <td>1241</td>\n",
       "      <td>1247</td>\n",
       "      <td>1250</td>\n",
       "      <td>1250</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E08000016</th>\n",
       "      <td>Barnsley</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>684</td>\n",
       "      <td>699</td>\n",
       "      <td>717</td>\n",
       "      <td>732</td>\n",
       "      <td>744</td>\n",
       "      <td>751</td>\n",
       "      <td>754</td>\n",
       "      <td>757</td>\n",
       "      <td>757</td>\n",
       "      <td>757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E06000022</th>\n",
       "      <td>Bath and North East Somerset</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>212</td>\n",
       "      <td>215</td>\n",
       "      <td>217</td>\n",
       "      <td>219</td>\n",
       "      <td>219</td>\n",
       "      <td>220</td>\n",
       "      <td>220</td>\n",
       "      <td>222</td>\n",
       "      <td>222</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E06000055</th>\n",
       "      <td>Bedford</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>485</td>\n",
       "      <td>487</td>\n",
       "      <td>492</td>\n",
       "      <td>497</td>\n",
       "      <td>500</td>\n",
       "      <td>501</td>\n",
       "      <td>501</td>\n",
       "      <td>501</td>\n",
       "      <td>501</td>\n",
       "      <td>501</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 102 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 GSS_NM  2020-01-30  2020-01-31  2020-02-01  \\\n",
       "utla                                                                          \n",
       "E09000002          Barking and Dagenham           0           0           0   \n",
       "E09000003                        Barnet           0           0           0   \n",
       "E08000016                      Barnsley           0           0           0   \n",
       "E06000022  Bath and North East Somerset           0           0           0   \n",
       "E06000055                       Bedford           0           0           0   \n",
       "\n",
       "           2020-02-02  2020-02-03  2020-02-04  2020-02-05  2020-02-06  \\\n",
       "utla                                                                    \n",
       "E09000002           0           0           0           0           0   \n",
       "E09000003           0           0           0           0           0   \n",
       "E08000016           0           1           1           1           1   \n",
       "E06000022           0           0           0           0           0   \n",
       "E06000055           0           0           0           0           0   \n",
       "\n",
       "           2020-02-07  ...  2020-04-30  2020-05-01  2020-05-02  2020-05-03  \\\n",
       "utla                   ...                                                   \n",
       "E09000002           0  ...         454         459         460         462   \n",
       "E09000003           0  ...        1217        1221        1225        1229   \n",
       "E08000016           1  ...         684         699         717         732   \n",
       "E06000022           0  ...         212         215         217         219   \n",
       "E06000055           0  ...         485         487         492         497   \n",
       "\n",
       "           2020-05-04  2020-05-05  2020-05-06  2020-05-07  2020-05-08  \\\n",
       "utla                                                                    \n",
       "E09000002         465         469         471         476         476   \n",
       "E09000003        1236        1241        1247        1250        1250   \n",
       "E08000016         744         751         754         757         757   \n",
       "E06000022         219         220         220         222         222   \n",
       "E06000055         500         501         501         501         501   \n",
       "\n",
       "           2020-05-09  \n",
       "utla                   \n",
       "E09000002         476  \n",
       "E09000003        1250  \n",
       "E08000016         757  \n",
       "E06000022         222  \n",
       "E06000055         501  \n",
       "\n",
       "[5 rows x 102 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Preview data\n",
    "utla_cases_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GSS_NM        Barking and DagenhamBarnetBarnsleyBath and Nor...\n",
       "2020-01-30                                                    1\n",
       "2020-01-31                                                    2\n",
       "2020-02-01                                                    2\n",
       "2020-02-02                                                    2\n",
       "                                    ...                        \n",
       "2020-05-05                                               127861\n",
       "2020-05-06                                               129007\n",
       "2020-05-07                                               129952\n",
       "2020-05-08                                               130280\n",
       "2020-05-09                                               130325\n",
       "Length: 102, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Preview data\n",
    "utla_cases_df.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PosixPath('data/secondary_sources_bak/secondary_sources_bak_2020-05-10')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Save data\n",
    "utla_cases_df.to_csv(utla_cases_file)\n",
    "\n",
    "# Delete old backup\n",
    "backup_dir = Path('data/secondary_sources_bak/secondary_sources_bak_' +\n",
    "                  datetime.date.today().isoformat())\n",
    "if backup_dir.exists():\n",
    "    shutil.rmtree(backup_dir)\n",
    "\n",
    "# Make new backup\n",
    "shutil.copytree(Path('data/secondary_sources'), backup_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}