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 2022-05-22 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 -31.1% (188.6 cases per 100k). Percentage of ICU beds occupied: 5.6%

Weekly trend in Austria: Down -32.2% (231.5 cases per 100k). Percentage of ICU beds occupied: 6.7%

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: 3.2%, 14 day incidence: 671.94 ( from 703.35 the day before)

Kärnten is Red - Positivity: 7.6%, 14 day incidence: 430.89 ( from 450.46 the day before)

Niederösterreich is Dark Red - Positivity: 0.7%, 14 day incidence: 651.55 ( from 680.24 the day before)

Oberösterreich is Red - Positivity: 6.4%, 14 day incidence: 464.96 ( from 483.42 the day before)

Salzburg is Dark Red - Positivity: 1.2e+01%, 14 day incidence: 512.03 ( from 520.59 the day before)

Steiermark is Red - Positivity: 4.4%, 14 day incidence: 425.88 ( from 438.63 the day before)

Tirol is Dark Red - Positivity: 1.1e+01%, 14 day incidence: 533.08 ( from 551.50 the day before)

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

Wien is Dark Red - Positivity: 1.1%, 14 day incidence: 768.89 ( from 810.95 the day before)

Österreich is Dark Red - Positivity: 1.5%, 14 day incidence: 573.51 ( from 597.94 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()