Music is the Moon

This quote in a wonderful book I have been reading has stuck with me for days.

Andrew Peterson is one of my favorite artists. His music, his writing and his creative talent are gifts from God that I am so thankful that he shares with the world.

Open Letter to St. Charles County Executive Steve Ehlmann

I have just sent the following email to our County Executive (and others), in the hopes of beginning a conversation around how the County might improve the sharing of Covid-19 data with concerned residents. I am hopeful that there can be some enhancements and improvements in how the data is shared.

Subject: Request for Additional Data Transparency/Availability

Mr. Ehlmann,

I have been a resident of St. Charles County for 33 years and am incredibly proud to call it home for me, my wife and our 4 children. I value the service that you and your colleagues have provided during that time to the over 400,000 residents under your charge and I thank you for all of your many efforts to make St. Charles County such a wonderful place.

I am writing to you today to request additional information and data resources around Covid-19 in St. Charles County. I have been following closely the disease in our County since the first case was announced in March and have been manually tracking the numbers on a daily basis. The Covid-19 dashboard that was released is helpful and provides some insight into the numbers, but I desire and wish for some additional insight. I wish to begin a discussion around how the County might share the underlying data tables to the residents, empowering them to perform analysis and make the graphs that they find valuable. I understand that many people are not as into data as I am, but I believe that there is value in being as transparent as possible with the citizens. I understand that there are possible HIPAA concerns and other legalities that must be considered, but I was hoping to see if it might be possible to even just start by sharing the data that drives the current dashboard.

At the moment, you can see the number of Total Deaths, but you cannot see the day to day # of deaths in a table. You can see the numbers broken out by each Zip Code if you click on that portion of the map, but you cannot find the Zip Code level data in a table. You can filter and see all of the data by gender or race, but none of that data is available in a table. This lack of tabular data has led to an inability to do any type of detailed analysis of trends and impacted populations. 

Currently, I am creating and tracking the Estimated Active Cases, as that number is more meaningful than the much reported Cumulative Totals. I use this calculated value to determine what the 7 and 14 day trend is for Cases the County, which many people have found useful. I have attached today's graph of the Active Cases for reference. (Additional graphs can be found here, if desired.)

I understand that this has been an unforeseen global event, and I do appreciate the data that the County has made available. However, I do believe that we can work to make the data even more transparent and available for residents who, like myself, might find more data availability a great thing.

I welcome any comments, suggestions and discussion, as I hope to improve what our wonderful County has already put in place. Thank you again for your time and for your service.

Respectfully,
Jeremy Walker

CC:
Doug Bolnick - Director Public information Officer
Mary Enger - Director, Communications
Matthew Seeds - Director, Information Systems

Covid-19 in Saint Charles County - Graphs

Below is a collection of graphs that I have created to show several different data points that I find helpful in tracking this disease in the county. You can read some of my notes about the data in these charts here.

Covid-19 Data Issues in Saint Charles County

May 22 Update:
I reached out to the County Health Department about this discrepancy and received the following reply/explanation:

The County’s health and information systems departments did switch reporting systems recently and noticed an error in the presentation of reported numbers. This caused the issue you note, and this was corrected as soon as possible.

I am not sure if the switch refers to the update to the PowerBI dashboard or if it refers to the actual source they are collecting the data from. I have not nioticed any updates the the actual layout or content of the dashboard, so i assume it is the data source. If the change in source resulted in a greater than 5% negative change in reported cases, then there seems to be valid reason for concern with the validity of the numbers that have been previously reported.

---

Original post:
I have been tracking the spread of Covid-19 in St. Charles County since the day the first case was announced. For several weeks, the County only had a table of current data on the site, so I was manually tracking daily case totals in a spreadsheet in order to see the trends. Finally, the County utilized PowerBI to provide some graphs of the data in a dashboard format. However, the County had not created some of the graphs that I thought helped better tell the story, so I continue to input and update daily new cases and deaths into my spreadsheet, all based on the published County data.

Over the course of this pandemic, firm numbers regarding positive cases, and even deaths, have been hard to establish. For Saint Charles County, the tale is no different. Most days, I have to scroll back though the previous days and update a few up or down by 1 or 2 cases. Typically, most of the changes occur to dates within the past 14 days, which makes sense, as this is within the window of symptoms presenting and test results being obtained. However, over the past week, I have noticed that the numbers are changing on dates much further back in time. 

Active Cases of Covid-19 in St. Charles County

Currently, the County website continues to show cumulative case numbers and does not show active case numbers. I have been tracking this data for over a month and present the below for reference.

Updated 06MAY20

Current Cumulative Cases: 637

Current Active Cases: 126

You can view the entire collection of my graphs that are updated regularly here.

Covid-19 Graph Notes

I have published some of the charts that I have created about Covid-19 in St. Charles County, MO. level data. Below are a couple of notes about how I arrived at some of my data points.

Notes:

• I have utilized the daily death total that I have been tracking on my own, as the county still does not publish them in a table at the daily level. 
• Due to the unavailability of the Recovery data at the county level, I have assumed the standard 14 day recovery window for all cases. I realize that this is variable, but for consistency sake and trend analysis, I felt like this data point was useful.
• I have calculated the number of Active Cases as follows:
• Active Cases = Yesterday's Active Cases + Today's New Cases - Today's Deaths - 14 Day Prior Daily Delta (Defined : Daily New Cases - Daily Deaths Announced)