Message Boards

GROUPS:

Staff Picks Authoring and Publishing Data Science Curated Data Dynamic Interactivity Graphics and Visualization Wolfram Cloud

David Lomiashvili

City-level Search Tool for Coronavirus (COVID-19) Confirmed Cases

David Lomiashvili, Wolfram Research Inc

Posted 1 year ago

2214 Views

2 Replies

5 Total Likes

Follow this post

MODERATOR NOTE: coronavirus resources & updates: https://wolfr.am/coronavirus

I wanted to make a cloud applet that would allow users to enter the name of a city and receive a number of confirmed cases in it. Data quality, key outputs and their visualisations were going to be important for usefulness of the tool. I used CloudDeploy and FormPage to make an interactive search tool that queries “Patient Medical Data for Novel Coronavirus COVID-19” from Wolfram Data Repository.

Row[{Hyperlink["OPEN CLOUD APP",URL["https://www.wolframcloud.com/obj/davidl0/CoronavirusTool"],FrameMargins10,BaseStyleWhite,ActiveStyleBlack,BackgroundRed,Appearance"Palette"],Text[" [NOTE: Long load times]"]}]

OPEN CLOUD APP

[NOTE: Long load times]

Out[]=

Objectives & Motivation

There are a few things that (almost) equally contributed to my motivation for doing this project:

I found it weird that basically no news source or a search engine provided a number of confirmed cases on a city level - which is arguably a more important measure to people than a State-level number. It’d be foolish to disregard the importance of commute and other types of daily movement of people between cities but I’d argue that what impacts people’s behavior and state of mind most is the severity of spread in their locality - city, town, etc. [UPDATE: Since I wrote this post, NYT released a dataset for county-level data]

There are so many great resources and content that we (Wolfram Research) is producing around this topic, making interesting computational explorations, essays and deployed applets becomes super straightforward and easy (COVID-19 data is right there in the Wolfram Data Repository (WDR), ready to be ingested with just one line of code);

After reading so many insightful posts by our community members, I got bitten by the contribution bug (sorry for a bad pun);

I’ve always wanted to post on our Community and given my family’s current state - Sheltered-in-place, I finally had free time to take on a pet-project using WL;

Project Breakdown

A great thing about this project is that the data is basically provided on a platter (by WDR), no need to clean and wrangle it for hours; All we need to do is to do some data exploration and then count relevant cases, albeit the counting will require using some sophisticated functionality of WL. Overall project involves importing, exploring and computing on data and eventually deploying a data “product”.

I will be describing the following stages of the project in this post:

Import the data

◼

Explore the data and assess what is achievable

◼

Map out the computations and output visualisations

◼

Deploy and automate

◼

Data Import

Having aggregated patient data available in a computable form in the WDR was one of the main reasons I was able to finish this tool in a weekend (I’m not the fastest when it comes to coding).
So, let’s import the dataset object from WDR:

coviddataobject=ResourceObject["Patient Medical Data for Novel Coronavirus COVID-19"]

ResourceObject

Name: Patient Medical Data for Novel Coronavirus COVID-19 »
Type: DataResource
Description: Medical records of patients infected with novel coronavirus COVID-19



Out[]=

WDR supports auto-updating of resource objects but if it fails we may need to manually delete the object and re-initialize it.

DeleteObject[coviddataobject]

Just to make sure the data was successfully imported, let’s do a quick examination of the dataset using a RandomSample of 10:

coviddata=ResourceData[coviddataobject];

In[]:=

RandomSample[coviddata,10]

Age

Sex

City

AdministrativeDivision

Country

GeoPosition

DateOfOnsetSymptoms

DateOfAdmissionHospital

DateOfConfirmation

Symptoms

LivesInWuhan

LivesInWuhanComment

TravelHistoryDates

TravelHistoryLocation

ReportedMarketExposure

ReportedMarketExposureComment

ChronicDiseaseQ

ChronicDiseases

SequenceAvailable

DischargedQ

DeathQ

DateOfDeath

DateOfDischarge

—

Xiangfan

Hubei, China

China

31.93°N 111.9°E

—

Sat 1 Feb 2020

—

Hamedan, Iran

Iran

34.87°N 48.55°E

—

Tue 3 Mar 2020

—

Taegu-gwangyoksi, South Korea

South Korea

35.78°N 128.6°E

—

Sat 22 Feb 2020

—

Wuhan

Hubei, China

China

30.63°N 114.3°E

—

Sat 8 Feb 2020

—

Xiaogan

Hubei, China

China

31.12°N 113.9°E

—

Sat 1 Feb 2020

—

Wuhan

Hubei, China

China

30.63°N 114.3°E

—

Sun 16 Feb 2020

—

Wuhan

Hubei, China

China

30.63°N 114.3°E

—

Neimenggu, China

China

40.21°N 109.9°E

—

Mon 3 Feb 2020

—

Wuhan

Hubei, China

China

30.63°N 114.3°E

—

Sat 8 Feb 2020

—

Wuhan

Hubei, China

China

30.63°N 114.3°E

—

Mon 27 Jan 2020

