Related questions

Trying to web scrape a merged Html table with r

2018-02-13 02:55 Eliot Lee imported from Stackoverflow

I'm trying to be able to scrape all the tables on any page of the Bureau of Labor Statistics (particularly this one: https://www.bls.gov/news.release/empsit.htm). However, I'm reaching an issue with R on a specific table on that site. The last table which is labelled as Table 7. Using the package rvest, I am using a recursive loop that will first create a matrix of tableIDs taken from the website, filtering out those that have no actual use, and then putting it into the html_table function(). Since there are merged cells, I left fill = TRUE on for the loop, and I also added extra conditions in order to account for some tables that actually have html table but still have the table id (Exhibit 4, 6, and 7). The issue lies in that for the final table, the second row actually does not have enough inputs for the amount of columns given and the rvest function fills it up in an odd way. The second line shouldn't have the 2016 between the May and Jun column and will mess up any future query that I do. Can anybody please help?

Output:

            Benchmark 2017 2016 2016 2016 2016 2016 2016 2016 2016 2016 2017 2017 2017 Total
1           Benchmark 2017  Apr  May 2016  Jun  Jul  Aug  Sep  Oct  Nov  Dec  Jan  Feb   Mar
2   Actual Net Birth/Death  404  180   15  244  105  -38  255  -14  -35 -179   98   76 1,111
3 Forecast Net Birth/Death  255  231   99  154  113  -58  237    7  -17 -247  124   32   930
4               Difference  149  -51  -84   90   -8   20   18  -21  -18   68  -26   44   181
5    Cumulative Difference  149   98   14  104   96  116  134  113   95  163  137  181

Code below:

webpage<- read_html("https://www.bls.gov/web/empsit/cesbmart.htm")
links <- html_nodes(webpage, "table")

titleMat <- bind_rows(lapply(xml_attrs(links), function(x) 
data.frame(as.list(x), stringsAsFactors=FALSE)))
tableExtract <- list()
tableNames <- array()
tableCap <- array()  
emptyArr <- array()

takeOut <- array()
counter <-0


for(i in 1:nrow(titleMat)){
  bool1 <- (titleMat[i,"class"] == "NA")

  if(is.na(bool1)){
    counter <- counter+1
    takeOut[counter] <- i
    }


}

tableID <- bind_rows(lapply(xml_attrs(links), function(x) data.frame(as.list(x), stringsAsFactors=FALSE)))[,"id"]
tableID
if (counter > 0){
tableID <- tableID[-c(takeOut)]
}

emptyCheck <- 0
for (cnt in 1:length(tableID)){


  capCheck <-webpage %>% html_nodes(paste("#",tableID[cnt],sep="")) %>% html_nodes("caption") %>% html_text()

  if (nchar(capCheck)>0){

    changedCap <-trimws(capCheck)

    tableCap[cnt] <- changedCap
  }



  thead <-webpage %>% html_nodes(paste("#",tableID[cnt],sep="")) %>% html_nodes("thead") %>% html_text()
  tbody <- webpage %>% html_nodes(paste("#",tableID[cnt],sep="")) %>% html_nodes("tbody") %>% html_text()
  tfoot <- webpage %>% html_nodes(paste("#",tableID[cnt],sep="")) %>% html_nodes("tfoot") %>% html_text()

  if( isTRUE(nchar(thead) > 0) || isTRUE(nchar(tbody) > 0) || isTRUE(nchar(tfoot) > 0)   ){

    tableExtract[[cnt]] <- webpage %>% html_nodes(paste("#",tableID[cnt],sep="")) %>% html_table(fill = TRUE) %>% .[[1]]
    tableExtract[[cnt]]
    tableNames[cnt] <- paste(tableID[cnt]," - v",sep="")
  }
  else{
    tableExtract[[cnt]] <- matrix("There are no recent updates for this table",1,1)
    tableNames[cnt] <- paste(tableID[cnt]," - v",sep="")

    emptyCheck <- emptyCheck + 1
    emptyArr[emptyCheck] <- cnt
  }
}

