Visualization of the regional COVID-19 Development in Germany

26 Apr 2020

The situation report from the RKI contains a figure which shows the development of the pandemic in Germany over the last 7 days. I wanted to know how this progresses over time and whether the outbreak clusters remain regional.

I used Pandas and Folium to recreate the figure and created this animation. See the code on Github to reproduce the result with current data.

I used Pandas to preprocess the raw data from the RKI.

def new_cases_by_date(rki_raw, rki_flag_column='Neuer Fall', rki_count_columns='AnzahlFall'):
    condition = rki_raw[rki_flag_column].isin((0, 1))
    rki_series = rki_raw[condition].groupby(['IdLandkreis'])[rki_count_columns].sum().to_frame(name = rki_count_columns).reset_index()
    
    rki_series = rki_series[['IdLandkreis', 'AnzahlFall']]

    # join geodata
    rki_series['IdLandkreis'] = rki_series['IdLandkreis'].astype(str).str.zfill(5)
    rki_series = rki_series.set_index('IdLandkreis')
    rki_series = rki_series.join(rki_geo)

    return rki_series
start_date = pd.to_datetime('2020-03-01')
end_date = rki_raw.index.max().tz_convert(None)

frames = []

for j in pd.date_range(start_date, end_date - pd.to_timedelta(6,'d')):
    startrange = j.strftime('%Y-%m-%d')
    endrange = (j + pd.to_timedelta(6,'d')).strftime('%Y-%m-%d')
    rki_cases = new_cases_by_date(rki_raw[startrange:endrange], rki_flag_column='NeuerFall', rki_count_columns='AnzahlFall')
    rki_cases['date'] = (j + pd.to_timedelta(6,'d'))
    rki_cases['RS'] = rki_cases.index
    rki_cases['cases_per_100k'] = rki_cases['AnzahlFall'] / rki_cases['EWZ'] * 100000

    rki_cases = rki_cases.set_index('date')
    frames.append(rki_cases)

result = pd.concat(frames)

The map is generated with the choropleth function from Folium. Selenium is used to store the maps as png sequence.

bins = [0, 5, 25, 50, 100, 500]
map_osm = folium.Map(attr="Robert Koch-Institut (RKI), dl-de/by-2-0", location=[51.3, 10.5], tiles='cartodbpositron', zoom_start=7)

#result.apply(lambda row:folium.GeoJson(row[1], fill_color=colorscale(row[0])).add_to(map_osm), axis=1)
folium.Choropleth(
    geo_data=rki_geo_raw,
    data=result['03-10-2020':'03-10-2020'],
    columns=['RS', 'cases_per_100k'],
    key_on='feature.properties.RS',
    fill_color='YlOrRd',
    fill_opacity=0.6,
    line_opacity=0.0,
    nan_fill_color='#f5f5f3',
    legend_name='cases per 100k',
    bins=[float(x) for x in bins],
    smooth_factor = 0.1
).add_to(map_osm)

The final animation is created with FFMPEG and this Gist

Github

Related Posts