R converting a Json output to a dataframe

Question

I'm trying to convert a Json output to a data frame. The Json output is as follows

[[1]]
[[1]]$id
[1] "176248"

[[1]]$report_list_id
[[1]]$report_list_id[[1]]
[1] "183556"


[[1]]$name
[1] "Torchy's Tacos"


[[2]]
[[2]]$id
[1] "180642"

[[2]]$report_list_id
[[2]]$report_list_id[[1]]
[1] "188160"


[[2]]$name
[1] "CircusTrix"

The code that I'm using is as follows

library(jsonlite)
library(httr)
library(RJSONIO)
x= content(dash)$data
xx <- data.frame(do.call(rbind, x))

However, this code does not unlist some columns and the resulting df looks like this.

id
report_list_id
name
1
176248
list("183556")
Torchy's Tacos
2
180642
list("188160")
CircusTrix

Is there a better way to convert the Json to a DF avoiding issues like these.

@Dee I'm not quite sure what you mean by dput results. What is the best way to convert a Json to a DF — rrodrigorn0, Jul 25 '16 at 08:48
http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Deena, Jul 25 '16 at 08:59

Alex Ioannides · Answer 1 · 2016-07-25T10:14:42.843

It looks as if one of the problems is caused when parsing from JSON to list as the resulting output is a list of lists (i.e. a nested structure).

For example, [[1]]$report_list_id[[1]] implies that list element #1 has an element named $report_list_id which is a list with first (and only element) has value "183556".

After you take care of the nested lists, you'll need to convert each list element to a data.frame and then bind the rows together (in your code your binding rows and then converting to a data.frame).

EDIT - here's what I mean:

# original data structure (with nested list)
the_list <- list(list('id' = "176248", 'report_list_id' = list("183556"), 'name' = "Torchy's Tacos"),
                 list('id' = "180642", 'report_list_id' = list("188160"), 'name' = "CircusTrix"))

# convert each 'row/document' of data (i.e. element in the top-level list) into a data frame,
# taking care to un-nest the 'report_list_id' field
list_of_data_frame_rows <- lapply(X = the_list, 
                                  FUN = function(x) { data.frame('id' = x$id, 'report_list_id' = x$report_list_id[[1]], 'name' = x$name) })

# combine rows into a single data.frame
# (note: this is slow for many rows - 'bind_rows(...)' in the dplyr package is a safer bet)
combined_data_frame <- do.call(rbind, list_of_data_frame_rows)

combined_data_frame
#   id      report_list_id  name
# 1 176248  183556          Torchy's Tacos
# 2 180642  188160          CircusTrix

How do you suggest i do this. Should I have my code as - df = rbind(do.call(unlist,x)) — rrodrigorn0, Jul 25 '16 at 09:38
See my edit. It would be useful to see the original JSON data in future. — Alex Ioannides, Jul 25 '16 at 10:15

R converting a Json output to a dataframe

1 Answers1