Structuring Diverse Data Sources for Machine Learning and Analysis

Maureen Baehr and Ben Kickert
StartingUpGood

ABSTRACT

What do you do when the data you need comes from various sources such as online content, relational databases, EntityStores and a variety of different flat tables? In this presentation, we will explore how diverse data sources with divergent structures and formats can be brought together for unified analysis. Skills covered will include:

Importing structured and unstructured data

Data cleaning and structuring techniques

Using Wolfram Entities to supplement existing data

Connecting to relational databases such as SQL through Wolfram

Using APIs and JSON to generate usable tables

Using WebSession to effectively scrape content

Joining diverse content

Implementing Machine Learning to supplement and analyze your data

This session will focus on data related to the Sustainable Development goals but will be applicable to a wide range of fields.

Working with Diverse Data

Objective of this presentation: use various health data sources to predict life expectancy of countries.

Example data sources and formats related to global health:

Wolfram Entities - Country Data

SQLite Database - CIA Factbook

CSV Data - UN SDG Database

API / JSON - WHO Global Health Observations

Web Scraping - SDG Index

Essential steps to combining data:

Choose a final format you wish to work in.  For this example we will move everything to Dataset

Identify one or more primary keys that can be used to bridge the data sources.  We will work with country codes today.

Wolfram Data Repository and Entities

Power of Wolfram Entities

While there are many tools and programming languages one can use for data analytics, one key strength of Mathematica is the availability of curated data.  Within the Wolfram Repository there are nearly 300 types.  CountryData alone has over 750 properties to explore.  We will start with a few properties to easily identify the countries and also some properties related to health:

Creating a dataset from Entities

Find relevant Entity Properties

Shallow[EntityProperties["Country"]]

entityprops={(EntityProperty["country code", EntityProperty["Country","CountryCode"], "EntityProperty[\"Country\", \"CountryCode\"]"]),(EntityProperty["UN code", EntityProperty["Country","UNCode"], "EntityProperty[\"Country\", \"UNCode\"]"]),(EntityProperty["name", EntityProperty["Country","Name"], "EntityProperty[\"Country\", \"Name\"]"]),(EntityProperty["Gini index", EntityProperty["Country","GiniIndex"], "EntityProperty[\"Country\", \"GiniIndex\"]"]),(EntityProperty["infant mortality fraction", EntityProperty["Country","InfantMortalityFraction"], "EntityProperty[\"Country\", \"InfantMortalityFraction\"]"]),(EntityProperty["HIV/AIDS fraction", EntityProperty["Country","HIVAIDSFraction"], "EntityProperty[\"Country\", \"HIVAIDSFraction\"]"]),(EntityProperty["GDP", EntityProperty["Country","GDP"], "EntityProperty[\"Country\", \"GDP\"]"]),(EntityProperty["population", EntityProperty["Country","Population"], "EntityProperty[\"Country\", \"Population\"]"]),(EntityProperty["life expectancy", EntityProperty["Country","LifeExpectancy"], "EntityProperty[\"Country\", \"LifeExpectancy\"]"])};

Although documented, PerCapita GDP is not available as part of the Country Entity values.  (It is available in CountryData)

EntityProperty["Country","Population"]
EntityProperty["Country","GDPPerCapita"]

So we can created an Extended Entity Class for this.  This is one of many new features that came to Entities in V12.

wolframentities=ExtendedEntityClass[EntityClass["Country","Countries"],"PerCapitaGDP"->EntityFunction[p,If[MissingQ[p["GDP"]],Missing["NotAvailable"],p["GDP"]/p["Population"]]]];

Then turn the information into a dataset

