The Making of the COVID-19 Report
Our Leadership Table asked us to prepare a report about how the COVID-19 pandemic has affected Racine County. They were specifically interested in its impact on our key indicators, and more generally interested in its effects on education and employment in our community. I’m still pretty new here at Higher Expectations, so I learned a lot about the community as I worked on it.
I like to think of the process for this kind of report as falling into four stages.
Speculate
In the first stage, we speculate about what might be important. In this case, the Higher Expectations team got together and shared our experiences during the pandemic. We considered how other folks in the community may have fared. We predicted that the pandemic would be particularly difficult for people with lower incomes, and for folks in races and ethnic groups that have been subjected to discrimination. These ideas came together into a list of factors that might put some numbers on people’s real, lived experiences.
Research
The second stage of the process was to do research on these factors. There were three criteria that a factor had to meet to be useful to our report. First, it needed to be a numeric quantity that we could report on. Second, it must have been gathered both before and after the start of the pandemic. Third, we needed access to the data. These criteria helped me to narrow down the list of factors. A vitally important consideration arose during this stage: the pandemic and lockdown meant that US Census data from 2020 are really different from other years’. We ended up avoiding some questions that we would have liked to answer because of the peculiarities of the 2020 Census data. Despite that challenge, I was still able to collect data from Racine Unified Schools, the state Departments of Health Services, Public Instruction, and Workforce Development, as well as the US Bureau of Labor Statistics, Department of Education, and Centers for Disease Control.
Wrangle
All of these data were in different file formats and table structures. Each data source had its own alphabet soup of abbreviations and acronyms. The next stage of the project was to wrangle everything into a coherent data set. This is the most technical part of the process. Some of it was point-and-click downloading, but I tried to do as much of the work as possible with scripts written in either Python or R. When you do something just once, it takes much more time to write (and debug!!!) a script than it does to click through some menus. However, writing the scripts creates a record of your thought process, documents the logical connections between all of the pieces, and makes it possible to automate and repeat tasks. This is golden. Without a script, I might have to spend a week of 2023 re-downloading data and copying-and-pasting between spreadsheets. With scripts, I can take 10 seconds to execute a function like “create_covid_report(2023).” This lets me focus my time on understanding what the data are showing about our community.
Present
After the data are all tidied up, it is time for the final stage of the project, where we decide how to present our findings. I made preliminary graphs of all of the different factors and wrote a short summary of any patterns that I thought I saw in those visualizations. I shared that first-draft report with the Higher Expectations team, and we had several discussions about what we saw, how to interpret it, and how to present the data. The key to a good visualization is that it helps tell a story AND is also still a true representation of the data.
Our final result is a reasonably long document, but it is mostly pictures. I hope that reading it is as interesting and informative a process for you as creating the document was for me!
To read the report, click here.
To learn more about the report and what our community is doing to support families in recovering from the impacts of the pandemic, join us for our two-part webinar series, April 28 & May 5th from 2-3 pm. Click here to register.
Written by Ben Taft, Data Impact Manager