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