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()
Next we have the 7 day incidence rate for states of Vorarlberg, Tirol and Wien compared to all of Austria. If you are on a computer, you can drag select the date range of the next graph by selecting it on the small graph at the bottom. The bar chart in the middle shows the maximum incidence rate for each state within that date range, while the bar chart on the right shows the maximum incidence rate up to before the end of the date range selected. Selecting the state from the legend will highlight the corresponding line in the charts.
start_date = full_data['Time'].min()
end_date = full_data['Time'].max()
x_init = pd.to_datetime([start_date, end_date]).astype(int) / 1E6
leg_selection = alt.selection_multi(fields=['Bundesland'], bind='legend')
brush = alt.selection(type='interval', name='DateBrush',encodings=['x'],fields=['Time'], init={'Time': list(x_init)})
base = alt.Chart(data_url).mark_line().encode(
x=alt.X("yearmonthdate(Time):T", axis=alt.Axis(title='Date')),
y=alt.Y("SiebenTageInzidenzFaelle:Q", axis=alt.Axis(title='Incidence rate')),
tooltip=['Bundesland:N', "SiebenTageInzidenzFaelle:Q", 'yearmonthdate(Time):T'],
color='Bundesland:N',
opacity=alt.condition(leg_selection, alt.value(2), alt.value(0.1))
).add_selection(leg_selection).properties(width=600)
upper = base.encode(
alt.X('yearmonthdate(Time):T',axis=alt.Axis(title=''),
scale=alt.Scale(domain=brush))
).properties(title='7 day incidence rate for states in Austria')
lower = base.properties(
height=60, width=600
).add_selection(brush)
# bars_max = base.transform_filter('datum.Time >= DateBrush.Time[0] && datum.Time <= DateBrush.Time[1]').mark_bar().encode(
# y = alt.Y('SiebenTageInzidenzFaelle:Q', title=None),
# x = alt.X('Bundesland:N', title=None),
# color='Bundesland',
# opacity=alt.condition(leg_selection, alt.value(2), alt.value(0)),
# ).properties(width=150, title='Max incidence rate (selected)')
# bars_current = base.transform_filter('datum.Time <= DateBrush.Time[1]').mark_bar().encode(
# y = alt.Y('SiebenTageInzidenzFaelle:Q', title=None),
# x = alt.X('Bundesland:N', title=None),
# color='Bundesland',
# opacity=alt.condition(leg_selection, alt.value(2), alt.value(0)),
# ).properties(width=150, title='Max incidence rate (all time)')
# alt.vconcat(alt.hconcat(upper, bars_max, bars_current).resolve_scale(y='shared'),lower)
upper & lower
brush = alt.selection(type='interval', encodings=['x'])
states = full_data['Bundesland'].unique()
states.sort()
selection = alt.selection_single(
name='Select',
fields=['Bundesland'],
init={'Bundesland': 'Vorarlberg'},
bind={'Bundesland': alt.binding_select(options=states)}
)
bars = alt.Chart(data_url).mark_bar().add_selection(
selection
).encode(
x=alt.X("yearmonthdate(Time):T", axis=alt.Axis(title='Date')),
y=alt.Y("TwoWeeklyCasesRate:Q", axis=alt.Axis(title='Incidence rate')),
tooltip=['TwoWeeklyCasesRate:Q', 'Bundesland:N', 'yearmonthdate(Time):T'],
opacity=alt.condition(selection, alt.value(1), alt.value(0))
).properties(title=f'14 day incidence rate of individual states vs rolling mean across Austria', width=800)
line = alt.Chart(full_data.query("BundeslandID==10")).mark_line(
color='red',
size=2,
).transform_window(
rolling_mean='mean(TwoWeeklyCasesRate)',
frame=[-3, 3]
).encode(
x='yearmonthdate(Time):T',
y='rolling_mean:Q',
tooltip=['TwoWeeklyCasesRate:Q', 'Bundesland', 'yearmonthdate(Time):T']
)
base = alt.layer(bars, line)
upper_bars = bars.encode(
alt.X('yearmonthdate(Time):T',axis=alt.Axis(title='Date'),
scale=alt.Scale(domain=brush))
)
upper_line = line.encode(
alt.X('yearmonthdate(Time):T',axis=alt.Axis(title='Date'),
scale=alt.Scale(domain=brush))
)
upper = alt.layer(upper_bars, upper_line)
lower = base.properties(
height=60
).add_selection(brush)
upper & lower