X-Git-Url: https://git.njae.me.uk/?p=covid19.git;a=blobdiff_plain;f=publish.py;h=82a3a25dd53b07bfb4f0f08d527032c21620b952;hp=8ed4adb114363ce8e30a187e7a22dbb89b82aa39;hb=HEAD;hpb=5afedd66506be7575034ae6deebcfaa7c2ced978 diff --git a/publish.py b/publish.py index 8ed4adb..82a3a25 100644 --- a/publish.py +++ b/publish.py @@ -16,6 +16,7 @@ import os import matplotlib as mpl import matplotlib.pyplot as plt +# # %matplotlib inline # %% @@ -27,27 +28,15 @@ engine = sqlalchemy.create_engine(connection_string) # %% -# DEATH_COUNT_THRESHOLD = 10 -COUNTRIES_CORE = tuple('IT DE UK ES IE FR BE'.split()) -# COUNTRIES_NORDIC = 'SE NO DK FI UK'.split() -# COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split() -# # COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split() +COUNTRIES_CORE = tuple(sorted('ITA DEU GBR ESP IRL FRA BEL'.split())) +COUNTRIES_FRIENDS = tuple('ITA GBR ESP BEL SVN MEX'.split()) -# COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA -# 'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT', -# 'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN', -# 'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE'] -# COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS)) -# COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS)) - - -# # Write results to summary file # %% def singleton_sql_value(engine, query_string): with engine.connect() as conn: result = conn.execute(query_string) - return result.next()[0] + return result.first()[0] # %% @@ -55,7 +44,7 @@ last_uk_date = singleton_sql_value(engine, 'select max(date) from uk_data') # %% -last_intl_date = singleton_sql_value(engine, 'select max(report_date) from weekly_cases') +last_intl_date = singleton_sql_value(engine, 'select max(date) from weekly_cases') # %% @@ -63,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 @@ -94,11 +85,11 @@ with open('covid_summary.md', 'a') as f: # %% -query_string = f'''select geo_id, country_name, culm_deaths -from weekly_cases join countries using (geo_id) -where geo_id in {COUNTRIES_CORE} - and report_date = '{last_intl_date.isoformat()}' -order by geo_id''' +query_string = f'''select country_code, country, culm_deaths +from weekly_cases join countries using (country_code) +where country_code in {COUNTRIES_CORE} + and date = '{last_intl_date.isoformat()}' +order by country_code''' with engine.connect() as conn: results = list(conn.execute(query_string)) @@ -143,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') # %% @@ -220,23 +211,7 @@ with open('covid_summary.md', 'a') as f: # %% os.system('pandoc --toc -s covid_summary.md > covid_summary.html') - - -# %% os.system('scp covid_summary.html neil@ogedei:/var/www/scripts.njae.me.uk/covid/index.html') -os.system('scp covid_deaths_total_linear.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp cases_and_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp cases_and_deaths_last_60_days.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp deaths-radar-2021.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp covid_deaths_per_week.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp fraction_positive_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/ ') -os.system('scp tests_and_cases.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp deaths_by_date_last_6_weeks.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp fraction_positive_tests_vs_tests.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp tests_vs_fraction_positive_animation.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/ ') -os.system('scp people_in_hospital.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp cases_admissions_deaths.png neil@ogedei:/var/www/scripts.njae.me.uk/covid/') - # %% with open('uk_covid_deaths.js', 'w') as f: @@ -254,20 +229,35 @@ with open('last_uk_date.js', 'w') as f: with open('last_intl_date.js', 'w') as f: f.write(f"document.write('{pd.to_datetime(last_intl_date).strftime('%d %B %Y')}');") - # %% -os.system('scp uk_covid_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp uk_deaths_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp uk_cases_30_days.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -# # !scp estimated_total_deaths.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/ -# # !scp excess_deaths_upto.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/ -os.system('scp last_uk_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp last_intl_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/') -os.system('scp hospital_normalisation_date.js neil@ogedei:/var/www/scripts.njae.me.uk/covid/') - +transfer_files = [ + 'covid_deaths_total_linear.png', + 'cases_and_deaths.png', + 'cases_and_deaths_last_60_days.png', + 'deaths_radar_2021.png', + 'covid_deaths_per_week.png', + 'fraction_positive_tests.png', + 'tests_and_cases.png', + 'deaths_by_date_last_6_weeks.png', + 'fraction_positive_tests_vs_tests.png', + 'tests_vs_fraction_positive_animation.png', + 'people_in_hospital.png', + 'cases_admissions_deaths.png', + 'uk_covid_deaths.js', + 'uk_deaths_30_days.js', + 'uk_cases_30_days.js', + 'last_uk_date.js', + 'last_intl_date.js', + 'hospital_normalisation_date.js' +] # %% +for f in transfer_files: + if os.path.isfile(f): + os.system(f'scp {f} neil@ogedei:/var/www/scripts.njae.me.uk/covid/') + print(f'Transferred {f}') + else: + print(f'Cannot transfer {f}: file does not exist') - - +# %%