How are the countries responding to contain the spread of Covid?
As the world is gearing to tackle the ramifications of this pandemic, there is a need for united action and planning to mitigate the effects of the virus. Since the beginning of the outbreak, the countries across the world were effected by the disease to various extents, prompting many governments to adopt strict curbs on public movement and undertake slew of measures to revive economy and enhance healthcare facilities.
Oxford Covid-19 Government Response Tracker (OxCGRT) is a holistic data source in this respect and has been coupled with the Tableau dashboard above to visualize the underneath metrics
The first tab in the report looks at the five nations where the cases have been surging the most, We are comparing stringency, which is the degree to which a country has imposed lockdown against the growth in cases. The visual draws inspiration from Coronavirus in the U.S.: Latest Map and Case Count as below.
The calculations in the report can be summarized using the graphic below.
In many of the calculations above, there is a population field appearing which originates from another data source (Courtesy: Population by Country — 2020 in Kaggle). This file is bonded with the main data file with a left join. A metric which is not covered above is Case Growth Rate which is shown below.
This is indicative of spread of disease in the country. As Confirmed Cases is a running total value, hence at any given date, case growth rate indicates the number of people affected with the virus per 100000 people, up until that date. Note that this is different from Cases in the table above, which is a per date value. Also to be noted is that all views are restricted up till the date, the data exists. This date is the calculation “max date per country non null” as seen in graphic above.
Turning over to the second dashboard, one can see the stringency and case growth rate spread on the timeline, further broken down by different policy indicators.
News Feed
There is a news feed functionality here at the top ribbon that uses Tableau’s show/hide container feature to display a web object. There is a good place to learn more on this feature (How to Create a Collapsible Menu Container in Tableau).
We can either search in the News Feed web object directly to get the requires results or we can click on worksheets on the dashboard that will pass the search keywords automatically. The second way uses URL Actions feature of Tableau by which we can pass filter keywords dynamically in URL address.
Data Preparation
The OxCGRT_latest file requires some data prep before plugging into Tableau. This is necessary in this case as the raw file has many fields dropped when fed into Tableau directly. Tableau acknowledges this issue and has a KB article to explain the solution. In a nutshell, Tableau expects a more robust file system like xlsx or instead use schema.ini file in the same directory as csv file. Steps to create schema.ini file can be found here.
Since the report needs to be updated regularly, manual download of file from github could be tedious. Instead we can set up a bat script to do this task for us. First, we need to get wget,exe utility from here and place in the path “C:\Windows\System32”. After this we need to create a folder, preferably in root directory. The idea is to not have spaces in the file path. Lastly we will create a bat file using notepad with content as below.
del “C:\Covid19\OxCGRT_latest.csv” /f
wget -c — directory-prefix=C:\Covid19 https://raw.githubusercontent.com/OxCGRT/covid-policy-tracker/master/data/OxCGRT_latest.csv
Note that file paths in two places above need to be modified to the one where we will keep the data file. After this the script has to be executed once and the latest file would be auto downloaded. Post this we would have to refresh the data connection from Tableau.
