Posts Tagged ‘open table’

Analysing the history of Winter Olympics medals with YQL

Thursday, February 18th, 2010

I 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 "" as medals;
select * from medals

see it as XML or in the YQL console

UK Gold medals (small dataset):

use "" 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 "" 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 "" 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!