4 "cell_type": "markdown",
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)"
14 "execution_count": 192,
21 "output_type": "stream",
23 "The sql extension is already loaded. To reload it, use:\n",
30 "import collections\n",
32 "import pandas as pd\n",
33 "import numpy as np\n",
34 "from scipy.stats import gmean\n",
37 "import matplotlib as mpl\n",
38 "import matplotlib.pyplot as plt\n",
39 "%matplotlib inline\n",
45 "execution_count": 193,
51 "connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'"
56 "execution_count": 194,
64 "'Connected: covid@covid'"
67 "execution_count": 194,
69 "output_type": "execute_result"
73 "%sql $connection_string"
78 "execution_count": 195,
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",
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))"
99 "cell_type": "markdown",
104 "# Write results to summary file"
109 "execution_count": 196,
116 "output_type": "stream",
118 " * postgresql://covid:***@localhost/covid\n",
125 "datetime.date(2021, 1, 26)"
128 "execution_count": 196,
130 "output_type": "execute_result"
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",
141 "execution_count": 197,
148 "output_type": "stream",
150 " * postgresql://covid:***@localhost/covid\n",
157 "datetime.date(2021, 1, 18)"
160 "execution_count": 197,
162 "output_type": "execute_result"
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",
173 "execution_count": 198,
178 "output_type": "stream",
180 " * postgresql://covid:***@localhost/covid\n",
181 "390 rows affected.\n",
182 "Returning data to local variable results\n"
187 "%%sql results << select date, new_cases, new_deaths \n",
194 "execution_count": 199,
202 " .dataframe tbody tr th:only-of-type {\n",
203 " vertical-align: middle;\n",
206 " .dataframe tbody tr th {\n",
207 " vertical-align: top;\n",
210 " .dataframe thead th {\n",
211 " text-align: right;\n",
214 "<table border=\"1\" class=\"dataframe\">\n",
216 " <tr style=\"text-align: right;\">\n",
218 " <th>new_cases</th>\n",
219 " <th>new_deaths</th>\n",
229 " <th>2021-01-17</th>\n",
230 " <td>28875.0</td>\n",
234 " <th>2021-01-18</th>\n",
235 " <td>44732.0</td>\n",
239 " <th>2021-01-19</th>\n",
240 " <td>39311.0</td>\n",
244 " <th>2021-01-20</th>\n",
245 " <td>35015.0</td>\n",
249 " <th>2021-01-21</th>\n",
250 " <td>31430.0</td>\n",
254 " <th>2021-01-22</th>\n",
255 " <td>29094.0</td>\n",
259 " <th>2021-01-23</th>\n",
260 " <td>20495.0</td>\n",
264 " <th>2021-01-24</th>\n",
265 " <td>14266.0</td>\n",
269 " <th>2021-01-25</th>\n",
270 " <td>4482.0</td>\n",
274 " <th>2021-01-26</th>\n",
283 " new_cases new_deaths\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"
297 "execution_count": 199,
299 "output_type": "execute_result"
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",
311 "execution_count": 200,
316 "output_type": "stream",
318 " * postgresql://covid:***@localhost/covid\n",
325 "datetime.date(2021, 1, 26)"
328 "execution_count": 200,
330 "output_type": "execute_result"
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"
341 "execution_count": 201,
348 "output_type": "stream",
350 " * postgresql://covid:***@localhost/covid\n",
351 "7 rows affected.\n",
352 "Returning data to local variable results\n"
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",
366 "execution_count": 202,
372 "datetime.date(2020, 12, 27)"
375 "execution_count": 202,
377 "output_type": "execute_result"
381 "thirty_days_ago = most_recent_uk_date - datetime.timedelta(days=30)\n",
387 "execution_count": 203,
392 "output_type": "stream",
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",
405 "(100184, 29366, 1283174)"
408 "execution_count": 203,
410 "output_type": "execute_result"
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"
426 "execution_count": 204,
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",
444 "execution_count": 205,
450 "with open('covid_summary.md', 'a') as f:\n",
451 " f.write('## Headlines (UK data)\\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",
464 "execution_count": 206,
470 "with open('covid_summary.md', 'a') as f:\n",
471 " f.write('## International comparison\\n')\n",
473 " f.write(f'Based on weekly data. Last data from {last_intl_date.strftime(\"%d %b %Y\")}\\n')\n",
475 " f.write('### Total deaths\\n')\n",
477 " f.write('![Total deaths](covid_deaths_total_linear.png)\\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",
488 "execution_count": 207,
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",
515 "execution_count": 208,
521 "with open('covid_summary.md', 'a') as f:\n",
522 " f.write('### Deaths per week\\n')\n",
524 " f.write('![Deaths per week](covid_deaths_per_week.png)\\n')\n",
526 " f.write('![Deaths per week, last 6 weeks](deaths_by_date_last_6_weeks.png)\\n')\n",
532 "execution_count": 209,
536 "with open('covid_summary.md', 'a') as f:\n",
537 " f.write('## UK data\\n')\n",
539 " f.write('### Total deaths\\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",
547 " f.write('![Deaths compared to past five years](deaths-radar-2020.png)\\n')\n",
554 "execution_count": 210,
560 "with open('hospital_normalisation_date.json') as f:\n",
561 " hospital_normalisation_date_data = json.load(f)"
566 "execution_count": 211,
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",
576 " f.write('![Cases, admissions, deaths](cases_admissions_deaths.png)\\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",
582 " f.write('People in hospital, and on mechanical ventilators\\n')\n",
584 " f.write('![People in hospital and on mechancial ventilators](people_in_hospital.png)\\n')\n",
590 "execution_count": 212,
596 "with open('covid_summary.md', 'a') as f:\n",
597 " f.write('### Testing effectiveness\\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",
603 " f.write('![Positive tests and cases](tests_and_cases.png)\\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",
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",
615 " f.write('![Tests against fraction positive, trajectory](fraction_positive_tests_vs_tests.png)\\n')\n",
617 " f.write('The trajectory of tests done vs fraction positive tests.\\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",
624 " f.write('![Tests against fraction positive, trajectory](tests_vs_fraction_positive_animation.png)\\n')\n",
630 "execution_count": null,
639 "execution_count": 213,
645 "with open('covid_summary.md', 'a') as f:\n",
646 " f.write('# Data sources\\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",
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",
656 " f.write('\\n\\n')\n",
657 " f.write('> [Source code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\\n')\n",
663 "execution_count": 214,
669 "!pandoc --toc -s covid_summary.md > covid_summary.html"
674 "execution_count": 215,
681 "output_type": "stream",
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"
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/"
718 "execution_count": 216,
724 "with open('uk_covid_deaths.js', 'w') as f:\n",
725 " f.write(f\"document.write('{total_uk_deaths}');\")\n",
727 "with open('uk_deaths_30_days.js', 'w') as f:\n",
728 " f.write(f\"document.write('{deaths_in_past_month}');\")\n",
730 "with open('uk_cases_30_days.js', 'w') as f:\n",
731 " f.write(f\"document.write('{cases_in_past_month}');\") \n",
733 "# with open('estimated_total_deaths.js', 'w') as f:\n",
734 "# f.write(f\"document.write('{uk_deaths_to_date:.0f}');\")\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",
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",
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"
749 "execution_count": 217,
755 "# pd.to_datetime(excess_deaths_upto).strftime('%d %B %Y')"
760 "execution_count": 218,
767 "output_type": "stream",
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"
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/"
791 "execution_count": null,
801 "formats": "ipynb,md"
804 "display_name": "Python 3",
805 "language": "python",
813 "file_extension": ".py",
814 "mimetype": "text/x-python",
816 "nbconvert_exporter": "python",
817 "pygments_lexer": "ipython3",