Christian Heilmann

Posts Tagged ‘guardian’

Analyzing the FIFA2010 worldcup with Guardian Data and YQL

Wednesday, July 14th, 2010

Breaking news: The Guardian once again involved in committing a data awesome! As before, the UK newspaper graced developers with a really cool piece of information published on the web: all the World Cup 2010 statistics as an Excel Sheet.

Now, the easiest way to play with this data is to use YQL, so I simply took a copy of the information and shared it as a CSV document on Google Docs. That way I can use it in YQL:

select * from csv where url=”http://spreadsheets.google.com/pub?
key=0AhphLklK1Ve4dEdrWC1YcjVKN0ZRbTlHQUhaWXBKdGc&single=true&gid=1&x=1&
output=csv” and columns=”surname,team,position,time,shots,passes,tackles,saves”

You can Try this out in the console and see the results here.

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

What were the German mid field Players?

select * from csv where url=”http://spreadsheets.google.com/pub?
key=0AhphLklK1Ve4dEdrWC1YcjVKN0ZRbTlHQUhaWXBKdGc&single=true&gid=1&x=1&
output=csv” and columns=”surname,team,position,time,shots,passes,tackles,saves”
and team=”Germany” and position=”Midfielder”

You can Try this out in the console and see the results here.

Using sort() and reverse() you can do rankings. For example:

Who was the goalkeeper with the most saves?

(Neuner of Germany, Kingson of Ghana and Enyeama of Nigeria in case you wonder)

select * from csv where url=”http://spreadsheets.google.com/pub?
key=0AhphLklK1Ve4dEdrWC1YcjVKN0ZRbTlHQUhaWXBKdGc&single=true&gid=1&x=1&
output=csv” and
columns=”surname,team,position,time,shots,passes,tackles,saves”
and position=”Goalkeeper” | sort(field=”saves”) | reverse()

You can Try this out in the console and see the results here.

Which was the player that spent most time on the pitch?

select * from csv where url=”http://spreadsheets.google.com/pub?
key=0AhphLklK1Ve4dEdrWC1YcjVKN0ZRbTlHQUhaWXBKdGc&single=true&gid=1&
output=csv” and
columns=”surname,team,position,time,shots,passes,tackles,saves”
| sort(field=”time”) | reverse()

You can Try this out in the console and see the results here.

Who were the players who were the least on the pitch in the German and Brazilian teams?

select * from csv where url=”http://spreadsheets.google.com/pub?
key=0AhphLklK1Ve4dEdrWC1YcjVKN0ZRbTlHQUhaWXBKdGc&single=true&gid=1&x=1&
output=csv” and columns=”surname,team,position,time,shots,passes,tackles,saves”
and team in (“Germany”,”Brazil”) | sort(field=”time”)

You can Try this out in the console and see the results here.

Using the CSV output and YQL you can do all kind of cool things with that data – as YQL also releases it as JSON it makes it easy to create interactive interfaces and visualizations, too – why don’t you have a go?

TTMMHTM: Google delivers, Guardian delivers and some more developer goodies

Friday, May 21st, 2010

Damn, life is good as a developer right now.

** We finally have an open video format with WebM which is a container based on Matroska with VP8 as the video and Vorbis for audio. It is backed by Opera, Mozilla and Google amongst other huge players. IE9 says it will support it with a plugin and Safari seems to be supporting it via a Quicktime plugin.

** Google released a library to make it easier to embed web fonts and also host quite a bunch of fonts for easy embedding – preliminary reports by friends of mine state that they look amazing on Mac but very bac on PC - can you verify this?

** Google Latitude now has an API but it also shows some problems

** Google also released BigQuery which is a REST API to analyse data in Google Storage. One of the uses they showed is the Prediction API which does natural language analysis and could become a contender for Open Calais.

** AppEngine got some good updates including a Comet API

** Android got a major update – it now runs the superfast V8 engine, you will have access to accelerometer and camera with a JavaScript API and you can send applications to the phone from computers via the cloud. Makes me want to kill the guy who dropped my Nexus One and hope HTC will be quick in repairing it.

TTMMHTM: Public data explorer, good weather, dogs with taches, and automated Twitter to delicious bookmarking

Monday, March 8th, 2010

Things that made me happy this morning:

I just arrived in Atlanta for Georgia Tech University hack day and the weather is awesome. I spent the day in the sun in cafes writing my slides for the Mix10 conference next week and now I am going through my feeds. So time for another TTMMHTM:

TTMMHTM – BBC Web animals, two very cool APIs and there’s something about the LG logo

Tuesday, February 23rd, 2010

Things that made me happy this morning:

TTMMHTTM: Twitter coaching, twitter backlash, MP expenses crowdsourced, YQL for data.gov, colour checking and Pixar win

Friday, June 19th, 2009

Things that made me happy this morning: