Graphing Covid-19 in gSheets

I initially underestimated Covid-19 when the issues started. I guess that's because we're fairly protected on our small island. Unfortunately, we will keep up with the rest of the world. In New Zealand we only have 8 confirmed cases, but the number of actual cases is probably much, much higher.


This is just a quick post. A bit of a how-to for graphing the current info in gSheets.


Here's how I did it. (Too lazy to do it yourself? Just make a copy of mine and adjust it how you want to!)


First, we need to find the info. A data sheet that has fairly up-to-date info can be found at covid.ourworldindata.org. Just pull the info from their csv file with this formula:

=QUERY(TRANSPOSE(QUERY( IMPORTDATA("https://covid.ourworldindata.org/data/total_cases.csv"), "order by Col1 desc limit 1 format Col1 'YYYY-MM-DD'",1)), "where Col1<>'World' order by Col2 desc label Col1 'Last updated:'",1)  

Select the data and choose 'Geo chart with markers' to get a nice chart of the data. Give it a few moments to populate.


What it does:

This formula is importing data from the csv (comma separated values) file - just a small file that exists on the interwebs. The data is flipped though, so the TRANSPOSE moves it from horizontal to vertical places.

It then looks through the data, orders it by date in descending (desc) order and only returns one column, which is the most recent info (that's the limit 1 part). It then formats the dates as year-month-date.

The next part ignores the 'World' label because we're making a geo chart and don't what the whole world coloured in. It orders the data by number of cases and finally labels the column "Last updated:".

The 1 at the end just says that the data has headers.

If you want info on how many cases there are in a particular country you can just import the data straight from the csv file. The problem with this is you'll need to continually update which column you're wanting to view the differences in countries.

=QUERY( IMPORTDATA("https://covid.ourworldindata.org/data/total_cases.csv"), "order by Col1 desc label Col1 'Date' format Col1 'YYYY-MM-DD'",1)

What it does:

Pretty much the same as previous. This formula is importing data from the csv file. It then looks through the data, orders it by date in descending (desc) order, labels the column "Date" (because in the original csv the date is not capitalised), then formats the dates as year-month-date.


But what if you want a graph that updates when you select a different country?


Luckily we did that previous step because we can now use it in the next part.


Next you'll need a list of the countries. In a new cell in a new tab click Data > Data validation, then using the import from above select all the countries (you choose if you want 'World' in there) from whichever tab you put it in. This creates a drop-down menu for you to select which country to look at.


Next, let's get all the dates. In a cell below the drop down box use this formula:

=INDIRECT("'Raw live data'!A:A")

and next to it, this code block to get the data:

=INDIRECT("'Raw live data'!"&SUBSTITUTE(ADDRESS(1,MATCH($B$2,'Raw live data'!$A$1:$EH$1,0),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,MATCH($B$2,'Raw live data'!$A$1:$EH$1,0),4),1,""))

*Note here that my data is in a sheet called 'Raw live data' and the data is in cells from A1 to column EH. My drop-down box is in cell B2.


What it does:

The first Indirect formula just pulls the entire date column. Nothing interesting there.

The second indirect formula looks at which country you've selected (that's the $B$2 part) and checks the entire raw data to see which column it's in. In the image below, the word "World" is in column B in the tab 'Raw live data', so that entire formula just boils down to =INDIRECT("'Raw live data'!B:B) and pulls that column in.


Here's what it all looks like:


Hopefully I can find some time to model what the situation might be like here in New Zealand over the coming weeks. I'll try find time to get that up and running!


For those already being heavily affected, I wish you all the best and remember your social distancing measures!

© TheMathLab