/docs/community?hl=en
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
1 Recommended Answer
$0 Recommended Answers
1 Relevant Answer
$0 Relevant Answers
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.
Most Relevant Answer Most Relevant Answers (0)
All Replies (106)
Most Relevant Answer
great work! 
Thank you!
marked this as an answer
Most relevant based on info available
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
marked this as an answer
Most relevant based on info available
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
 
 
marked this as an answer
Most relevant based on info available
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
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
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?
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.
This reply is no longer available.
/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.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
/docs/profile/0?hl=en