wolframds=Values[EntityValue[wolframentities,{(EntityProperty["country code", EntityProperty["Country","CountryCode"], "EntityProperty[\"Country\", \"CountryCode\"]"]),(EntityProperty["UN code", EntityProperty["Country","UNCode"], "EntityProperty[\"Country\", \"UNCode\"]"]),(EntityProperty["name", EntityProperty["Country","Name"], "EntityProperty[\"Country\", \"Name\"]"]),(EntityProperty["Gini index", EntityProperty["Country","GiniIndex"], "EntityProperty[\"Country\", \"GiniIndex\"]"]),(EntityProperty["infant mortality fraction", EntityProperty["Country","InfantMortalityFraction"], "EntityProperty[\"Country\", \"InfantMortalityFraction\"]"]),(EntityProperty["HIV/AIDS fraction", EntityProperty["Country","HIVAIDSFraction"], "EntityProperty[\"Country\", \"HIVAIDSFraction\"]"]),(EntityProperty["life expectancy", EntityProperty["Country","LifeExpectancy"], "EntityProperty[\"Country\", \"LifeExpectancy\"]"]),(EntityPropertyWithIcon["PerCapitaGDP", EntityProperty[ExtendedEntityClass[EntityClass["Country","Countries"],"PerCapitaGDP"->EntityFunction[p,If[MissingQ[p["GDP"]],Missing["NotAvailable"],((p["GDP"])/(p["Population"]))]]],"PerCapitaGDP"], "EntityProperty[ExtendedEntityClass[EntityClass[\"Country\", \"Countries\"], \"PerCapitaGDP\" -> EntityFunction[p, If[MissingQ[p[\"GDP\"]], Missing[\"NotAvailable\"], p[\"GDP\"]/p[\"Population\"]]]], \"PerCapitaGDP\"]", Extended])},"Dataset"]]

Cleaning the Dataset

Mathematica uses the EntityProperty as the Key for the dataset resulting in more complexity in referencing your data and also resulting in less readable formatting.

Keys[First@wolframds]//Normal

We can correct for this with a KeyMap that takes the existing keys and points only to the EntityProperty name.

