Analysing the history of Winter Olympics medals with YQL
Thursday, February 18th, 2010I am a big fan of the Guardian Data Blog which releases all kind of cool datasets used in their research for people to mash up. One of the recent data sets was the Statistics of Winter Olympics medals over the years.
I’ve taken the Excel sheet and exported it as a CSV. Then I created a YQL open table to make it easier to use and filter the information.
Using this table you can now get the statistics of the Winter Olympics in terms of medals from 1924 up to 2006 (the 2010 data is of course not in there yet). Try it out for yourself:
Get all medal information:
use “http://isithackday.com/wintermedals.xml” as medals;
select * from medals
see it as XML or in the YQL console
UK Gold medals (small dataset):
use “http://isithackday.com/wintermedals.xml” as medals;
select * from medals where country=”gbr” and type=”gold”
see it as XML or in the YQL console
All Skating medals in the Speed Skating discipline won by men
use “http://isithackday.com/wintermedals.xml” as medals;
select * from medals where sport=”skating”
and discipline=”speed skating”
and gender=”m”
see it as XML or in the YQL console
All US, Canadian and French Medals in the Games before 2000
use “http://isithackday.com/wintermedals.xml” as medals;
select * from medals where country in
(‘usa’,’can’,’fra’) and year=”19”
see it as XML or in the YQL console
All the things you can filter with
Basically you can get the medal information and you can filter your research with the following criteria:
- year – the year of the olympics
- city – the city it was held in, like “Lillehammer” or “Sarajevo”
- sport – the sport
- discipline – the sub-discipline
- country – the country as an NOC code
- event – the event, like “alpine combined” or “two-man”
- gender – the athlete’s gender, X is for pair sports
- type – the medal type (Gold, Silver, Bronze)
Any of these could also be used as a wildcard, so country="g"
would find GBR, FRG, GDR, YUG and GER.
Have fun!