Johns Hopkins University COVID-19 daily data workflow

Johns Hopkins University CSSE released the data set that powers their dashboard on GitHub (https://github.com/CSSEGISandData/COVID-19). If you want to work with that data easily, I created the attached workflow and macro to import the daily data found within that repository. Install the package to the root of the folder that Git creates. This will import the daily data, parse the date fields that change formats halfway through the timeseries, update null latitude and longitude fields, and other general cleansing. With that done, you can experiment with daily worldwide COVID-19 confirmed/deaths/recovered case numbers at the country/region and province/state level, with geocoding available for about 99% of records.

If this workflow is useful, please let us know. If you need help or have improvements to the workflow, please share.

We'd also love for you to share what you create or discover by replying to this thread!

EDIT: The workflow has been updated to better clean and regularize the data. A lot of clean up is being now to country and state names, with merging of duplicates being done, and a locality field being parsed out of values such as "Chicago, IL". Review the new workflow for details. This should improve the quality of the output data significantly, although JHU is still working on upstream issues on their end.

JHU_COVID-19_Daily_Import.yxzp

Topic of Interest

Data Challenge

Resource

COVID-19

Accepted answers

All comments

mbarone

Hi David,

JHU stopped publishing county level data on Mar 09. Starting with Mar 10, you'll only have state level data on both the daily report csvs and time series csvs. Just a heads up in case anyone is wondering why all the counties suddenly show "0" for all the county values as of Mar 10. To my knowledge, that only place to get county level stats is to search and find individual state Departments of Health and see if they publish them.

ChrisK

There's a lot of cleansing needed here. Every file from 02-09 to 02-29 has a row:

"Chicago, IL",US,2020-02-09T19:03:03,2,0,2

Probably a unique tool on Province/State, Country/Region, Last Update would be good.

DavidW

A new version of the workflow that handles all of the irregularities in country, region, and locality data has been uploaded. The time portion of the updated field was removed and a unique tool added. Also, cruise ships were assigned in various ways previously, and now are under "Cruise Ship" for country, as that seemed to be the general direction JHU was heading. Keep that in mind if you'd prefer to handle the disposition of cruise ship data in a different manner.

The country name is now changed to use the ISO-3166-2 name. I've also added the ISO-3166-2 sub-region and intermediate region as new fields. The CSV file containing that data is included in the YXZP if you'd like to bring in additional country-related fields from that.

kkoenig

@mbarone The University of Virginia's Biocomplexity Institute has county level data available on their dashboard. It's in rough shape, but nothing Alteryx can't handle.

I've attached the workflow I've been using to get it into a usable format. It's nothing fancy, but it works! Any suggestions for improvement are always welcome.

The workflow will look for "COVID FIPS Lookup Table.yxdb" in C:/COVID_Data and will write the output to the same directory.

EDIT: The first formula was subtracting 5 hours instead of adding them to match the UTC time zone in the data source. It's fixed in the attached version.

Latest_UVA_COVID_Data.yxdb

COVID FIPS Lookup Table.yxdb

klpenney_Raytheon

Please go check out this amazing dataset. They are doing it the old fashion way with brains and tenacity.

https://coronavirus.1point3acres.com/zh/faq

https://coronavirus.1point3acres.com/en/about

klpenney_Raytheon

See this source please

https://coronavirus.1point3acres.com/zh/faq

kkoenig

@klpenney_Raytheon Thank you for sharing! I see that they have the death details by county as well. That's so helpful!

klpenney_Raytheon

I have requested data access but since it is for commercial purpose I am not sure how he will respond. Please let me/us know if you find a way in.

LukeM

Hi @DavidW,

Love this - great work.

Is there a reason that you haven't set it up to run the 'git pull' as an event before the workflow runs in order to pull the latest data set automatically?

Luke

DavidW

@LukeM I didn't want to assume that people were using git to retrieve the data. In the discussion forum, another user set up a Download tool instead to retrieve the files. I certainly can make a version of the workflow that handles the `git pull` automatically.

LukeM

@DavidW I guess I just wanted to make people aware of the ability to create a batch file to run the command line script to perform the 'git pull' and then use the Events panel to trigger the batch file before the workflow starts.

sean_bolte_dup_544

Already put this to use, and feeding a Tableau dashboard. Same comment as Luke on this, and I just created the cmd line prior to module kick-off for obtaining the refresh. The "git pull" is a new methodology, so I thank you for highlighting another option for obtaining data from the web!!

klaus01

Hi @DavidW,

Thanks for sharing the knowledge.

I'm curious about @LukeM comment. May I ask how to create a event to run the 'git pull', like @LukeM suggested?

Thanks!

LukeM

AUTOMATE GIT PULL IN ALTERYX BEFORE WORKFLOW RUNS

You need to create a batch file (.bat) which can trigger the command line script as part of your Alteryx workflow. This can then be set up as a Event to be triggered before the workflow.

Steps (these are a continuation of the process outlined above)

1. Create a text file in notepad which contains the following script:

cd \Data\COVID-19
git pull

This changes the directory and then runs the 'git pull' command.

2. Save this file with the extension .bat rather than .txt to create the batch file

3. In your 'Daily_Import.yxmd' workflow head to the 'Events' tab of the workflow configuration panel.

4. Click 'Add..' and select 'Run Command' and then locate your newly created batch file and configure it to trigger "Before run" as shown:

@klaus01 @sean_bolte_dup_544 @DavidW

DavidW

@LukeM @klaus01 You can simplify the event process even further. Since the workflow should be placed in the same directory as the data, there's no need to have a batch file that changes to that directory - you're already there. In @LukeM's instructions, skip to step3 and in step 4, change the command to git with the command arguments set to pull.

You will only need the batch file if you have the workflow placed somewhere other than the data directory, in which case you would also need to modify several tool parameters to absolute file references, rather than relative references anyway.

klaus01

@LukeM

Thank you! It works perfectly.

@DavidW

I didn't leave the workflow in the same directory as the data, for fear of interfering in some process when updating the data through git pull.

nickbecks

As of 3-23-20 the format of the daily reports has changed and thus data isn't making it thru the workflow. Wish I could figure out how to fix it, but my skills are still noob.

DavidW

@nickbecks I'm working on an updated workflow now. Thanks for the heads up!

DavidW

Thanks to @nickbecks for notifying me that the data format changed on 23 March. I'm working on an updated version of the workflow that will handle the changes. JHU likes to keep us on our toes with their schema changes.

klaus01

@nickbecks

They changed date format, added same countries with different names, included columns. I treated the new file (23/03/2020) separately, and then overwrite on the "\csse_covid_19_daily_reports" folder.

I tried to treat on the first formula, but I was not able. So I took the detour above.

shevshenko

All the data Extract and the Alteryx Workflow worked perfectly, my only question is if anyone has worked with the Data? I have sliced it and built a TDE file to read in Tableau but for some reason the Confirmed cases around the globe are way higher that the numbers reported in the dashboard , Not sure if I am doing something wrong but for example US as of today is showing arond 53k confirmed cases vs the 120k confirmed cases that I am getting with the extracted data. I attached a Viz that uses a TDE generated through Alteryx. Any thougths?

klaus01

@shevshenko

Did you consider just the last day row for each country?

Because, if you sum all row per country you'll get distorted number. Example, day 1, 3 confirmed, day 2, 5 confirmed, the correct amount of confirmed is 5, but you sum will get 8 confirmed.

Check if is not this.

klpenney_Raytheon

You are correct there are global lags. It is really immaterial as long as you are not over counting via too many duplications. Be rational.

Keith L. Penney
Data Analyst, Sr.
TEKWISSEN Contractor
Supply Chain Transformation
Space and Airborne Systems (SAS)
keith.penney-nr@raytheon.com
Desk: 972-344-9349
Mobile: 901-603-2906

DavidW

@nickbecks and others:

Here's a new version of the workflow that addresses the radical change in schema that JHU introduced starting with the 22 March data file. They've cleaned up column names, which is nice, and also added a FIPS field with the numeric code for US counties, and Admin2 field with the name of an administrative region below province or state level (this is county level for the US), and added an Active field which represents the current active cases as of the Last_Updated value. This is calculated as Confirmed - Deaths - Recovered.

Because of the number of changes in the schema, the original batch macro is removed. Otherwise, use as you would the original workflow.

Daily_Import2.yxzp

nickbecks

@DavidW thanks, this is great.

Is there a way to pass file name as a field output from the Dynamic Input tool? I tried and failed...

I'm looking to use file name as date rather than last updated date. Using last updated date results in gaps when a specific region does not have a data point for a given date. For example many regions (US, Spain, Italy, Germany, France, etc) don't have a last updated date record for 3/13/20, so if you were to sum total cases on 3/13/20 you would end up with a misleading result.

neilgallen

More sources are being made available, so I figured I'd contribute other options.

New York Times: In response to a number of requests, the New York times is releasing the data they have used for their own reporting. Details can be found on their github here. The data starts from the first reported case in Washington state as of 2020-01-21.

This workflow utilizes one python tool to pull the data in csv form (simpler to transform into a table using pandas), and then requires a few small modifications to include latitude and longitude where none was provided.

New_York_Times_Github.yxmd

neilgallen

In the theme of additional sources, google has made the johns Hopkins CSSE dataset available via their bigquery platform.

I haven't thoroughly checked the accuracy, given how many changes the CSSE dataset has gone through regarding schema, but it's google so I trust them?

Obviously accessing via alteryx is as simple as using the big query input tool, or python!

kevinraj127

There's been a lot of great ideas and discussion on here, thanks a lot for everyone's help! I've attached a workflow that uses the GeoJSON API that powers the Johns Hopkins COVID-19 dashboard. This should help in getting data more periodically throughout the day rather than at the end of the day when the csv files are posted to JHU's GitHub page. Here is the link on ESRI's site for more info on the data:

https://coronavirus-resources.esri.com/datasets/628578697fb24d8ea4c32fa0c5ae1843_0?geometry=13.259%2C-19.609%2C-37.717%2C71.199

Thank you and be safe!

-Kevin Raj

Johns_Hopkins_Datapull_ESRI_GeoJSON.yxmd

imattclark

@LukeM

I am getting the following error:

Error: Designer x64: The Designer x64 reported: Error running Event #1: The external program "gitpull.bat" returned an error code: 1: The system could not find the environment option that was entered. (203)

Is this because I've already run the git pull today? The .bat itself will execute outside of Alteryx.

chriscuk

David,

I downloaded your original workflow and I think it processed all the files in the daily reports directory, but I have just revisited it and it seems to only process up to the end of March. Do you have a later version?

shevshenko

Hello Chriscuk - I had noticed some small tune up that might be required, I have seen for instance some column headers that may be affecting when you run but at the end I included a version based on David's that I am currently using to load my own dahsboard. Please take a look to the attached one, I just used it to load till April 12 Data that is the latest available Data file.

shevshenko

I missed the workflow, sorry.

Daily_Import2.yxmd

bcampbell0621

Hi David,

This workflow is wonderful! I used it yesterday for the first time and worked well. I used the latest daily reports from JHU which included all reports up to 5/7/2020, but when the flow ran, I only had up to 3/22/2020. I went through the flow, but was unable to determine why.

Any ideas?

Many thanks for this!

Bruce.

shevshenko

Thanks for sharing, while analyzing the flow, does this only include US country? I could not handles to see a global overview but US only.

shevshenko

Hi - have you tried to use the Workflow I shared in this same previous post: Daily_Import2.yxmd

I have been using it, I remember I had some issues while getting all of the files Data but I have made some adjustments to it and should be working okay now.

Thanks

Sergio