projects
/
covid19.git
/ blobdiff
commit
grep
author
committer
pickaxe
?
search:
re
summary
|
shortlog
|
log
|
commit
|
commitdiff
|
tree
raw
|
inline
| side by side
General updates
[covid19.git]
/
uk_deaths_import.md
diff --git
a/uk_deaths_import.md
b/uk_deaths_import.md
index 612783c9d629b396cdf8d7372c009d0cd503b70f..1d3448643aee4ae3a0c7d5d3c052b109039d7042 100644
(file)
--- a/
uk_deaths_import.md
+++ b/
uk_deaths_import.md
@@
-6,7
+6,7
@@
jupyter:
extension: .md
format_name: markdown
format_version: '1.3'
extension: .md
format_name: markdown
format_version: '1.3'
- jupytext_version: 1.1
0.2
+ jupytext_version: 1.1
1.1
kernelspec:
display_name: Python 3
language: python
kernelspec:
display_name: Python 3
language: python
@@
-52,16
+52,20
@@
connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
engine = create_engine(connection_string)
```
engine = create_engine(connection_string)
```
+```python
+!ls uk-deaths-data/publishedweek*2021*xlsx
+```
+
```python Collapsed="false"
```python Collapsed="false"
-england_wales_filename = 'uk-deaths-data/publishedweek
05
2021.xlsx'
+england_wales_filename = 'uk-deaths-data/publishedweek
12
2021.xlsx'
```
```python Collapsed="false"
```
```python Collapsed="false"
-scotland_filename = 'uk-deaths-data/Scottish Government COVID-19 data (
15 February
2021).xlsx'
+scotland_filename = 'uk-deaths-data/Scottish Government COVID-19 data (
08 April
2021).xlsx'
```
```python
```
```python
-n_ireland_filename = 'uk-deaths-data/Weekly_Deaths
_0.xlsx
'
+n_ireland_filename = 'uk-deaths-data/Weekly_Deaths
- w e 2nd April 2021.XLSX
'
```
```python Collapsed="false"
```
```python Collapsed="false"
@@
-114,7
+118,7
@@
rd = eng_xls.iloc[1:][['Week ended', 'Wales']].reset_index(level=0).rename(
rd['year'] = 2021
rd['nation'] = 'Wales'
rd.dropna(inplace=True)
rd['year'] = 2021
rd['nation'] = 'Wales'
rd.dropna(inplace=True)
-rd.
head
()
+rd.
tail
()
```
```python
```
```python
@@
-136,7
+140,7
@@
rd.to_sql(
```
```python
```
```python
-%sql select * from all_causes_deaths where year = 2021 limit 10
+%sql select * from all_causes_deaths where year = 2021
order by date_up_to desc
limit 10
```
```python
```
```python
@@
-179,7
+183,7
@@
rd.to_sql(
```python
ni_xls = pd.read_excel(n_ireland_filename,
sheet_name='Table 1',
```python
ni_xls = pd.read_excel(n_ireland_filename,
sheet_name='Table 1',
- skiprows=[0, 1, 2
, 3
],
+ skiprows=[0, 1, 2],
header=0,
).rename(
columns={'Week Ending (Friday)': 'date_up_to', 'Total Number of Deaths Registered in Week (2021P)': 'deaths',
header=0,
).rename(
columns={'Week Ending (Friday)': 'date_up_to', 'Total Number of Deaths Registered in Week (2021P)': 'deaths',
@@
-187,7
+191,7
@@
ni_xls = pd.read_excel(n_ireland_filename,
rd = ni_xls[ni_xls['deaths'].notna()][['week', 'date_up_to', 'deaths']]
rd['year'] = 2021
rd['nation'] = 'Northern Ireland'
rd = ni_xls[ni_xls['deaths'].notna()][['week', 'date_up_to', 'deaths']]
rd['year'] = 2021
rd['nation'] = 'Northern Ireland'
-rd
+rd
.tail()
```
```python
```
```python
@@
-209,7
+213,7
@@
rd.to_sql(
```
```python
```
```python
-%sql select nation from all_causes_deaths group by nation
+%sql select nation
, count(*)
from all_causes_deaths group by nation
```
```python
```
```python