cases_excess_deaths.md

   1 ---
   2 jupyter:
   3   jupytext:
   4     formats: ipynb,md
   5     text_representation:
   6       extension: .md
   7       format_name: markdown
   8       format_version: '1.3'
   9       jupytext_version: 1.10.2
  10   kernelspec:
  11     display_name: Python 3
  12     language: python
  13     name: python3
  14 ---
  15
  16 ```python Collapsed="false"
  17 import itertools
  18 import collections
  19 import json
  20 import pandas as pd
  21 import numpy as np
  22 from scipy.stats import gmean
  23 import datetime
  24
  25 import matplotlib as mpl
  26 import matplotlib.pyplot as plt
  27 %matplotlib inline
  28 %load_ext sql
  29 ```
  30
  31 ```python Collapsed="false"
  32 connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
  33 ```
  34
  35 ```python Collapsed="false"
  36 %sql $connection_string
  37 ```
  38
  39 ```python
  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()
  42 # deaths_cases.head()
  43 ```
  44
  45 ```sql magic_args="res << select sum(new_deaths) as covid_deaths, extract(week from (date_trunc('day', date) + interval '2 day')) as eweek"
  46 from uk_data
  47 where extract(year from date) = 2020
  48 group by eweek
  49 order by eweek
  50 ```
  51
  52 ```python
  53 deaths_covid = res.DataFrame()
  54 deaths_covid.tail()
  55 ```
  56
  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
  61 where year < 2020
  62 group by week
  63 order by week
  64 ```
  65
  66 ```python
  67 mean_deaths = res.DataFrame()
  68 mean_deaths['deaths_mean'] = pd.to_numeric(mean_deaths.deaths_mean)
  69 mean_deaths.head()
  70 ```
  71
  72 ```python
  73 mean_deaths.dtypes
  74 ```
  75
  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
  83 order by ac.week
  84 ```
  85
  86 ```python
  87 deaths_2020 = res.DataFrame()
  88 deaths_2020.head()
  89 ```
  90
  91 ```python
  92 deaths_2020 = deaths_2020.merge(mean_deaths, how='outer', on='week')
  93 deaths_2020.head()
  94 ```
  95
  96 ```python
  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)
 100 deaths_2020.head()
 101 ```
 102
 103 ```python
 104 deaths_2020.tail()
 105 ```
 106
 107 ```python
 108 deaths_2020.dtypes
 109 ```
 110
 111 ```python
 112 deaths_2020[['deaths_covid', 'excess']].plot()
 113 ```
 114
 115 ```python
 116 deaths_2020.tail()
 117 ```
 118
 119 ```python Collapsed="false"
 120
 121 ```