covid.md

   1 ---
   2 jupyter:
   3   jupytext:
   4     formats: ipynb,md
   5     text_representation:
   6       extension: .md
   7       format_name: markdown
   8       format_version: '1.2'
   9       jupytext_version: 1.3.4
  10   kernelspec:
  11     display_name: Python 3
  12     language: python
  13     name: python3
  14 ---
  15
  16 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)
  17
  18 ```python
  19 import itertools
  20 import collections
  21 import json
  22 import pandas as pd
  23 import numpy as np
  24 from scipy.stats import gmean
  25 import datetime
  26
  27 import matplotlib as mpl
  28 import matplotlib.pyplot as plt
  29 %matplotlib inline
  30 ```
  31
  32 ```python
  33 DEATH_COUNT_THRESHOLD = 10
  34 COUNTRIES_CORE = 'IT DE UK ES IE FR'.split()
  35 COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()
  36 COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()
  37 COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))
  38 COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC))
  39 ```
  40
  41 ```python
  42 !curl https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/ > covid.csv
  43 ```
  44
  45 ```python
  46 # First col is a date, treat geoId of NA as 'Namibia', not "NA" value
  47 raw_data = pd.read_csv('covid.csv', parse_dates=[0], keep_default_na=False, dayfirst=True)
  48 ```
  49
  50 ```python
  51 raw_data.size
  52 ```
  53
  54 ```python
  55 raw_data.head()
  56 ```
  57
  58 ```python
  59 raw_data.dtypes
  60 ```
  61
  62 ```python
  63 base_data = raw_data.set_index(['geoId', 'dateRep'])
  64 base_data.sort_index(inplace=True)
  65 base_data
  66 ```
  67
  68 ```python
  69 base_data.loc['UK']
  70 ```
  71
  72 ```python
  73 base_data.loc['UK', '2020-04-17']
  74 ```
  75
  76 ```python
  77 countries = raw_data[['geoId', 'countriesAndTerritories', 'popData2018']]
  78 countries = countries[countries['popData2018'] != '']
  79 countries = countries.drop_duplicates()
  80 countries.set_index('geoId', inplace=True)
  81 countries = countries.astype({'popData2018': 'int64'})
  82 countries.head()
  83 ```
  84
  85 ```python
  86 countries.shape
  87 ```
  88
  89 ```python
  90 countries[countries.countriesAndTerritories == 'Finland']
  91 ```
  92
  93 ```python
  94 countries.loc[COUNTRIES_OF_INTEREST]
  95 ```
  96
  97 ```python
  98 data_by_date = base_data[['cases', 'deaths']]
  99 data_by_date.head()
 100 ```
 101
 102 ```python
 103 data_by_date.loc['UK']
 104 ```
 105
 106 ```python
 107 data_by_date.groupby(level=0).cumsum()
 108 ```
 109
 110 ```python
 111 data_by_date = data_by_date.merge(
 112     data_by_date.groupby(level=0).cumsum(),
 113     suffixes=('', '_culm'),
 114     left_index=True, right_index=True)
 115 data_by_date
 116 ```
 117
 118 ```python
 119 data_by_date = data_by_date.merge(
 120     data_by_date[['cases', 'deaths']].groupby(level=0).diff(),
 121     suffixes=('', '_diff'),
 122     left_index=True, right_index=True)
 123 data_by_date
 124 ```
 125
 126 ```python
 127 data_by_date.loc['UK', '2020-04-17']
 128 ```
 129
 130 ```python
 131 data_by_date.loc['UK']
 132 ```
 133
 134 ```python
 135 # data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD]
 136 ```
 137
 138 ```python
 139 # days_since_threshold = data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD].groupby(level=0).cumcount()
 140 # days_since_threshold.rename('since_threshold', inplace=True)
 141 ```
 142
 143 ```python
 144 dbd = data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD].reset_index(level=1)
 145 dbd['since_threshold'] = dbd.dateRep
 146 dbd.set_index('dateRep', append=True, inplace=True)
 147 dbd.sort_index(inplace=True)
 148 days_since_threshold = dbd.groupby(level=0).diff().since_threshold.dt.days.fillna(0).astype(int).groupby(level=0).cumsum()
 149 # days_since_threshold.groupby(level=0).cumsum()
 150
 151 # days_since_threshold = dbd.rename('since_threshold')
 152 days_since_threshold
 153 ```
 154
 155 ```python
 156 # days_since_threshold = (data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD]
 157 #                         .reset_index(level=1).groupby(level=0)
 158 #                         .diff().dateRep.dt.days
 159 #                         .groupby(level=0).cumcount()
 160 #                        )
 161 # days_since_threshold.rename('since_threshold', inplace=True)
 162 # days_since_threshold
 163 ```
 164
 165 ```python
 166 data_since_threshold = data_by_date.merge(days_since_threshold,
 167     left_index=True, right_index=True)
 168 data_since_threshold
 169 ```
 170
 171 ```python
 172 data_since_threshold = data_since_threshold.set_index('since_threshold', append=True
 173                               ).reorder_levels(['since_threshold', 'geoId', 'dateRep']
 174                                               ).reset_index('dateRep')
 175 data_since_threshold
 176 ```
 177
 178 ```python
 179 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 180 ```
 181
 182 ```python
 183 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), ['deaths_culm']].unstack().plot(logy=True)
 184 ```
 185
 186 ```python
 187 # deaths = data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT', 'IE']), ['deaths_culm']].unstack().xs('deaths_culm', axis=1, drop_level=True)
 188 ```
 189
 190 ```python
 191 deaths = data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_culm']].unstack().sort_index().xs('deaths_culm', axis=1, drop_level=True)
 192 ```
 193
 194 ```python
 195 data_since_threshold.reset_index().merge(countries, on='geoId').set_index(['since_threshold', 'geoId'])
 196 ```
 197
 198 ```python
 199 data_since_threshold.reset_index().merge(countries, on='geoId').set_index(['since_threshold', 'geoId']).sort_index(inplace=True)
 200 ```
 201
 202 ```python
 203 data_since_threshold_per_capita = data_since_threshold.reset_index().merge(countries, on='geoId').set_index(['since_threshold', 'geoId'])
 204 data_since_threshold_per_capita['cases_culm_pc'] = data_since_threshold_per_capita.cases_culm / data_since_threshold_per_capita.popData2018
 205 data_since_threshold_per_capita['deaths_culm_pc'] = data_since_threshold_per_capita.deaths_culm / data_since_threshold_per_capita.popData2018
 206 data_since_threshold_per_capita
 207 ```
 208
 209 ```python
 210 deaths_pc = data_since_threshold_per_capita.loc[(slice(None), ['UK', 'DE', 'IT', 'IE']), ['deaths_culm_pc']].unstack().sort_index().xs('deaths_culm_pc', axis=1, drop_level=True)
 211 ```
 212
 213 ```python
 214 deaths_pc
 215 ```
 216
 217 ```python
 218 deaths_pc.index
 219 ```
 220
 221 ```python
 222 deaths_pc = data_since_threshold_per_capita.loc[(slice(None), COUNTRIES_ALL), ['deaths_culm_pc']].unstack().xs('deaths_culm_pc', axis=1, drop_level=True)
 223 ```
 224
 225 ```python
 226 deaths[COUNTRIES_CORE].plot()
 227 ```
 228
 229 ```python
 230 deaths[COUNTRIES_FRIENDS].plot()
 231 ```
 232
 233 ```python
 234 ax = deaths[COUNTRIES_CORE].plot(figsize=(10, 6), title="Total deaths, linear")
 235 for c in COUNTRIES_CORE:
 236     lvi = deaths[c].last_valid_index()
 237     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 238 plt.savefig('covid_deaths_total_linear.png')
 239 ```
 240
 241 ```python
 242 ax = deaths[COUNTRIES_NORDIC].plot(figsize=(10, 6), title="Total deaths, linear")
 243 for c in COUNTRIES_NORDIC:
 244     lvi = deaths[c].last_valid_index()
 245     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 246 # plt.savefig('covid_deaths_total_linear.png')
 247 ```
 248
 249 ```python
 250 ax = deaths[COUNTRIES_OF_INTEREST].plot(figsize=(10, 6), title="Total deaths, linear")
 251 for c in COUNTRIES_OF_INTEREST:
 252     lvi = deaths[c].last_valid_index()
 253     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 254 # plt.savefig('covid_deaths_total_linear.png')
 255 ```
 256
 257 ```python
 258 ax = deaths[COUNTRIES_CORE].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 259 for c in COUNTRIES_CORE:
 260     lvi = deaths[c].last_valid_index()
 261     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 262
 263 plt.savefig('covid_deaths_total_log.png')
 264 ```
 265
 266 ```python
 267 ylim = (5*10**3, 5*10**4)
 268 ax = deaths[COUNTRIES_CORE].plot(logy=True, figsize=(10, 6), ylim=ylim, title="Total deaths, log")
 269 for c in COUNTRIES_CORE:
 270     lvi = deaths[c].last_valid_index()
 271     if ylim[0] < deaths[c][lvi] < ylim[1]:
 272         ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 273
 274 # plt.savefig('covid_deaths_total_log.png')
 275 ```
 276
 277 ```python
 278 ax = deaths[COUNTRIES_FRIENDS].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 279 for c in COUNTRIES_FRIENDS:
 280     lvi = deaths[c].last_valid_index()
 281     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 282
 283 # plt.savefig('covid_deaths_total_log.png')
 284 ```
 285
 286 ```python
 287 ax = deaths[COUNTRIES_NORDIC].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 288 for c in COUNTRIES_NORDIC:
 289     lvi = deaths[c].last_valid_index()
 290     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 291
 292 # plt.savefig('covid_deaths_total_log.png')
 293 ```
 294
 295 ```python
 296 ax = deaths[COUNTRIES_OF_INTEREST].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 297 for c in COUNTRIES_OF_INTEREST:
 298     lvi = deaths[c].last_valid_index()
 299     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 300
 301 plt.savefig('covid_deaths_total_log.png')
 302 ```
 303
 304 ```python
 305 ax = deaths_pc.plot(figsize=(10, 6), title="Deaths per capita, linear")
 306 for c in deaths_pc.columns:
 307     lvi = deaths_pc[c].last_valid_index()
 308     ax.text(x = lvi + 1, y = deaths_pc[c][lvi], s = c)
 309 plt.savefig('covid_deaths_per_capita_linear.png')
 310 ```
 311
 312 ```python
 313 ax = deaths_pc.plot(logy=True, figsize=(10, 6), title="Deaths per capita, log")
 314 for c in deaths_pc.columns:
 315     lvi = deaths_pc[c].last_valid_index()
 316     ax.text(x = lvi + 1, y = deaths_pc[c][lvi], s = c)
 317 ```
 318
 319 ```python
 320 deaths_pc[['UK', 'IE']].plot( figsize=(10, 6), title="Deaths per capita, linear")
 321 ```
 322
 323 ```python
 324 deaths_pc[['UK', 'IE']].plot(logy=True, figsize=(10, 6), title="Deaths per capita, log")
 325 ```
 326
 327 ```python
 328 deaths[['UK', 'ES', 'IT']].plot(logy=True, figsize=(10, 6), title="Deaths, log")
 329 plt.savefig('covid_deaths_selected_log.png')
 330 ```
 331
 332 ```python
 333 deaths[['UK', 'ES', 'IT', 'MX']].plot(logy=True, figsize=(10, 6), title="Deaths, log")
 334 ```
 335
 336 ```python
 337 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 338 ```
 339
 340 ```python
 341 data_since_threshold['deaths_m4'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(4, 1).mean())
 342 data_since_threshold['deaths_m7'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(7, 1).mean())
 343 # data_since_threshold['deaths_diff_m4'] = data_since_threshold.groupby(level=1)['deaths_diff'].transform(lambda x: x.rolling(4, 1).mean())
 344 # data_since_threshold['deaths_diff_m7'] = data_since_threshold.groupby(level=1)['deaths_diff'].transform(lambda x: x.rolling(7, 1).mean())
 345 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 346 ```
 347
 348 ```python
 349 deaths_m4 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_m4']]
 350              .unstack().sort_index().xs('deaths_m4', axis=1, drop_level=True))
 351 ```
 352
 353 ```python
 354 deaths_m7 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_m7']]
 355              .unstack().sort_index().xs('deaths_m7', axis=1, drop_level=True))
 356 ```
 357
 358 ```python
 359 ax = deaths_m4.plot(figsize=(10, 6), title="Deaths per day, 4 day moving average")
 360 for c in deaths_m4.columns:
 361     lvi = deaths_m4[c].last_valid_index()
 362     ax.text(x = lvi + 1, y = deaths_m4[c][lvi], s = c)
 363 plt.savefig('covid_deaths_per_day.png')
 364 ```
 365
 366 ```python
 367 ax = deaths_m4[COUNTRIES_CORE].plot(figsize=(10, 6), title="Deaths per day, 4 day moving average")
 368 for c in COUNTRIES_CORE:
 369     lvi = deaths_m4[c].last_valid_index()
 370     ax.text(x = lvi + 1, y = deaths_m4[c][lvi], s = c)
 371 plt.savefig('covid_deaths_per_day-core.png')
 372 ```
 373
 374 ```python
 375 ax = deaths_m4[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Deaths per day, 4 day moving average")
 376 for c in COUNTRIES_FRIENDS:
 377     lvi = deaths_m4[c].last_valid_index()
 378     ax.text(x = lvi + 1, y = deaths_m4[c][lvi], s = c)
 379 plt.savefig('covid_deaths_per_day-friends.png')
 380 ```
 381
 382 ```python
 383 C7s = 'ES FR IT UK'.split()
 384 ax = deaths_m7[C7s].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 385 for c in C7s:
 386     lvi = deaths_m7[c].last_valid_index()
 387     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 388 # plt.savefig('covid_deaths_per_day-friends.png')
 389 ```
 390
 391 ```python
 392 ax = deaths_m7[COUNTRIES_CORE].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 393 for c in COUNTRIES_CORE:
 394     lvi = deaths_m7[c].last_valid_index()
 395     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 396 plt.savefig('covid_deaths_per_day_7.png')
 397 ```
 398
 399 ```python
 400 ax = deaths_m7[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 401 for c in COUNTRIES_FRIENDS:
 402     lvi = deaths_m7[c].last_valid_index()
 403     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 404 plt.savefig('covid_deaths_per_day_friends_7.png')
 405 ```
 406
 407 ```python
 408 def gmean_scale(items):
 409     return gmean(items) / items[-1]
 410 ```
 411
 412 ```python
 413 def doubling_time(df):
 414     return np.log(2) / np.log((df.deaths_culm + df.deaths_g4) / df.deaths_culm)
 415
 416 def doubling_time_7(df):
 417     return np.log(2) / np.log((df.deaths_culm + df.deaths_g7) / df.deaths_culm)
 418 ```
 419
 420 ```python
 421 # data_since_threshold['deaths_g4'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(4, 1).apply(gmean_scale, raw=True))
 422 # data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 423 ```
 424
 425 ```python
 426 data_since_threshold['deaths_g4'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(4, 1).apply(gmean, raw=True))
 427 data_since_threshold['deaths_g7'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(7, 1).apply(gmean, raw=True))
 428 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 429 ```
 430
 431 ```python
 432 data_since_threshold['doubling_time'] = data_since_threshold.groupby(level=1).apply(doubling_time).reset_index(level=0, drop=True)
 433 data_since_threshold['doubling_time_7'] = data_since_threshold.groupby(level=1).apply(doubling_time_7).reset_index(level=0, drop=True)
 434 # data_since_threshold.loc[(slice(None), 'UK'), :]
 435 ```
 436
 437 ```python
 438 doubling_times = (data_since_threshold.loc[(slice(None), COUNTRIES_OF_INTEREST), ['doubling_time']]
 439              .unstack().sort_index().xs('doubling_time', axis=1, drop_level=True))
 440 doubling_times.replace([np.inf, -np.inf], np.nan, inplace=True)
 441 ```
 442
 443 ```python
 444 doubling_times_7 = (data_since_threshold.loc[(slice(None), COUNTRIES_OF_INTEREST), ['doubling_time_7']]
 445              .unstack().sort_index().xs('doubling_time_7', axis=1, drop_level=True))
 446 doubling_times_7.replace([np.inf, -np.inf], np.nan, inplace=True)
 447 ```
 448
 449 ```python
 450 ax = doubling_times.plot(figsize=(10, 6), title="Doubling times, 4 day average")
 451 for c in doubling_times.columns:
 452     lvi = doubling_times[c].last_valid_index()
 453     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 454 # plt.savefig('covid_deaths_per_day.png')
 455 ```
 456
 457 ```python
 458 ax = doubling_times_7[COUNTRIES_CORE].plot(figsize=(10, 6), title="Doubling times, 7 day average")
 459 for c in COUNTRIES_CORE:
 460     lvi = doubling_times_7[c].last_valid_index()
 461     ax.text(x = lvi + 1, y = doubling_times_7[c][lvi], s = c)
 462 plt.savefig('covid_doubling_times_7.png')
 463 ```
 464
 465 ```python
 466 ax = doubling_times[COUNTRIES_CORE].plot(figsize=(10, 6), title="Doubling times, 4 day average")
 467 for c in COUNTRIES_CORE:
 468     lvi = doubling_times[c].last_valid_index()
 469     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 470 plt.savefig('covid_doubling_times.png')
 471 ```
 472
 473 ```python
 474 ax = doubling_times[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Doubling times")
 475 for c in COUNTRIES_FRIENDS:
 476     lvi = doubling_times[c].last_valid_index()
 477     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 478 plt.savefig('covid_doubling_times_friends.png')
 479 ```
 480
 481 ```python
 482 ax = doubling_times[C7s].plot(figsize=(10, 6), title="Doubling times")
 483 for c in C7s:
 484     lvi = doubling_times[c].last_valid_index()
 485     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 486 # plt.savefig('covid_doubling_times_friends.png')
 487 ```
 488
 489 ```python
 490 # deaths_diff_m4 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_diff_m4']]
 491 #              .unstack().sort_index().xs('deaths_diff_m4', axis=1, drop_level=True))
 492 ```
 493
 494 ```python
 495 # deaths_diff_m7 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_diff_m7']]
 496 #              .unstack().sort_index().xs('deaths_diff_m7', axis=1, drop_level=True))
 497 ```
 498
 499 ```python
 500 # deaths_diff_m7
 501 ```
 502
 503 ```python
 504 data_since_threshold.replace([np.inf, -np.inf], np.nan).groupby(level=1).last().loc[COUNTRIES_ALL]#, [doubling_time]]
 505 ```
 506
 507 ```python
 508 data_since_threshold.replace([np.inf, -np.inf], np.nan).groupby(level=1).last().loc[['UK', 'DE', 'IT']]#, [doubling_time]]
 509 ```
 510
 511 ```python
 512 it_since_threshold = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['IT']), :]
 513 s_end = it_since_threshold.index.max()[0]
 514 s_end
 515 ```
 516
 517 ```python
 518 uk_projection = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), :]
 519 s_start = uk_projection.index.max()[0] + 1
 520 s_start
 521 ```
 522
 523 ```python
 524 proj = it_since_threshold.loc[(slice(s_start, s_end), slice(None)), ['cases', 'deaths']]
 525 proj.index = pd.MultiIndex.from_tuples([(n, 'UK') for n, _ in proj.index], names=proj.index.names)
 526 proj
 527 ```
 528
 529 Projected deaths, UK following IT trend from now.
 530
 531 ```python
 532 uk_projection = uk_projection.append(proj, sort=True)
 533 uk_projection.deaths.sum()
 534 ```
 535
 536 ```python
 537 it_since_threshold.deaths.sum()
 538 ```
 539
 540 ```python
 541 with open('excess_deaths.json') as f:
 542     excess_deaths_data = json.load(f)
 543 excess_deaths_data
 544 ```
 545
 546 ```python
 547 excess_deaths_upto = '2020-05-08'
 548 excess_deaths = 54500
 549 ```
 550
 551 ```python
 552 excess_deaths_upto = excess_deaths_data['end_date']
 553 excess_deaths = excess_deaths_data['excess_deaths']
 554 ```
 555
 556 Recorded deaths in period where ONS has reported total deaths
 557
 558 ```python
 559 reported_deaths = base_data.loc['UK'][:excess_deaths_upto]['deaths'].sum()
 560 reported_deaths
 561 ```
 562
 563 ```python
 564 excess_deaths / reported_deaths
 565 ```
 566
 567 True deaths to date, if we follow the scaling of excess deaths over reported deaths so far.
 568
 569 ```python
 570 uk_covid_deaths = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'deaths_culm'].iloc[-1]
 571 uk_covid_deaths
 572 ```
 573
 574 ```python
 575 data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['IT']), 'dateRep'].iloc[-1] + pd.Timedelta(s_end - s_start, unit='days')
 576 ```
 577
 578 ```python
 579 data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'dateRep'].iloc[-1].strftime("%Y-%m-%d")
 580 ```
 581
 582 ```python
 583 uk_covid_deaths * excess_deaths / reported_deaths
 584 ```
 585
 586 ```python
 587 uk_projection.deaths.sum() * excess_deaths / reported_deaths
 588 ```
 589
 590 # Write results to summary file
 591
 592 ```python
 593 with open('covid_summary.md', 'w') as f:
 594     f.write('% Covid death data summary\n')
 595     f.write('% Neil Smith\n')
 596     f.write(f'% Created on {datetime.datetime.now().strftime("%Y-%m-%d")}\n')
 597     f.write('\n')
 598
 599     last_uk_date = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'dateRep'].iloc[-1]
 600     f.write(f'> Last UK data from {last_uk_date.strftime("%Y-%m-%d")}\n')
 601 ```
 602
 603 ```python
 604 with open('covid_summary.md', 'a') as f:
 605     f.write('\n')
 606     f.write('## Headlines\n')
 607     f.write('\n')
 608     f.write('| []() | |\n')
 609     f.write('|:---|---:|\n')
 610     f.write(f'| Deaths reported so far | {reported_deaths} | \n')
 611     f.write(f'| Total Covid deaths to date | {uk_covid_deaths * excess_deaths / reported_deaths:.0f} |\n')
 612     projection_date = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['IT']), 'dateRep'].iloc[-1] + pd.Timedelta(s_end - s_start, unit='days')
 613     f.write(f'| Projected total deaths up to {projection_date.strftime("%Y-%m-%d")} | {uk_projection.deaths.sum() * excess_deaths / reported_deaths:.0f} | \n')
 614     f.write('\n')
 615 ```
 616
 617 ```python
 618 with open('covid_summary.md', 'a') as f:
 619     f.write('\n')
 620     f.write('## Total deaths\n')
 621     f.write(f'Time based on days since {DEATH_COUNT_THRESHOLD} deaths\n')
 622     f.write('\n')
 623     f.write('![Total deaths](covid_deaths_total_linear.png)\n')
 624     f.write('\n')
 625     f.write('| Country ID | Country name | Total deaths |\n')
 626     f.write('|:-----------|:-------------|-------------:|\n')
 627     for c in sorted(COUNTRIES_CORE):
 628         lvi = deaths[c].last_valid_index()
 629         f.write(f'| {c} | {countries.loc[c].countriesAndTerritories} | {int(deaths[c][lvi])} |\n')
 630     f.write('\n')
 631 ```
 632
 633 ```python
 634 with open('covid_summary.md', 'a') as f:
 635     f.write('\n')
 636     f.write('## All-causes deaths, UK\n')
 637     f.write('\n')
 638     f.write('![All-causes deaths](deaths-radar.png)\n')
 639     f.write('\n')
 640     f.write('### Excess deaths\n')
 641     f.write('\n')
 642     f.write(f'From week ending 20 March 2020, there have been approximately **{excess_deaths:.0f}** excess deaths, over the average for the previous five years.\n')
 643     f.write('\n')
 644 ```
 645
 646 ```python
 647 with open('covid_summary.md', 'a') as f:
 648     f.write(f'In that period, the UK reported {reported_deaths} Covid deaths.\n')
 649     f.write(f'That means the actual number of Covid death is about {excess_deaths / reported_deaths:.2f} times higher than the reported figures.\n')
 650     f.write('\n')
 651     f.write(f'The UK has reported {uk_covid_deaths} deaths so far.\n')
 652     f.write(f'Using the scaling factor above, I infer that there have been **{uk_covid_deaths * excess_deaths / reported_deaths:.0f}** total deaths so far.\n')
 653     f.write('\n')
 654 ```
 655
 656 ```python
 657 with open('covid_summary.md', 'a') as f:
 658     f.write('\n')
 659     f.write('## Deaths per day\n')
 660     f.write(f'Based on a 7-day moving average\n')
 661     f.write('\n')
 662     f.write('![Deaths per day](covid_deaths_per_day_7.png)\n')
 663     f.write('\n')
 664 ```
 665
 666 ```python
 667 with open('covid_summary.md', 'a') as f:
 668     f.write('\n')
 669     f.write('## Projected deaths\n')
 670     f.write(f"The UK's daily deaths data is very similar to Italy's.\n")
 671     f.write(f'If I use the Italian data for the next {s_end - s_start - 1} days,')
 672     f.write(f' the UK will report {uk_projection.deaths.sum()} deaths on day {s_end} of the epidemic.\n')
 673     f.write('\n')
 674     f.write('Using the excess deaths scaling from above, that will translate into ')
 675     f.write(f'**{(uk_projection.deaths.sum() * excess_deaths / reported_deaths):.0f}** Covid deaths total.\n')
 676     f.write('\n')
 677 ```
 678
 679 ```python
 680 with open('covid_summary.md', 'a') as f:
 681     f.write('\n')
 682     f.write('## Deaths doubling times\n')
 683     f.write(f'Based on a 7-day moving average\n')
 684     f.write('\n')
 685     f.write('![Deaths doubling times](covid_doubling_times_7.png)\n')
 686     f.write('\n')
 687 ```
 688
 689 ```python
 690 with open('covid_summary.md', 'a') as f:
 691     f.write('# Data sources\n')
 692     f.write('\n')
 693     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')
 694     f.write('\n')
 695     f.write("""> Population data from:
 696
 697 * [Office of National Statistics](https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/weeklyprovisionalfiguresondeathsregisteredinenglandandwales) (Endland and Wales) Weeks start on a Saturday.
 698 * [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.
 699 * [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.""")
 700
 701     f.write('\n\n')
 702     f.write('> [Souce code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\n')
 703     f.write('\n')
 704
 705 ```
 706
 707 ```python
 708 !pandoc -s covid_summary.md > covid_summary.html
 709 ```
 710
 711 ```python
 712 !scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html
 713 !scp covid_deaths_total_linear.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 714 !scp deaths-radar.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 715 !scp covid_deaths_per_day_7.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 716 !scp covid_doubling_times_7.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
 717 ```
 718
 719 ```python
 720
 721 ```