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 99 Replies 131 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
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 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 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
All Replies (99)
Recommended 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 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 Answer
great work!
Thank you!
recommended this
marked this as an answer
Recommended 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 Answer
Thanks .
Posted under the New topic
recommended this
marked this as an 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.
