INTRODUCTION
I wanted to incorporate a heatmap that illustrated the death rates (per 100,000 population) across time in the United States. But I also wanted to show when the coronavirus pandemic 2019 (COVID-19) vaccine was introduced and how it impacted death rates. I thought that a heatmap would do a nice job of illustrating this.
The data visualization by Tynan DeBold and Dov Friedman from the Wall Street Journal has a great visualization on the impact of vaccines for various disease from the measles to smallpox on death rates (See figure below). This heatmap shows the number of measles cases per 100,000 population between 1920 and 2000. Each row represents a state or territory of the United States (U.S.). In this tutorial, we’ll create a similar heatmap for COVID-19 deaths.
Source: Tynan DeBold and Dov Friedman, Wall Street Journal (link)
I set out to create my own heat map with COVID-related death rates using data from the Centers for Disease Prevention and Protection (CDC). The CDC provides a dashboard to visualize the trends in death rates by states and U.S. territories (Compare Trends in COVID-19 Cases and Deaths in the US). However, the data was not compiled in an easy manner. You can only visualize 6 territories at a time. I was able to download all the data and compile this into a single file for this tutorial, which you can download here. Use the file with the *.xlsm extension, which supports macros.
TUTORIAL
Step 1. Download the Excel file with the data. Use the data from the “data” tab. Inspect the data. The columns represent the weekly death rate (7-day average number of deaths per 100,000 population). The rows represents the states and U.S. territories.
Step 2. Use the VBA macro. In a previous article, I explained how to create a heatmap with different gradient levels. We will use a modified version of the macro for this exercise.
This is the VBA macro that we’ll use (link). Don’t be intimidated by this. I’ll go over how to use this code
I start by determining the number of gradient levels for the heatmap. The average death rate was 0.35 per 100,000 population, so I generated 20 levels of gradient (0 to 0.999, 1.0 to 1.999, 2.0 to 2.999, etc). I wanted a “blue” shade for this heatmap, so I had to figure out the RGB scheme for each level. I identified the RGB color scheme using a gradient generator by ColorDesigner. RGB code uses three values to represents the main color on the spectrum (red, green, blue).
Once you have the RGB codes for the gradient levels, you can edit the VBA macro.
In the Developer tab, click on “Visual Basic.” Make sure that the Developer tab is viewable on the Ribbon. If it is not, then you can activate this by going to the File > Options > Customize Ribbon and activate it by entering a check by the Ribbon box.
The Visual Basic interface is a separate window that pops up.
In the “Sub ChangeCellColor()” macro, we’re going to include 20 gradient levels. It’s important to make sure the Range() includes the data that we’re interested in modifying. Since the first cell is in A1 and the last cell is in CZ61, the range is Range("A1:CZ61").
Then we include the 20 gradient levels by changing the Case statement with the corresponding RGB codes. As you modify each Case statement, make sure to change the value ranges for each statement. For example, if you want to apply the RGB code for (18, 123, 141), the death rate range is 1.8 to 1.8999999. You can do this for all the gradient levels.
Here is an example:
Case 1.8 To 1.8999999
oCell.Interior.Color = RGB(18, 23, 141)
oCell.Font.Bold = True
oCell.Font.Color = RGB(18, 23, 141)
oCell.Font.Name = "Times New Roman"
oCell.HorizontalAlignment = xlCenter
Case 1.8 to 1.8999999 denotes the range of the values in each cell (7-day average deaths per 100,000 population).
oCell.Interior.Color = RGB(18, 23, 141) denotes the RGB color scheme for our gradient
oCell.Font.Bold = True denotes that the font is bolded
oCell.Font.Color = RGB(18, 23, 141) denotes that the font color matches the cell color
oCell.Font.Name = "Times New Roman" denotes that the font is Times New Roman
oCell.HorizontalAlignment = xlCenter denotes that the value is aligned in the center
After you’ve adjusted your code, you can execute the macro. To execute the macro, go to the Ribbon and select “Macros.” The Macro window will appear with three macros. Select the “ChangeCellColor” macro and click “Run.” This should execute the macro, and you will notice that the data will start to change color to the corresponding gradient values.
To sort by the last column, select the “SortColumn” macro and click “Run.”
To create white borders around the cells, select on the “WhiteOutlineCells” macro and click “Run.”
Step 4. Final touches. You can select the columns and change width to 2.
Once you have the correct cell sizes, you can start to add labels to the file. I included a line to delineate when the first vaccine was introduced and a line for when the president announced that COVID-19 was a national emergency. I also added labels to the bottom part of the table to indicate dates along the timeline. The rows represented the states and U.S. territories.
CONCLUSIONS
The number of deaths was high early in the pandemic in a few select places in the U.S. As the vaccine is introduced, the number of deaths reached a zenith around December 2020 before falling to low levels in February 2021. Then, the death rate started to increase around the beginning of July 2021. Based on the heatmap, the vaccine may have resulted in a decrease in deaths. But the death rate increased approximately 6 months later in what appears to be the beginning of a seasonal pattern. It is unclear whether the introduction of new variants causes the increased death rate, but there is speculation that it may be a contributor. This heatmap does not generate any claims to what is actually happening; it only provides a visual of the patterns that are reported across each U.S. state and territory.
REFERENCES
I took inspiration from the data visualization by Tynan DeBold and Dov Friedman from the Wall Street Journal.
Date for this exercise came from the CDC (link).
A previous article on how to create heatmaps is available here.
I used the Gradient Generator by ColorDesigner to find out the RGB values for my gradient levels.