publish.ipynb

   1 {
   2  "cells": [
   3   {
   4    "cell_type": "markdown",
   5    "metadata": {
   6     "Collapsed": "false"
   7    },
   8    "source": [
   9     "Data from [European Centre for Disease Prevention and Control](https://www.ecdc.europa.eu/en/publications-data/download-todays-data-geographic-distribution-covid-19-cases-worldwide)"
  10    ]
  11   },
  12   {
  13    "cell_type": "code",
  14    "execution_count": 192,
  15    "metadata": {
  16     "Collapsed": "false"
  17    },
  18    "outputs": [
  19     {
  20      "name": "stdout",
  21      "output_type": "stream",
  22      "text": [
  23       "The sql extension is already loaded. To reload it, use:\n",
  24       "  %reload_ext sql\n"
  25      ]
  26     }
  27    ],
  28    "source": [
  29     "import itertools\n",
  30     "import collections\n",
  31     "import json\n",
  32     "import pandas as pd\n",
  33     "import numpy as np\n",
  34     "from scipy.stats import gmean\n",
  35     "import datetime\n",
  36     "\n",
  37     "import matplotlib as mpl\n",
  38     "import matplotlib.pyplot as plt\n",
  39     "%matplotlib inline\n",
  40     "%load_ext sql"
  41    ]
  42   },
  43   {
  44    "cell_type": "code",
  45    "execution_count": 193,
  46    "metadata": {
  47     "Collapsed": "false"
  48    },
  49    "outputs": [],
  50    "source": [
  51     "connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'"
  52    ]
  53   },
  54   {
  55    "cell_type": "code",
  56    "execution_count": 194,
  57    "metadata": {
  58     "Collapsed": "false"
  59    },
  60    "outputs": [
  61     {
  62      "data": {
  63       "text/plain": [
  64        "'Connected: covid@covid'"
  65       ]
  66      },
  67      "execution_count": 194,
  68      "metadata": {},
  69      "output_type": "execute_result"
  70     }
  71    ],
  72    "source": [
  73     "%sql $connection_string"
  74    ]
  75   },
  76   {
  77    "cell_type": "code",
  78    "execution_count": 195,
  79    "metadata": {
  80     "Collapsed": "false"
  81    },
  82    "outputs": [],
  83    "source": [
  84     "# DEATH_COUNT_THRESHOLD = 10\n",
  85     "COUNTRIES_CORE = tuple('IT DE UK ES IE FR BE'.split())\n",
  86     "# COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()\n",
  87     "# COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()\n",
  88     "# # COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()\n",
  89     "\n",
  90     "# COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA\n",
  91     "#        'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',\n",
  92     "#        'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',\n",
  93     "#        'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']\n",
  94     "# COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))\n",
  95     "# COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))"
  96    ]
  97   },
  98   {
  99    "cell_type": "markdown",
 100    "metadata": {
 101     "Collapsed": "false"
 102    },
 103    "source": [
 104     "# Write results to summary file"
 105    ]
 106   },
 107   {
 108    "cell_type": "code",
 109    "execution_count": 196,
 110    "metadata": {
 111     "Collapsed": "false"
 112    },
 113    "outputs": [
 114     {
 115      "name": "stdout",
 116      "output_type": "stream",
 117      "text": [
 118       " * postgresql://covid:***@localhost/covid\n",
 119       "1 rows affected.\n"
 120      ]
 121     },
 122     {
 123      "data": {
 124       "text/plain": [
 125        "datetime.date(2021, 1, 26)"
 126       ]
 127      },
 128      "execution_count": 196,
 129      "metadata": {},
 130      "output_type": "execute_result"
 131     }
 132    ],
 133    "source": [
 134     "last_uk_date = %sql select date from uk_data order by date desc limit 1\n",
 135     "last_uk_date = last_uk_date[0][0]\n",
 136     "last_uk_date"
 137    ]
 138   },
 139   {
 140    "cell_type": "code",
 141    "execution_count": 197,
 142    "metadata": {
 143     "Collapsed": "false"
 144    },
 145    "outputs": [
 146     {
 147      "name": "stdout",
 148      "output_type": "stream",
 149      "text": [
 150       " * postgresql://covid:***@localhost/covid\n",
 151       "1 rows affected.\n"
 152      ]
 153     },
 154     {
 155      "data": {
 156       "text/plain": [
 157        "datetime.date(2021, 1, 18)"
 158       ]
 159      },
 160      "execution_count": 197,
 161      "metadata": {},
 162      "output_type": "execute_result"
 163     }
 164    ],
 165    "source": [
 166     "last_intl_date = %sql select report_date from weekly_cases order by report_date desc limit 1\n",
 167     "last_intl_date = last_intl_date[0][0]\n",
 168     "last_intl_date"
 169    ]
 170   },
 171   {
 172    "cell_type": "code",
 173    "execution_count": 198,
 174    "metadata": {},
 175    "outputs": [
 176     {
 177      "name": "stdout",
 178      "output_type": "stream",
 179      "text": [
 180       " * postgresql://covid:***@localhost/covid\n",
 181       "390 rows affected.\n",
 182       "Returning data to local variable results\n"
 183      ]
 184     }
 185    ],
 186    "source": [
 187     "%%sql results << select date, new_cases, new_deaths \n",
 188     "from uk_data \n",
 189     "order by date"
 190    ]
 191   },
 192   {
 193    "cell_type": "code",
 194    "execution_count": 199,
 195    "metadata": {},
 196    "outputs": [
 197     {
 198      "data": {
 199       "text/html": [
 200        "<div>\n",
 201        "<style scoped>\n",
 202        "    .dataframe tbody tr th:only-of-type {\n",
 203        "        vertical-align: middle;\n",
 204        "    }\n",
 205        "\n",
 206        "    .dataframe tbody tr th {\n",
 207        "        vertical-align: top;\n",
 208        "    }\n",
 209        "\n",
 210        "    .dataframe thead th {\n",
 211        "        text-align: right;\n",
 212        "    }\n",
 213        "</style>\n",
 214        "<table border=\"1\" class=\"dataframe\">\n",
 215        "  <thead>\n",
 216        "    <tr style=\"text-align: right;\">\n",
 217        "      <th></th>\n",
 218        "      <th>new_cases</th>\n",
 219        "      <th>new_deaths</th>\n",
 220        "    </tr>\n",
 221        "    <tr>\n",
 222        "      <th>date</th>\n",
 223        "      <th></th>\n",
 224        "      <th></th>\n",
 225        "    </tr>\n",
 226        "  </thead>\n",
 227        "  <tbody>\n",
 228        "    <tr>\n",
 229        "      <th>2021-01-17</th>\n",
 230        "      <td>28875.0</td>\n",
 231        "      <td>671</td>\n",
 232        "    </tr>\n",
 233        "    <tr>\n",
 234        "      <th>2021-01-18</th>\n",
 235        "      <td>44732.0</td>\n",
 236        "      <td>599</td>\n",
 237        "    </tr>\n",
 238        "    <tr>\n",
 239        "      <th>2021-01-19</th>\n",
 240        "      <td>39311.0</td>\n",
 241        "      <td>1610</td>\n",
 242        "    </tr>\n",
 243        "    <tr>\n",
 244        "      <th>2021-01-20</th>\n",
 245        "      <td>35015.0</td>\n",
 246        "      <td>1820</td>\n",
 247        "    </tr>\n",
 248        "    <tr>\n",
 249        "      <th>2021-01-21</th>\n",
 250        "      <td>31430.0</td>\n",
 251        "      <td>1290</td>\n",
 252        "    </tr>\n",
 253        "    <tr>\n",
 254        "      <th>2021-01-22</th>\n",
 255        "      <td>29094.0</td>\n",
 256        "      <td>1401</td>\n",
 257        "    </tr>\n",
 258        "    <tr>\n",
 259        "      <th>2021-01-23</th>\n",
 260        "      <td>20495.0</td>\n",
 261        "      <td>1348</td>\n",
 262        "    </tr>\n",
 263        "    <tr>\n",
 264        "      <th>2021-01-24</th>\n",
 265        "      <td>14266.0</td>\n",
 266        "      <td>610</td>\n",
 267        "    </tr>\n",
 268        "    <tr>\n",
 269        "      <th>2021-01-25</th>\n",
 270        "      <td>4482.0</td>\n",
 271        "      <td>592</td>\n",
 272        "    </tr>\n",
 273        "    <tr>\n",
 274        "      <th>2021-01-26</th>\n",
 275        "      <td>NaN</td>\n",
 276        "      <td>1631</td>\n",
 277        "    </tr>\n",
 278        "  </tbody>\n",
 279        "</table>\n",
 280        "</div>"
 281       ],
 282       "text/plain": [
 283        "            new_cases  new_deaths\n",
 284        "date                             \n",
 285        "2021-01-17    28875.0         671\n",
 286        "2021-01-18    44732.0         599\n",
 287        "2021-01-19    39311.0        1610\n",
 288        "2021-01-20    35015.0        1820\n",
 289        "2021-01-21    31430.0        1290\n",
 290        "2021-01-22    29094.0        1401\n",
 291        "2021-01-23    20495.0        1348\n",
 292        "2021-01-24    14266.0         610\n",
 293        "2021-01-25     4482.0         592\n",
 294        "2021-01-26        NaN        1631"
 295       ]
 296      },
 297      "execution_count": 199,
 298      "metadata": {},
 299      "output_type": "execute_result"
 300     }
 301    ],
 302    "source": [
 303     "uk_data = results.DataFrame()\n",
 304     "uk_data['date'] = uk_data.date.astype('datetime64[ns]')\n",
 305     "uk_data.set_index('date', inplace=True)\n",
 306     "uk_data.tail(10)"
 307    ]
 308   },
 309   {
 310    "cell_type": "code",
 311    "execution_count": 200,
 312    "metadata": {},
 313    "outputs": [
 314     {
 315      "name": "stdout",
 316      "output_type": "stream",
 317      "text": [
 318       " * postgresql://covid:***@localhost/covid\n",
 319       "1 rows affected.\n"
 320      ]
 321     },
 322     {
 323      "data": {
 324       "text/plain": [
 325        "datetime.date(2021, 1, 26)"
 326       ]
 327      },
 328      "execution_count": 200,
 329      "metadata": {},
 330      "output_type": "execute_result"
 331     }
 332    ],
 333    "source": [
 334     "most_recent_uk_date = %sql select max(date) from uk_data\n",
 335     "most_recent_uk_date = most_recent_uk_date[0][0]\n",
 336     "most_recent_uk_date"
 337    ]
 338   },
 339   {
 340    "cell_type": "code",
 341    "execution_count": 201,
 342    "metadata": {
 343     "Collapsed": "false"
 344    },
 345    "outputs": [
 346     {
 347      "name": "stdout",
 348      "output_type": "stream",
 349      "text": [
 350       " * postgresql://covid:***@localhost/covid\n",
 351       "7 rows affected.\n",
 352       "Returning data to local variable results\n"
 353      ]
 354     }
 355    ],
 356    "source": [
 357     "%%sql results << select geo_id, country_name, culm_deaths \n",
 358     "from weekly_cases join countries using (geo_id)\n",
 359     "where geo_id in :COUNTRIES_CORE \n",
 360     "    and (geo_id, report_date) in (select geo_id, max(report_date) from weekly_cases group by geo_id)\n",
 361     "order by geo_id"
 362    ]
 363   },
 364   {
 365    "cell_type": "code",
 366    "execution_count": 202,
 367    "metadata": {},
 368    "outputs": [
 369     {
 370      "data": {
 371       "text/plain": [
 372        "datetime.date(2020, 12, 27)"
 373       ]
 374      },
 375      "execution_count": 202,
 376      "metadata": {},
 377      "output_type": "execute_result"
 378     }
 379    ],
 380    "source": [
 381     "thirty_days_ago = most_recent_uk_date - datetime.timedelta(days=30)\n",
 382     "thirty_days_ago"
 383    ]
 384   },
 385   {
 386    "cell_type": "code",
 387    "execution_count": 203,
 388    "metadata": {},
 389    "outputs": [
 390     {
 391      "name": "stdout",
 392      "output_type": "stream",
 393      "text": [
 394       " * postgresql://covid:***@localhost/covid\n",
 395       "1 rows affected.\n",
 396       " * postgresql://covid:***@localhost/covid\n",
 397       "1 rows affected.\n",
 398       " * postgresql://covid:***@localhost/covid\n",
 399       "1 rows affected.\n"
 400      ]
 401     },
 402     {
 403      "data": {
 404       "text/plain": [
 405        "(100184, 29366, 1283174)"
 406       ]
 407      },
 408      "execution_count": 203,
 409      "metadata": {},
 410      "output_type": "execute_result"
 411     }
 412    ],
 413    "source": [
 414     "# thirty_days_ago = most_recent_uk_date - datetime.interval(days=30)\n",
 415     "total_uk_deaths = %sql select sum(new_deaths) from uk_data\n",
 416     "total_uk_deaths = total_uk_deaths[0][0]\n",
 417     "deaths_in_past_month = %sql select sum(new_deaths) from uk_data where date > :thirty_days_ago\n",
 418     "deaths_in_past_month = deaths_in_past_month[0][0]\n",
 419     "cases_in_past_month = %sql select sum(new_cases) from uk_data where date > :thirty_days_ago\n",
 420     "cases_in_past_month = cases_in_past_month[0][0]\n",
 421     "total_uk_deaths, deaths_in_past_month, cases_in_past_month"
 422    ]
 423   },
 424   {
 425    "cell_type": "code",
 426    "execution_count": 204,
 427    "metadata": {
 428     "Collapsed": "false"
 429    },
 430    "outputs": [],
 431    "source": [
 432     "with open('covid_summary.md', 'w') as f:\n",
 433     "    f.write('% Covid death data summary\\n')\n",
 434     "    f.write('% Neil Smith\\n')\n",
 435     "    f.write(f'% Created on {datetime.datetime.now().strftime(\"%Y-%m-%d\")}\\n')\n",
 436     "    f.write('\\n')       \n",
 437     "    f.write(f'> Last UK data from {last_uk_date.strftime(\"%d %b %Y\")}. ')\n",
 438     "    f.write(f' Last international data from {last_intl_date.strftime(\"%d %b %Y\")}.\\n')\n",
 439     "    f.write('\\n')    "
 440    ]
 441   },
 442   {
 443    "cell_type": "code",
 444    "execution_count": 205,
 445    "metadata": {
 446     "Collapsed": "false"
 447    },
 448    "outputs": [],
 449    "source": [
 450     "with open('covid_summary.md', 'a') as f:\n",
 451     "    f.write('## Headlines (UK data)\\n')\n",
 452     "    f.write('\\n')\n",
 453     "    f.write('| []() | |\\n')\n",
 454     "    f.write('|:---|---:|\\n')\n",
 455     "    f.write(f'| Deaths reported so far | {total_uk_deaths} | \\n')\n",
 456     "    f.write(f'| Deaths in last 30 days | {deaths_in_past_month} | \\n')\n",
 457     "    f.write(f'| Cases in last 30 days  | {cases_in_past_month} | \\n')\n",
 458     "#     f.write(f'| Total Covid deaths to date (estimated) | {uk_deaths_to_date:.0f} |\\n')\n",
 459     "    f.write('\\n')"
 460    ]
 461   },
 462   {
 463    "cell_type": "code",
 464    "execution_count": 206,
 465    "metadata": {
 466     "Collapsed": "false"
 467    },
 468    "outputs": [],
 469    "source": [
 470     "with open('covid_summary.md', 'a') as f:\n",
 471     "    f.write('## International comparison\\n')\n",
 472     "    f.write('\\n')\n",
 473     "    f.write(f'Based on weekly data. Last data from {last_intl_date.strftime(\"%d %b %Y\")}\\n')\n",
 474     "    f.write('\\n')\n",
 475     "    f.write('### Total deaths\\n')\n",
 476     "    f.write('\\n')\n",
 477     "    f.write('![Total deaths](covid_deaths_total_linear.png)\\n')\n",
 478     "    f.write('\\n')\n",
 479     "    f.write('| Country ID | Country name | Total deaths |\\n')\n",
 480     "    f.write('|:-----------|:-------------|-------------:|\\n')\n",
 481     "    for c_id, c_name, t_deaths in results:\n",
 482     "        f.write(f'| {c_id} | {c_name} | {t_deaths} |\\n')\n",
 483     "    f.write('\\n')"
 484    ]
 485   },
 486   {
 487    "cell_type": "code",
 488    "execution_count": 207,
 489    "metadata": {
 490     "Collapsed": "false"
 491    },
 492    "outputs": [],
 493    "source": [
 494     "# with open('covid_summary.md', 'a') as f:\n",
 495     "#     f.write('## All-causes deaths, UK\\n')\n",
 496     "#     f.write('\\n')\n",
 497     "#     f.write('![All-causes deaths](deaths-radar.png)\\n')\n",
 498     "#     f.write('\\n')\n",
 499     "#     f.write('### True deaths\\n')\n",
 500     "#     f.write('\\n')\n",
 501     "#     f.write(f'The number of deaths reported in official statistics, {uk_covid_deaths}, is an underestimate '\n",
 502     "#             'of the true number of Covid deaths.\\n'\n",
 503     "#             'This is especially true early in the pandemic, approximately March to May 2020.\\n')\n",
 504     "#     f.write('We can get a better understanding of the impact of Covid by looking at the number of deaths, '\n",
 505     "#             'over and above what would be expected at each week of the year.\\n')\n",
 506     "#     f.write(f'The ONS (and other bodies in Scotland and Northern Ireland) have released data on the number of deaths '\n",
 507     "#             f'up to {pd.to_datetime(excess_deaths_data[\"end_date\"]).strftime(\"%d %B %Y\")}.\\n\\n')\n",
 508     "#     f.write('If, for each of those weeks, I take the largest of the excess deaths or the reported Covid deaths, ')\n",
 509     "#     f.write(f'I estimate there have been **{uk_deaths_to_date}** total deaths so far.\\n')\n",
 510     "#     f.write('\\n')"
 511    ]
 512   },
 513   {
 514    "cell_type": "code",
 515    "execution_count": 208,
 516    "metadata": {
 517     "Collapsed": "false"
 518    },
 519    "outputs": [],
 520    "source": [
 521     "with open('covid_summary.md', 'a') as f:\n",
 522     "    f.write('### Deaths per week\\n')\n",
 523     "    f.write('\\n')\n",
 524     "    f.write('![Deaths per week](covid_deaths_per_week.png)\\n')\n",
 525     "    f.write('\\n')\n",
 526     "    f.write('![Deaths per week, last 6 weeks](deaths_by_date_last_6_weeks.png)\\n')\n",
 527     "    f.write('\\n')"
 528    ]
 529   },
 530   {
 531    "cell_type": "code",
 532    "execution_count": 209,
 533    "metadata": {},
 534    "outputs": [],
 535    "source": [
 536     "with open('covid_summary.md', 'a') as f:\n",
 537     "    f.write('## UK data\\n')\n",
 538     "    f.write('\\n')\n",
 539     "    f.write('### Total deaths\\n')\n",
 540     "    f.write('\\n')\n",
 541     "    f.write(f'Deaths reported up to {last_uk_date.strftime(\"%d %b %Y\")}: {total_uk_deaths}\\n')\n",
 542     "    f.write('\\n')    \n",
 543     "    f.write('![Total deaths](cases_and_deaths.png)\\n')\n",
 544     "    f.write('\\n')    \n",
 545     "    f.write('![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png)\\n')\n",
 546     "    f.write('\\n')\n",
 547     "    f.write('![Deaths compared to past five years](deaths-radar-2020.png)\\n')\n",
 548     "    f.write('\\n')\n",
 549     "    "
 550    ]
 551   },
 552   {
 553    "cell_type": "code",
 554    "execution_count": 210,
 555    "metadata": {
 556     "Collapsed": "false"
 557    },
 558    "outputs": [],
 559    "source": [
 560     "with open('hospital_normalisation_date.json') as f:\n",
 561     "    hospital_normalisation_date_data = json.load(f)"
 562    ]
 563   },
 564   {
 565    "cell_type": "code",
 566    "execution_count": 211,
 567    "metadata": {
 568     "Collapsed": "false"
 569    },
 570    "outputs": [],
 571    "source": [
 572     "with open('covid_summary.md', 'a') as f:\n",
 573     "    f.write('### Hospital care\\n')\n",
 574     "    f.write(f'Based on a 7-day moving average\\n')\n",
 575     "    f.write('\\n')\n",
 576     "    f.write('![Cases, admissions, deaths](cases_admissions_deaths.png)\\n')\n",
 577     "    f.write('\\n')\n",
 578     "    f.write('Due to the large scale differences between the three '\n",
 579     "            'measures, they are all normalised to show changes ')\n",
 580     "    f.write(f'since {pd.to_datetime(hospital_normalisation_date_data[\"hospital_normalisation_date\"]).strftime(\"%d %B %Y\")}.\\n')\n",
 581     "    f.write('\\n')\n",
 582     "    f.write('People in hospital, and on mechanical ventilators\\n')\n",
 583     "    f.write('\\n')\n",
 584     "    f.write('![People in hospital and on mechancial ventilators](people_in_hospital.png)\\n')\n",
 585     "    f.write('\\n')"
 586    ]
 587   },
 588   {
 589    "cell_type": "code",
 590    "execution_count": 212,
 591    "metadata": {
 592     "Collapsed": "false"
 593    },
 594    "outputs": [],
 595    "source": [
 596     "with open('covid_summary.md', 'a') as f:\n",
 597     "    f.write('### Testing effectiveness\\n')\n",
 598     "    f.write('\\n')\n",
 599     "    f.write('A question about testing is whether more detected cases is a result of more tests being '\n",
 600     "            'done or is because the number of cases is increasing. One way of telling the differeence '\n",
 601     "            'is by looking at the fraction of tests that are positive.\\n')\n",
 602     "    f.write('\\n')\n",
 603     "    f.write('![Positive tests and cases](tests_and_cases.png)\\n')\n",
 604     "    f.write('\\n')\n",
 605     "    f.write('Numbers of positive tests and cases, '\n",
 606     "            '7-day moving average.\\n'\n",
 607     "            'Note the different y-axes\\n')\n",
 608     "    f.write('\\n')    \n",
 609     "    f.write('![Fraction of tests with positive result](fraction_positive_tests.png)\\n')\n",
 610     "    f.write('\\n')\n",
 611     "    f.write('Fraction of tests with a positive result, both daily figures and '\n",
 612     "            '7-day moving average.\\n')\n",
 613     "    f.write('\\n')    \n",
 614     "    f.write('\\n')\n",
 615     "    f.write('![Tests against fraction positive, trajectory](fraction_positive_tests_vs_tests.png)\\n')\n",
 616     "    f.write('\\n')\n",
 617     "    f.write('The trajectory of tests done vs fraction positive tests.\\n')\n",
 618     "    f.write('\\n')\n",
 619     "    f.write('Points higher indicate more tests; points to the right indicate more positive tests.'\n",
 620     "            'More tests being done with the same infection prevelance will move the point up '\n",
 621     "            'and to the left.\\n')\n",
 622     "    f.write('\\n')\n",
 623     "    f.write('\\n')\n",
 624     "    f.write('![Tests against fraction positive, trajectory](tests_vs_fraction_positive_animation.png)\\n')\n",
 625     "    f.write('\\n')"
 626    ]
 627   },
 628   {
 629    "cell_type": "code",
 630    "execution_count": null,
 631    "metadata": {
 632     "Collapsed": "false"
 633    },
 634    "outputs": [],
 635    "source": []
 636   },
 637   {
 638    "cell_type": "code",
 639    "execution_count": 213,
 640    "metadata": {
 641     "Collapsed": "false"
 642    },
 643    "outputs": [],
 644    "source": [
 645     "with open('covid_summary.md', 'a') as f:\n",
 646     "    f.write('# Data sources\\n')\n",
 647     "    f.write('\\n')\n",
 648     "    f.write('> Covid data from [European Centre for Disease Prevention and Control](https://www.ecdc.europa.eu/en/publications-data/download-todays-data-geographic-distribution-covid-19-cases-worldwide)\\n')\n",
 649     "    f.write('\\n')    \n",
 650     "    f.write(\"\"\"> Population data from:\n",
 651     "\n",
 652     "* [Office of National Statistics](https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/weeklyprovisionalfiguresondeathsregisteredinenglandandwales) (Endland and Wales) Weeks start on a Saturday.\n",
 653     "* [Northern Ireland Statistics and Research Agency](https://www.nisra.gov.uk/publications/weekly-deaths) (Northern Ireland). Weeks start on a Saturday. Note that the week numbers don't match the England and Wales data.\n",
 654     "* [National Records of Scotland](https://www.nrscotland.gov.uk/statistics-and-data/statistics/statistics-by-theme/vital-events/general-publications/weekly-and-monthly-data-on-births-and-deaths/weekly-data-on-births-and-deaths) (Scotland). Note that Scotland uses ISO8601 week numbers, which start on a Monday.\"\"\")\n",
 655     "    \n",
 656     "    f.write('\\n\\n')\n",
 657     "    f.write('> [Source code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\\n')\n",
 658     "    f.write('\\n') \n"
 659    ]
 660   },
 661   {
 662    "cell_type": "code",
 663    "execution_count": 214,
 664    "metadata": {
 665     "Collapsed": "false"
 666    },
 667    "outputs": [],
 668    "source": [
 669     "!pandoc --toc -s covid_summary.md > covid_summary.html"
 670    ]
 671   },
 672   {
 673    "cell_type": "code",
 674    "execution_count": 215,
 675    "metadata": {
 676     "Collapsed": "false"
 677    },
 678    "outputs": [
 679     {
 680      "name": "stdout",
 681      "output_type": "stream",
 682      "text": [
 683       "covid_summary.html                            100%   10KB 287.3KB/s   00:00    \n",
 684       "covid_deaths_total_linear.png                 100%   45KB   1.8MB/s   00:00    \n",
 685       "cases_and_deaths.png                          100%   62KB   5.3MB/s   00:00    \n",
 686       "cases_and_deaths_last_60_days.png             100%   62KB   8.4MB/s   00:00    \n",
 687       "deaths-radar-2020.png                         100%  199KB   5.4MB/s   00:00    \n",
 688       "covid_deaths_per_week.png                     100%   61KB   8.0MB/s   00:00    \n",
 689       "fraction_positive_tests.png                   100%   59KB   2.8MB/s   00:00    \n",
 690       "tests_and_cases.png                           100%   40KB   5.4MB/s   00:00    \n",
 691       "deaths_by_date_last_6_weeks.png               100%   33KB   5.4MB/s   00:00    \n",
 692       "fraction_positive_tests_vs_tests.png          100%   41KB   7.7MB/s   00:00    \n",
 693       "tests_vs_fraction_positive_animation.png      100% 1982KB  10.3MB/s   00:00    \n",
 694       "people_in_hospital.png                        100%   42KB   8.0MB/s   00:00    \n",
 695       "cases_admissions_deaths.png                   100%   44KB   2.3MB/s   00:00    \n"
 696      ]
 697     }
 698    ],
 699    "source": [
 700     "!scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html\n",
 701     "!scp covid_deaths_total_linear.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 702     "!scp cases_and_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 703     "!scp cases_and_deaths_last_60_days.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 704     "# !scp deaths-radar.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 705     "!scp deaths-radar-2020.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 706     "!scp covid_deaths_per_week.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 707     "!scp fraction_positive_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/    \n",
 708     "!scp tests_and_cases.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 709     "!scp deaths_by_date_last_6_weeks.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 710     "!scp fraction_positive_tests_vs_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 711     "!scp tests_vs_fraction_positive_animation.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/   \n",
 712     "!scp people_in_hospital.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 713     "!scp cases_admissions_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/"
 714    ]
 715   },
 716   {
 717    "cell_type": "code",
 718    "execution_count": 216,
 719    "metadata": {
 720     "Collapsed": "false"
 721    },
 722    "outputs": [],
 723    "source": [
 724     "with open('uk_covid_deaths.js', 'w') as f:\n",
 725     "    f.write(f\"document.write('{total_uk_deaths}');\")\n",
 726     "    \n",
 727     "with open('uk_deaths_30_days.js', 'w') as f:\n",
 728     "    f.write(f\"document.write('{deaths_in_past_month}');\")\n",
 729     "\n",
 730     "with open('uk_cases_30_days.js', 'w') as f:\n",
 731     "    f.write(f\"document.write('{cases_in_past_month}');\")    \n",
 732     "    \n",
 733     "# with open('estimated_total_deaths.js', 'w') as f:\n",
 734     "#     f.write(f\"document.write('{uk_deaths_to_date:.0f}');\")\n",
 735     "\n",
 736     "# edut = pd.to_datetime(excess_deaths_data[\"end_date\"]).strftime('%d %B %Y')\n",
 737     "# with open('excess_deaths_upto.js', 'w') as f:\n",
 738     "#     f.write(f\"document.write('{edut}');\")\n",
 739     "    \n",
 740     "with open('last_uk_date.js', 'w') as f:\n",
 741     "    f.write(f\"document.write('{pd.to_datetime(last_uk_date).strftime('%d %B %Y')}');\")\n",
 742     "\n",
 743     "with open('last_intl_date.js', 'w') as f:\n",
 744     "    f.write(f\"document.write('{pd.to_datetime(last_intl_date).strftime('%d %B %Y')}');\")\n"
 745    ]
 746   },
 747   {
 748    "cell_type": "code",
 749    "execution_count": 217,
 750    "metadata": {
 751     "Collapsed": "false"
 752    },
 753    "outputs": [],
 754    "source": [
 755     "# pd.to_datetime(excess_deaths_upto).strftime('%d %B %Y')"
 756    ]
 757   },
 758   {
 759    "cell_type": "code",
 760    "execution_count": 218,
 761    "metadata": {
 762     "Collapsed": "false"
 763    },
 764    "outputs": [
 765     {
 766      "name": "stdout",
 767      "output_type": "stream",
 768      "text": [
 769       "uk_covid_deaths.js                            100%   25     2.0KB/s   00:00    \n",
 770       "uk_deaths_30_days.js                          100%   24     1.2KB/s   00:00    \n",
 771       "uk_cases_30_days.js                           100%   26    21.3KB/s   00:00    \n",
 772       "last_uk_date.js                               100%   34     2.4KB/s   00:00    \n",
 773       "last_intl_date.js                             100%   34     3.8KB/s   00:00    \n",
 774       "hospital_normalisation_date.js                100%   33    29.1KB/s   00:00    \n"
 775      ]
 776     }
 777    ],
 778    "source": [
 779     "!scp uk_covid_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 780     "!scp uk_deaths_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 781     "!scp uk_cases_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 782     "# !scp estimated_total_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 783     "# !scp excess_deaths_upto.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 784     "!scp last_uk_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 785     "!scp last_intl_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
 786     "!scp hospital_normalisation_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/"
 787    ]
 788   },
 789   {
 790    "cell_type": "code",
 791    "execution_count": null,
 792    "metadata": {
 793     "Collapsed": "false"
 794    },
 795    "outputs": [],
 796    "source": []
 797   }
 798  ],
 799  "metadata": {
 800   "jupytext": {
 801    "formats": "ipynb,md"
 802   },
 803   "kernelspec": {
 804    "display_name": "Python 3",
 805    "language": "python",
 806    "name": "python3"
 807   },
 808   "language_info": {
 809    "codemirror_mode": {
 810     "name": "ipython",
 811     "version": 3
 812    },
 813    "file_extension": ".py",
 814    "mimetype": "text/x-python",
 815    "name": "python",
 816    "nbconvert_exporter": "python",
 817    "pygments_lexer": "ipython3",
 818    "version": "3.8.5"
 819   }
 820  },
 821  "nbformat": 4,
 822  "nbformat_minor": 4
 823 }