Austrian Covid data exploration
Exploring the latest figures of COVID19 in Austria
The data used in this post can be found at https://www.data.gv.at/covid-19/. After downloading the CSV file called CovidFaelle_Timeline.csv, we need to do some cleaning of the date column and split off some specific sets - the numbers for Austria, Volarlberg, Tirol and Wien. We also need to format the decimal numbers since the CSV file uses a ',' instead of a '.' as decimal.
data_url = "https://raw.githubusercontent.com/idjotherwise/nlp-otherwise/master/data_sets/at_full_data.csv"
full_data = pd.read_csv(data_url, parse_dates=['Time'])
current_date = full_data.iloc[-1,0]
all_austria = full_data.query("BundeslandID==10").sort_values(by='Time')
string_to_md = f"- Note that the latest date in this data is {current_date}."
display(Markdown(string_to_md))
latest_data = full_data.query(f"Time>='{str((current_date - datetime.timedelta(7)).date())}'")
saturdays_data = full_data.query("Time.dt.dayofweek == 5").copy()
saturdays_data['rate_change']=saturdays_data.groupby(by='Bundesland').SiebenTageInzidenzFaelle.pct_change().replace(np.inf, np.nan).fillna(0)*100
latest_rate_vbg = latest_data.query("Bundesland=='Vorarlberg'").SiebenTageInzidenzFaelle
latest_rate_aus = latest_data.query("Bundesland=='Österreich'").SiebenTageInzidenzFaelle
vbg_change = round(list(latest_rate_vbg)[-1] - list(latest_rate_vbg)[0]) / list(latest_rate_vbg)[0]
aus_change = round(list(latest_rate_aus)[-1] - list(latest_rate_aus)[0]) / list(latest_rate_aus)[0]
week_trend_vbg = f'<span style="color: green;">Down</span> {vbg_change:.1%}' if vbg_change < 0 else f'<span style="color: red;">Up</span> {vbg_change:.1%}'
week_trend_aus = f'<span style="color: green;">Down</span> {aus_change:.1%}' if aus_change < 0 else f'<span style="color: red;">Up</span>{aus_change:.1%}'
vbg_string = f"Weekly trend in **Vorarlberg**: {week_trend_vbg} ({latest_rate_vbg.iloc[-1]:.4} cases per 100k). Percentage of ICU beds occupied: {full_data.query('BundeslandID==8').ICUTakenPercent.iloc[-1]/100:.1%}"
aus_string = f"Weekly trend in **Austria**: {week_trend_aus} ({latest_rate_aus.iloc[-1]:.4} cases per 100k). Percentage of ICU beds occupied: {full_data.query('BundeslandID==10').ICUTakenPercent.iloc[-1]/100:.1%}"
display(Markdown(vbg_string))
display(Markdown(aus_string))
def traffic_light(x, y):
if y >= 500:
return '<span style="color: darkred;">Dark Red</span>'
if x < 1:
if y < 75:
return '<span style="color: green;">Green</span>'
if y < 200:
return '<span style="color: orange;">Orange</span>'
if y < 500:
return '<span style="color: red;">Red</span>'
if 1 <= x < 4:
if y < 50:
return '<span style="color: green;">Green</span>'
if y <= 200:
return '<span style="color: orange;">Orange</span>'
if y < 500:
return '<span style="color: red;">Red</span>'
if x >= 4:
if y < 75:
return '<span style="color: orange;">Orange</span>'
if y < 500:
return '<span style="color: red;">Red</span>'
else:
return '<span style="color: grey;">Grey</span>'
strings = []
for country in full_data.Bundesland.unique():
country_data = full_data.query('Bundesland == @country')
positivity, caserate = country_data.Positivity.iloc[-1], country_data.TwoWeeklyCasesRate.iloc[-1]
previous_caserate = country_data.TwoWeeklyCasesRate.iloc[-2]
previous_text = f"(<span style='color: green'>⬇</span> from {previous_caserate:.2f} the day before)" if previous_caserate > caserate else f"(<span style='color:red;'>⬆</span> from {previous_caserate:.2f} the day before)"
text = traffic_light(positivity,caserate)
strings.append(f"{country} is {text} - Positivity: {positivity:.2}%, 14 day incidence: {caserate:.2f} {previous_text}")
for s in strings:
display(Markdown(s))
source = saturdays_data.query("Time >= '2021-01-31'")
alt.Chart(source).mark_circle(
opacity=0.8,
stroke='black',
strokeWidth=1
).encode(
alt.X('yearmonthdate(Time):T', axis=alt.Axis(title='', labelAngle=-45)),
alt.Y('Bundesland:N'),
alt.Size('rate_change:Q',
scale=alt.Scale(range=[0, 500]),
legend=alt.Legend(title='Percentage change')
),
alt.Color('Bundesland:N', legend=None),
tooltip=['Time', 'rate_change']
).properties(
width=600,
height=320,
title='Percentage change of incidence rate from the previous week'
)
Here is a historical plot of the 'traffic light colours' in Austria, as defined by the European Centre for Disease Prevention and Control. See also my other post about the traffic light system for countries in the UK here, where I show what the rules are for the different traffic lights.
Notice how long Austria was in the 'Red' (and 'Dark red') between September and May - almost 6 months!
base = alt.Chart(full_data.query("BundeslandID==10")).mark_point(size=2).encode(
x=alt.X("yearmonthdate(Time):T", axis=alt.Axis(title='Date')),
y=alt.Y("TwoWeeklyCasesRate:Q",
axis=alt.Axis(title='Cases per 100k')),
tooltip=['Time:T','TwoWeeklyCasesRate:Q', 'Positivity:Q']
).properties(
title='Number of cases per 100,000 in Austria', width=800
)
chart_to_show = alt.layer(
base.encode(color=alt.condition((alt.datum.Positivity < 4) &
(alt.datum.TwoWeeklyCasesRate >= 75) &
(alt.datum.TwoWeeklyCasesRate < 200),
alt.ColorValue('orange'),
alt.ColorValue('red')
),
opacity=alt.condition((alt.datum.TwoWeeklyCasesRate >= 75) &
(alt.datum.TwoWeeklyCasesRate < 500),
alt.value(1),
alt.value(0)
)
),
base.encode(color=alt.value('darkred'),
opacity=alt.condition(alt.datum.TwoWeeklyCasesRate >= 500,
alt.value(1),
alt.value(0)
)
),
base.encode(color=alt.condition((alt.datum.Positivity < 4) &
(alt.datum.TwoWeeklyCasesRate < 50),
alt.ColorValue('green'),
alt.ColorValue('orange')
),
opacity=alt.condition((alt.datum.TwoWeeklyCasesRate < 75) &
(alt.datum.Positivity >= 1),
alt.value(1),
alt.value(0)
)
),
base.encode(color=alt.ColorValue('green'),
opacity=alt.condition((alt.datum.TwoWeeklyCasesRate < 75) & (alt.datum.Positivity < 1),
alt.value(1),
alt.value(0)
)
)
)
chart_to_show.interactive()