X-Git-Url: https://git.njae.me.uk/?a=blobdiff_plain;ds=sidebyside;f=cases_excess_deaths.md;fp=cases_excess_deaths.md;h=9cdd1d5832b45092073fb4ff71f66ba2788ff72b;hb=6fc69eb203d25465f8ccef719bc4d662dc66863a;hp=0000000000000000000000000000000000000000;hpb=4feace1e2a5076f3efa0fd65b31411ac511efbb1;p=covid19.git

diff --git a/cases_excess_deaths.md b/cases_excess_deaths.md
new file mode 100644
index 0000000..9cdd1d5
--- /dev/null
+++ b/cases_excess_deaths.md
@@ -0,0 +1,121 @@
+---
+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"
+
+```