This weekend I was bored and thought I'd use one of the awesome data sets the Guardian released lately for free use: a list of all the medals won at the Winter Olympics from 1924 onwards. Using this, with YQL and YUI, I built winterolympicsmedals.com, a research interface that allows you to check the history of Winter Olympics medals by country, year, sport, and games:
The data was released by the Guardian as an excel sheet on Google Docs, so the first step for me was to download the information and save it as a CSV file. Originally I started using PHP to convert the CSV and parse it, but then I realized it made more sense to use YQL to allow myself to access and filter the data in the CSV in a more flexible way.
However, if you use YQL to parse the CSV you get not very useful identifiers back. If you run the following request in YQL:
select * from csv where url="http://winterolympicsmedals.com/medals.csv"
What you will get back is row/col data:
<?xml version="1.0" encoding="UTF-8"?> <query ...> <diagnostics...> </diagnostics> <results> <row> <col0>Year</col0> <col1>City</col1> <col2>Sport</col2> <col3>Discipline</col3> <col4>NOC</col4> <col5>Event</col5> <col6>Event gender</col6> <col7>Medal</col7> </row> <row> <col0>1924</col0> <col1>Chamonix</col1> <col2>Skating</col2> <col3>Figure skating</col3> <col4>AUT</col4> <col5>individual</col5> <col6>M</col6> <col7>Silver</col7> </row>
As this was not quite useful to me for parameters, I put together an open table to convert these to more meaningful XML tags. Using this one like so:
use "http://winterolympicsmedals.com/wintermedals.xml" as medals; select * from medals;
<?xml version="1.0" encoding="UTF-8"?> <query...> <diagnostics...> </diagnostics> <results> <medals> <medal> <year>Year</year> <city>City</city> <sport>Sport</sport> <discipline>Discipline</discipline> <country>NOC</country> <event>Event</event> <gender>Event gender</gender> <type>Medal</type> </medal> <medal> <year>1924</year> <city>Chamonix</city> <sport>Skating</sport> <discipline>Figure skating</discipline> <country>AUT</country> <event>individual</event> <gender>M</gender> <type>Silver</type> </medal> </medals> </results>
This allows me to run lookups in YQL:
select * from medals where country="gbr" and type="bronze"- gives you all UK bronze medals
select * from medals where year="1992" and type="bronze" and sport="skiing"- gets me all bronze medals in Albertville 1992 in the skiing category
select * from medals where country in ('gdr','frg') and type = "gold"- West and East German gold medals combined
Cool, eh? Now, using this with some YUI3 magic, a lick of CSS, and the ever so handy YUI2 datatable, putting together WinterOlympicsMedals.com was quite easy.
Yahoo Developer Network