Get best of the week

My dashboard and analysis of the dynamics of the spread of COVID-19 infection



I want to share my dashboard and analysis of the dynamics of the spread of infection. The dashboard is written in Google Sheets and Google Apps Script.

UFO Care Minute


COVID-19 — , SARS-CoV-2 (2019-nCoV). — , /, .



, .

, , .

: |

Note: The author does not claim to be objective data. There are many direct and indirect factors affecting the reliability of current statistics. At the end of the Pandemic, professional research and meta-analysis will be conducted that will allow an objective assessment of the Pandemic in numbers. This article was published in order to familiarize ourselves with a tool for analyzing the dynamics of the spread of infection based on the data published by WHO in its reports.

Dashboards are available at this address . Data is automatically updated on a daily basis as WHO reports are published. Content does not adapt for mobile devices. Better to watch on the big screen.

What for


  • , , . , COVID-19;
  • . , . , , . ;
  • , , , ;
  • It is convenient to open the laptop in the morning and on one screen to find out what happened over the past day.
  • This is a great opportunity to practice with Google Sheets and Google Apps Script.


Data


Initially, I used the data from the WHO reports , but they either made mistakes when transferring data from the reports to the database, or changed the time regarding which they considered new cases of infection. As a result, I rewrote scripts to use data from ECDC . This is the same WHO data, but with a stable publication time and no errors.

What to look at




  • Informers. They display the current key dynamics around the world;
  • The dynamics of the total number of cases of infection in the world (TOTAL CASES);
  • The dynamics of new infections worldwide (DAILY NEW CASES);
  • The dynamics of the spread of infection and deaths by country;
  • Visually assess the situation on the world map;
  • Mortality dynamics (CFR);
  • The duration of the peak period of the epidemic by country;
  • Correlation of the broad S&P 500 index with the number of new infections;
  • Find out how critical the situation in a particular country is, adjusted for the population of that country.


Mortality (CFR)




There is a lot of debate on the Internet about the lethality of the new coronavirus, there are articles on Habré. I do not want to enter into another controversy, I just operate on the data that we have.

Yes, there are definitely problems with collecting statistics, there are problems in mass testing, which would make it possible to more accurately judge mortality. The accuracy of the data is also influenced by such things as the seasonal influenza epidemic in Europe, the high mortality rate in general in some countries, such as India, which, possibly combined with health infrastructure problems, simply erodes the mortality statistics of the new coronavirus. I admit that in countries with a large number of infections, due to the load on the healthcare system, it is simply impossible to determine the cause of death in a timely manner, or, on the contrary, ascribe it to a new virus. But, you can definitely say for sure that many underestimated the criticality of the situation.

People confuse mortality with mortality . According to who andCDC mortality rate for seasonal influenza - 0.1% , for SARS-CoV-2 coronavirus - 4.45% at the time of writing (26.03.2020). In some countries, such as Italy, mortality reaches 9% at the moment.

Yes, the lethality of the new coronavirus is not as high as that of MERS-CoV and Ebola (34% and 50%), but the contagiousness , virulence , speed and extent of spread of the new coronavirus can not be compared - they are much higher.

Peak period


To work, I needed to know how long the peak period of the epidemic in a particular country lasts. If we take into account that most countries will take more or less proportionate measures of total quarantine, then based on the experience of other countries, it will be possible to judge when the epidemic will decline in a particular country.

I did not find any methodology for determining the peak period, so I had to improvise. Based on the dynamics of the number of new infections, I decided to determine the importance of new infections, on the basis of which it would be possible to judge whether the peak of the epidemic has passed or if the country is in a peak period. The average and median didn’t quite fit, so I decided to use Quartile. I removed days with a zero value from a sample of data on new infections, after this normalization, I used the function of determining the value of the Third Quartile. Further, the formula considers the number of days that exceed the value of the Third Quartile - this is the duration of the peak period.

If you apply this calculation for all countries, then it can be used to compare the peak period for countries.

The peak period will more reliably reflect the situation towards the end of the epidemic. You can look at the timelines of China and South Korea, where the peak peak of the epidemic has passed. Despite the fact that new infections are still present in China and South Korea, the calculation of the peak period perfectly reflects the effectiveness of the notorious methods of total quarantine and curve smoothing methods adopted in South Korea. Keep in mind that there is a risk of a second wave of the epidemic in South Asia. Due to the hasty removal of quarantine, or due to the import of the virus from neighboring countries, where the epidemic is just beginning. Therefore, the peak period may increase.

Country Summary Table


It displays all the key information by country, and there are also timelines by which you can visually assess the epidemic curve.



World Map Data


Visualization on the map allows you to evaluate the current foci of infection.



What I plan to add


  • Separate tables on the number of infections adjusted for the population ;
  • Visualization of dynamics on world maps adjusted for population ;
  • Visually highlight peak periods if an epidemic in a particular country is currently in a peak period.


Feedback


I will be glad to hear your wishes and recommendations to make the Dashboard even more informative.

More articles:


All Articles