Live Google Spreadsheet For Keeping Track Of Sochi Medals
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
The “medals” R post by TRInker and re-blogged by Revolutions 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 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.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.