Visualizing the growth of the British COVID variant in The Netherlands

[#1]

Recently, the weekly number of COVID-19 cases in The Netherlands has been steadily dropping week over week. However, underneath this lies a hidden positive trend of cases with the British COVID variant. 

In this article, I explain how I made my first visual in Cloudera Data Visualization with just a few clicks.

Step 1: The Data

The data is created by combining several sources that shed light on the percentage of new infections that are made up with this variant, as well as the total number of cases. As we are here for tech, more than science, I will only highlight the official report of cases per week and an official article that contains the latest percentage, as well as an earlier percentage. Additional explanation on the numbers is given in the post below this article.

From here I could have chosen from many data sources, including a CSV file, but for reproducibility, I decided to upload it to Hive with a simple query:

create table covid_cases as
SELECT 431 as british_cases, DATE '2020-12-08' AS week_end_date
UNION SELECT 1168 , DATE '2020-12-15'
UNION SELECT 2470 , DATE '2020-12-22'
UNION SELECT 3369 , DATE '2020-12-29'
UNION SELECT 4854 , DATE '2021-01-05'
UNION SELECT 5434 , DATE '2021-01-12'
UNION SELECT 7755 , DATE '2021-01-19'
UNION SELECT 11760 , DATE '2021-01-26'
UNION SELECT 19085 , DATE '2021-02-02'

People have different opinions on the best way to mock up data, but if I only need a few rows, I always like to build this kind of query with the Excel concat function.

Step 2: The Connection

As I used Cloudera Data Visualization within a Data Warehouse, the connection to that Data Warehouse is available out of the box. As such, I only needed to select the database and table.

Step 3: The Visualization

In order to minimize the effort, I decided to stick to the default settings where possible. This has the additional benefit that it is very easy to reproduce what I have done.

1. Create a Dashboard
2. Add a visualization for the table
3. Select type: Bars
4. Y-axis: british_cases (it automatically understands that we want the sum)
5. X-axis: week_end_date (it already recognizes that it is a date)
6. Change week_end_date in X-axis type to timestamp
7. Labels: british_cases (it automatically understands that we want the sum)
8. Give your X-axis and Y-axis a nice alias, and add a title and subtitle to the chart

Now your chart should look just like the picture on top of this article.

Hopefully, this enables everyone to gain more insight into how COVID develops in The Netherlands and of course in how to visualize data with just a few clicks.

---

Edit:

This additional data source indicates the cases for the week ending on 2nd Feb:

Nieuwe varianten gooien roet in het eten

[#2]

Unfortunately most sources are in Dutch but for good measure I will explain the most important data points:

1. Total weekly new covid infections come from the RIVM (dutch official body): https://www.rivm.nl/coronavirus-covid-19/archief-corona-updates

2. For the week of 26 jan, it is mentioned in an article by the RIVM that over one third of the current new infections is of the British Variant: https://www.rivm.nl/nieuws/Britse-variant-wint-terrein-in-Nederland 

3. The same article mentions the rate was 8.6% in the period of 4-10 jan

These are the most important points, and already show the trend. However, here are the additional sources:

4. In 'early december' the rate was about 1% according to this article on the largest news site of the country: https://www.nu.nl/coronavirus/6101869/wat-weten-we-nu-van-de-britse-coronavariant-ja-die-is-echt-bes...

5. In a national press conference on 12 Jan, the minister of health indicated that in the past period the rate was between 2-5%. More frequent updates confirmed that there was steady growth in this period, so for the period between 9 and 29 december, there was likely growth from about 2% to about 5%. This could be off by one or two percentage points.

6. Though there was no clear source, several news sites recently referred to a current rate of 20%, this was likely observed between 13 and 19 jan.