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)
12 from scipy
.stats
import gmean
17 import matplotlib
as mpl
18 import matplotlib
.pyplot
as plt
19 # # %matplotlib inline
23 connection_string
= 'postgresql://covid:3NbjJTkT63@localhost/covid'
27 engine
= sqlalchemy
.create_engine(connection_string
)
31 COUNTRIES_CORE
= tuple(sorted('ITA DEU GBR ESP IRL FRA BEL'.split()))
32 COUNTRIES_FRIENDS
= tuple('ITA GBR ESP BEL SVN MEX'.split())
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]
43 last_uk_date
= singleton_sql_value(engine
, 'select max(date) from uk_data')
47 last_intl_date
= singleton_sql_value(engine
, 'select max(date) from weekly_cases')
51 thirty_days_ago
= last_uk_date
- datetime
.timedelta(days
=30)
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
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')
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')
75 with
open('covid_summary.md', 'a') as f
:
76 f
.write('## Headlines (UK data)\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')
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'''
94 with engine
.connect() as conn
:
95 results
= list(conn
.execute(query_string
))
99 with
open('covid_summary.md', 'a') as f
:
100 f
.write('## International comparison\n')
102 f
.write(f
'Based on weekly data. Last data from {last_intl_date.strftime("%d %b %Y")}\n')
104 f
.write('### Total deaths\n')
106 f
.write('![Total deaths](covid_deaths_total_linear.png)\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')
116 with
open('covid_summary.md', 'a') as f
:
117 f
.write('### Deaths per week\n')
119 f
.write('![Deaths per week](covid_deaths_per_week.png)\n')
121 f
.write('![Deaths per week, last 6 weeks](deaths_by_date_last_6_weeks.png)\n')
126 with
open('covid_summary.md', 'a') as f
:
127 f
.write('## UK data\n')
129 f
.write('### Total deaths\n')
131 f
.write(f
'Deaths reported up to {last_uk_date.strftime("%d %b %Y")}: {total_uk_deaths}\n')
133 f
.write('![Total deaths](cases_and_deaths.png)\n')
135 f
.write('![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png)\n')
137 f
.write('![Deaths compared to past five years](deaths_radar_2021.png)\n')
141 with
open('hospital_normalisation_date.json') as f
:
142 hospital_normalisation_date_data
= json
.load(f
)
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')
150 f
.write('![Cases, admissions, deaths](cases_admissions_deaths.png)\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')
156 f
.write('People in hospital, and on mechanical ventilators\n')
158 f
.write('![People in hospital and on mechancial ventilators](people_in_hospital.png)\n')
163 with
open('covid_summary.md', 'a') as f
:
164 f
.write('### Testing effectiveness\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')
170 f
.write('![Positive tests and cases](tests_and_cases.png)\n')
172 f
.write('Numbers of positive tests and cases, '
173 '7-day moving average.\n'
174 'Note the different y-axes\n')
176 f
.write('![Fraction of tests with positive result](fraction_positive_tests.png)\n')
178 f
.write('Fraction of tests with a positive result, both daily figures and '
179 '7-day moving average.\n')
182 f
.write('![Tests against fraction positive, trajectory](fraction_positive_tests_vs_tests.png)\n')
184 f
.write('The trajectory of tests done vs fraction positive tests.\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')
191 f
.write('![Tests against fraction positive, trajectory](tests_vs_fraction_positive_animation.png)\n')
196 with
open('covid_summary.md', 'a') as f
:
197 f
.write('# Data sources\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')
201 f
.write("""> Population data from:
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.""")
208 f
.write('> [Source code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\n')
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')
217 with
open('uk_covid_deaths.js', 'w') as f
:
218 f
.write(f
"document.write('{total_uk_deaths}');")
220 with
open('uk_deaths_30_days.js', 'w') as f
:
221 f
.write(f
"document.write('{deaths_in_past_month}');")
223 with
open('uk_cases_30_days.js', 'w') as f
:
224 f
.write(f
"document.write('{cases_in_past_month}');")
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')}');")
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')}');")
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',
251 'hospital_normalisation_date.js'
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}')
260 print(f
'Cannot transfer {f}: file does not exist')