Derived (and augmented) dataset available in JSON, TSV and SQL formats

[cipriancraciun]

I have wrote some scripts that take both the series and daily reports files output the following two files:

• https://github.com/cipriancraciun/covid19-datasets -- the repository with the data and scripts;
• TSV -- https://github.com/cipriancraciun/covid19-datasets/blob/master/exports/jhu/v1/daily/values.tsv
• JSON -- https://github.com/cipriancraciun/covid19-datasets/blob/master/exports/jhu/v1/daily/values.json
• SQL -- https://github.com/cipriancraciun/covid19-datasets/blob/master/exports/jhu/v1/daily/values-sqlite.sql

If you want to automate the download (given how GitHub handles URL's to raw files), you can use the links listed on this page.

Also some plots for these available at:

• https://scratchpad.volution.ro/ciprian/eedf5eb117ec363ca4f88492b48dbcd3/

---

What I've done:

• the original JHU dataset has the daily data points in columns, which basically doesn't work with 90% of the usual tools; thus I have "normalized" this in a more SQL-friendly format, with one data-point per row, keyed by location + date;
• for those countries with regions / provinces I have added a "total" row; (it's easier for plotting;)
• I have also provided total for US country and US states;
• I have added an infected column which is computed as infected := confirmed - deaths - recovered; (this data is available up to 2020-03-22;)
• for each of the metrics (confirmed, recovered, deaths and infected) I have added four additional metrics (i.e. in total 16 metrics):
  • absolute_* -- the original value from the JHU dataset, i.e. cumulative values;
  • relative_* -- the metric divided by confirmed in percentage; (I.e. how many recovered people from the total confirmed up to that date;)
  • delta_* -- the difference from the previous day; (in case of infected the number can be negative;)
  • deltapct_* -- the delta divided by the previous day value; (i.e. the speed in percentage;)
• I have also added the day_index_* columns which represents the day index since that country / region has reached either 1, 10, 100, or 1000 confrimed cases; (it helps align countries and compare them to that;)
• I have normalized the country names (i.e. some countries are named differently in different rows, etc.);
• I have augmented the country data with ISO codes, continents, subcontinents and other useful information;
• I have added population columns based on CIA Factbook dataset;
• I have added total rows for continent and sub-continent levels;
• I have provided the date in ISO format;

I will update these files twice per day, say at 06 UTC and 12 UTC.

Moreover I have also added the in the same format also the NY Times US dataset and the ECDC one.

---

The scripts are available in the following repository and consist mainly of jq snippets.

• https://github.com/cipriancraciun/covid19-datasets

---

If anyone has other ideas about what I can add to these augmented datasets please let me know.

[cipriancraciun]

I'll point here a few other issues that are solved by my derived dataset:

• Stabilise country names #1395 -- complaints about mismatched country names;
• Is it necessary to constantly change the column titles/formatting? #1342, Schema Control #1411, How to still get recovery cases #1402

[cipriancraciun]

Additional issues that popped-up in the last day that would be helped:

• change of data structure and format (too) frequent  #1588, Datasourse Consistency #1587 -- complaints about frequent changes in the structure;
• Date format for Global Recovered counts differs from Global Confirmed and Global Deaths counts #1581, New time series global files issue with dates #1638 -- complains about date formatting inconsistencies;
• row consolidation in global.csv #1586 -- requests consolidated data;
• US states no longer available in time series data??? #1578, Adding column with total value for state #1568, US Data by States Now Unavailable #1549 -- complaints about US series data;
• If you need: I created US state level time series confirmed cases up to Mar. 25, 2020 #1583 -- provides downloads for US state level;
• Isreal data is inconsistent between daily report and time series #1574, Warning for everyone who use the CSV files. #1553 -- inconsistencies between daily reports and time series;
• Trends and predictions #1571, Conversion tool #1502 -- similar projects to parse the JHU data;
• Diamond Princess is not a Canadian province - lines 233 and 240 in time_series_covid19_confirmed_global.csv #1562, Consistency of cruise ship names #1608 -- issues with Diamond/Grand Princess as provinces of US / Canada;
• Do you need R code to import all daily files into single dataframe? #1543 -- about R code to load daily reports in one DataFrame;
• column names for daily reports are inconsistently labelled #1539, Consistency of Identifiers (Surprise! Country/Region is now Country_Region...) #1326, Columns Different #1617 -- inconsistent column names for daily reports;
• Your actions have consequences. #1615 -- breaking dashboards with automatic data retrieval;

[dnalkram]

Thanks cipriancraciun but app I'm using throws a validation error trying to use the JSON file and online validators too indicate problems with format.

[cipriancraciun]

@dnalkram What is the actual error you are getting?

You could open an issue on my repository (https://github.com/cipriancraciun/covid19-datasets) so that we don't clutter the JHU repository with this.

[dnalkram]

In Power BI I get
"We found extra characters at the end of JSON input.".
I then used site: https://www.freeformatter.com/json-validator.html
and got:
"The JSON input is NOT valid in JavaScript, illegally formed XML syntax (At line #1164), (At position #1)"
Hope this helps.

[cipriancraciun]

@dnalkram Could you paste the URL you've used to download the file?

The links in the description above are directly from GitHub, and you must use the raw URL from that page.

To keep things simple, at the following link you can find the files on my own site, which shouldn't give you any issues:

• https://scratchpad.volution.ro/ciprian/eedf5eb117ec363ca4f88492b48dbcd3/exports/jhu/v1/values.tsv
• https://scratchpad.volution.ro/ciprian/eedf5eb117ec363ca4f88492b48dbcd3/exports/jhu/v1/values.json

(You can use these second links as I usually update them as soon as I push to GitHub.)

If you still get errors, download that link and double check that you are actually getting a JSON and not an error HTML page.

[dnalkram]

That file worked fine, thanks for all the help!

[sbw78]

THANK YOU! I have spent the past 3 days trying to wrangle the CCSSE daily reports into a parseable time series, but with the constant changes in data format and inconsistent location naming I was going insane. Looking forward to checking out your data.

[cipriancraciun]

@sbw78 I'm glad I could help you. If you encounter any issues, please open a ticket on my repository and describe the issue. (Given the "quarantine" I usually reply fairly quickly.) :)

BTW, in the interim I have integrated also the NY Times dataset and the ECDC one; thus if you are looking for alternative data you can choose one of these.

[ChrisParkerWA]

Hi Ciprian,
Thought I would submit a request here if I may.
Is there any way to create a simple JSON dataset along the lines of, for example
"country": "Australia"
"cases": 4860
"deaths": 20
"recovered": 244

[cipriancraciun]

@ChrisParkerWA I just opened an issue on my repository (cipriancraciun/covid19-datasets#12), where I've proposed to add such a simple format, but perhaps with only a few more values. Please verify my proposal there and let me know if it works for you.

[cipriancraciun]

In the interim I've also added SQL and SQLite DB files for all the datasets.

[AmauryVanEspen]

Hello @cipriancraciun
there is several API available on SwaggerHub
It seems https://gravitee.io/ could also do the job or a simple flask app http://michal.karzynski.pl/blog/2016/06/19/building-beautiful-restful-apis-using-flask-swagger-ui-flask-restplus/
Is there a willingness to setup this kind of service ?

[cipriancraciun]

@AmauryVanEspen I've just opened an issue about this feature request on my repository:

• Provide an actual API for exposing the data cipriancraciun/covid19-datasets#28

I would propose moving the discussion there, as this isn't strictly JHU related.

(I would be open to such an API, however we must first understand exactly what its use-cases would be.)