22 September 2014

In Previous Episodes

  • Seen functions to load data in passing
  • Learned about string manipulation and regexp


  • Getting data into and out of the system when it's already in R format
  • Import and export when the data is already very structured and machine-readable
  • Dealing with less structured data
  • Web scraping

Reading Data from R

  • You can load and save R objects
    • R has its own format for this, which is shared across operating systems
    • It's an open, documented format if you really want to pry into it
  • save(thing, file="name") saves thing in a file called name (conventional extension: rda or Rda)
  • load("name") loads the object or objects stored in the file called name, with their old names

gmp <- read.table("http://www.stat.cmu.edu/~cshalizi/statcomp/14/lectures/06/gmp.dat")
gmp$pop <- round(gmp$gmp/gmp$pcgmp)
## [1] FALSE
not_gmp <- load(file="gmp.Rda")
## [1] "MSA"   "gmp"   "pcgmp" "pop"
## [1] "gmp"

  • We can load or save more than one object at once; this is how RStudio will load your whole workspace when you're starting, and offer to save it when you're done
  • Many packages come with saved data objects; there's the convenience function data() to load them
##  Sex         Bwt            Hwt       
##  F:47   Min.   :2.00   Min.   : 6.30  
##  M:97   1st Qu.:2.30   1st Qu.: 8.95  
##         Median :2.70   Median :10.10  
##         Mean   :2.72   Mean   :10.63  
##         3rd Qu.:3.02   3rd Qu.:12.12  
##         Max.   :3.90   Max.   :20.50

Note: data() returns the name of the loaded data file!

Non-R Data Tables

  • Tables full of data, just not in the R file format
  • Main function: read.table()
    • Presumes space-separated fields, one line per row
    • Main argument is the file name or URL
    • Returns a dataframe
    • Lots of options for things like field separator, column names, forcing or guessing column types, skipping lines at the start of the file…
  • read.csv() is a short-cut to set the options for reading comma-separated value (CSV) files
    • Spreadsheets will usually read and write CSV

Writing Dataframes

  • Counterpart functions write.table(), write.csv() write a dataframe into a file
  • Drawback: takes a lot more disk space than what you get from load or save
  • Advantage: can communicate with other programs, or even edit manually

Less Friendly Data Formats

  • The foreign package on CRAN has tools for reading data files from lots of non-R statistical software
  • Spreadsheets are special

Spreadsheets Considered Harmful

  • Spreadsheets look like they should be dataframes
  • Real spreadsheets are full of ugly irregularities
    • Values or formulas?
    • Headers, footers, side-comments, notes
    • Columns change meaning half-way down
    • Whole separate programming languages apparently intended to mostly to spread malware
  • Ought-to-be-notorious source of errors in both industry (1, 2) and science (e.g., Reinhart and Rogoff)

Spreadsheets, If You Have To

  • Save the spreadsheet as a CSV; read.csv()
  • Save the spreadsheet as a CSV; edit in a text editor; read.csv()
  • Use read.xls() from the gdata package
  • Tries very hard to work like read.csv(), can take a URL or filename
  • Can skip down to the first line that matches some pattern, select different sheets, etc.
  • You may still need to do a lot of tidying up after

require(gdata, quietly=TRUE)
## gdata: read.xls support for 'XLS' (Excel 97-2004) files ENABLED.
## gdata: read.xls support for 'XLSX' (Excel 2007+) files ENABLED.
## Attaching package: 'gdata'
## The following object is masked from 'package:stats':
##     nobs
## The following object is masked from 'package:utils':
##     object.size

gmp_2008_2013 <- read.xls("gdp_metro0914.xls",pattern="U.S.")
##       U.S..metropolitan.areas X13.269.057 X12.994.636 X13.461.662
## 1                 Abilene, TX       5,725       5,239       5,429
## 2                   Akron, OH      28,663      27,761      28,616
## 3                  Albany, GA       4,795       4,957       4,928
## 4                  Albany, OR       3,235       3,064       3,050
## 5 Albany-Schenectady-Troy, NY      40,365      42,454      42,969
## 6             Albuquerque, NM      37,359      38,110      38,801
##   X13.953.082 X14.606.938 X15.079.920 .......
## 1       5,761       6,143       6,452     252
## 2      29,425      31,012      31,485      80
## 3       4,938       5,122       5,307     290
## 4       3,170       3,294       3,375     363
## 5      43,663      45,330      46,537      58
## 6      39,967      41,301      41,970      64

Semi-Structured Files, Odd Formats

  • Files with metadata (e.g., earthquake catalog)
  • Non-tabular arrangement
  • Generally, write function to read in one (or a few) lines and split it into some nicer format
    • Generally involves a lot of regexps
    • Functions are easier to get right than code blocks in loops

In Praise of Capture Groups

  • Parentheses don't just group for quantifiers; they also create capture groups, which the regexp engine remembers
  • Can be referred to later (\1, \2, etc.)
  • Can also be used to simplify getting stuff out
  • Examples in the handout on regexps, but let's reinforce the point

Scraping the Rich

  • Remember that the lines giving net worth looked like

        <td class="worth">$72 B</td>


        <td class="worth">$5,3 B</td>

One regexp which catches this:

richhtml <- readLines("http://www.stat.cmu.edu/~cshalizi/statcomp/14/labs/03/rich.html")
worth_pattern <- "\\$[0-9,]+ B"
worth_lines <- grep(worth_pattern, richhtml)
## [1] 100

(that last to check we have the right number of matches)

