Question 247349 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Getting list from JSON output into a data frame

I have tried a number of methods that are suggested for similar sounding problems but I can’t get them to work….

I am trying to get output from a JSON call into a data frame, but I can’t find a way to manipulate the list – it looks as though it should be easy, but all the solutions proposed seem very complicated and I can’t get them to work.

An example of the call is:

res = GET("https://vpic.nhtsa.dot.gov/api/vehicles?Year=2011&Make=Acura&Model=&units=US&format=json")
data = fromJSON(rawToChar(res$content), flatten = TRUE)
R<-data$Results
S<-R$Specs

This then gives me a list of 9 data frames, each with a Name column and a Value column

The Name column is repeated exactly in each data frame

I simply want to get these into a single data frame with the Name column providing column names and the Value columns from each data frame as individual rows.

Any suggestions gratefully received – many thanks.

Tags:

2

Answers


  1. 0

    You need to extract each Name and Value pair and then bind them together. For one row you can do this:

    library(jsonlite)
res <- read_json("https://vpic.nhtsa.dot.gov/api/vehicles?Year=2011&Make=Acura&Model=&units=US&format=json")

do.call(cbind, lapply(
    res$Results[[1]]$Specs,
    (x) setNames(data.frame(x$Value), x$Name)
))

#    Make                           Model MYR       OL      OW      OH       WB       CW       A       B       C       D       E       F       G     TWF     TWR    WD
# 1 ACURA CSX 4DR SEDAN FWD /TECH PACKAGE   6 178.7402 68.8976 56.6929 106.2992 2866.006 37.0079 17.7165 14.1732 29.9213 43.3071 36.2205 36.6142 59.0551 59.4488 61/39

    Or to apply this to your entire result.

    do.call(
    rbind,
    lapply(res$Results, (dat) do.call(cbind, lapply(dat$Specs, (x) setNames(data.frame(x$Value), x$Name))))
)
#    Make                                  Model MYR       OL      OW      OH       WB        CW       A       B       C       D       E       F       G     TWF     TWR
# 1 ACURA        CSX 4DR SEDAN FWD /TECH PACKAGE   6 178.7402 68.8976 56.6929 106.2992  2866.006 37.0079 17.7165 14.1732 29.9213 43.3071 36.2205 36.6142 59.0551 59.4488
# 2 ACURA            MDX 4DR SUV AWD /TECH/ELITE  10 190.9449 78.3465 68.1102 108.2677 4550.3357  46.063 72.0472 16.5354 33.4646 49.6063 39.3701 43.3071 67.7165 67.7165
# 3 ACURA          RDX 4DR SUV AWD /TECH PACKAGE  10 181.8898  73.622 65.3543 104.3307 3946.2698 49.2126 59.8425 14.9606 33.0709 48.8189 38.5827 38.9764 62.2047 62.5984
# 4 ACURA                RL 4DR SEDAN AWD /ELITE   9 195.6693 72.8346 57.4803 110.2362 4109.4117 49.2126 28.7402 14.5669 31.4961 45.6693 40.9449 43.7008  61.811  61.811
# 5 ACURA           TL 4 DR SEDAN FWD/TECHNOLOGY   9 195.6693 74.0157 57.0866 109.4488 3708.1708 52.7559 24.4094 13.3858 31.8898  46.063 41.3386 45.2756 62.9921 63.3858
# 6 ACURA TL 4 DR SEDAN SH-AWD/SH-AWD TECHNOLOGY   9 195.6693 74.0157 57.0866 109.4488 3970.5206 52.7559 24.4094 13.3858 31.8898  46.063 41.3386 45.2756 62.9921 63.3858
# 7 ACURA TSX 4DR SEDAN FWD TECH PACKAGE/PREMIUM   9 186.2205 72.4409 56.6929 106.6929 3406.1379 47.6378 17.3228 13.7795 31.1024 45.2756  38.189 40.9449 62.2047 62.2047
# 8 ACURA                   TSX 4DR SEDAN FWD V6   9 186.2205 72.4409 56.6929 106.6929 3686.1246 47.6378 17.3228 13.7795 31.1024 45.2756  38.189 40.9449 62.2047 62.2047
# 9 ACURA           ZDX 4DR SUV AWD / TECH ELITE  10  192.126 78.3465 62.9921 108.2677 4550.3357 52.3622 42.9134 12.9921 34.6457 47.2441 42.9134 40.9449 67.7165 67.3228

    Alternative to jsonlite::read_json()

    I do not know why read_json() would give you an error as it seems to work when I use it. However, you can also get the res object by doing:

    response <- httr::GET("https://vpic.nhtsa.dot.gov/api/vehicles?Year=2011&Make=Acura&Model=&units=US&format=json")
