--- /dev/null
+---
+jupyter:
+ jupytext:
+ formats: ipynb,md
+ text_representation:
+ extension: .md
+ format_name: markdown
+ format_version: '1.3'
+ jupytext_version: 1.10.2
+ kernelspec:
+ display_name: Python 3
+ language: python
+ name: python3
+---
+
+```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
+%load_ext sql
+```
+
+```python Collapsed="false"
+connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
+```
+
+```python Collapsed="false"
+%sql $connection_string
+```
+
+```python
+# res = %sql select report_date, deaths_weekly as deaths_covid from weekly_cases where geo_id = 'UK' order by report_date
+# deaths_cases = res.DataFrame()
+# deaths_cases.head()
+```
+
+```sql magic_args="res << select sum(new_deaths) as covid_deaths, extract(week from (date_trunc('day', date) + interval '2 day')) as eweek"
+from uk_data
+where extract(year from date) = 2020
+group by eweek
+order by eweek
+```
+
+```python
+deaths_covid = res.DataFrame()
+deaths_covid.tail()
+```
+
+```sql magic_args="res << select week, avg(ydd) as deaths_mean"
+from (select week, year, sum(deaths) as ydd
+ from all_causes_deaths
+ group by year, week) as year_deaths
+where year < 2020
+group by week
+order by week
+```
+
+```python
+mean_deaths = res.DataFrame()
+mean_deaths['deaths_mean'] = pd.to_numeric(mean_deaths.deaths_mean)
+mean_deaths.head()
+```
+
+```python
+mean_deaths.dtypes
+```
+
+```sql magic_args="res << select ac.week, wk.date_up_to, sum(ac.deaths) as deaths_2020"
+from all_causes_deaths ac,
+ (select week, date_up_to
+ from all_causes_deaths
+ where year = 2020 and nation = 'England') as wk
+where year = 2020 and ac.week = wk.week
+group by ac.week, wk.date_up_to
+order by ac.week
+```
+
+```python
+deaths_2020 = res.DataFrame()
+deaths_2020.head()
+```
+
+```python
+deaths_2020 = deaths_2020.merge(mean_deaths, how='outer', on='week')
+deaths_2020.head()
+```
+
+```python
+deaths_2020['deaths_covid'] = deaths_covid.covid_deaths
+deaths_2020['excess'] = deaths_2020.deaths_2020 - deaths_2020.deaths_mean
+deaths_2020.set_index('week', inplace=True)
+deaths_2020.head()
+```
+
+```python
+deaths_2020.tail()
+```
+
+```python
+deaths_2020.dtypes
+```
+
+```python
+deaths_2020[['deaths_covid', 'excess']].plot()
+```
+
+```python
+deaths_2020.tail()
+```
+
+```python Collapsed="false"
+
+```