{"_id":"578e4dd02c3c790e00937563","user":{"_id":"546d17e2eb9cfd1400dd4529","username":"","name":"World Triathlon"},"initVersion":{"_id":"55773a5ba042551900b002ce","version":"1"},"project":"55773a5ba042551900b002cb","__v":0,"createdAt":"2016-07-19T15:57:04.490Z","changelog":[],"body":"With the Mixed Relay World Championships [having taken place over the weekend in Hamburg](http://www.triathlon.org/results/result/2016_hamburg_itu_triathlon_mixed_relay_world_championships/281319) it is worth noting that all Mixed Relay data is available in the [Team API](https://developers.triathlon.org/docs/team-api-overview).\n\nIn 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](http://www.triathlon.org/triathlon_mixed_relay/results_event/2009_hy-vee_itu_triathlon_team_world_championships) in that year. \n\nPerhaps 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](https://developers.triathlon.org/docs/search-results) with a query string of `Mixed Relay World Championships` which returns as expected 8 results from years 2009 to 2016 inclusively.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"curl --header \\\"apikey: YOUR_APP_KEY\\\" \\\"https://api.triathlon.org/v1/search/events?query=Mixed%20Relay%20World%20Championships\\\"\",\n      \"language\": \"curl\"\n    }\n  ]\n}\n[/block]\nFormatting this output yields the following result:\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Year\",\n    \"h-1\": \"Event ID\",\n    \"h-2\": \"Event Title\",\n    \"0-0\": \"2009\",\n    \"1-0\": \"2010\",\n    \"2-0\": \"2011\",\n    \"3-0\": \"2012\",\n    \"4-0\": \"2013\",\n    \"5-0\": \"2014\",\n    \"6-0\": \"2015\",\n    \"7-0\": \"2016\",\n    \"0-1\": \"5261\",\n    \"0-2\": \"2009 Hy-Vee ITU Triathlon Mixed Relay World Championships\",\n    \"1-1\": \"36951\",\n    \"1-2\": \"2010 Lausanne ITU Triathlon Mixed Relay World Championships\",\n    \"2-1\": \"45688\",\n    \"2-2\": \"2011 Lausanne ITU Triathlon Mixed Relay World Championships\",\n    \"3-1\": \"55945\",\n    \"3-2\": \"2012 Stockholm ITU Triathlon Mixed Relay World Championships\",\n    \"4-1\": \"66638\",\n    \"4-2\": \"2013 Hamburg ITU Triathlon Mixed Relay World Championships\",\n    \"5-1\": \"79407\",\n    \"5-2\": \"2014 Hamburg ITU Triathlon Mixed Relay World Championships\",\n    \"6-1\": \"91030\",\n    \"6-2\": \"2015 Hamburg ITU Triathlon Mixed Relay World Championships\",\n    \"7-1\": \"97646\",\n    \"7-2\": \"2016 Hamburg ITU Triathlon Mixed Relay World Championships\"\n  },\n  \"cols\": 3,\n  \"rows\": 8\n}\n[/block]\nWe 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.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Extracting National Federation Medal Winners\"\n}\n[/block]\nWe 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](https://developers.triathlon.org/docs/program-listings) method which yields the `prog_id` for the associated program. Using the `prog_id` we simply make a call to [Program Results](https://developers.triathlon.org/docs/program-results) to extract the final data. The following [sample script](https://gist.github.com/garethtdavies/ea2b5642c3a47f3c0d3a8006e7b928dd) will output the raw medal winner data as a delimited list that can be manipulated to achieve the desired visualization.\n\nRunning 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).\n\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"Event Name | Event Date | Position | Country Name | Nat  \\n-------------------------------------------------------- \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 3 | Canada | CAN \",\n      \"language\": \"text\",\n      \"name\": \"Sample output\"\n    }\n  ]\n}\n[/block]\nThough we may write a script to parse this data we will simply import the raw data to [Tableau](http://www.tableau.com/) to complete the analysis (you could of course do the same in Microsoft Excel).\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/b777c78-Mixed_Relay_Medal_Table.png\",\n        \"Mixed Relay Medal Table.png\",\n        429,\n        221,\n        \"#f3f3f2\"\n      ],\n      \"caption\": \"Mixed Relay Medal Tally\",\n      \"sizing\": \"smart\",\n      \"border\": true\n    }\n  ]\n}\n[/block]\nThe 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.\n[block:callout]\n{\n  \"type\": \"success\",\n  \"title\": \"The Tableau worksheets are available on Tableau Public\",\n  \"body\": \"Download the worksheets from [here](https://public.tableau.com/profile/world.triathlon#!/vizhome/MixedRelayMedalTally/MixedRelayMedalTable)\"\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Extracting Athlete Medal Winners\"\n}\n[/block]\nAll Mixed Relay results include a `team_members` object which is comprised of an array of [basic athlete objects](https://developers.triathlon.org/docs/athletes-api-overview#basic-athlete-object) 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](https://gist.github.com/garethtdavies/8ee04a3826c3dcb140e5bd1ac2f90da6) adds the additional logic and outputs the following:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"Event Name | Event Date | Position | Country Name | Nat | Athlete ID | Athlete Title | Athlete Gender \\n----------------------------------------------------------------------------------------------------- \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 5523 | Magali Di Marco | female \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 5657 | Ruedi Wild | male \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 7766 | Daniela Ryf | female \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 10935 | Lukas Salvisberg | male \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5526 | Emma Moffatt | female \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5591 | Courtney Atkinson | male \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5530 | Emma Snowsill | female \\n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5711 | Brad Kahlefeldt | male \",\n      \"language\": \"text\",\n      \"name\": \"Sample Output\"\n    }\n  ]\n}\n[/block]\nOnce 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.\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/ea98945-individual-medal-winners.png\",\n        \"individual-medal-winners.png\",\n        750,\n        295,\n        \"#ededec\"\n      ],\n      \"caption\": \"Individual Mixed Relay Medal Tally\"\n    }\n  ]\n}\n[/block]\nAs all data is included in the `team_members` object such as individual split data a much larger variety of data analysis may be completed.","slug":"mixed-relay-data-analysis","title":"Mixed Relay Data Analysis"}

