When data comes in a nasty arrangement, the automatic processing done by jsonlite::fromJSON may not be the best choice. By looking at the basic list version, we can come up with a function to turn one element into a data row.
lresult <- content(GET(url), as = "parsed", simplifyDataFrame = FALSE)
str(lresult[["values"]][[1]])
# List of 4
# $ kpi : chr "N00945"
# $ municipality: chr "1860"
# $ period : int 1996
# $ values :List of 1
# ..$ :List of 4
# .. ..$ count : int 1
# .. ..$ gender: chr "T"
# .. ..$ status: chr ""
# .. ..$ value : NULL
So each observation is broken into two lists. We can make a data frame from each, then bind the columns together. We also have to convert the NULL to NA when it pops up.
process_value_list <- function(vlist) {
basics <- as_data_frame(vlist[c("kpi", "municipality", "period")])
values <- vlist[["values"]][[1]]
if (is.null(values[["value"]])) {
values[["value"]] <- NA
}
details <- as_data_frame(values)
cbind(basics, details)
}
process_value_list(lresult[["values"]][[1]])
# kpi municipality period count gender status value
# 1 N00945 1860 1996 1 T NA
Since that works, we can loop over the whole list and combine the results.
dframe <- lresult[["values"]] %>%
lapply(process_value_list) %>%
bind_rows()
dframe
# kpi municipality period count gender status value
# 1 N00945 1860 1996 1 T NA
# 2 N00945 1860 1997 1 T NA
# 3 N00945 1860 1998 1 T 34.68172
# 4 N00945 1860 1999 1 T 34.59331
# 5 N00945 1860 2000 1 T 37.69174
# 6 N00945 1860 2001 1 T 40.26130
# ...