Unnesting JSON file

[TeeTrea]

I'm new to working with JSON files and I have trouble unnesting a JSON file. It looks like this

data <- '[{
  "AF": {
    "name": "Afghanistan",
    "divisions": {
      "AF-BDS": "Badakhshān",
      "AF-BDG": "Bādghīs",
      "AF-BGL": "Baghlān",
      "AF-BAL": "Balkh",
    }
  },
  "AL": {
    "name": "Albania",
    "divisions": {
      "AL-BR": "Berat",
      "AL-BU": "Bulqizë",
      "AL-DL": "Delvinë"
    }
  }
}]'

The data is taken from https://raw.githubusercontent.com/olahol/iso-3166-2.json/master/iso-3166-2.json
Unfortunately, it's not reading the data in the above example but it works when I read the file as I do below.

When I import it using jsonlite or comparable packages, like so:

library(tidyverse)
library(jsonlite)

iso <- fromJSON("https://raw.githubusercontent.com/olahol/iso-3166-2.json/master/iso-3166-2.json")

I get the following:

head(iso, 1)
$AF
$AF$name
[1] "Afghanistan"

$AF$divisions
$AF$divisions$`AF-BDS`
[1] "Badakhshān"

$AF$divisions$`AF-BDG`
[1] "Bādghīs"

$AF$divisions$`AF-BGL`
[1] "Baghlān"

$AF$divisions$`AF-BAL`
[1] "Balkh"

$AF$divisions$`AF-BAM`
[1] "Bāmīān"

$AF$divisions$`AF-FRA`
[1] "Farāh"

$AF$divisions$`AF-FYB`
[1] "Fāryāb"

$AF$divisions$`AF-GHA`
[1] "Ghaznī"

$AF$divisions$`AF-GHO`
[1] "Ghowr"

$AF$divisions$`AF-HEL`
[1] "Helmand"

$AF$divisions$`AF-HER`
[1] "Herāt"

$AF$divisions$`AF-JOW`
[1] "Jowzjān"

$AF$divisions$`AF-KAB`
[1] "Kabul (Kābol)"

$AF$divisions$`AF-KAN`
[1] "Kandahār"

$AF$divisions$`AF-KAP`
[1] "Kāpīsā"

$AF$divisions$`AF-KNR`
[1] "Konar (Kunar)"

$AF$divisions$`AF-KDZ`
[1] "Kondoz (Kunduz)"

$AF$divisions$`AF-LAG`
[1] "Laghmān"

$AF$divisions$`AF-LOW`
[1] "Lowgar"

$AF$divisions$`AF-NAN`
[1] "Nangrahār (Nangarhār)"

$AF$divisions$`AF-NIM`
[1] "Nīmrūz"

$AF$divisions$`AF-ORU`
[1] "Orūzgān (Urūzgā"

$AF$divisions$`AF-PIA`
[1] "Paktīā"

$AF$divisions$`AF-PKA`
[1] "Paktīkā"

$AF$divisions$`AF-PAR`
[1] "Parwān"

$AF$divisions$`AF-SAM`
[1] "Samangān"

$AF$divisions$`AF-SAR`
[1] "Sar-e Pol"

$AF$divisions$`AF-TAK`
[1] "Takhār"

$AF$divisions$`AF-WAR`
[1] "Wardak (Wardag)"

$AF$divisions$`AF-ZAB`
[1] "Zābol (Zābul)"

I parse it to a dataframe like so:

iso %>% as_tibble()

# A tibble: 2 x 237
  AF     AL     DZ     AS     AD    AO    AI    AQ    AG    AR    AM    AW    AU    AT    AZ    BS    BH    BD    BB   
  <name> <name> <name> <name> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam> <nam>
1 <chr … <chr … <chr … <chr … <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr… <chr…
2 <name… <name… <name… <name… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam… <nam…
# … with 218 more variables: BY <named list>, BE <named list>, BZ <named list>, BJ <named list>, BM <named list>,
#   BT <named list>, BO <named list>, BA <named list>, BW <named list>, BV <named list>, BR <named list>,

But what I would like to have looks more like this

country_code    country_name     divisions_code     divisions_name
AF              Afghanistan      AF-BDS             Badakhshān
AF              Afghanistan      AF-BDG             Bādghīs
AF              Afghanistan      AF-BGL             Badakhshān

[mattwarkentin]

Hi @TeeTrea,

Not super concise but this should do it...

library(tidyverse)
library(jsonlite)
library(tibblify)

iso <- fromJSON("https://raw.githubusercontent.com/olahol/iso-3166-2.json/master/iso-3166-2.json")

iso %>% 
  tibblify() %>% 
  rename(
    country_name = name,
    divisions_name = divisions
  ) %>% 
  bind_cols(country_code = names(iso)) %>% 
  mutate(divisions_code = map(divisions_name, names)) %>% 
  unnest(c(divisions_name, divisions_code))
#> # A tibble: 3,807 x 4
#>    country_name divisions_name country_code divisions_code
#>    <chr>        <chr>          <chr>        <chr>         
#>  1 Afghanistan  Badakhshān     AF           AF-BDS        
#>  2 Afghanistan  Bādghīs        AF           AF-BDG        
#>  3 Afghanistan  Baghlān        AF           AF-BGL        
#>  4 Afghanistan  Balkh          AF           AF-BAL        
#>  5 Afghanistan  Bāmīān         AF           AF-BAM        
#>  6 Afghanistan  Farāh          AF           AF-FRA        
#>  7 Afghanistan  Fāryāb         AF           AF-FYB        
#>  8 Afghanistan  Ghaznī         AF           AF-GHA        
#>  9 Afghanistan  Ghowr          AF           AF-GHO        
#> 10 Afghanistan  Helmand        AF           AF-HEL        
#> # … with 3,797 more rows