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?
Pin
Lock
4 Recommended Answers 117 Replies 154 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 (4)
Recommended Answer
Relevant Answer
[edit 10 February 2021]
Hi everyone,
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:
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.
Recommended Answer
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.
Recommended Answer
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.
Recommended Answer
Relevant Answer
Hi everyone,
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.
The spreadsheet is freely available under a CC Attribution-ShareAlike 3.0 license.
Thank you all for the interest you have shown in this visualization utility.
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.
Relevant Answer Relevant Answers (0)
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
All Replies (117)
Recommended Answer
Relevant Answer
[edit 10 February 2021]
Hi everyone,
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:
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.
Recommended Answer
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.
Recommended Answer
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.
Recommended Answer
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.
Recommended Answer
Relevant Answer
Thank you Hyde for taking this huge project on over the past year! It has been a fabulous resource and has helped many people world-wide. Thank you!
Adam
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.
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.
