From 4104f54bfd2a8f5094704f1f78f8abf44dae0534 Mon Sep 17 00:00:00 2001 From: Neil Smith Date: Tue, 16 Mar 2021 14:42:15 +0000 Subject: [PATCH] Small updates --- covid_summary.md | 30 +++++++++++++++--------------- data_import.py | 6 ++++++ publish.py | 6 ++++-- uk_deaths_import.md | 12 ++++++++---- 4 files changed, 33 insertions(+), 21 deletions(-) diff --git a/covid_summary.md b/covid_summary.md index edc961e..c7b1620 100644 --- a/covid_summary.md +++ b/covid_summary.md @@ -1,20 +1,20 @@ % Covid death data summary % Neil Smith -% Created on 2021-02-21 +% Created on 2021-03-11 -> Last UK data from 20 Feb 2021. Last international data from 14 Feb 2021. +> Last UK data from 11 Mar 2021. Last international data from 07 Mar 2021. ## Headlines (UK data) | []() | | |:---|---:| -| Deaths reported so far | 120387 | -| Deaths in last 30 days | 25785 | -| Cases in last 30 days | 480504 | +| Deaths reported so far | 125168 | +| Deaths in last 30 days | 11147 | +| Cases in last 30 days | 237470 | ## International comparison -Based on weekly data. Last data from 14 Feb 2021 +Based on weekly data. Last data from 07 Mar 2021 ### Total deaths @@ -22,13 +22,13 @@ Based on weekly data. Last data from 14 Feb 2021 | Country ID | Country name | Total deaths | |:-----------|:-------------|-------------:| -| BEL | Belgium | 21720 | -| DEU | Germany | 65076 | -| ESP | Spain | 65449 | -| FRA | France | 81814 | -| GBR | United Kingdom | 117166 | -| IRL | Ireland | 3948 | -| ITA | Italy | 93577 | +| BEL | Belgium | 22287 | +| DEU | Germany | 71934 | +| ESP | Spain | 71436 | +| FRA | France | 88600 | +| GBR | United Kingdom | 124501 | +| IRL | Ireland | 4422 | +| ITA | Italy | 99785 | ### Deaths per week @@ -40,13 +40,13 @@ Based on weekly data. Last data from 14 Feb 2021 ### Total deaths -Deaths reported up to 20 Feb 2021: 120387 +Deaths reported up to 11 Mar 2021: 125168 ![Total deaths](cases_and_deaths.png) ![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png) -![Deaths compared to past five years](deaths-radar-2021.png) +![Deaths compared to past five years](deaths_radar_2021.png) ### Hospital care Based on a 7-day moving average diff --git a/data_import.py b/data_import.py index 8ec03d1..833a6eb 100644 --- a/data_import.py +++ b/data_import.py @@ -171,6 +171,7 @@ uk_query_string = ( "metric=newCasesBySpecimenDate&" "metric=hospitalCases&" "metric=newDeaths28DaysByPublishDate&" +"metric=cumDeaths28DaysByPublishDate&" "format=csv" ) @@ -210,6 +211,7 @@ uk_data.rename( 'newCasesBySpecimenDate': 'new_cases', 'hospitalCases': 'hospital_cases', 'newDeaths28DaysByPublishDate': 'new_deaths', + 'cumDeaths28DaysByPublishDate': 'cum_deaths', 'newAdmissions': 'new_admissions', 'newPCRTestsByPublishDate': 'new_pcr_tests', 'newTestsByPublishDate': 'new_tests', @@ -221,6 +223,7 @@ uk_data.rename( uk_data[['date', 'hospital_cases', 'ventilator_beds', 'new_cases', 'new_deaths', + 'cum_deaths', 'hospital_cases', 'new_admissions', 'new_pcr_tests', 'new_tests', 'new_pillar_1_2_tests' ]].to_sql( @@ -236,6 +239,7 @@ uk_data[['date', "new_cases": Integer, "hospital_cases": Integer, "new_deaths": Integer, + "cum_deaths": Integer, "new_admissions": Integer, 'new_pcr_tests': Integer, 'new_tests': Integer, @@ -305,3 +309,5 @@ insert into uk_data_7(date, with engine.connect() as connection: connection.execute(query_string) + +# %% diff --git a/publish.py b/publish.py index 8dd4b0d..d60a7ef 100644 --- a/publish.py +++ b/publish.py @@ -52,7 +52,9 @@ thirty_days_ago = last_uk_date - datetime.timedelta(days=30) # %% -total_uk_deaths = singleton_sql_value(engine, 'select sum(new_deaths) from uk_data') +# total_uk_deaths = singleton_sql_value(engine, 'select (cum_deaths) from uk_data') +total_uk_deaths = singleton_sql_value(engine, + 'select cum_deaths from uk_data where cum_deaths > 0 order by date desc limit 1') deaths_in_past_month = singleton_sql_value(engine, f"select sum(new_deaths) from uk_data where date > '{thirty_days_ago.isoformat()}'") cases_in_past_month = singleton_sql_value(engine, f"select sum(new_cases) from uk_data where date > '{thirty_days_ago.isoformat()}'") total_uk_deaths, deaths_in_past_month, cases_in_past_month @@ -132,7 +134,7 @@ with open('covid_summary.md', 'a') as f: f.write('\n') f.write('![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png)\n') f.write('\n') - f.write('![Deaths compared to past five years](deaths-radar-2021.png)\n') + f.write('![Deaths compared to past five years](deaths_radar_2021.png)\n') f.write('\n') # %% diff --git a/uk_deaths_import.md b/uk_deaths_import.md index 612783c..baa5ae6 100644 --- a/uk_deaths_import.md +++ b/uk_deaths_import.md @@ -52,16 +52,20 @@ connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid' 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/publishedweek082021.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 (10 March 2021).xlsx' ``` ```python -n_ireland_filename = 'uk-deaths-data/Weekly_Deaths_0.xlsx' +n_ireland_filename = 'uk-deaths-data/Weekly_Deaths.xlsx' ``` ```python Collapsed="false" @@ -136,7 +140,7 @@ rd.to_sql( ``` ```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 -- 2.34.1