Wolfram Research @ the Illinois Marathon

Jeff Bryant, Eila Stiegler

Join the Conversation #WolframTechConf

Background: Running @ Wolfram Research

Many of us like to run. We are everywhere, and all the time. 
Runkeeper, Strava

Why run/race?

Health ... yada, yada ... 

Maintain some level of sanity.

Location-independent.

Runners are a friendly bunch of people!

And have you seen the race signs? Hilarious, often politically incorrect, and R-rated!

2015

2016: The 26.2 Blog ... turned EntityStore

2017: Sponsorship ... but why?

The Illinois Marathon ties together CU

... and has evolved into a staple community event

dataset=Dataset[{
<|""->"Runners","2009"->"9,700","2017"->"18,000"|>,
<|""->"Events","2009"->"5 over 1 day","2017"->"9 over 2 days"|>,
<|""->"Volunteers","2009"->"1,800","2017"->"2,700"|>,
<|""->"Sponsors","2009"->"17","2017"->"71"|>,
<|""->"Hotel Partners","2009"->"0","2017"->"24"|>,
<|""->"Economic Impact","2009"->"$4.3 million","2017"->"$10 million"|>}]

Charitable Giving 2009 - 2017: $1.2 million

Runners covered 198,025 miles ...

WolframAlpha["198,025 miles",{{"ComparisonAsDistance",1},"Content"}]

WolframAlpha["how many soccer fields stretch 198025 miles"]

... used 80,000 safety pins for bibs, ...

WolframAlpha["safety pin",{{"BasicInformation:InventionData",1},"Content"}]

... ate 1600 oranges, and 15000 bananas; drank 9600 gallons of water, and 
1920 gallons of Gatorade along the race course ...

WolframAlpha["sugar in 1,600 oranges",{{"Result",1},"Content"}]

WolframAlpha["How many oranges fit in a bathtub?",{{"ApproximateResult",1},"Content"}]

... finished 800 lbs of pasta, 1100 pizzas, and 32,600 bottles of water at the finish line ...

WolframAlpha["calories in 800 lbs of pasta and 1100 pizzas",{{"Result",1},"Content"}]

... and were greeted with 70 sheet cakes to celebrate the U of I’s 150th birthday.

One phone call to the Meijer bakery ...

cakeCals=70  Quantity[21340,"LargeCalories"]

{WolframAlpha[ToString[cakeCals],{{"Comparison",1},"Content"}],WolframAlpha[ToString[cakeCals],{{"Comparison",2},"Content"}]}//Column

Comparable to:

WolframAlpha["calories in 15,000 bananas",{{"Result",1},"Content"}]

What to do with those calories?

WolframAlpha["Calories burned running at pace 9 min/mi for 26.2 miles",IncludePods->"MetabolicProperties",AppearanceElements->{"Pods"}]

It'll take ~500 marathons to burn just the sheet cake calories. 

But, guilt-free:

marathonCals=LinguisticAssistant

marathonCals/LinguisticAssistant

N[marathonCals/(cakeCals/(70*108))]

... to replenish the calories you spent running the marathon.

Did I mention weather?

April 30, 2016 was horrible:

{WolframAlpha["Weather Champaign 4/30/2016",{{"WeatherCharts:WeatherData",2},"Content"}],WolframAlpha["Weather Champaign 4/30/2016",{{"WeatherCharts:WeatherData",3},"Content"}]}//Column

But not Champaign' s low point:

The Mess of Real-world Data

Real-world data often starts out as textual data, such as in a comma separated value (CSV) file format.

raw=Import["C:\\Users\\jeffb\\Desktop\\Marathon_Results_Modified.csv", "Numeric"->False];

We can see the raw table descriptions as follows:

header=raw[[1]]

It’s more convenient to represent the raw data as key->value pairs:

