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