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