ECDC's Covid traffic-light system

• Summary:
• Plots
• Traffic Lights
  • Wales
  • England
  • Scotland
  • Northern Ireland

The European Center for Disease Prevention and Control (ECDC) publishes situation updates on COVID-19 for each country in the EU. Part of this reporting relates to the traffic-light system which informs the travel restrictions between countries within the EU (see for example https://reopen.europa.eu/en, which has a tool to say what the restrictions are between any two countries). The rules for each traffic light color is the following (see the ecdc website, updated 14 June 2021):

• Green:
  • if the 14-day notification rate is less than 50 and the test positivity rate is less than 4%; or
  • if the 14-day notification rate is less than 75 and the test positivity rate less than 1%
• Orange:
  • if the 14-day notification rate is less than 50 and the test positivity rate is 4% or more; or
  • the 14-day notification rate is 50 or more and less than 75 and the test positivity rate is 1% or more; or
  • the 14-day notification rate is between 75 and 200 and the test positivity rate is less than 4%
• Red:
  • if the 14-day cumulative COVID-19 case notification rate ranges from 75 to 200 and the test positivity rate of tests for COVID-19 infection is 4% or more; or
  • if the 14-day cumulative COVID-19 case notification rate is more than 200 but less than 500
• Dark red:
  • if the 14-day cumulative COVID-19 case notification rate is 500 or more
• Grey:
  • if there is insufficient information or if the testing rate is lower than 300 cases per 100 000.

And here it is in picture form:

filter_all_nations = [
    "areaType=nation"
]
filter_all_uk = [
    "areaType=overview"
]

structure_cases_death = {
    "date": "date",
    "areaName": "areaName",
    "newCases": "newCasesByPublishDate",
    "cumCases": "cumCasesBySpecimenDate",
    "cumCasesRate": "cumCasesBySpecimenDateRate",
    "newDeaths": "newDeathsByDeathDate",
    "newTests": "newTestsByPublishDate"
}

uk_cases = Cov19API(filters=filter_all_nations,
                    structure=structure_cases_death).get_dataframe().fillna(0)

uk_cases['date'] = pd.to_datetime(uk_cases['date'], format='%Y-%m-%d')
uk_cases.sort_values(['areaName', 'date'], inplace=True)
uk_cases.reset_index(drop=True, inplace=True)

date_list = ['2020-12-13', '2020-12-14',
             '2020-12-15', '2020-12-16', '2020-12-17']

uk_cases.iloc[(uk_cases.query("areaName=='Wales'").query("date==@date_list").index), 2] = np.flip(
    np.array(list(range(2494 + int((2801 - 2494)/6), 2801 - int((2801 - 2494)/6), int((2801 - 2494)/6)))))

countries = ['Wales', 'Scotland', 'Northern Ireland', 'England']
countries_population = dict()
for country in countries:
    countries_population[country] = round(100000 * uk_cases.query(
        "areaName == @country").cumCases.max() / uk_cases.query("areaName == @country").cumCasesRate.max())

if 'population' not in uk_cases.columns:
    countries_pop_df = pd.DataFrame.from_dict(countries_population, orient='index', columns=[
        'population'])
    uk_cases = uk_cases.join(countries_pop_df, on='areaName')

uk_cases['newCasesRate'] = 100000 * uk_cases.newCases / uk_cases.population

uk_cases['weeklyCasesRate'] = uk_cases.groupby(by='areaName')['newCasesRate'].rolling(7).sum().reset_index(drop=True).fillna(0)
uk_cases['twoWeeklyCasesRate'] = uk_cases.groupby(by='areaName')['newCasesRate'].rolling(14).sum().reset_index(drop=True).fillna(0)

uk_cases['weeklyTests'] = uk_cases.groupby(by='areaName')['newTests'].rolling(7).sum().reset_index(drop=True).fillna(0)
uk_cases['weeklyCases'] = uk_cases.groupby(by='areaName')['newCases'].rolling(7).sum().reset_index(drop=True).fillna(0)
uk_cases['testPositivity'] = 100 * uk_cases['weeklyCases'] / uk_cases['weeklyTests']

overview_cases = Cov19API(filters=filter_all_uk, structure=structure_cases_death).get_dataframe().fillna(0)
def preprocess_dataframe(df):
    df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')
    df.sort_values('date', inplace=True)
    df.reset_index(drop=True, inplace=True)
    df['casesChange'] = df['newCases'] - df['newCases'].shift(-1).fillna(0)
    population = round(100000 * df.cumCases.max() /
                  df.cumCasesRate.max())
    df['newCasesRate'] = 100000 * df.newCases / population
    df['casesChangeRate'] = 100000 * df.casesChange / population
    df['weeklyCasesRate'] = df['newCasesRate'].rolling(7).sum().fillna(0)
    df['twoWeeklyCasesRate'] = df['newCasesRate'].rolling(14).sum().fillna(0)
    df['weeklyTests'] = df.groupby(by='areaName')['newTests'].rolling(7).sum().reset_index(drop=True).fillna(0)
    df['weeklyCases'] = df.groupby(by='areaName')['newCases'].rolling(7).sum().reset_index(drop=True).fillna(0)
    df['testPositivity'] = 100 * df['weeklyCases'] / df['weeklyTests']
    return df
preprocess_dataframe(overview_cases)
pass

Summary:

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 countries:
    country_data = uk_cases.query('areaName == @country')
    positivity, caserate = country_data.testPositivity.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:.3}%, 14 day incidence: {caserate:.2f} {previous_text}")
