<jupyter_start><jupyter_text>"World-wide excess deaths in the time of COVID-19"> "This notebook visualises excess deaths around the world. It's very much work in progress. In fact, it may never progress past its current state. Test"- toc: true- branch: master- badges: true- comments: false- author: <NAME> (https://github.com/sj)- categories: [fastpages, jupyter, covid19]<jupyter_code>import pandas as pd
import numpy as np
import scipy.stats as st

# Altair visualisations. Install using pip:
# pip install altair
import altair as alt
file_url = "https://raw.githubusercontent.com/sj/covid19-excess-deaths-data/master/weekly-deaths.csv"
#file_url = "../../covid19-excess-deaths-data/weekly-deaths.csv"

# Read data into Pandas dataframe
df = pd.read_csv(file_url, sep=';', parse_dates=['first_day', 'last_day'])

## Create separate column containing the year of the measurement. Will be used to split up
## years in different data series in the visualisation.
df['first_day:year'] = df['first_day'].dt.year
df['last_day:year'] = df['last_day'].dt.year

## Create column for day-of-the-year to plot the different series for the different years
df['first_day:yday'] = df['first_day'].dt.strftime('%j').astype(int)
df['last_day:yday'] = df['last_day'].dt.strftime('%j').astype(int)

## Create column for the middle day 
df['middle_day:yday'] = (df['first_day:yday'] + (df['last_day:yday'] - df['first_day:yday']) / 2.0).astype(int)

## Just do NL for now
df = df[df['country'] == 'NL']

## To investigate/display the entire dataset, uncomment the following:
#with pd.option_context('display.max_rows', 2000):
display(df)
# Select pre-COVID data (everything before 2020) and build confidence range
df_precovid = df[df['first_day:year'] < 2020]

## There must be a neater way to do this with Pandas, but for now we'll use this way to calculate 
## a rolling-window mean and stddev for every day of the year.
window_prefix_postfix = 14 # 5 days before every day, the day itself, 5 days after every day

ydays_means_stdevs = []
means = []
stdevs = []
for yday in range (1, 366):
    
    
    window_first = yday - window_prefix_postfix - 1
    window_last = yday + window_prefix_postfix
    
    window_ydays = range(window_first + 365, window_last + 365)
    window_ydays = [(yday % 365) + 1 for yday in window_ydays]
    
    # If window straddles multiple years, also include data from 366th day (for leap years)
    if window_ydays[0] > window_ydays[-1]:
        window_ydays.append(366)
        
    #print(str(yday) + ": " + str(window_ydays))
    
    df_relevant = df_precovid[df_precovid['middle_day:yday'].isin(window_ydays)]
    
    ydays_means_stdevs.append([yday, df_relevant['deaths'].mean(), df_relevant['deaths'].std(), df_relevant['deaths'].count()])

# Account for leap years. Mean and stddev of day 366 is same as for day 365
ydays_means_stdevs.append(ydays_means_stdevs[-1])
ydays_means_stdevs[-1][0] = 366
df_ydays_means_stdevs = pd.DataFrame(ydays_means_stdevs, columns = ["yday", "mean", "stdev", "sample_size"])

# Define 68% (1 stdev) and 95% (2 stdev) confidence intervals
df_ydays_means_stdevs['ci_68_lower'] = df_ydays_means_stdevs['mean'] - df_ydays_means_stdevs['stdev']
df_ydays_means_stdevs['ci_68_upper'] = df_ydays_means_stdevs['mean'] + df_ydays_means_stdevs['stdev']
df_ydays_means_stdevs['ci_95_lower'] = df_ydays_means_stdevs['mean'] - 2 * df_ydays_means_stdevs['stdev']
df_ydays_means_stdevs['ci_95_upper'] = df_ydays_means_stdevs['mean'] + 2 * df_ydays_means_stdevs['stdev']

df_ydays_means_stdevs
# Combine yday, mean, stdev into new dataframe

#print(means)
#print(stdevs)
#df_precovid.rolling()
#st.t.interval(0.95, len(df_precovid)-1, loc=np.mean(df_precovid['deaths']))

#alt.Chart(df_precovid).mark_line().encode(
#    alt.X('first_day:yday', type='quantitative'),
#    alt.Y('deaths', type='quantitative')
#)
legend_selection = alt.selection_multi(fields=['first_day:year'], bind='legend')

# Plots a single line for every year in an interactive chart
df['line_size'] = df.apply(lambda row: 0.5 if row['first_day:year'] != 2020 else 1, axis = 1)

df_recent = df[df['first_day:year'] > 2015]
line = alt.Chart(df).mark_line().encode(
    alt.X(
        'first_day:yday', type='quantitative', 
        title='Day of the year (week following)',
        scale=alt.Scale(domain=(1, 366))
    ),
    alt.Y('deaths', type='quantitative', title='Number of deaths'),
    
    # Documentation on available colour schemes in Vega: https://vega.github.io/vega/docs/schemes/
    alt.Color('first_day:year', type='ordinal', scale=alt.Scale(scheme='yellowgreenblue'), title='Year'),
    
    # Enable selection of series (years) from the legend
    opacity=alt.condition(legend_selection, alt.value(1), alt.value(0.3)),
    
    # Display tooltips
    tooltip=[alt.Tooltip('country', title='Country'), 
             alt.Tooltip('first_day', title='Week starting'),
             alt.Tooltip('deaths', title='Number of deaths')
    ],
    
    size = alt.condition(legend_selection, alt.value(1.5), alt.value(0.7))
).properties(
    width=800
).interactive().add_selection(legend_selection) #.add_selection(alt.selection_interval(bind='scales'))

ci68_band = alt.Chart(df_ydays_means_stdevs).mark_area(
    opacity = 0.3,
    color = 'goldenrod'
).encode(
    x = 'yday',
    y = 'ci_68_lower',
    y2 = 'ci_68_upper',

)

ci95_band = alt.Chart(df_ydays_means_stdevs).mark_area(
    opacity = 0.2,
    color = 'goldenrod'
).encode(
    x = 'yday',
    y = 'ci_95_lower',
    y2 = 'ci_95_upper',

)

mean_line = alt.Chart(df_ydays_means_stdevs).mark_line(
    strokeDash=[3,5],
    color = 'black',
    opacity = 0.5,
    size = 1
).encode(
    x = 'yday',
    y = 'mean'
)

#band = alt.Chart(df).mark_errorband(extent='ci').encode(
#    x = 'first_day',
#    y = 'deaths'
#)

line + ci68_band + ci95_band + mean_line<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1450706623531415, 'fraction_numerical': 0.022816277881832114, 'mean_word_length': 4.610315186246418, 'pattern_counts': {'":': 1, '<': 7, '<?xml version=': 0, '>': 9, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '72827', 'license': 'Apache-2.0', 'n_tokens_mistral': 2254, 'n_tokens_neox': 2108, 'n_words': 481, 'path': '_notebooks/covid19-excess-deaths.ipynb', 'repo_name': 'sj/covid19-excess-deaths-fastpages', 'size': '5881'}