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

How to import JSON data into R with one nested list?

I have a JSON file to be imported into R. Here is a section of the data:

{"data":[{"British Columbia":"BC","BC":"4.63"}, {"Alberta":"AB","AB":"4.15"}, {"Ontario":"ON","ON":"13.6"}]}

I need to turn this data into a dataframe, but the import is producing many NA values.

I tried the following:

library(jsonlite)
jsonData <- fromJSON("filepath")
jsonData

Output:

$data
  British Columbia   BC Alberta   AB Ontario   ON Manitoba   MB Saskatchewan   SK
1               BC 4.63    <NA> <NA>    <NA> <NA>     <NA> <NA>         <NA> <NA>
2             <NA> <NA>      AB 4.15    <NA> <NA>     <NA> <NA>         <NA> <NA>
3             <NA> <NA>    <NA> <NA>      ON 13.6     <NA> <NA>         <NA> <NA>
4             <NA> <NA>    <NA> <NA>    <NA> <NA>       MB 1.28         <NA> <NA>
5             <NA> <NA>    <NA> <NA>    <NA> <NA>     <NA> <NA>           SK  1.1

It appears to be because of the single term "data" at the beginning of the JSON file. How can I avoid this issue?

Desired output:

British Columbia BC
BC               4.63
Alberta          AB
AB               4.15
Ontario          ON
ON               13.6
Manitoba         MB
MB               1.28
Saskatchewan     SK
SK               1.1
Tags:

2

Answers


  1. 0

    Given x shown in the Note at the end, use fromJSON, extract the data portion, remove the NA’s and convert the types to the correct ones.

    library (jsonlite)
library (purrr)

x |>
  fromJSON() |>
  _$data |>
  map_dfr(na.omit) |>
  type.convert(as.is = TRUE)

    giving

    # A tibble: 1 × 6
  `British Columbia`    BC Alberta    AB Ontario    ON
  <chr>              <dbl> <chr>   <dbl> <chr>   <dbl>
1 BC                  4.63 AB       4.15 ON       13.6

    Note

    x <- '{"data":[{"British Columbia":"BC","BC":"4.63"}, {"Alberta":"AB","AB":"4.15"}, {"Ontario":"ON","ON":"13.6"}]}'
    Login or Signup to reply.
  2. 0

    In the call to jsonlite::fromJSON(…) add "simplifyVector = FALSE" so that the data is returned as a list.
    Then unlist and convert to a data.frame

    library(jsonlite)
library(tibble)

jsonData <- fromJSON('{"data":[{"British Columbia":"BC","BC":"4.63"}, {"Alberta":"AB","AB":"4.15"}, {"Ontario":"ON","ON":"13.6"}]}', simplifyVector = FALSE)

df<-as.data.frame(unlist(jsonData))
rownames_to_column(df)


                rowname unlist(jsonData)
1 data.British Columbia               BC
2               data.BC             4.63
3          data.Alberta               AB
4               data.AB             4.15
5          data.Ontario               ON
6               data.ON             13.6
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search