jsonlite's fromJSON is returning a list of 2 lists instead of a df

Question

I'm following the FCC's documentation to download some metadata about proceedings.

I don't believe I can post the data but you can get a free API key.

My code results in a listed list of 2 lists instead of a structured df from the JSON format.

My goal is to have a dataframe where each json element is it's own column.. like a normal df.

library(httr)
library(jsonlite)

datahere = "C:/fcc/"
setwd(datahere)

URL <- "https://publicapi.fcc.gov/ecfs/filings?api_key=<KEY HERE>&proceedings.name=14-28&sort=date_disseminated,DESC"
dataDF <- GET(URL)
dataJSON <- content(dataDF, as="text")
dataJSON <- fromJSON(dataJSON)

# NAs
dataJSON2 <- lapply(dataJSON, function(x) {
  x[sapply(x, is.null)] <- NA
  unlist(x)
})

x <- do.call("rbind", dataJSON2)
x <- as.data.frame(x)

score 2 · Answer 1 · answered Jul 21 '16 at 22:32

The JSON is really deeply nested, so you need to put a little more thought into converting between list and data.frame. The logic below pulls out a data.frame of 25 filings (102 variables) and 10 aggregations (25 variables).

# tackle the filings object
filings_df <- ldply(dataJSON$filings, function(x) {
  # removes null list elements
  x[sapply(x, is.null)] <- NA
  # converts to a named character vector
  unlisted_x <- unlist(x)
  # converts the named character vector to data.frame
  # with 1 column and rows for each element
  d <- as.data.frame(unlisted_x)
  # we need to transpose this data.frame because 
  # the rows should be columns, and don't check names when converting
  d <- as.data.frame(t(d), check.names=F)
  # now assign the actual names based on that original 
  # unlisted character vector
  colnames(d) <- names(unlisted_x)
  # now return to ldply function, which will automatically stack them together
  return(d)
})

# tackle the aggregations object
# same exact logic to create the data.frame
aggregations_df <- ldply(dataJSON$aggregations, function(x) {
  # removes null list elements
  x[sapply(x, is.null)] <- NA
  # converts to a named character vector
  unlisted_x <- unlist(x)
  # converts the named character vector to data.frame
  # with 1 column and rows for each element
  d <- as.data.frame(unlisted_x)
  # we need to transpose this data.frame because 
  # the rows should be columns, and don't check names when converting
  d <- as.data.frame(t(d), check.names=F)
  # now assign the actual names based on that original 
  # unlisted character vector
  colnames(d) <- names(unlisted_x)
  # now return to ldply function, which will automatically stack them together
  return(d)
})

jsonlite's fromJSON is returning a list of 2 lists instead of a df

1 Answers1