for s in strings:
    display(Markdown(s))
    

positivity, caserate = overview_cases.testPositivity.iloc[-1], overview_cases.twoWeeklyCasesRate.iloc[-1]
text = traffic_light(positivity,caserate)
previous_caserate = overview_cases.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)"
display(Markdown(f"Overall, the UK is {text} - Positivity: {positivity:.2}%, 14 day incidence: {caserate:.2f} {previous_text}"))

Wales is Dark Red - Positivity: 18.8%, 14 day incidence: 995.81 (⬆ from 990.79 the day before)

Scotland is Dark Red - Positivity: 12.2%, 14 day incidence: 645.43 (⬇ from 646.71 the day before)

Northern Ireland is Dark Red - Positivity: 11.2%, 14 day incidence: 1245.10 (⬆ from 1244.26 the day before)

England is Dark Red - Positivity: 6.97%, 14 day incidence: 950.05 (⬆ from 943.18 the day before)

Overall, the UK is Dark Red - Positivity: 7.4%, 14 day incidence: 935.73 (⬆ from 929.78 the day before)

Plots

First a plot with each country in the UK. The top right shows the positivity rate for (which is the percentage of registered tests which were positive) while the top left is the 14 day incidence rate. The bottom chart can be used to select the timeframe (does not work on a mobile device).

brush = alt.selection(type='interval', name='DateBrush',encodings=['x'],fields=['Time'])
leg_selection = alt.selection_multi(fields=['areaName'], bind='legend')

base = alt.Chart(uk_cases).mark_line().encode(
    x=alt.X("yearmonthdate(date):T", axis=alt.Axis(title='Date')),
    y=alt.Y("twoWeeklyCasesRate:Q", axis=alt.Axis(title='14 day incidence rate')),
    tooltip=['areaName', 'date','twoWeeklyCasesRate', 'testPositivity'],
    color='areaName',
    opacity=alt.condition(leg_selection, alt.value(2), alt.value(0.1))
).add_selection(leg_selection).properties(width=350)

upper = base.encode(alt.X('yearmonthdate(date):T', axis=alt.Axis(title=''),
                         scale=alt.Scale(domain=brush))
                   ).properties(title=f'14 day incidence rate of UK')
upper_right = upper.encode(alt.Y('testPositivity:Q', axis=alt.Axis(title='Test Positivity'))).properties(title='Test positivity')
lower = base.properties(height=60, width = 760).add_selection(brush)

(upper | upper_right) & lower

def plot_traffic_light(country: str):
    brush = alt.selection(type='interval', name='DateBrush',encodings=['x'],fields=['Time'])
    leg_selection = alt.selection_multi(fields=['testPositivity'], bind='legend')

    base = alt.Chart(uk_cases.query("areaName==@country")).mark_point(size=2).encode(
        x=alt.X("yearmonthdate(date):T", axis=alt.Axis(title='Date')),
        y=alt.Y("twoWeeklyCasesRate:Q", axis=alt.Axis(title='14 day incidence rate')),
        tooltip=['areaName', 'date','twoWeeklyCasesRate', 'testPositivity'],        
        color=alt.condition(alt.datum.testPositivity <= 4,alt.value('green'), alt.value('orange'))
    )

    upper = base.encode(alt.X('yearmonthdate(date):T', axis=alt.Axis(title=''),scale=