publish.py

   1 #!/usr/bin/env python
   2 # coding: utf-8
   3 # %%
   4 # 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)
   5
   6 # %%
   7 import itertools
   8 import collections
   9 import json
  10 import pandas as pd
  11 import numpy as np
  12 from scipy.stats import gmean
  13 import datetime
  14 import sqlalchemy
  15 import os
  16
  17 import matplotlib as mpl
  18 import matplotlib.pyplot as plt
  19 # # %matplotlib inline
  20
  21
  22 # %%
  23 connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
  24
  25
  26 # %%
  27 engine = sqlalchemy.create_engine(connection_string)
  28
  29
  30 # %%
  31 COUNTRIES_CORE = tuple(sorted('ITA DEU GBR ESP IRL FRA BEL'.split()))
  32 COUNTRIES_FRIENDS = tuple('ITA GBR ESP BEL SVN MEX'.split())
  33
  34
  35 # %%
  36 def singleton_sql_value(engine, query_string):
  37     with engine.connect() as conn:
  38         result = conn.execute(query_string)
  39     return result.first()[0]
  40
  41
  42 # %%
  43 last_uk_date = singleton_sql_value(engine, 'select max(date) from uk_data')
  44
  45
  46 # %%
  47 last_intl_date = singleton_sql_value(engine, 'select max(date) from weekly_cases')
  48
  49
  50 # %%
  51 thirty_days_ago = last_uk_date - datetime.timedelta(days=30)
  52
  53
  54 # %%
  55 # total_uk_deaths = singleton_sql_value(engine, 'select (cum_deaths) from uk_data')
  56 total_uk_deaths = singleton_sql_value(engine,
  57                                       'select cum_deaths from uk_data where cum_deaths > 0 order by date desc limit 1')
  58 deaths_in_past_month = singleton_sql_value(engine, f"select sum(new_deaths) from uk_data where date > '{thirty_days_ago.isoformat()}'")
  59 cases_in_past_month = singleton_sql_value(engine, f"select sum(new_cases) from uk_data where date > '{thirty_days_ago.isoformat()}'")
  60 total_uk_deaths, deaths_in_past_month, cases_in_past_month
  61
  62
  63 # %%
  64 with open('covid_summary.md', 'w') as f:
  65     f.write('% Covid death data summary\n')
  66     f.write('% Neil Smith\n')
  67     f.write(f'% Created on {datetime.datetime.now().strftime("%Y-%m-%d")}\n')
  68     f.write('\n')
  69     f.write(f'> Last UK data from {last_uk_date.strftime("%d %b %Y")}. ')
  70     f.write(f' Last international data from {last_intl_date.strftime("%d %b %Y")}.\n')
  71     f.write('\n')
  72
  73
  74 # %%
  75 with open('covid_summary.md', 'a') as f:
  76     f.write('## Headlines (UK data)\n')
  77     f.write('\n')
  78     f.write('| []() | |\n')
  79     f.write('|:---|---:|\n')
  80     f.write(f'| Deaths reported so far | {total_uk_deaths} | \n')
  81     f.write(f'| Deaths in last 30 days | {deaths_in_past_month} | \n')
  82     f.write(f'| Cases in last 30 days  | {cases_in_past_month} | \n')
  83 #     f.write(f'| Total Covid deaths to date (estimated) | {uk_deaths_to_date:.0f} |\n')
  84     f.write('\n')
  85
  86
  87 # %%
  88 query_string = f'''select country_code, country, culm_deaths
  89 from weekly_cases join countries using (country_code)
  90 where country_code in {COUNTRIES_CORE}
  91     and date = '{last_intl_date.isoformat()}'
  92 order by country_code'''
  93
  94 with engine.connect() as conn:
  95     results = list(conn.execute(query_string))
  96 results
  97
  98 # %%
  99 with open('covid_summary.md', 'a') as f:
 100     f.write('## International comparison\n')
 101     f.write('\n')
 102     f.write(f'Based on weekly data. Last data from {last_intl_date.strftime("%d %b %Y")}\n')
 103     f.write('\n')
 104     f.write('### Total deaths\n')
 105     f.write('\n')
 106     f.write('![Total deaths](covid_deaths_total_linear.png)\n')
 107     f.write('\n')
 108     f.write('| Country ID | Country name | Total deaths |\n')
 109     f.write('|:-----------|:-------------|-------------:|\n')
 110     for c_id, c_name, t_deaths in results:
 111         f.write(f'| {c_id} | {c_name} | {t_deaths} |\n')
 112     f.write('\n')
 113
 114
 115 # %%
 116 with open('covid_summary.md', 'a') as f:
 117     f.write('### Deaths per week\n')
 118     f.write('\n')
 119     f.write('![Deaths per week](covid_deaths_per_week.png)\n')
 120     f.write('\n')
 121     f.write('![Deaths per week, last 6 weeks](deaths_by_date_last_6_weeks.png)\n')
 122     f.write('\n')
 123
 124
 125 # %%
 126 with open('covid_summary.md', 'a') as f:
 127     f.write('## UK data\n')
 128     f.write('\n')
 129     f.write('### Total deaths\n')
 130     f.write('\n')
 131     f.write(f'Deaths reported up to {last_uk_date.strftime("%d %b %Y")}: {total_uk_deaths}\n')
 132     f.write('\n')
 133     f.write('![Total deaths](cases_and_deaths.png)\n')
 134     f.write('\n')
 135     f.write('![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png)\n')
 136     f.write('\n')
 137     f.write('![Deaths compared to past five years](deaths_radar_2021.png)\n')
 138     f.write('\n')
 139
 140 # %%
 141 with open('hospital_normalisation_date.json') as f:
 142     hospital_normalisation_date_data = json.load(f)
 143
 144
 145 # %%
 146 with open('covid_summary.md', 'a') as f:
 147     f.write('### Hospital care\n')
 148     f.write(f'Based on a 7-day moving average\n')
 149     f.write('\n')
 150     f.write('![Cases, admissions, deaths](cases_admissions_deaths.png)\n')
 151     f.write('\n')
 152     f.write('Due to the large scale differences between the three '
 153             'measures, they are all normalised to show changes ')
 154     f.write(f'since {pd.to_datetime(hospital_normalisation_date_data["hospital_normalisation_date"]).strftime("%d %B %Y")}.\n')
 155     f.write('\n')
 156     f.write('People in hospital, and on mechanical ventilators\n')
 157     f.write('\n')
 158     f.write('![People in hospital and on mechancial ventilators](people_in_hospital.png)\n')
 159     f.write('\n')
 160
 161
 162 # %%
 163 with open('covid_summary.md', 'a') as f:
 164     f.write('### Testing effectiveness\n')
 165     f.write('\n')
 166     f.write('A question about testing is whether more detected cases is a result of more tests being '
 167             'done or is because the number of cases is increasing. One way of telling the differeence '
 168             'is by looking at the fraction of tests that are positive.\n')
 169     f.write('\n')
 170     f.write('![Positive tests and cases](tests_and_cases.png)\n')
 171     f.write('\n')
 172     f.write('Numbers of positive tests and cases, '
 173             '7-day moving average.\n'
 174             'Note the different y-axes\n')
 175     f.write('\n')
 176     f.write('![Fraction of tests with positive result](fraction_positive_tests.png)\n')
 177     f.write('\n')
 178     f.write('Fraction of tests with a positive result, both daily figures and '
 179             '7-day moving average.\n')
 180     f.write('\n')
 181     f.write('\n')
 182     f.write('![Tests against fraction positive, trajectory](fraction_positive_tests_vs_tests.png)\n')
 183     f.write('\n')
 184     f.write('The trajectory of tests done vs fraction positive tests.\n')
 185     f.write('\n')
 186     f.write('Points higher indicate more tests; points to the right indicate more positive tests.'
 187             'More tests being done with the same infection prevelance will move the point up '
 188             'and to the left.\n')
 189     f.write('\n')
 190     f.write('\n')
 191     f.write('![Tests against fraction positive, trajectory](tests_vs_fraction_positive_animation.png)\n')
 192     f.write('\n')
 193
 194
 195 # %%
 196 with open('covid_summary.md', 'a') as f:
 197     f.write('# Data sources\n')
 198     f.write('\n')
 199     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')
 200     f.write('\n')
 201     f.write("""> Population data from:
 202
 203 * [Office of National Statistics](https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/weeklyprovisionalfiguresondeathsregisteredinenglandandwales) (Endland and Wales) Weeks start on a Saturday.
 204 * [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.
 205 * [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.""")
 206
 207     f.write('\n\n')
 208     f.write('> [Source code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\n')
 209     f.write('\n')
 210
 211
 212 # %%
 213 os.system('pandoc --toc -s covid_summary.md > covid_summary.html')
 214 os.system('scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html')
 215
 216 # %%
 217 with open('uk_covid_deaths.js', 'w') as f:
 218     f.write(f"document.write('{total_uk_deaths}');")
 219
 220 with open('uk_deaths_30_days.js', 'w') as f:
 221     f.write(f"document.write('{deaths_in_past_month}');")
 222
 223 with open('uk_cases_30_days.js', 'w') as f:
 224     f.write(f"document.write('{cases_in_past_month}');")
 225
 226 with open('last_uk_date.js', 'w') as f:
 227     f.write(f"document.write('{pd.to_datetime(last_uk_date).strftime('%d %B %Y')}');")
 228
 229 with open('last_intl_date.js', 'w') as f:
 230     f.write(f"document.write('{pd.to_datetime(last_intl_date).strftime('%d %B %Y')}');")
 231
 232 # %%
 233 transfer_files = [
 234     'covid_deaths_total_linear.png',
 235     'cases_and_deaths.png',
 236     'cases_and_deaths_last_60_days.png',
 237     'deaths_radar_2021.png',
 238     'covid_deaths_per_week.png',
 239     'fraction_positive_tests.png',
 240     'tests_and_cases.png',
 241     'deaths_by_date_last_6_weeks.png',
 242     'fraction_positive_tests_vs_tests.png',
 243     'tests_vs_fraction_positive_animation.png',
 244     'people_in_hospital.png',
 245     'cases_admissions_deaths.png',
 246     'uk_covid_deaths.js',
 247     'uk_deaths_30_days.js',
 248     'uk_cases_30_days.js',
 249     'last_uk_date.js',
 250     'last_intl_date.js',
 251     'hospital_normalisation_date.js'
 252 ]
 253
 254 # %%
 255 for f in transfer_files:
 256     if os.path.isfile(f):
 257         os.system(f'scp {f} neil@ogedei:/var/www/scripts.njae.me.uk/covid/')
 258         print(f'Transferred {f}')
 259     else:
 260         print(f'Cannot transfer {f}: file does not exist')
 261
 262 # %%
 263