Directions: Tidying and Transforming Data Assignment: Working with HTML and JSON This is a warm up exercise to help you to get more familiar with the HTML and JSON file formats, and using packages to read these data formats for downstream use in R data frames. In the next two class weeks, we’ll be loading these file formats from the web, using web scraping and web APIs.
Select three books on a single subject of your choice. At least one book must have multiple authors. For each book, record the title, authors, and two to three additional attributes of your choosing (e.g., publication year, publisher, ISBN, genre, rating).
Using this information, manually create two data files:
An HTML file containing a table with the book data (e.g., books.html)
A JSON file representing the same data (e.g., books.json)
Unless you are already very comfortable with these formats, you are expected to create both files by hand to better understand their structure.
Next, write R code (using packages of your choice) to: 1. Load the HTML data into an R data frame 2. Load the JSON data into a separate R data frame 3. Compare the two data frames and determine whether they are identical
Deliverables 1. books.html 2. books.json 3. R code that loads and compares both data sources
Package your complete solution in a Quarto (.qmd) file and publish it to rpubs.com.
Introduction
Both datasets were created in notepad. For JSON I used JSONLint to help format. For HTML I used Static.App to assist with formatting and see in real time what the code looks like on a webpage. For more details see the citations.
Tackling the problem: First, I will review the basics of how to code in html and json. I will review the resources provided this week including the core reading and web data in r video. I’ll choose the books and information and then will first try to write the information up in visual studio, then if that isn’t working move to using notepad with free online resources JSONLint and Static App to check the code’s formatting. Then I’ll try to upload the files saved as .html and .json into this file.
Anticipated data challenges: I have never used JSON and am only slightly familiar with HTML coding, so it will be challenging to write in two different coding structures.
#Not shown: Location on computer where I am grabbing the data and creating a saved location
Packages
Code
#Install packages needed pacman::p_load(# Package Install and Management pacman, # package install/load janitor, #clean up data names # Project and File Management readr, # import data httr, #github passkey usethis, #r environment RPostgres, #SQL DBI, #SQL jsonlite, #working with json rvest, #html files rstuioapi, #api knitr, #displaying data in quarto# General Data Management dplyr, # data management tidyr, # data management skimr, # data management lubridate, # work with dates zoo, # work with dates tidyverse, # work with dates scales, # ggplot gmodels, # freq tables SAS patchwork #data visualization, side by side )
loading the HTML and JSON data, looking at properties
Code
#1. Load the HTML data into an R data frame #2. Load the JSON data into a separate R data frame#Load data#html filebooks_html <-file.path(csv_location, "books.html")books_html <-read_html(books_html)#JSON FILE books_json <-fromJSON(file.path(csv_location, "books.json"))#inspect code str(books_html)
List of 2
$ node:<pointer: 0x00000290988fea80>
$ doc :<pointer: 0x000002908ffff680>
- attr(*, "class")= chr [1:2] "xml_document" "xml_node"
Code
str(books_json)
List of 2
$ heading:List of 2
..$ id : chr "first"
..$ text: chr "literary classics"
$ books :'data.frame': 3 obs. of 4 variables:
..$ title : chr [1:3] "Frankenstein" "The Metamorphosis" "In Cold Blood"
..$ authors : chr [1:3] "Mary Shelley" "Franz Kafka & Stanley Corngold (translator)" "Truman Capote"
..$ genre : chr [1:3] "gothic/sci fi" "existentialism" "true crime"
..$ goodreads_rating: chr [1:3] "3.92" "3.91" "4.09"
Code
# List the top-level fields and the fields inside properties.names(books_json)
[1] "heading" "books"
Code
names(books_json$books)
[1] "title" "authors" "genre" "goodreads_rating"
Code
str(books_json$books, max.level =1) # Inspect one level of the nested structure.
'data.frame': 3 obs. of 4 variables:
$ title : chr "Frankenstein" "The Metamorphosis" "In Cold Blood"
$ authors : chr "Mary Shelley" "Franz Kafka & Stanley Corngold (translator)" "Truman Capote"
$ genre : chr "gothic/sci fi" "existentialism" "true crime"
$ goodreads_rating: chr "3.92" "3.91" "4.09"
Code
names(books_html)
[1] "node" "doc"
Code
names(books_html$doc)
NULL
Code
str(books_html$doc, max.level =1) # Inspect one level of the nested structure.
<pointer: 0x000002908ffff680>
Comparing the data frames
#JSON vs HTML
Question
JSON
HTML
Typical source
API or data download
web page
Import
jsonlite::fromJSON()
rvest::read_html()
Main challenge
nested structure
element selection
Typical result
list or tibble
document tree
Code
# 3. Compare the two data frames and determine whether they are identical#reshaping into a tidy analysis table#used claude for help here https://claude.ai/share/d5c87c61-b4b8-4ee8-9f5f-6dd67f7855e0 ######JSON#Use `unnest_wider()` when one nested object contains several named attributes that belong in separate columns.#The `courses` list-column contains a vector of courses per student. `unnest_longer()` creates one row for each course and repeats the students other values.#Use `unnest_longer()` when a nested vector or array contains repeated items. The result has one observation per student-course combination.#first store nested information and course vectors.books_json_vector <-tibble(heading =list(books_json$heading),books =list(books_json$books))#create df books_json_df <- books_json_vector %>%unnest_wider(heading) |># headingunnest_longer(books) |># one row per bookunnest_wider(books) |># title, authors, genre, ratingmutate(goodreads_rating =as.numeric(goodreads_rating))#####html #extract books_html_df <-tibble(id = books_html |>html_element("h1") |>html_attr("id"),text = books_html |>html_element("h1") |>html_text2(),line = books_html |>html_elements("p") |>html_text2())#split the lines into columnsbooks_html_df <- books_html_df %>%slice(-1) %>%# drop the header lineseparate_wider_delim( line, delim =",",names =c("title", "authors", "genre", "goodreads_rating"))%>%mutate(across(title:goodreads_rating, str_trim), # remove spaces after commasgoodreads_rating =as.numeric(goodreads_rating) )
Both files when first ingested had a nested structure. When looking at the fields and properties, the JSON file had a clearer nested structure. The HTML file’s fields and properties seemed more difficult to understand, for instance the names(books_html) came back “node” and “doc.” This makes sense since in this format we are working with less of a structure, and elements rather than nested structure.
Once the files were reshaped we see that they can be shaped into the same format.
Conclusion
I found that writing code was easier in HML format, but when it came to ingesting and reshaping with R, using JSON and the clearer nested data was easier to work with.
Instead of creating my own files, I would like to use public APIs and data scraping to read in data through a url.