The “medals” R post by [TRInker](http://trinkerrstuff.wordpress.com/2014/02/09/sochi-olympic-medals-2/) and re-blogged by [Revolutions](http://blog.revolutionanalytics.com/2014/02/winter-olympic-medal-standings-presented-by-r.html) were both spiffy and a live example why there’s no point in not publishing raw data.
You don’t need to have R (or any other language) do the scraping, though. The “`IMPORTHTML`” function (yes, function names seem to be ALL CAPS now over at Google Drive) in Google Drive Spreadsheets can easily do the scraping with just s simple:
=IMPORTHTML("http://www.sochi2014.com/en/medal-standings","table",0)that will refresh on demand and every hour.
Here’s a [live URL](https://docs.google.com/spreadsheets/d/1Al7I7nS0BP50IfThs55OKv5UPI9u-ctZgZRyDQma_G8/export?format=csv&gid=0) that will give back a CSV of the results which can easily be used in R thusly:
library(RCurl) sochi.medals.URL = "https://docs.google.com/spreadsheets/d/1Al7I7nS0BP50IfThs55OKv5UPI9u-ctZgZRyDQma_G8/export?format=csv&gid=0" medals <- read.csv(textConnection(getURL(sochi.medals.URL)), stringsAsFactors = FALSE) str(medals) 'data.frame': 89 obs. of 6 variables: $ Rank : chr "1" "2" "3" "4" ... $ Country: chr "Norway" "Canada" "Netherlands" "United States" ... $ Gold : int 4 4 3 2 2 1 1 1 1 1 ... $ Silver : int 3 3 2 1 0 3 2 0 0 0 ... $ Bronze : int 4 2 3 3 0 3 0 1 0 0 ... $ Total : int 11 9 8 6 2 7 3 2 1 1 ... print(medals) Rank Country Gold Silver Bronze Total 1 1 Norway 4 3 4 11 2 2 Canada 4 3 2 9 3 3 Netherlands 3 2 3 8 4 4 United States 2 1 3 6 5 5 Germany 2 0 0 2 6 6 Russian Fed. 1 3 3 7 7 7 Austria 1 2 0 3 8 8 France 1 0 1 2 9 =9 Belarus 1 0 0 1 10 =9 Korea 1 0 0 1 11 =9 Poland 1 0 0 1 12 =9 Slovakia 1 0 0 1 13 =9 Switzerland 1 0 0 1 14 14 Sweden 0 3 1 4 15 15 Czech Republic 0 2 1 3 16 16 Slovenia 0 1 2 3 17 17 Italy 0 1 1 2 18 =18 China 0 1 0 1 19 =18 Finland 0 1 0 1 20 =20 Great Britain 0 0 1 1 21 =20 Ukraine 0 0 1 1 22 - Albania 0 0 0 0 23 - Andorra 0 0 0 0 24 - Argentina 0 0 0 0 25 - Armenia 0 0 0 0 ... 87 - Virgin Isl, B 0 0 0 0 88 - Virgin Isl, US 0 0 0 0 89 - Zimbabwe 0 0 0 0
Which frees you up from dealing with the scraping and lets you focus solely on the data.
You can set it up in your own Google Docs as well, just make sure to publish the spreadhseet to the web (with ‘everyone’ read permisssions), strip off the `pubhtml` at the end of the published URL and add `export?format=csv&gid=0` in its place.
4 Comments
Interesting. Good to learn about the IMPORTHTML function!
Very nice. It seems that syntax of Google functions depends on localization settings.
With my Google Drive settings following is working:
ImportHtml(“http://www.sochi2014.com/en/medal-standings”;”table”;0)
Should be 0 not o
ImportHtml(“http://www.sochi2014.com/en/medal-standings”;”table”;0)
Good to know! Thanks for sharing!
2 Trackbacks/Pingbacks
[…] article was first published on rud.is » R, and kindly contributed to […]
[…] the Google spreadsheet data-munging “hack” from the previous post in a Shiny […]