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.
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.
The coronavirus spreadsheet has been available since March 2020 with tens of thousands of copies made. Nevertheless, the traffic in the spreadsheet has gone down quite a bit, and after the recent show of hands, I have decided to stop the maintenance of the spreadsheet.
The latest version is release 3.177 of 5 February 2021, and it will continue to be available at this link. You can also create your own private copy to analyze the data and create your own summaries and charts. The spreadsheet is freely available under a CC Attribution-ShareAlike 3.0 license.
If you need help with your own summaries and charts, please post in a new topic in the Google Sheets forum, and share a sample spreadsheet. The experienced Google Sheets users there will be happy to help as long as you remember to share a representative copy of your spreadsheet.
Cheers --Hyde
----------
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:
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.
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.
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.
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.
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).
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.
After the recent show of hands, I have decided to stop the maintenance of the coronavirus spreadsheet.
The latest version is release 3.177 of 5 February 2021, and it will continue to be available at this link. You can also create your own private copy to analyze the data and create your own summaries and charts.
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.
0
Relevant Answer Relevant Answers (0)
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
The coronavirus spreadsheet has been available since March 2020 with tens of thousands of copies made. Nevertheless, the traffic in the spreadsheet has gone down quite a bit, and after the recent show of hands, I have decided to stop the maintenance of the spreadsheet.
The latest version is release 3.177 of 5 February 2021, and it will continue to be available at this link. You can also create your own private copy to analyze the data and create your own summaries and charts. The spreadsheet is freely available under a CC Attribution-ShareAlike 3.0 license.
If you need help with your own summaries and charts, please post in a new topic in the Google Sheets forum, and share a sample spreadsheet. The experienced Google Sheets users there will be happy to help as long as you remember to share a representative copy of your spreadsheet.
Cheers --Hyde
----------
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:
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.
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.
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.
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.
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.
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.
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.
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.
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
false
false
Subscribe to updates
Write a reply
10 characters required
Failed to attach file, click here to try again.
Edit link
Text to display:Link to:
–|
Notifications are currently off and you won't receive updates. To turn them on, go to Notifications preferences on your Profile page.
Discard post?
You will lose what you have written so far.
Personal information found
We found the following personal information in your message:
This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?
A problem occurred. Please try again.
Create Reply
Edit Reply
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.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.
Go to the Legal Help page to request content changes for legal reasons.
Reported post for abuse
Unable to send report.
Report post
What type of post are you reporting?
Off-topic
Post is disrespectful, about third party products or contains unrelated content or personal information.
Abuse
Post contains harassment, hate speech, impersonation, nudity; malicious, illegal, sexually explicit or commercial content.
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.
Go to the Legal Help page to request content changes for legal reasons.
Reported post for abuse
Unable to send report.
Google user
This reply is no longer available.
Badges
Some community members might have badges that indicate their identity or level of participation in a community.
Google Employee — Google product team members and community managers
Community Specialist — Google partners who help ensure the quality of community content
Diamond Product Expert — Community members with product mastery who help other Google users and Product Experts
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Marked as Recommended Answer
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.