—

Out[]=

Looks like we got the data.
Next, let’s see what type of information is actually in it.

Data Exploration

Data Fields

As we can see from the column headers of the dataset in the previous section (and by extracting Keys of the dataset), there are 23 data fields.

coviddata[1]//Keys

Age	Sex	City	AdministrativeDivision	Country	GeoPosition
DateOfOnsetSymptoms	DateOfAdmissionHospital	DateOfConfirmation	Symptoms	LivesInWuhan	LivesInWuhanComment
TravelHistoryDates	TravelHistoryLocation	ReportedMarketExposure	ReportedMarketExposureComment	ChronicDiseaseQ	ChronicDiseases
SequenceAvailable	DischargedQ	DeathQ	DateOfDeath	DateOfDischarge

Out[]=

This is great though we won’t be using most of them for this projects purposes. We will primarily be utilizing location and temporal data from the dataset.

How up-to-date is our data

A very important factor for usefulness of our tool will be how current the underline data is. We can find this out by checking the maximum for “DateOfConfirmation” values.

coviddata[Max,#DateOfConfirmation&]

Day: Fri 13 Mar 2020

Out[]=

Not quite real-time but within a 1 day window.

Location Data

Let’s examine just the location fields:

coviddata[All,{"City","AdministrativeDivision","Country","GeoPosition"}]

City	AdministrativeDivision	Country	GeoPosition
Hefei	Anhui, China	China	31.65°N 117.7°E
Hefei	Anhui, China	China	31.78°N 117.3°E
Hefei	Anhui, China	China	31.83°N 117.2°E
Hefei	Anhui, China	China	31.83°N 117.2°E
Hefei	Anhui, China	China	32.0°N 117.6°E
Luan	Anhui, China	China	31.76°N 116.3°E
Fuyang	Anhui, China	China	32.92°N 115.7°E
Huaibei	Anhui, China	China	33.73°N 116.7°E
Huainan	Anhui, China	China	32.76°N 116.7°E
Hefei	Anhui, China	China	31.79°N 117.3°E
Luan	Anhui, China	China	31.76°N 116.3°E
Fuyang	Anhui, China	China	32.92°N 115.7°E
Anqing	Anhui, China	China	30.61°N 116.6°E
Chizhou	Anhui, China	China	30.29°N 117.4°E
Bengbu	Anhui, China	China	33.11°N 117.3°E
—	Beijing, China	China	40.21°N 116.2°E
—	Beijing, China	China	40.21°N 116.2°E
—	Beijing, China	China	40.21°N 116.2°E
—	Beijing, China	China	39.64°N 116.4°E
—	Beijing, China	China	39.64°N 116.4°E
showing 1–20 of 41926

Out[]=

So, it seems that some of the entries are missing the City field, this may be due to the fact that they are associated with rural areas (or even non-land areas, e.g. cruise ships) or because the data is incomplete. The good thing is that we can get the precise GeoPosition data which we will use to identify the city/municipality associated with the case.

Next, let’s derive expressions we need to extract the insights we are interested in.

Outputs & Visualisation

Date

As discussed earlier, it’s important we indicate the date when the dataset was last updated:

coviddata[Max,#DateOfConfirmation&]

Day: Fri 13 Mar 2020

Out[]=

Case count

Given that our goal is to make a cloud applet that would allow a user to enter the name of a city and receive a number of confirmed cases in it, and that “City” column is missing a lot of values, we’ll need to find another way of finding a corresponding “City” value that’ll work for each case.
We can do this using GeoWithinQ function that returns True or False depending on whether the point (given by GeoPosition) is within a polygon of a given entity (in this case a City). So, we’ll check if the confirmed case occurred in the city based on its GeoPosition and do it for each row. We can reduce the amount of Geo-computation needed by first filtering all of the cases for the Country that contains the City and do the row-by-row check with GeoWithinQ.

Let’s assume we are querying the data for Chicago, IL:

EntityValue

Chicago

CITY

,"Country"

United States

Out[]=

Normal@QuerySelect#CountryEntityValue

Chicago

CITY

,"Country"&,"GeoPosition"@coviddata

GeoPosition41.8781,-87.6298,GeoPosition48.0482,-121.696,GeoPosition33.7033,-117.761,GeoPosition34.05,-118.25,GeoPosition33.4128,-111.943,GeoPosition41.8781,-87.6298,GeoPosition37.2317,-121.693,GeoPosition42.3601,-71.0589,GeoPosition37.2317,-121.693,GeoPosition36.6064,-121.074,

⋯1528⋯

,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678,GeoPosition37.3802,-119.678

large output

show less

show all

set size limit...

Out[]=

This gives us GeoPositions for every US case. Now, we need to check which ones belong to Chicago area.

CountGeoWithinQ

Chicago

CITY

,Normal@QuerySelect#CountryEntityValue

Chicago

CITY

,"Country"&,"GeoPosition"@coviddata,True

Out[]=

Perfect! So, we are able to find City values based on GeoPositions.

In terms of visualisation of results, we can construct a simple but effective display using rows and columns:

RasterizeRowColu