April 17, 2020

Research analyst builds charts to help New Mexicans visualize COVID-19 data

Courtesy John Culbertson

John Culbertson has been tracking the number of COVID-19 cases globally in the news since early January. Culbertson, who has more than 25 years experience as a research analyst and has a Master’s degree in educational research and statistics, is very comfortable with numbers.

But Culbertson said he was struggling to grasp the big picture trends in infection rates by looking just at the numbers. 

“I started watching the Johns Hopkins map, they started collecting data when this first was really getting started in China, they were tracking the spread of the disease in China and all over the world. I started watching that pretty early on, and was alarmed at the rate it was spreading in China,” Culbertson told NM Political Report. “When the cases first started in New Mexico, I was having to try to do a lot of this in my head, looking at the numbers and doing some calculations in my head. That was difficult.”

So Culbertson did what any data researcher might do: He plugged some numbers into Microsoft Excel, and began generating charts. 

Courtesy John Culbertson

“With something like this, that we’ve never gone through before, being able to see it tracked over time visually is just much easier for everyone, rather than trying to look at it in tables,” Culbertson said. “It’s helpful, especially when you’re looking at something over time on the X-axis, you can get a better sense visually, tracking something longitudinally, as a trend line over time.”

Culbertson said he realized early on that many people would likely find the charts and information useful, so he spent about a week building a program and setting up a Google Sites page to share. 

Culbertson said he uses a data scraping program to collect all the raw data. 

“The source for all of this is the New Mexico Department of Health site. I have some codes set up that look for table data within their html code on their site. It scrapes that data, and puts the raw data into excel. From that, I’m able to extrapolate and build the charts and some of the statistics,” he said. 

Courtesy John Culbertson

For national tracking, Culbertson is scraping data from Worldometer, an online site that is tracking COVID-19 cases, deaths and recoveries in each country. 

All the data collected is put into Excel. 

“Most of it is automated. Once everything is pulled into Excel, I have some further programming and code that automatically generates and updates all the charts, and I have some more code that copies all the charts and uploads it to the Google Sites page,” he said. “ I go through and verify that everything is updated correctly and that it’s reconciling with their numbers, but that doesn’t take very long.”

Culbertson is periodically expanding the charts, using different methods to visualize the data in different ways. 

“When I launched it I just had two or three charts on it. As the Department of Health has been adding more and more to their news releases, I’ve been able to pull more data from that, and create additional charts,” he said. “We can visually see how the infection rate changes over time, by location and other variables. For instance, we can see where, unfortunately, the number of cases per 10,000 is surging in the Four Corners area, especially in McKinley County, compared to Doña Ana County, where the number of cases per 10,000 is much lower. Taking the data that the DOH is providing and charting it by location and other variables allows us to see these changes in infection rate over time.”