covid-old.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 <!-- #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)
  18 <!-- #endregion -->
  19
  20 ```python Collapsed="false"
  21 import itertools
  22 import collections
  23 import json
  24 import pandas as pd
  25 import numpy as np
  26 from scipy.stats import gmean
  27 import datetime
  28
  29 import matplotlib as mpl
  30 import matplotlib.pyplot as plt
  31 %matplotlib inline
  32 ```
  33
  34 ```python Collapsed="false"
  35 DEATH_COUNT_THRESHOLD = 10
  36 COUNTRIES_CORE = 'IT DE UK ES IE FR BE'.split()
  37 COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()
  38 COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()
  39 # COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()
  40
  41 COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA
  42        'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',
  43        'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',
  44        'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']
  45 COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))
  46 COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))
  47 ```
  48
  49 ```python Collapsed="false"
  50 !curl https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/ > covid.csv
  51 ```
  52
  53 ```python Collapsed="false"
  54 # First col is a date, treat geoId of NA as 'Namibia', not "NA" value
  55 raw_data = pd.read_csv('covid.csv',
  56                        parse_dates=[0], dayfirst=True,
  57                        keep_default_na=False, na_values = [''],
  58 #                        dtype = {'day': np.int64,
  59 #                                 'month': np.int64,
  60 #                                 'year': np.int64,
  61 #                                 'cases': np.int64,
  62 #                                 'deaths': np.int64,
  63 #                                 'countriesAndTerritories': str,
  64 #                                 'geoId': str,
  65 #                                 'countryterritoryCode': str,
  66 #                                 'popData2019': np.int64,
  67 #                                 'continentExp': str,
  68 #                                 }
  69                       )
  70 ```
  71
  72 ```python Collapsed="false"
  73 raw_data.size
  74 ```
  75
  76 ```python Collapsed="false"
  77 raw_data.fillna(0, inplace=True)
  78 ```
  79
  80 ```python Collapsed="false"
  81 raw_data.head()
  82 ```
  83
  84 ```python Collapsed="false"
  85 raw_data.dtypes
  86 ```
  87
  88 ```python Collapsed="false"
  89 # raw_data = raw_data.astype({'dateRep': np.datetime64,
  90 #     'day': np.int64,
  91 #     'month': np.int64,
  92 #     'year': np.int64,
  93 #     'cases': np.int64,
  94 #     'deaths': np.int64,
  95 #     'countriesAndTerritories': str,
  96 #     'geoId': str,
  97 #     'countryterritoryCode': str,
  98 #     'popData2019': np.int64,
  99 #     'continentExp': str })
 100 raw_data = raw_data.astype({'dateRep': np.datetime64,
 101     'day': np.int64,
 102     'month': np.int64,
 103     'year': np.int64,
 104     'cases': np.int64,
 105     'deaths': np.int64,
 106     'countriesAndTerritories': str,
 107     'geoId': str,
 108     'countryterritoryCode': str,
 109     'popData2019': np.int64,
 110     'continentExp': str })
 111 ```
 112
 113 ```python Collapsed="false"
 114 raw_data.dtypes
 115 ```
 116
 117 ```python Collapsed="false"
 118 raw_data[((raw_data.geoId == 'UK') & (raw_data.dateRep >= '2020-07-10'))]
 119 ```
 120
 121 ```python Collapsed="false"
 122 # raw_data = raw_data[~ ((raw_data.geoId == 'ES') & (raw_data.dateRep >= '2020-05-22'))]
 123 ```
 124
 125 ```python Collapsed="false"
 126 base_data = raw_data.set_index(['geoId', 'dateRep'])
 127 base_data.sort_index(inplace=True)
 128 base_data
 129 ```
 130
 131 ```python Collapsed="false"
 132 base_data.loc['ES'].loc['2020-05-10':]
 133 ```
 134
 135 ```python Collapsed="false"
 136 countries = raw_data[['geoId', 'countriesAndTerritories', 'popData2019', 'continentExp']]
 137 countries = countries[countries['popData2019'] != '']
 138 countries = countries.drop_duplicates()
 139 countries.set_index('geoId', inplace=True)
 140 countries = countries.astype({'popData2019': 'int64'})
 141 countries.head()
 142 ```
 143
 144 ```python Collapsed="false"
 145 countries.shape
 146 ```
 147
 148 ```python Collapsed="false"
 149 countries[countries.countriesAndTerritories == 'Finland']
 150 ```
 151
 152 ```python Collapsed="false"
 153 countries.loc[COUNTRIES_OF_INTEREST]
 154 ```
 155
 156 ```python Collapsed="false"
 157 countries[countries.continentExp == 'America'].index
 158 ```
 159
 160 ```python Collapsed="false"
 161 data_by_date = base_data[['cases', 'deaths']]
 162 data_by_date.head()
 163 ```
 164
 165 ```python Collapsed="false"
 166 data_by_date.loc['UK']
 167 ```
 168
 169 ```python Collapsed="false"
 170 # data_by_date.deaths.drop_duplicates().sort_values().to_csv('dth.csv', header=True)
 171 ```
 172
 173 ```python Collapsed="false"
 174 data_by_date.groupby(level=0).cumsum()
 175 ```
 176
 177 ```python Collapsed="false"
 178 data_by_date = data_by_date.merge(
 179     data_by_date.groupby(level=0).cumsum(),
 180     suffixes=('', '_culm'),
 181     left_index=True, right_index=True)
 182 data_by_date
 183 ```
 184
 185 ```python Collapsed="false"
 186 data_by_date = data_by_date.merge(
 187     data_by_date[['cases', 'deaths']].groupby(level=0).diff(),
 188     suffixes=('', '_diff'),
 189     left_index=True, right_index=True)
 190 data_by_date
 191 ```
 192
 193 ```python Collapsed="false"
 194 data_by_date.loc['UK', '2020-04-17']
 195 ```
 196
 197 ```python Collapsed="false"
 198 data_by_date.loc['UK']
 199 ```
 200
 201 ```python Collapsed="false"
 202 # data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD]
 203 ```
 204
 205 ```python Collapsed="false"
 206 # days_since_threshold = data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD].groupby(level=0).cumcount()
 207 # days_since_threshold.rename('since_threshold', inplace=True)
 208 ```
 209
 210 ```python Collapsed="false"
 211 dbd = data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD].reset_index(level=1)
 212 dbd['since_threshold'] = dbd.dateRep
 213 dbd.set_index('dateRep', append=True, inplace=True)
 214 dbd.sort_index(inplace=True)
 215 days_since_threshold = dbd.groupby(level=0).diff().since_threshold.dt.days.fillna(0).astype(int).groupby(level=0).cumsum()
 216 # days_since_threshold.groupby(level=0).cumsum()
 217
 218 # days_since_threshold = dbd.rename('since_threshold')
 219 days_since_threshold
 220 ```
 221
 222 ```python Collapsed="false"
 223 # days_since_threshold = (data_by_date[data_by_date.deaths_culm > DEATH_COUNT_THRESHOLD]
 224 #                         .reset_index(level=1).groupby(level=0)
 225 #                         .diff().dateRep.dt.days
 226 #                         .groupby(level=0).cumcount()
 227 #                        )
 228 # days_since_threshold.rename('since_threshold', inplace=True)
 229 # days_since_threshold
 230 ```
 231
 232 ```python Collapsed="false"
 233 data_since_threshold = data_by_date.merge(days_since_threshold,
 234     left_index=True, right_index=True)
 235 data_since_threshold
 236 ```
 237
 238 ```python Collapsed="false"
 239 data_since_threshold = data_since_threshold.set_index('since_threshold', append=True
 240                               ).reorder_levels(['since_threshold', 'geoId', 'dateRep']
 241                                               ).reset_index('dateRep')
 242 data_since_threshold.sort_index(inplace=True)
 243 data_since_threshold
 244 ```
 245
 246 ```python Collapsed="false"
 247 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 248 ```
 249
 250 ```python Collapsed="false"
 251 data_since_threshold.loc[(slice(None), ['ES']), :].tail(8)
 252 ```
 253
 254 ```python Collapsed="false"
 255 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), ['deaths_culm']].unstack().plot(logy=True)
 256 ```
 257
 258 ```python Collapsed="false"
 259 # deaths = data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT', 'IE']), ['deaths_culm']].unstack().xs('deaths_culm', axis=1, drop_level=True)
 260 ```
 261
 262 ```python Collapsed="false"
 263 deaths = data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_culm']].unstack().sort_index().xs('deaths_culm', axis=1, drop_level=True)
 264 deaths_by_date = data_by_date.loc[COUNTRIES_ALL, ['deaths_culm']].unstack().sort_index().xs('deaths_culm', axis=1, drop_level=True).T
 265 ```
 266
 267 ```python Collapsed="false"
 268 cases = data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['cases_culm']].unstack().sort_index().xs('cases_culm', axis=1, drop_level=True)
 269 cases_by_date = data_by_date.loc[ COUNTRIES_ALL, ['cases_culm']].unstack().sort_index().xs('cases_culm', axis=1, drop_level=True).T
 270 ```
 271
 272 ```python Collapsed="false"
 273 COUNTRIES_AMERICAS_DEAD = list(set(deaths.columns) & set(COUNTRIES_AMERICAS))
 274 ```
 275
 276 ```python Collapsed="false"
 277 data_since_threshold.reset_index().merge(countries, on='geoId').set_index(['since_threshold', 'geoId'])
 278 ```
 279
 280 ```python Collapsed="false"
 281 data_since_threshold.reset_index().merge(countries, on='geoId').set_index(['since_threshold', 'geoId']).sort_index(inplace=True)
 282 ```
 283
 284 ```python Collapsed="false"
 285 data_since_threshold_per_capita = data_since_threshold.reset_index().merge(countries, on='geoId').set_index(['since_threshold', 'geoId'])
 286 data_since_threshold_per_capita['cases_culm_pc'] = data_since_threshold_per_capita.cases_culm / data_since_threshold_per_capita.popData2019
 287 data_since_threshold_per_capita['deaths_culm_pc'] = data_since_threshold_per_capita.deaths_culm / data_since_threshold_per_capita.popData2019
 288 data_since_threshold_per_capita
 289 ```
 290
 291 ```python Collapsed="false"
 292 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)
 293 ```
 294
 295 ```python Collapsed="false"
 296 deaths_pc.index
 297 ```
 298
 299 ```python Collapsed="false"
 300 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)
 301 ```
 302
 303 ```python Collapsed="false"
 304 deaths[COUNTRIES_CORE].plot()
 305 ```
 306
 307 ```python Collapsed="false"
 308 deaths[COUNTRIES_FRIENDS].plot()
 309 ```
 310
 311 ```python Collapsed="false"
 312 ax = deaths[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Total deaths, linear")
 313 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
 314 for c in COUNTRIES_FRIENDS:
 315     lvi = deaths[c].last_valid_index()
 316     ax.text(x = lvi + 1, y = deaths[c][lvi], s = f"{c}: {deaths[c][lvi]:.0f}")
 317 # plt.savefig('covid_deaths_total_linear.png')
 318 ```
 319
 320 ```python Collapsed="false"
 321 ax = deaths[COUNTRIES_CORE].plot(figsize=(10, 6), title="Total deaths, linear")
 322 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
 323 for c in COUNTRIES_CORE:
 324     lvi = deaths[c].last_valid_index()
 325     ax.text(x = lvi + 1, y = deaths[c][lvi], s = f"{c}: {deaths[c][lvi]:.0f}")
 326 # plt.savefig('covid_deaths_total_linear.png')
 327 ```
 328
 329 ```python Collapsed="false"
 330 ax = deaths_by_date.loc['2020-03-15':, COUNTRIES_CORE].plot(figsize=(10, 6), title="Total deaths, linear")
 331 # data_by_date.loc[COUNTRIES_CORE]
 332 # deaths_by_date = data_by_date.loc[COUNTRIES_ALL, ['deaths_culm']].unstack().sort_index().xs('deaths_culm', axis=1, drop_level=True)
 333 ax.set_xlabel(f"Date")
 334 for c in COUNTRIES_CORE:
 335     lvi = deaths_by_date[c].last_valid_index()
 336     ax.text(x = lvi + pd.Timedelta(days=1), y = deaths_by_date[c][lvi], s = f"{c}: {deaths_by_date[c][lvi]:.0f}")
 337 plt.savefig('covid_deaths_total_linear.png')
 338 ```
 339
 340 ```python Collapsed="false"
 341 deaths_prime = deaths[COUNTRIES_CORE].copy()
 342 deaths_prime.loc[73:, 'ES'] = np.NaN
 343 # deaths_prime['ES'][70:]
 344 ```
 345
 346 ```python Collapsed="false"
 347 ax = deaths_prime[COUNTRIES_CORE].plot(figsize=(10, 6), title="Total deaths, linear")
 348 for c in COUNTRIES_CORE:
 349     lvi = deaths_prime[c].last_valid_index()
 350     ax.text(x = lvi + 1, y = deaths_prime[c][lvi], s = f"{c}: {deaths_prime[c][lvi]:.0f}")
 351 # plt.savefig('covid_deaths_total_linear.png')
 352 ```
 353
 354 ```python Collapsed="false"
 355 ax = cases[COUNTRIES_CORE].plot(figsize=(10, 6), title="Total cases, linear")
 356 for c in COUNTRIES_CORE:
 357     lvi = cases[c].last_valid_index()
 358     ax.text(x = lvi + 1, y = cases[c][lvi], s = c)
 359 plt.savefig('covid_cases_total_linear.png')
 360 ```
 361
 362 ```python Collapsed="false"
 363 ax = deaths[COUNTRIES_AMERICAS_DEAD].plot(figsize=(10, 6), title="Total deaths, linear")
 364 for c in COUNTRIES_AMERICAS_DEAD:
 365     lvi = deaths[c].last_valid_index()
 366     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 367 # plt.savefig('covid_deaths_total_linear.png')
 368 ```
 369
 370 ```python Collapsed="false"
 371 ax = deaths[COUNTRIES_CORE + ['BR', 'MX']].plot(figsize=(10, 6), title="Total deaths, linear")
 372 for c in COUNTRIES_CORE + ['BR', 'MX']:
 373     lvi = deaths[c].last_valid_index()
 374     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 375 # plt.savefig('covid_deaths_total_linear.png')
 376 ```
 377
 378 ```python Collapsed="false"
 379 ax = deaths[COUNTRIES_NORDIC].plot(figsize=(10, 6), title="Total deaths, linear")
 380 for c in COUNTRIES_NORDIC:
 381     lvi = deaths[c].last_valid_index()
 382     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 383 # plt.savefig('covid_deaths_total_linear.png')
 384 ```
 385
 386 ```python Collapsed="false"
 387 ax = deaths[COUNTRIES_OF_INTEREST].plot(figsize=(10, 6), title="Total deaths, linear")
 388 for c in COUNTRIES_OF_INTEREST:
 389     lvi = deaths[c].last_valid_index()
 390     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 391 plt.savefig('covid_deaths_total_linear_of_interest.png')
 392 ```
 393
 394 ```python Collapsed="false"
 395 ax = deaths[COUNTRIES_CORE].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 396 for c in COUNTRIES_CORE:
 397     lvi = deaths[c].last_valid_index()
 398     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 399
 400 plt.savefig('covid_deaths_total_log.png')
 401 ```
 402
 403 ```python Collapsed="false"
 404 ylim = (5*10**3, 5*10**4)
 405 ax = deaths[COUNTRIES_CORE].plot(logy=True, figsize=(10, 6), ylim=ylim, title="Total deaths, log")
 406 for c in COUNTRIES_CORE:
 407     lvi = deaths[c].last_valid_index()
 408     if ylim[0] < deaths[c][lvi] < ylim[1]:
 409         ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 410
 411 # plt.savefig('covid_deaths_total_log.png')
 412 ```
 413
 414 ```python Collapsed="false"
 415 ax = deaths[COUNTRIES_FRIENDS].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 416 for c in COUNTRIES_FRIENDS:
 417     lvi = deaths[c].last_valid_index()
 418     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 419
 420 # plt.savefig('covid_deaths_total_log.png')
 421 ```
 422
 423 ```python Collapsed="false"
 424 ax = deaths[COUNTRIES_NORDIC].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 425 for c in COUNTRIES_NORDIC:
 426     lvi = deaths[c].last_valid_index()
 427     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 428
 429 # plt.savefig('covid_deaths_total_log.png')
 430 ```
 431
 432 ```python Collapsed="false"
 433 ax = deaths[COUNTRIES_OF_INTEREST].plot(logy=True, figsize=(10, 6), title="Total deaths, log")
 434 for c in COUNTRIES_OF_INTEREST:
 435     lvi = deaths[c].last_valid_index()
 436     ax.text(x = lvi + 1, y = deaths[c][lvi], s = c)
 437
 438 plt.savefig('covid_deaths_total_log.png')
 439 ```
 440
 441 ```python Collapsed="false"
 442 ax = deaths_pc.plot(figsize=(10, 6), title="Deaths per capita, linear")
 443 for c in deaths_pc.columns:
 444     lvi = deaths_pc[c].last_valid_index()
 445     ax.text(x = lvi + 1, y = deaths_pc[c][lvi], s = c)
 446 plt.savefig('covid_deaths_per_capita_linear.png')
 447 ```
 448
 449 ```python Collapsed="false"
 450 ax = deaths_pc.plot(logy=True, figsize=(10, 6), title="Deaths per capita, log")
 451 for c in deaths_pc.columns:
 452     lvi = deaths_pc[c].last_valid_index()
 453     ax.text(x = lvi + 1, y = deaths_pc[c][lvi], s = c)
 454 ```
 455
 456 ```python Collapsed="false"
 457 deaths_pc[['UK', 'IE']].plot( figsize=(10, 6), title="Deaths per capita, linear")
 458 ```
 459
 460 ```python Collapsed="false"
 461 deaths_pc[['UK', 'IE']].plot(logy=True, figsize=(10, 6), title="Deaths per capita, log")
 462 ```
 463
 464 ```python Collapsed="false"
 465 deaths[['UK', 'ES', 'IT']].plot(logy=True, figsize=(10, 6), title="Deaths, log")
 466 plt.savefig('covid_deaths_selected_log.png')
 467 ```
 468
 469 ```python Collapsed="false"
 470 deaths[['UK', 'ES', 'IT', 'MX']].plot(logy=True, figsize=(10, 6), title="Deaths, log")
 471 ```
 472
 473 ```python Collapsed="false"
 474 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 475 ```
 476
 477 ```python Collapsed="false"
 478 data_since_threshold['deaths_m4'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(4, 1).mean())
 479 data_since_threshold['deaths_m7'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(7, 1).mean())
 480 data_since_threshold['cases_m7'] = data_since_threshold.groupby(level=1)['cases'].transform(lambda x: x.rolling(7, 1).mean())
 481 # data_since_threshold['deaths_diff_m4'] = data_since_threshold.groupby(level=1)['deaths_diff'].transform(lambda x: x.rolling(4, 1).mean())
 482 # data_since_threshold['deaths_diff_m7'] = data_since_threshold.groupby(level=1)['deaths_diff'].transform(lambda x: x.rolling(7, 1).mean())
 483 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 484 ```
 485
 486 ```python Collapsed="false"
 487 deaths_m4 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_m4']]
 488              .unstack().sort_index().xs('deaths_m4', axis=1, drop_level=True))
 489 ```
 490
 491 ```python Collapsed="false"
 492 deaths_m7 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_m7']]
 493              .unstack().sort_index().xs('deaths_m7', axis=1, drop_level=True))
 494 ```
 495
 496 ```python Collapsed="false"
 497 cases_m7 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['cases_m7']]
 498              .unstack().sort_index().xs('cases_m7', axis=1, drop_level=True))
 499 ```
 500
 501 ```python Collapsed="false"
 502 data_by_date['cases_m7'] = data_by_date.groupby(level=0)['cases'].transform(lambda x: x.rolling(7, 1).mean())
 503 data_by_date['deaths_m7'] = data_by_date.groupby(level=0)['deaths'].transform(lambda x: x.rolling(7, 1).mean())
 504 data_by_date
 505 ```
 506
 507 ```python Collapsed="false"
 508 data_by_date.loc[('UK', '2020-07-15'):'UK', 'cases'].plot()
 509 ```
 510
 511 ```python Collapsed="false"
 512 cases_by_date_m7 = data_by_date.loc[COUNTRIES_ALL, 'cases_m7'].unstack(level=0).sort_index()
 513 cases_by_date_m7[COUNTRIES_CORE].plot()
 514 ```
 515
 516 ```python Collapsed="false"
 517 deaths_by_date_m7 = data_by_date.loc[COUNTRIES_ALL, 'deaths_m7'].unstack(level=0).sort_index()
 518 deaths_by_date_m7[COUNTRIES_CORE].plot()
 519 ```
 520
 521 ```python Collapsed="false"
 522 ax = deaths_m4.plot(figsize=(10, 6), title="Deaths per day, 4 day moving average")
 523 for c in deaths_m4.columns:
 524     lvi = deaths_m4[c].last_valid_index()
 525     ax.text(x = lvi + 1, y = deaths_m4[c][lvi], s = c)
 526 plt.savefig('covid_deaths_per_day.png')
 527 ```
 528
 529 ```python Collapsed="false"
 530 ax = deaths_m4[COUNTRIES_CORE].plot(figsize=(10, 6), title="Deaths per day, 4 day moving average")
 531 for c in COUNTRIES_CORE:
 532     lvi = deaths_m4[c].last_valid_index()
 533     ax.text(x = lvi + 1, y = deaths_m4[c][lvi], s = c)
 534 plt.savefig('covid_deaths_per_day-core.png')
 535 ```
 536
 537 ```python Collapsed="false"
 538 ax = deaths_m4[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Deaths per day, 4 day moving average")
 539 for c in COUNTRIES_FRIENDS:
 540     lvi = deaths_m4[c].last_valid_index()
 541     ax.text(x = lvi + 1, y = deaths_m4[c][lvi], s = c)
 542 # plt.savefig('covid_deaths_per_day-friends.png')
 543 ```
 544
 545 ```python Collapsed="false"
 546 C7s = 'ES FR IT UK'.split()
 547 ax = deaths_m7[C7s].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 548 for c in C7s:
 549     lvi = deaths_m7[c].last_valid_index()
 550     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 551 # plt.savefig('covid_deaths_per_day-friends.png')
 552 ```
 553
 554 ```python Collapsed="false"
 555 ax = deaths_m7[COUNTRIES_CORE].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 556 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
 557 for c in COUNTRIES_CORE:
 558     lvi = deaths_m7[c].last_valid_index()
 559     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 560 # plt.axhline(0, color='0.7')
 561 plt.savefig('covid_deaths_per_day_7.png')
 562 ```
 563
 564 ```python Collapsed="false"
 565 ax = deaths_m7[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 566 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
 567 for c in COUNTRIES_FRIENDS:
 568     lvi = deaths_m7[c].last_valid_index()
 569     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 570 # plt.axhline(0, color='0.7')
 571 plt.savefig('covid_deaths_per_day-friends.png')
 572 ```
 573
 574 ```python Collapsed="false"
 575 deaths_m7_prime = deaths_m7[COUNTRIES_CORE].copy()
 576 deaths_m7_prime.loc[73:, 'ES'] = np.NaN
 577 deaths_m7_prime['ES'][70:]
 578 ```
 579
 580 ```python Collapsed="false"
 581 ax = deaths_m7_prime[COUNTRIES_CORE].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 582 for c in COUNTRIES_CORE:
 583     lvi = deaths_m7_prime[c].last_valid_index()
 584     ax.text(x = lvi + 1, y = deaths_m7_prime[c][lvi], s = c)
 585 plt.savefig('covid_deaths_per_day_7.png') # see below for where this is written, with the projection
 586 ```
 587
 588 ```python Collapsed="false"
 589 ax = deaths_by_date_m7.loc['2020-03-01':, COUNTRIES_CORE].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 590 ax.set_xlabel('Date')
 591 for c in COUNTRIES_CORE:
 592     lvi = deaths_by_date_m7[c].last_valid_index()
 593     ax.text(x = lvi + pd.Timedelta(days=1), y = deaths_by_date_m7[c][lvi], s = c)
 594 plt.savefig('covid_deaths_per_day_7.png')
 595 ```
 596
 597 ```python Collapsed="false"
 598 ax = deaths_m7[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 599 for c in COUNTRIES_FRIENDS:
 600     lvi = deaths_m7[c].last_valid_index()
 601     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 602 plt.savefig('covid_deaths_per_day_friends_7.png')
 603 ```
 604
 605 ```python Collapsed="false"
 606 ax = deaths_m7[COUNTRIES_CORE + ['BR', 'MX']].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")
 607 for c in COUNTRIES_CORE + ['BR', 'MX']:
 608     lvi = deaths_m7[c].last_valid_index()
 609     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 610 # plt.savefig('covid_deaths_per_day_7.png')
 611 ```
 612
 613 ```python Collapsed="false"
 614 ax = deaths_by_date_m7.iloc[-30:][COUNTRIES_CORE].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")#, ylim=(-10, 100))
 615 ax.set_xlabel("Date")
 616
 617 text_x_pos = deaths_by_date_m7.last_valid_index() + pd.Timedelta(days=1)
 618
 619 for c in COUNTRIES_CORE:
 620     lvi = deaths_by_date_m7[c].last_valid_index()
 621 #     if c != 'ES':
 622     ax.text(x = text_x_pos, y = deaths_by_date_m7[c][lvi], s = f"{c}: {deaths_by_date_m7[c][lvi]:.0f}")
 623 plt.savefig('deaths_by_date_last_30_days.png')
 624 ```
 625
 626 ```python Collapsed="false"
 627 ax = deaths_by_date_m7.iloc[-30:][COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average")#, ylim=(-10, 100))
 628 ax.set_xlabel("Date")
 629
 630 text_x_pos = deaths_by_date_m7.last_valid_index() + pd.Timedelta(days=1)
 631
 632 for c in COUNTRIES_FRIENDS:
 633     lvi = deaths_by_date_m7[c].last_valid_index()
 634 #     if c != 'ES':
 635     ax.text(x = text_x_pos, y = deaths_by_date_m7[c][lvi], s = f"{c}: {deaths_by_date_m7[c][lvi]:.0f}")
 636 plt.savefig('deaths_by_date_last_30_days_friends.png')
 637 ```
 638
 639 ```python Collapsed="false"
 640 ax = cases_m7[COUNTRIES_CORE].plot(figsize=(10, 6), title="Cases per day, 7 day moving average")
 641 for c in COUNTRIES_CORE:
 642     lvi = cases_m7[c].last_valid_index()
 643     ax.text(x = lvi + 1, y = cases_m7[c][lvi], s = c)
 644 plt.savefig('covid_cases_per_day-core.png')
 645 ```
 646
 647 ```python Collapsed="false"
 648 ax = cases_m7[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Cases per day, 7 day moving average")
 649 for c in COUNTRIES_FRIENDS:
 650     lvi = cases_m7[c].last_valid_index()
 651     ax.text(x = lvi + 1, y = cases_m7[c][lvi], s = c)
 652 plt.savefig('covid_cases_per_day-core.png')
 653 ```
 654
 655 ```python Collapsed="false"
 656 ax = cases_by_date_m7.iloc[-30:][COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Cases per day, 7 day moving average")#, ylim=(-10, 100))
 657 ax.set_xlabel("Date")
 658
 659 text_x_pos = cases_by_date_m7.last_valid_index() + pd.Timedelta(days=1)
 660
 661 for c in COUNTRIES_FRIENDS:
 662     lvi = cases_by_date_m7[c].last_valid_index()
 663 #     if c != 'ES':
 664     ax.text(x = text_x_pos, y = cases_by_date_m7[c][lvi], s = f"{c}: {cases_by_date_m7[c][lvi]:.0f}")
 665 plt.savefig('cases_by_date_last_30_days_friends.png')
 666 ```
 667
 668 ```python Collapsed="false"
 669 def gmean_scale(items):
 670     return gmean(items) / items[-1]
 671 ```
 672
 673 ```python Collapsed="false"
 674 def doubling_time(df):
 675     return np.log(2) / np.log((df.deaths_culm + df.deaths_g4) / df.deaths_culm)
 676
 677 def doubling_time_7(df):
 678     return np.log(2) / np.log((df.deaths_culm + df.deaths_g7) / df.deaths_culm)
 679 ```
 680
 681 ```python Collapsed="false"
 682 # data_since_threshold['deaths_g4'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(4, 1).apply(gmean_scale, raw=True))
 683 # data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 684 ```
 685
 686 ```python Collapsed="false"
 687 data_since_threshold['deaths_g4'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(4, 1).apply(gmean, raw=True))
 688 data_since_threshold['deaths_g7'] = data_since_threshold.groupby(level=1)['deaths'].transform(lambda x: x.rolling(7, 1).apply(gmean, raw=True))
 689 data_since_threshold.loc[(slice(None), ['UK', 'DE', 'IT']), :]
 690 ```
 691
 692 ```python Collapsed="false"
 693 data_since_threshold['doubling_time'] = data_since_threshold.groupby(level=1).apply(doubling_time).reset_index(level=0, drop=True).sort_index()
 694 data_since_threshold['doubling_time_7'] = data_since_threshold.groupby(level=1).apply(doubling_time_7).reset_index(level=0, drop=True).sort_index()
 695 # data_since_threshold.loc[(slice(None), 'UK'), :]
 696 ```
 697
 698 ```python Collapsed="false"
 699 data_by_date['deaths_g4'] = data_by_date.groupby(level=0)['deaths'].transform(lambda x: x.rolling(4, 1).apply(gmean, raw=True))
 700 data_by_date['deaths_g7'] = data_by_date.groupby(level=0)['deaths'].transform(lambda x: x.rolling(7, 1).apply(gmean, raw=True))
 701 data_by_date['doubling_time'] = data_by_date.groupby(level=0).apply(doubling_time).reset_index(level=0, drop=True).sort_index()
 702 data_by_date['doubling_time_7'] = data_by_date.groupby(level=0).apply(doubling_time_7).reset_index(level=0, drop=True).sort_index()
 703 data_by_date.loc['UK']
 704 ```
 705
 706 ```python Collapsed="false"
 707 doubling_times = (data_since_threshold.loc[(slice(None), COUNTRIES_OF_INTEREST), ['doubling_time']]
 708              .unstack().sort_index().xs('doubling_time', axis=1, drop_level=True))
 709 doubling_times.replace([np.inf, -np.inf], np.nan, inplace=True)
 710 ```
 711
 712 ```python Collapsed="false"
 713 doubling_times_7 = (data_since_threshold.loc[(slice(None), COUNTRIES_OF_INTEREST), ['doubling_time_7']]
 714              .unstack().sort_index().xs('doubling_time_7', axis=1, drop_level=True))
 715 doubling_times_7.replace([np.inf, -np.inf], np.nan, inplace=True)
 716 ```
 717
 718 ```python Collapsed="false"
 719 ax = doubling_times.plot(figsize=(10, 6), title="Doubling times, 4 day average")
 720 for c in doubling_times.columns:
 721     lvi = doubling_times[c].last_valid_index()
 722     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 723 # plt.savefig('covid_deaths_per_day.png')
 724 ```
 725
 726 ```python Collapsed="false"
 727 ax = doubling_times_7[COUNTRIES_CORE].plot(figsize=(10, 6), title="Doubling times, 7 day average")
 728 ax.legend(loc="upper left")
 729 for c in COUNTRIES_CORE:
 730     lvi = doubling_times_7[c].last_valid_index()
 731     ax.text(x = lvi + 1, y = doubling_times_7[c][lvi], s = c)
 732 plt.savefig('covid_doubling_times_7.png')
 733 ```
 734
 735 ```python Collapsed="false"
 736 ax = doubling_times[COUNTRIES_CORE].plot(figsize=(10, 6), title="Doubling times, 4 day average")
 737 for c in COUNTRIES_CORE:
 738     lvi = doubling_times[c].last_valid_index()
 739     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 740 plt.savefig('covid_doubling_times.png')
 741 ```
 742
 743 ```python Collapsed="false"
 744 ax = doubling_times[COUNTRIES_FRIENDS].plot(figsize=(10, 6), title="Doubling times")
 745 for c in COUNTRIES_FRIENDS:
 746     lvi = doubling_times[c].last_valid_index()
 747     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 748 plt.savefig('covid_doubling_times_friends.png')
 749 ```
 750
 751 ```python Collapsed="false"
 752 ax = doubling_times[C7s].plot(figsize=(10, 6), title="Doubling times")
 753 for c in C7s:
 754     lvi = doubling_times[c].last_valid_index()
 755     ax.text(x = lvi + 1, y = doubling_times[c][lvi], s = c)
 756 # plt.savefig('covid_doubling_times_friends.png')
 757 ```
 758
 759 ```python Collapsed="false"
 760 # deaths_diff_m4 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_diff_m4']]
 761 #              .unstack().sort_index().xs('deaths_diff_m4', axis=1, drop_level=True))
 762 ```
 763
 764 ```python Collapsed="false"
 765 # deaths_diff_m7 = (data_since_threshold.loc[(slice(None), COUNTRIES_ALL), ['deaths_diff_m7']]
 766 #              .unstack().sort_index().xs('deaths_diff_m7', axis=1, drop_level=True))
 767 ```
 768
 769 ```python Collapsed="false"
 770 # deaths_diff_m7
 771 ```
 772
 773 ```python Collapsed="false"
 774 # data_since_threshold.replace([np.inf, -np.inf], np.nan).groupby(level=1).last().loc[COUNTRIES_ALL]#, [doubling_time]]
 775 ```
 776
 777 ```python Collapsed="false"
 778 dstl = data_since_threshold.replace([np.inf, -np.inf], np.nan).groupby(level=1).last()
 779 dstl.loc[dstl.index.intersection(COUNTRIES_ALL)]
 780 ```
 781
 782 ```python Collapsed="false"
 783 # data_since_threshold.replace([np.inf, -np.inf], np.nan).groupby(level=1).last().loc[['UK', 'DE', 'IT']]#, [doubling_time]]
 784 dstl.loc[['UK', 'DE', 'IT', 'FR', 'ES']]
 785 ```
 786
 787 ```python Collapsed="false"
 788 data_since_threshold.loc[(slice(None), ['UK']), :].tail(20)
 789 ```
 790
 791 ```python Collapsed="false"
 792 data_since_threshold.loc[(slice(None), ['ES']), :].tail(20)
 793 ```
 794
 795 <!-- #region Collapsed="false" -->
 796 ## Death projections
 797 <!-- #endregion -->
 798
 799 ```python Collapsed="false"
 800 data_since_threshold.loc[(slice(None), ['UK']), :].tail(15)
 801 ```
 802
 803 ```python Collapsed="false"
 804 it_since_threshold = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['IT']), :]
 805 s_end = it_since_threshold.index.max()[0]
 806 s_end
 807 ```
 808
 809 ```python Collapsed="false"
 810 uk_projection = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), :]
 811 uk_current_end = uk_projection.index.max()[0] + 1
 812 # s_start = uk_projection.index.max()[0] + 1
 813 uk_current_end
 814 ```
 815
 816 ```python Collapsed="false"
 817 current_uk_deaths_m7 = uk_projection[uk_projection.deaths_m7 >= 0].iloc[-1].deaths_m7
 818 current_uk_deaths_m7
 819 ```
 820
 821 ```python Collapsed="false"
 822 it_since_threshold[it_since_threshold.deaths_m7 <= current_uk_deaths_m7].loc[60:].first_valid_index()[0]
 823 ```
 824
 825 ```python Collapsed="false"
 826 s_start = it_since_threshold[it_since_threshold.deaths_m7 <= current_uk_deaths_m7].loc[60:].first_valid_index()[0]
 827 s_start
 828 ```
 829
 830 ```python Collapsed="false"
 831 s_start_date = data_since_threshold.loc[(89, 'IT'), 'dateRep']# .iloc[0]
 832 s_start_date
 833 ```
 834
 835 ```python Collapsed="false"
 836 s_end - s_start
 837 ```
 838
 839 ```python Collapsed="false"
 840 uk_end = s_end - s_start + uk_current_end
 841 uk_end
 842 ```
 843
 844 ```python Collapsed="false"
 845 proj = it_since_threshold.loc[(slice(s_start, s_end), slice(None)), ['cases', 'deaths', 'deaths_m7']]
 846 ndiff = uk_current_end - s_start
 847 proj.index = pd.MultiIndex.from_tuples([(n + ndiff, 'UK') for n, _ in proj.index], names=proj.index.names)
 848 proj
 849 ```
 850
 851 ```python Collapsed="false"
 852 it_since_threshold.loc[(slice(s_start - 8, s_start + 2), slice(None)), ['cases', 'deaths', 'deaths_m7']]
 853 ```
 854
 855 ```python Collapsed="false"
 856 uk_projection[['cases', 'deaths', 'deaths_m7']].tail()
 857 ```
 858
 859 ```python Collapsed="false"
 860 # proj['deaths_m7'] = proj['deaths_m7'] + 20
 861 # proj
 862 ```
 863
 864 <!-- #region Collapsed="false" -->
 865 Projected deaths, UK following IT trend from now.
 866 <!-- #endregion -->
 867
 868 ```python Collapsed="false"
 869 uk_projection = uk_projection.append(proj, sort=True)
 870 uk_projection.deaths.sum()
 871 ```
 872
 873 ```python Collapsed="false"
 874 uk_projection = uk_projection.droplevel(1)
 875 uk_projection
 876 ```
 877
 878 ```python Collapsed="false"
 879 uk_projection.loc[152, 'deaths']
 880 ```
 881
 882 <!-- #region Collapsed="false" -->
 883 ## Correction for cumulative deaths correction on 14 August
 884 <!-- #endregion -->
 885
 886 ```python Collapsed="false"
 887 # uk_projection.loc[152, 'deaths'] = 50
 888 ```
 889
 890 ```python Collapsed="false"
 891 uk_projection['deaths_m7'] = uk_projection['deaths'].transform(lambda x: x.rolling(7, 1).mean())
 892 uk_projection.loc[(uk_current_end - 20):(uk_current_end + 5)]
 893 ```
 894
 895 ```python Collapsed="false"
 896 uk_projection.loc[(uk_current_end - 5):]
 897 ```
 898
 899 ```python Collapsed="false"
 900 uk_projection.deaths_m7.plot()
 901 ```
 902
 903 ```python Collapsed="false"
 904 proj.droplevel(level=1)
 905 ```
 906
 907 ```python Collapsed="false"
 908 ax = deaths_m7[COUNTRIES_CORE].plot()
 909 # uk_projection['deaths_m7'].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average", label="Projection", style='--', ax=ax)
 910 proj.droplevel(level=1)['deaths_m7'].plot(figsize=(10, 6), title="Deaths per day, 7 day moving average", label="Projection", style='--', ax=ax)
 911 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
 912 for c in COUNTRIES_CORE:
 913     lvi = deaths_m7[c].last_valid_index()
 914     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = c)
 915 # plt.savefig('covid_deaths_per_day_7.png')
 916 ```
 917
 918 ```python Collapsed="false"
 919 it_since_threshold.deaths.sum()
 920 ```
 921
 922 <!-- #region Collapsed="false" -->
 923 # Excess deaths calculation
 924 <!-- #endregion -->
 925
 926 ```python Collapsed="false"
 927 with open('excess_deaths.json') as f:
 928     excess_deaths_data = json.load(f)
 929
 930 # with open('excess_death_accuracy.json') as f:
 931 #     excess_death_accuracy = json.load(f)
 932
 933 excess_deaths_data
 934 ```
 935
 936 ```python Collapsed="false"
 937 additional_deaths = data_by_date.loc[('UK', excess_deaths_data['end_date']):('UK')].iloc[1:].deaths.sum()
 938 additional_deaths
 939 ```
 940
 941 ```python Collapsed="false"
 942 uk_covid_deaths = data_by_date.loc['UK'].deaths.sum()
 943 uk_covid_deaths
 944 ```
 945
 946 ```python Collapsed="false"
 947 uk_deaths_to_date = int(excess_deaths_data['excess_deaths']) + additional_deaths
 948 uk_deaths_to_date
 949 ```
 950
 951 ```python Collapsed="false"
 952 # excess_deaths_upto = '2020-05-08'
 953 # excess_deaths = 54500
 954 ```
 955
 956 ```python Collapsed="false"
 957 # excess_deaths_upto = excess_deaths_data['end_date']
 958 # excess_deaths = excess_deaths_data['excess_deaths']
 959 ```
 960
 961 <!-- #region Collapsed="false" -->
 962 Recorded deaths in period where ONS has reported total deaths
 963 <!-- #endregion -->
 964
 965 ```python Collapsed="false"
 966 # ons_reported_deaths = base_data.loc['UK'][:excess_deaths_upto]['deaths'].sum()
 967 # ons_reported_deaths
 968 ```
 969
 970 ```python Collapsed="false"
 971 # excess_deaths_upto
 972 ```
 973
 974 <!-- #region Collapsed="false" -->
 975 ## Correction for deaths total correction on 14 August
 976 <!-- #endregion -->
 977
 978 ```python Collapsed="false"
 979 # ons_unreported_deaths_data = base_data.loc['UK'][excess_deaths_upto:].iloc[1:]['deaths']
 980 # ons_unreported_deaths_data['2020-08-14'] = 50
 981 ```
 982
 983 ```python Collapsed="false"
 984 # ons_unreported_deaths = ons_unreported_deaths_data.sum()
 985 # ons_unreported_deaths
 986 ```
 987
 988 ```python Collapsed="false"
 989 # scaled_ons_unreported_deaths = ons_unreported_deaths * excess_death_accuracy
 990 # scaled_ons_unreported_deaths
 991 ```
 992
 993 ```python Collapsed="false"
 994 # uk_deaths_to_date = excess_deaths + scaled_ons_unreported_deaths
 995 # uk_deaths_to_date
 996 ```
 997
 998 ```python Collapsed="false"
 999 # data_since_threshold.loc[(slice(None), 'UK'), :][data_since_threshold.dateRep == excess_deaths_data['end_date']]
1000 ```
1001
1002 ```python Collapsed="false"
1003 # data_since_threshold[data_since_threshold.dateRep == excess_deaths_data['end_date']].loc[(slice(None), 'UK'), :]
1004 ```
1005
1006 ```python Collapsed="false"
1007 # ons_unreported_start = data_since_threshold[data_since_threshold.dateRep == excess_deaths_data['end_date']].loc[(slice(None), 'UK'), :].first_valid_index()[0] + 1
1008 # ons_unreported_start
1009 ```
1010
1011 ```python Collapsed="false"
1012 # unreported_projected_deaths = uk_projection.loc[ons_unreported_start:].deaths.sum()
1013 # unreported_projected_deaths
1014 ```
1015
1016 ```python Collapsed="false"
1017 # unreported_projected_deaths_scaled = unreported_projected_deaths * excess_death_accuracy
1018 # unreported_projected_deaths_scaled
1019 ```
1020
1021 ```python Collapsed="false"
1022 # uk_projection.loc[(s_start):].deaths.sum()
1023 ```
1024
1025 ```python Collapsed="false"
1026 # deaths_actual_projected_scaled = uk_deaths_to_date + uk_projection.loc[(s_start):].deaths.sum() * excess_death_accuracy
1027 # deaths_actual_projected_scaled
1028 ```
1029
1030 ```python Collapsed="false"
1031 # excess_deaths / reported_deaths
1032 ```
1033
1034 <!-- #region Collapsed="false" -->
1035 True deaths to date, if we follow the scaling of excess deaths over reported deaths so far.
1036 <!-- #endregion -->
1037
1038 ```python Collapsed="false"
1039 # uk_covid_deaths = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'deaths_culm'].iloc[-1]
1040 # uk_covid_deaths
1041 ```
1042
1043 ```python Collapsed="false"
1044 # uk_covid_deaths_scaled = excess_deaths + unreported_deaths * excess_death_accuracy
1045 # uk_covid_deaths_scaled
1046 ```
1047
1048 ```python Collapsed="false"
1049 # 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')
1050 ```
1051
1052 ```python Collapsed="false"
1053 # data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'dateRep'].iloc[-1].strftime("%Y-%m-%d")
1054 ```
1055
1056 ```python Collapsed="false"
1057 # uk_covid_deaths * excess_deaths / reported_deaths
1058 ```
1059
1060 ```python Collapsed="false"
1061 # uk_projection.deaths.sum() * excess_deaths / reported_deaths
1062 ```
1063
1064 ```python Collapsed="false"
1065 # data_since_threshold.loc[(slice(None), 'FR'), :]
1066 # data_since_threshold[data_since_threshold.dateRep == '2020-05-18'].loc[(slice(None), 'FR'), :]
1067 ```
1068
1069 <!-- #region Collapsed="false" -->
1070 ## School reopenings
1071 <!-- #endregion -->
1072
1073 ```python Collapsed="false"
1074 school_reopenings = {
1075     'ES': {'date': '2020-05-18'},
1076     'FR': {'date': '2020-05-18'}, # some areas only
1077 #     'IT': {'date': '2020-09-01'},
1078     # 'IE': {'date': '2020-09-01'},
1079     'DE': {'date': '2020-05-04'},
1080     'UK': {'date': '2020-06-01'}
1081 }
1082 ```
1083
1084 ```python Collapsed="false"
1085 data_since_threshold[data_since_threshold.dateRep == '2020-05-04'].loc[(slice(None), ['DE']), :].first_valid_index()
1086 ```
1087
1088 ```python Collapsed="false"
1089 data_since_threshold[data_since_threshold.dateRep == '2020-05-04'].loc[(slice(None), ['DE']), :].iloc[0].deaths_m7
1090 ```
1091
1092 ```python Collapsed="false"
1093 for cID in school_reopenings:
1094     dst_in = data_since_threshold[data_since_threshold.dateRep == (school_reopenings[cID]['date'])].loc[(slice(None), [cID]), :]
1095     dst_i = dst_in.first_valid_index()
1096     dst_n = dst_in.iloc[0].deaths_m7
1097     school_reopenings[cID]['since_threshold'] = dst_i[0]
1098     school_reopenings[cID]['deaths_m7'] = dst_n
1099 school_reopenings
1100 ```
1101
1102 ```python Collapsed="false"
1103 ax = deaths_m7[COUNTRIES_CORE].plot(figsize=(15, 9), title="Deaths per day, 7 day moving average")
1104 # uk_projection.deaths_m7.plot(ax=ax)
1105 for c in COUNTRIES_CORE:
1106     lvi = deaths_m7[c].last_valid_index()
1107     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = f"{c}: {deaths_m7[c][lvi]:.0f}")
1108     if c in school_reopenings:
1109         marker_col = [l for l in ax.lines if l.get_label() == c][0].get_color()
1110         ax.plot(school_reopenings[c]['since_threshold'], school_reopenings[c]['deaths_m7'], '*',
1111                 markersize=18, markerfacecolor=marker_col, markeredgecolor=marker_col)
1112         ax.text(x = school_reopenings[c]['since_threshold'] + 1, y = school_reopenings[c]['deaths_m7'],
1113                 s = f"{school_reopenings[c]['date']}: {school_reopenings[c]['deaths_m7']:.0f}")
1114 plt.savefig('school_reopenings.png')
1115 ```
1116
1117 ```python Collapsed="false"
1118 # ax = deaths_m7[COUNTRIES_CORE].plot(figsize=(15, 9), title="Deaths per day, 7 day moving average",
1119 #                                    xlim=(46, 91), ylim=(0, 400))
1120 # # uk_projection.deaths_m7.plot(ax=ax)
1121 # for c in COUNTRIES_CORE:
1122 #     lvi = deaths_m7[c].last_valid_index()
1123 #     ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = f"{c}: {deaths_m7[c][lvi]:.0f}", fontsize=14)
1124 #     if c in school_reopenings:
1125 #         marker_col = [l for l in ax.lines if l.get_label() == c][0].get_color()
1126 #         ax.plot(school_reopenings[c]['since_threshold'], school_reopenings[c]['deaths_m7'], '*',
1127 #                 markersize=18, markerfacecolor=marker_col, markeredgecolor=marker_col)
1128 #         ax.text(x = school_reopenings[c]['since_threshold'] + 1, y = school_reopenings[c]['deaths_m7'],
1129 #                 s = f"{school_reopenings[c]['date']}: {school_reopenings[c]['deaths_m7']:.0f}",
1130 #                 fontsize=14)
1131 # plt.savefig('school_reopenings_detail.png')
1132 ```
1133
1134 <!-- #region Collapsed="false" -->
1135 # Lockdown graphs
1136 <!-- #endregion -->
1137
1138 ```python Collapsed="false"
1139 lockdown_dates = {
1140     'ES': { 'part_start': {'date': '2020-03-14'}
1141           , 'full_start': {'date': '2020-03-15'}
1142           , 'part_finish': {'date': '2020-05-18'}
1143           },
1144     'FR': { 'part_start': {'date': '2020-03-13'}
1145           , 'full_start': {'date': '2020-03-17'}
1146           , 'part_finish': {'date': '2020-05-11'}
1147           },
1148     'IT': { 'part_start': {'date': '2020-03-08'}
1149           , 'full_start': {'date': '2020-03-10'}
1150           , 'part_finish': {'date': '2020-05-04'}
1151           },
1152     'DE': { #'part_start': {'date': '2020-03-13'}
1153           'full_start': {'date': '2020-03-22'}
1154           , 'part_finish': {'date': '2020-05-06'}
1155           },
1156     'UK': { 'part_start': {'date': '2020-03-23'}
1157           , 'full_start': {'date': '2020-03-23'}
1158           , 'part_finish': {'date': '2020-05-31'}
1159           },
1160     'IE': { #'part_start': {'date': '2020-03-12'}
1161           'full_start': {'date': '2020-03-27'}
1162           , 'part_finish': {'date': '2020-05-18'}
1163           },
1164 }
1165 ```
1166
1167 ```python Collapsed="false"
1168 for cID in lockdown_dates:
1169     for phase in lockdown_dates[cID]:
1170         dst_in = data_since_threshold[data_since_threshold.dateRep == (lockdown_dates[cID][phase]['date'])].loc[(slice(None), [cID]), :]
1171         dst_i = dst_in.first_valid_index()
1172         dst_n = dst_in.iloc[0].deaths_m7
1173         dst_c = dst_in.iloc[0].cases_m7
1174         lockdown_dates[cID][phase]['since_threshold'] = dst_i[0]
1175         lockdown_dates[cID][phase]['deaths_m7'] = dst_n
1176         lockdown_dates[cID][phase]['cases_m7'] = dst_c
1177
1178 lockdown_dates
1179 ```
1180
1181 ```python Collapsed="false"
1182 ax = deaths_m7[COUNTRIES_CORE].plot(figsize=(15, 9), title="Deaths per day, 7 day moving averagee, with lockdown dates")
1183 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
1184 # uk_projection.deaths_m7.plot(ax=ax)
1185 for c in COUNTRIES_CORE:
1186     lvi = deaths_m7[c].last_valid_index()
1187     if c != 'UK':
1188         ax.text(x = lvi + 1, y = deaths_m7[c][lvi], s = f"{c}: {deaths_m7[c][lvi]:.0f}")
1189     if c in lockdown_dates:
1190         for phase in lockdown_dates[c]:
1191             marker_col = [l for l in ax.lines if l.get_label() == c][0].get_color()
1192             ax.plot(lockdown_dates[c][phase]['since_threshold'], lockdown_dates[c][phase]['deaths_m7'], '*',
1193                     markersize=18, markerfacecolor=marker_col, markeredgecolor=marker_col)
1194             if 'start' not in phase:
1195                 ax.text(x = lockdown_dates[c][phase]['since_threshold'] + 1, y = lockdown_dates[c][phase]['deaths_m7'],
1196                         s = f"{lockdown_dates[c][phase]['date']}: {lockdown_dates[c][phase]['deaths_m7']:.0f}")
1197 # plt.savefig('school_reopenings.png')
1198 ```
1199
1200 ```python Collapsed="false"
1201 ax = cases_m7.iloc[-50:][COUNTRIES_CORE].plot(figsize=(15, 9), title="Cases per day, 7 day moving average, with lockdown dates") #, ylim=(-10, 1500))
1202 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
1203 # uk_projection.deaths_m7.plot(ax=ax)
1204 for c in COUNTRIES_CORE:
1205     lvi = cases_m7[c].last_valid_index()
1206 #     if c != 'UK':
1207     ax.text(x = lvi + 1, y = cases_m7[c][lvi], s = f"{c}: {cases_m7[c][lvi]:.0f}")
1208
1209 ```
1210
1211 ```python Collapsed="false"
1212 ax = cases_m7[COUNTRIES_CORE].plot(figsize=(15, 9), title="Cases per day, 7 day moving average, with lockdown dates")
1213 ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
1214 # uk_projection.deaths_m7.plot(ax=ax)
1215 for c in COUNTRIES_CORE:
1216     lvi = cases_m7[c].last_valid_index()
1217 #     if c != 'UK':
1218     ax.text(x = lvi + 1, y = cases_m7[c][lvi], s = f"{c}: {cases_m7[c][lvi]:.0f}")
1219     if c in lockdown_dates:
1220         for phase in lockdown_dates[c]:
1221             marker_col = [l for l in ax.lines if l.get_label() == c][0].get_color()
1222             if 'start' in phase:
1223                 marker_shape = '^'
1224             else:
1225                 marker_shape = 'v'
1226             ax.plot(lockdown_dates[c][phase]['since_threshold'], lockdown_dates[c][phase]['cases_m7'],
1227                     marker_shape,
1228                     markersize=18, markerfacecolor=marker_col, markeredgecolor=marker_col)
1229             if 'start' not in phase:
1230                 ax.text(x = lockdown_dates[c][phase]['since_threshold'] + 1, y = lockdown_dates[c][phase]['cases_m7'],
1231                         s = f"{lockdown_dates[c][phase]['date']}: {lockdown_dates[c][phase]['cases_m7']:.0f}")
1232 # plt.savefig('cases_per_day_with_lockdown.png')
1233 ```
1234
1235 ```python Collapsed="false"
1236 plot_start_date = '2020-03-01'
1237 ax = cases_by_date_m7.loc[plot_start_date:, COUNTRIES_CORE].plot(figsize=(15, 9), title="Cases per day, 7 day moving average, with lockdown dates")
1238 ax.set_xlabel(f"Date")
1239 ax.set_ylabel("Number of cases")
1240 # uk_projection.deaths_m7.plot(ax=ax)
1241 for c in COUNTRIES_CORE:
1242     lvi = cases_by_date_m7[c].last_valid_index()
1243 #     if c != 'UK':
1244     ax.text(x = lvi + pd.Timedelta(days=1), y = cases_by_date_m7[c][lvi], s = f"{c}: {cases_by_date_m7[c][lvi]:.0f}")
1245     if c in lockdown_dates:
1246         for phase in lockdown_dates[c]:
1247             marker_col = [l for l in ax.lines if l.get_label() == c][0].get_color()
1248             if 'start' in phase:
1249                 marker_shape = '^'
1250             else:
1251                 marker_shape = 'v'
1252             marker_x_pos = ax.get_xlim()[0] + mpl.dates.date2num(pd.to_datetime(lockdown_dates[c][phase]['date'])) - mpl.dates.date2num(pd.to_datetime(plot_start_date))
1253             ax.plot(marker_x_pos, lockdown_dates[c][phase]['cases_m7'],
1254                     marker_shape,
1255                     markersize=18, markerfacecolor=marker_col, markeredgecolor=marker_col)
1256             if 'start' not in phase:
1257                 ax.text(x = marker_x_pos + 3, y = lockdown_dates[c][phase]['cases_m7'],
1258                         s = f"{lockdown_dates[c][phase]['date']}: {lockdown_dates[c][phase]['cases_m7']:.0f}")
1259 plt.savefig('cases_per_day_with_lockdown.png')
1260 ```
1261
1262 ```python Collapsed="false"
1263 ax = cases_m7[COUNTRIES_CORE].plot(figsize=(10, 6), title="Cases per day, 7 day moving average")
1264 for c in COUNTRIES_CORE:
1265     lvi = cases_m7[c].last_valid_index()
1266     ax.text(x = lvi + 1, y = cases_m7[c][lvi], s = c)
1267 plt.savefig('covid_cases_per_day-core.png')
1268 ```
1269
1270 ```python Collapsed="false"
1271 ax = deaths_m7[COUNTRIES_CORE].plot(figsize=(15, 9), title="Deaths per day, 7 day moving average",
1272                                    xlim=(0, 15),
1273                                     ylim=(0, 66)
1274                                    )
1275 # uk_projection.deaths_m7.plot(ax=ax)
1276 for c in COUNTRIES_CORE:
1277     lvi = deaths_m7[c].last_valid_index()
1278     if c in lockdown_dates:
1279         for phase in lockdown_dates[c]:
1280             if 'start' in phase:
1281                 print(c, phase)
1282                 marker_col = [l for l in ax.lines if l.get_label() == c][0].get_color()
1283                 ax.plot(lockdown_dates[c][phase]['since_threshold'], lockdown_dates[c][phase]['deaths_m7'], '*',
1284                         markersize=18, markerfacecolor=marker_col, markeredgecolor=marker_col)
1285                 ax.text(x = lockdown_dates[c][phase]['since_threshold'] + 0.3, y = lockdown_dates[c][phase]['deaths_m7'],
1286                         s = f"{lockdown_dates[c][phase]['date']}: {lockdown_dates[c][phase]['deaths_m7']:.0f}")
1287 # plt.savefig('school_reopenings.png')
1288 ```
1289
1290 ```python Collapsed="false"
1291
1292 ```
1293
1294 ```python Collapsed="false"
1295
1296 ```
1297
1298 <!-- #region Collapsed="false" -->
1299 # Write results to summary file
1300 <!-- #endregion -->
1301
1302 ```python Collapsed="false"
1303 with open('covid_summary.md', 'w') as f:
1304     f.write('% Covid death data summary\n')
1305     f.write('% Neil Smith\n')
1306     f.write(f'% Created on {datetime.datetime.now().strftime("%Y-%m-%d")}\n')
1307     f.write('\n')
1308
1309     last_uk_date = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'dateRep'].iloc[-1]
1310     f.write(f'> Last UK data from {last_uk_date.strftime("%Y-%m-%d")}\n')
1311     f.write('\n')
1312 ```
1313
1314 ```python Collapsed="false"
1315 with open('covid_summary.md', 'a') as f:
1316     f.write('## Headlines\n')
1317     f.write('\n')
1318     f.write('| []() | |\n')
1319     f.write('|:---|---:|\n')
1320     f.write(f'| Deaths reported so far | {uk_covid_deaths} | \n')
1321     f.write(f'| Total Covid deaths to date (estimated) | {uk_deaths_to_date:.0f} |\n')
1322     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')
1323 #     f.write(f'| Projected total deaths up to {projection_date.strftime("%Y-%m-%d")} | {deaths_actual_projected_scaled:.0f} | \n')
1324     f.write('\n')
1325 ```
1326
1327 ```python Collapsed="false"
1328 with open('covid_summary.md', 'a') as f:
1329     f.write('## Total deaths\n')
1330 #     f.write(f'Time based on days since {DEATH_COUNT_THRESHOLD} deaths\n')
1331     f.write('\n')
1332     f.write('![Total deaths](covid_deaths_total_linear.png)\n')
1333     f.write('\n')
1334     f.write('| Country ID | Country name | Total deaths |\n')
1335     f.write('|:-----------|:-------------|-------------:|\n')
1336     for c in sorted(COUNTRIES_CORE):
1337         lvi = deaths_by_date[c].last_valid_index()
1338         f.write(f'| {c} | {countries.loc[c].countriesAndTerritories} | {int(deaths_by_date[c][lvi])} |\n')
1339     f.write('\n')
1340 ```
1341
1342 ```python Collapsed="false"
1343 with open('covid_summary.md', 'a') as f:
1344     f.write('## All-causes deaths, UK\n')
1345     f.write('\n')
1346     f.write('![All-causes deaths](deaths-radar.png)\n')
1347     f.write('\n')
1348     f.write('### True deaths\n')
1349     f.write('\n')
1350     f.write(f'The number of deaths reported in official statistics, {uk_covid_deaths}, is an underestimate '
1351             'of the true number of Covid deaths.\n'
1352             'This is especially true early in the pandemic, approximately March to May 2020.\n')
1353     f.write('We can get a better understanding of the impact of Covid by looking at the number of deaths, '
1354             'over and above what would be expected at each week of the year.\n')
1355     f.write(f'The ONS (and other bodies in Scotland and Northern Ireland) have released data on the number of deaths '
1356             f'up to {pd.to_datetime(excess_deaths_data["end_date"]).strftime("%d %B %Y")}.\n\n')
1357     f.write('If, for each of those weeks, I take the largest of the excess deaths or the reported Covid deaths, ')
1358     f.write(f'I estimate there have been **{uk_deaths_to_date}** total deaths so far.\n')
1359     f.write('\n')
1360 ```
1361
1362 ```python Collapsed="false"
1363 # with open('covid_summary.md', 'a') as f:
1364 #     f.write(f'In that period, the UK reported {ons_reported_deaths} Covid deaths.\n')
1365 #     f.write(f'In the last three weeks for which excess deaths have been reported, the excess deaths have been {excess_death_accuracy:.3f} higher than the Covid-reported deaths.\n')
1366 # #     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')
1367 #     f.write('\n')
1368 #     f.write(f'The UK has reported {uk_covid_deaths} deaths so far.\n')
1369 #     f.write(f'Using the scaling factor above (for Covid-19 deaths after the ONS figures), I infer that there have been **{uk_deaths_to_date:.0f}** total deaths so far.\n')
1370 #     f.write('\n')
1371 ```
1372
1373 ```python Collapsed="false"
1374 with open('covid_summary.md', 'a') as f:
1375     f.write('## Deaths per day\n')
1376     f.write(f'Based on a 7-day moving average\n')
1377     f.write('\n')
1378     f.write('![Deaths per day](covid_deaths_per_day_7.png)\n')
1379     f.write('\n')
1380     f.write('![Deaths per day, last 30 days](deaths_by_date_last_30_days.png)\n')
1381     f.write('\n')
1382 ```
1383
1384 ```python Collapsed="false"
1385 s_end - s_start - 1
1386 ```
1387
1388 ```python Collapsed="false"
1389 with open('covid_summary.md', 'a') as f:
1390     f.write('## Projected deaths\n')
1391     f.write("Previously, I was using Italy's deaths data to predict the UK's deaths data. "
1392             "This worked when both countries' trends of deaths were falling or constant, "
1393             "as they were until September.\n")
1394     f.write("\n")
1395     f.write("As of mid-September, with cases rising in both countries at around the same time, "
1396             "I can't use Italian data to predict the UK's future deaths.\n")
1397     f.write("\n")
1398 #     f.write(f"The UK's daily deaths data is very similar to Italy's.\n")
1399 #     f.write(f'If I use the Italian data for the next {s_end - s_start - 1} days (from {s_start_date.strftime("%d %B %Y")} onwards),')
1400 #     f.write(f' the UK will report {uk_projection.deaths.sum()} deaths on day {uk_end} of the epidemic.\n')
1401 #     f.write('\n')
1402 #     f.write('Using the excess deaths scaling from above, that will translate into ')
1403 #     f.write(f'**{deaths_actual_projected_scaled:.0f}** Covid deaths total.\n')
1404 #     f.write('\n')
1405 ```
1406
1407 ```python Collapsed="false"
1408 with open('covid_summary.md', 'a') as f:
1409     f.write('## Deaths doubling times\n')
1410     f.write(f'Based on a 7-day moving average\n')
1411     f.write('\n')
1412     f.write('![Deaths doubling times](covid_doubling_times_7.png)\n')
1413     f.write('\n')
1414 ```
1415
1416 ```python Collapsed="false"
1417 with open('covid_summary.md', 'a') as f:
1418     f.write('\n')
1419     f.write('## Cases per day and lockdown dates\n')
1420     f.write(f'Based on a 7-day moving average\n')
1421     f.write('\n')
1422     f.write('![Cases per day](cases_per_day_with_lockdown.png)\n')
1423     f.write('\n')
1424 ```
1425
1426 ```python Collapsed="false"
1427 with open('covid_summary.md', 'a') as f:
1428     f.write('| Country ID | Country name | Most recent daily cases | Most recent daily deaths |\n')
1429     f.write('|:-----------|:-------------|------------------------:|-------------------------:|\n')
1430     for c in sorted(COUNTRIES_CORE):
1431         lvic = cases_m7[c].last_valid_index()
1432         lvid = deaths_m7[c].last_valid_index()
1433         f.write(f'| {c} | {countries.loc[c].countriesAndTerritories} | {cases_m7[c][lvic]:.0f} | {deaths_m7[c][lvid]:.0f} | \n')
1434     f.write('\n')
1435     f.write('(Figures are 7-day averages)\n')
1436     f.write('\n')
1437 ```
1438
1439 ```python Collapsed="false"
1440 with open('hospital_normalisation_date.json') as f:
1441     hospital_normalisation_date_data = json.load(f)
1442 ```
1443
1444 ```python Collapsed="false"
1445 with open('covid_summary.md', 'a') as f:
1446     f.write('## Hospital care\n')
1447     f.write(f'Based on a 7-day moving average\n')
1448     f.write('\n')
1449     f.write('![Cases, admissions, deaths](cases_admissions_deaths.png)\n')
1450     f.write('\n')
1451 #     f.write('Admissions are shifted by 10 days, deaths by 25 days. '
1452 #             'This reflects the typical timescales of infection: '
1453 #             'patients are admitted 10 days after onset of symptoms, '
1454 #             'and die 15 days after admission.\n')
1455 #     f.write('\n')
1456 #     f.write('Plotting this data with offsets shows more clearly '
1457 #             'the relative changes in these three metrics.\n')
1458     f.write('Due to the large scale differences between the three '
1459             'measures, they are all normalised to show changes ')
1460     f.write(f'since {pd.to_datetime(hospital_normalisation_date_data["hospital_normalisation_date"]).strftime("%d %B %Y")}.\n')
1461     f.write('\n')
1462 ```
1463
1464 ```python Collapsed="false"
1465 with open('covid_summary.md', 'a') as f:
1466     f.write('## Testing effectiveness\n')
1467     f.write('\n')
1468     f.write('A question about testing is whether more detected cases is a result of more tests being '
1469             'done or is because the number of cases is increasing. One way of telling the differeence '
1470             'is by looking at the fraction of tests that are positive.\n')
1471     f.write('\n')
1472     f.write('![Positive tests and cases](tests_and_cases.png)\n')
1473     f.write('\n')
1474     f.write('Numbers of positive tests and cases, '
1475             '7-day moving average.\n'
1476             'Note the different y-axes\n')
1477     f.write('\n')
1478     f.write('![Fraction of tests with positive result](fraction_positive_tests.png)\n')
1479     f.write('\n')
1480     f.write('Fraction of tests with a positive result, both daily figures and '
1481             '7-day moving average.\n')
1482     f.write('\n')
1483     f.write('\n')
1484     f.write('![Tests against fraction positive, trajectory](fraction_positive_tests_vs_tests.png)\n')
1485     f.write('\n')
1486     f.write('The trajectory of tests done vs fraction positive tests.\n')
1487     f.write('\n')
1488     f.write('Points higher indicate more tests; points to the right indicate more positive tests.'
1489             'More tests being done with the same infection prevelance will move the point up '
1490             'and to the left.\n')
1491     f.write('\n')
1492     f.write('\n')
1493     f.write('![Tests against fraction positive, trajectory](tests_vs_fraction_positive_animation.png)\n')
1494     f.write('\n')
1495 ```
1496
1497 ```python Collapsed="false"
1498
1499 ```
1500
1501 ```python Collapsed="false"
1502 with open('covid_summary.md', 'a') as f:
1503     f.write('# Data sources\n')
1504     f.write('\n')
1505     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')
1506     f.write('\n')
1507     f.write("""> Population data from:
1508
1509 * [Office of National Statistics](https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/weeklyprovisionalfiguresondeathsregisteredinenglandandwales) (Endland and Wales) Weeks start on a Saturday.
1510 * [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.
1511 * [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.""")
1512
1513     f.write('\n\n')
1514     f.write('> [Source code available](https://git.njae.me.uk/?p=covid19.git;a=tree)\n')
1515     f.write('\n')
1516
1517 ```
1518
1519 ```python Collapsed="false"
1520 !pandoc --toc -s covid_summary.md > covid_summary.html
1521 ```
1522
1523 ```python Collapsed="false"
1524 !scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html
1525 !scp covid_deaths_total_linear.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1526 !scp deaths-radar.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1527 !scp covid_deaths_per_day_7.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1528 !scp covid_doubling_times_7.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1529 !scp cases_per_day_with_lockdown.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1530 !scp cases_admissions_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1531 !scp fraction_positive_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1532 !scp tests_and_cases.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1533 !scp deaths_by_date_last_30_days.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1534 !scp fraction_positive_tests_vs_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1535 !scp tests_vs_fraction_positive_animation.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1536 ```
1537
1538 ```python Collapsed="false"
1539 with open('uk_covid_deaths.js', 'w') as f:
1540     f.write(f"document.write('{uk_covid_deaths}');")
1541
1542 with open('estimated_total_deaths.js', 'w') as f:
1543     f.write(f"document.write('{uk_deaths_to_date:.0f}');")
1544
1545 # with open('projection_date.js', 'w') as f:
1546 #     f.write(f"document.write(\'{projection_date.strftime('%d %B %Y')}\');")
1547
1548 # with open('projected_deaths.js', 'w') as f:
1549 #     f.write(f"document.write('{uk_projection.deaths.sum():.0f}');")
1550
1551 # with open('projected_excess_deaths.js', 'w') as f:
1552 #     f.write(f"document.write('{deaths_actual_projected_scaled:.0f}');")
1553
1554 edut = pd.to_datetime(excess_deaths_data["end_date"]).strftime('%d %B %Y')
1555 with open('excess_deaths_upto.js', 'w') as f:
1556     f.write(f"document.write('{edut}');")
1557
1558 # with open('excess_deaths.js', 'w') as f:
1559 #     f.write(f"document.write('{excess_deaths:.0f}');")
1560
1561 # with open('reported_deaths.js', 'w') as f:
1562 #     f.write(f"document.write('{ons_reported_deaths:.0f}');")
1563
1564 # with open('scaling_factor.js', 'w') as f:
1565 #     f.write(f"document.write('{excess_death_accuracy:.2f}');")
1566
1567 # with open('projection_length.js', 'w') as f:
1568 #     f.write(f"document.write('{s_end - s_start - 1}');")
1569
1570 # with open('s_end.js', 'w') as f:
1571 #     f.write(f"document.write('{s_end}');")
1572
1573 # s_start_date_str = s_start_date.strftime("%d %B %Y")
1574 # with open('s_start_date.js', 'w') as f:
1575 #     f.write(f"document.write('{s_start_date_str}');")
1576
1577 # with open('uk_end.js', 'w') as f:
1578 #     f.write(f"document.write('{uk_end}');")
1579
1580 with open('last_uk_date.js', 'w') as f:
1581     f.write(f"document.write('{pd.to_datetime(last_uk_date).strftime('%d %B %Y')}');")
1582 ```
1583
1584 ```python Collapsed="false"
1585 # pd.to_datetime(excess_deaths_upto).strftime('%d %B %Y')
1586 ```
1587
1588 ```python Collapsed="false"
1589 !scp uk_covid_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1590 !scp estimated_total_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1591 # !scp projection_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1592 # !scp projected_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1593 # !scp projected_excess_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1594 !scp excess_deaths_upto.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1595 # !scp excess_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1596 # !scp reported_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1597 # !scp scaling_factor.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1598 # !scp projection_length.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1599 # !scp s_end.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1600 # !scp s_start_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1601 # !scp uk_end.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1602 !scp last_uk_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1603 !scp hospital_normalisation_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/
1604 ```
1605
1606 ```python Collapsed="false"
1607 data_by_date.loc['UK'].to_csv('data_by_day_uk.csv', header=True, index=True)
1608 data_by_date.loc['BE'].to_csv('data_by_day_be.csv', header=True, index=True)
1609 ```
1610
1611 ```python Collapsed="false"
1612 ukd = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), ['deaths', 'deaths_m7']].droplevel(1)
1613 ax = ukd.deaths.plot.bar(figsize=(12, 8))
1614 ukd.deaths_m7.plot.line(ax=ax, color='red')
1615 # ax = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'deaths_m7'].plot.line(figsize=(12, 8), color='red')
1616 # ax = data_since_threshold.replace([np.inf, -np.inf], np.nan).loc[(slice(None), ['UK']), 'deaths'].plot.bar(ax=ax)
1617 ax.set_xticks(range(0, 120, 20))
1618 ```
1619
1620 ```python Collapsed="false"
1621 ukdd = data_by_date.loc['UK'].iloc[-30:]
1622 ax = ukdd.deaths_m7.plot.line(figsize=(12, 8), color='red')
1623 # ukdd.deaths.plot.bar(ax=ax)
1624 ax.bar(ukdd.index, ukdd.deaths)
1625 ```
1626
1627 ```python Collapsed="false"
1628 ukdd
1629 ```
1630
1631 ```python Collapsed="false"
1632 np.arange(0, 130, 20)
1633 ```
1634
1635 ```python Collapsed="false"
1636 data_by_date.loc['UK']
1637 ```
1638
1639 ```python Collapsed="false"
1640 data_by_date.loc['UK'].plot(x='deaths_culm', y='deaths', logx=True, logy=True)
1641 ```
1642
1643 ```python Collapsed="false"
1644 data_by_date.loc['UK'].plot(x='cases_culm', y='cases')
1645 ```
1646
1647 ```python Collapsed="false"
1648 ukdbd = data_by_date.loc['UK'].copy()
1649 ukdbd['deaths_m7'] = ukdbd.deaths.transform(lambda x: x.rolling(7, 1).mean())
1650 ukdbd['cases_m7'] = ukdbd.cases.transform(lambda x: x.rolling(7, 1).mean())
1651 ukdbd
1652 ```
1653
1654 ```python Collapsed="false"
1655 ukdbd.plot(x='deaths_culm', y='deaths_m7', logx=True, logy=True)
1656 ```
1657
1658 ```python Collapsed="false"
1659 fig, ax = plt.subplots(figsize=(12, 8))
1660 xmax = 10
1661 for c in COUNTRIES_CORE:
1662     if data_since_threshold.loc[(slice(None), c), 'deaths_culm'].max() > xmax:
1663         xmax = data_since_threshold.loc[(slice(None), c), 'deaths_culm'].max()
1664     data_since_threshold.loc[(slice(None), c), :].plot(x='deaths_culm', y='deaths_m7', logx=True, logy=True, xlim=(10, xmax * 1.1), label=c, ax=ax)
1665 ```
1666
1667 ```python Collapsed="false"
1668 data_since_threshold.loc[(slice(None), 'UK'), 'deaths_culm'].max()
1669 ```
1670
1671 ```python Collapsed="false"
1672 countries.continentExp.unique()
1673 ```
1674
1675 ```python Collapsed="false"
1676 countries.loc['KW']
1677 ```
1678
1679 ```python Collapsed="false"
1680 data_by_date.groupby(level=0)['deaths'].shift(-25)
1681 ```
1682
1683 ```python Collapsed="false"
1684 offset_data = data_by_date.loc[:, ['cases']]
1685 offset_data['deaths'] = data_by_date.groupby(level=0)['deaths'].shift(-25)
1686 offset_data['cases_m7'] = offset_data.groupby(level=0)['cases'].transform(lambda x: x.rolling(7, 1).mean())
1687 offset_data['deaths_m7'] = offset_data['deaths'].dropna().groupby(level=0).transform(lambda x: x.rolling(7, 1).mean())
1688 offset_data['deaths_per_case'] = offset_data.deaths_m7 / offset_data.cases_m7
1689 offset_data
1690 ```
1691
1692 ```python Collapsed="false"
1693 deaths_m7
1694 ```
1695
1696 ```python Collapsed="false"
1697 offset_deaths_m7 = (offset_data.loc[COUNTRIES_ALL, ['deaths_m7']]
1698              .unstack().sort_index().xs('deaths_m7', axis=1, drop_level=True)).T.sort_index()
1699 offset_deaths_m7
1700 ```
1701
1702 ```python Collapsed="false"
1703 offset_deaths_m7['UK']
1704 ```
1705
1706 ```python Collapsed="false"
1707 data_since_threshold.loc[(slice(None), 'UK'), :].tail()
1708 ```
1709
1710 ```python Collapsed="false"
1711 countries.loc['PT']
1712 ```
1713
1714 ```python Collapsed="false"
1715 ax = cases_by_date_m7.iloc[-50:][COUNTRIES_FRIENDS].plot(figsize=(15, 9), title="Cases per day, 7 day moving average")#, ylim=(-10, 1500))
1716 # ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
1717 # uk_projection.deaths_m7.plot(ax=ax)
1718 for c in COUNTRIES_FRIENDS:
1719     lvi = cases_by_date_m7[c].last_valid_index()
1720     ax.text(x = lvi + pd.Timedelta(days=1), y = cases_by_date_m7[c][lvi], s = f"{c}: {cases_by_date_m7[c][lvi]:.0f}")
1721
1722 ```
1723
1724 ```python Collapsed="false"
1725 ax = deaths_by_date_m7.iloc[-50:][COUNTRIES_FRIENDS].plot(figsize=(15, 9), title="Deaths per day, 7 day moving average")#, ylim=(-10, 100))
1726 # ax.set_xlabel(f"Days since {DEATH_COUNT_THRESHOLD} deaths")
1727 # uk_projection.deaths_m7.plot(ax=ax)
1728 for c in COUNTRIES_FRIENDS:
1729     lvi = deaths_by_date_m7[c].last_valid_index()
1730 #     if c != 'ES':
1731     ax.text(x = lvi + pd.Timedelta(days=1), y = deaths_by_date_m7[c][lvi], s = f"{c}: {deaths_by_date_m7[c][lvi]:.0f}")
1732 ```
1733
1734 ```python Collapsed="false"
1735
1736 ```