text_representation:
extension: .md
format_name: markdown
- format_version: '1.2'
- jupytext_version: 1.9.1
+ format_version: '1.3'
+ jupytext_version: 1.11.1
kernelspec:
display_name: Python 3
language: python
engine = create_engine(connection_string)
```
+```python
+!ls uk-deaths-data/publishedweek*2021*xlsx
+```
+
```python Collapsed="false"
-england_wales_filename = 'uk-deaths-data/publishedweek052021.xlsx'
+england_wales_filename = 'uk-deaths-data/publishedweek122021.xlsx'
```
```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
-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"
+# eng_xls = pd.read_excel(england_wales_filename,
+# sheet_name="Weekly figures 2021",
+# skiprows=[0, 1, 2, 3],
+# skipfooter=11,
+# header=0,
+# index_col=[1]
+# ).iloc[:99].T
+# eng_xls
```
```python Collapsed="false"
eng_xls = pd.read_excel(england_wales_filename,
sheet_name="Weekly figures 2021",
skiprows=[0, 1, 2, 3],
- skipfooter=11,
+ skipfooter=10,
header=0,
index_col=[1]
- ).iloc[:91].T
+ ).T
eng_xls
```
rd['year'] = 2021
rd['nation'] = 'Wales'
rd.dropna(inplace=True)
-rd.head()
+rd.tail()
```
```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
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',
rd = ni_xls[ni_xls['deaths'].notna()][['week', 'date_up_to', 'deaths']]
rd['year'] = 2021
rd['nation'] = 'Northern Ireland'
-rd
+rd.tail()
```
```python
```
```python
-%sql select nation from all_causes_deaths group by nation
+%sql select nation, count(*) from all_causes_deaths group by nation
```
```python
ax.set_xticklabels(dataset.index)
plt.legend()
plt.title(f"Deaths by week over years, {title_string}")
- plt.savefig(f'deaths-radar-2021{filename_suffix}.png')
+ plt.savefig(f'deaths_radar_2021{filename_suffix}.png')
plt.show()
```
<!-- #endregion -->
```python
-create_and_save_radar_plot(deaths_e, 'England', '-england')
+create_and_save_radar_plot(deaths_e, 'England', '_england')
```
```python
-create_and_save_radar_plot(deaths_w, 'Wales', '-wales')
+create_and_save_radar_plot(deaths_w, 'Wales', '_wales')
```
```python
-create_and_save_radar_plot(deaths_s, 'Scotland', '-scotland')
+create_and_save_radar_plot(deaths_s, 'Scotland', '_scotland')
```
```python
-create_and_save_radar_plot(deaths_i, 'Northern Ireland', '-northern-ireland')
+create_and_save_radar_plot(deaths_i, 'Northern Ireland', '_northern_ireland')
```
```python Collapsed="false"