Scraping in R - Revision history

Rb: /* Get your reference spreadsheet */

2015-11-06T15:22:44Z

‎Get your reference spreadsheet

Rb at 12:06, 6 November 2015

2015-11-06T12:06:56Z

Rb: /* Merging data */

2015-11-05T14:57:56Z

‎Merging data

Rb at 14:54, 5 November 2015

2015-11-05T14:54:23Z

Rb: /* The challenge */

2015-11-04T10:58:08Z

‎The challenge

Rb: Created page with "= The challenge = The internet is a very rich source of data and here we demonstrate how to tap it. well, at least we provide an introduction into how this can be done if the..."

2015-11-04T10:57:47Z

Created page with "= The challenge = The internet is a very rich source of data and here we demonstrate how to tap it. well, at least we provide an introduction into how this can be done if the..."

New page

= The challenge =

The internet is a very rich source of data and here we demonstrate how to tap it. well, at least we provide an introduction into how this can be done if the data are structured very nicely.

The example we shall use here is the following. Schools that educate Children from families with low income receive an extra amount of money for educating these children. In the educational jargon these pupils are called ''Free School Meal (FSM)'' pupils. We shall investigate how the proportion of ''FSM'' children correlates with the actual expenditure per child.

The source of this information is going to be the Department of Education's [http://www.education.gov.uk/schools/performance/ School Performance Database]. Say we are interested in Claycots School in Slough (which is close to being the largest Primary Schol in the country). Once you searched for that school you will find a large amount of information. at this stage I want you to have a good look at the url of that page:

http://www.education.gov.uk/cgi-bin/schools/performance/school.pl?urn=132089

The last six digits in that url (132089) are the ''Unique reference Number (urn)''. This will be important later.

But for starters, have a look at that website and you should be able to find some information on the percentage of pupils eligible for Free School meals, here 25.2% (as per November 2015). Also browse this website and check what other info may be of interest to you. Clearly we will want to look at the ''Total Income (Â£ per pupil)'' and ''Total expenditure (Â£ per pupil)'' information. And we may also be interested in the actual number of pupils (''Total number of pupils on roll (all ages)'').

What we want our end result to be is a spreadsheet with these bits of information for all Primary schools (and perhaps their location, Local Authority etc). I should warn you, you will need some patience and you will have to learn a little programming and html to achieve this.

= The website structure =

Before you can think about extracting data from websites you will have to understand the rough structure of the website. First, given we want to extract information for several (thousands) of schools you want to understand what changes for this website from school to school. Go to the url in your browser and change the urn number from 132089 to 101193 (which is for the manor Infants School in barking, another huge Primary School). as you change the urn in the url and press enter you should see that really nothing changes in the structure of the website, but the entries to the Table. That is an important feature which enables us t automate the scraping (collection) of data.

Next, you need to understand a little bit of the html code that is used to produce the website. The way to do that is to do the following (in Windows at least). Put your cursor into that part of the page you are interested in, say the table row with the info on FSM pupil percentage. Then right mouse click and then click on "Inspect Element" and then ... refrain from panicking! You will see something like

[[File:InspectElement.JPG|frame|none]]

This is how Tables look in html. <code><table></code> and <code></table></code> mark the beginnings and end of each table. Each row begins with <code><tr></code> and ends with <code></tr></code>. these markers are called tags and we will use them soon to our advantage. Tags are your friends! In essence we will later ask R to find the row in the Table that has the entry "Percentage of pupils eligible for FSM at any time during the past 6 years" and then we will just take the entry in the second column of that same row to be the number we want. Sounds easy, right? Conceptually yes, of course making it happen is another sory.

= Prep Work =

Start with setting your working directory

<pre class="r"># make sure this points to your working directory
setwd("C:/Users/Ralf/Dropbox/R/scraping/DeptEduc")</pre>
We will require the <code>Rcurl</code> and <code>XML</code> package, so let's install these (if you havn't already done so) and load them using the library command.

<pre class="r">x <- c("RCurl","XML")
# install.packages(x) # warning: uncommenting this may take a number of minutes
lapply(x, library, character.only = TRUE) # load the required packages</pre>
<pre>## Loading required package: bitops</pre>
== Get your reference spreadsheet ==

