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: 12.6%, 14 day incidence: 1073.65 ( from 1135.10 the day before)

Scotland is Dark Red - Positivity: 12.3%, 14 day incidence: 1497.35 ( from 1545.90 the day before)

Northern Ireland is Dark Red - Positivity: 5.68%, 14 day incidence: 1057.29 ( from 1042.68 the day before)

England is Dark Red - Positivity: 2.91%, 14 day incidence: 643.78 ( from 645.95 the day before)

Overall, the UK is Dark Red - Positivity: 3.6%, 14 day incidence: 745.33 ( from 753.61 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=alt.Scale(domain=brush)
                             ),
                        color= alt.condition(alt.datum.twoWeeklyCasesRate <= 25,
                                            alt.value('green'),
                                            alt.value('orange')
                                           ),
                       ).properties(title=f'14 day incidence rate').properties(width=300)
    
    upper_right = upper.encode(alt.Y('testPositivity:Q', axis=alt.Axis(title='Test Positivity')),
                               color=alt.condition(alt.datum.testPositivity <= 4,alt.value('green'), alt.value('orange'))).properties(title='Test positivity').properties(width=300)
    
    lower = alt.layer(
        base.encode(color=alt.condition((alt.datum.testPositivity < 4) &
                                        (alt.datum.twoWeeklyCasesRate >= 50) &
                                        (alt.datum.twoWeeklyCasesRate < 200),
                                            alt.ColorValue('orange'),
                                            alt.ColorValue('red')
                                           ),
                    opacity=alt.condition((alt.datum.twoWeeklyCasesRate >= 50) &
                                              (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.testPositivity < 4) &
                                        (alt.datum.twoWeeklyCasesRate < 50),
                                        alt.ColorValue('green'),
                                        alt.ColorValue('orange')
                                       ),
                    opacity=alt.condition(alt.datum.twoWeeklyCasesRate < 50,
                                          alt.value(1),
                                          alt.value(0)
                                         )
                   )
    ).add_selection(brush).properties(height=120, width=660)

    return (upper | upper_right) & lower

Traffic Lights

The next 4 charts show's the same data in abit more detail. The points are color coded to indicate when the 'traffic light' would change color. In the top left and right charts, this is just done on the basis of 'green' or 'not green' - so 'green' when the positivity rate is below $4\%$ and 'green' when the 14 day incidence rate is below $25$. The bottom chart then combines all the cases and shows what color traffic light the countries would be according to the ECDC. Note that while the incidence rate goes back to the start of the pandemic, the testing only started being recorded at the beginning of July 2020, so the colouring before that date wouldn't necessarily make much sense.

Wales

plot_traffic_light('Wales')