OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets

Another Olympics, so another chance to look at how to construct a real time medal map using the tricks and techniques that are available to us today, cf. the approach I took in 2008. In this post, I’ll show how to create a medal map using Wikipedia data and Google spreadsheet query functions in Google Apps Standard Edition (this will also work in Google Apps for Edu, Business and so on, but not (currently) in the ‘everyday’ edition of Google spreadsheets that you get with a personal Google account. Why not? Because I make use of Google Apps Script, that’s why…)

To start with, let’s get some data. Wikipedia is surprisingly good as a near realtime data source for large sporting events, so let’s see what’s on the Winter Olympics 2010 medals page:

We can pull this data into a Google spreadsheet using the =importHTML() formula:

***NOTE – THE PAGE LAYOUT HAS CHANGED AND WITH IT TABLE NUMBER. THE FORMULA SHOULD NOW READ:
=ImportHtml(“http://en.wikipedia.org/wiki/2010_Winter_Olympics_medal_table”,”table”,2) ***
[Update: bah – back to table 3 again…oh, the joys or arbitrary HTML scraping!)

In order to plot medals in a “heat map” using the Google heatmap widget:

we need to provide two columns of data: an ISO country code and the number value we want to associate with that country code.

Looking at the medals table we have imported, we see that we have country name and a non-ISO country code.

Let’s start by extracting the country name using a Google Apps Script (tutorials). These scripts are user defined formulae that you can create and then use in the same way that you would use any of the normally provided spreadsheet formulae. Open the script editor from the spreadsheet Tools menu:

Write your function(s):

then call them in the spreadsheet:

The next step is to get the ISO country code for each country. I’m going to do this using a QUERY formula that interrogates a list of ISO country codes in another sheet (see Using Google Spreadsheets Like a Database – The QUERY Formula for more on the Google Spreadsheets QUERY function):

At its heart, a particular QUERY will look something like this:

=QUERY('ISO Country Codes'!A2:B268,"select A,B where A contains 'FRANCE' limit 1")))

The limit 1 ensures we only return a single result.

To construct the QUERY from a country name specisifed in a cell, we embed a CONCATENATE formula to construct the select string:

=QUERY('ISO Country Codes'!A2:B268,concatenate("select A,B where A contains '",upper(H2),"' limit 1")))

The contains operator is case sensitive, so as we have country names in all upper case in our ISO sheet, we convert the country name pulled in from the medal table to upper case as well. (I couldn’t get the more appropriate matches expression to work?)

So now we can get a column of country names (again) along with the corresponding ISO country code:

(The IF statement is there to keep things tidy.)

If we now copy across medal data into a column alongside the coutnry code column, we can create a heat map widget:

This widget is also available as a Google gadget:

Although there is some latency in the system, as changes are made to the original WIkipedia page, the Google spreadsheet should occasionally check it as and when the spreadsheet – or the heat map – is viewed.

So there you have it, a real time Medal Map, with data pulled into a Google spreadsheet from a Wikipedia page using an importHTML function, tidied using a Google Apps script function, used to key a QUERY in an ISO codes spreadsheet/database, and then rendered via a third party Google gadget.

Overall hack time – about half an hour, first time round. Time to write this post – over an hour…

PS see also Data Scraping Wikipedia with Google Spreadsheets

Written by Tony Hirst

February 15, 2010 at 11:48 am

Posted in Tinkering

Tagged with

9 Responses

Subscribe to comments with RSS.

  1. Overall hack time – about half an hour, first time round. Time to write this post – over an hour…

    It’s much appreciated :) Writing this kind of stuff up, particularly the way you do it, is immensely valuable for lurkers like me. Thanks.

    Steph Gray

    February 15, 2010 at 1:33 pm

  2. [...] Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets « OUseful.Info, the blog… (tags: mmj04 scraping tonyhirst spreadsheets data visualisation) [...]

  3. [...] PPPS Once you have scraped the data into a Google spreadsheet, it’s possible to treat it as a database using the QUERY spreadsheet function. For more on the QUERY function, see Using Google Spreadsheets Like a Database – The QUERY Formula and Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets. [...]

  4. [...] Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets « OUseful.Info, the blog… (tags: google docs spreadsheet data howto olympics vancouver onlinenewspapers) [...]

  5. [...] Posts Creating a Winter Olympics 2010 Medal Map In Google SpreadsheetsWhy Private Browsing Isn't…Using Google Spreadsheets as a Database with the Google Visualisation [...]

  6. Ditto, thanks for the very nice post. I’m glad I found this even though it’s not what I was looking for, because I just learned a couple of super useful things about Google Spreadsheets. Now, if only I could find a post on how to link a polygon in a KML file to a value in a Google spreadsheet, but I will continue my search!

    Sally

    April 5, 2010 at 11:26 am

  7. [...] Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets « OUseful.Info, the blog… Also for #onlinejournalism students: Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets http://bit.ly/97FVmm (tags: onlinejournalism via:packrati.us) Possibly related posts: (automatically generated)links for 2010-05-14 [...]

  8. [...] Om Google nu een mooie World Link Map te laten maken op basis van de gegevens, moet je de ISO landencodes toevoegen in deze nieuwe kolom. Dit is misschien niet het leukste gedeelte van de hele klus, maar het toevoegen van een nieuwe functie die het toevoegen van de ISO landcodes staat al bij Majestic SEO op de to-do lijst Tot die tijd zul je het moeten doen met de suggestie om gebruik te maken van de “Query Formule“. [...]

  9. [...] an example to demonstrate importHtml. Rather than reinventing the wheel I thought I’ve revisit Tony Hirst’s Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets (hmm are we allowed to use the word ‘Olympic’ in the same sentence as Google as they are not an [...]


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 820 other followers

%d bloggers like this: