2
library(jsonlite) 
test <-as.dataframe(fromJSON('http://api.worldbank.org/v2/countries/all/indicators/SH.STA.ACSN?format=json')[2])
names(test)

However, it looks like there are more columns, such as the "country" column actually has "country.value" and "country.id" columns. This is annoying because "country.value" does not seem to exist, thus the following code returns errors saying this column is not in the data frame test. I only want to keep country.value. How can I resolve this? Does it have anything to do with how JSON returns data in R?

test$country.value

enter image description here

aynber
  • 22,380
  • 8
  • 50
  • 63
Jingyu Gao
  • 71
  • 3
  • There's a package wrapping that API: `library(WDI); test <- WDI(indicator = 'SH.STA.ACSN')` – alistaire Feb 15 '18 at 05:55
  • Thanks. But it seems WDI package has not updated data since 2012. I want to use the most recent data and use json api might be the only way. – Jingyu Gao Feb 15 '18 at 06:02
  • It calls the JSON API. If you set `end`, there's data through 2015: `test <- WDI(indicator = 'SH.STA.ACSN', end = 2015)` – alistaire Feb 15 '18 at 06:05
  • Thanks! It works. I still want to understand why the problem I posted exist and wonder solutions. Or it is generally too complicated to clean JSON API data as the way I tried to? – Jingyu Gao Feb 15 '18 at 06:12
  • 1
    `jsonlite::fromJSON` is returning a data.frame with list columns that contain nested data.frames. You can unpack them, but it's fussy. – alistaire Feb 15 '18 at 06:14
  • e.g. `library(tidyverse); test[[2]] %>% map_if(is.list, split, seq(nrow(.))) %>% bind_cols() %>% unnest()` or in base, `cbind(test[[2]][-1:-2], test[[2]][[1]], test[[2]][[2]])` – alistaire Feb 15 '18 at 06:19
  • Another route is to just tell `jsonlite` not to simplify to data frames, and then do it yourself afterwards, e.g. `library(purrr); test <- jsonlite::fromJSON('http://api.worldbank.org/v2/countries/all/indicators/SH.STA.ACSN?format=json', simplifyDataFrame = FALSE); test[[2]] %>% map(flatten) %>% map_dfr(map, as.character) %>% readr::type_convert()` – alistaire Feb 15 '18 at 06:29

1 Answers1

0

Does flatten work for you? I am not sure.

library(jsonlite) 
test <-data.frame(fromJSON('http://api.worldbank.org/v2/countries/all/indicators/SH.STA.ACSN?format=json')[2])
names(test)
test_flatten<-flatten(test)
names(test_flatten)
Dimitrios Zacharatos
  • 850
  • 1
  • 6
  • 16