fullmarathon=AssociationThread[header->#]&/@Rest[raw];

fullmarathon[[1;;3]]

Cleaning up Data

Some of the raw text data is fine to be left alone, such as names. But other data is messy and inconsistent such as cities, states, countries, and gender, all of which can be aligned with entities in the Wolfram Language. This not only allows for a consistent representation, but gives access to all of the data in the Wolfram Knowledgebase for those items if desired later.

Making use of Interpreter

Interpreter is your friend. It allows you to parse and arbitrary string as a particular entity type and is often the first step in trying to align data. Let’s show how to align the location information. This is particularly tricky since the location information may not be what you expect. The CITY field could contain neighborhoods or typos making an automatic interpretation impossible so we need to be prepared for other contingencies.

allLocations2017=Union[{"CITY","STATE","COUNTRY"}/.fullmarathon];

With the CITY, STATE, and COUNTRY pulled out, we can use Interpreter to attempt alignment such as in this case:

Interpreter["City"][StringJoin[StringRiffle[{"Ballwin","MO","United States"}]]]

But data can be messy. Not all provided cities are actually cities, or perhaps the linguistics for these locations isn’t complete, and so Interpreter, on its own, isn’t able to align such items.

Interpreter["City"][StringJoin[StringRiffle[{"Scott Air Force Base","IL","United States"}]]]

We can fall back to a contingency in these cases by making use of the provided postal codes.

allZips2017=Union[{"POSTAL_CODE","COUNTRY"}/.fullmarathon];

With[{loc=Interpreter["Location"]["62225"]},
GeoNearest["City",loc]]

So, often we need to make multiple passes to clean up the data. First with a direct Interpreter approach to use CITY, then, for failure cases, try making use of the postal code and find the nearest city to that location. Even this may require some manual alignment, for example subdivisions (rather than cities) outside the U.S. which don’t auto-align to cities, but don’t have a useful postal code that works with Interpreter.

Processing Splits

The Christie Clinic 2017 marathons recognized 6 different splits: Start, 10K, 10M, 1/2, 20M, Finish. For each split, we can extract the net time, time of day, and the pace. This should be really straight forward because these are just number separated by colons. We want to represent these as Wolfram Language Quantity objects.

Quantity[MixedMagnitude[FromDigits/@StringSplit["2:34:15",":"]],MixedUnit[{"Hours","Minutes","Seconds"}]]

But, once again, messy real-world data is messy and inconsistent. Sometimes, the times reported are not for the full marathon, but for 5K runners, etc. And their numbers are not hours, minutes, seconds, but minutes, seconds, milliseconds. So logic must be put in to verify the values before returning the final Quantity objects.

Quantity[MixedMagnitude[FromDigits/@StringSplit["25:6:34",":"]],MixedUnit[{"Minutes","Seconds","Milliseconds"}]]

Assembling the Data

Once the data has been cleaned up, it’s just a matter of creating an Association of key->value pairs. An example piece of data for one runner shows the structure:

\[LeftAssociation]"BibNumber"->1245,"Event"->"Marathon","LastName"->"Strain","FirstName"->"Walter","Name"->"Walter Strain","Label"->"Walter Strain (1245)","City"->(Entity["Riley", Entity["City",{"Riley","Indiana","UnitedStates"}], "Entity[\"City\", {\"Riley\", \"Indiana\", \"UnitedStates\"}]", "city"]),"State"->(Entity["Indiana, United States", Entity["AdministrativeDivision",{"Indiana","UnitedStates"}], "Entity[\"AdministrativeDivision\", {\"Indiana\", \"UnitedStates\"}]", "administrative division"]),"Country"->(Entity["United States", Entity["Country","UnitedStates"], "Entity[\"Country\", \"UnitedStates\"]", "country"]),"ZIP"->(Entity["47846", Entity["ZIPCode","47846"], "Entity[\"ZIPCode\", \"47846\"]", "ZIP Code"]),"ChristieClinic2017Division"->Entity["ChristieClinic2017Division","Male50To54"],"Gender"->(Entity["male", Entity["Gender","Male"], "Entity[\"Gender\", \"Male\"]", "gender"]),"PlaceDivision"->45,"PlaceGender"->471,"PlaceOverall"->679,"Splits"->\[LeftAssociation]"Start"->\[LeftAssociation]"NetTime"->(QuantityMixedUnit3[0, 0, 0.`,  "h",  "min",  "s", hours,minutes,seconds, {"Hours","Minutes","Seconds"}]),"TimeOfDay"->(DateObject["Sat 22 Apr 2017 07:06:03""GMT"-5.`, DateObject[{2017,4,22,7,6,3.`},"Instant","Gregorian",-5.`]]),"Pace"->Missing["NotApplicable"]\[RightAssociation],"10K"->\[LeftAssociation]"NetTime"->(QuantityMixedUnit3[56, 35, 0,  "min",  "s",  "ms", minutes,seconds,milliseconds, {"Minutes","Seconds","Milliseconds"}]),"TimeOfDay"->(DateObject["Sat 22 Apr 2017 08:02:38""GMT"-5.`, DateObject[{2017,4,22,8,2,38.`},"Instant","Gregorian",-5.`]]),"Pace"->(Quantity[9.1062048`, "min""/""mi", minutes per mile, (("Minutes")/("Miles"))])\[RightAssociation],"10M"->\[LeftAssociation]"NetTime"->(QuantityMixedUnit3[1, 32, 41,  "h",  "min",  "s", hours,minutes,seconds, {"Hours","Minutes","Seconds"}]),"TimeOfDay"->(DateObject["Sat 22 Apr 2017 08:38:44""GMT"-5.`, DateObject[{2017,4,22,8,38,44.`},"Instant","Gregorian",-5.`]]),"Pace"->(Quantity[9.268333333333333`, "min""/""mi", minutes per mile, (("Minutes")/("Miles"))])\[RightAssociation],"Half"->\[LeftAssociation]"NetTime"->(QuantityMixedUnit3[2, 2, 20,  "h",  "min",  "s", hours,minutes,seconds, {"Hours","Minutes","Seconds"}]),"TimeOfDay"->(DateObject["Sat 22 Apr 2017 09:08:23""GMT"-5.`, DateObject[{2017,4,22,9,8,23.`},"Instant","Gregorian",-5.`]]),"Pace"->(Quantity[9.331741485958053`, "min""/""mi", minutes per mile, (("Minutes")/("Miles"))])\[RightAssociation],"20M"->\[LeftAssociation]"NetTime"->(QuantityMixedUnit3[3, 11, 48,  "h",  "min",  "s", hours,minutes,seconds, {"Hours","Minutes","Seconds"}]),"TimeOfDay"->(DateObject["Sat 22 Apr 2017 10:17:51""GMT"-5.`, DateObject[{2017,4,22,10,17,51.`},"Instant","Gregorian",-5.`]]),"Pace"->(Quantity[9.589999999999998`, "min""/""mi", minutes per mile, (("Minutes")/("Miles"))])\[RightAssociation],"Finish"->\[LeftAssociation]"NetTime"->(QuantityMixedUnit3[4, 19, 26,  "h",  "min",  "s", hours,minutes,seconds, {"Hours","Minutes","Seconds"}]),"TimeOfDay"->(DateObject["Sat 22 Apr 2017 11:25:29""GMT"-5.`, DateObject[{2017,4,22,11,25,29.`},"Instant","Gregorian",-5.`]]),"Pace"->(Quantity[9.894951496622824`, "min""/""mi", minutes per mile, (("Minutes")/("Miles"))])\[RightAssociation]\[RightAssociation]\[RightAssociation]

