data_import.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.9.1
  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 from sqlalchemy.types import Integer, Text, String, DateTime, Date, Float
  22 from sqlalchemy import create_engine
  23 ```
  24
  25 ```python Collapsed="false"
  26 import itertools
  27 import collections
  28 import json
  29 import pandas as pd
  30 import numpy as np
  31 from scipy.stats import gmean
  32 import datetime
  33
  34 import matplotlib as mpl
  35 import matplotlib.pyplot as plt
  36 %matplotlib inline
  37 ```
  38
  39 ```python Collapsed="false"
  40 %load_ext sql
  41 ```
  42
  43 ```python Collapsed="false"
  44 connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
  45 ```
  46
  47 ```python Collapsed="false"
  48 %sql $connection_string
  49 ```
  50
  51 ```python Collapsed="false"
  52 eng = create_engine(connection_string)
  53 engine = eng.execution_options(isolation_level="AUTOCOMMIT")
  54 ```
  55
  56 ```python Collapsed="false"
  57 DEATH_COUNT_THRESHOLD = 10
  58 COUNTRIES_CORE = 'IT DE UK ES IE FR BE'.split()
  59 COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()
  60 COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()
  61 # COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()
  62
  63 COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA
  64        'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',
  65        'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',
  66        'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']
  67 COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))
  68 COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))
  69 ```
  70
  71 ```python Collapsed="false"
  72 !curl https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/ > covid.csv
  73 ```
  74
  75 ```python Collapsed="false"
  76 # First col is a date, treat geoId of NA as 'Namibia', not "NA" value
  77 raw_data = pd.read_csv('covid.csv',
  78                        parse_dates=[0], dayfirst=True,
  79                        keep_default_na=False, na_values = [''],
  80 #                        dtype = {'day': np.int64,
  81 #                                 'month': np.int64,
  82 #                                 'year': np.int64,
  83 #                                 'cases': np.int64,
  84 #                                 'deaths': np.int64,
  85 #                                 'countriesAndTerritories': str,
  86 #                                 'geoId': str,
  87 #                                 'countryterritoryCode': str,
  88 #                                 'popData2019': np.int64,
  89 #                                 'continentExp': str,
  90 #                                 }
  91                       )
  92 ```
  93
  94 ```python Collapsed="false"
  95 raw_data.size
  96 ```
  97
  98 ```python Collapsed="false"
  99 raw_data.fillna(0, inplace=True)
 100 ```
 101
 102 ```python Collapsed="false"
 103 raw_data.head()
 104 ```
 105
 106 ```python Collapsed="false"
 107 raw_data.rename(columns={'dateRep':'report_date', 'geoId': 'geo_id',
 108                         'countriesAndTerritories': 'country_name',
 109                         'countryterritoryCode': 'country_territory_code',
 110                         'popData2019': 'population_2019',
 111                         'continentExp': 'continent'}, inplace=True)
 112 ```
 113
 114 ```python Collapsed="false"
 115 raw_data.columns
 116 ```
 117
 118 ```python Collapsed="false"
 119 raw_data.dtypes
 120 ```
 121
 122 ```python Collapsed="false"
 123 raw_data[['report_date', 'cases_weekly', 'deaths_weekly', 'geo_id', 'notification_rate_per_100000_population_14-days']]
 124 ```
 125
 126 ```python Collapsed="false"
 127 raw_data[['country_name', 'geo_id', 'country_territory_code',
 128        'population_2019', 'continent']]
 129 ```
 130
 131 ```python Collapsed="false"
 132 raw_data[['report_date', 'cases_weekly', 'deaths_weekly', 'geo_id', 'notification_rate_per_100000_population_14-days']].to_sql(
 133     'weekly_cases',
 134     engine,
 135     if_exists='replace',
 136     index=False,
 137     chunksize=500,
 138     dtype={
 139         "report_date": Date,
 140         "cases_weekly": Integer,
 141         "deaths_weekly": Integer,
 142         "geo_id": String,
 143         "notification_rate_per_100000_population_14-days": Float
 144     }
 145 )
 146 ```
 147
 148 ```python Collapsed="false"
 149 raw_data[['country_name', 'geo_id', 'country_territory_code',
 150        'population_2019', 'continent']].drop_duplicates().to_sql(
 151     'countries',
 152     engine,
 153     if_exists='replace',
 154     index=False,
 155     chunksize=500,
 156     dtype={
 157         "country_name": Text,
 158         "geo_id": String,
 159         "country_territory_code": String,
 160         "population_2019": Integer,
 161         "continent": Text
 162     }
 163 )
 164 ```
 165
 166 ```python Collapsed="false"
 167 # %sql select geo_id from weekly_cases limit 10
 168 ```
 169
 170 ```python Collapsed="false"
 171 # %%sql alter table weekly_cases add primary key (geo_id, report_date);
 172 # alter table countries add primary key (geo_id);
 173 # alter table weekly_cases add foreign key (geo_id) references countries(geo_id);
 174 # alter table weekly_cases add culm_cases integer;
 175 # alter table weekly_cases add culm_deaths integer;
 176 ```
 177
 178 ```python
 179 with engine.connect() as connection:
 180     connection.execute('alter table weekly_cases add primary key (geo_id, report_date)')
 181     connection.execute('alter table countries add primary key (geo_id);')
 182     connection.execute('alter table weekly_cases add foreign key (geo_id) references countries(geo_id);')
 183     connection.execute('alter table weekly_cases add culm_cases integer;')
 184     connection.execute('alter table weekly_cases add culm_deaths integer;')
 185 ```
 186
 187 ```python Collapsed="false"
 188 # %sql select report_date, cases_weekly, country_name from weekly_cases join countries using (geo_id) order by report_date desc limit 10
 189 ```
 190
 191 ```python Collapsed="false"
 192 # %sql select report_date, cases_weekly, country_name from weekly_cases join countries on weekly_cases.geo_id = countries.geo_id order by report_date desc limit 10
 193 ```
 194
 195 ```python Collapsed="false"
 196 %sql select * from weekly_cases limit 10
 197 ```
 198
 199 ```python Collapsed="false"
 200 # %%sql
 201 # with culm as
 202 #     (select report_date, geo_id,
 203 #         sum(cases_weekly) over (partition by geo_id
 204 #                                 order by report_date) as culm_data
 205 #      from weekly_cases
 206 #     )
 207 # update weekly_cases
 208 #     set culm_cases = culm_data
 209 #     from culm
 210 #     where weekly_cases.report_date = culm.report_date and
 211 #           weekly_cases.geo_id = culm.geo_id
 212 ```
 213
 214 ```python Collapsed="false"
 215 query_string = '''with culm as
 216     (select report_date, geo_id,
 217         sum(cases_weekly) over (partition by geo_id
 218                                 order by report_date) as culm_data
 219      from weekly_cases
 220     )
 221 update weekly_cases
 222     set culm_cases = culm_data
 223     from culm
 224     where weekly_cases.report_date = culm.report_date and
 225           weekly_cases.geo_id = culm.geo_id'''
 226 with engine.connect() as connection:
 227     connection.execute(query_string)
 228 ```
 229
 230 ```python Collapsed="false"
 231 # %%sql
 232 # with culm as
 233 #     (select report_date, geo_id,
 234 #         sum(deaths_weekly) over (partition by geo_id
 235 #                                 order by report_date) as culm_data
 236 #      from weekly_cases
 237 #     )
 238 # update weekly_cases
 239 #     set culm_deaths = culm_data
 240 #     from culm
 241 #     where weekly_cases.report_date = culm.report_date and
 242 #           weekly_cases.geo_id = culm.geo_id
 243 ```
 244
 245 ```python Collapsed="false"
 246 query_string = '''with culm as
 247     (select report_date, geo_id,
 248         sum(deaths_weekly) over (partition by geo_id
 249                                 order by report_date) as culm_data
 250      from weekly_cases
 251     )
 252 update weekly_cases
 253     set culm_deaths = culm_data
 254     from culm
 255     where weekly_cases.report_date = culm.report_date and
 256           weekly_cases.geo_id = culm.geo_id'''
 257 with engine.connect() as connection:
 258     connection.execute(query_string)
 259 ```
 260
 261 ```python Collapsed="false"
 262 uk_query_string = (
 263 "https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&"
 264 "metric=covidOccupiedMVBeds&"
 265 "metric=newAdmissions&"
 266 "metric=newCasesBySpecimenDate&"
 267 "metric=hospitalCases&"
 268 "metric=newDeaths28DaysByPublishDate&"
 269 "format=csv"
 270 )
 271
 272 !curl "$uk_query_string" > uk_data.csv
 273 ```
 274
 275 ```python Collapsed="false"
 276 test_query_string = (
 277 "https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&"
 278 "metric=newPCRTestsByPublishDate&"
 279 "metric=newTestsByPublishDate&"
 280 "metric=newPillarOneTwoTestsByPublishDate&"
 281 "format=csv"
 282 )
 283 !curl "$test_query_string" > test_data.csv
 284 ```
 285
 286 ```python Collapsed="false"
 287 # hospital_query_string = (
 288 # "https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&"
 289 # "metric=newAdmissions&"
 290 # "format=csv"
 291 # )
 292 # !curl "$hospital_query_string" > hospital_admissions.csv
 293 ```
 294
 295 ```python Collapsed="false"
 296 # hospital_query_string = (
 297 # "https://api.coronavirus.data.gov.uk/v1/data?"
 298 # "filters=areaName=United%2520Kingdom;areaType=overview&"
 299 # "structure=%7B%22date%22:%22date%22,%22areaName%22:%22areaName%22,%22areaType%22:%22areaType%22,"
 300 # "%22newAdmissions%22:%22newAdmissions%22,%22cumAdmissions%22:%22cumAdmissions%22%7D&format=csv"
 301 # )
 302
 303 # !curl "$hospital_query_string" | gunzip > hospital_admissions.csv
 304 ```
 305
 306 ```python Collapsed="false"
 307 uk_data = pd.read_csv('uk_data.csv',
 308                        parse_dates=[0], dayfirst=True)
 309 uk_data
 310 ```
 311
 312 ```python Collapsed="false"
 313 test_data = pd.read_csv('test_data.csv',
 314                        parse_dates=[0], dayfirst=True)
 315 test_data
 316 ```
 317
 318 ```python Collapsed="false"
 319 test_data.columns
 320 ```
 321
 322 ```python Collapsed="false"
 323 uk_data = uk_data.merge(test_data[['date', 'newPCRTestsByPublishDate',
 324        'newTestsByPublishDate', 'newPillarOneTwoTestsByPublishDate']], how='outer', on='date')
 325 ```
 326
 327 ```python Collapsed="false"
 328 uk_data.columns
 329 ```
 330
 331 ```python Collapsed="false"
 332 uk_data.rename(
 333     columns={
 334         'covidOccupiedMVBeds': 'ventilator_beds',
 335         'newCasesBySpecimenDate': 'new_cases',
 336         'hospitalCases': 'hospital_cases',
 337         'newDeaths28DaysByPublishDate': 'new_deaths',
 338         'newAdmissions': 'new_admissions',
 339         'newPCRTestsByPublishDate': 'new_pcr_tests',
 340         'newTestsByPublishDate': 'new_tests',
 341         'newPillarOneTwoTestsByPublishDate': 'new_pillar_1_2_tests'
 342     }, inplace=True)
 343 ```
 344
 345 ```python Collapsed="false"
 346 uk_data.dtypes
 347 ```
 348
 349 ```python Collapsed="false"
 350  uk_data.columns
 351 ```
 352
 353 ```python Collapsed="false"
 354 uk_data[['date',
 355          'hospital_cases', 'ventilator_beds',
 356          'new_cases', 'new_deaths',
 357          'hospital_cases', 'new_admissions',
 358          'new_pcr_tests', 'new_tests', 'new_pillar_1_2_tests'
 359         ]].to_sql(
 360     'uk_data',
 361     engine,
 362     if_exists='replace',
 363     index=False,
 364     chunksize=500,
 365     dtype={
 366         "date": Date,
 367         "hospital_cases": Integer,
 368         "ventilator_beds": Integer,
 369         "new_cases": Integer,
 370         "hospital_cases": Integer,
 371         "new_deaths": Integer,
 372         "new_admissions": Integer,
 373         'new_pcr_tests': Integer,
 374         'new_tests': Integer,
 375         'new_pillar_1_2_tests': Integer
 376     }
 377 )
 378 ```
 379
 380 ```python Collapsed="false"
 381 # %sql select * from uk_data order by date desc limit 10
 382 ```
 383
 384 ```python Collapsed="false"
 385 query_string = '''drop table if exists uk_data_7;
 386 create table uk_data_7
 387 (date date primary key,
 388  hospital_cases real,
 389  ventilator_beds real,
 390  new_cases real,
 391  new_deaths real,
 392  new_admissions real,
 393  new_pcr_tests real,
 394  new_tests real,
 395  new_pillar_1_2_tests real
 396 );'''
 397
 398 with engine.connect() as connection:
 399     connection.execute(query_string)
 400 ```
 401
 402 ```python
 403 update_string = '''with ownd as (
 404     select date,
 405         avg(hospital_cases) over wnd as w_hospital_cases,
 406         avg(ventilator_beds) over wnd as w_ventilator_beds,
 407         avg(new_cases) over wnd as w_new_cases,
 408         avg(new_deaths) over wnd as w_new_deaths,
 409         avg(new_admissions) over wnd as w_new_admissions,
 410         avg(new_pcr_tests) over wnd as w_new_pcr_tests,
 411         avg(new_tests) over wnd as w_new_tests,
 412         avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
 413         count(*) over wnd as w_size
 414     from uk_data
 415     window wnd as (
 416         order by uk_data.date
 417         rows between 3 preceding and 3 following
 418     )
 419 )
 420 insert into uk_data_7(date,
 421     hospital_cases,
 422     ventilator_beds,
 423     new_cases,
 424     new_deaths,
 425     new_admissions,
 426     new_pcr_tests,
 427     new_tests,
 428     new_pillar_1_2_tests
 429     )
 430 (select date,
 431         w_hospital_cases,
 432         w_ventilator_beds,
 433         w_new_cases,
 434         w_new_deaths,
 435         w_new_admissions,
 436         w_new_pcr_tests,
 437         w_new_tests,
 438         w_new_pillar_1_2_tests
 439     from ownd
 440     where w_size = 7
 441 )'''
 442 with engine.connect() as connection:
 443     connection.execute(update_string)
 444 ```
 445
 446 ```python Collapsed="false"
 447 # %%sql insert into uk_data_7(date, ventilator_beds, new_cases, hospital_cases, new_deaths, new_admissions)
 448 # values (
 449 #     select date,
 450 #     avg(ventilator_beds) over (order by date rows between 6 preceding and current row)
 451 #     from uk_data
 452 # )
 453 ```
 454
 455 ```python Collapsed="false"
 456 # query_string = '''insert into uk_data_7(date, hospital_cases)
 457 #     select uk_data.date,
 458 #         avg(uk_data.hospital_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as hospital_cases
 459 #     from uk_data'''
 460 # with engine.connect() as connection:
 461 #     connection.execute(query_string)
 462 ```
 463
 464 ```python Collapsed="false"
 465 # %%sql
 466 # with m7 as
 467 #     (select uk_data.date as date7,
 468 #         avg(uk_data.ventilator_beds) over (order by uk_data.date rows between 6 preceding and current row) as nc7
 469 #      from uk_data
 470 #     )
 471 # update uk_data_7
 472 #     set ventilator_beds = nc7
 473 #     from m7
 474 #     where uk_data_7.date = m7.date7
 475 ```
 476
 477 ```python Collapsed="false"
 478 # %%sql
 479 # with m7 as
 480 #     (select uk_data.date as date7,
 481 #         avg(uk_data.ventilator_beds) over (order by uk_data.date rows between 3 preceding and 3 following) as nc7
 482 #      from uk_data
 483 #     )
 484 # update uk_data_7
 485 #     set ventilator_beds = nc7
 486 #     from m7
 487 #     where uk_data_7.date = m7.date7
 488 ```
 489
 490 ```python Collapsed="false"
 491 # %%sql
 492 # with m7 as
 493 #     (select uk_data.date as date7,
 494 #         avg(uk_data.new_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as nc7
 495 #      from uk_data
 496 #     )
 497 # update uk_data_7
 498 #     set new_cases = nc7
 499 #     from m7
 500 #     where uk_data_7.date = m7.date7
 501 ```
 502
 503 ```python Collapsed="false"
 504 # %%sql
 505 # with m7 as
 506 #     (select uk_data.date as date7,
 507 #         avg(uk_data.hospital_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
 508 #      from uk_data
 509 #     )
 510 # update uk_data_7
 511 #     set hospital_cases = d7
 512 #     from m7
 513 #     where uk_data_7.date = m7.date7
 514 ```
 515
 516 ```python Collapsed="false"
 517 # %%sql
 518 # with m7 as
 519 #     (select uk_data.date as date7,
 520 #         avg(uk_data.new_deaths) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
 521 #      from uk_data
 522 #     )
 523 # update uk_data_7
 524 #     set new_deaths = d7
 525 #     from m7
 526 #     where uk_data_7.date = m7.date7
 527 ```
 528
 529 ```python Collapsed="false"
 530 # %%sql
 531 # with m7 as
 532 #     (select uk_data.date as date7,
 533 #         avg(uk_data.new_admissions) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
 534 #      from uk_data
 535 #     )
 536 # update uk_data_7
 537 #     set new_admissions = d7
 538 #     from m7
 539 #     where uk_data_7.date = m7.date7
 540 ```
 541
 542 ```python Collapsed="false"
 543 # %%sql
 544 # with m7 as
 545 #     (select uk_data.date as date7,
 546 #         avg(uk_data.new_pcr_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
 547 #      from uk_data
 548 #     )
 549 # update uk_data_7
 550 #     set new_pcr_tests = d7
 551 #     from m7
 552 #     where uk_data_7.date = m7.date7
 553 ```
 554
 555 ```python Collapsed="false"
 556 # %%sql
 557 # with m7 as
 558 #     (select uk_data.date as date7,
 559 #         avg(uk_data.new_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
 560 #      from uk_data
 561 #     )
 562 # update uk_data_7
 563 #     set new_tests = d7
 564 #     from m7
 565 #     where uk_data_7.date = m7.date7
 566 ```
 567
 568 ```python Collapsed="false"
 569 # %%sql
 570 # with m7 as
 571 #     (select uk_data.date as date7,
 572 #         avg(uk_data.new_pillar_1_2_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
 573 #      from uk_data
 574 #     )
 575 # update uk_data_7
 576 #     set new_pillar_1_2_tests = d7
 577 #     from m7
 578 #     where uk_data_7.date = m7.date7
 579 ```
 580
 581 ```python Collapsed="false"
 582 # %%sql
 583 # with wnd as
 584 #     (   select date,
 585 #              avg(new_pillar_1_2_tests)
 586 #                 over (order by uk_data.date rows between 3 preceding and 3 following) as a_new_pillar_1_2_tests
 587 #         from uk_data
 588 #     )
 589 # update uk_data_7
 590 #     set new_pillar_1_2_tests = wnd.a_new_pillar_1_2_tests
 591 #     from wnd
 592 #     where uk_data_7.date = wnd.date
 593 #     and (select count(*) from wnd) = 7
 594 ```
 595
 596 ```python Collapsed="false"
 597 # %%sql
 598 # with wnd as
 599 #     (   select date,
 600 #              avg(new_pillar_1_2_tests)
 601 #                 over (order by uk_data.date rows between 3 preceding and 3 following) as a_new_pillar_1_2_tests,
 602 #              count(new_pillar_1_2_tests)
 603 #                 over (order by uk_data.date rows between 3 preceding and 3 following) as a_count
 604 #         from uk_data
 605 #     )
 606 # select uk_data_7.date, new_pillar_1_2_tests, wnd.a_new_pillar_1_2_tests, wnd.a_count
 607 #     from uk_data_7, wnd
 608 #     where uk_data_7.date = wnd.date
 609 #     order by uk_data_7.date desc limit 10
 610
 611 # select date,
 612 #     count(*) over wnd as w_size
 613 # from uk_data
 614 # window wnd as (
 615 #     order by uk_data.date
 616 #     rows between 3 preceding and 3 following
 617 # );
 618 ```
 619
 620 ```python Collapsed="false"
 621 # %%sql
 622 # select date,
 623 #     count(*) over wnd as w_size
 624 # from uk_data
 625 # window wnd as (
 626 #     order by uk_data.date
 627 #     rows between 3 preceding and 3 following
 628 # )
 629 # order by date desc limit 10
 630 ```
 631
 632 ```python
 633 # %%sql
 634 # with ownd as (
 635 #     select date,
 636 #         avg(hospital_cases) over wnd as w_hospital_cases,
 637 #         avg(ventilator_beds) over wnd as w_ventilator_beds,
 638 #         avg(new_cases) over wnd as w_new_cases,
 639 #         avg(new_deaths) over wnd as w_new_deaths,
 640 #         avg(new_admissions) over wnd as w_new_admissions,
 641 #         avg(new_pcr_tests) over wnd as w_new_pcr_tests,
 642 #         avg(new_tests) over wnd as w_new_tests,
 643 #         avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
 644 #         count(*) over wnd as w_size
 645 #     from uk_data
 646 #     window wnd as (
 647 #         order by uk_data.date
 648 #         rows between 3 preceding and 3 following
 649 #     ))
 650 # insert into uk_data_7(date,
 651 #     hospital_cases,
 652 #     ventilator_beds,
 653 #     new_cases,
 654 #     new_deaths,
 655 #     new_admissions,
 656 #     new_pcr_tests,
 657 #     new_tests,
 658 #     new_pillar_1_2_tests
 659 #     )
 660 # (select date,
 661 #         avg(hospital_cases) over wnd as w_hospital_cases,
 662 #         avg(ventilator_beds) over wnd as w_ventilator_beds,
 663 #         avg(new_cases) over wnd as w_new_cases,
 664 #         avg(new_deaths) over wnd as w_new_deaths,
 665 #         avg(new_admissions) over wnd as w_new_admissions,
 666 #         avg(new_pcr_tests) over wnd as w_new_pcr_tests,
 667 #         avg(new_tests) over wnd as w_new_tests,
 668 #         avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
 669 #         count(*) over wnd as w_size
 670 #     from uk_data
 671 #     window wnd as (
 672 #         order by uk_data.date
 673 #         rows between 3 preceding and 3 following
 674 #     )
 675 # )
 676 #     set date = ownd.date,
 677 #         hospital_cases = w_hospital_cases,
 678 #         ventilator_beds = w_ventilator_beds,
 679 #         new_cases = w_new_cases,
 680 #         new_deaths = w_new_deaths,
 681 #         new_admissions = w_new_admissions,
 682 #         new_pcr_tests = w_new_pcr_tests,
 683 #         new_tests = w_new_tests,
 684 #         new_pillar_1_2_tests = w_new_pillar_1_2_tests
 685 #     from ownd
 686 #     where w_size = 7
 687 ```
 688
 689 ```python
 690 # %%sql
 691 # with ownd as (
 692 #     select date,
 693 #         avg(hospital_cases) over wnd as w_hospital_cases,
 694 #         avg(ventilator_beds) over wnd as w_ventilator_beds,
 695 #         avg(new_cases) over wnd as w_new_cases,
 696 #         avg(new_deaths) over wnd as w_new_deaths,
 697 #         avg(new_admissions) over wnd as w_new_admissions,
 698 #         avg(new_pcr_tests) over wnd as w_new_pcr_tests,
 699 #         avg(new_tests) over wnd as w_new_tests,
 700 #         avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
 701 #         count(*) over wnd as w_size
 702 #     from uk_data
 703 #     window wnd as (
 704 #         order by uk_data.date
 705 #         rows between 3 preceding and 3 following
 706 #     )
 707 # )
 708 # insert into uk_data_7(date,
 709 #     hospital_cases,
 710 #     ventilator_beds,
 711 #     new_cases,
 712 #     new_deaths,
 713 #     new_admissions,
 714 #     new_pcr_tests,
 715 #     new_tests,
 716 #     new_pillar_1_2_tests
 717 #     )
 718 # (select date,
 719 #         w_hospital_cases,
 720 #         w_ventilator_beds,
 721 #         w_new_cases,
 722 #         w_new_deaths,
 723 #         w_new_admissions,
 724 #         w_new_pcr_tests,
 725 #         w_new_tests,
 726 #         w_new_pillar_1_2_tests
 727 #     from ownd
 728 #     where w_size = 7
 729 # )
 730 ```
 731
 732 ```python Collapsed="false"
 733 %sql select * from uk_data_7 order by date desc limit 10
 734 ```
 735
 736 ```python Collapsed="false"
 737 %sql select * from uk_data order by date desc limit 10
 738 ```
 739
 740 ```python Collapsed="false"
 741
 742 ```