Week7

Assignment – Working with XML and JSON in R

Pick three of your favorite books on one of your favorite subjects. At least one of the books should have more than one author. For each book, include the title, authors, and two or three other attributes that you find interesting. Take the information that you’ve selected about these three books, and separately create three files which store the book’s information in HTML (using an html table), XML, and JSON formats (e.g. “books.html”, “books.xml”, and “books.json”)… Write R code, using your packages of choice, to load the information from each of the three sources into separate R data frames. Are the three data frames identical?

Load libraries

library(RCurl)
library(xml2)
library(XML)
library(htmltab)

## Warning: package 'htmltab' was built under R version 4.0.4

library(jsonlite)
library(tibble)
library(tidyverse)

## -- Attaching packages --------------------------------------- tidyverse 1.3.0 --

## v ggplot2 3.3.3     v dplyr   1.0.4
## v tidyr   1.1.2     v stringr 1.4.0
## v readr   1.4.0     v forcats 0.5.1
## v purrr   0.3.4

## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x tidyr::complete() masks RCurl::complete()
## x dplyr::filter()   masks stats::filter()
## x purrr::flatten()  masks jsonlite::flatten()
## x dplyr::lag()      masks stats::lag()

Define connections

htmlFile = "https://raw.githubusercontent.com/dburtsev/CUNYR/master/books.html"
jsonFile = "https://raw.githubusercontent.com/dburtsev/CUNYR/master/books.json"
xmlFile = "https://raw.githubusercontent.com/dburtsev/CUNYR/master/books.xml"

Load data into R

var_html = htmltab(doc = htmlFile, which = 1)
var_json = jsonlite::fromJSON(jsonFile)
var_xml = read_xml(xmlFile)

Display data from html

class(var_html)

## [1] "data.frame"

head(var_html)

##                       title                    authors          ISBN price
## 2 Data Science for Business Foster Provost,Tom Fawcett    1449361323   $37
## 3    Doing Math with Python                  Amid Saha    1593276400   $21
## 4        T-SQL Fundamentals              Itzik Ben-Gan 9781509302000   $41

Display data from json

class(var_json)

## [1] "data.frame"

head(var_json)

##                       title                     authors          ISBN price
## 1 Data Science for Business Foster Provost, Tom Fawcett    1449361323   $37
## 2    Doing Math with Python                   Amid Saha    1593276400   $21
## 3        T-SQL Fundamentals               Itzik Ben-Gan 9781509302000   $41

Display data from xml

class(var_xml)

## [1] "xml_document" "xml_node"

xml_name(var_xml)

## [1] "books"

xml_text(var_xml)

## [1] "Data Science for Business\"Foster ProvostTom Fawcett1449361323$37Doing Math with PythonAmid Saha1593276400$21T-SQL FundamentalsItzik Ben-Gan9781509302000$41"

xml_structure(var_xml)

## <books>
##   <book>
##     <title>
##       {text}
##     <authors>
##       <author>
##         {text}
##       <author>
##         {text}
##     <ISBN>
##       {text}
##     <price>
##       {text}
##   <book>
##     <title>
##       {text}
##     <authors>
##       <author>
##         {text}
##     <ISBN>
##       {text}
##     <price>
##       {text}
##   <book>
##     <title>
##       {text}
##     <authors>
##       <author>
##         {text}
##     <ISBN>
##       {text}
##     <price>
##       {text}

Convert xml to data frame

doc = xmlParse(var_xml)
df = xmlToDataFrame(getNodeSet(doc, "//books/book"))
class(df)

## [1] "data.frame"

head(df)

##                        title                   authors          ISBN price
## 1 Data Science for Business" Foster ProvostTom Fawcett    1449361323   $37
## 2     Doing Math with Python                 Amid Saha    1593276400   $21
## 3         T-SQL Fundamentals             Itzik Ben-Gan 9781509302000   $41

Conclusion

We can load data from HTML, JSON, and XML format into R data frame. Data frames are not the same, there is double-quote at the end of the title ‘Data Science for Business’ from XML and authors are processed differently. The data needs to be clean up.