Get Daily Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import urllib.request\n",
    "import time\n",
    "from pathlib import Path\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "import csv\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Functions\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# open a file and read the last line, so we can calculate changing values \n",
    "def get_last_line (filename):\n",
    "    data = []\n",
    "    data_directory = Path(\"data/processed/\")\n",
    "    file_to_open = data_directory / filename\n",
    "\n",
    "    with open(file_to_open) as csv_file:\n",
    "        csv_reader = csv.reader(csv_file, delimiter=',')\n",
    "        line_count = 0\n",
    "        for row in csv_reader:\n",
    "            data.append(row)\n",
    "    return data[-1]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# open a file for writing and add a line to the end of it\n",
    "def write_to_file(filename, values):\n",
    "    #print (filename)\n",
    "    #print (values)\n",
    "\n",
    "    #hack to deal with empty strings\n",
    "    out_list = []\n",
    "    for x in values:\n",
    "        if x == \" \":\n",
    "            out_list.append(\"\")\n",
    "        else:\n",
    "            out_list.append (x)\n",
    "    \n",
    "    data_directory = Path(\"data/processed/\")\n",
    "    file_to_write = data_directory / filename\n",
    "    \n",
    "    with open(file_to_write, 'a') as f:\n",
    "        writer = csv.writer(f)\n",
    "        #writer.writerow(values)\n",
    "        writer.writerow(out_list)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the current date and yesterday as strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "#set two global variables for later (re)use\n",
    "now = datetime.now()\n",
    "cur_dat = now.strftime(\"%d-%b-%Y\")\n",
    "\n",
    "#today_date = datetime.strptime(cur_dat, '%d-%b-%Y')\n",
    "yd = datetime.today() - timedelta(days=1)\n",
    "yest_dat = yd.strftime(\"%d-%b-%Y\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the external data for today\n",
    "Get the text of our target page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.gov.scot/publications/coronavirus-covid-19-tests-and-cases-in-scotland/'\n",
    "response = requests.get(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Where will we store the file, giving it today's date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_folder = Path(\"data/webpages/\")\n",
    "\n",
    "filename = cur_dat + \".html\"\n",
    "file_to_open = data_folder / filename\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write the file for safe keeping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(file_to_open, 'w') as my_data_file:\n",
    "    my_data_file.write(response.text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Scraping the content of what we've grabbed \n",
    "Now we use Beautiful Soup to hunt through the text of the source webpage (still in memory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "soup = BeautifulSoup(response.text, 'html.parser')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = soup.find(id='preamble')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Uncomment the next line if you want to see the HTML of the section we are interested in\n",
    "\n",
    "#print(results.prettify())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find all the non-tabular text entries for data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "mybody = results.find(\"div\", {\"class\": \"body-content publication-body\"})\n",
    "#print (mybody)\n",
    "first_txt = mybody.find('p')\n",
    "subject = str(first_txt)\n",
    "\n",
    "#find the total number tested\n",
    "total_tested = int(\"\".join (re.findall(r\"([\\d*])\",subject)))\n",
    "\n",
    "#Now find all the pieces of text buried in lists\n",
    "firstH3 = mybody.find('h3') # Start here\n",
    "uls = []\n",
    "\n",
    "for nextSibling in firstH3.findNextSiblings():\n",
    "    if nextSibling.name == 'h2':\n",
    "        break\n",
    "    if nextSibling.name == 'ul':\n",
    "        uls.append(nextSibling)\n",
    "        \n",
    "lis = []\n",
    "\n",
    "for ul in uls:\n",
    "    for li in ul.findAll('li'):\n",
    "        if li.find('ul'):\n",
    "            break\n",
    "        lis.append(li)\n",
    "\n",
    "total_neg = \"\".join (re.findall(r\"([\\d*])\", lis[0].text))\n",
    "total_pos = \"\".join (re.findall(r\"([\\d*])\", lis[1].text))\n",
    "total_dec = \"\".join (re.findall(r\"([\\d*])\", lis[2].text)) #broken on 19 Apr\n",
    "\n",
    "#fix added 19 Apr then removed 21 Apr\n",
    "#dec_list = re.findall('[0-9]+', lis[2].text)\n",
    "#tot_dec = dec_list[0]\n",
    "\n",
    "\n",
    "icus = re.findall('[0-9]+', lis[3].text)\n",
    "icu_tot = icus[0]\n",
    "icu_pos = icus[1]\n",
    "\n",
    "hosp = re.findall('[0-9]+', lis[4].text.replace(\",\",\"\"))\n",
    "hosp_tot = hosp[0]\n",
    "\n",
    "calls = re.findall('[0-9]+', lis[5].text.replace(\",\",\"\"))\n",
    "one_tot = calls[0]\n",
    "cv_tot = calls[2]\n",
    "\n",
    "ambs = re.findall('[0-9]+', lis[6].text.replace(\",\",\"\"))\n",
    "sas_tot = ambs[0]\n",
    "cv_amb_tot = ambs[1]\n",
    "sas_uplifts = ambs[3]\n",
    "\n",
    "#Give up on this for now - this figure is sometimes in, sometimes out of the list\n",
    "#---------------------------------------------------\n",
    "#staff_ab_list = re.findall('[0-9]+', lis[8].text.replace(\",\",\"\"))\n",
    "#staff_abs = staff_ab_list[1]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Uncomment this block to show values found. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Tested: 51499\n",
      "Total negative tests: 40728\n",
      "Total positive tests: 10721\n",
      "Total deceased: 1332\n",
      "Total in ICU: 126\n",
      "ICU positives: 19\n",
      "Hospitalised: 1754\n",
      "Calls to 111: 3460\n",
      "Calls to CV Hotline: 495\n",
      "SAS Attendances: 1531\n",
      "SAS Attendances at suspected CV19: 343\n",
      "SAS CV19 uplifts: 225\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print (f\"Total Tested: {total_tested}\")\n",
    "print (f\"Total negative tests: {total_neg}\")\n",
    "print (f\"Total positive tests: {total_pos}\")\n",
    "print (f\"Total deceased: {total_dec}\")\n",
    "print (f\"Total in ICU: {icu_tot}\")\n",
    "print (f\"ICU positives: {icu_pos}\")\n",
    "print (f\"Hospitalised: {hosp_tot}\")\n",
    "print (f\"Calls to 111: {one_tot}\")\n",
    "print (f\"Calls to CV Hotline: {cv_tot}\")\n",
    "print (f\"SAS Attendances: {sas_tot}\")\n",
    "print (f\"SAS Attendances at suspected CV19: {cv_amb_tot}\")\n",
    "print (f\"SAS CV19 uplifts: {sas_uplifts}\")\n",
    "#print (f\"Staff absences: {staff_abs}\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Do the calculations and write all of the above to the right files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "#What were the last data written to scot_tests.csv ?\n",
    "cur_data = get_last_line (\"scot_tests.csv\")\n",
    "#print(cur_data)\n",
    "\n",
    "# prepare values to write\n",
    "cur_tot_tests = int(cur_data[6])\n",
    "cur_neg = int(cur_data[5])\n",
    "cur_pos = int(cur_data[4])\n",
    "\n",
    "new_tests = int(total_tested) -  cur_tot_tests\n",
    "new_neg = int(total_neg) - cur_neg\n",
    "new_pos = int(total_pos) - cur_pos\n",
    "#print (new_tests,new_neg, new_pos)\n",
    "\n",
    "#write values to our file scot_tests.csv\n",
    "out_list = []\n",
    "out_list.extend ([str(cur_dat), new_tests, new_pos, new_neg,total_pos, total_neg, total_tested])\n",
    "write_to_file (\"scot_tests.csv\", out_list )\n",
    "\n",
    "#===========================================\n",
    "\n",
    "#write values to our file regional_deaths.csv\n",
    "out_list = [cur_dat]\n",
    "out_list.extend ([\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\",\"x\"])\n",
    "out_list.extend([total_dec])\n",
    "write_to_file (\"regional_deaths.csv\", out_list )\n",
    "\n",
    "#============================================\n",
    "#write values to our file intensive_care.csv\n",
    "out_list = [cur_dat]\n",
    "out_list.extend ([icu_tot])\n",
    "write_to_file (\"intensive_care.csv\", out_list )\n",
    "\n",
    "#============================================\n",
    "#write values to our file new_daily_cases.csv\n",
    "out_list = [cur_dat]\n",
    "out_list.extend ([new_pos])\n",
    "write_to_file (\"new_daily_cases.csv\", out_list )\n",
    "\n",
    "#============================================\n",
    "#Update our file scot_test_positive_deceased\n",
    "cur_data = get_last_line (\"scot_test_positive_deceased.csv\")\n",
    "day_no = int(cur_data[4]) +1\n",
    "\n",
    "#write values to our file scot_test_positive_deceased.csv\n",
    "out_list = [cur_dat]\n",
    "out_list.extend ([int(total_pos),int(total_dec),int(total_tested)])\n",
    "out_list.extend ([day_no])\n",
    "write_to_file (\"scot_test_positive_deceased.csv\", out_list )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Next find the data in tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a list _tables_ of all tables we find (normally two)\n",
    "tables = mybody.findAll(\"table\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Health Board Cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Work on our first table (i.e. _tables[0]_)\n",
    "hb_list = [] #a list for all health boards\n",
    "\n",
    "for tr in tables[0].find_all('tr')[1:]:\n",
    "    in_list = []\n",
    "    tds = tr.find_all('td')\n",
    "    #print (f\" Cases: {tds[1].text.strip()}, PPl in H:{tds[2].text.strip()}, PPL in ICU: {tds[3].text.strip()}\") \n",
    "    if re.search('[0-9]+', tds[0].text.strip()):\n",
    "        in_list.append(re.findall('[0-9]+', tds[0].text.strip()))\n",
    "    else:\n",
    "        in_list.append(\"\")\n",
    "    \n",
    "    if re.search('[0-9]+', tds[1].text.strip()):\n",
    "        \n",
    "        #change on 20 Apr to include commas in numbers, broke this. Changed 21 Apr\n",
    "        # in_list.append(re.findall('[0-9]+', tds[1].text.strip()))\n",
    "        in_list.append(re.findall('[0-9]+', tds[1].text.replace(\",\",\"\").strip()))\n",
    "    elif re.search('/*', tds[1].text.strip()):\n",
    "        in_list.append(\" \")\n",
    "    else:\n",
    "        in_list.append(\" \")\n",
    "    \n",
    "    if re.search('[0-9]+', tds[2].text.strip()):\n",
    "        in_list.append(re.findall('[0-9]+', tds[2].text.strip()))\n",
    "    elif re.search('/*', tds[2].text.strip()):\n",
    "        in_list.append(\" \")\n",
    "    else:\n",
    "        in_list.append(\" \")\n",
    "    \n",
    "    if re.search('[0-9]+', tds[3].text.strip()):\n",
    "        in_list.append(re.findall('[0-9]+', tds[3].text.strip()))\n",
    "    elif re.search('/*', tds[3].text.strip()):\n",
    "        in_list.append(\" \")\n",
    "    else:\n",
    "        in_list.append(\" \")\n",
    "        \n",
    "    hb_list.append(in_list)\n",
    "\n",
    "#print (hb_list)    \n",
    "\n",
    "#Get data ready to update regional_cases.csv\n",
    "out_list = [cur_dat]\n",
    "for l in hb_list:\n",
    "    out_list.extend (l[1])\n",
    "\n",
    "out_list = out_list[:15] #dump Golden Jubilee, which was a late addition, as we don't use it\n",
    "out_list.extend([total_pos])\n",
    "\n",
    "# Write data to file\n",
    "write_to_file (\"regional_cases.csv\", out_list)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Staff Absences"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Hospitalisations and ICU cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "#We already have all of this in hb_list, created above. We've not used it yet. \n",
    "\n",
    "#Get data ready to update regional_hospitalisations.csv\n",
    "out_list = [cur_dat]\n",
    "for l in hb_list:\n",
    "    out_list.extend (l[2])\n",
    "\n",
    "out_list = out_list[:15] #dump Golden Jubilee as we don't use it\n",
    "out_list.extend([hosp_tot])\n",
    "\n",
    "# Write data to file\n",
    "write_to_file (\"regional_hospitalisations.csv\", out_list)\n",
    "\n",
    "\n",
    "#Get data ready to update regional_icu.csv\n",
    "out_list = [cur_dat]\n",
    "for l in hb_list:\n",
    "    out_list.extend (l[3])\n",
    "\n",
    "out_list = out_list[:15] #dump Golden Jubilee as we don't use it\n",
    "out_list.extend([icu_tot])\n",
    "\n",
    "# Write data to file\n",
    "write_to_file (\"regional_icu.csv\", out_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## THE FOLLOWING IS NO LONGER USED. The table was removed 14 April 2020. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "list index out of range",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-24-f76d3efac85a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m#use the same methods as Health Boards above\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mabsence_list\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0;32mfor\u001b[0m \u001b[0mtr\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mtables\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfind_all\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'tr'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m     \u001b[0min_list\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0mtds\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfind_all\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'td'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIndexError\u001b[0m: list index out of range"
     ]
    }
   ],
   "source": [
    "\n",
    "#use the same methods as Health Boards above\n",
    "absence_list = []\n",
    "for tr in tables[1].find_all('tr')[1:]:\n",
    "    in_list = []\n",
    "    tds = tr.find_all('td')\n",
    "    #print (f\"Health Board: {tds[0].text.strip()},  Yesterday_count: {tds[3].text.strip()}\") \n",
    "    if re.search('Nursing', tds[0].text.strip()):\n",
    "        in_list.append('NMA')\n",
    "    elif re.search('Medical', tds[0].text.strip()):\n",
    "        in_list.append('MDSA')\n",
    "    elif re.search('Other', tds[0].text.strip()):\n",
    "        in_list.append('OSA')\n",
    "    elif re.search('All', tds[0].text.strip()):\n",
    "        in_list.append('ASA')\n",
    "    in_list.append (tds[7].text.strip().replace(\",\",\"\"))\n",
    "    absence_list.append(in_list)\n",
    "#print (absence_list)\n",
    "\n",
    "out_list = [yest_dat]\n",
    "for inner in absence_list:\n",
    "    out_list.extend([inner[1]])\n",
    "\n",
    "# Write data to file\n",
    "write_to_file (\"staff_absences.csv\", out_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To be done\n",
    "Currently we dont capture the data elements below. \n",
    "\n",
    "We need to create a couple of data files, add the previous data, and then create write routines, as we do above, as we do for each days other data items."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# ICU positives: {icu_pos}\n",
    "# Calls to 111: {one_tot}\n",
    "# Calls to CV Hotline: {cv_tot}\n",
    "# SAS Attendances: {sas_tot}\n",
    "# SAS Attendances at suspected CV19: {cv_amb_tot}\n",
    "# SAS CV19 uplifts: {sas_uplifts}\n"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}