COVID-19 DATA EXPLORATIONS

  • Goal

    The goal is to analyze the data of new covid cases and deaths in multiple perspectives, such as how each continent, country suffered from this contagious plague over time, and to answer the question of how an increasing vaccination rate had influenced the number of new cases

  • Result

    Results are presented in Q&A format through the use of visualizations. The dataset being used is from Our World in Data COVID-19 dataset. The tools I utilized are SQL and Tableau. I imported the dataset to SQL and constructed multiple Dataframes, then using Tableau for visualizations

  • Project Duration

    2 weeks

    last updated: Sep 1 2021

 

1. What are the numbers of global cases and deaths?


As of August 31 2021, the conservative number of covid cases and deaths are 217 million and 4.5 million, 2.08% of patients died. As we can see from the graph, we are in the 3rd wave of the global pandemic since the bottom in June 20 2021. The global peak of daily new cases was 906,017 new infections in April 28 2021, global peak of new deaths was 18,000 deceased in Jan 20 2021. As the transmissibility continues to rise, death rate was at 4% the highest in May 2020 and has dropped to around 2% since then.

global.png

2. Which continents suffered the worst from the pandemic and which countries had contributed largely to the spike in cases?


Asia has the highest total cases while Europe has the highest total death counts. The all time peak of monthly new cases is in Asia, Asia had 11 million cases in May 2021. Let's investigate further about the cause.

Continents.png

The table below shows the top 15 countries with the highest new cases in Asia. India had a sharp increase in cases in April 2021 and May 2021. The number of cases of India in May 2021 was 8 times greater than in March 2021, which had contributed largely to making Asia being the worst continent with covid cases.


If we exclude the figures in India, Europe has the highest number of cases. Europe and North America had their peaks of New Cases of around 7 million in November 2020, both continents have had somewhat similar trends. We can also see that Asia’s new cases keep reaching a higher level even without India’s figures, meaning that other Asian countries have a worsening situation.

no india.png

We can see that United States had accounted for most of the cases in North America whereas United Kingdom accounted most for Europe.

3. As of Aug 2021, how had the 3rd wave affected each continent?


Below only shows the data since January 2021. Europe and North America have experienced the greatest decline in new cases, from nearly 7 Million cases per month to 1 Million in June, in contrary to Asia and South America whose struggling from rising cases.

However, New cases in Europe and North America slowly increase again since July due to the easing of restrictions. In July, Europe and North America had around 2.5 times more cases than in June. It is obvious that the 3rd wave had been approaching since July and may cause even greater danger in winter if precautions have not been sufficiently carried out.

2021.png
The situation from June to August have changed tremendously. Cases started rising globally since July and even higher in August. In North America, USA has 10 times more cases in August than in June. In Europe, UK tripled its cases over three months, France has 6 times more cases in August than in June. In Asia, India is still having the largest number of cases per month while Iran and Indonesia also suffered badly from rising cases. Only South America have declining number of cases over these months.

The situation from June to August have changed tremendously. Cases started rising globally since July and even higher in August. In North America, USA has 10 times more cases in August than in June. In Europe, UK tripled its cases over three months, France has 6 times more cases in August than in June. In Asia, India is still having the largest number of cases per month while Iran and Indonesia also suffered badly from rising cases. Only South America have declining number of cases over these months.

4. What are the relationship between infected population and death rate?


Infected population refers to total cases per total population. Countries with high infected percentage (>10%) have deeper colour, such as United States, various countries in South America, and Europe etc. There are already lots of similar diagrams showing the number of infections, therefore I visualize the cases in terms of infected percentage to allow countries of different population size to be compared easily.

% Population infected.png
Death and infection .png

5. Is the current measures sufficient to stop the rampant spread?


Stringency Index is a composite measure based on nine response indicators including school closures, workplace closures, and
travel bans, rescaled to a value from 0 to 100 (100 = strictest).

2021 has more lenient restrictions than 2020, as we can see the red dots are mostly under a stringency index of 55

The spread of covid 19 is much more contagious in 2021. A stringency index of 40-50 have caused the number of new cases hitting new records in 2021. That being said, the virus may have enough room to mutate and become more contagious with such level of covid restrictions and measures.

Stringency.png

Each month is visualized by a unique colour. We can analyze monthly cases and stringency index by locating the specified colour and make comparison.

Monthly stringency.png

The average stringency index in 2021 August is on average 15 to 20 points below August 2020, but the transmissibility of the virus is a lot higher.  

5. What is the percentage of fully vaccinated population?


Canada, USA and and European Union, China have the highest vaccinated percentage. The changes in new cases of those countries are important to measure the effectiveness of vaccinations, however, bear in mind that other protective measures such as mask and social distancing could elevate the figures accordingly.

vaccinated population.png

5. How effective is vaccination in reducing new cases?


Europe had reached its monthly bottom in June, however, cases have been rising again in July despite a high vaccinated population. UK and France have had rising trends of new cases since May 20 despite having high fully vaccinated rate of nearly 50%. Therefore, precautionary measures such as mask and social distancing is absolutely essential in controlling the spread.

Europe vaccine.png
 

Israel once had the highest vaccination rate in the globe, and now its daily case reaches record high again.

Vaccine israel.png

The same trend also applies to North America, especially USA, the daily new cases is reaching its record high as in December 2020.


The above countries served as a good example to show that covid vaccine would become less effective over time, as indicated by the abrupt rise in cases. The causes are the declining antibodies, rapid mutation of the virus and the easing of restrictions. The following charts show a noticeable trend of South American Countries having much higher vaccination rate accompanied by a lower number of new cases. It is likely that South America will also experience the same decline in vaccine efficacy in the near future if no further precautionary measure is imposed.

Vaccine SA.png

5. How do the vaccines from different manufacturers perform?


This is a preliminary analysis of grouped countries using Pfizer, Moderna, AZ (LEFT) comparing to those using Sinovac, China National, Sputnik V (RIGHT). For the countries using Pfizer and Moderna, as indicated in the bottom left, we can clearly see a sharp decline in new cases since January 2021 after the start of wide vaccination. The decline in cases lasted until June 13 and since then rise back to record high. On the other hand, the second group of countries using Sinovac and Sputnik V have not had declining new cases until late July. That means the number of new cases kept rising rampantly since March 2021 despite a higher number of vaccinated population. It becomes more difficult to sort country in groups because those who original used Sinovac have recently introduced MRNA vaccines from Pfizer and Moderna etc.

Vaccine sorted.png