Roger Stringer   About ▾

I'm Roger Stringer: a father, writer, developer, consultant, chef, speaker. Founder of TheInterviewr.

Analyzing Olympic Medal Winners with YQL

 

I’m going to be writing a series of posts about YQL over the next month, mostly to share the cool things that can be done with it.

I’ve been using YQL for the past 4 years for various projects from straight aggregation sites, to data analysis, and on, and love it.

First off, for anyone who doesn’t know, YQL is the Yahoo! Query Language, which is an SQL-like query language that is designed to retrieve and manipulate data from APIs through a single Web interface, thus allowing mashups that enable developers to create their own applications.

Some of the cool things you can do with YQL is use a spreadsheet stored on google docs as your data to display searchable and sortable data on a web site.

For example, I posted on my google drive a spreadsheet containing all Summer Olympic Medal Winners since 1980, Now I can use YQL to read that spreadsheet as a CSV file and then use the data:

select * from csv where url="https://docs.google.com/spreadsheet/pub?key=0AmSmtp2dBZcYdFM0QXFmUW9CVmRYRkhYdlVRZEV1RWc&single=true&gid=0&output=csv" and columns="city,edition,sport,discipline,athlete,noc,gender,event,medal"

You can try this out in the YQL Console and see the results here.

Using YQL to filter and sort this, you can do some interesting searches on that information. For example:

Who won Gold for Canada in 1992?

select * from csv where url="https://docs.google.com/spreadsheet/pub?key=0AmSmtp2dBZcYdFM0QXFmUW9CVmRYRkhYdlVRZEV1RWc&single=true&gid=0&output=csv" and columns="city,edition,sport,discipline,athlete,noc,gender,event,medal" and noc="CAN" and edition="1992" and medal="Gold"

You can try this out in the YQL Console and see the results here.

Who’s won medals for the Canadian Boxing team since 1980?

Again, this is an example:

select * from csv where url="https://docs.google.com/spreadsheet/pub?key=0AmSmtp2dBZcYdFM0QXFmUW9CVmRYRkhYdlVRZEV1RWc&single=true&gid=0&output=csv" and columns="city,edition,sport,discipline,athlete,noc,gender,event,medal" and noc="CAN" and sport="Boxing"

You can try this out in the YQL Console and see the results here.

As you can, quite a bit can be done here.. You can also use YQL to read web pages in the same way, which we’ll get into next time when we get into YQL some more.

This data originally came from The Guardian and goes all the way back to 1896, but I wanted just grabbed the last 32 years of data for the sake of this test case.

Filed Under: Code, yql

Roger Stringer spends most of his time solving problems for people, and otherwise occupying himself with being a dad, cooking, speaking, learning, writing, reading, and the overall pursuit of life. He lives in Penticton, BC.

Connect: Twitter |  Google+ |  Buy Me A Cup of Coffee