+ {
+ "cell_type": "code",
+ "execution_count": 772,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "{'start_date': '2020-03-20 00:00:00',\n",
+ " 'end_date': '2020-05-08 00:00:00',\n",
+ " 'excess_deaths': 54534.600000000006}"
+ ]
+ },
+ "execution_count": 772,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "with open('excess_deaths.json') as f:\n",
+ " excess_deaths_data = json.load(f)\n",
+ "excess_deaths_data"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 750,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "excess_deaths_upto = '2020-05-08'\n",
+ "excess_deaths = 54500"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 773,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "excess_deaths_upto = excess_deaths_data['end_date']\n",
+ "excess_deaths = excess_deaths_data['excess_deaths']"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Recorded deaths in period where ONS has reported total deaths"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 774,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "30615"
+ ]
+ },
+ "execution_count": 774,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "reported_deaths = base_data.loc['UK'][:excess_deaths_upto]['deaths'].sum()\n",
+ "reported_deaths"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 775,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "1.7813032827045567"
+ ]
+ },
+ "execution_count": 775,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "excess_deaths / reported_deaths"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "True deaths to date, if we follow the scaling of excess deaths over reported deaths so far."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 776,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "36393"
+ ]
+ },
+ "execution_count": 776,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "uk_covid_deaths = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'deaths_culm'].iloc[-1]\n",
+ "uk_covid_deaths"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 777,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "Timestamp('2020-06-09 00:00:00')"
+ ]
+ },
+ "execution_count": 777,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['IT']), 'dateRep'].iloc[-1] + pd.Timedelta(s_end - s_start, unit='days')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 778,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "'2020-05-23'"
+ ]
+ },
+ "execution_count": 778,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'dateRep'].iloc[-1].strftime(\"%Y-%m-%d\")"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 779,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "64826.970367466936"
+ ]
+ },
+ "execution_count": 779,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "uk_covid_deaths * excess_deaths / reported_deaths"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 780,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "71109.6270455659"
+ ]
+ },
+ "execution_count": 780,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "uk_projection.deaths.sum() * excess_deaths / reported_deaths"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# Write results to summary file"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 781,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'w') as f:\n",
+ " f.write('% Covid death data summary\\n')\n",
+ " f.write('% Neil Smith\\n')\n",
+ " f.write(f'% Created on {datetime.datetime.now().strftime(\"%Y-%m-%d\")}\\n')\n",
+ " f.write('\\n')\n",
+ " \n",
+ " last_uk_date = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'dateRep'].iloc[-1]\n",
+ " f.write(f'> Last UK data from {last_uk_date.strftime(\"%Y-%m-%d\")}\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 782,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write('\\n')\n",
+ " f.write('## Headlines\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('| []() | |\\n')\n",
+ " f.write('|:---|---:|\\n')\n",
+ " f.write(f'| Deaths reported so far | {reported_deaths} | \\n')\n",
+ " f.write(f'| Total Covid deaths to date | {uk_covid_deaths * excess_deaths / reported_deaths:.0f} |\\n')\n",
+ " projection_date = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['IT']), 'dateRep'].iloc[-1] + pd.Timedelta(s_end - s_start, unit='days')\n",
+ " f.write(f'| Projected total deaths up to {projection_date.strftime(\"%Y-%m-%d\")} | {uk_projection.deaths.sum() * excess_deaths / reported_deaths:.0f} | \\n')\n",
+ " f.write('\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 783,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write('\\n')\n",
+ " f.write('## Total deaths\\n')\n",
+ " f.write(f'Time based on days since {DEATH_COUNT_THRESHOLD} deaths\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('![Total deaths](covid_deaths_total_linear.png)\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('| Country ID | Country name | Total deaths |\\n')\n",
+ " f.write('|:-----------|:-------------|-------------:|\\n')\n",
+ " for c in sorted(COUNTRIES_CORE):\n",
+ " lvi = deaths[c].last_valid_index()\n",
+ " f.write(f'| {c} | {countries.loc[c].countriesAndTerritories} | {int(deaths[c][lvi])} |\\n')\n",
+ " f.write('\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 784,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write('\\n')\n",
+ " f.write('## All-causes deaths, UK\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('![All-causes deaths](deaths-radar.png)\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('### Excess deaths\\n')\n",
+ " f.write('\\n')\n",
+ " f.write(f'From week ending 20 March 2020, there have been approximately **{excess_deaths:.0f}** excess deaths, over the average for the previous five years.\\n')\n",
+ " f.write('\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 785,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write(f'In that period, the UK reported {reported_deaths} Covid deaths.\\n')\n",
+ " f.write(f'That means the actual number of Covid death is about {excess_deaths / reported_deaths:.2f} times higher than the reported figures.\\n')\n",
+ " f.write('\\n')\n",
+ " f.write(f'The UK has reported {uk_covid_deaths} deaths so far.\\n')\n",
+ " f.write(f'Using the scaling factor above, I infer that there have been **{uk_covid_deaths * excess_deaths / reported_deaths:.0f}** total deaths so far.\\n')\n",
+ " f.write('\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 786,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write('\\n')\n",
+ " f.write('## Deaths per day\\n')\n",
+ " f.write(f'Based on a 7-day moving average\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('![Deaths per day](covid_deaths_per_day_7.png)\\n')\n",
+ " f.write('\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 787,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write('\\n')\n",
+ " f.write('## Projected deaths\\n')\n",
+ " f.write(f\"The UK's daily deaths data is very similar to Italy's.\\n\")\n",
+ " f.write(f'If I use the Italian data for the next {s_end - s_start - 1} days,')\n",
+ " f.write(f' the UK will report {uk_projection.deaths.sum()} deaths on day {s_end} of the epidemic.\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('Using the excess deaths scaling from above, that will translate into ')\n",
+ " f.write(f'**{(uk_projection.deaths.sum() * excess_deaths / reported_deaths):.0f}** Covid deaths total.\\n')\n",
+ " f.write('\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 788,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write('\\n')\n",
+ " f.write('## Deaths doubling times\\n')\n",
+ " f.write(f'Based on a 7-day moving average\\n')\n",
+ " f.write('\\n')\n",
+ " f.write('![Deaths doubling times](covid_doubling_times_7.png)\\n')\n",
+ " f.write('\\n')"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 789,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "with open('covid_summary.md', 'a') as f:\n",
+ " f.write('# Data sources\\n')\n",
+ " f.write('\\n')\n",
+ " 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",
+ " f.write('\\n') \n",
+ " f.write(\"\"\"> Population data from:\n",
+ "\n",
+ "* [Office of National Statistics](https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/weeklyprovisionalfiguresondeathsregisteredinenglandandwales) (Endland and Wales) Weeks start on a Saturday.\n",
+ "* [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",
+ "* [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",
+ " \n",
+ " f.write('\\n\\n')\n",
+ " f.write('> [Souce code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\\n')\n",
+ " f.write('\\n') \n"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 790,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "!pandoc -s covid_summary.md > covid_summary.html"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 791,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "covid_summary.html 100% 5576 2.8MB/s 00:00 \n",
+ "covid_deaths_total_linear.png 100% 42KB 7.8MB/s 00:00 \n",
+ "deaths-radar.png 100% 198KB 10.2MB/s 00:00 \n",
+ "covid_deaths_per_day_7.png 100% 55KB 8.0MB/s 00:00 \n",
+ "covid_doubling_times_7.png 100% 37KB 7.2MB/s 00:00 \n"
+ ]
+ }
+ ],
+ "source": [
+ "!scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html\n",
+ "!scp covid_deaths_total_linear.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
+ "!scp deaths-radar.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
+ "!scp covid_deaths_per_day_7.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/\n",
+ "!scp covid_doubling_times_7.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/"
+ ]
+ },