Mixed Relay Data Analysis

With the Mixed Relay World Championships having taken place over the weekend in Hamburg it is worth noting that all Mixed Relay data is available in the Team API.

In order to demonstrate exploring the data available we will generate a medal table for all Federations as well as analysing the number of medals each athlete has earned. The Mixed Relay event has undergone a few changes over the years varying in format and number of athletes participating but the current standard dates back to 2009 and the Hy-Vee World Championships in that year.

Perhaps the quickest way to extract all the event_ids for all Mixed Relay World Championships would be to make a call to the Search API with a query string of Mixed Relay World Championships which returns as expected 8 results from years 2009 to 2016 inclusively.

curl --header "apikey: YOUR_APP_KEY" "https://api.triathlon.org/v1/search/events?query=Mixed%20Relay%20World%20Championships"

Formatting this output yields the following result:

We can immediately see from the data that the Mixed Relay World Championships have been held in 4 separate locations with Hamburg being the home of the event for the last 4 years.

Extracting National Federation Medal Winners

We would now like to know which National Federations have won medals at the Mixed Relay World Championships. To do so we will need to extract the results of the top 3 for each 4xMixed Relay program at each event. We will achieve this by using the event_id from the Search API to make a call to the Program Listings method which yields the prog_id for the associated program. Using the prog_id we simply make a call to Program Results to extract the final data. The following sample script will output the raw medal winner data as a delimited list that can be manipulated to achieve the desired visualization.

Running the script with php -f mixed-relay-medal-winners.php > mixed-relay-medal-winners.txt will output the following pipe delimited file that can be opened in standard tools (note you can of course use a comma as a delimiter but care must be taken where commas are included in the event title which may occur in rare circumstances).

Event Name | Event Date | Position | Country Name | Nat  
-------------------------------------------------------- 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 3 | Canada | CAN

Though we may write a script to parse this data we will simply import the raw data to Tableau to complete the analysis (you could of course do the same in Microsoft Excel).

The data shows that Great Britain are the most successful nation at the Mixed Relay World Championships with 3 victories and a bronze medal and the United States became the 5th Federation to win (and also the first outside of Europe) during their victory in the 2016 version of the event.

👍

The Tableau worksheets are available on Tableau Public

Download the worksheets from here

Extracting Athlete Medal Winners

All Mixed Relay results include a team_members object which is comprised of an array of basic athlete objects for members of the relay. Using this data we can simply extract in the same manner a list of all individual athlete medal winners at the Mixed Relay World Championships. The following sample script adds the additional logic and outputs the following:

Event Name | Event Date | Position | Country Name | Nat | Athlete ID | Athlete Title | Athlete Gender 
----------------------------------------------------------------------------------------------------- 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 5523 | Magali Di Marco | female 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 5657 | Ruedi Wild | male 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 7766 | Daniela Ryf | female 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 10935 | Lukas Salvisberg | male 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5526 | Emma Moffatt | female 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5591 | Courtney Atkinson | male 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5530 | Emma Snowsill | female 
2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5711 | Brad Kahlefeldt | male

Once again importing the data into Tableau we produce the following medal table for individuals which shows that 72 athletes have won medals and Jonathan Brownlee is the only athlete to have 3 victories in the event. Four athletes have won 3 medals at the event (Jonathan Brownlee, Ruedi Wild, Vicky Holland and Vincent Luis). A preview of the individual medal tally is shown below with the full data available in the Tableau workbook.

As all data is included in the team_members object such as individual split data a much larger variety of data analysis may be completed.