Obviously we all spend a lot more time indoors these days. One of the things I’ve done that is non work related at my computer, has been playing with the data for Covid-19 cases in Australia. This has proven to be both interesting, plus a little shocking. Interesting in the sense of both from looking at the data itself to learning how to make things work in spreadsheets.
I’m sure there’s a better way to track this but this is what I ended up with after some tinkering and deciding what I wanted along the way. I type the up to date total for “recoveries”, “deaths” and “total cases”, the rest is automated by formulas.
I wanted to detail this process somewhere in case I ever want to remember how to use these formulas again.
Getting the Data in Google Sheets
One spreadsheet listing dates with columns…
- Date
- New cases
- Recoveries
- Deaths
- Active cases
- Total cases
- Percent increase
- Events
- Sources
Date: this column lists each day with data validation setting to display a valid date. This allows me to drag a cell down to the next row and it will automatically display the next day.
New cases: this column uses the following formula to automatically show the difference between the row’s “total cases” and the previous row’s “total cases”.
Example =IF(F3-F2<=0,””,F3-F2)
Recoveries: I manually enter the number of recoveries in this column.
Deaths: Manually entered. Of course, this is the hardest part to deal with and results in a moment of reflection on how sad this pandemic is. Someone’s grandparents? Mother? Father? Son? Daughter? Even though the number of deaths in Australia are low, this column has a moving effect and reinforces how serious this is. It also makes understanding what the data reveals to be paramount. Did we do enough? Were we quick enough? When do we open up again?
Active cases: this column uses a formula that takes the previous row’s “total cases” and then adds “new cases”, minus “death” and “recoveries” and if no change, will display the previous day’s “total cases”.
Example =IF(E79+B80-C80-D80<=0,””,E79+B80-C80-D80)
Total cases: Manually enter the day’s reported total of cases.
Percent increase: This column shows the percentage increase based on the day’s new cases against the previous day’s total cases.
Example = =IF(B82/F81<=0,””,B82/F81)
Events and Sources: at the moment these columns are being used to record key events and sources detailing these events. This has no impact on any graphs but is interesting as a timeline of events against the development of the pandemic across Australia.
Note the Ruby Princess cruise ship return to Sydney and the resultant explosion of new cases in the weeks after.
Here is a link to the spreadsheet
Graphing the data
The graphs on this sheet automatically update based on the data in the first sheet.
There is another source of data I use from the health.gov.au website purely to generate the one pie chart showing the share of cases across states and territories.
Publish to a Google Site
This allows the graphs to be embedded on a webpage. Please check it out.
Google tools are a great way to collect data, update and publish live results as you go. Being able to input the data, have the graphs update and display in a website is all very simple. A nice process that has many uses in a school… or pass the time during a pandemic.
– David Burke, stuck at home during the covid-19 pandemic, 2020.
The eye opening part of this process has been able to track the effect of measures introduced, though it’s obviously not possible to trace the effect of each individual law or action. Different states took different measures and many rules were introduced along with various others.
One thing is certain, the ability to trace, detect and act FAST can make a huge difference and protect lives and livelihoods. The data doesn’t lie.
Dave ICT 