Import nested Json into R

user_12222304 0
Viewed 37

I'm trying to import a json file into R and make it into a regular dataframe. I've been trying for hours and haven't managed (I'm a beginner).

That's what the json looks like (it's much longer):

 {"000D04":{"r":"FA1282","t":"C295","f":"00","d":"CASA C-295 M"},"000005":{"r":"N-BXF","t":"E145","f":"00","d":"Embraer ERJ-145-LR"}}

First I import the json:

raw_json <- fromJSON(txt="json_file.json")

That gives me a nested list:

$ 000D04:List of 4
  ..$ r: chr "FA1282"
  ..$ t: chr "C295"
  ..$ f: chr "00"
  ..$ d: chr "CASA C-295 M"
 $ 000005:List of 4
  ..$ r: chr "N-BXF"
  ..$ t: chr "E145"
  ..$ f: chr "00"
  ..$ d: chr "Embraer ERJ-145-LR"

I tried to do as_tibble() and looked up lots of other options. The closer I got was with the following, but it's still not there.

asTibble <- as.data.frame(t(as_tibble(aircrafts)))

I can't seem to manage. Any ideas?

Thanks in advance!!

r nested json
1 Answers

We can use map with tibble

library(purrr)
library(tibble)
map_dfr(raw_json, as_tibble, .id = 'id')
# A tibble: 2 x 5
#  r      t     f     d                  id    
#* <chr>  <chr> <chr> <chr>              <chr> 
#1 FA1282 C295  00    CASA C-295 M       000D04
#2 N-BXF  E145  00    Embraer ERJ-145-LR 000005

Or with unnest_wider

library(tidyr)
library(dplyr)
tibble(col1 = raw_json) %>%
    mutate(id = names(col1)) %>% 
    unnest_wider(c(col1))
# A tibble: 2 x 5
#  r      t     f     d                  id    
#  <chr>  <chr> <chr> <chr>              <chr> 
#1 FA1282 C295  00    CASA C-295 M       000D04
#2 N-BXF  E145  00    Embraer ERJ-145-LR 000005
user_37322710
Related