How to flatten json object into a dataframe in r

Question

I am trying to flatten a complex json object json$suggestions$events$ticket_availability into a dataframe 'Tickets'.

I have tried various methods including each of:

fromJSON((tmp[[1]][,2]), flatten=TRUE)
tmp %>%
  map(~ fromJSON(.x)) %>%
  bind_rows()

to stackoverflow.com/questions/11553592/…. None seem to work for me.


library(httr)
library(rvest)
library(dplyr)
library(magrittr)
library(stringr)
library(lubridate)
library(purrr)
library(jsonlite)

getYear = "2019"
getWeek = "31"


base_url = "https://www.eventbrite.com/d/poland--pozna%C5%84/conference/"
query_params = list(yr=getYear, wk=getWeek)

resp <- GET(url=base_url, query=query_params)

resp

body_tags <- read_html(resp) %>% 
  html_nodes('body') %>% 
  html_text() %>% 
  toString() # to produce a single character string describing an R object.

# str_match_all - Extract matched groups from a string.
# output - a list of character matrices
# search window Server data for all items
tmp <- str_match_all(body_tags,'window.__SERVER_DATA__ = (.*?);')  

# Convert R objects from JSON - output - list
json <- jsonlite::fromJSON(tmp[[1]][,2])
str(json)

Tickets <- json$suggestions$events$ticket_availability

Here is the dput for the dataframe 'Tickets`

structure(list(is_sold_out = c(FALSE, TRUE, FALSE, FALSE, FALSE, 
FALSE, FALSE, TRUE, FALSE, FALSE), has_available_tickets = c(TRUE, 
FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE), minimum_ticket_price = structure(list(
    currency = c("EUR", "USD", "PLN", "PLN", "USD", "USD", "USD", 
    "USD", "CAD", "USD"), value = c(25946L, 28880L, 25530L, 4900L, 
    4406L, 0L, 28000L, 0L, 1000L, 28000L), major_value = c("259.46", 
    "288.80", "255.30", "49.00", "44.06", "0.00", "280.00", "0.00", 
    "10.00", "280.00"), display = c("259.46 EUR", "288.80 USD", 
    "255.30 PLN", "49.00 PLN", "44.06 USD", "0.00 USD", "280.00 USD", 
    "0.00 USD", "10.00 CAD", "280.00 USD")), class = "data.frame", row.names = c(NA, 
10L)), is_free = c(FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, TRUE, FALSE, FALSE), maximum_ticket_price = structure(list(
    currency = c("EUR", "USD", "PLN", "PLN", "USD", "USD", "USD", 
    "USD", "CAD", "USD"), value = c(49077L, 36702L, 25530L, 9911L, 
    4406L, 15684L, 28000L, 0L, 1000L, 28000L), major_value = c("490.77", 
    "367.02", "255.30", "99.11", "44.06", "156.84", "280.00", 
    "0.00", "10.00", "280.00"), display = c("490.77 EUR", "367.02 USD", 
    "255.30 PLN", "99.11 PLN", "44.06 USD", "156.84 USD", "280.00 USD", 
    "0.00 USD", "10.00 CAD", "280.00 USD")), class = "data.frame", row.names = c(NA, 
10L))), class = "data.frame", row.names = c(NA, 10L))

I would like to flatten minimum_ticket_price and maximum_ticket_price to create the dataframe Tickets.

take a look at the `jsonlite` package. https://stackoverflow.com/questions/2617600/importing-data-from-a-json-file-into-r — GordonShumway, Aug 01 '19 at 18:53
We shouldn't need to guess what packages have those functions. Learn to include `library` calls at the top of you code blocks. — IRTFM, Aug 01 '19 at 18:56

score 0 · Answer 1 · answered Dec 13 '19 at 00:33

Either flatten on import as you tried, but note that the new column names will all begin with the words "ticket_availability." rather than being nested under a single column.

json <- jsonlite::fromJSON(tmp[[1]][,2], flatten = TRUE)

Tickets_flat <- json$suggestions$events %>% 
  select(starts_with("ticket_availability"))

Or flatten using the jsonlite::flatten command (noting that purrr also has a flatten command which does not work in the same way)

Tickets_flat <- jsonlite::flatten(Tickets)

Also see https://stackoverflow.com/a/35497845/4241780 for further information.

How to flatten json object into a dataframe in r

1 Answers1

Linked