Webscraping with R – from messy & unstructured to blisfully tidy
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
I stopped caring about the estimates that occasionally raid the internet about how much time data scientists spend on data wrangling VS modelling. The answer is: probably a lot and likely more than originally planned (probably indicating here dependency on the state and richness of input data and the intended application for it, ekhem).
Still, the right tools can go a long way in achieving the desired result in the time frame that can surprise even the most optimistic of us. Needless to say, R is an excellent example of that right tool :).
Goal
I want to be able to analyse the data on annual CO2 emissions per person for 130 nations worldwide published by Food and Agriculture Organization of the United Nations (FAO), that are nicely summarised on nu3 website.
Challenge
The data resides in the HTML table that has notoriously messy headers. Look and weep:
But fear not! There’s nothing that R can’t fix in a blink of an eye. And here’s how:
Solution
First, load a handful of classic R packages:
{rvest}for web-scraping{dplyr}for data-wrangling{tidyr}for data transformation{stringr}for string manipulation{janitor}for clean headers that your OCD will love you for
library(rvest) library(dplyr) library(tidyr) library(stringr) library(janitor)
Next, scrape the content of the website and extract the HTML table:
url <- "https://www.nu3.de/blogs/nutrition/food-carbon-footprint-index-2018"
# scrape the website
url_html <- read_html(url)
# extract the HTML table
whole_table <- url_html %>%
html_nodes('table') %>%
html_table(fill = TRUE) %>%
.[[1]]
str(whole_table)
## 'data.frame': 133 obs. of 27 variables:
## $ X1 : chr "" "" "#" "1" ...
## $ X2 : chr "" "" "Country" "Argentina" ...
## $ X3 : chr "Animal Products" "" "" "10.51" ...
## $ X4 : chr "Animal Products" "" "" "37.20" ...
## $ X5 : chr "Animal Products" "" "" "38.66" ...
## $ X6 : chr "Animal Products" "" "" "41.53" ...
## $ X7 : chr "Animal Products" "" "" "55.48" ...
## $ X8 : chr "Animal Products" "" "" "1712.00" ...
## $ X9 : chr "Animal Products" "" "" "1.56" ...
## $ X10: chr "Animal Products" "" "" "54.63" ...
## $ X11: chr "Animal Products" "" "" "4.36" ...
## $ X12: chr "Animal Products" "" "" "6.96" ...
## $ X13: chr "Animal Products" "" "" "11.39" ...
## $ X14: chr "Animal Products" "" "" "10.46" ...
## $ X15: chr "Animal Products" "" "" "195.08" ...
## $ X16: chr "Animal Products" "" "" "277.87" ...
## $ X17: chr "Animal Products" "" "" "2140.65" ...
## $ X18: chr "Non-Animal Products" "" "" "103.11" ...
## $ X19: chr "Non-Animal Products" "" "" "19.66" ...
## $ X20: chr "Non-Animal Products" "" "" "8.77" ...
## $ X21: chr "Non-Animal Products" "" "" "11.22" ...
## $ X22: chr "Non-Animal Products" "" "" "0.00" ...
## $ X23: chr "Non-Animal Products" "" "" "0.00" ...
## $ X24: chr "Non-Animal Products" "" "" "0.49" ...
## $ X25: chr "Non-Animal Products" "" "" "0.87" ...
## $ X26: chr "Non-Animal Products" "" "" "31.75" ...
## $ X27: chr "Animal vs. Non-Animal Products:" "Difference - Kg CO2/person/year" "" "2108.90" ...
The state of column names can be summarised in only one word here: messmessmessmessmessmessmessmess….!!
Before we tidy them up, let’s scrap the headers and isolate just the content of the table:
# tidying the table table_content <- whole_table %>% select(-X1) %>% # remove redundant column filter(!dplyr::row_number() %in% 1:3) # remove redundant rows head(table_content) ## X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 ## 1 Argentina 10.51 37.20 38.66 41.53 55.48 1712.00 1.56 54.63 4.36 ## 2 Australia 24.14 85.44 46.12 49.54 33.86 1044.85 9.87 345.65 17.69 ## 3 Albania 10.88 38.51 13.23 14.21 22.50 694.30 15.32 536.50 3.85 ## 4 Iceland 21.69 76.77 26.87 28.86 13.36 412.26 21.12 739.62 74.41 ## 5 New Zealand 22.29 78.90 34.98 37.58 22.49 693.99 18.91 662.23 20.36 ## 6 USA 27.64 97.83 50.01 53.72 36.24 1118.29 0.43 15.06 12.35 ## X12 X13 X14 X15 X16 X17 X18 X19 X20 X21 X22 ## 1 6.96 11.39 10.46 195.08 277.87 2140.65 103.11 19.66 8.77 11.22 0.00 ## 2 28.25 8.51 7.82 234.49 334.01 1895.55 70.46 13.44 11.03 14.12 0.19 ## 3 6.15 12.45 11.44 303.72 432.62 1733.73 138.64 26.44 7.78 9.96 0.00 ## 4 118.81 8.24 7.57 225.82 321.66 1705.55 72.92 13.91 3.89 4.98 0.11 ## 5 32.51 9.91 9.10 137.25 195.50 1709.80 76.91 14.67 9.16 11.72 0.44 ## 6 19.72 14.58 13.39 254.69 362.78 1680.79 80.43 15.34 6.88 8.80 0.04 ## X23 X24 X25 X26 X27 ## 1 0.00 0.49 0.87 31.75 2108.90 ## 2 0.09 8.73 15.45 43.09 1852.46 ## 3 0.00 4.36 7.72 44.11 1689.62 ## 4 0.05 3.88 6.87 25.80 1679.75 ## 5 0.20 8.22 14.55 41.14 1668.67 ## 6 0.02 7.86 13.91 38.07 1642.72
A bit better. It’s worth noting that we’re dealing with 26 variables that need column names. Now, how can we extract these?
When inspecting the HTML code behind the table (Right Click => Inspect => Navigate to the part of the code that highlights the table headers), you will notice that all the “icon headers” belong to thead-icon class that has an attribute title with the very column names that we’re interested in.
{rvest} package makes it super easy to extract these using html_nodes() and html_attr() functions:
raw_headers <- url_html %>%
html_nodes(".thead-icon") %>%
html_attr('title')
raw_headers[1:35]
## [1] "Schwein"
## [2] "Geflügel"
## [3] "Rind"
## [4] "Lamm- und Ziegenfleisch"
## [5] "Fisch"
## [6] "Eier"
## [7] "Milch - inkl. Käse"
## [8] "Durchschnitt Kg CO2/Person/Jahr"
## [9] "Weizen und Weizenerzeugnisse"
## [10] "Reis"
## [11] "Sojabohnen"
## [12] "Nüsse - inkl. Erdnussbutter"
## [13] "Durchschnitt Kg CO2/Person/Jahr"
## [14] "Supplied for Consumption (kg/person/year)"
## [15] "Kg CO2/person/year"
## [16] "Animal vs. Non-Animal Products: Difference - Kg CO2/person/year"
## [17] "Pork"
## [18] "Poultry"
## [19] "Beef"
## [20] "Lamb & Goat"
## [21] "Fish"
## [22] "Eggs"
## [23] "Milk - inc. cheese"
## [24] "Wheat and Wheat Products"
## [25] "Rice"
## [26] "Soybeans"
## [27] "Nuts inc. Peanut Butter"
## [28] "Supplied for Consumption (kg/person/year)"
## [29] "Kg CO2/person/year"
## [30] "Supplied for Consumption (kg/person/year)"
## [31] "Kg CO2/person/year"
## [32] "Supplied for Consumption (kg/person/year)"
## [33] "Kg CO2/person/year"
## [34] "Supplied for Consumption (kg/person/year)"
## [35] "Kg CO2/person/year"
As you can see, this way we extracted a few additional titles, but it’s easy to select the correct ones from here. First, let’s get the bottom-most names that refer to the actual column names:
tidy_bottom_header <- raw_headers[28:length(raw_headers)] tidy_bottom_header[1:10] ## [1] "Supplied for Consumption (kg/person/year)" ## [2] "Kg CO2/person/year" ## [3] "Supplied for Consumption (kg/person/year)" ## [4] "Kg CO2/person/year" ## [5] "Supplied for Consumption (kg/person/year)" ## [6] "Kg CO2/person/year" ## [7] "Supplied for Consumption (kg/person/year)" ## [8] "Kg CO2/person/year" ## [9] "Supplied for Consumption (kg/person/year)" ## [10] "Kg CO2/person/year"
Then, the middle ones:
raw_middle_header <- raw_headers[17:27] raw_middle_header ## [1] "Pork" "Poultry" ## [3] "Beef" "Lamb & Goat" ## [5] "Fish" "Eggs" ## [7] "Milk - inc. cheese" "Wheat and Wheat Products" ## [9] "Rice" "Soybeans" ## [11] "Nuts inc. Peanut Butter"
And finally, let’s organise them in a way that the column names reflect the variable order:
tidy_headers <- c( rep(raw_middle_header[1:7], each = 2), "animal_total", rep(raw_middle_header[8:length(raw_middle_header)], each = 2), "non_animal_total", "country_total") tidy_headers ## [1] "Pork" "Pork" ## [3] "Poultry" "Poultry" ## [5] "Beef" "Beef" ## [7] "Lamb & Goat" "Lamb & Goat" ## [9] "Fish" "Fish" ## [11] "Eggs" "Eggs" ## [13] "Milk - inc. cheese" "Milk - inc. cheese" ## [15] "animal_total" "Wheat and Wheat Products" ## [17] "Wheat and Wheat Products" "Rice" ## [19] "Rice" "Soybeans" ## [21] "Soybeans" "Nuts inc. Peanut Butter" ## [23] "Nuts inc. Peanut Butter" "non_animal_total" ## [25] "country_total"
I must say, this does the job but it is the part of the code that I’m least happy with, as it requires a lot of manual effort and thus is prone to errors. If you know better ways how to abstract/automate it, feel free to share it in the comments below!
Ok, over the first hurdle! Now, we want to retain the information about both, the type of food that the value refers too, as well as the metric that it describes (consumption or CO2 emmissions). Let’s combine the two and make them column names:
combined_colnames <- paste(tidy_headers, tidy_bottom_header, sep = ';')
colnames(table_content) <- c("Country", combined_colnames)
glimpse(table_content[, 1:10])
## Observations: 130
## Variables: 10
## $ Country <chr> "Argen...
## $ `Pork;Supplied for Consumption (kg/person/year)` <chr> "10.51...
## $ `Pork;Kg CO2/person/year` <chr> "37.20...
## $ `Poultry;Supplied for Consumption (kg/person/year)` <chr> "38.66...
## $ `Poultry;Kg CO2/person/year` <chr> "41.53...
## $ `Beef;Supplied for Consumption (kg/person/year)` <chr> "55.48...
## $ `Beef;Kg CO2/person/year` <chr> "1712....
## $ `Lamb & Goat;Supplied for Consumption (kg/person/year)` <chr> "1.56"...
## $ `Lamb & Goat;Kg CO2/person/year` <chr> "54.63...
## $ `Fish;Supplied for Consumption (kg/person/year)` <chr> "4.36"...
I know what you’re thinking - this is faaaar from perfect (or pretty, for that matter), but we’re only 2 lines of code away to having something much more digestible. Long live {tidyr} !!
long_table <- table_content %>%
# make column names observations of Category variable
tidyr::pivot_longer(cols = -Country, names_to = "Category", values_to = "Values") %>%
# separate food-related information from the metric
tidyr::separate(col = Category, into = c("Food Category", "Metric"), sep = ';')
glimpse(long_table)
## Observations: 3,250
## Variables: 4
## $ Country <chr> "Argentina", "Argentina", "Argentina", "Argent...
## $ `Food Category` <chr> "Pork", "Pork", "Poultry", "Poultry", "Beef", ...
## $ Metric <chr> "Supplied for Consumption (kg/person/year)", "...
## $ Values <chr> "10.51", "37.20", "38.66", "41.53", "55.48", "...
Almost there! I’m still not happy with the Metric variable that ideally should be split into two separate columns for an easier comparison of consumption and CO2 emissions between the countries:
tidy_table <- long_table %>%
tidyr::pivot_wider(names_from = Metric, values_from = Values) %>%
janitor::clean_names('snake')
glimpse(tidy_table)
## Observations: 1,820
## Variables: 4
## $ country <chr> "Argentina", "Argentin...
## $ food_category <chr> "Pork", "Poultry", "Be...
## $ supplied_for_consumption_kg_person_year <chr> "10.51", "38.66", "55....
## $ kg_co2_person_year <chr> "37.20", "41.53", "171...
Great stuff. Now we only need some final touches renaming the long column names and removing the instances of summarised data - these will be easy to calculate, if we need to, based on the final dataset:
final_table <- tidy_table %>%
rename(consumption = 3,
co2_emmission = 4) %>%
filter(!stringr::str_detect(food_category, "total"))
head(final_table, 20)
## # A tibble: 20 x 4
## country food_category consumption co2_emmission
## <chr> <chr> <chr> <chr>
## 1 Argentina Pork 10.51 37.20
## 2 Argentina Poultry 38.66 41.53
## 3 Argentina Beef 55.48 1712.00
## 4 Argentina Lamb & Goat 1.56 54.63
## 5 Argentina Fish 4.36 6.96
## 6 Argentina Eggs 11.39 10.46
## 7 Argentina Milk - inc. cheese 195.08 277.87
## 8 Argentina Wheat and Wheat Products 103.11 19.66
## 9 Argentina Rice 8.77 11.22
## 10 Argentina Soybeans 0.00 0.00
## 11 Argentina Nuts inc. Peanut Butter 0.49 0.87
## 12 Australia Pork 24.14 85.44
## 13 Australia Poultry 46.12 49.54
## 14 Australia Beef 33.86 1044.85
## 15 Australia Lamb & Goat 9.87 345.65
## 16 Australia Fish 17.69 28.25
## 17 Australia Eggs 8.51 7.82
## 18 Australia Milk - inc. cheese 234.49 334.01
## 19 Australia Wheat and Wheat Products 70.46 13.44
## 20 Australia Rice 11.03 14.12
Voila! We’ve got what we wanted with a few lines of code and basic knowledge of HTML. The whole data-wrangling process took only a fraction of what I expected it to take and now the data is tidy and ‘analysis-ready’.
Do you know any examples of tricky-to-tidy data? Make sure to share your knowledge - and frustrations, why not!
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.