Scraping a wikipedia table

Admin 1
Viewed 202

I scraped a wikipedia table using r

library(rvest)

url <- "https://en.wikipedia.org/wiki/New_York_City"
nyc <- url %>%
  read_html() %>%
  html_node(xpath = '//*[@id="mw-content-text"]/div/table[1]') %>%
  html_table(fill = TRUE)

And want to save the values into a new dataframe.

Output

Area           population
468.484 sq mi  8,336,817

What is the best way to do this?

web-scraping r
2 Answers

You need to choose which table. From the table select needed columns and rows. Assign column names using setNames and reset rownames by setting them to NULL. I'm sure you want population column as.integer, just use gsub before to clean out the non-digits.

I'm not sure about the html_node line and left it out.

library(rvest)

url <- "https://en.wikipedia.org/wiki/New_York_City"

nyc <- read_html(url)
# nyc <- html_node(nyc, xpath = '//*[@id="mw-content-text"]/div/table[1]')
nyc <- html_table(nyc, header=TRUE, fill = TRUE)

nyc <- `rownames<-`(
  setNames(nyc[[3]][-c(1:2, 10), 2:3], c("area", "population")), 
  NULL)
nyc <- transform(nyc, population=as.integer(gsub("\\D", "", population)))
nyc
#                area population
# 1             Bronx    1418207
# 2             Kings    2559903
# 3          New York    1628706
# 4            Queens    2253858
# 5          Richmond     476143
# 6  City of New York    8336817
# 7 State of New York   19453561
user_65740380

Judging from OP's example output they want the table given at a different xpath to that which they provided in the question. Please see the following workflow, note: names have been set manually to save the hassle of formatting the strings from rows: 

# Initialise package in session: rvest => .GlobalEnv()
library(rvest)

# Store the url scalar: url => character vector 
url <- "https://en.wikipedia.org/wiki/New_York_City"

# Scrape the table and store it memory: nyc => data.frame
nyc <- 
  url %>%
  read_html() %>%
  html_node(xpath = '/html/body/div[3]/div[3]/div[4]/div/table[3]') %>%
  html_table(fill = TRUE) %>% 
  data.frame()

# Set the names appropriately: names(nyc) character vector
names(nyc) <- c("borough", "county", "pop_est_2019", 
                "gdp_bill_usd", "gdp_per_cap", 
                "land_area_sq_mi", "land_area_sq_km", 
                "density_pop_sq_mi", "density_pop_sq_km")

# Coerce the vectors to the appropriate type: cleaned => data.frame
cleaned <- data.frame(lapply(nyc[4:nrow(nyc)-1,], function(x){ 
    if(length(grep("\\d+\\,\\d+$|^\\d+\\.\\d+$", x)) > 0){
      as.numeric(trimws(gsub("\\,", "", as.character(x)), "both"))
    }else{ 
      as.factor(x)
      }
    }
  )
)
user_92031580
Related