wolframds2=wolframds[All,KeyMap[#/.Thread[Normal[Keys[First[wolframds]]]->Last/@Normal[Keys[First[wolframds]]]]&,#]&]

Keys[First@wolframds2]//Normal

We also want to use QuantityMagnitude to normalize all the data to only integers.

quantityclean[input_]:=If[MissingQ[input],Missing["NotAvailable"],QuantityMagnitude[input]]
finalwolframds=wolframds2[All,{"InfantMortalityFraction"->quantityclean,"HIVAIDSFraction"->quantityclean,"PerCapitaGDP"->quantityclean,"LifeExpectancy"->quantityclean}]

This Dataset can then function as the base upon which we add additional information.

SQLite Database to EntityStore

Using the new Database tools found in Mathematica 12, we can follow a similar process for working with a SQL database using Entity Tools.  The following example utilizes a SQLite database of the CIA Fact Book that has been made available on GitHub, but could also work by connecting directly to a server.

Download the data, create an EntityStore and extract a Dataset

Download the database from https://github.com/factbook/factbook.sql

URLDownload["https://github.com/factbook/factbook.sql/releases/download/v0.1/factbook.db",Directory[]<>"\\factbook.db"];

Identify it as a Relational Database

ciafacts=RelationalDatabase[File["factbook.db"]]

Now we can bring that database in as an EntityStore and register it.

EntityRegister[EntityStore[{"facts"},ciafacts]]

EntityStores[]

EntityProperties["facts"]

There are countless options for ways you can manipulate a RelationalDatabase to include relevant information and all of that is possible in Mathematica.  However, for this example, we are going to simply create a dataset from the EntityStore

sqlds=EntityValue[EntityList["facts"],EntityProperties["facts"],"Dataset"]//Values

Cleaning the Dataset

Just like with Entities, we need to clean up the keys.  We can also drop a few keys so we only have the data we want to include in our final steps.  And, we will adjust the CountryCode to be upper case and have it match the same key as our first Dataset.

finalsqlds=KeyDrop[sqlds[All,KeyMap[#/.Thread[Normal[Keys[sqlds//First]]->Last/@Normal[Keys[First[sqlds]]]]&,#]&],{"id","name","area","area_land","area_water","population","created_at","updated_at"}][All,{"code"->ToUpperCase}][All,KeyMap[#/."code"->"CountryCode"&,#]&]

At this point we can join the two datasets.  I am using an Outer Join so we can preserve all the information in both tables.  Depending on your emphasis on data completeness versus data cleanliness, you may choose to do a Left or Inner Join.

combinedds1=JoinAcross[Normal@finalwolframds,Normal@finalsqlds,Key["CountryCode"],"Outer"]//Dataset

Here we can see that the CIA Factbook has data on entities not in Wolfram’s Country Data.  This demonstrates the power of supplement and expanding original sources with other sources.

Importing CSV Data

In many fields, data is still commonly stored in spreadsheets or CSV files.  The following is a small download from the UN SDG Database available at https://unstats.un.org/sdgs/indicators/database/ 
It covers 8 indicators over the last 3 years.

Bringing in tables

rawsdg=Import["https://www.wolframcloud.com/obj/ben.kickert/sdgdata.csv"]

SemanticImport can be used for simple dataset creation.

SemanticImport["https://www.wolframcloud.com/obj/ben.kickert/sdgdata.csv"]

While this is an easy approach, it also can result in some missing or incorrectly registered data.  Therefore, we most frequently create our own dataset through an AssociationThread pure function

sdgds=Dataset[AssociationThread[First[rawsdg]->#]&/@Rest[rawsdg]]

Clean and organize the data

Clean up the dates for time series that have a range by using StringSplit and adjust for non-number entries

datecleaner[input_]:=If[IntegerQ[input],input,ToExpression[Last[StringSplit[input,"-"]]]]
sdgds=sdgds[All,{"Time_Detail"->datecleaner}]/."<2.5"->1

Query to find most recent results

mostrecentsdgds=sdgds[GroupBy[#[["GeoAreaName"]]&],GroupBy[#[["SeriesDescription"]]&],Last,#[["Value"]]&]

Rearrange results and fill in the gaps with KeyUnion.

sdgds2=SortBy[Join[Dataset[AssociationThread["Country"->#]&/@Normal[Keys[mostrecentsdgds]]],KeyUnion[Values[mostrecentsdgds]],2],"Country"]

Create a primary key and join the datasets

We only have country names and no country codes, but we can use the Interpreter Function to find that information.  This approach can be used in a wide variety of fields to supplement or normalize data from the nearly 300 Entity types in Wolfram curated data.

codes=Dataset[<|"Country"->#,"CountryCode"->Interpreter["Country"][#]["CountryCode"]|>&/@Normal[sdgds2[[All,1]]]]

We are using this to calculate a primary key to Join data, but it also be used by itself to supplement data.  If you started with a non Wolfram source, you could easily use this to add Entity Properties from the Repository to the

Now we can Join these codes to the original dataset.  We will drop datapoints that do not have country codes because in this case they are made up of larger regions.

finalsdgds=KeyDrop[Dataset[JoinAcross[Normal@DeleteMissing[codes,1,2],Normal@sdgds2,Key["Country"],"Left"]],"Country"]

And then join it to the growing compiled dataset

combinedds2=JoinAcross[Normal@combinedds1,Normal@finalsdgds,Key["CountryCode"],"Outer"]//Dataset

API / JSON in

A significant source of world health data is the Global Health Observations database from the World Health Organization and is available via an API with results being returned in several different formats.  In this case we will work with JSON due to the ability to easily convert results into a Dataset by specifying “RawJSON” in the import phase.

Importing Data

Start by importing all of the meta data for the database.  Information on the GHO can be found here: https://apps.who.int/gho/data/node.resources.api?lang=en

meta=Import["http://apps.who.int/gho/athena/api/GHO?format=json","RawJSON"]//Dataset

And then extract the indicators.

fullindicators=meta[["dimension",All,"code",All,{"label","display"}]]//First

Find mortality related indicators

Select[fullindicators,StringContainsQ[#display,"mortality",IgnoreCase->True]&]

We can also import a full list of countries

country=Import["http://apps.who.int/gho/athena/api/COUNTRY?format=json","RawJSON"]//Dataset

And create a country list

countrylist=country["dimension",All,"code",All,{"label","display"}]//First

With the information we extracted above, we can use the API to pull specific health information.  The code below show all Under 5 Mortality stats for both sexes in 2017 from Afghanistan.  If you were regularly utilizing API calls, you could use Wolfram to automate the entire process.

Import["http://apps.who.int/gho/athena/api/GHO/MDG_0000000007.json?filter=COUNTRY:AFG;YEAR:2017;SEX:BTSX","RawJSON"]//Dataset

Now we can bring in all the same data but for all countries.

allu5mortality=Import["http://apps.who.int/gho/athena/api/GHO/MDG_0000000007.json?filter=YEAR:2017;SEX:BTSX;COUNTRY:*","RawJSON"]//Dataset

Cleaning and structuring

Extracting only country code and values for child mortality.

dim[input_]:=Select[input,#category=="COUNTRY"&][[1,2]]
value[input_]:=input[["numeric"]]
whods=allu5mortality["fact",All,{"Dim","value"}][All,{"Dim"->dim,"value"->value}]

And change the KeyName to match our CountryCode

finalwhods=whods[All,KeyMap[#/.{"Dim"->"UNCode","value"->"Under 5 Mortality"}&,#]&]

And add the information to our master dataset

combinedds3=JoinAcross[Normal@combinedds2,Normal@finalwhods,Key["UNCode"],"Outer"]//Dataset

Web Scraping SDGIndex

The WebSession functions built into Mathematica V12 allow for much more efficient and simple scraping of data from websites.  Here we will look at a scrape from a dashboard style website found at https://dashboards.sdgindex.org/#/  that displays overall scores for countries based on their SDG progress.

Sample Scrape

Start a WebSession

session=StartWebSession["Chrome"]

.

Open a page, find the items labeled as “item total” then select the relevant options.

WebExecute["OpenPage"->"https://dashboards.sdgindex.org/#/USA"];

WebExecute["ElementText"->WebExecute[session,"LocateElements"->"CSSSelector"->"div.item-total"]][[1;;4]]

Full Scrape

SDG Index uses ISO country codes just like the WHO Index, so we can use that information set up a pure function to get it all.

countryids=combinedds3[[All,"UNCode"]]//Normal//DeleteMissing//DeleteDuplicates

Now we can run a pure function to open the page of each country and scrape the four elements we are interested in.

fullscrape=Quiet[Prepend[WebExecute[{"OpenPage"->"https://dashboards.sdgindex.org/#/"<>#,"ElementText"->WebExecute[session,"LocateElements"->"CSSSelector"->"div.item-total"]}][[2,1;;4]],#]&/@countryids];
Shallow@fullscrape

Add the keys

indexkeys={"UNCode","SDG Global Rank","Global Index Score","Regional Average Score","GDP"};

Create the dataset

Using the data we have gathered, we can now format it to be included in our master dataset.  The GDP since we already have that data.

sdgindexds=KeyDrop[Dataset[AssociationThread[indexkeys->#]&/@fullscrape],"GDP"]

Now we can create our final combined dataset paying attention to how missing data is displayed.

finalcombinedds=Dataset[JoinAcross[Normal@combinedds3,Normal@sdgindexds,Key["UNCode"],"Outer"]]/.{""->Missing["NotAvailable"],"-"->Missing["NotAvailable"],"NA"->Missing["NotAvailable"],Missing[__]->Missing["NotAvailable"]}

Data backup

Save the full data for future use

CloudPut[finalcombinedds,CloudObject["finalcombinedds"],Permissions->"Public"]

finalcombinedds=CloudGet["https://www.wolframcloud.com/obj/ben.kickert/finalcombinedds"]

DumpSave["finalcombinedds.mx",finalcombinedds];

Get["finalcombinedds.mx"]

finalcombinedds

Assessing Data Quality and Structuring for Machine Learning

While there are approximately 195 countries in the world, we have gathered data on 341 entities.  This discrepancy is primarily due to inclusion of territories and regions in some of the data sources.  While we want as many examples as possible, for our work in machine learning we need the most accurate data we can get.

Start by looking at how many values we have for each entry.

KeySort[Counts[Length[DeleteMissing[Normal[#]]]&/@finalcombinedds]]//BarChart

To improve our chances of having a good prediction, let’s limit our work to only countries with at least 10 datapoints and that have a life expectancy listed.

topresults=Select[finalcombinedds,MissingQ[#LifeExpectancy]==False&&Length[DeleteMissing[Normal[#[[4;;]]]]]≥10&]

Now we need to create data that we can use in machine learning applications.  For our example, we want to use all the data we have predict life expectancy.  So we will thread together all of the other numbers pointing to the life expectancy.

aidata=Values[KeyDrop[#,{"CountryCode","UNCode","Name","LifeExpectancy"}]]->#[["LifeExpectancy"]]&/@Normal[topresults];
First@aidata

Create training data leaving at least 50 examples to test on

trainingdata=RandomSample[aidata,138];

Separate out the test data

testdata=Complement[aidata,trainingdata];

Using the Predict Function

Once the data is structured, the code to create a predictor is surprisingly simple.

predict=Predict[trainingdata,Method->Automatic,PerformanceGoal->"Quality"]

Check the details

Information[predict]

Then use the test data to create a comparison plot

PredictorMeasurements[predict,testdata,"ComparisonPlot"]

While the automatic method generally produces good results, it can be helpful to visually compare the results of various approaches.  The following code runs several methods and displays the results.

Column[(predictcompare=Predict[trainingdata,PerformanceGoal->"Quality",Method->#];
Grid[{{Rotate[Style[#,{FontWeight->Bold,FontSize->30}],.5Pi],Information[predictcompare],PredictorMeasurements[predictcompare,testdata,"ComparisonPlot"]}}])&/@{"DecisionTree","GradientBoostedTrees","LinearRegression","NearestNeighbors","RandomForest","GaussianProcess"}]

Here we can see that RandomForest produces results with the lowest standard deviation and a comparison plot that provides good estimates even at high and low values.

Adjusting the inputs for accuracy

Using the basic code above, you can quickly adjust the inputs for your predictor

Say for instance you only want to look at income data

aidata2=Values[#[[{"GiniIndex","PerCapitaGDP","Proportion of population below international poverty line (%)"}]]]->#[["LifeExpectancy"]]&/@Normal[topresults];
trainingdata2=RandomSample[aidata2,138];
testdata2=Complement[aidata2,trainingdata2];

Then run the Predict function

predict2=Predict[trainingdata2,Method->Automatic,PerformanceGoal->"Quality"]

Check the details

Information[predict2]
PredictorMeasurements[predict2,testdata2,"ComparisonPlot"]

Or we can just look at health data

aidata3=Values[#[[{"InfantMortalityFraction","HIVAIDSFraction","Number of new HIV infections per 1,000 population, by sex and age (per 1,000 uninfected population)","Tuberculosis incidence (per 100,000 population)"}]]]->#[["LifeExpectancy"]]&/@Normal[topresults];
trainingdata3=RandomSample[aidata3,138];
testdata3=Complement[aidata3,trainingdata3];
predict3=Predict[trainingdata3,Method->Automatic,PerformanceGoal->"Quality"];
Information[predict3]
PredictorMeasurements[predict3,testdata3,"ComparisonPlot"]

Since all of the data from all 5 sources has already been compiled in place, you can evaluate which indicators are most important to accuracy.

Using these Methods

Bringing together data sources in different formats has it’s own usefulness: Compare data sources for accuracy, add depth to an existing source (Wolfram data can be used for more than just adding Country Codes), create tables from data that is formatted differently (websites, APIs, Entities).  Automate API queries.

Predict function allows you fill in information with best guesses.

Classify could be used in a similar way to help categorize (Social Objectives in Impact Investing)

Create expanded Wolfram Entities by adding your own data from other sources

Thank You!

Maureen, Connette, Benji, Ben

SDGCounting and StartingUpGood Team