{"id":164,"date":"2013-02-03T13:38:17","date_gmt":"2013-02-03T18:38:17","guid":{"rendered":"http:\/\/www.clayford.net\/statistics\/?p=164"},"modified":"2024-11-17T11:05:16","modified_gmt":"2024-11-17T16:05:16","slug":"scraping-data-off-a-web-site","status":"publish","type":"post","link":"https:\/\/www.clayford.net\/statistics\/scraping-data-off-a-web-site\/","title":{"rendered":"Scraping Data off a Web Site"},"content":{"rendered":"

I\u2019m taking the Data Analysis class through Coursera<\/a> and one of the topics we\u2019ve covered so far is how to \u201cscape\u201d data off a web site. The idea is to programmatically got through the source code of a web page, pull out some data, and then clean it up so you can analyze it. This may seem like overkill at first glance. After all, why not just select the data with your mouse and copy-and-paste into a spreadsheet? Well, for one, there may be dozens (or hundreds) of pages to visit and copying-and-pasting from each one would be time-consuming and impractical. Second, rarely does a copy-and-paste off a web site produce data ready for analysis. You have to tidy it up, sometimes quite a bit. Clearly these are both tasks we would like to automate.<\/p>\n

To put this idea to use, I decided to scrape some data from the box scores of Virginia Tech football games. I attended Tech and love watching their football team, so this seemed like a fun exercise. Here\u2019s an example<\/a> of one of their box scores. You\u2019ll see it is has everything but what songs the band played during halftime. I decided to start simple and just scrape the Virginia Tech Drive Summaries. This summarizes each drive, including things like number of plays, number of yards gained, and time of possession. Here\u2019s the function I wrote in R, called vtFballData:<\/p>\n

vtFballData <- function(start,stop,season){\r\n    dsf <- c()\r\n    # read the source code\r\n    for (i in start:stop){\r\n    url <- paste("http:\/\/www.hokiesports.com\/football\/stats\/showstats.html?",i,sep="")\r\n    web_page <- readLines(url)\r\n\r\n    # find where VT drive summary begins\r\n    dsum <- web_page[(grep("Virginia Tech Drive Summary", web_page) - 2):\r\n                         (grep("Virginia Tech Drive Summary", web_page) + 18)]\r\n    dsum2 <- readHTMLTable(dsum)\r\n    rn <- dim(dsum2[[1]])[1]\r\n    cn <- dim(dsum2[[1]])[2]\r\n    ds <- dsum2[[1]][4:rn,c(1,(cn-2):cn)]\r\n    ds[,3] <- as.character(ds[,3]) # convert from factor to character\r\n    py <- do.call(rbind,strsplit(sub("-"," ",ds[,3])," "))\r\n    ds2 <- cbind(ds,py)\r\n    ds2[,5] <- as.character(ds2[,5]) # convert from factor to character\r\n    ds2[,6] <- as.character(ds2[,6]) # convert from factor to character\r\n    ds2[,5] <- as.numeric(ds2[,5]) # convert from character to numeric\r\n    ds2[,6] <- as.numeric(ds2[,6]) # convert from character to numeric\r\n    ds2[,3] <- NULL # drop original pl-yds column\r\n\r\n    names(ds2) <-c("quarter","result","top","plays","yards")\r\n    # drop unused factor levels carried over from readlines\r\n    ds2$quarter <- ds2$quarter[, drop=TRUE] \r\n    ds2$result <- ds2$result[, drop=TRUE]\r\n\r\n    # convert TOP from factor to character\r\n    ds2[,3] <- as.character(ds2[,3]) \r\n    # convert TOP from M:S to just seconds\r\n    ds2$top <- sapply(strsplit(ds2$top,":"),\r\n        function(x) {\r\n            x <- as.numeric(x)\r\n            x[1]*60 + x[2]})\r\n\r\n    # need to add opponent\r\n    opp <- web_page[grep("Drive Summary", web_page)]\r\n    opp <- opp[grep("Virginia Tech", opp, invert=TRUE)] # not VT\r\n    opp <- strsplit(opp,">")[[1]][2]\r\n    opp <- sub(" Drive Summary<\/td","",opp)\r\n    ds2 <- cbind(season,opp,ds2)\r\n    dsf <- rbind(dsf,ds2)\r\n    }\r\nreturn(dsf)\r\n}<\/code><\/pre>\n
I\u2019m sure this is three times longer than it needs to be and could be written much more efficiently, but it works and I understand it. Let\u2019s break it down.<\/p>\n
My function takes three values: start, stop, and season. Start and stop are both numerical values needed to specify a range of URLs on hokiesports.com. Season is simply the year of the season. I could have scraped that as well but decided to enter it by hand since this function is intended to retrieve all drive summaries for a given season.<\/p>\n
The first thing I do in the function is define an empty variable called \u201cdsf\u201d (\u201cdrive summaries final\u201d) that will ultimately be what my function returns. Next I start a for loop that will start and end at numbers I feed the function via the \u201cstart\u201d and \u201cstop\u201d parameters. For example, the box score of the 1st game of the 2012 season has a URL ending in 14871. The box score of the last regular season game ends in 14882. To hit every box score of the 2012 season, I need to cycle through this range of numbers. Each time through the loop I \u201cpaste\u201d the number to the end of \u201chttp:\/\/www.hokiesports.com\/football\/stats\/showstats.html<\/a>?\u201d and create my URL. I then feed this URL to the readLines() function which retrieves the code of the web page and I save it as \u201cweb_page\u201d.<\/p>\n
Let\u2019s say we\u2019re in the first iteration of our loop and we\u2019re doing the 2012 season. We just retrieved the code of the box score web page for the Georgia Tech game<\/a>. If you go to that page, right click on it and view source, you\u2019ll see exactly what we have stored in our \u201cweb_page\u201d object. You\u2019ll notice it has a lot of stuff we don\u2019t need. So the next part of my function zeros in on the Virginia Tech drive summary:<\/p>\n
# find where VT drive summary begins\r\ndsum <- web_page[(grep("Virginia Tech Drive Summary", web_page) - 2):\r\n                 (grep("Virginia Tech Drive Summary", web_page) + 18)]<\/code><\/pre>\n
This took some trial and error to assemble. The grep() function tells me which line contains the phrase \u201cVirginia Tech Drive Summary\u201d. I subtract 2 from that line to get the line number where the HTML table for the VT drive summary begins (i.e., where the opening <table> tag appears). I need this for the upcoming function. I also add 18 to that line number to get the final line of the table code. I then use this range of line numbers to extract the drive summary table and store it as \u201cdsum\u201d. Now I feed \u201cdsum\u201d to the readHTMLTable() function, which converts an HTML table to a dataframe (in a list object) and save it as \u201cdsum2\u201d. The readHTMLTable() function is part of the XML package, so you have download and install that package first and call library(XML) before running this function.<\/p>\n
At this point we have a pretty good looking table. But it has 4 extra rows at the top we need to get rid of. Plus I don\u2019t want every column. I only want the first column (quarter) and last three columns (How lost, Pl-Yds, and TOP). This is a personal choice. I suppose I could have snagged every column, but decided to just get a few. To get what I want, I define two new variables, \u201crn\u201d and \u201ccn\u201d. They stand for row number and column number, respectively. \u201cdsum2\u201d is a list object with the table in the first element, [[1]]. I reference that in the call to the dim () function. The first element returned is the number of rows, the second the number of columns. Using \u201crn\u201d and \u201ccn\u201d I then index dsum2 to pull out a new table called \u201cds\u201d. This is pretty much what I wanted. The rest of the function is mainly just formatting the data and giving names to the columns.<\/p>\n
The next three lines of code serve to break up the \u201cPl-Yds\u201d column into two separate columns: plays and yards. The following five lines change variable classes and remove the old \u201cPl-Yds\u201d column. After that I assign names to the columns and drop unused factor levels. Next up I convert TOP into seconds. This allows me to do mathematical operations, such as summing and averaging.<\/p>\n
The final chunk of code adds the opponent. This was harder than I thought it would be. I\u2019m sure it can be done faster and easier than I did it, but what I does works. First I use the grep() function to identify the two lines that contain the phrase \u201cDrive Summary\u201d. One will always have Virginia Tech and the other their opponent. The next line uses the invert parameter of grep to pick the line that does not<\/em> contain Virginia Tech. The selected line looks like this for the first box score of 2012: \u201c<td colspan=”9″>Georgia Tech Drive Summary<\/td>\u201d. Now I need to extract \u201cGeorgia Tech\u201d. To do this I split the string by \u201c>\u201d and save the second element:<\/p>\n
opp <- strsplit(opp,">")[[1]][2]<\/code><\/pre>\n
It looks like this after I do the split:<\/p>\n
[[1]]\r\n[1] "<td colspan=\\"9\\""              "Georgia Tech Drive Summary<\/td"\r\n<\/code><\/pre>\n
Hence the need to add the \u201c[[1]][2]\u201d reference. Finally I substitute \u201d Drive Summary<\/td\u201d with nothing and that leaves me with \u201cGeorgia Tech\u201d. Finally I add the season and opponent to the table and update the \u201cdsf\u201d object. The last line is necessary to allow me to add each game summary to the bottom of the previous table of game summaries.<\/p>\n
Here\u2019s how I used the function to scrape all VT drive summaries from the 2012 regular season:<\/p>\n
dsData2012 <- vtFballData(14871,14882,2012)<\/code><\/pre>\n
To identify start and stop numbers I had to go to the VT 2012 stats page<\/a> and hover over all the box score links to figure out the number sequence. Fortunately they go in order. (Thank you VT athletic dept!) The bowl game is out of sequence; its number is 15513. But I could get it by calling vtFballData(15513,15513,2012). After I call the function, which takes about 5 seconds to run, I get a data frame that looks like this:<\/p>\n
 season          opp quarter result top plays yards\r\n   2012 Georgia Tech       1   PUNT 161     6    24\r\n   2012 Georgia Tech       1     TD 287    12    56\r\n   2012 Georgia Tech       1  DOWNS 104     5    -6\r\n   2012 Georgia Tech       2   PUNT 298     7    34\r\n   2012 Georgia Tech       2   PUNT  68     4    10\r\n   2012 Georgia Tech       2   PUNT  42     3     2<\/code><\/pre>\n
Now I\u2019m ready to do some analysis! There are plenty of other variables I could have added, such as whether VT won the game, whether it was a home or away game, whether it was a noon, afternoon or night game, etc. But this was good enough as an exercise. Maybe in the future I\u2019ll revisit this little function and beef it up.<\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019m taking the Data Analysis class through Coursera and one of the topics we\u2019ve covered so far is how to… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[32],"class_list":["post-164","post","type-post","status-publish","format-standard","hentry","category-using-r","tag-virginia-tech"],"_links":{"self":[{"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/posts\/164","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/comments?post=164"}],"version-history":[{"count":3,"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/posts\/164\/revisions"}],"predecessor-version":[{"id":984,"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/posts\/164\/revisions\/984"}],"wp:attachment":[{"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/media?parent=164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/categories?post=164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.clayford.net\/statistics\/wp-json\/wp\/v2\/tags?post=164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}