R, the Programmable Web, and Transparency in Social Science Research

Alois Stutzer and I recently contributed a guest post to the BITSS-Blog (of the Berkeley Initiative for Transparency in the Social Sciences). As a big part of it focuses on R-related topics, I figured it might also be of interest for readers of this blog. Here the gist:

"The replicability of social science research is becoming more demanding in the age of big data. First, researchers aiming to replicate a study based on massive data face substantial computational costs. Second, and probably more challenging, they are often confronted with “highly unique” data sets derived and compiled from sources with different and unusual formats (as they are originally generated and recorded for purposes other than data analysis or research). This holds in particular for Internet data from social media, new e-businesses, and digital government. More and more social scientists attempt to exploit these new data sources following ad hoc procedures in the compilation of their data sets. 
..."

The entire post can be found here.

In this context, I also want to explicitly point to all the very relevant contributions listed in the CRAN Task View on Web Technologies and Services,  the CRAN Open Data Task View, as well as the contributions by rOpenSci.

R and the web (for beginners), Part III: Scraping MPs' expenses in detail from the web

In this last post of my little series (see my latest post) on R and the web I explain how to extract data of a website (web scraping/screen scraping) with R. If the data you want to analyze are a part of a web page, for example a HTML-table (or hundreds of them) it might be very time-consuming (and boring!) to manually copy/paste all of its content or even typewrite it to a spreadsheet table or data frame. Instead, you can let R do the job for you!

This post is really aimed at beginners. Thus, to keep things simple it only deals with scraping one data table from one web page: a table published by BBC NEWS containing the full range of British Members of Parliament' expenses in 2007-2008. Quite an interesting data set if you are into political scandals...

Web scraping with R

There are several R packages that might be helpful for web scraping, such as XML, RCurl, and scrapeR. In this example only the XML package is used. As a fist step, you parse the whole HTML-file and extract all HTML-tables in it:

library(XML)

# URL of interest:
mps <- "http://news.bbc.co.uk/2/hi/uk_politics/8044207.stm" 

# parse the document for R representation:
mps.doc <- htmlParse(mps)

# get all the tables in mps.doc as data frames

mps.tabs <- readHTMLTable(mps.doc) 

mps.tabs is a list containing in each element a HTML-table from the parsed website (mps.doc) as data.frame. The website contains several HTML-tables (some are rather used to structure the website and not to present data). The list mps.tabs actually has seven entries, hence there were seven HTML-tables in the parsed document:

length(mps.tabs)

