library(dplyr)
library(tidyverse)
library(httr)
library(jsonlite)
library(rlist)I assigned the below http request to a local variable called url. My selection criteria is looking for books by the author Naomi Novik and which if any of her novels have made it to the NYT best sellers list. My second query parameter is the api-key assigned to me by NYT for authentication to use this API.
url <- "https://api.nytimes.com/svc/books/v3/lists/best-sellers/history.json?author=Naomi%20Novik&api-key=AaML1lfAaLB6s04LSukIZzGrUEkC4mrC"
api_key <- "AaML1lfAaLB6s04LSukIZzGrUEkC4mrC"I am utilizing a GET request to call the server to return a response per my query parameters and utilizing content() to examine response. Setting return format as text allows passing in the response to fromJSON function which converts into a named list for easier manipulation in R.
#query_params <- list(author="Naomi%20Novik")
resp <- GET(url)
#,add_headers('Authorization' = paste("Authorization: ", api_key))
resp<- content(resp, as ="text")
result <- fromJSON(resp, flatten = TRUE)
#db_result <- result %>% bind_rows() %>% mutate(results = rep(names(result), map_dbl(result, nrow)))
#db_result <- db_result %>% bind_rows()
db_result <- result$resultsI utilized unnest to include the columns and observations of the nested dataframes within results returned. This was done in order to review the full scope of results (i.e.ย all books and their respective weeks on best sellers list). Of the 6 published books under her name, 3 have made the best sellers list although only one has been reviewed by the New York Times.
db_result_long <- db_result %>% unnest_longer(ranks_history) %>% as.data.frame()
db_result_longer <- db_result_long %>% unnest_longer(reviews) %>% select(-contains("isbn")) %>% as.data.frame()
str(db_result_longer)## 'data.frame': 9 obs. of 10 variables:
## $ title : chr "A DEADLY EDUCATION" "A DEADLY EDUCATION" "CRUCIBLE OF GOLD" "SPINNING SILVER" ...
## $ description : chr "While others expect a dark sorceress to cause damage, she has other plans." "While others expect a dark sorceress to cause damage, she has other plans." "" "Miryem goes to collect debts owed to her father and winds up on a dangerous quest." ...
## $ contributor : chr "by Naomi Novik" "by Naomi Novik" "by Naomi Novik" "by Naomi Novik" ...
## $ author : chr "Naomi Novik" "Naomi Novik" "Naomi Novik" "Naomi Novik" ...
## $ contributor_note: chr "" "" "" "" ...
## $ price : chr "0.00" "0.00" "0.00" "0.00" ...
## $ age_group : chr "" "" "" "" ...
## $ publisher : chr "Del Rey" "Del Rey" "Del Rey" "Del Rey" ...
## $ ranks_history :'data.frame': 9 obs. of 11 variables:
## ..$ primary_isbn10 : chr "0593128494" "0593128486" "0345522869" "0399180982" ...
## ..$ primary_isbn13 : chr "9780593128497" "9780593128480" "9780345522863" "9780399180989" ...
## ..$ rank : int 10 12 19 8 12 5 4 33 28
## ..$ list_name : chr "Combined Print and E-Book Fiction" "Hardcover Fiction" "Hardcover Fiction" "Hardcover Fiction" ...
## ..$ display_name : chr "Combined Print & E-Book Fiction" "Hardcover Fiction" "Hardcover Fiction" "Hardcover Fiction" ...
## ..$ published_date : chr "2020-10-18" "2020-10-18" "2012-03-25" "2018-07-29" ...
## ..$ bestsellers_date: chr "2020-10-03" "2020-10-03" "2012-03-10" "2018-07-14" ...
## ..$ weeks_on_list : int 1 1 0 1 1 1 1 0 0
## ..$ rank_last_week : int 0 0 0 0 0 0 0 0 0
## ..$ asterisk : int 0 0 0 0 0 0 0 0 0
## ..$ dagger : int 0 0 0 0 0 0 0 0 0
## $ reviews :'data.frame': 9 obs. of 4 variables:
## ..$ book_review_link : chr "" "" "" "https://www.nytimes.com/2018/08/03/books/review/naomi-novik-spinning-silver.html" ...
## ..$ first_chapter_link : chr "" "" "" "" ...
## ..$ sunday_review_link : chr "" "" "" "" ...
## ..$ article_chapter_link: chr "" "" "" "" ...head(db_result_longer)## title
## 1 A DEADLY EDUCATION
## 2 A DEADLY EDUCATION
## 3 CRUCIBLE OF GOLD
## 4 SPINNING SILVER
## 5 SPINNING SILVER
## 6 THE LAST GRADUATE
## description
## 1 While others expect a dark sorceress to cause damage, she has other plans.
## 2 While others expect a dark sorceress to cause damage, she has other plans.
## 3
## 4 Miryem goes to collect debts owed to her father and winds up on a dangerous quest.
## 5 Miryem goes to collect debts owed to her father and winds up on a dangerous quest.
## 6 The second book in the Scholomance series. Will students now in their senior year make it out alive?
## contributor author contributor_note price age_group publisher
## 1 by Naomi Novik Naomi Novik 0.00 Del Rey
## 2 by Naomi Novik Naomi Novik 0.00 Del Rey
## 3 by Naomi Novik Naomi Novik 0.00 Del Rey
## 4 by Naomi Novik Naomi Novik 0.00 Del Rey
## 5 by Naomi Novik Naomi Novik 0.00 Del Rey
## 6 by Naomi Novik Naomi Novik 0.00 Del Rey
## ranks_history.primary_isbn10 ranks_history.primary_isbn13 ranks_history.rank
## 1 0593128494 9780593128497 10
## 2 0593128486 9780593128480 12
## 3 0345522869 9780345522863 19
## 4 0399180982 9780399180989 8
## 5 0399180982 9780399180989 12
## 6 0593128869 9780593128862 5
## ranks_history.list_name ranks_history.display_name
## 1 Combined Print and E-Book Fiction Combined Print & E-Book Fiction
## 2 Hardcover Fiction Hardcover Fiction
## 3 Hardcover Fiction Hardcover Fiction
## 4 Hardcover Fiction Hardcover Fiction
## 5 Combined Print and E-Book Fiction Combined Print & E-Book Fiction
## 6 Hardcover Fiction Hardcover Fiction
## ranks_history.published_date ranks_history.bestsellers_date
## 1 2020-10-18 2020-10-03
## 2 2020-10-18 2020-10-03
## 3 2012-03-25 2012-03-10
## 4 2018-07-29 2018-07-14
## 5 2018-07-29 2018-07-14
## 6 2021-10-17 2021-10-02
## ranks_history.weeks_on_list ranks_history.rank_last_week
## 1 1 0
## 2 1 0
## 3 0 0
## 4 1 0
## 5 1 0
## 6 1 0
## ranks_history.asterisk ranks_history.dagger
## 1 0 0
## 2 0 0
## 3 0 0
## 4 0 0
## 5 0 0
## 6 0 0
## reviews.book_review_link
## 1
## 2
## 3
## 4 https://www.nytimes.com/2018/08/03/books/review/naomi-novik-spinning-silver.html
## 5 https://www.nytimes.com/2018/08/03/books/review/naomi-novik-spinning-silver.html
## 6
## reviews.first_chapter_link reviews.sunday_review_link
## 1
## 2
## 3
## 4
## 5
## 6
## reviews.article_chapter_link
## 1
## 2
## 3
## 4
## 5
## 6#attempt to rename all column headers removing ranks_history prefix
db_result_longer <- rename_with(db_result_longer, ~ gsub("ranks_history.", "", .x, fixed = TRUE))#efforts made to corral nested dataframes to enable accessing and manipulation
rank_history <- db_result$rank_history
reviews <- db_result$reviews
titles <- db_result$title
names(reviews) <- titles
reviews <- as.data.frame(reviews)
reviews <- pivot_longer(reviews, cols=-1, names_to="vars", values_to="vals")raw_db <- enframe(unlist(result)) %>% slice(-c(1,2,3))credit for syntax structure: https://www.r-bloggers.com/2018/10/converting-nested-json-to-a-tidy-data-frame-with-r/
raw_wide <- raw_db %>% pivot_wider(names_from=name, names_prefix="", values_from=value)
raw_long <- raw_wide %>% pivot_longer(cols=1:6,names_to="title_placeholder",values_to="title")
raw_long <- raw_long %>% pivot_longer(cols=1:6,names_to="descr_placeholder",values_to="description")
raw_long <- raw_long %>% pivot_longer(cols=7:12,names_to="author_placeholder",values_to="author")raw_long <- raw_long %>% select(-contains(c("placeholder","isbn")))
raw_long <- raw_long %>% select(title, description, author, contains("published_date"), contains("bestsellers_date"), contains("book_review_link"))author <- db_result %>% select(title, description, author) %>% filter(!description=="")
history <- as.data.frame(db_result$ranks_history) %>% select(bestsellers_date, weeks_on_list) %>% filter(!weeks_on_list == 0) %>% distinct()
review <- as.data.frame(db_result$reviews) %>% select(book_review_link.2)#tying subsets back together but got stuck in how to tie back nested dataframes
db_result_clean <- db_result %>%
bind_cols(history) %>%
bind_cols(review) %>% select(-c(reviews,isbns,ranks_history))