How to Parse JSON Output into R into Data Frame Using APIs?

Question

I'm trying to get data from the World Bank (https://datahelpdesk.worldbank.org/knowledgebase/articles/898581-basic-api-call-structures). Specifically, I'm trying to query the API, parse the JSON output into R and return a data frame, where each row is a country.

I went to the website and obtained the JSON output URL (for url) below. My code is the following:

library('tidyverse')
library('httr')
library('jsonlite')
url <- 'http://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json'

r <- GET(url, query = list(q = "country"))

json <- content(r, "parsed")
    
json_as_text <- content(r, "text")
json <- fromJSON(json_as_text)
df <- json$country %>% as_tibble()

However, the data frame returned is empty. I'm new to APIs, and essentially trying to return a list of countries available on the website. Any ideas how to do this?

Allan Cameron · Accepted Answer · 2020-11-08 19:12:42Z

1

The following modifications seem to work (this is a straight-up reprex):

library('tidyverse')
library('httr')
library('jsonlite')

url <- 'http://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json'

(df <- fromJSON(url) %>% pluck(2) %>% as_tibble())

#> # A tibble: 50 x 8
#>    indicator$id $value country$id $value countryiso3code date    value unit 
#>    <chr>        <chr>  <chr>      <chr>  <chr>           <chr>   <int> <chr>
#>  1 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2020  NA      ""   
#>  2 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2019   4.28e8 ""   
#>  3 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2018   4.20e8 ""   
#>  4 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2017   4.12e8 ""   
#>  5 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2016   4.04e8 ""   
#>  6 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2015   3.96e8 ""   
#>  7 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2014   3.88e8 ""   
#>  8 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2013   3.80e8 ""   
#>  9 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2012   3.71e8 ""   
#> 10 SP.POP.TOTL  Popul~ 1A         Arab ~ ARB             2011   3.63e8 ""   
#> # ... with 40 more rows, and 2 more variables: obs_status <chr>, decimal <int>

^{Created on 2020-11-08 by the reprex package (v0.3.0)}

edited Nov 8, 2020 at 19:12

answered Nov 8, 2020 at 19:07

Allan Cameron

178k7 gold badges70 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Allan Cameron Over a year ago

Ah, I missed that. Thank you @27ϕ9

Collectives™ on Stack Overflow

How to Parse JSON Output into R into Data Frame Using APIs?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related