publish.md

   1 ---
   2 jupyter:
   3   jupytext:
   4     formats: ipynb,md
   5     text_representation:
   6       extension: .md
   7       format_name: markdown
   8       format_version: '1.2'
   9       jupytext_version: 1.9.1
  10   kernelspec:
  11     display_name: Python 3
  12     language: python
  13     name: python3
  14 ---
  15
  16 <!-- #region Collapsed="false" -->
  17 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)
  18 <!-- #endregion -->
  19
  20 ```python Collapsed="false"
  21 import itertools
  22 import collections
  23 import json
  24 import pandas as pd
  25 import numpy as np
  26 from scipy.stats import gmean
  27 import datetime
  28
  29 import matplotlib as mpl
  30 import matplotlib.pyplot as plt
  31 %matplotlib inline
  32 %load_ext sql
  33 ```
  34
  35 ```python Collapsed="false"
  36 connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
  37 ```
  38
  39 ```python Collapsed="false"
  40 %sql $connection_string
  41 ```
  42
  43 ```python Collapsed="false"
  44 # DEATH_COUNT_THRESHOLD = 10
  45 COUNTRIES_CORE = tuple('IT DE UK ES IE FR BE'.split())
  46 # COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()
  47 # COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()
  48 # # COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()
  49
  50 # COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA
  51 #        'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',
  52 #        'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',
  53 #        'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']
  54 # COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))
  55 # COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))
  56 ```
  57
  58 <!-- #region Collapsed="false" -->
  59 # Write results to summary file
  60 <!-- #endregion -->
  61
  62 ```python Collapsed="false"
  63 last_uk_date = %sql select date from uk_data order by date desc limit 1
  64 last_uk_date = last_uk_date[0][0]
  65 last_uk_date
  66 ```
  67
  68 ```python Collapsed="false"
  69 last_intl_date = %sql select report_date from weekly_cases order by report_date desc limit 1
  70 last_intl_date = last_intl_date[0][0]
  71 last_intl_date
  72 ```
  73
  74 ```sql magic_args="results << select date, new_cases, new_deaths "
  75 from uk_data
  76 order by date
  77 ```
  78
  79 ```python
  80 uk_data = results.DataFrame()
  81 uk_data['date'] = uk_data.date.astype('datetime64[ns]')
  82 uk_data.set_index('date', inplace=True)
  83 uk_data.tail(10)
  84 ```
  85
  86 ```python
  87 most_recent_uk_date = %sql select max(date) from uk_data
  88 most_recent_uk_date = most_recent_uk_date[0][0]
  89 most_recent_uk_date
  90 ```
  91
  92 ```sql Collapsed="false" magic_args="results << select geo_id, country_name, culm_deaths "
  93 from weekly_cases join countries using (geo_id)
  94 where geo_id in :COUNTRIES_CORE
  95     and (geo_id, report_date) in (select geo_id, max(report_date) from weekly_cases group by geo_id)
  96 order by geo_id
  97 ```
  98
  99 ```python
 100 thirty_days_ago = most_recent_uk_date - datetime.timedelta(days=30)
 101 thirty_days_ago
 102 ```
 103
 104 ```python
 105 # thirty_days_ago = most_recent_uk_date - datetime.interval(days=30)
 106 total_uk_deaths = %sql select sum(new_deaths) from uk_data
 107 total_uk_deaths = total_uk_deaths[0][0]
 108 deaths_in_past_month = %sql select sum(new_deaths) from uk_data where date > :thirty_days_ago
 109 deaths_in_past_month = deaths_in_past_month[0][0]
 110 cases_in_past_month = %sql select sum(new_cases) from uk_data where date > :thirty_days_ago
 111 cases_in_past_month = cases_in_past_month[0][0]
 112 total_uk_deaths, deaths_in_past_month, cases_in_past_month
 113 ```
 114
 115 ```python Collapsed="false"
 116 with open('covid_summary.md', 'w') as f:
 117     f.write('% Covid death data summary\n')
 118     f.write('% Neil Smith\n')
 119     f.write(f'% Created on {datetime.datetime.now().strftime("%Y-%m-%d")}\n')
 120     f.write('\n')
 121     f.write(f'> Last UK data from {last_uk_date.strftime("%d %b %Y")}. ')
 122     f.write(f' Last international data from {last_intl_date.strftime("%d %b %Y")}.\n')
 123     f.write('\n')
 124 ```
 125
 126 ```python Collapsed="false"
 127 with open('covid_summary.md', 'a') as f:
 128     f.write('## Headlines (UK data)\n')
 129     f.write('\n')
 130     f.write('| []() | |\n')
 131     f.write('|:---|---:|\n')
 132     f.write(f'| Deaths reported so far | {total_uk_deaths} | \n')
 133     f.write(f'| Deaths in last 30 days | {deaths_in_past_month} | \n')
 134     f.write(f'| Cases in last 30 days  | {cases_in_past_month} | \n')
 135 #     f.write(f'| Total Covid deaths to date (estimated) | {uk_deaths_to_date:.0f} |\n')
 136     f.write('\n')
 137 ```
 138
 139 ```python Collapsed="false"
 140 with open('covid_summary.md', 'a') as f:
 141     f.write('## International comparison\n')
 142     f.write('\n')
 143     f.write(f'Based on weekly data. Last data from {last_intl_date.strftime("%d %b %Y")}\n')
 144     f.write('\n')
 145     f.write('### Total deaths\n')
 146     f.write('\n')
 147     f.write('![Total deaths](covid_deaths_total_linear.png)\n')
 148     f.write('\n')
 149     f.write('| Country ID | Country name | Total deaths |\n')
 150     f.write('|:-----------|:-------------|-------------:|\n')
 151     for c_id, c_name, t_deaths in results:
 152         f.write(f'| {c_id} | {c_name} | {t_deaths} |\n')
 153     f.write('\n')
 154 ```
 155
 156 ```python Collapsed="false"
 157 # with open('covid_summary.md', 'a') as f:
 158 #     f.write('## All-causes deaths, UK\n')
 159 #     f.write('\n')
 160 #     f.write('![All-causes deaths](deaths-radar.png)\n')
 161 #     f.write('\n')
 162 #     f.write('### True deaths\n')
 163 #     f.write('\n')
 164 #     f.write(f'The number of deaths reported in official statistics, {uk_covid_deaths}, is an underestimate '
 165 #             'of the true number of Covid deaths.\n'
 166 #             'This is especially true early in the pandemic, approximately March to May 2020.\n')
 167 #     f.write('We can get a better understanding of the impact of Covid by looking at the number of deaths, '
 168 #             'over and above what would be expected at each week of the year.\n')
 169 #     f.write(f'The ONS (and other bodies in Scotland and Northern Ireland) have released data on the number of deaths '
 170 #             f'up to {pd.to_datetime(excess_deaths_data["end_date"]).strftime("%d %B %Y")}.\n\n')
 171 #     f.write('If, for each of those weeks, I take the largest of the excess deaths or the reported Covid deaths, ')
 172 #     f.write(f'I estimate there have been **{uk_deaths_to_date}** total deaths so far.\n')
 173 #     f.write('\n')
 174 ```
 175
 176 ```python Collapsed="false"
 177 with open('covid_summary.md', 'a') as f:
 178     f.write('### Deaths per week\n')
 179     f.write('\n')
 180     f.write('![Deaths per week](covid_deaths_per_week.png)\n')
 181     f.write('\n')
 182     f.write('![Deaths per week, last 6 weeks](deaths_by_date_last_6_weeks.png)\n')
 183     f.write('\n')
 184 ```
 185
 186 ```python
 187 with open('covid_summary.md', 'a') as f:
 188     f.write('## UK data\n')
 189     f.write('\n')
 190     f.write('### Total deaths\n')
 191     f.write('\n')
 192     f.write(f'Deaths reported up to {last_uk_date.strftime("%d %b %Y")}: {total_uk_deaths}\n')
 193     f.write('\n')
 194     f.write('![Total deaths](cases_and_deaths.png)\n')
 195     f.write('\n')
 196     f.write('![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png)\n')
 197     f.write('\n')
 198     f.write('![Deaths compared to past five years](deaths-radar-2020.png)\n')
 199     f.write('\n')
 200
 201 ```
 202
 203 ```python Collapsed="false"
 204 with open('hospital_normalisation_date.json') as f:
 205     hospital_normalisation_date_data = json.load(f)
 206 ```
 207
 208 ```python Collapsed="false"
 209 with open('covid_summary.md', 'a') as f:
 210     f.write('### Hospital care\n')
 211     f.write(f'Based on a 7-day moving average\n')
 212     f.write('\n')
 213     f.write('![Cases, admissions, deaths](cases_admissions_deaths.png)\n')
 214     f.write('\n')
 215     f.write('Due to the large scale differences between the three '
 216             'measures, they are all normalised to show changes ')
 217     f.write(f'since {pd.to_datetime(hospital_normalisation_date_data["hospital_normalisation_date"]).strftime("%d %B %Y")}.\n')
 218     f.write('\n')
 219     f.write('People in hospital, and on mechanical ventilators\n')
 220     f.write('\n')
 221     f.write('![People in hospital and on mechancial ventilators](people_in_hospital.png)\n')
 222     f.write('\n')
 223 ```
 224
 225 ```python Collapsed="false"
 226 with open('covid_summary.md', 'a') as f:
 227     f.write('### Testing effectiveness\n')
 228     f.write('\n')
 229     f.write('A question about testing is whether more detected cases is a result of more tests being '
 230             'done or is because the number of cases is increasing. One way of telling the differeence '
 231             'is by looking at the fraction of tests that are positive.\n')
 232     f.write('\n')
 233     f.write('![Positive tests and cases](tests_and_cases.png)\n')
 234     f.write('\n')
 235     f.write('Numbers of positive tests and cases, '
 236             '7-day moving average.\n'
 237             'Note the different y-axes\n')
 238     f.write('\n')
 239     f.write('![Fraction of tests with positive result](fraction_positive_tests.png)\n')
 240     f.write('\n')
 241     f.write('Fraction of tests with a positive result, both daily figures and '
 242             '7-day moving average.\n')
 243     f.write('\n')
 244     f.write('\n')
 245     f.write('![Tests against fraction positive, trajectory](fraction_positive_tests_vs_tests.png)\n')
 246     f.write('\n')
 247     f.write('The trajectory of tests done vs fraction positive tests.\n')
 248     f.write('\n')
 249     f.write('Points higher indicate more tests; points to the right indicate more positive tests.'
 250             'More tests being done with the same infection prevelance will move the point up '
 251             'and to the left.\n')
 252     f.write('\n')
 253     f.write('\n')
 254     f.write('![Tests against fraction positive, trajectory](tests_vs_fraction_positive_animation.png)\n')
 255     f.write('\n')
 256 ```
 257
 258 ```python Collapsed="false"
 259
 260 ```
 261
 262 ```python Collapsed="false"
 263 with open('covid_summary.md', 'a') as f:
 264     f.write('# Data sources\n')
 265     f.write('\n')
 266     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')
 267     f.write('\n')
 268     f.write("""> Population data from:
 269
 270 * [Office of National Statistics](https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/weeklyprovisionalfiguresondeathsregisteredinenglandandwales) (Endland and Wales) Weeks start on a Saturday.
 271 * [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.
 272 * [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.""")
 273
 274     f.write('\n\n')
 275     f.write('> [Source code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\n')
 276     f.write('\n')
 277
 278 ```
 279
 280 ```python Collapsed="false"
 281 !pandoc --toc -s covid_summary.md > covid_summary.html
 282 ```
 283
 284 ```python Collapsed="false"
 285 !scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html
 286 !scp covid_deaths_total_linear.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 287 !scp cases_and_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 288 !scp cases_and_deaths_last_60_days.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 289 # !scp deaths-radar.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 290 !scp deaths-radar-2020.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 291 !scp covid_deaths_per_week.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 292 !scp fraction_positive_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 293 !scp tests_and_cases.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 294 !scp deaths_by_date_last_6_weeks.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 295 !scp fraction_positive_tests_vs_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 296 !scp tests_vs_fraction_positive_animation.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 297 !scp people_in_hospital.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 298 !scp cases_admissions_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 299 ```
 300
 301 ```python Collapsed="false"
 302 with open('uk_covid_deaths.js', 'w') as f:
 303     f.write(f"document.write('{total_uk_deaths}');")
 304
 305 with open('uk_deaths_30_days.js', 'w') as f:
 306     f.write(f"document.write('{deaths_in_past_month}');")
 307
 308 with open('uk_cases_30_days.js', 'w') as f:
 309     f.write(f"document.write('{cases_in_past_month}');")
 310
 311 # with open('estimated_total_deaths.js', 'w') as f:
 312 #     f.write(f"document.write('{uk_deaths_to_date:.0f}');")
 313
 314 # edut = pd.to_datetime(excess_deaths_data["end_date"]).strftime('%d %B %Y')
 315 # with open('excess_deaths_upto.js', 'w') as f:
 316 #     f.write(f"document.write('{edut}');")
 317
 318 with open('last_uk_date.js', 'w') as f:
 319     f.write(f"document.write('{pd.to_datetime(last_uk_date).strftime('%d %B %Y')}');")
 320
 321 with open('last_intl_date.js', 'w') as f:
 322     f.write(f"document.write('{pd.to_datetime(last_intl_date).strftime('%d %B %Y')}');")
 323
 324 ```
 325
 326 ```python Collapsed="false"
 327 # pd.to_datetime(excess_deaths_upto).strftime('%d %B %Y')
 328 ```
 329
 330 ```python Collapsed="false"
 331 !scp uk_covid_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 332 !scp uk_deaths_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 333 !scp uk_cases_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 334 # !scp estimated_total_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 335 # !scp excess_deaths_upto.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 336 !scp last_uk_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 337 !scp last_intl_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 338 !scp hospital_normalisation_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 339 ```
 340
 341 ```python Collapsed="false"
 342
 343 ```