In the end of October 2020 I decided to start tracking Covid-19 Cases for myself. This was both to keep myself educated about the pandemic and to shake the dust off my Data Analytic skills. I was mostly curious if I could find trends in cases and if it was possible to forecast case numbers with any type of accuracy. Keep in mind, everything below is currently based on Travis County (Austin Texas). Other states and cities will be coming.
DATA GATHERING
Covid Cases- Google has a pretty good Covid-19 Case count interface. You can look up any Country, State, City or major County and find their New Case and Death counts.
Temperature- In order to see any trends related to Temperature changes, I needed to find a database of Historical Temperatures for specific areas. I stumbled upon https://w2.weather.gov The site has a database of past Average Temperatures per day. You can look up these numbers by month and are exported into a table which I copied into Excel and rearranged for my purposes.
I also needed future Average Temperatures in specific areas. For this I used https://www.timeanddate.com/weather It gave me the forecasted High & Lows for the next week. I used these numbers to average out each days temperatures and attempted to predict case counts based on future temperature changes.
DATA PROCESSING
This is an early view of the Data Processing Excel Sheet. It is in constant layout change processes.
Date | New Cases (Actual) | Total Case Count | Percent of Counties Population | Change in Population Percent from Previous Week | Predicted Covid Cases for the Week | Predicted Total Count | Prediction Error based on Actual | Predicted Total Population Percent with Covid | Previous Weeks Percent Increase | Daily Average Temperatures | Temperature Changes Week to Week | Temperature Change Rating | Actual Week Case Numbers & Percentages
FORECASTING
I am currently using two methods to help forecast Covid-19 Case increases:
Based on previous 4 week Case Increase Percentage Averages
Based on Temperature Changes
Educated Guesses
4 Week Averages- Each week I calculate the percent increase from previous weeks. I then use the percentages from the previous 4 weeks and average them out to give me a very basic idea of what next weeks percentage could be.
The idea behind this approach is that if there is a consistent rise or fall in cases over the past few weeks, then this formula will predict a continuation of this trend. However, things like Holidays, drastic changes in weather, or stricter lockdown procedures will disrupt any trend.
This approach is mostly used as my base projections and are not considered to be accurate.
Temperate Changes- Fairly quickly I was able to identify a consistent pattern between Covid Case numbers and weekly average Temperature changes. If the temperatures went up, Covid Case increases went down (Still increased, but the new case count for the week in question was lower than the previous week) If temperatures went down, then the Covid Case increases went up.
My explanation for this is that higher temperatures invite people to go outside where they might be more distanced and the spread of the virus is less likely to happen. Lower temperatures cause people to stay inside and closer to eachother….yuck!
While this approach can’t predict an actual number, it can help me make informed decisions.
Educated Guesses- This is how make my predictions, by using all the above information, historical data and world events. While the data I have gathered can give me a picture of the past, my formulas attempt to give me an idea of the future. But they aren’t full proof.
For example: The formula predictions for the week after Halloween showed only a 567 case increase. However, having seen a 172% case increase the week after July 4th from the base predictions, and knowing that both Holidays typically involve many people gathering together… I knew the base prediction of 567 was low. So I multiplied it by 172% and formally predicited (On Facebook) that the November 2nd to 8th Case increase would be 975.24.
The actual case count ended up being 948. My educated guess was off by 27, a 2.84% error. Thats pretty good if I do say so myself.
However, my prediction of the week to come was very off base. I predicted 800 cases and it ended up being over 1400. My error was not taking into effect two things:
Undiagnosed Halloween cases that were recorded in the following week
The exponential affects of a 172% increase over time. If 1 person usually gets Covid and now 1.72 (lets just say 2)….. and now 2 people got it, then they spread the disease to 2 more people.
So now I am working on a way to track exponential growth and exponential predictions.
Lets see how this goes.