162

How do I scrape html tables using the XML package?

Take, for example, this wikipedia page on the Brazilian soccer team. I would like to read it in R and get the "list of all matches Brazil have played against FIFA recognised teams" table as a data.frame. How can I do this?

epo3
  • 2,991
  • 2
  • 33
  • 60
Eduardo Leoni
  • 8,991
  • 6
  • 42
  • 49

4 Answers4

152

…or a shorter try:

library(XML)
library(RCurl)
library(rlist)
theurl <- getURL("https://en.wikipedia.org/wiki/Brazil_national_football_team",.opts = list(ssl.verifypeer = FALSE) )
tables <- readHTMLTable(theurl)
tables <- list.clean(tables, fun = is.null, recursive = FALSE)
n.rows <- unlist(lapply(tables, function(t) dim(t)[1]))

the picked table is the longest one on the page

tables[[which.max(n.rows)]]
Jim G.
  • 15,141
  • 22
  • 103
  • 166
  • The readHTMLTable help also provides an example of reading a plain text table out of an HTML PRE element using htmlParse(), getNodeSet(), textConnection() and read.table() – Dave X Aug 07 '13 at 13:59
49
library(RCurl)
library(XML)

# Download page using RCurl
# You may need to set proxy details, etc.,  in the call to getURL
theurl <- "http://en.wikipedia.org/wiki/Brazil_national_football_team"
webpage <- getURL(theurl)
# Process escape characters
webpage <- readLines(tc <- textConnection(webpage)); close(tc)

# Parse the html tree, ignoring errors on the page
pagetree <- htmlTreeParse(webpage, error=function(...){})

# Navigate your way through the tree. It may be possible to do this more efficiently using getNodeSet
body <- pagetree$children$html$children$body 
divbodyContent <- body$children$div$children[[1]]$children$div$children[[4]]
tables <- divbodyContent$children[names(divbodyContent)=="table"]

#In this case, the required table is the only one with class "wikitable sortable"  
tableclasses <- sapply(tables, function(x) x$attributes["class"])
thetable  <- tables[which(tableclasses=="wikitable sortable")]$table

#Get columns headers
headers <- thetable$children[[1]]$children
columnnames <- unname(sapply(headers, function(x) x$children$text$value))

# Get rows from table
content <- c()
for(i in 2:length(thetable$children))
{
   tablerow <- thetable$children[[i]]$children
   opponent <- tablerow[[1]]$children[[2]]$children$text$value
   others <- unname(sapply(tablerow[-1], function(x) x$children$text$value)) 
   content <- rbind(content, c(opponent, others))
}

# Convert to data frame
colnames(content) <- columnnames
as.data.frame(content)

Edited to add:

Sample output

                     Opponent Played Won Drawn Lost Goals for Goals against  % Won
    1               Argentina     94  36    24   34       148           150  38.3%
    2                Paraguay     72  44    17   11       160            61  61.1%
    3                 Uruguay     72  33    19   20       127            93  45.8%
    ...
Richie Cotton
  • 118,240
  • 47
  • 247
  • 360
  • 7
    For anyone else who is fortunate enough to find this post, this script will likely not execute unless the user adds their "User-Agent" information, as described in this other helpful post: http://stackoverflow.com/questions/9056705/setting-an-informative-user-agent-string-in-geturl – Rguy Mar 02 '12 at 20:17
31

The rvest along with xml2 is another popular package for parsing html web pages.

library(rvest)
theurl <- "http://en.wikipedia.org/wiki/Brazil_national_football_team"
file<-read_html(theurl)
tables<-html_nodes(file, "table")
table1 <- html_table(tables[4], fill = TRUE)

The syntax is easier to use than the xml package and for most web pages the package provides all of the options ones needs.

Dave2e
  • 22,192
  • 18
  • 42
  • 50
  • The read_html gives me the error "'file:///Users/grieb/Auswertungen/tetyana-snp-2016/data/snp-nexus/15/SNP%20Annotation%20Tool.html' does not exist in current working directory ('/Users/grieb/Auswertungen/tetyana-snp-2016/code')." – scs Jul 14 '16 at 15:55
28

Another option using Xpath.

library(RCurl)
library(XML)

theurl <- "http://en.wikipedia.org/wiki/Brazil_national_football_team"
webpage <- getURL(theurl)
webpage <- readLines(tc <- textConnection(webpage)); close(tc)

pagetree <- htmlTreeParse(webpage, error=function(...){}, useInternalNodes = TRUE)

# Extract table header and contents
tablehead <- xpathSApply(pagetree, "//*/table[@class='wikitable sortable']/tr/th", xmlValue)
results <- xpathSApply(pagetree, "//*/table[@class='wikitable sortable']/tr/td", xmlValue)

# Convert character vector to dataframe
content <- as.data.frame(matrix(results, ncol = 8, byrow = TRUE))

# Clean up the results
content[,1] <- gsub(" ", "", content[,1])
tablehead <- gsub(" ", "", tablehead)
names(content) <- tablehead

Produces this result

> head(content)
   Opponent Played Won Drawn Lost Goals for Goals against % Won
1 Argentina     94  36    24   34       148           150 38.3%
2  Paraguay     72  44    17   11       160            61 61.1%
3   Uruguay     72  33    19   20       127            93 45.8%
4     Chile     64  45    12    7       147            53 70.3%
5      Peru     39  27     9    3        83            27 69.2%
6    Mexico     36  21     6    9        69            34 58.3%
learnr
  • 6,479
  • 4
  • 27
  • 23
  • Excellent call on using xpath. Minor point: you can slightly simplify the path argument by changing //*/ to //, e.g. "//table[@class='wikitable sortable']/tr/th" – Richie Cotton Sep 14 '09 at 09:33
  • I get an error "Scripts should use an informative User-Agent string with contact information, or they may be IP-blocked without notice." [2] " Is there a way round this to implement this method? – pssguy Jan 17 '12 at 20:59
  • 2
    options(RCurlOptions = list(useragent = "zzzz")). See also http://www.omegahat.org/RCurl/FAQ.html section "Runtime" for other alternatives and discussion. – learnr Feb 03 '12 at 20:58