Multiple list JSON to Data Frame in R

Question

I'm getting data from an API which returns a JSON list with sub-lists of varying lengths. I would like to flatten the structure into a data frame in R. Below is the code I've used thus far however, each of my 'rows' or lists contain a varying number of objects (columns). What is the best way to do this in R? I'm assuming it may be some modification of the sapply function??

library(httr)
library(jsonlite)
rf <- GET("https://data.fortworthtexas.gov/resource/2ys6-nns2.json?fatality=True")
rfc <- content(rf)
rff <- fromJSON(rfc, simplifyDataFrame = TRUE)
json_file <- sapply(rfc, function(x) {
  x[sapply(x, is.null)] <- NA
  unlist(x)
})
json_file$id <- rownames(json_file)

Answer 1

Use data.table::rbindList() with fill = TRUE

You also need to transpose t() your matrix and convert into a data.frame in the function.

library(httr)
library(jsonlite)
rf <- GET("https://data.fortworthtexas.gov/resource/2ys6-nns2.json?fatality=True")
rfc <- content(rf)

json_file <- sapply(rfc, function(x) {
  x[sapply(x, is.null)] <- NA
  unlist(x)
  as.data.frame(t(x))
})

library(data.table)
data.table::rbindlist(json_file, fill= TRUE)

   atintersection           crashdatetime fatality hitandrun intersectingstreetblocknumber intersectingstreetname intersectingstreetsuffix location_1 reportnumber       streetname
1:           True 2018-09-30T04:30:00.000     True     False                           700              W.DICKSON                       ST     <list>     18-87957         HEMPHILL
2:          False 2018-10-18T19:49:00.000     True     False                             0              RIVERSIDE                       DR     <list>    180093550                 
3:          False 2018-10-18T00:22:00.000     True     False                           100    SILVER RIDGE BLVD E                              <list>    180093211 WHITE SETTLEMENT
4:          False 2018-10-11T02:55:00.000     True     False                          5800                                            LOOP     <list>     18-91258                 
5:          False 2018-10-13T13:15:00.000     True     False                          1000                 LUELLA                       ST     <list>     18-91935            SOUTH
   streetsuffix intersectingstreetprefix streetprefix intersectingstreetdescription streetdescription
1:           ST                                                                                      
2:                                     N           NE                                                
3:           RD                                                            CONCRETE          BLACKTOP
4:          FWY                        E            E                                                
5:          FWY                                                             ASPHALT           ASPHALT