To proceed you need to check which of these data frames (list entries) contains the table you want (the MPs' expenses). You can do that "manually" by checking how the data frame starts and ends and compare it with the original table of the website:

head(mps.tabs[[1]])  #and

tail(mps.tabs[[1]])  #for 1 to 7

With only seven entries this is quite fast. But alternatively you could also write a little loop to do the job for you. The loop checks each data frame for certain conditions. In this case: the string of the first row and first column and the string in the last row and column. According to the original table from the website that should be:

first <- "Abbott, Ms Diane"

last <- "157,841"

# ... and the loop:

for (i in 1:length(mps.tabs)) {

 

  lastrow <- nrow(mps.tabs[[i]]) # get number of rows

  lastcol <- ncol(mps.tabs[[i]])

 

  if (as.character(mps.tabs[[i]][1,1])==first & as.character(mps.tabs[[i]][lastrow,lastcol])==last) {

   

    tabi <- i

     

    }

  }

Check if that is realy what you want and extract the relevant table as data frame.

head(mps.tabs[[tabi]])

tail(mps.tabs[[tabi]])

mps <- mps.tabs[[tabi]] 

Before you can properly analyze this data set we have to remove the commas in the columns with expenses and format them as numeric:

money <- sapply(mps[,-1:-3], FUN= function(x) as.numeric(gsub(",", "", as.character(x), fixed = TRUE) ))

mps2 <- cbind(mps[,1:3],money)

Now you are ready to go... For example, you could compare how the total expenses are distributed for each of the five biggest parties:

# which are the five biggest parties by # of mps?

nbig5 <- names(summary(mps2$Party)[order(summary(mps2$Party)*-1)][1:5])

#subset of mps only with the five biggest parties:

big5 <- subset(mps2, mps$Party%in%nbig5)

# load the lattice package for a nice plot

library(lattice)

bwplot(Total ~  Party, data=big5, ylab="Total expenses per MP (in £)")

Here is the resulting plot: 

 And the relevant R code in one piece:

library(XML)

# URL of interest:
mps <- "http://news.bbc.co.uk/2/hi/uk_politics/8044207.stm" 

# parse the document for R representation:
mps.doc <- htmlParse(mps)

# get all the tables in mps.doc as data frames

mps.tabs <- readHTMLTable(mps.doc)

# loop to find relevant table:

first <- "Abbott, Ms Diane"

last <- "157,841"

for (i in 1:length(mps.tabs)) {

 

  lastrow <- nrow(mps.tabs[[i]]) # get number of rows

  lastcol <- ncol(mps.tabs[[i]])

 

  if (as.character(mps.tabs[[i]][1,1])==first & as.character(mps.tabs[[i]][lastrow,lastcol])==last) {

   

    tabi <- i

     

    }

  }


# extract the relevant table and format it:

mps <- mps.tabs[[tabi]]  

money <- sapply(mps[,-1:-3], FUN= function(x) as.numeric(gsub(",", "", as.character(x), fixed = TRUE) ))

mps2 <- cbind(mps[,1:3],money)

# which are the five biggest parties by # of mps?

nbig5 <- names(summary(mps2$Party)[order(summary(mps2$Party)*-1)][1:5])

#subset of mps only with the five biggest parties:

big5 <- subset(mps2, mps$Party%in%nbig5)

# load the lattice package for a nice plot

library(lattice)

bwplot(Total ~  Party, data=big5, ylab="Total expenses per MP (in £)")

 More web scraping examples on r-bloggers.com

If you are interested in more web scraping with R, check out the following links to posts with more advanced/specific examples presented on r-bloggers.com:

http://www.r-bloggers.com/web-scraper-for-google-scholar-updated/ 

http://www.r-bloggers.com/web-scraping-in-r/

http://www.r-bloggers.com/r-web-scraping-r-bloggers-facebook-page-to-gain-further-information-about-an-authors%E2%80%99-r-blog-posts-e-g-number-of-likes-comments-shares-etc/

http://www.r-bloggers.com/web-scraping-yahoo-search-page-via-xpath/

http://www.r-bloggers.com/how-to-buy-a-used-car-with-r-part-1/ 

R and the web (for beginners), Part II: XML in R

This second post of my little series on R and the web deals with how to access and process XML-data with R. XML is a markup language that is commonly used to interchange data over the Internet. If you want to access some online data over a webpage's API you are likely to get it in XML format. So here is a very simple example of how to deal with XML in R.

Duncan Temple Lang wrote a very helpful R-package which makes it quite easy to parse, process and generate XML-data with R. I use that package in this example. The XML document (taken from w3schools.com) used in this example describes a fictive plant catalog. Not that thrilling, I know, but the goal of this post is not to analyze the given data but to show how to parse it and transform it to a data frame. The analysis is up to you...

How to parse/read this XML-document into R?

 

# install and load the necessary package

install.packages("XML")

library(XML)

# Save the URL of the xml file in a variable

xml.url <- "http://www.w3schools.com/xml/plant_catalog.xml"

# Use the xmlTreePares-function to parse xml file directly from the web

 

xmlfile <- xmlTreeParse(xml.url)

# the xml file is now saved as an object you can easily work with in R:

class(xmlfile)

# Use the xmlRoot-function to access the top node

xmltop = xmlRoot(xmlfile)

# have a look at the XML-code of the first subnodes:

print(xmltop)[1:2]

This should look more or less like:

$PLANT
<PLANT>
 <COMMON>Bloodroot</COMMON>
 <BOTANICAL>Sanguinaria canadensis</BOTANICAL>
 <ZONE>4</ZONE>
 <LIGHT>Mostly Shady</LIGHT>
 <PRICE>$2.44</PRICE>
 <AVAILABILITY>031599</AVAILABILITY>
</PLANT>

$PLANT
<PLANT>
 <COMMON>Columbine</COMMON>
 <BOTANICAL>Aquilegia canadensis</BOTANICAL>
 <ZONE>3</ZONE>
 <LIGHT>Mostly Shady</LIGHT>
 <PRICE>$9.37</PRICE>
 <AVAILABILITY>030699</AVAILABILITY>
</PLANT>

attr(,"class")
[1] "XMLNodeList"

One can already assume how this data should look like in a matrix or data frame. The goal is to extract the XML-values from each XML-tag <> for all $PLANT nodes and save them in a data frame with a row for each plant ($PLANT-node) and a column for each tag (variable) describing it. How can you do that?

# To extract the XML-values from the document, use xmlSApply:

plantcat <- xmlSApply(xmltop, function(x) xmlSApply(x, xmlValue))

# Finally, get the data in a data-frame and have a look at the first rows and columns

plantcat_df <- data.frame(t(plantcat),row.names=NULL)

plantcat_df[1:5,1:4]

The first rows and columns of that data frame should look like this:

               COMMON              BOTANICAL ZONE        LIGHT
1           Bloodroot Sanguinaria canadensis    4 Mostly Shady
2           Columbine   Aquilegia canadensis    3 Mostly Shady
3      Marsh Marigold       Caltha palustris    4 Mostly Sunny
4             Cowslip       Caltha palustris    4 Mostly Shady
5 Dutchman's-Breeches    Dicentra cucullaria    3 Mostly Shady

Which is exactly what we need to analyze this data in R.

R and the web (for beginners), Part I: How is the local nuclear plant doing?

One of the things I especially like about R is its ability to easily access and process data from the web. If you are new to R, or never have used it to access data from the Internet, here is the first part of a little series of posts with examples to get you started. This first post gives a very simple example of how to access a data set that is saved online.

This might be particularly useful if the data set at hand is frequently updated and you want to repeatedly generate a statistical report of some kind based on this data set. Hence, having the analysis and the link to the data in one R-script means you only have to rerun the script whenever you want to update your report or anybody else wants to reproduce it. The data I'm using for this example is exactly of that type. It's a file published by the United States Nuclear Regulatory Commission (U.S. NRC) reporting the power reactor status of U.S. nuclear power plants for the last 365 days, thus it is updated every day. 

How to access that data directly through R? 

# First: save the url of the data file as character string

url.npower <- "http://www.nrc.gov/reading-rm/doc-collections/event-status/reactor-status/PowerReactorStatusForLast365Days.txt"

# then read the data file into R (in this case the data file is a text file with "|" separating the columns) 

npower <- read.table(url.npower, sep="|", header=TRUE)

# and format the date column

npower$ReportDt <- as.Date(npower$ReportDt, format="%m/%d/%Y")

The data set is now ready for analysis. For example: a graphical analysis of the recent power reactor status of some of the nuclear power plants:

# load the necessary lattice package

# (if it isn't installed yet, run: install.packages("lattice")

library(lattice)

# take a subset of the data

sample <- npower[npower$Unit==as.character(unique(npower$Unit)[1:24]),]

# get a graphical overview

xyplot(Power~ReportDt | Unit, data=sample, type="l",col.line="black", xlab="Time",ylab="Power" )

Save the code above in an R-script, rerun it some days later, and your graphical analysis will be up to date.