Using R to scrape a table and links from a web page

user_13048009 0
Viewed 241

I am trying scraping a website with R. I need the table and the links from that table associated with the correct row in the table. I can get the table and the links but because in the web table there are two columns with links and some rows in the table don't have links and the links can't be sorted and joined by the file names. I can't figure out how to create a dateframe with the columns and the links associated with the correct row.

library(rvest)

#Read HTML from EPA website 
content <- read_html("https://www.epa.gov/national-aquatic-resource-surveys/data-national-aquatic-resource-surveys")


tables <- content %>% 
          html_table(fill = TRUE) 
EPA_table <- tables[[1]]

#get links from table 
web <- content %>%
    html_nodes("table") %>% html_nodes("tr") %>% html_nodes("a") %>%
    html_attr("href") #as above
data-cleaning web-scraping dataframe r hyperlink
2 Answers

Use xpath= argument of to select columns.

## Data links
web <- content %>%
  html_nodes("table tr")%>%
  html_nodes(xpath="//td[3]") %>%  ## xpath
  html_nodes("a") %>%
  html_attr("href")

EPA_table$web1 <- web  ## add Data links column

## metadata links accordingly
web2 <- content %>%
  html_nodes("table tr") %>%
  html_nodes(xpath="//td[4]") %>%  ## xpath
  html_nodes("a") %>%
  html_attr("href")

The empty metadata cells can be set to NA, the description links fit where it's not NA then.

EPA_table[EPA_table$Metadata %in% "", "Metadata"] <- NA
EPA_table[!is.na(EPA_table$Metadata), "web2"] <- web2  ## add metadata column

Result

head(EPA_table)
# Survey         Indicator
# 1 Lakes 2007               All
# 2 Lakes 2007    Landscape Data
# 3 Lakes 2007   Water Chemistry
# 4 Lakes 2007 Visual Assessment
# 5 Lakes 2007  Site Information
# 6 Lakes 2007             Notes
# Data
# 1                               NLA 2007 All Data (ZIP)(1 pg, 5 MB)
# 2 NLA 2007 Basin Landuse Metrics - Data 20061022 (CSV)(1 pg, 307 K)
# 3               NLA 2007 Profile - Data 20091008 (CSV)(1 pg, 888 K)
# 4     NLA 2007 Visual Assessment - Data 20091015 (CSV)(1 pg, 813 K)
# 5      NLA 2007 Site Information - Data 20091113 (CSV)(1 pg, 980 K)
# 6                   National Lakes Assessment 2007 Final Data Notes
# Metadata
# 1                                                                <NA>
#   2 NLA 2007 Basin Landuse Metrics - Metadata 20091022 (TXT)(1 pg, 4 K)
# 3             NLA 2007 Profile - Metadata 20091008 (TXT)(1 pg, 650 B)
# 4     NLA 2007 Visual Assessment - Metadata 10091015 (TXT)(1 pg, 7 K)
# 5      NLA 2007 Site Information - Metadata 20091113 (TXT)(1 pg, 8 K)
# 6                                                                <NA>
#   web1
# 1                                /sites/production/files/2017-02/nla2007_alldata.zip
# 2         /sites/production/files/2013-09/nla2007_basin_landuse_metrics_20061022.csv
# 3                       /sites/production/files/2013-09/nla2007_profile_20091008.csv
# 4              /sites/production/files/2014-01/nla2007_visualassessment_20091015.csv
# 5        /sites/production/files/2014-01/nla2007_sampledlakeinformation_20091113.csv
# 6 /national-aquatic-resource-surveys/national-lakes-assessment-2007-final-data-notes
# web2
# 1                                                                             <NA>
#   2  /sites/production/files/2013-09/nla2007_basin_landuse_metrics_info_20091022.txt
# 3              /sites/production/files/2013-09/nla2007_profile_info_20091008_0.txt
# 4       /sites/production/files/2014-01/nla2007_visualassessment_info_20091015.txt
# 5 /sites/production/files/2014-01/nla2007_sampledlakeinformation_info_20091113.txt
# 6                                                                             <NA>
user_65740380

I would have gone with css selectors and :nth-child to separate out individual columns from a loop over the table rows. By using tbody in the selector I would exclude the header row and only process the table body rows and pass that list to map_df

library(rvest)
library(purrr)

url <- "https://www.epa.gov/national-aquatic-resource-surveys/data-national-aquatic-resource-surveys"
rows <- read_html(url) %>% html_nodes("#narsdata tbody tr")

df <- map_df(rows, function(x) {
  data.frame(
    Survey = x %>% html_node("td:nth-child(1)") %>% html_text(),
    Indicator = x %>% html_node("td:nth-child(2)") %>% html_text(),
    Data = x %>% html_node("td:nth-child(3) a") %>% html_attr("href") %>% if_else(is.na(.), ., url_absolute(., url)),
    Metadata = x %>% html_node("td:nth-child(4) a") %>% html_attr("href") %>% if_else(is.na(.), ., url_absolute(., url)),
    stringsAsFactors = FALSE
  )
})

Don't think you really need the file names in addition to the urls, but if so, you can expand the data.frame with two additional columns and extract the html_text rather than html_attr e.g.

Data_Name = x %>% html_node("td:nth-child(3) a") %>% html_text(),
Metadata_Name = x %>% html_node("td:nth-child(4) a") %>% html_text()
user_62412350
Related