we said we wanted this info for all Primary Schools in the country. So what we need is a list of these and, importantly, their ''urn''. Fortunately this exists. Go to the homepage of the UK's [https://www.gov.uk/government/organisations/department-for-education Department for Education] and then click on '''Edubase'''. In the data downloads part of the website you will find the following spreadsheet: "All EduBase data.csv" which you should download into your working directory. Have a look at the spreadsheet. You should easily be able to identify the column named ''URN''. This is the list we are after. Also note that there are more than 44K entries in that spreadsheet. A lot of websirtes to look at!

<pre class="r"># Read in school admin data
schooldata <- read.csv("edubasealldata20151029.csv") # make sure the name matches that of your csv file</pre>
The spreadsheet is now available as the dataframe <code>schooldata</code>

Let's rename a few variables as some of the names are extremely clumsy:

<pre class="r">names(schooldata)[names(schooldata)=="PhaseOfEducation..name."] <- "EducPhase"
names(schooldata)[names(schooldata)=="EstablishmentStatus..name."] <- "EstabStatus"
names(schooldata)[names(schooldata)=="TypeOfEstablishment..name."] <- "EstabType"</pre>
And now we will select the relevant schools (Primary schools that are still open)

<pre class="r"># Select relevant school
primary_data <- schooldata[schooldata$EducPhase == "Primary",] # only primary schools
primary_data <- primary_data[primary_data$EstabStatus == "Open",] # only open schools</pre>
This leaves us with 17,988 primary schools.

== Setup of required info ==

Now we start preparing for the real work. First we indicate what the url (but for the school's urn) looks like

<pre class="r"># Base url from which to find the data - URN needs to be appended
url_base_2014 <- "http://www.education.gov.uk/cgi-bin/schools/performance/school.pl?urn="</pre>
We will soon combine this with the ''URN'' information from the reference spreadsheet.

Let's prepare to get information from the website. As we aregued before, our search strategy is going to be to find Table rows with entries that identify the rows with the data we are interested in. So let's collect thise pieces of identifying information in two list (although we will not really use them later) as they focus your mind:

<pre class="r"># this identifies the rows of interest
get_info = rbind("Percentage of pupils eligible for FSM at any time during the past 6 years",
"Total income",
"Total expenditure")

# these will be the variable names for the respective data
get_info_new = rbind("eFSM",
"TotalIncome",
"TotalExp")</pre>
Now we prepare the dataframe into which we will save the scraped information. We are basically creating a dummay entry into a new dataframe called <code>save_data</code>:

<pre class="r">save_data <- data.frame("URN"=-9999, "eFSM"=NA, "TotalIncome"=NA, "TotalExp"=NA)</pre>
== Getting the data from the website ==

In the end we will have to create a loop which repeats the information extraction for all the schools in <code>primary_data</code>. But first we shall investigate how this works for one particular school, say the first school in the <code>primary_data</code> dataframe, which is the Sir John Cass's Foundation Primary School (urn = 100000). Later we will extract this from all schools.

<pre class="r">id <- primary_data$URN[1] # get first URN from primary_data
url <- paste0(url_base_2014,id) # assemble the complete url
print(url)</pre>
<pre>## [1] "http://www.education.gov.uk/cgi-bin/schools/performance/school.pl?urn=100000"</pre>
<pre class="r">SOURCE <- getURL(url,encoding="UTF-8") #Download the page
PARSED <- htmlParse(SOURCE) #Format the html code </pre>
The <code>getURL</code> function retrieves what you saw when you looked at the html code of the website <code>url</code> and saves it into the <code>SOURCE</code> object. At this stage this is basically a very long and unstructured piece of text. The next line <code>htmlParse(SOURCE)</code> then takes this text and divides it into little bits using the tags (like <code><table></code> and <code><tr></code>) which we saw earlier. This will facilitate our search process.

Print <code>SOURCE</code> and <code>PARSED</code> to see the difference.

We continue by creating a new object in which we will save all the new school's info. Eventually we will add this to <code>save_data</code>.

<pre class="r">newschool <- NULL # create object that will be attached to dataframe
newschool["URN"] <- id</pre>
== Finding the relevant information ==

While <code>PARSED</code> is more structured than <code>SOURCE</code>, it is still massive. But there is a very convenient function (<code>xpathSApply</code>) that allows to quickly identify relevant bits of the website.

=== Finding the FSM data ===

Let's quote the line first and explain after:

<pre class="r">temp_tr <- xpathSApply(PARSED, "//tr[count(td)=1]") # get all rows with one td tag count, as results are in Table</pre>
The first input is the object <code>PARSED</code>, the second input is something like a search criterion for tags. This is when you haveto go back to the html info to understand what you are looking for. The FSM info is in a table with two columns, the first being a header column (
<th>
...
</th>
) and the second being an ordinary column (
<td>
...
</td>
). So if we find all table rows (
<tr>
...
</tr>
) with one "
<td>
...
</td>
" entry. If you want to understand the syntax of this search criterion and want to understand how to adjust it to your problem you should check this [http://www.zvon.org/xxl/XPathTutorial/General/examples.html website].

Look at the first two elements to see what <code>temp_tr</code> delivers:

<pre class="r">head(temp_tr,2)</pre>
<pre>## [[1]]
## <tr>
## <th>
## <label>Street</label>
## </th>
## <td class="num "> St James's Passage</td>
## </tr>
##
## [[2]]
## <tr>
## <th>
## <label>Town</label>
## </th>
## <td class="num "> London</td>
## </tr></pre>
each element is a html row and indeed has one elemnt of <code><td>...<td></code> (and in fact being preceeded by a <code><th> ... </th></code> element). We will now loop through all these elements and check whether we can find one which we are interested in.

<pre class="r"> # search through selected rows to find the ones we need
if (length(temp_tr)>0){ # case in which school data are available
for (j in 1:length(temp_tr)){
temp <- temp_tr[[j]] # this picks out the current row (including all tags)
temp1 <- xmlSApply(temp,xmlValue) # this extracts the values from the tags
# check if we found the value we are after
if (temp1[[1]] == "Percentage of pupils eligible for FSM at any time during the past 6 years") {
var <- "eFSM" # Set's the variable name we found
temp2 <- gsub("%","",temp1[[2]]) # gsub is a function to subsitute something
newschool[var] <- temp2 # places the value in newschool
}
}
} </pre>
To check what we have achieved so far we look at <code>newschool</code>

<pre class="r">print(newschool)</pre>
<pre>## URN eFSM
## "100000" " 33.2"</pre>
As you can see we have added the percentage (without percentage sign) of chidren on Free School Meals.

Just a few extra notes on the previous block of code. It starts with an <code>if</code> condition. This has to be added as some school's webpage has no relevant data (no elements in <code>temp_tr</code>) and if that is the case the code would fail. When I first wrote this code I did not have this condition and while the code worked well for the first 100 odd schools it failed then as there was a school with no available information. When you write codes like this you will have to do this a lot, proofing the code against some unexpected behaviour. This is why code never works on the first go and of course the code can fail in all sorts of different ways. Think of being a detective that needs to figire out how it fails and then think about how to fix it.

In the previous block of code I used two functions <code>xmlSApply</code> and <code>gsub</code>. I added comments to explain what these functions do but of course you can use <code>?xmlSApply</code> and <code>?gsub</code> to find out more details. How do you find these functions in the first place? Dr Google is your friend. For instance google for "How can I extract values from XML elements in R?" and you will after some searching find someone who had the same problem as you and in general you will also find someone providing a solution.

=== Finding the financial data ===

The financial data we are after ''Total income'' and ''Total Expenditure'' are in tables that have a slightly different structure. Open one of the school's site on the performance tables and go to the Financial Table (note that some schools have little such information published). Then right-mouse click and "Inspect Element" and you should see something like this:

[[File:InspectElement2.JPG|frame|none]]

Here you can see that in this table each row has one <code><th> ...</th></code> element and four <code><tr>...</tr></code> elements. This means that we have to change our filtering strategy

<pre class="r">temp_tr <- xpathSApply(PARSED, "//tr[count(td)=4]") # get all rows with four td tag count, as results are in Table</pre>
Now we have filtered all rows from tables with four columns (plus a header column although we didn't specify this).

What follows now is the serach for the rows with ''Total income'' and ''Total expenditure'' but that works in almost exactly the same way as for ''FSM''. See whether you can spot the slight differences.

<pre class="r"> # search through selected rows to find the ones we need
if (length(temp_tr)>0){ # case in which school data are available
for (j in 1:length(temp_tr)){
temp <- temp_tr[[j]] # this picks out the current row (including all tags)
temp1 <- xmlSApply(temp,xmlValue) # this extracts the values from the tags
# check if we found the value we are after
if (temp1[[1]] == "Total income") {
var <- "TotalIncome" # Set's the variable name we found
temp2 <- temp1[[2]] # The school's finance data is in columns 2
newschool[var] <- temp2 # places the value in newschool
}
if (temp1[[1]] == "Total expenditure") {
var <- "TotalExp" # Set's the variable name we found
temp2 <- temp1[[2]] # The school's finance data is in columns 2
newschool[var] <- temp2 # places the value in newschool
}
}
} </pre>
Clearly we now looked for two pieces of information rather than just one, which is why we had two <code>if</code> conditions. If you have many more you may want to learn about the <code>switch</code> function instead. We also didn't need to strib any "%" signs out of our values, so the definition of <code>temp2</code> is a little more straightforward.

To check what we have achieved so far we look at <code>newschool</code>

<pre class="r">print(newschool)</pre>
<pre>## URN eFSM TotalIncome TotalExp
## "100000" " 33.2" " 9578" " 9628"</pre>
You can see that we have now also added the financial data to our <code>newschool</code> entry. We have collected everything we want. Let's add that new school to our dataframe with new data, <code>save_data</code>. For this we use the <code>rbind</code> command which attaches two matrices (or rows in our case) to each other.

<pre class="r"> # add the newschool entry to the dataframe
save_data <- rbind(save_data,newschool)</pre>

@@ Line 39: / Line 39: @@
 == Get your reference spreadsheet ==
-we said we wanted this info for all Primary Schools in the country. So what we need is a list of these and, importantly, their ''urn''. Fortunately this exists. Go to the homepage of the UK's [https://www.gov.uk/government/organisations/department-for-education Department for Education] and then click on '''Edubase'''. In the data downloads part of the website you will find the following spreadsheet: &quot;All EduBase data.csv&quot; which you should download into your working directory. Have a look at the spreadsheet. You should easily be able to identify the column named ''URN''. This is the list we are after. Also note that there are more than 44K entries in that spreadsheet. A lot of websirtes to look at!
+we said we wanted this info for all Primary Schools in the country. So what we need is a list of these and, importantly, their ''urn''. Fortunately this exists. Go to the homepage of the UK's [https://www.gov.uk/government/organisations/department-for-education Department for Education] and then click on '''Edubase'''. In the data downloads part of the website you will find the following spreadsheet: &quot;All EduBase data.csv&quot; which you should download into your working directory. Have a look at the spreadsheet. You should easily be able to identify the column named ''URN''. This is the list we are after. Also note that there are more than 44K entries in that spreadsheet. A lot of websites to look at!
 <pre class="r"># Read in school admin data
-schooldata &lt;- read.csv(&quot;edubasealldata20151029.csv&quot;)   # make sure the name matches that of your csv file</pre>
+schooldata &lt;- read.csv(&quot;edubasealldata20151029.csv&quot;)   # make sure the name matches that of your csv file, the date changes fequently</pre>
 The spreadsheet is now available as the dataframe <code>schooldata</code>

← Older revision		Revision as of 12:06, 6 November 2015
Line 1:		Line 1:
−	~~= ScrapingIntroduction =~~
−
−	~~Ralf Becker<br />~~
−	~~Monday, November 02, 2015~~
−
	= The challenge =		= The challenge =

@@ Line 276: / Line 276: @@
 <pre class="r"># primary_data_merged_2014 &lt;- merge(primary_data,save_data,by=&quot;URN&quot;)</pre>
-The merge command does what it says and it merges all the info. The variable by which it merges is <code>URN</code> which exists in both <code>primary_data</code> and <code>save_data</code>. check out <code>?merge</code> to find moe details on merging two dataframes.
+The merge command does what it says and it merges all the info. The variable by which it merges is <code>URN</code> which exists in both <code>primary_data</code> and <code>save_data</code>. check out <code>?merge</code> to find more details on merging two dataframes.
 == Saving data ==

@@ Line 1: / Line 1: @@
 = The challenge =
@@ Line 11: / Line 16: @@
 The last six digits in that url (132089) are the ''Unique reference Number (urn)''. This will be important later.
-But for starters, have a look at that website and you should be able to find some information on the percentage of pupils eligible for Free School meals, here 25.2% (as per November 2015). Also browse this website and check what other info may be of interest to you. Clearly we will want to look at the ''Total Income (£ per pupil)'' and ''Total expenditure (£ per pupil)'' information. And we may also be interested in the actual number of pupils (''Total number of pupils on roll (all ages)'').
+But for starters, have a look at that website and you should be able to find some information on the percentage of pupils eligible for Free School meals, here 25.2% (as per November 2015). Also browse this website and check what other info may be of interest to you. Clearly we will want to look at the ''Total Income (Â£ per pupil)'' and ''Total expenditure (Â£ per pupil)'' information. And we may also be interested in the actual number of pupils (''Total number of pupils on roll (all ages)'').
 What we want our end result to be is a spreadsheet with these bits of information for all Primary schools (and perhaps their location, Local Authority etc). I should warn you, you will need some patience and you will have to learn a little programming and html to achieve this.
@@ Line 79: / Line 84: @@
 <pre class="r">save_data &lt;- data.frame(&quot;URN&quot;=-9999, &quot;eFSM&quot;=NA, &quot;TotalIncome&quot;=NA, &quot;TotalExp&quot;=NA)</pre>
-== Getting the data from the website ==
+= Getting the data from the website =
 In the end we will have to create a loop which repeats the information extraction for all the schools in <code>primary_data</code>. But first we shall investigate how this works for one particular school, say the first school in the <code>primary_data</code> dataframe, which is the Sir John Cass's Foundation Primary School (urn = 100000). Later we will extract this from all schools.
@@ Line 97: / Line 102: @@
 <pre class="r">newschool &lt;- NULL   # create object that will be attached to dataframe
 newschool[&quot;URN&quot;] &lt;- id</pre>
-== Finding the relevant information ==
+= Finding the relevant information =
 While <code>PARSED</code> is more structured than <code>SOURCE</code>, it is still massive. But there is a very convenient function (<code>xpathSApply</code>) that allows to quickly identify relevant bits of the website.
-=== Finding the FSM data ===
+== Finding the FSM data ==
 Let's quote the line first and explain after:
@@ Line 168: / Line 173: @@
 In the previous block of code I used two functions <code>xmlSApply</code> and <code>gsub</code>. I added comments to explain what these functions do but of course you can use <code>?xmlSApply</code> and <code>?gsub</code> to find out more details. How do you find these functions in the first place? Dr Google is your friend. For instance google for &quot;How can I extract values from XML elements in R?&quot; and you will after some searching find someone who had the same problem as you and in general you will also find someone providing a solution.
-=== Finding the financial data ===
+== Finding the financial data ==
 The financial data we are after ''Total income'' and ''Total Expenditure'' are in tables that have a slightly different structure. Open one of the school's site on the performance tables and go to the Financial Table (note that some schools have little such information published). Then right-mouse click and &quot;Inspect Element&quot; and you should see something like this:
@@ Line 206: / Line 211: @@
 <pre>##         URN        eFSM TotalIncome    TotalExp
 ##    &quot;100000&quot;     &quot; 33.2&quot;     &quot; 9578&quot;     &quot; 9628&quot;</pre>
-You can see that we have now also added the financial data to our <code>newschool</code> entry. We have collected everything we want. Let's add that new school to our dataframe with new data, <code>save_data</code>. For this we use the <code>rbind</code> command which attaches two matrices (or rows in our case) to each other.
+You can see that we have now also added the financial data to our <code>newschool</code> entry. We have collected everything we want. Let's add that new school to our dataframe with new data, <code>save_data</code>. For this we use the <code>rbind</code> command (row binding) which attaches the <code>newschool</code> entry to the already existing <code>save_data</code> dataframe.
 <pre class="r">  # add the newschool entry to the dataframe
    save_data &lt;- rbind(save_data,newschool)</pre>