Visual representations make trends and differences easy to spot.
Trying to make sense of the news during the Coronavirus crisis provides a powerful example of how difficult it can be to extract meaning from raw data. Like everybody else I have been struggling to interpret the daily deluge of numbers so I can determine whether we are at least moving in the right direction.
Without wishing to trivialise anything, one small benefit for me has been to clarify the whole question of exponential growth. If you are just interested in an overview of how cases are growing, or falling, across a representative sample of countries including the UK, the graph above shows you how things developed in April. Further down you can see a live graph showing where we are at now.
The information conveyed by this graph can be understood at a glance. It compares the daily rate of growth of reported Coronavirus infections between China, Spain, Italy, the UK and the US. Seeing a line on a downward curve with a clear trend would seem to indicate successful social distance measures while a more erratic trajectory points towards less effective measures.
The daily rate of growth is calculated by taking the number of infections reported today and dividing this number by the number reported yesterday. You will get an answer in the range 1.0 to 1.3 which represents 0% to 30%. From this number you can calculate the number of days required for the infected number to double, for example, at 1.2 the numbers double every four days, at 1.06 every 12 days and at 1.02, doubling takes 35 days. Knowing this you can plot your exponential curve and estimate such critical projections as the number of people requiring intensive care within the next thirty days (as a proportion of the number infected).
I have been plotting these numbers on a spreadsheet since March 1st. It appears to be the case that a daily rate of increase number above 1.2 is the point at which a lock down is triggered and below 1.02 is the point at which restrictions can begin to be eased.This gives you a working range to monitor progress in any country you may be interested in. There are a number of different sources for this data available online. The one I have been using is Worldometers.
Though not the point of this blog, you may be interested in how to get from a spreadsheet to an online live display of your data. This is done using Power BI as part of my Microsoft 365 subscription. The data is stored in an Excel spreadsheet in MS One Drive and Power BI is used to visualise and publish the data.
This visualisation is a lot simpler than many you will find online and, therefore, may be useful to quickly see key trends. One thing that was puzzling me was how close the numbers appeared to be (in the above graph) between Italy and Spain on the one hand and the US and UK on the other. The difference between these two groups of countries is that the former had a very rigorous lock down imposed while the latter went with something much looser, and yet the result seems much the same.I did a bit of rethinking on the graph for May, see below, and now the difference seems clear.
In this graph (May rate of growth) I have taken a slightly different approach by showing the daily rate of growth as a percentage value rather than today as a multiple of yesterday. To calculate this value you subtract yesterday’s number of infected (x) from today’s number (y) and divide the answer (z) by yesterday’s number (x), then multiply the answer by 100 to get a percentage. In Excel terms you can do this with the formula ((y-x)/x)*100. This is always a useful formula in business to calculate how much better or worse we are doing this month or year compared to last month or year. With the rate of increase expressed as a percentage it is then very easy to calculate doubling time. To do this you can use the rule of 70 (or 72), you just divide 72 by the percentage rate of growth. The answer is a pretty close approximation of the number of days until the number of infections will double. I don’t know why this works but it is a very useful rule to know about if you ever need, e.g., to calculate the effects of compound interest on your mortgage or pension. Getting back to our Power BI data visualisation and my puzzlement about the the similar effects of different lock down policies, as appears to be the case on the top graph. In fact, with our slightly adjusted calculation we can get a very clear picture of the different outcomes and see quite a significant gap between the top two lines, representing the US and UK, and the lower two showing the trajectory of Spain and Italy. On this view, the daily rate of increase in infections is shown as up to four or five times higher with a weak lock down.
I hope you find the above useful and if I or any of the team at Macnamara can offer any help or support during this crisis please do feel free to drop me a line.
