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-12-04 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: Down -21.7% (1.024e+03 cases per 100k). Percentage of ICU beds occupied: 32.5%

Weekly trend in Austria: Down -36.4% (618.2 cases per 100k). Percentage of ICU beds occupied: 54.6%

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 Dark Red - Positivity: 2.4%, 14 day incidence: 1045.57 ( from 1065.17 the day before)

Kärnten is Dark Red - Positivity: 5.5%, 14 day incidence: 2267.43 ( from 2418.66 the day before)

Niederösterreich is Dark Red - Positivity: 4.9%, 14 day incidence: 1369.52 ( from 1488.57 the day before)

Oberösterreich is Dark Red - Positivity: 3.2%, 14 day incidence: 2082.77 ( from 2243.30 the day before)

Salzburg is Dark Red - Positivity: 2.3%, 14 day incidence: 2197.75 ( from 2348.99 the day before)

Steiermark is Dark Red - Positivity: 3.0%, 14 day incidence: 1458.53 ( from 1502.95 the day before)

Tirol is Dark Red - Positivity: 5.5%, 14 day incidence: 2027.22 ( from 2170.09 the day before)

Vorarlberg is Dark Red - Positivity: 1.1e+01%, 14 day incidence: 2331.45 ( from 2451.93 the day before)

Wien is Dark Red - Positivity: 0.48%, 14 day incidence: 868.79 ( from 905.23 the day before)

Österreich is Dark Red - Positivity: 2.1%, 14 day incidence: 1590.40 ( from 1691.05 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