TLDR; This blog post shows how to import a tsv file using the file visualizer and how to visualize data that is not shown the same way on the German impfdashboard.de website.
I tend to watch the German vaccination status page daily - as a sign of hope and progress. There are a couple of community ones and also the official one named Impfdashboard.de.
There was however one graphic that I don’t like and it’s the one how the vaccinations are split between vaccination centers and local doctors - because it does not show the insane amount of work, that the doctors are doing.
It’s this one:
You can hover with the mouse over each bucket and see the amount of vaccinations done, but also have to subtract those from the previous bucket. Let’s fix this via the Elastic Stack.
First we need to get the data. However when using the inspector and check the files being downloaded when loading the Impfdashboard you will see something like this
Note: I have not verified if the URL to the TSV files change everyday, so maybe you need to adapt this URL to get the latest data.
You can just retrieve via browser or curl and store this locally. Upon
inspecting this, this file lives up to its name and really is a tab
separated value file, also containing a header line. Let’s go with Kibana’s
file visualizer and import this file. You can find this by clicking
Machine Learning and then the
Data Visualizer in the tab navigation. Click on
Import data and select your file:
Good news! Everything has been detected properly and we can go ahead and import the data (don’t worry about the weird position of the import button, this was due to my screen shot tool).
After clicking import you need to name an index - I got creative and picked
Let’s try a couple of different things. Go to
Analytics > Visualize Library and click on
Create visualization, select
You now have an empty chart like this:
Panel options and set the index name to
the time field to
@timestamp and the interval to
1d, as we only have a
single data point per day, and do not drop the last bucket. Your graph will
now look like this:
Now, let’s add some data. Select
Counter rate for the aggregation, use
dosen_dim_kumulativ. You will end up with an image like this for the
vaccinations done in vaccination centers:
Time to add the vaccinations by doctors. Add a new series, specify
Counter Rate again, but this time use
dosen_kbv_kumulativ as a field. Also pick a
fancy color and optionally add some labels
What a surprise. The doctors have been surpassing the vaccination centers by a vast amount last week - which is absolutely impressive given the vaccination centers pretty much have only a single task to do 😀.
So, we know now, we can display the visualization we want - but we’re not done yet. You probably have heard already about Lens, which had been introduced into Kibana late 2019. Over the last months it has gotten tremendous attention, so that you can even cover the above use-case easily with lens. Let’s try to create the same visualization using Lens.
So, create a new visualization, but this time choose
Lens - which nowadays
is supposed to be go-to visualization strategy.
This is our starting screen:
Next, let’s add the
@timestamp field for the horizontal axis and customize
the time interval to be one day again.
For the vertical axis, select ‘Counter rate’ as function and
dosen_kbv_kumulativ as field. Ensure to set
per day as normalize by
unit! I also tweaked the display name and the value format plus the
series color, but that is all optional.
Finally let’s add a similar counter rate field for the
field. Now our graph looks like this
Do you spot the issue? It’s a stacked bar. Luckily this can be easily fixed to be a area chart like this and you can play around with tweaking the axes a little to end up with this chart
OK, we could go on now to replicate some more graphs from the dashboard like the first and total vaccination ratio among the population.
This visualization is based on the
fields. You could also visualize based on the vaccine manufacturer.
Also, there are more TSV files in on the impfdashboard website that you could download and visualize on. For now this should be enough.
- The impfdashboard.de uses TSV files for visualization (they also use Svelte)
- You can download those files and use Kibana’s File Data Visualizer to import them, without needing to do any tweaking
- TSVB used to be the de-facto standard for time series data
- Lens however has become the new standard for all kinds of visualizations and has full support for our use-cases as well, so there is no need for TSVB anymore