--- /dev/null
+---
+jupyter:
+ jupytext:
+ formats: ipynb,md
+ text_representation:
+ extension: .md
+ format_name: markdown
+ format_version: '1.2'
+ jupytext_version: 1.9.1
+ kernelspec:
+ display_name: Python 3
+ language: python
+ name: python3
+---
+
+<!-- #region Collapsed="false" -->
+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)
+<!-- #endregion -->
+
+```python Collapsed="false"
+from sqlalchemy.types import Integer, Text, String, DateTime, Date, Float
+from sqlalchemy import create_engine
+```
+
+```python Collapsed="false"
+import itertools
+import collections
+import json
+import pandas as pd
+import numpy as np
+from scipy.stats import gmean
+import datetime
+
+import matplotlib as mpl
+import matplotlib.pyplot as plt
+%matplotlib inline
+```
+
+```python Collapsed="false"
+%load_ext sql
+```
+
+```python Collapsed="false"
+connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
+```
+
+```python Collapsed="false"
+%sql $connection_string
+```
+
+```python Collapsed="false"
+eng = create_engine(connection_string)
+engine = eng.execution_options(isolation_level="AUTOCOMMIT")
+```
+
+```python Collapsed="false"
+DEATH_COUNT_THRESHOLD = 10
+COUNTRIES_CORE = 'IT DE UK ES IE FR BE'.split()
+COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()
+COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()
+# COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()
+
+COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA
+ 'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',
+ 'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',
+ 'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']
+COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))
+COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))
+```
+
+```python Collapsed="false"
+!curl https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/ > covid.csv
+```
+
+```python Collapsed="false"
+# First col is a date, treat geoId of NA as 'Namibia', not "NA" value
+raw_data = pd.read_csv('covid.csv',
+ parse_dates=[0], dayfirst=True,
+ keep_default_na=False, na_values = [''],
+# dtype = {'day': np.int64,
+# 'month': np.int64,
+# 'year': np.int64,
+# 'cases': np.int64,
+# 'deaths': np.int64,
+# 'countriesAndTerritories': str,
+# 'geoId': str,
+# 'countryterritoryCode': str,
+# 'popData2019': np.int64,
+# 'continentExp': str,
+# }
+ )
+```
+
+```python Collapsed="false"
+raw_data.size
+```
+
+```python Collapsed="false"
+raw_data.fillna(0, inplace=True)
+```
+
+```python Collapsed="false"
+raw_data.head()
+```
+
+```python Collapsed="false"
+raw_data.rename(columns={'dateRep':'report_date', 'geoId': 'geo_id',
+ 'countriesAndTerritories': 'country_name',
+ 'countryterritoryCode': 'country_territory_code',
+ 'popData2019': 'population_2019',
+ 'continentExp': 'continent'}, inplace=True)
+```
+
+```python Collapsed="false"
+raw_data.columns
+```
+
+```python Collapsed="false"
+raw_data.dtypes
+```
+
+```python Collapsed="false"
+raw_data[['report_date', 'cases_weekly', 'deaths_weekly', 'geo_id', 'notification_rate_per_100000_population_14-days']]
+```
+
+```python Collapsed="false"
+raw_data[['country_name', 'geo_id', 'country_territory_code',
+ 'population_2019', 'continent']]
+```
+
+```python Collapsed="false"
+raw_data[['report_date', 'cases_weekly', 'deaths_weekly', 'geo_id', 'notification_rate_per_100000_population_14-days']].to_sql(
+ 'weekly_cases',
+ engine,
+ if_exists='replace',
+ index=False,
+ chunksize=500,
+ dtype={
+ "report_date": Date,
+ "cases_weekly": Integer,
+ "deaths_weekly": Integer,
+ "geo_id": String,
+ "notification_rate_per_100000_population_14-days": Float
+ }
+)
+```
+
+```python Collapsed="false"
+raw_data[['country_name', 'geo_id', 'country_territory_code',
+ 'population_2019', 'continent']].drop_duplicates().to_sql(
+ 'countries',
+ engine,
+ if_exists='replace',
+ index=False,
+ chunksize=500,
+ dtype={
+ "country_name": Text,
+ "geo_id": String,
+ "country_territory_code": String,
+ "population_2019": Integer,
+ "continent": Text
+ }
+)
+```
+
+```python Collapsed="false"
+# %sql select geo_id from weekly_cases limit 10
+```
+
+```python Collapsed="false"
+# %%sql alter table weekly_cases add primary key (geo_id, report_date);
+# alter table countries add primary key (geo_id);
+# alter table weekly_cases add foreign key (geo_id) references countries(geo_id);
+# alter table weekly_cases add culm_cases integer;
+# alter table weekly_cases add culm_deaths integer;
+```
+
+```python
+with engine.connect() as connection:
+ connection.execute('alter table weekly_cases add primary key (geo_id, report_date)')
+ connection.execute('alter table countries add primary key (geo_id);')
+ connection.execute('alter table weekly_cases add foreign key (geo_id) references countries(geo_id);')
+ connection.execute('alter table weekly_cases add culm_cases integer;')
+ connection.execute('alter table weekly_cases add culm_deaths integer;')
+```
+
+```python Collapsed="false"
+# %sql select report_date, cases_weekly, country_name from weekly_cases join countries using (geo_id) order by report_date desc limit 10
+```
+
+```python Collapsed="false"
+# %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
+```
+
+```python Collapsed="false"
+%sql select * from weekly_cases limit 10
+```
+
+```python Collapsed="false"
+# %%sql
+# with culm as
+# (select report_date, geo_id,
+# sum(cases_weekly) over (partition by geo_id
+# order by report_date) as culm_data
+# from weekly_cases
+# )
+# update weekly_cases
+# set culm_cases = culm_data
+# from culm
+# where weekly_cases.report_date = culm.report_date and
+# weekly_cases.geo_id = culm.geo_id
+```
+
+```python Collapsed="false"
+query_string = '''with culm as
+ (select report_date, geo_id,
+ sum(cases_weekly) over (partition by geo_id
+ order by report_date) as culm_data
+ from weekly_cases
+ )
+update weekly_cases
+ set culm_cases = culm_data
+ from culm
+ where weekly_cases.report_date = culm.report_date and
+ weekly_cases.geo_id = culm.geo_id'''
+with engine.connect() as connection:
+ connection.execute(query_string)
+```
+
+```python Collapsed="false"
+# %%sql
+# with culm as
+# (select report_date, geo_id,
+# sum(deaths_weekly) over (partition by geo_id
+# order by report_date) as culm_data
+# from weekly_cases
+# )
+# update weekly_cases
+# set culm_deaths = culm_data
+# from culm
+# where weekly_cases.report_date = culm.report_date and
+# weekly_cases.geo_id = culm.geo_id
+```
+
+```python Collapsed="false"
+query_string = '''with culm as
+ (select report_date, geo_id,
+ sum(deaths_weekly) over (partition by geo_id
+ order by report_date) as culm_data
+ from weekly_cases
+ )
+update weekly_cases
+ set culm_deaths = culm_data
+ from culm
+ where weekly_cases.report_date = culm.report_date and
+ weekly_cases.geo_id = culm.geo_id'''
+with engine.connect() as connection:
+ connection.execute(query_string)
+```
+
+```python Collapsed="false"
+uk_query_string = (
+"https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&"
+"metric=covidOccupiedMVBeds&"
+"metric=newAdmissions&"
+"metric=newCasesBySpecimenDate&"
+"metric=hospitalCases&"
+"metric=newDeaths28DaysByPublishDate&"
+"format=csv"
+)
+
+!curl "$uk_query_string" > uk_data.csv
+```
+
+```python Collapsed="false"
+test_query_string = (
+"https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&"
+"metric=newPCRTestsByPublishDate&"
+"metric=newTestsByPublishDate&"
+"metric=newPillarOneTwoTestsByPublishDate&"
+"format=csv"
+)
+!curl "$test_query_string" > test_data.csv
+```
+
+```python Collapsed="false"
+# hospital_query_string = (
+# "https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&"
+# "metric=newAdmissions&"
+# "format=csv"
+# )
+# !curl "$hospital_query_string" > hospital_admissions.csv
+```
+
+```python Collapsed="false"
+# hospital_query_string = (
+# "https://api.coronavirus.data.gov.uk/v1/data?"
+# "filters=areaName=United%2520Kingdom;areaType=overview&"
+# "structure=%7B%22date%22:%22date%22,%22areaName%22:%22areaName%22,%22areaType%22:%22areaType%22,"
+# "%22newAdmissions%22:%22newAdmissions%22,%22cumAdmissions%22:%22cumAdmissions%22%7D&format=csv"
+# )
+
+# !curl "$hospital_query_string" | gunzip > hospital_admissions.csv
+```
+
+```python Collapsed="false"
+uk_data = pd.read_csv('uk_data.csv',
+ parse_dates=[0], dayfirst=True)
+uk_data
+```
+
+```python Collapsed="false"
+test_data = pd.read_csv('test_data.csv',
+ parse_dates=[0], dayfirst=True)
+test_data
+```
+
+```python Collapsed="false"
+test_data.columns
+```
+
+```python Collapsed="false"
+uk_data = uk_data.merge(test_data[['date', 'newPCRTestsByPublishDate',
+ 'newTestsByPublishDate', 'newPillarOneTwoTestsByPublishDate']], how='outer', on='date')
+```
+
+```python Collapsed="false"
+uk_data.columns
+```
+
+```python Collapsed="false"
+uk_data.rename(
+ columns={
+ 'covidOccupiedMVBeds': 'ventilator_beds',
+ 'newCasesBySpecimenDate': 'new_cases',
+ 'hospitalCases': 'hospital_cases',
+ 'newDeaths28DaysByPublishDate': 'new_deaths',
+ 'newAdmissions': 'new_admissions',
+ 'newPCRTestsByPublishDate': 'new_pcr_tests',
+ 'newTestsByPublishDate': 'new_tests',
+ 'newPillarOneTwoTestsByPublishDate': 'new_pillar_1_2_tests'
+ }, inplace=True)
+```
+
+```python Collapsed="false"
+uk_data.dtypes
+```
+
+```python Collapsed="false"
+ uk_data.columns
+```
+
+```python Collapsed="false"
+uk_data[['date',
+ 'hospital_cases', 'ventilator_beds',
+ 'new_cases', 'new_deaths',
+ 'hospital_cases', 'new_admissions',
+ 'new_pcr_tests', 'new_tests', 'new_pillar_1_2_tests'
+ ]].to_sql(
+ 'uk_data',
+ engine,
+ if_exists='replace',
+ index=False,
+ chunksize=500,
+ dtype={
+ "date": Date,
+ "hospital_cases": Integer,
+ "ventilator_beds": Integer,
+ "new_cases": Integer,
+ "hospital_cases": Integer,
+ "new_deaths": Integer,
+ "new_admissions": Integer,
+ 'new_pcr_tests': Integer,
+ 'new_tests': Integer,
+ 'new_pillar_1_2_tests': Integer
+ }
+)
+```
+
+```python Collapsed="false"
+# %sql select * from uk_data order by date desc limit 10
+```
+
+```python Collapsed="false"
+query_string = '''drop table if exists uk_data_7;
+create table uk_data_7
+(date date primary key,
+ hospital_cases real,
+ ventilator_beds real,
+ new_cases real,
+ new_deaths real,
+ new_admissions real,
+ new_pcr_tests real,
+ new_tests real,
+ new_pillar_1_2_tests real
+);'''
+
+with engine.connect() as connection:
+ connection.execute(query_string)
+```
+
+```python
+update_string = '''with ownd as (
+ select date,
+ avg(hospital_cases) over wnd as w_hospital_cases,
+ avg(ventilator_beds) over wnd as w_ventilator_beds,
+ avg(new_cases) over wnd as w_new_cases,
+ avg(new_deaths) over wnd as w_new_deaths,
+ avg(new_admissions) over wnd as w_new_admissions,
+ avg(new_pcr_tests) over wnd as w_new_pcr_tests,
+ avg(new_tests) over wnd as w_new_tests,
+ avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
+ count(*) over wnd as w_size
+ from uk_data
+ window wnd as (
+ order by uk_data.date
+ rows between 3 preceding and 3 following
+ )
+)
+insert into uk_data_7(date,
+ hospital_cases,
+ ventilator_beds,
+ new_cases,
+ new_deaths,
+ new_admissions,
+ new_pcr_tests,
+ new_tests,
+ new_pillar_1_2_tests
+ )
+(select date,
+ w_hospital_cases,
+ w_ventilator_beds,
+ w_new_cases,
+ w_new_deaths,
+ w_new_admissions,
+ w_new_pcr_tests,
+ w_new_tests,
+ w_new_pillar_1_2_tests
+ from ownd
+ where w_size = 7
+)'''
+with engine.connect() as connection:
+ connection.execute(update_string)
+```
+
+```python Collapsed="false"
+# %%sql insert into uk_data_7(date, ventilator_beds, new_cases, hospital_cases, new_deaths, new_admissions)
+# values (
+# select date,
+# avg(ventilator_beds) over (order by date rows between 6 preceding and current row)
+# from uk_data
+# )
+```
+
+```python Collapsed="false"
+# query_string = '''insert into uk_data_7(date, hospital_cases)
+# select uk_data.date,
+# avg(uk_data.hospital_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as hospital_cases
+# from uk_data'''
+# with engine.connect() as connection:
+# connection.execute(query_string)
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.ventilator_beds) over (order by uk_data.date rows between 6 preceding and current row) as nc7
+# from uk_data
+# )
+# update uk_data_7
+# set ventilator_beds = nc7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.ventilator_beds) over (order by uk_data.date rows between 3 preceding and 3 following) as nc7
+# from uk_data
+# )
+# update uk_data_7
+# set ventilator_beds = nc7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.new_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as nc7
+# from uk_data
+# )
+# update uk_data_7
+# set new_cases = nc7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.hospital_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
+# from uk_data
+# )
+# update uk_data_7
+# set hospital_cases = d7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.new_deaths) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
+# from uk_data
+# )
+# update uk_data_7
+# set new_deaths = d7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.new_admissions) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
+# from uk_data
+# )
+# update uk_data_7
+# set new_admissions = d7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.new_pcr_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
+# from uk_data
+# )
+# update uk_data_7
+# set new_pcr_tests = d7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.new_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
+# from uk_data
+# )
+# update uk_data_7
+# set new_tests = d7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with m7 as
+# (select uk_data.date as date7,
+# avg(uk_data.new_pillar_1_2_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7
+# from uk_data
+# )
+# update uk_data_7
+# set new_pillar_1_2_tests = d7
+# from m7
+# where uk_data_7.date = m7.date7
+```
+
+```python Collapsed="false"
+# %%sql
+# with wnd as
+# ( select date,
+# avg(new_pillar_1_2_tests)
+# over (order by uk_data.date rows between 3 preceding and 3 following) as a_new_pillar_1_2_tests
+# from uk_data
+# )
+# update uk_data_7
+# set new_pillar_1_2_tests = wnd.a_new_pillar_1_2_tests
+# from wnd
+# where uk_data_7.date = wnd.date
+# and (select count(*) from wnd) = 7
+```
+
+```python Collapsed="false"
+# %%sql
+# with wnd as
+# ( select date,
+# avg(new_pillar_1_2_tests)
+# over (order by uk_data.date rows between 3 preceding and 3 following) as a_new_pillar_1_2_tests,
+# count(new_pillar_1_2_tests)
+# over (order by uk_data.date rows between 3 preceding and 3 following) as a_count
+# from uk_data
+# )
+# select uk_data_7.date, new_pillar_1_2_tests, wnd.a_new_pillar_1_2_tests, wnd.a_count
+# from uk_data_7, wnd
+# where uk_data_7.date = wnd.date
+# order by uk_data_7.date desc limit 10
+
+# select date,
+# count(*) over wnd as w_size
+# from uk_data
+# window wnd as (
+# order by uk_data.date
+# rows between 3 preceding and 3 following
+# );
+```
+
+```python Collapsed="false"
+# %%sql
+# select date,
+# count(*) over wnd as w_size
+# from uk_data
+# window wnd as (
+# order by uk_data.date
+# rows between 3 preceding and 3 following
+# )
+# order by date desc limit 10
+```
+
+```python
+# %%sql
+# with ownd as (
+# select date,
+# avg(hospital_cases) over wnd as w_hospital_cases,
+# avg(ventilator_beds) over wnd as w_ventilator_beds,
+# avg(new_cases) over wnd as w_new_cases,
+# avg(new_deaths) over wnd as w_new_deaths,
+# avg(new_admissions) over wnd as w_new_admissions,
+# avg(new_pcr_tests) over wnd as w_new_pcr_tests,
+# avg(new_tests) over wnd as w_new_tests,
+# avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
+# count(*) over wnd as w_size
+# from uk_data
+# window wnd as (
+# order by uk_data.date
+# rows between 3 preceding and 3 following
+# ))
+# insert into uk_data_7(date,
+# hospital_cases,
+# ventilator_beds,
+# new_cases,
+# new_deaths,
+# new_admissions,
+# new_pcr_tests,
+# new_tests,
+# new_pillar_1_2_tests
+# )
+# (select date,
+# avg(hospital_cases) over wnd as w_hospital_cases,
+# avg(ventilator_beds) over wnd as w_ventilator_beds,
+# avg(new_cases) over wnd as w_new_cases,
+# avg(new_deaths) over wnd as w_new_deaths,
+# avg(new_admissions) over wnd as w_new_admissions,
+# avg(new_pcr_tests) over wnd as w_new_pcr_tests,
+# avg(new_tests) over wnd as w_new_tests,
+# avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
+# count(*) over wnd as w_size
+# from uk_data
+# window wnd as (
+# order by uk_data.date
+# rows between 3 preceding and 3 following
+# )
+# )
+# set date = ownd.date,
+# hospital_cases = w_hospital_cases,
+# ventilator_beds = w_ventilator_beds,
+# new_cases = w_new_cases,
+# new_deaths = w_new_deaths,
+# new_admissions = w_new_admissions,
+# new_pcr_tests = w_new_pcr_tests,
+# new_tests = w_new_tests,
+# new_pillar_1_2_tests = w_new_pillar_1_2_tests
+# from ownd
+# where w_size = 7
+```
+
+```python
+# %%sql
+# with ownd as (
+# select date,
+# avg(hospital_cases) over wnd as w_hospital_cases,
+# avg(ventilator_beds) over wnd as w_ventilator_beds,
+# avg(new_cases) over wnd as w_new_cases,
+# avg(new_deaths) over wnd as w_new_deaths,
+# avg(new_admissions) over wnd as w_new_admissions,
+# avg(new_pcr_tests) over wnd as w_new_pcr_tests,
+# avg(new_tests) over wnd as w_new_tests,
+# avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,
+# count(*) over wnd as w_size
+# from uk_data
+# window wnd as (
+# order by uk_data.date
+# rows between 3 preceding and 3 following
+# )
+# )
+# insert into uk_data_7(date,
+# hospital_cases,
+# ventilator_beds,
+# new_cases,
+# new_deaths,
+# new_admissions,
+# new_pcr_tests,
+# new_tests,
+# new_pillar_1_2_tests
+# )
+# (select date,
+# w_hospital_cases,
+# w_ventilator_beds,
+# w_new_cases,
+# w_new_deaths,
+# w_new_admissions,
+# w_new_pcr_tests,
+# w_new_tests,
+# w_new_pillar_1_2_tests
+# from ownd
+# where w_size = 7
+# )
+```
+
+```python Collapsed="false"
+%sql select * from uk_data_7 order by date desc limit 10
+```
+
+```python Collapsed="false"
+%sql select * from uk_data order by date desc limit 10
+```
+
+```python Collapsed="false"
+
+```
projects / covid19.git / blobdiff