9 jupytext_version: 1.9.1
11 display_name: Python 3
16 <!-- #region Collapsed="false" -->
17 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)
20 ```python Collapsed="false"
26 from scipy.stats import gmean
29 import matplotlib as mpl
30 import matplotlib.pyplot as plt
35 ```python Collapsed="false"
36 connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
39 ```python Collapsed="false"
40 %sql $connection_string
43 ```python Collapsed="false"
44 # DEATH_COUNT_THRESHOLD = 10
45 COUNTRIES_CORE = tuple('IT DE UK ES IE FR BE'.split())
46 # COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()
47 # COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()
48 # # COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()
50 # COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA
51 # 'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',
52 # 'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',
53 # 'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']
54 # COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))
55 # COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))
58 <!-- #region Collapsed="false" -->
59 # Write results to summary file
62 ```python Collapsed="false"
63 last_uk_date = %sql select date from uk_data order by date desc limit 1
64 last_uk_date = last_uk_date[0][0]
68 ```python Collapsed="false"
69 last_intl_date = %sql select report_date from weekly_cases order by report_date desc limit 1
70 last_intl_date = last_intl_date[0][0]
74 ```sql magic_args="results << select date, new_cases, new_deaths "
80 uk_data = results.DataFrame()
81 uk_data['date'] = uk_data.date.astype('datetime64[ns]')
82 uk_data.set_index('date', inplace=True)
87 most_recent_uk_date = %sql select max(date) from uk_data
88 most_recent_uk_date = most_recent_uk_date[0][0]
92 ```sql Collapsed="false" magic_args="results << select geo_id, country_name, culm_deaths "
93 from weekly_cases join countries using (geo_id)
94 where geo_id in :COUNTRIES_CORE
95 and (geo_id, report_date) in (select geo_id, max(report_date) from weekly_cases group by geo_id)
100 thirty_days_ago = most_recent_uk_date - datetime.timedelta(days=30)
105 # thirty_days_ago = most_recent_uk_date - datetime.interval(days=30)
106 total_uk_deaths = %sql select sum(new_deaths) from uk_data
107 total_uk_deaths = total_uk_deaths[0][0]
108 deaths_in_past_month = %sql select sum(new_deaths) from uk_data where date > :thirty_days_ago
109 deaths_in_past_month = deaths_in_past_month[0][0]
110 cases_in_past_month = %sql select sum(new_cases) from uk_data where date > :thirty_days_ago
111 cases_in_past_month = cases_in_past_month[0][0]
112 total_uk_deaths, deaths_in_past_month, cases_in_past_month
115 ```python Collapsed="false"
116 with open('covid_summary.md', 'w') as f:
117 f.write('% Covid death data summary\n')
118 f.write('% Neil Smith\n')
119 f.write(f'% Created on {datetime.datetime.now().strftime("%Y-%m-%d")}\n')
121 f.write(f'> Last UK data from {last_uk_date.strftime("%d %b %Y")}. ')
122 f.write(f' Last international data from {last_intl_date.strftime("%d %b %Y")}.\n')
126 ```python Collapsed="false"
127 with open('covid_summary.md', 'a') as f:
128 f.write('## Headlines (UK data)\n')
130 f.write('| []() | |\n')
131 f.write('|:---|---:|\n')
132 f.write(f'| Deaths reported so far | {total_uk_deaths} | \n')
133 f.write(f'| Deaths in last 30 days | {deaths_in_past_month} | \n')
134 f.write(f'| Cases in last 30 days | {cases_in_past_month} | \n')
135 # f.write(f'| Total Covid deaths to date (estimated) | {uk_deaths_to_date:.0f} |\n')
139 ```python Collapsed="false"
140 with open('covid_summary.md', 'a') as f:
141 f.write('## International comparison\n')
143 f.write(f'Based on weekly data. Last data from {last_intl_date.strftime("%d %b %Y")}\n')
145 f.write('### Total deaths\n')
147 f.write('![Total deaths](covid_deaths_total_linear.png)\n')
149 f.write('| Country ID | Country name | Total deaths |\n')
150 f.write('|:-----------|:-------------|-------------:|\n')
151 for c_id, c_name, t_deaths in results:
152 f.write(f'| {c_id} | {c_name} | {t_deaths} |\n')
156 ```python Collapsed="false"
157 # with open('covid_summary.md', 'a') as f:
158 # f.write('## All-causes deaths, UK\n')
160 # f.write('![All-causes deaths](deaths-radar.png)\n')
162 # f.write('### True deaths\n')
164 # f.write(f'The number of deaths reported in official statistics, {uk_covid_deaths}, is an underestimate '
165 # 'of the true number of Covid deaths.\n'
166 # 'This is especially true early in the pandemic, approximately March to May 2020.\n')
167 # f.write('We can get a better understanding of the impact of Covid by looking at the number of deaths, '
168 # 'over and above what would be expected at each week of the year.\n')
169 # f.write(f'The ONS (and other bodies in Scotland and Northern Ireland) have released data on the number of deaths '
170 # f'up to {pd.to_datetime(excess_deaths_data["end_date"]).strftime("%d %B %Y")}.\n\n')
171 # f.write('If, for each of those weeks, I take the largest of the excess deaths or the reported Covid deaths, ')
172 # f.write(f'I estimate there have been **{uk_deaths_to_date}** total deaths so far.\n')
176 ```python Collapsed="false"
177 with open('covid_summary.md', 'a') as f:
178 f.write('### Deaths per week\n')
180 f.write('![Deaths per week](covid_deaths_per_week.png)\n')
182 f.write('![Deaths per week, last 6 weeks](deaths_by_date_last_6_weeks.png)\n')
187 with open('covid_summary.md', 'a') as f:
188 f.write('## UK data\n')
190 f.write('### Total deaths\n')
192 f.write(f'Deaths reported up to {last_uk_date.strftime("%d %b %Y")}: {total_uk_deaths}\n')
194 f.write('![Total deaths](cases_and_deaths.png)\n')
196 f.write('![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png)\n')
198 f.write('![Deaths compared to past five years](deaths-radar-2020.png)\n')
203 ```python Collapsed="false"
204 with open('hospital_normalisation_date.json') as f:
205 hospital_normalisation_date_data = json.load(f)
208 ```python Collapsed="false"
209 with open('covid_summary.md', 'a') as f:
210 f.write('### Hospital care\n')
211 f.write(f'Based on a 7-day moving average\n')
213 f.write('![Cases, admissions, deaths](cases_admissions_deaths.png)\n')
215 f.write('Due to the large scale differences between the three '
216 'measures, they are all normalised to show changes ')
217 f.write(f'since {pd.to_datetime(hospital_normalisation_date_data["hospital_normalisation_date"]).strftime("%d %B %Y")}.\n')
219 f.write('People in hospital, and on mechanical ventilators\n')
221 f.write('![People in hospital and on mechancial ventilators](people_in_hospital.png)\n')
225 ```python Collapsed="false"
226 with open('covid_summary.md', 'a') as f:
227 f.write('### Testing effectiveness\n')
229 f.write('A question about testing is whether more detected cases is a result of more tests being '
230 'done or is because the number of cases is increasing. One way of telling the differeence '
231 'is by looking at the fraction of tests that are positive.\n')
233 f.write('![Positive tests and cases](tests_and_cases.png)\n')
235 f.write('Numbers of positive tests and cases, '
236 '7-day moving average.\n'
237 'Note the different y-axes\n')
239 f.write('![Fraction of tests with positive result](fraction_positive_tests.png)\n')
241 f.write('Fraction of tests with a positive result, both daily figures and '
242 '7-day moving average.\n')
245 f.write('![Tests against fraction positive, trajectory](fraction_positive_tests_vs_tests.png)\n')
247 f.write('The trajectory of tests done vs fraction positive tests.\n')
249 f.write('Points higher indicate more tests; points to the right indicate more positive tests.'
250 'More tests being done with the same infection prevelance will move the point up '
251 'and to the left.\n')
254 f.write('![Tests against fraction positive, trajectory](tests_vs_fraction_positive_animation.png)\n')
258 ```python Collapsed="false"
262 ```python Collapsed="false"
263 with open('covid_summary.md', 'a') as f:
264 f.write('# Data sources\n')
266 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')
268 f.write("""> Population data from:
270 * [Office of National Statistics](https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/weeklyprovisionalfiguresondeathsregisteredinenglandandwales) (Endland and Wales) Weeks start on a Saturday.
271 * [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.
272 * [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.""")
275 f.write('> [Source code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\n')
280 ```python Collapsed="false"
281 !pandoc --toc -s covid_summary.md > covid_summary.html
284 ```python Collapsed="false"
285 !scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html
286 !scp covid_deaths_total_linear.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
287 !scp cases_and_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
288 !scp cases_and_deaths_last_60_days.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
289 # !scp deaths-radar.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
290 !scp deaths-radar-2020.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
291 !scp covid_deaths_per_week.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
292 !scp fraction_positive_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
293 !scp tests_and_cases.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
294 !scp deaths_by_date_last_6_weeks.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
295 !scp fraction_positive_tests_vs_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
296 !scp tests_vs_fraction_positive_animation.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
297 !scp people_in_hospital.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
298 !scp cases_admissions_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
301 ```python Collapsed="false"
302 with open('uk_covid_deaths.js', 'w') as f:
303 f.write(f"document.write('{total_uk_deaths}');")
305 with open('uk_deaths_30_days.js', 'w') as f:
306 f.write(f"document.write('{deaths_in_past_month}');")
308 with open('uk_cases_30_days.js', 'w') as f:
309 f.write(f"document.write('{cases_in_past_month}');")
311 # with open('estimated_total_deaths.js', 'w') as f:
312 # f.write(f"document.write('{uk_deaths_to_date:.0f}');")
314 # edut = pd.to_datetime(excess_deaths_data["end_date"]).strftime('%d %B %Y')
315 # with open('excess_deaths_upto.js', 'w') as f:
316 # f.write(f"document.write('{edut}');")
318 with open('last_uk_date.js', 'w') as f:
319 f.write(f"document.write('{pd.to_datetime(last_uk_date).strftime('%d %B %Y')}');")
321 with open('last_intl_date.js', 'w') as f:
322 f.write(f"document.write('{pd.to_datetime(last_intl_date).strftime('%d %B %Y')}');")
326 ```python Collapsed="false"
327 # pd.to_datetime(excess_deaths_upto).strftime('%d %B %Y')
330 ```python Collapsed="false"
331 !scp uk_covid_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
332 !scp uk_deaths_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
333 !scp uk_cases_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
334 # !scp estimated_total_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
335 # !scp excess_deaths_upto.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
336 !scp last_uk_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
337 !scp last_intl_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
338 !scp hospital_normalisation_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
341 ```python Collapsed="false"