Assembling Divisions

The divisions recognized by the Christie Clinic 2017 marathon included:

{"Female19AndUnder","Female20To24","Female25To29","Female30To34","Female35To39","Female40To44","Female45To49","Female50To54","Female55To59","Female60To64","Female65To69","Female70To74","Male19AndUnder","Male20To24","Male25To29","Male30To34","Male35To39","Male40To44","Male45To49","Male50To54","Male55To59","Male60To64","Male65To69","Male70To74","Male75To79","Male80AndOver","FemaleOverall","FemaleMaster","MaleOverall","MaleMaster"}

For each division, the following properties are available:

{"mean net time","min net time","max net time","Boston qualifying standard","beat Boston qualifying standard","count beat Boston qualifying standard","within range Boston qualifying standard","count within range Boston qualifying standard","outside range Boston qualifying standard","count outside range Boston qualifying standard"}

Populating the data for these divisions was simply a matter of combining GroupBy and Select and then performing various statistics on the results. Results for a given division had structure like the following:

"Female19AndUnder"->\[LeftAssociation]"Mean"->(Quantity[15723, "s", seconds, "Seconds"]),"Min"->(Quantity[12221, "s", seconds, "Seconds"]),"Max"->(Quantity[17997, "s", seconds, "Seconds"]),"BQStandard"->(Quantity[12900, "s", seconds, "Seconds"]),"BeatBQ"->{(Entity["Jayza Riley (1836)", Entity["ChristieClinicMarathon2017","1836"], "Entity[\"ChristieClinicMarathon2017\", \"1836\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[12221, "s", seconds, "Seconds"])},"NumberBeat"->1,"RangeBQ"->{(Entity["Alyssa Klunk (44)", Entity["ChristieClinicMarathon2017","44"], "Entity[\"ChristieClinicMarathon2017\", \"44\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[13374, "s", seconds, "Seconds"]),(Entity["Margaret McClowry (1169)", Entity["ChristieClinicMarathon2017","1169"], "Entity[\"ChristieClinicMarathon2017\", \"1169\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[14664, "s", seconds, "Seconds"]),(Entity["Olivia Draminski (832)", Entity["ChristieClinicMarathon2017","832"], "Entity[\"ChristieClinicMarathon2017\", \"832\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[14087, "s", seconds, "Seconds"])},"NumberRange"->3,"OutsideBQ"->{(Entity["Joycelyn VanAntwerp (206)", Entity["ChristieClinicMarathon2017","206"], "Entity[\"ChristieClinicMarathon2017\", \"206\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[14746, "s", seconds, "Seconds"]),(Entity["Katherine Hayek (324)", Entity["ChristieClinicMarathon2017","324"], "Entity[\"ChristieClinicMarathon2017\", \"324\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[17647, "s", seconds, "Seconds"]),(Entity["Lexie Dorsey (520)", Entity["ChristieClinicMarathon2017","520"], "Entity[\"ChristieClinicMarathon2017\", \"520\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[15308, "s", seconds, "Seconds"]),(Entity["Julia Welle (69)", Entity["ChristieClinicMarathon2017","69"], "Entity[\"ChristieClinicMarathon2017\", \"69\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[15402, "s", seconds, "Seconds"]),(Entity["Cathie Seebauer (548)", Entity["ChristieClinicMarathon2017","548"], "Entity[\"ChristieClinicMarathon2017\", \"548\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[16280, "s", seconds, "Seconds"]),(Entity["Stephanie Hedgespeth (1130)", Entity["ChristieClinicMarathon2017","1130"], "Entity[\"ChristieClinicMarathon2017\", \"1130\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[17329, "s", seconds, "Seconds"]),(Entity["Belle Snyder (238)", Entity["ChristieClinicMarathon2017","238"], "Entity[\"ChristieClinicMarathon2017\", \"238\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[17995, "s", seconds, "Seconds"]),(Entity["Elizabeth Snyder (237)", Entity["ChristieClinicMarathon2017","237"], "Entity[\"ChristieClinicMarathon2017\", \"237\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[17997, "s", seconds, "Seconds"]),(Entity["Jamie Watson (424)", Entity["ChristieClinicMarathon2017","424"], "Entity[\"ChristieClinicMarathon2017\", \"424\"]", "Christie Clinic Marathon 2017 participant"])->(Quantity[17345, "s", seconds, "Seconds"])},"NumberOutside"->9\[RightAssociation]

Assembling an EntityStore

With the data cleaned up and processed, its now a simple matter to construct an EntityStore so that the data can be used in the EntityValue framework in the Wolfram Language. It’s mainly just a matter of attaching metadata to the properties so that they have display-friendly labels.

EntityStore[
{"ChristieClinicMarathon2017"-><|
"Label"->"Christie Clinic Marathon 2017 participant",
"LabelPlural"->"Christie Clinic Marathon 2017 participants",
"Entities"->processed,
"Properties"->\[LeftAssociation]
"BibNumber"->\[LeftAssociation]"Label"->"bib number"\[RightAssociation],
"Event"->\[LeftAssociation]"Label"->"event"\[RightAssociation],
"LastName"->\[LeftAssociation]"Label"->"last name"\[RightAssociation],
"FirstName"->\[LeftAssociation]"Label"->"first name"\[RightAssociation],
"Name"->\[LeftAssociation]"Label"->"name"\[RightAssociation],
"Label"->\[LeftAssociation]"Label"->"label"\[RightAssociation],
"City"->\[LeftAssociation]"Label"->"city"\[RightAssociation],
"State"->\[LeftAssociation]"Label"->"state"\[RightAssociation],
"Country"->\[LeftAssociation]"Label"->"country"\[RightAssociation],
"ZIP"-><|"Label"->"ZIP"|>,
"ChristieClinic2017Division"->\[LeftAssociation]"Label"->"Christie Clinic 2017 division"\[RightAssociation],
"Gender"->\[LeftAssociation]"Label"->"gender"\[RightAssociation],
"PlaceDivision"->\[LeftAssociation]"Label"->"place division"\[RightAssociation],
"PlaceGender"->\[LeftAssociation]"Label"->"place gender"\[RightAssociation],
"PlaceOverall"->\[LeftAssociation]"Label"->"place overall"\[RightAssociation],
"Splits"->\[LeftAssociation]"Label"->"splits"\[RightAssociation]\[RightAssociation]
|>,
"ChristieClinic2017Division"-><|"Label"->"Christie Clinic 2017 division",
"LabelPlural"->"Christie Clinic 2017 divisions",
"Entities"->divTypeEntities,
"Properties"->\[LeftAssociation]"Label"->\[LeftAssociation]"Label"->"label"\[RightAssociation],"Mean"->\[LeftAssociation]"Label"->"mean net time"\[RightAssociation],"Min"->\[LeftAssociation]"Label"->"min net time"\[RightAssociation],"Max"->\[LeftAssociation]"Label"->"max net time"\[RightAssociation],"BQStandard"->\[LeftAssociation]"Label"->"Boston qualifying standard"\[RightAssociation],"BeatBQ"->\[LeftAssociation]"Label"->"beat Boston qualifying standard"\[RightAssociation],"NumberBeat"->\[LeftAssociation]"Label"->"count beat Boston qualifying standard"\[RightAssociation],
"RangeBQ"->\[LeftAssociation]"Label"->"within range Boston qualifying standard"\[RightAssociation],"NumberRange"->\[LeftAssociation]"Label"->"count within range Boston qualifying standard"\[RightAssociation],
"OutsideBQ"->\[LeftAssociation]"Label"->"outside range Boston qualifying standard"\[RightAssociation],"NumberOutside"->\[LeftAssociation]"Label"->"count outside range Boston qualifying standard"\[RightAssociation]\[RightAssociation]
|>}]

More Information on Data Cleaning

Stephen Wolfram blog post: Launching the Wolfram Data Repository: Data Publishing that Really Works