CODEWITHSUNDEEP
htmlr
PiyushGulati
PiyushGulati

Reputation: 21

Cleaning HTML Data in R

I am not able to remove symbols like asterix and some whitespaces using gsub. Please check out the following code:-

library(rvest)
library(plyr)
url = "http://www.statcan.gc.ca/pub/11-626-x/2016056/tbl/tbl01-eng.htm"
page = read_html(url)
df.list = page %>% 
    html_nodes(".table-responsive") %>% 
    html_nodes("table") %>% 
    html_table(fill=TRUE)
str(df.list)
df = df.list[[1]]
df = df[,-1] 
df[,2] = gsub("Note","", df[,2])
df[,2] = gsub("*","", df[,2])

Upvotes: 2

Views: 250

Answers (2)

hrbrmstr
hrbrmstr

Reputation: 78832

It's pretty straightforward:

library(rvest)
library(dplyr)

URL <- "http://www.statcan.gc.ca/pub/11-626-x/2016056/tbl/tbl01-eng.htm"
pg <- read_html(URL)

pg %>% 
    html_nodes(".table-responsive") %>% 
    html_nodes("table") %>% 
    html_table(fill=TRUE) -> df

This get's rid of the cruft at the top and bottom, then makes saner column names.

df <- setNames(df[[1]][-c(1:2,154),], c("cip_code", "field_of_study",
                                        "college_men", "college_women", 
                                        "bachelors_men", "bachelors_women",
                                        "masters_men", "masters_women"))

We'll use this in a second, but it removes everything but numbers from a character vector:

make_numeric <- function(x) { as.numeric(gsub("[^0-9]", "", x)) }

Now, we run that over each column (except the first two).

df <- mutate_each(df, funs(make_numeric), -cip_code, -field_of_study)

Boom. Done.

glimpse(df)

## Observations: 151
## Variables: 8
## $ cip_code        (chr) "1.00", "1.03", "1.06", "3.01", "3.02", "3.05", "3.06", "4.02", "4.03", "4.09", "5.01", "9.01...
## $ field_of_study  (chr) "Agriculture, general", "Agricultural production operations", "Applied horticulture/horticult...
## $ college_men     (dbl) 54095, 48329, 58948, 73514, 67374, 63693, 61992, NA, NA, 69677, NA, 67594, 59027, 64652, 5856...
## $ college_women   (dbl) NA, NA, 38855, NA, 60126, 47135, NA, NA, NA, 53502, NA, 53607, 55221, 57860, 51968, 46424, 53...
## $ bachelors_men   (dbl) 71610, NA, NA, 79448, NA, 72277, NA, 78738, 84319, NA, NA, 71466, 73217, NA, NA, NA, 80290, N...
## $ bachelors_women (dbl) 59829, NA, NA, 65014, NA, NA, NA, 64290, 66414, NA, 56851, 61712, 67580, 64610, NA, NA, 63534...
## $ masters_men     (dbl) NA, NA, NA, 86788, NA, NA, NA, 84446, 90274, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 9560...
## $ masters_women   (dbl) NA, NA, NA, 76566, NA, NA, NA, NA, 74163, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 77776, ...

Upvotes: 1

simch
simch

Reputation: 11

Asterix is a special character in grep, so you have to place two backslashes before it. Example:

gsub("\\*","", df[,2])

If you also want to remove whitespaces use this:

gsub("[[:blank:]|\\*]","", df[,2])

Upvotes: 0

Related Questions