Mixed Relay Data Analysis


With the Mixed Relay World Championships [having taken place over the weekend in Hamburg](http://www.triathlon.org/results/result/2016_hamburg_itu_triathlon_mixed_relay_world_championships/281319) it is worth noting that all Mixed Relay data is available in the [Team API](https://developers.triathlon.org/docs/team-api-overview). 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](http://www.triathlon.org/triathlon_mixed_relay/results_event/2009_hy-vee_itu_triathlon_team_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](https://developers.triathlon.org/docs/search-results) with a query string of `Mixed Relay World Championships` which returns as expected 8 results from years 2009 to 2016 inclusively. [block:code] { "codes": [ { "code": "curl --header \"apikey: YOUR_APP_KEY\" \"https://api.triathlon.org/v1/search/events?query=Mixed%20Relay%20World%20Championships\"", "language": "curl" } ] } [/block] Formatting this output yields the following result: [block:parameters] { "data": { "h-0": "Year", "h-1": "Event ID", "h-2": "Event Title", "0-0": "2009", "1-0": "2010", "2-0": "2011", "3-0": "2012", "4-0": "2013", "5-0": "2014", "6-0": "2015", "7-0": "2016", "0-1": "5261", "0-2": "2009 Hy-Vee ITU Triathlon Mixed Relay World Championships", "1-1": "36951", "1-2": "2010 Lausanne ITU Triathlon Mixed Relay World Championships", "2-1": "45688", "2-2": "2011 Lausanne ITU Triathlon Mixed Relay World Championships", "3-1": "55945", "3-2": "2012 Stockholm ITU Triathlon Mixed Relay World Championships", "4-1": "66638", "4-2": "2013 Hamburg ITU Triathlon Mixed Relay World Championships", "5-1": "79407", "5-2": "2014 Hamburg ITU Triathlon Mixed Relay World Championships", "6-1": "91030", "6-2": "2015 Hamburg ITU Triathlon Mixed Relay World Championships", "7-1": "97646", "7-2": "2016 Hamburg ITU Triathlon Mixed Relay World Championships" }, "cols": 3, "rows": 8 } [/block] 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. [block:api-header] { "type": "basic", "title": "Extracting National Federation Medal Winners" } [/block] 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](https://developers.triathlon.org/docs/program-listings) method which yields the `prog_id` for the associated program. Using the `prog_id` we simply make a call to [Program Results](https://developers.triathlon.org/docs/program-results) to extract the final data. The following [sample script](https://gist.github.com/garethtdavies/ea2b5642c3a47f3c0d3a8006e7b928dd) 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). [block:code] { "codes": [ { "code": "Event Name | Event Date | Position | Country Name | Nat \n-------------------------------------------------------- \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 3 | Canada | CAN ", "language": "text", "name": "Sample output" } ] } [/block] Though we may write a script to parse this data we will simply import the raw data to [Tableau](http://www.tableau.com/) to complete the analysis (you could of course do the same in Microsoft Excel). [block:image] { "images": [ { "image": [ "https://files.readme.io/b777c78-Mixed_Relay_Medal_Table.png", "Mixed Relay Medal Table.png", 429, 221, "#f3f3f2" ], "caption": "Mixed Relay Medal Tally", "sizing": "smart", "border": true } ] } [/block] 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. [block:callout] { "type": "success", "title": "The Tableau worksheets are available on Tableau Public", "body": "Download the worksheets from [here](https://public.tableau.com/profile/world.triathlon#!/vizhome/MixedRelayMedalTally/MixedRelayMedalTable)" } [/block] [block:api-header] { "type": "basic", "title": "Extracting Athlete Medal Winners" } [/block] All Mixed Relay results include a `team_members` object which is comprised of an array of [basic athlete objects](https://developers.triathlon.org/docs/athletes-api-overview#basic-athlete-object) 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](https://gist.github.com/garethtdavies/8ee04a3826c3dcb140e5bd1ac2f90da6) adds the additional logic and outputs the following: [block:code] { "codes": [ { "code": "Event Name | Event Date | Position | Country Name | Nat | Athlete ID | Athlete Title | Athlete Gender \n----------------------------------------------------------------------------------------------------- \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 5523 | Magali Di Marco | female \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 5657 | Ruedi Wild | male \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 7766 | Daniela Ryf | female \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 1 | Switzerland | SUI | 10935 | Lukas Salvisberg | male \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5526 | Emma Moffatt | female \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5591 | Courtney Atkinson | male \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5530 | Emma Snowsill | female \n2009 Hy-Vee ITU Triathlon Mixed Relay World Championships | 2009-06-28 | 2 | Australia | AUS | 5711 | Brad Kahlefeldt | male ", "language": "text", "name": "Sample Output" } ] } [/block] 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. [block:image] { "images": [ { "image": [ "https://files.readme.io/ea98945-individual-medal-winners.png", "individual-medal-winners.png", 750, 295, "#ededec" ], "caption": "Individual Mixed Relay Medal Tally" } ] } [/block] 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.