9 jupytext_version: 1.10.2
11 display_name: Python 3
16 ```python Collapsed="false"
22 from scipy.stats import gmean
25 import matplotlib as mpl
26 import matplotlib.pyplot as plt
31 ```python Collapsed="false"
32 connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
35 ```python Collapsed="false"
36 %sql $connection_string
40 # res = %sql select report_date, deaths_weekly as deaths_covid from weekly_cases where geo_id = 'UK' order by report_date
41 # deaths_cases = res.DataFrame()
45 ```sql magic_args="res << select sum(new_deaths) as covid_deaths, extract(week from (date_trunc('day', date) + interval '2 day')) as eweek"
47 where extract(year from date) = 2020
53 deaths_covid = res.DataFrame()
57 ```sql magic_args="res << select week, avg(ydd) as deaths_mean"
58 from (select week, year, sum(deaths) as ydd
59 from all_causes_deaths
60 group by year, week) as year_deaths
67 mean_deaths = res.DataFrame()
68 mean_deaths['deaths_mean'] = pd.to_numeric(mean_deaths.deaths_mean)
76 ```sql magic_args="res << select ac.week, wk.date_up_to, sum(ac.deaths) as deaths_2020"
77 from all_causes_deaths ac,
78 (select week, date_up_to
79 from all_causes_deaths
80 where year = 2020 and nation = 'England') as wk
81 where year = 2020 and ac.week = wk.week
82 group by ac.week, wk.date_up_to
87 deaths_2020 = res.DataFrame()
92 deaths_2020 = deaths_2020.merge(mean_deaths, how='outer', on='week')
97 deaths_2020['deaths_covid'] = deaths_covid.covid_deaths
98 deaths_2020['excess'] = deaths_2020.deaths_2020 - deaths_2020.deaths_mean
99 deaths_2020.set_index('week', inplace=True)
112 deaths_2020[['deaths_covid', 'excess']].plot()
119 ```python Collapsed="false"