res  <- jsonlite::fromJSON(httr::content(response), simplifyVector = FALSE)

    Then the rest of the code is the same.

    Login or Signup to reply.
  2. 0

    Here is another approach using rrapply() (in package rrapply) to unnest the nested list to a long data.frame and then using unstack() to transform from a long to a wide data.frame:

    jsonlite::read_json("https://vpic.nhtsa.dot.gov/api/vehicles?Year=2011&Make=Acura&Model=&units=US&format=json")$Results |>
  rrapply::rrapply(how = "bind") |>
  unstack(form = Value ~ Name)

#>         A       B       C        CW       D       E       F       G  Make                                  Model MYR      OH       OL      OW     TWF     TWR       WB    WD
#> 1 37.0079 17.7165 14.1732  2866.006 29.9213 43.3071 36.2205 36.6142 ACURA        CSX 4DR SEDAN FWD /TECH PACKAGE   6 56.6929 178.7402 68.8976 59.0551 59.4488 106.2992 61/39
#> 2  46.063 72.0472 16.5354 4550.3357 33.4646 49.6063 39.3701 43.3071 ACURA            MDX 4DR SUV AWD /TECH/ELITE  10 68.1102 190.9449 78.3465 67.7165 67.7165 108.2677 56/44
#> 3 49.2126 59.8425 14.9606 3946.2698 33.0709 48.8189 38.5827 38.9764 ACURA          RDX 4DR SUV AWD /TECH PACKAGE  10 65.3543 181.8898  73.622 62.2047 62.5984 104.3307 57/43
#> 4 49.2126 28.7402 14.5669 4109.4117 31.4961 45.6693 40.9449 43.7008 ACURA                RL 4DR SEDAN AWD /ELITE   9 57.4803 195.6693 72.8346  61.811  61.811 110.2362 58/42
#> 5 52.7559 24.4094 13.3858 3708.1708 31.8898  46.063 41.3386 45.2756 ACURA           TL 4 DR SEDAN FWD/TECHNOLOGY   9 57.0866 195.6693 74.0157 62.9921 63.3858 109.4488 61/39
#> 6 52.7559 24.4094 13.3858 3970.5206 31.8898  46.063 41.3386 45.2756 ACURA TL 4 DR SEDAN SH-AWD/SH-AWD TECHNOLOGY   9 57.0866 195.6693 74.0157 62.9921 63.3858 109.4488 59/41
#> 7 47.6378 17.3228 13.7795 3406.1379 31.1024 45.2756  38.189 40.9449 ACURA TSX 4DR SEDAN FWD TECH PACKAGE/PREMIUM   9 56.6929 186.2205 72.4409 62.2047 62.2047 106.6929 60/40
#> 8 47.6378 17.3228 13.7795 3686.1246 31.1024 45.2756  38.189 40.9449 ACURA                   TSX 4DR SEDAN FWD V6   9 56.6929 186.2205 72.4409 62.2047 62.2047 106.6929 60/40
#> 9 52.3622 42.9134 12.9921 4550.3357 34.6457 47.2441 42.9134 40.9449 ACURA           ZDX 4DR SUV AWD / TECH ELITE  10 62.9921  192.126 78.3465 67.7165 67.3228 108.2677 58/42
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search