1 answer

  • answered 2018-02-13 07:51 Guillaume Ottavianoni

    You can try html_table function with FILL parameter :

    library(rvest)

url <- "https://www.bls.gov/news.release/empsit.htm"
page <- read_html(url)

tables <- page %>% html_nodes("table")

for (i in 1:length(tables)) {
 content <- try(tables[i] %>% html_table(fill=T))
   if( typeof(content) == 'list' ) content <- data.frame(content) else {
      content <- matrix(content)[[1]]
      content <- content[-c(1,length(content[,1])-1,length(content[,1])),]
   }
 assign(paste0("table_",i),content)
}

    Hope that will help

    Gottavianoni

See also questions close to this topic

  • Glitch flickering with hovering menu list

    I have this problem where I want make a box around a menu list. But I've never seen something like this. When I hover over my menu list it flickers? The jsfiddle has the full-code.

    Here is the link to jsfiddle

    li {
  display: inline-block; 

    position:relative;
    line-height: 7vmax;
    vertical-align: middle;
 }



ul li a {color:rgb(23,123,177);text-decoration: none;margin-left:5vmax;font-size:1.3vmax;}

a:hover { background-color: #2c3e50;
  padding: 1vmax;}

    Does anyone know why is this? And how can I fix this? Thanks.

  • CSS3 Flex layout leaving empty space on the right

    I use 4 column layout. Every div Width is 25%. Leaving empty space on the right. How to fix it?

    enter image description here

    body,html{margin:0;padding:0}.flexbox-slider{display:-webkit-box;display:-ms-flexbox;display:flex;width:100%;height:364px;visibility:hidden;margin:auto;padding-bottom:40px}.flexbox-slider .flexbox-slide img{position:absolute}.flexbox-slider .flexbox-slide{-webkit-transition-property:all;transition-property:all;-webkit-transition-duration:.3s;transition-duration:.3s;-webkit-transition-timing-function:linear;transition-timing-function:linear;-webkit-transition-delay:0s;transition-delay:0s;width:25%;height:364px;position:relative;overflow:hidden;cursor:pointer;visibility:visible}.flexbox-slider .flexbox-slide:after{position:absolute;top:0;left:0;content:"";display:block;width:100%;height:100%;background-color:rgba(255,255,255,.6);z-index:2;opacity:0}.flexbox-slider .flexbox-slide img{osition:absolute;height:auto;width:auto;min-width:100%;min-height:100%;z-index:1}.flexbox-slider .flexbox-slide .text-block{position:absolute;left:30px;bottom:30px;max-width:400px;padding:20px;border-radius:5px;background-color:rgba(0,0,0,.6);color:#fff;z-index:4;visibility:hidden}.flexbox-slider .flexbox-slide .text-block h3{font-size:20px;font-weight:700}.flexbox-slider:hover .flexbox-slide:hover{-ms-flex-negative:0;flex-shrink:0;width:780px}.flexbox-slide .txt{z-index:89;position:absolute;bottom:-90%;left:0;width:100%;height:100%;display:block;padding:25% 50px 1px;color:#fff;transition:bottom 3s,background-color 0s;-moz-transition:bottom 3s,background-color 0s;-webkit-transition:bottom 3s,background-color 0s;-o-transition:bottom 3s,background-color 0s;-webkit-transition-property:background-color;-webkit-transition-duration:.5s;-webkit-transition-timing-function:ease}.flexslider,.slides li{height:650px}.flexbox-slide .txt span{width:85%;word-wrap:break-word;word-break:normal;display:block;margin-top:30px}.flexbox-slide p{font-size:18px;line-height:25px;color:#fff;font-weight:700}.flexbox-slide .txt:hover{bottom:-45%;background-color:rgba(0,0,0,.39)}.flexslider{position:relative;overflow:hidden;background:url(images/loading.gif) 50% no-repeat}.slides{position:relative;z-index:1}.flex-control-nav{position:absolute;bottom:10px;z-index:2;width:100%;text-align:center}.flex-control-nav a,.flex-control-nav li{display:inline-block;width:14px;height:14px}.flex-control-nav li{margin:0 5px;zoom:1}.flex-control-nav a{line-height:40px;overflow:hidden;background:url(images/dot.png) right 0 no-repeat;cursor:pointer}.flex-control-nav .flex-active{background-position:0 0}.flex-direction-nav{position:absolute;z-index:3;width:100%;top:45%}.flex-direction-nav li a{display:block;width:50px;height:50px;overflow:hidden;cursor:pointer;position:absolute}.flex-direction-nav li a.flex-prev{left:40px;background:url(images/prev.png) center center no-repeat}.flex-direction-nav li a.flex-next{right:40px;background:url(images/next.png) center center no-repeat}

I use 4 column layout.
Every div Width is 25%.
Leaving empty space on the right.
How to fix it?
    <html>
 <head></head>
 <body>
  <div class="flexbox-slider my-flexbox-slider" style=""> 
   <div class="flexbox-slide"> 
    <img src="https://www.dahuatech.com/upload/2017/10/24/15088249047345orij8.jpg" alt="Slide Image" /> 
   </div> 
   <div class="flexbox-slide"> 
    <img src="https://www.dahuatech.com/upload/2017/10/24/15088249047345orij8.jpg" alt="Slide Image" /> 
   </div> 
   <div class="flexbox-slide"> 
    <img src="https://www.dahuatech.com/upload/2017/10/24/15088249047345orij8.jpg" alt="Slide Image" /> 
   </div> 
   <div class="flexbox-slide"> 
    <img src="https://www.dahuatech.com/upload/2017/10/24/15088249047345orij8.jpg" alt="Slide Image" /> 
   </div> ... 
  </div>
 </body>
</html>

  • How to find out what's preventing a click from happening?

    I have the following link on the page:

    <a href="/go/some/where.htm">...</a>

    It lives among HTML that makes up a google.maps.InfoWindow box (which itself doesn't have a click event). This infobox comes up when the user moves the mouse over a mapping pin. For whatever reason, when the user clicks on a link in the infobox, it doesn't fire and I am trying to find out why.

    The code base is huge and I have no idea where exactly the preventDefault or propagation is stopped. Is there an easier way? Can I hook into the event flow some way that would show me what is preventing the click?

  • R: separate a series of string by comma

    I have a series of string marked by quotation marks and I'd like them to be separated by comma so that I can pass them to a vector. I tried paste command but I kept getting error messages. Thank you. The data is like:

    "Alabama"     "Arkansas"    "Connecticut" "Georgia"     "Hawaii"     "Minnesota"   "Mississippi" "Missouri"    "Oregon"
  • Assessing if points exhibit spatial clustering (using R)

    This is a bit of a theoretical and practical question at the same time. I have a df containing x, y, z coordinates of a list of points. These points are dots on a 3D surface generated by image segmentation. The question I am trying to address is if these points are randomly distributed on this surface or if they exhibit some clustering. I'm testing this in R.

    The first method I am using is kmeans. I ask the computer to determine the best (if any) number of groups these data can be made to fit into. I am using this piece of code. It tests 30 different indices (various methods) and outputs the best number of clusters

    library("NbClust")
nb <- NbClust(df, distance = "euclidean", min.nc = 2,
          max.nc = 10, method = "kmeans")
library("factoextra")
fviz_nbclust(nb)

    This code comes from http://www.sthda.com/english/articles/29-cluster-validation-essentials/96-determining-the-optimal-number-of-clusters-3-must-know-methods/

    I get a certain number of clusters, which I guess is an indication of clustering in the first place. However, I would like to calculate a metric out of it? Suggestions on how to?

    In addition I am also checking for clustering via histograms.

         df_mat <- df %>% as.matrix()
     dist_df <- dist(df_mat)
     hist(dist_df)

    You would expect multiple peaks for clustering, one single peak for more or less random distributions perhaps.

    Another approach I am trying is hierarchical clustering 

     my_hclustdf <- hclust(dist_df)
 plot(my_hclustdf)

    However, the output, a dendogram, itself does not tell me much.

    Any suggestion would be greatly appreciated. Many thanks

  • Replacing values between rasters

    I'm simplifying my question a bit, so hopefully, it makes sense.

    I have three rasters that I'm working with. 

    LC <- raster("finalRRaster2018.tif")
nativeAET <- raster('kohala_aet_mm_ann_int.tif')
nonnativeAET <- raster('pred_AET.tif`)

    The raster LC is a raster with landcover attribute values 1 being native landcover and 0 being non-native landcover.

    The nativeAET and nonnativeAET are rasters of evapotranspiration under native species and nonnative species respectively. Both rasters have attributes between [0,15000]

    What id like to do is replace all the values of 1 with the nativeAET values and all the values of 0 with the nonnativeAET values.

    My idea is to transform the landcover raster (with values 1 or 0) to an AET raster (with values between 0 and 15000).

    I used the following code 

    native <- values(LC) == 1
nonative <- values(LC) ==0
LC[native] <- values(nativeAET)[native]
LC[nonnative] <- values(nonnativeAET)[nonnative]

    but when I plot my graphs, i get the warning number of items to replace is not a multiple of replacement lengthnumber of items to replace is not a multiple of replacement length

    and the plot looks something like this,

    enter image description here

    My problem is I would expect the resulting plot to not look as chunky. I feel like the rasters are not aligning properly but when i check the extent and dimensions of the rasters, they all seem to share the same projections.

    Any help is greatly appreciated!

  • Speeding up data scraping and mysql inserting

    I am currently scraping a website tat provides a table of data. The structure would be as follows

    <table>
 <tr> #This is the first row
   <td> data 1 </td>
   .....
 </tr>
 ....
</table>

    Let's say in the end there is a table with 20 rows and 10 columns. My script has to go from one table to the next, being between 100 and 1000 tables.

    So, with xpath I locate each row, insert its data in 2 tables, and go to the next one. A pseudocode would be

    for table in tables: #Between 100 and 1000 tables
  for row in table:
    Here I get from the row each td tag and returns a list
    Insert in table 1 half of the data, and get the id of the row inserted
    insert in table 2 the other half with the id of the first table row, to link both.

    I´ve been timing it to see why and where this takes that long and I got the following

    Overall table time 16 seconds
  Getting the data and generating the list for one row 0,453 secs

  Inserting data in table 1 0,006 secs
  Inserting data in table 2 0,0067 secs

    This means that if I have to scrape all 1000 tables that would take me more than 10 hours, which is way too much time, considering that when I used beautiful soup overall time was between half an hour and 1:30h.

    Seeing the problem is in getting text data from each td tag in each row, is there any way to speed it up? Esentially what I am doing in that part of the script is

    data_in_position_1=row.find_element_by_xpath('.//td[1]').text
.....
data_in_position_15=row.find_element_by_xpath('.//td[15]').text

list=[data_in_position_1,.....,data_in_position_15]

return list

    Well, I don´t know if scraping the whole table at once, or a different approach will show different results, but I need some way to speed this up.

    Thanks

  • How can I get URL of next page of a .asp page?

    I am trying to scrape the data at this link: https://thereserve2.apx.com/myModule/rpt/myrpt.asp?r=112

    I am able to get the first page, but when I get to next page (using the "next" button at bottom of page) the URL to the next page is generic: https://thereserve2.apx.com/myModule/rpt/myrpt.asp

    How can I get a more specific URL to use in my automated scraping effort?

    Thanks.

  • How to use a selector to get the second and third element?

    I need a way to get the value of the attribute xodd ( tzccftzxt, czofczec, az9faz8p ).

    This is the html code:

    <tr class=" deactivate" xeid="A1nOsGN0">
    <td class="table-time datet t1523797200-8-1-0-0 ">15/04, 15:00</td>
    <td class="name table-participant"><a href="/soccer/italy/serie-a/ac-milan-napoli-A1nOsGN0/">AC Milan - <span class="bold">Napoli</span></a></td>
    <td class="center bold table-odds table-score">0:0</td>
    <td class="odds-nowrp" xoid="E-2r00txv464x0x6kc61" xodd="tzccftzxt"><a href="" onclick="globals.ch.togle(this , 'E-2r00txv464x0x6kc61');return false;" xparam="odds_text">4.24</a></td>
    <td class="result-ok odds-nowrp" xoid="E-2r00txv498x0x0" xodd="czofczec"><a href="" onclick="globals.ch.togle(this , 'E-2r00txv498x0x0');return false;" xparam="odds_text">3.53</a></td>
    <td class="odds-nowrp" xoid="E-2r00txv464x0x6kc62" xodd="az9faz8p"><a href="" onclick="globals.ch.togle(this , 'E-2r00txv464x0x6kc62');return false;" xparam="odds_text">1.87</a></td>
    <td class="center info-value">5</td>
</tr>

    and this is my python code:

     for partita in response.css('tr.deactivate'):

        yield {
            '1': partita.css('td.odds-nowrp ::attr(xodd)').extract_first(),
            'X': partita.css('td.odds-nowrp:nth-child(1) ::attr(xodd)').extract_first(),
            '2': partita.css('td.odds-nowrp:nth-child(2) ::attr(xodd)').extract_first(),
        }

    But I only get:

    1: tzccftzxt
X: None
2: None

    How can I get the second and the third value?

  • R: parsing data into a table after webscraping

    I am able to webscrape some data but I am having problems formatting it in a table containing four columns, empty column, three columns.

    library("methods")
library(rvest)

page <- read_html("https://www.galmarley.com/prices/CSV/AUX/USD/600/Full")
page
sources <- page %>%html_text()
as.data.frame(sources)

    Thank you for you help.

  • Scraping HTML webpage using R

    I am scraping JFK's website to get flight schedules. The link to the flight schedules is here;

    http://www.flightview.com/airport/JFK-New_York-NY-(Kennedy)/departures

    To begin with, I am inspecting the one of the fields of any given flight and noting down its xpath. Idea is to see the output and then develop the code from there. This is what I have so far:

    library(rvest)

Departure_url <- read_html('http://www.flightview.com/airport/JFK-New_York-NY-(Kennedy)/departures')

Departures <- Departure_url %>% html_nodes(xpath = '//*[@id="ffAlLbl"]') %>% html_text()

    I am getting an empty character object as output for 'Departures' object in the code above.

    I am not sure why this happens. I am looking for a node through which the entire schedule can be downloaded.

    Any help is appreciated !!

  • Scraping hidden content in onclick argument with rvest

    i'm relatively new to web scraping but i'm able to make progess step by step. I'm using RStudio in connection with the scraping package rvest. I have the task to scrape the number of likes and dislikes on the following page:

    Link

    I used selectorgadget in order to find the right css pattern and was able to figure out the position of the desired content which is marked in the following picture and seems to be hidden in the onclick statement. enter image description here

    I was not able to extract the information out of this onclick statement although trying several approaches. For example the outcome of the number of likes for the first comment submitted by Bernhard Klein-Schmeink should be two and the corresponding dislikes equals to zero. This is a serious problem since the normal method i used before for example

       title.article.node <- topagrar.html %>% 
  html_nodes(".entry-title") %>% 
  html_text()

    in order to extract the title of the article by searching for the suitable css pattern does not work anymore. I would be very glad if somebody could help me even a small hint were to look up this topic would be very helpful

    Thank your very much