This content is likely not relevant anymore. Try searching or browse recent questions.
best way to summarize Johns Hopkins coronavirus data - pivot table or query? 3 Recommended Answers 106 Replies 143 Upvotes
I'd like to take the raw data that Johns Hopkins is compiling on COVID-19 cases and try to summarize it in particular ways and chart the data. Not sure if pivot table or query is better suited for what I'm trying to do.
The raw data is here (and every day, a new column for the latest date is added): https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv
What I'd like to do is add a column for 'continental region', so for example, all European countries would fall under continental region Europe.
I'd also like a table that calculates the difference in cases from one day to the next. So the table would contain all the same rows representing countries and all the same dates, and each date would represent only the increase from the prior date's total cases.
And then I'd like to create several charts.
1. chart time series for the continental regions
2. chart time series of total cases for all countries where cases > 100
3. chart time series of new cases for all countries where cases > 100
Any suggestions?
Also, any suggestions on how to update the data? It's possible the row count in the raw data will change as new countries and states/provinces are added, so I can't just copy the last column representing the latest date and paste that onto the end of my table.
Details
Recommended Answer Recommended Answers (3)
Recommended Answer
Most Relevant Answer
Hi bokononist i,
I created a spreadsheet that imports the data and categorizes it. You can make your own private copy of the coronavirus spreadsheet here:
Your copy of the spreadsheet will update automatically every day with the latest data and charts. Please feel free to create your own summaries and charts on top of what is already there.
You can also get just the data by using an
importrange() formula that points to the original corona spreadsheet:That original instance of the COVID-19 spreadsheet experiences heavy traffic at times. If you have trouble opening the above link, and just want to quickly view the data, use this web page that mirrors the COVID spreadsheet and updates at the same time with it:
The spreadsheet keeps automatically up to date by importing confirmed coronavirus cases data from Johns Hopkins CSSE and The New York Times, and region data from StatisticsTimes and WikiPedia.
For those interested in how the spreadsheet works, the primary formulas that import and categorize the data are in 'JHU data', 'NYT data', 'Regions' and 'Totals by region and continent'. The charts get their data from dataset tabs that in turn use hidden helper tabs where
query() formulas do most of the the heavy lifting. It is all done with the built-in functionality of Google Sheets without the use of macros or scripts.Cheers --Hyde
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
Hi bokononist i,
Finetuned the categorization by region and continent in the Coronavirus - CSSEGISandData - COVID-19 spreadsheet.
Also inserted a couple of charts like the ones below. The first chart shows that Italy has by far the greatest number of reported cases in Europe, and continues to grow the fastest:
(chart)
The second chart shows that Asia continues to have the most reported cases, but Europe has reported more new daily cases during recent weeks:
(chart)
You can create additional charts by choosing File > Make a copy and using standard Google Sheets features to query the data and make charts.
The spreadsheet updates automatically by downloading new data from COVID-19 (2019-nCoV) Data Repository by Johns Hopkins CSSE at GitHub. Data on regions and continents comes from StatisticsTimes.com.
Cheers --Hyde
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
In the interests of providing as many options as possible, the people at Chartable have also created some charts to display the data that is updated as things progress. There are a couple interesting breakdowns there that I haven't seen elsewhere (yet).
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Most Relevant Answer Most Relevant Answers (0)
All Replies (106)
Recommended Answer
Most Relevant Answer
Hi bokononist i,
I created a spreadsheet that imports the data and categorizes it. You can make your own private copy of the coronavirus spreadsheet here:
Your copy of the spreadsheet will update automatically every day with the latest data and charts. Please feel free to create your own summaries and charts on top of what is already there.
You can also get just the data by using an
importrange() formula that points to the original corona spreadsheet:That original instance of the COVID-19 spreadsheet experiences heavy traffic at times. If you have trouble opening the above link, and just want to quickly view the data, use this web page that mirrors the COVID spreadsheet and updates at the same time with it:
The spreadsheet keeps automatically up to date by importing confirmed coronavirus cases data from Johns Hopkins CSSE and The New York Times, and region data from StatisticsTimes and WikiPedia.
For those interested in how the spreadsheet works, the primary formulas that import and categorize the data are in 'JHU data', 'NYT data', 'Regions' and 'Totals by region and continent'. The charts get their data from dataset tabs that in turn use hidden helper tabs where
query() formulas do most of the the heavy lifting. It is all done with the built-in functionality of Google Sheets without the use of macros or scripts.Cheers --Hyde
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
Hi bokononist i,
Finetuned the categorization by region and continent in the Coronavirus - CSSEGISandData - COVID-19 spreadsheet.
Also inserted a couple of charts like the ones below. The first chart shows that Italy has by far the greatest number of reported cases in Europe, and continues to grow the fastest:
(chart)
The second chart shows that Asia continues to have the most reported cases, but Europe has reported more new daily cases during recent weeks:
(chart)
You can create additional charts by choosing File > Make a copy and using standard Google Sheets features to query the data and make charts.
The spreadsheet updates automatically by downloading new data from COVID-19 (2019-nCoV) Data Repository by Johns Hopkins CSSE at GitHub. Data on regions and continents comes from StatisticsTimes.com.
Cheers --Hyde
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
great work!
Thank you!
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
Hi Martin,
Thanks. You are welcome to use my work in the Resource Center or elsewhere. Note that JHU CSSE and StatisticsTimes.com both have their own terms of use that are quite permissive as long as the data is used for educational or academic research purposes.
Cheers --Hyde
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
Hi everyone,
For your information, Johns Hopkins CSSE had a glitch in their coronavirus data update today. They reacted to my issue report pretty quickly, but there was a five hour window starting at about 7 am GMT today 12 August 2020 during which the data for Hong Kong, Macau and the Netherlands was incorrect.
JHU CSSE fixed the issue around 12 noon GMT today, and the coronavirus spreadsheet was updated with correct data soon after.
Release 3 of the coronavirus spreadsheet has been out for four months now. Some of you volunteer your country, city and interest when making a copy of the spreadsheet, which gives the following user demographics:
- 17% medical professionals or researchers
- 29% business, community or professional use
- 33% family or personal use
- 21% students
Copies have been made in almost 100 countries. The biggest interest has been in the USA, India, Mexico, the UK and Germany.
Spreadsheet release 3 has continued to work pretty OK, and there haven't been many feature requests. I have put the development of release 4 on hold, so it does not look like that a major new version would be forthcoming. I will post here if that changes.
You can make your own private copy of the coronavirus spreadsheet here:
In the event you need help with your summaries or charts, please post in a new topic mentioning coronavirus, and share a representative sample spreadsheet with "can edit" rights.
Cheers --Hyde
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
This question is locked and replying has been disabled.
Link to post
Delete post?
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
This reply is no longer available.
Badges
Some community members might have badges that indicate their identity or level of participation in a community.
