Scraping a table over multiple pages with Rvest

user_10082579 0
Viewed 554

I am trying to scrape a table from a website. I managed to write minimal code to get the data from the table. See code below:

 start_date <- "1947-01-01"
    end_date <- "2020-12-28"
    css_selector <- ".datatable"
    
    url <- paste0("https://www.prosportstransactions.com/basketball/Search/SearchResults.php?Player=&Team=&BeginDate=", start_date,"&EndDate=", end_date, "&ILChkBx=yes&InjuriesChkBx=yes&PersonalChkBx=yes&Submit=Search&start=0")
    webpage <- xml2::read_html(url)


    data <- webpage %>%
      rvest::html_node(css = css_selector) %>%
      rvest::html_table() %>% 
      as_tibble()
    
    colnames(data) = data[1,]

    data <- data[-1, ]

However the table is split over multiple pages with each page showing only 25 rows.

I checked this solution before however the difference is that for the table I am working with, the link is modified with the starting row number (instead of the page number).

Any ideas on how to solve this would be much appreciated.

rvest r
1 Answers

One can use the last argument in the URL, &start= to iterate through the results page by page. The search results page renders 25 items per page, so the sequence of pages is 25, 50, 75, 100...

We will obtain the first 5 pages of results, for a total of 125 transactions. Since the first page starts with &start=0, we assign a vector, startRows to represent the starting row for each page.

We then use the vector to drive lapply() with an anonymous function that reads the data and manipulates it to remove the header row from each page of data read.

library(rvest)
library(dplyr)
start_date <- "1947-01-01"
end_date <- "2020-12-28"
css_selector <- ".datatable"
startRows <- c(0,25,50,75,100)
pages <- lapply(startRows,function(x){
     url <- paste0("https://www.prosportstransactions.com/basketball/Search/SearchResults.php?Player=&Team=&BeginDate=", start_date,"&EndDate=", end_date, 
                   "&ILChkBx=yes&InjuriesChkBx=yes&PersonalChkBx=yes&Submit=Search&start=",x)
     webpage <- xml2::read_html(url)
     data <- webpage %>%
          rvest::html_node(css = css_selector) %>%
          rvest::html_table() %>% 
          as_tibble()
     colnames(data) = data[1,]
     data[-1, ]
})
data <- do.call(rbind,pages)
head(data,n=10)

...and the output:

> head(data,n=10)
# A tibble: 10 x 5
   Date     Team      Acquired            Relinquished          Notes                          
   <chr>    <chr>     <chr>               <chr>                 <chr>                          
 1 1947-08… Bombers … ""                  "• Jack Underman"     fractured legs (in auto accide…
 2 1948-02… Bullets … "• Harry Jeannette… ""                    broken rib (DTD) (date approxi…
 3 1949-03… Capitols  ""                  "• Horace McKinney /… personal reasons (DTD)         
 4 1949-11… Capitols  ""                  "• Fred Scolari"      fractured right cheekbone (out…
 5 1949-12… Knicks    ""                  "• Vince Boryla"      mumps (out ~2 weeks)           
 6 1950-01… Knicks    "• Vince Boryla"    ""                    returned to lineup (date appro…
 7 1950-10… Knicks    ""                  "• Goebel Ritter / T… bruised ligaments in left ankl…
 8 1950-11… Warriors  ""                  "• Andy Phillip"      lacerated foot (DTD)           
 9 1950-12… Celtics   ""                  "• Andy Duncan (a)"   fractured kneecap (out indefin…
10 1951-12… Bullets   ""                  "• Don Barksdale"     placed on IL                   
>

Verifying the results

We can verify the results by printing the first and last rows from each page, starting with last observation on page 1.

data[c(25,26,50,51,75,76,100,101,125),]

...and the output, which matches the the content rendered on pages 1 - 5 of the search results when navigated manually on the website.

> data[c(25,26,50,51,75,76,100,101,125),]
# A tibble: 9 x 5
  Date      Team        Acquired      Relinquished    Notes                                    
  <chr>     <chr>       <chr>         <chr>           <chr>                                    
1 1960-01-… Celtics     ""            "• Bill Sharma… sprained Achilles tendon (date approxima…
2 1960-01-… Celtics     ""            "• Jim Loscuto… sore back and legs (out indefinitely) (d…
3 1964-10-… Knicks      "• Art Heyma… ""              returned to lineup                       
4 1964-12-… Hawks       "• Bob Petti… ""              returned to lineup (date approximate)    
5 1968-11-… Nets (ABA)  ""            "• Levern Tart" fractured right cheekbone (out indefinit…
6 1968-12-… Pipers (AB… ""            "• Jim Harding" took leave of absence as head coach for …
7 1970-08-… Lakers      ""            "• Earnie Kill… dislocated left foot (out indefinitely)  
8 1970-10-… Lakers      ""            "• Elgin Baylo… torn Achilles tendon (out for season) (d…
9 1972-01-… Cavaliers   "• Austin Ca… ""              returned to lineup

If we look at the last page in the table, we find that the maximum value for the page series is &start=61475. The R code to generate the entire sequence of pages (2460, which matches the number of pages listed in the search results on the website) is:

# generate entire sequence of pages
pages <- c(0,seq(from=25,to=61475,by=25))

...and the output:

> head(pages)
[1]   0  25  50  75 100 125
> tail(pages)
[1] 61350 61375 61400 61425 61450 61475
user_84719310
Related