:de: :us: [kaggle version]

Warning: (Updated on 2021/11/22) First of all here are some clarifying personal notes: I’m vaccinated! The guidelines of of the STIKO (official German board for vaccination recommendation) have always been plausible. Therefore my eldest daughter got vaccinated, too. And our younger children will also get their vaccination as soon as a recommendation exists. So, if you look at the numbers below, keep in mind, those were the numbers from Spring 2021, before Delta hit Germany and during a time where kids had more restrictions than adults.

In this blog post, I want to find out how easy it is to create interactive charts in notebooks. As a framework, I will use Altair, as it can be easily integrated into my blog [FP20].

In order to make the contribution interesting, I evaluate the death rates from Germany over the last five years. I construct two periods - the first period before and the second one during Covid19.

We will analyze these questions:

  • How do deaths per age group and month change?
  • How do deaths per state change?

The first part describes the origin and processing of the data. Then, charts are built around the above questions. Finally, a technical conclusion to the framework follows.

Important: The death rates are not an indicator of the danger of Covid19. They only reflect the number of people who died retrospectively.

Source of data

For the analysis of deaths in Germany, the current death data from the Statistisches Bundesamt (Federal Statistical Office) [SB21] are used. The data include the number of deaths by age group or state on a monthly basis. In this blog post, the monthly death figures for the period March 2016 to February 2021 are examined. The most recent data are currently only available until February 2021 and include some estimations, which are explained in the data source in the tab "Hinweise". In addition to the death figures, data on the population density of the states are processed [SB20]. These figures refere to the reference date 31.12.2019.

Procession of the data

Preprocessing

The evaluation period is limited to March 2016 until February 2021. The period in which Corona was heavily active in Germany is simplified here to March 2020 (when Covid19 triggered the first major social changes in Germany) until February 2021 (orange). The pre-Covid19 period is set to March 2016 until February 2020 (blue). Thus, the Covid19 period covers exactly one year and the pre-Covid19 period exactly four years. Thus, both periods remain comparable without serious seasonal deviations. The split of the periods is illustrated in the diagram below.

Aggregation of data

In the calculations, the values are averaged over a period of time. Depending on the evaluation, this happens over the whole period or per month. The first and third quartile of the aggregated data may also be shown as shading in the diagrams. In some charts, the calculated points are interpolated to increase readability.

Tip: For some of the charts there are control elements in the upper right corner, such as drop-down boxes. The mouse wheel can be used to zoom and a chart can be reset by double-clicking.

Deaths by age

The diagram below shows the average number of deaths per month per age group. One age group covers five years. The point age 55 includes, for example, all deaths between 50 and 55. Deaths of those over 100 years of age are shown in point 100.

The values are aggregated over the pre-Covid19 period (blue) and the Covid19 period (orange). In the diagram, the months can be selected via drop-down box.

Below are the average deaths by age for each month. The list starts with the month of March.

Observation

In the age groups under 55, there was no excess mortality during these periods. In the age groups from 80 years and older, mortality increased massively.

Deaths by state

In the next diagram, the age groups 0-65 and 65+ can be evaluated for the two periods grouped by state. By drop-down box, you can choose between the key figures Deaths or Deaths per 100,000 inhabitants.

Observation

North Rhine-Westphalia (NW) has the largest number of deaths, since it is the state with highest population. In each state, there are increases in death rates in the Covid19 period. However, only minimal increases in Hesse (HE) and Bavaria (BY) can be seen for the age groups under the age of 65. In the other states, there is no noticeable increase in this age group.

There are smaller variations looking at the number of deaths per 100.000 inhabitants in the age group below 65. The increase in deaths in North Rhine-Westphalia (NW) is slightly lower than in Bavaria (BY) and roughly as high as in Baden-Württemberg (BW). Saxony (SN) and Brandenburg (BB) have the strongest increases.

Note: Only deaths are evaluated. No other aspects (such as demographic aspects) are taken into account.

Deaths by state and population density

The last diagram refers to the population density of the states and the amount of deaths per 100,000 inhabitants.

Observation

The city-states Hamburg (HH) and Bremen (HB) show a moderate increase in deaths despite their high population density. Berlin (BE) instead has a high increase in death rate. Schleswig-Holstein (SH) recorded the lowest increase.

The correlation coefficient of population density and increase in mortality is:

0.0232

Hence, There is no correlation for this comparison.

Note: Only deaths are evaluated. No other aspects (such as demographic aspects) are taken into account.

Technical conclusion

With Altair you can easily setup interactive charts and integrate them into notebooks. Their website contains a variety of examples [AA1]. Some questions are also answered in their Github Issue tracker [AA2].

I spent some time debugging JavaScript when I used the bindings incorrectly. However, there was finally a solution to every problem [AA2] or workarounds such as for the legend in the bar diagram [AG18]. As expected, a bit of practice is neccessary to get used to a new framework.

The integration [FP20] in this blog works perfectly. I will certainly use the framework elsewhere.

References

Data sources

The data used here are from the "Statistisches Bundesamt" (Federal Statistical Office) and are subject to the license "dl-de/by-2-0". The license text can be found at www.govdata.de/dl-de/by-2-0. The data were modified exclusively within this notebook by executing the specified program code for the purpose of analysis.

Other references

A lot of the coding is derived from various examples of the Altair homepage and from great examples in the coresponding Github Issue tracker answered by https://github.com/jakevdp.