Importing the data

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
  • Note that the latest date in this data is 2021-09-13 00:00:00.

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))

Weekly trend in Vorarlberg: Up 27.7% (147.5 cases per 100k). Percentage of ICU beds occupied: 15.6%

Weekly trend in Austria: Up19.7% (158.6 cases per 100k). Percentage of ICU beds occupied: 21.5%

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))

Burgenland is Red - Positivity: 0.58%, 14 day incidence: 203.03 ( from 191.89 the day before)

Kärnten is Orange - Positivity: 0.38%, 14 day incidence: 194.10 ( from 185.74 the day before)

Niederösterreich is Red - Positivity: 0.7%, 14 day incidence: 256.32 ( from 252.18 the day before)

Oberösterreich is Red - Positivity: 0.6%, 14 day incidence: 356.24 ( from 350.36 the day before)

Salzburg is Red - Positivity: 0.5%, 14 day incidence: 340.82 ( from 350.63 the day before)

Steiermark is Orange - Positivity: 0.38%, 14 day incidence: 197.74 ( from 192.21 the day before)

Tirol is Red - Positivity: 1.5%, 14 day incidence: 214.05 ( from 207.08 the day before)

Vorarlberg is Red - Positivity: 0.56%, 14 day incidence: 263.25 ( from 249.48 the day before)

Wien is Red - Positivity: 0.57%, 14 day incidence: 393.87 ( from 386.01 the day before)

Österreich is Red - Positivity: 0.57%, 14 day incidence: 290.79 ( from 285.07 the day before)

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'
)

Plotting the data

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