Just using this gives us strings, including the markers we used to pin down where the information was:

worth_matches <- regexpr(worth_pattern, richhtml)
worths <- regmatches(richhtml, worth_matches)
## [1] "$72 B"   "$58,5 B" "$41 B"   "$36 B"   "$36 B"   "$35,4 B"

Now we'd need to get rid of the anchoring $ and B; we could use substr, but…

Adding a capture group doesn't change what we match:

worth_capture <- worth_pattern <- "\\$([0-9,]+) B"
capture_lines <- grep(worth_capture, richhtml)
identical(worth_lines, capture_lines)
## [1] TRUE

but it does have an advantage

Using regexec

worth_matches <- regmatches(richhtml[capture_lines], 
  regexec(worth_capture, richhtml[capture_lines]))
## [[1]]
## [1] "$72 B" "72"   
## [[2]]
## [1] "$58,5 B" "58,5"

List with 1 element per matching line, giving the whole match and then each paranethesized matching sub-expression

Functions make the remaining manipulation easier:

second_element <- function(x) { return(x[2]) }
worth_strings <- sapply(worth_matches, second_element)
comma_to_dot <- function(x) {
worths <- as.numeric(sapply(worth_strings, comma_to_dot))
## [1] 72.0 58.5 41.0 36.0 36.0 35.4

Exercise: Write one function which takes a single line, gets the capture group, and converts it to a number

Web Scraping

  1. Take a webpage designed for humans to read
  2. Have the computer extract the information we actually want
  3. Iterate as appropriate

Take in unstructured pages, return rigidly formatted data

Being More Explicit in Step 2

  • The information we want is somewhere in the page, possibly in the HTML
  • There are usually markers surrounding it, probably in the HTML
  • We now know how to pick apart HTML using regular expressions

  • Figure out exactly what we want from the page
  • Understand how the information is organized on the page
    • What does a human use to find it?
    • Where do those cues appear in the HTML source?
  • Write a function to automate information extraction
    • Generally, this means regexps
    • Parenthesized capture groups are helpful
    • The function may need to iterate
    • You may need more than one function
  • Once you've got it working for one page, iterate over relevant pages

Example: Book Networks

  • Two books are linked if they're bought together at Amazon

  • Amazon gives this information away (to try to drive sales)

  • How would we replicate this?

  • Do we want "frequently bought together", or "customers who bought this also bought that"? Or even "what else do customers buy after viewing this"?
    • Let's say "customers who bought this also bought that"
  • Now look carefully at the HTML
    • There are over 14,000 lines in the HTML file for this page; you'll need a text editor
    • Fortunately most of it's irrelevant

<div class="shoveler" id="purchaseShvl">
    <div class="shoveler-heading">
        <h2>Customers Who Bought This Item Also Bought</h2>

<div class="shoveler-pagination" style="display:none">

Page <span class="page-number"></span>  of  <span class="num-pages"></span> 
<span class="start-over"><span class="a-text-separator"></span><a href="#" onclick="return false;" class="start-over-link">Start over</a></span>

    <div class="shoveler-button-wrapper" id="purchaseButtonWrapper">
        <a class="back-button" href="#Back" style="display:none" onclick="return false;"><span class="auiTestSprite s_shvlBack"><span>Back</span></span></a>
        <div class="shoveler-content">
            <ul tabindex="-1">

Here's the first of the also-bought books:

  <div class="new-faceout p13nimp"  id="purchase_0387981403" data-asin="0387981403" data-ref="pd_sim_b_1">
<a href="/ggplot2-Elegant-Graphics-Data-Analysis/dp/0387981403/ref=pd_sim_b_1?ie=UTF8&refRID=1HZ0VDHEFFX3EM2WNWRH"  class="sim-img-title" > <div class="product-image">
                       <img src="http://ecx.images-amazon.com/images/I/31I22xsT%2BXL._SL500_PIsitb-sticker-arrow-big,TopRight,35,-73_OU01_SS100_.jpg" width="100" alt="" height="100" border="0" />
                    <span title="ggplot2: Elegant Graphics for Data Analysis (Use R!)">ggplot2: Elegant Graphics for Data &#133;</span> </a>

    <div class="byline">
        <span class="carat">&#8250</span> 

We could extract the ISBN from this, and then go on to the next book, and so forth…

<div id="purchaseSimsData" class="sims-data"
style="display:none" data-baseAsin="0387747303"
data-deviceType="desktop" data-featureId="pd_sim" data-isAUI="1" data-pageId="0387747303" data-pageRequestId="1HZ0VDHEFFX3EM2WNWRH" data-reftag="pd_sim_b" data-vt="0387747303"

In this case there's a big block which gives us the ISBNs of all the also-bought books


  • Load the page as text
  • Search for the regexp which begins this block, contains at least one ISBN, and then ends
  • Extract the sequence of ISBNs as a string, split on comma
  • Record in a dataframe that Data Manipulation's ISBN is also bought with each of those ISBNs
  • Snowball sampling: Go to the webpage of each of those books and repeat
    • Stop when we get tired…
    • Or when Amazon gets annoyed with us

More considerations on web-scraping

  • You should really look at the site's robots.txt file and respect it
  • See [https://github.com/hadley/rvest] for a prototype of a package to automate a lot of the work of scraping webpages


  • Loading and saving R objects is very easy
  • Reading and writing dataframes is pretty easy
  • Extracting data from unstructured sources is about using regexps appropriately
    • Maybe not easy, but at least feasible