Introduction

In Data Analysis, the collection of data from all the possible sources is the most important constituent. The data is stored in a remote server in JSON, XML, HTML format which is accessible through API’s.

API which stands for Application Programming Interface (API) is the access point to the application that has access to the database which returns the large set of data in a JSON format.

JSON acronym from JavaScript Object Notation is a lightweight data-interchange format which is easy for machines to generate and parse; easy for humans to write and read.

Sample JSON format :

{ “data”: “Click Here”, “size”: 36, “style”: “bold”, “name”: “text1”, }

While working with API’s which is nothing but an URL in RStudio, HTTP clients are used to accessing the API. HyperText Transfer Protocol (HTTP) enables the communication between the client and server using HTTP methods. Most commonly used HTTP methods to consumed an API are:

GET: is used to request the data from the server.

POST: is used to send the data to the server to create/update the resource

API(URL) example : https://reqres.in/api/users/pageid/1

In R, httr and jsonlite packages are used to consume the API’s that provide data in json format.

httr

This R package provides us with the HTTP clients to access the API with GET/POST methods, passing query parameters, verfiying the response with regard to the data format.

jsonlite

This R package is used to convert the received json format to reaedble R object or data frame. Jsonlite can also be used to convert R objects or data frame to json format data type.

In conjunction with these two packages, rlist package in R can be used to perfrom additional manipulation on the received json response. list.stack and list.select are two important methods exposed by the rlist which is useful to get parsed json data into the tibble.

To know more about these packages, type ?(httr), ?(jsonlite) and ?(rlist) in the console of Rstudio to view the documentation.

Let’s work on JSON API and the above packages to get the json data in the data frame for manipulation.

Importing all the required libraries:

library(httr) 

library(jsonlite) 

library(rlist) 

library(dplyr) 
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(data.table)
## 
## Attaching package: 'data.table'
## The following objects are masked from 'package:dplyr':
## 
##     between, first, last

To domenstrate API usage, parameter-based API is used to show an example of GET API that expects the query parameter based on which the data is fetched from the server. The parameter based API can be used in two ways in R.

  • Firstly, the query parameter is added to URL( API ) itself. This is shown in below example:
jsonResponse<-GET("http://api.worldbank.org/country?per_page=10&region=OED&lendingtype=LNX&format=json")
  • Secondly, the query parameter is passed separately in the form of a list in the query argument of the GET method. The example is shown is below:
query<-list(per_page="10",region="OED",lendingtype="LNX",format="json")
jsonResponse<-GET("http://api.worldbank.org/country",query=query)

To ensure the hassle-free processing of the json response for further manipulation, httr package provides methods http_type and http_error.

  • http_type method provides the data format of the response fetched from the GET method of the API.
http_type(jsonResponse)
## [1] "application/json"

Whereas, http_error method provides the information on whether the response received is error free for further processing.

http_error(jsonResponse)
## [1] FALSE

Here, variable jsonResponse has the response from the API in json format. This response is structured into the raw data, character vector or R object using the content method provided in the package httr.

jsonResponseText <- content(jsonResponse, as = "text") #JSON response structured into raw data
jsonResponseText
## [1] "[{\"page\":1,\"pages\":4,\"per_page\":\"10\",\"total\":31},[{\"id\":\"AUS\",\"iso2Code\":\"AU\",\"name\":\"Australia\",\"region\":{\"id\":\"EAS\",\"value\":\"East Asia & Pacific\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Canberra\",\"longitude\":\"149.129\",\"latitude\":\"-35.282\"},{\"id\":\"AUT\",\"iso2Code\":\"AT\",\"name\":\"Austria\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Vienna\",\"longitude\":\"16.3798\",\"latitude\":\"48.2201\"},{\"id\":\"BEL\",\"iso2Code\":\"BE\",\"name\":\"Belgium\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Brussels\",\"longitude\":\"4.36761\",\"latitude\":\"50.8371\"},{\"id\":\"CAN\",\"iso2Code\":\"CA\",\"name\":\"Canada\",\"region\":{\"id\":\"NAC\",\"value\":\"North America\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Ottawa\",\"longitude\":\"-75.6919\",\"latitude\":\"45.4215\"},{\"id\":\"CHE\",\"iso2Code\":\"CH\",\"name\":\"Switzerland\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Bern\",\"longitude\":\"7.44821\",\"latitude\":\"46.948\"},{\"id\":\"CZE\",\"iso2Code\":\"CZ\",\"name\":\"Czech Republic\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Prague\",\"longitude\":\"14.4205\",\"latitude\":\"50.0878\"},{\"id\":\"DEU\",\"iso2Code\":\"DE\",\"name\":\"Germany\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Berlin\",\"longitude\":\"13.4115\",\"latitude\":\"52.5235\"},{\"id\":\"DNK\",\"iso2Code\":\"DK\",\"name\":\"Denmark\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Copenhagen\",\"longitude\":\"12.5681\",\"latitude\":\"55.6763\"},{\"id\":\"ESP\",\"iso2Code\":\"ES\",\"name\":\"Spain\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Madrid\",\"longitude\":\"-3.70327\",\"latitude\":\"40.4167\"},{\"id\":\"EST\",\"iso2Code\":\"EE\",\"name\":\"Estonia\",\"region\":{\"id\":\"ECS\",\"value\":\"Europe & Central Asia\"},\"adminregion\":{\"id\":\"\",\"value\":\"\"},\"incomeLevel\":{\"id\":\"HIC\",\"value\":\"High income\"},\"lendingType\":{\"id\":\"LNX\",\"value\":\"Not classified\"},\"capitalCity\":\"Tallinn\",\"longitude\":\"24.7586\",\"latitude\":\"59.4392\"}]]"
jsonResponseParsed <- content(jsonResponse, as="parsed") #JSON response structured into parsed data
jsonResponseParsed
## [[1]]
## [[1]]$page
## [1] 1
## 
## [[1]]$pages
## [1] 4
## 
## [[1]]$per_page
## [1] "10"
## 
## [[1]]$total
## [1] 31
## 
## 
## [[2]]
## [[2]][[1]]
## [[2]][[1]]$id
## [1] "AUS"
## 
## [[2]][[1]]$iso2Code
## [1] "AU"
## 
## [[2]][[1]]$name
## [1] "Australia"
## 
## [[2]][[1]]$region
## [[2]][[1]]$region$id
## [1] "EAS"
## 
## [[2]][[1]]$region$value
## [1] "East Asia & Pacific"
## 
## 
## [[2]][[1]]$adminregion
## [[2]][[1]]$adminregion$id
## [1] ""
## 
## [[2]][[1]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[1]]$incomeLevel
## [[2]][[1]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[1]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[1]]$lendingType
## [[2]][[1]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[1]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[1]]$capitalCity
## [1] "Canberra"
## 
## [[2]][[1]]$longitude
## [1] "149.129"
## 
## [[2]][[1]]$latitude
## [1] "-35.282"
## 
## 
## [[2]][[2]]
## [[2]][[2]]$id
## [1] "AUT"
## 
## [[2]][[2]]$iso2Code
## [1] "AT"
## 
## [[2]][[2]]$name
## [1] "Austria"
## 
## [[2]][[2]]$region
## [[2]][[2]]$region$id
## [1] "ECS"
## 
## [[2]][[2]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[2]]$adminregion
## [[2]][[2]]$adminregion$id
## [1] ""
## 
## [[2]][[2]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[2]]$incomeLevel
## [[2]][[2]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[2]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[2]]$lendingType
## [[2]][[2]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[2]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[2]]$capitalCity
## [1] "Vienna"
## 
## [[2]][[2]]$longitude
## [1] "16.3798"
## 
## [[2]][[2]]$latitude
## [1] "48.2201"
## 
## 
## [[2]][[3]]
## [[2]][[3]]$id
## [1] "BEL"
## 
## [[2]][[3]]$iso2Code
## [1] "BE"
## 
## [[2]][[3]]$name
## [1] "Belgium"
## 
## [[2]][[3]]$region
## [[2]][[3]]$region$id
## [1] "ECS"
## 
## [[2]][[3]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[3]]$adminregion
## [[2]][[3]]$adminregion$id
## [1] ""
## 
## [[2]][[3]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[3]]$incomeLevel
## [[2]][[3]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[3]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[3]]$lendingType
## [[2]][[3]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[3]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[3]]$capitalCity
## [1] "Brussels"
## 
## [[2]][[3]]$longitude
## [1] "4.36761"
## 
## [[2]][[3]]$latitude
## [1] "50.8371"
## 
## 
## [[2]][[4]]
## [[2]][[4]]$id
## [1] "CAN"
## 
## [[2]][[4]]$iso2Code
## [1] "CA"
## 
## [[2]][[4]]$name
## [1] "Canada"
## 
## [[2]][[4]]$region
## [[2]][[4]]$region$id
## [1] "NAC"
## 
## [[2]][[4]]$region$value
## [1] "North America"
## 
## 
## [[2]][[4]]$adminregion
## [[2]][[4]]$adminregion$id
## [1] ""
## 
## [[2]][[4]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[4]]$incomeLevel
## [[2]][[4]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[4]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[4]]$lendingType
## [[2]][[4]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[4]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[4]]$capitalCity
## [1] "Ottawa"
## 
## [[2]][[4]]$longitude
## [1] "-75.6919"
## 
## [[2]][[4]]$latitude
## [1] "45.4215"
## 
## 
## [[2]][[5]]
## [[2]][[5]]$id
## [1] "CHE"
## 
## [[2]][[5]]$iso2Code
## [1] "CH"
## 
## [[2]][[5]]$name
## [1] "Switzerland"
## 
## [[2]][[5]]$region
## [[2]][[5]]$region$id
## [1] "ECS"
## 
## [[2]][[5]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[5]]$adminregion
## [[2]][[5]]$adminregion$id
## [1] ""
## 
## [[2]][[5]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[5]]$incomeLevel
## [[2]][[5]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[5]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[5]]$lendingType
## [[2]][[5]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[5]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[5]]$capitalCity
## [1] "Bern"
## 
## [[2]][[5]]$longitude
## [1] "7.44821"
## 
## [[2]][[5]]$latitude
## [1] "46.948"
## 
## 
## [[2]][[6]]
## [[2]][[6]]$id
## [1] "CZE"
## 
## [[2]][[6]]$iso2Code
## [1] "CZ"
## 
## [[2]][[6]]$name
## [1] "Czech Republic"
## 
## [[2]][[6]]$region
## [[2]][[6]]$region$id
## [1] "ECS"
## 
## [[2]][[6]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[6]]$adminregion
## [[2]][[6]]$adminregion$id
## [1] ""
## 
## [[2]][[6]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[6]]$incomeLevel
## [[2]][[6]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[6]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[6]]$lendingType
## [[2]][[6]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[6]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[6]]$capitalCity
## [1] "Prague"
## 
## [[2]][[6]]$longitude
## [1] "14.4205"
## 
## [[2]][[6]]$latitude
## [1] "50.0878"
## 
## 
## [[2]][[7]]
## [[2]][[7]]$id
## [1] "DEU"
## 
## [[2]][[7]]$iso2Code
## [1] "DE"
## 
## [[2]][[7]]$name
## [1] "Germany"
## 
## [[2]][[7]]$region
## [[2]][[7]]$region$id
## [1] "ECS"
## 
## [[2]][[7]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[7]]$adminregion
## [[2]][[7]]$adminregion$id
## [1] ""
## 
## [[2]][[7]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[7]]$incomeLevel
## [[2]][[7]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[7]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[7]]$lendingType
## [[2]][[7]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[7]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[7]]$capitalCity
## [1] "Berlin"
## 
## [[2]][[7]]$longitude
## [1] "13.4115"
## 
## [[2]][[7]]$latitude
## [1] "52.5235"
## 
## 
## [[2]][[8]]
## [[2]][[8]]$id
## [1] "DNK"
## 
## [[2]][[8]]$iso2Code
## [1] "DK"
## 
## [[2]][[8]]$name
## [1] "Denmark"
## 
## [[2]][[8]]$region
## [[2]][[8]]$region$id
## [1] "ECS"
## 
## [[2]][[8]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[8]]$adminregion
## [[2]][[8]]$adminregion$id
## [1] ""
## 
## [[2]][[8]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[8]]$incomeLevel
## [[2]][[8]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[8]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[8]]$lendingType
## [[2]][[8]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[8]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[8]]$capitalCity
## [1] "Copenhagen"
## 
## [[2]][[8]]$longitude
## [1] "12.5681"
## 
## [[2]][[8]]$latitude
## [1] "55.6763"
## 
## 
## [[2]][[9]]
## [[2]][[9]]$id
## [1] "ESP"
## 
## [[2]][[9]]$iso2Code
## [1] "ES"
## 
## [[2]][[9]]$name
## [1] "Spain"
## 
## [[2]][[9]]$region
## [[2]][[9]]$region$id
## [1] "ECS"
## 
## [[2]][[9]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[9]]$adminregion
## [[2]][[9]]$adminregion$id
## [1] ""
## 
## [[2]][[9]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[9]]$incomeLevel
## [[2]][[9]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[9]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[9]]$lendingType
## [[2]][[9]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[9]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[9]]$capitalCity
## [1] "Madrid"
## 
## [[2]][[9]]$longitude
## [1] "-3.70327"
## 
## [[2]][[9]]$latitude
## [1] "40.4167"
## 
## 
## [[2]][[10]]
## [[2]][[10]]$id
## [1] "EST"
## 
## [[2]][[10]]$iso2Code
## [1] "EE"
## 
## [[2]][[10]]$name
## [1] "Estonia"
## 
## [[2]][[10]]$region
## [[2]][[10]]$region$id
## [1] "ECS"
## 
## [[2]][[10]]$region$value
## [1] "Europe & Central Asia"
## 
## 
## [[2]][[10]]$adminregion
## [[2]][[10]]$adminregion$id
## [1] ""
## 
## [[2]][[10]]$adminregion$value
## [1] ""
## 
## 
## [[2]][[10]]$incomeLevel
## [[2]][[10]]$incomeLevel$id
## [1] "HIC"
## 
## [[2]][[10]]$incomeLevel$value
## [1] "High income"
## 
## 
## [[2]][[10]]$lendingType
## [[2]][[10]]$lendingType$id
## [1] "LNX"
## 
## [[2]][[10]]$lendingType$value
## [1] "Not classified"
## 
## 
## [[2]][[10]]$capitalCity
## [1] "Tallinn"
## 
## [[2]][[10]]$longitude
## [1] "24.7586"
## 
## [[2]][[10]]$latitude
## [1] "59.4392"

The structured text response is converted to data frame using the package jsonlite. Package jsonlite provides method fromJSON that facilitates the conversion of text into the data frame.

fromJSON(jsonResponseText)
## [[1]]
## [[1]]$page
## [1] 1
## 
## [[1]]$pages
## [1] 4
## 
## [[1]]$per_page
## [1] "10"
## 
## [[1]]$total
## [1] 31
## 
## 
## [[2]]
##     id iso2Code           name region.id          region.value
## 1  AUS       AU      Australia       EAS   East Asia & Pacific
## 2  AUT       AT        Austria       ECS Europe & Central Asia
## 3  BEL       BE        Belgium       ECS Europe & Central Asia
## 4  CAN       CA         Canada       NAC         North America
## 5  CHE       CH    Switzerland       ECS Europe & Central Asia
## 6  CZE       CZ Czech Republic       ECS Europe & Central Asia
## 7  DEU       DE        Germany       ECS Europe & Central Asia
## 8  DNK       DK        Denmark       ECS Europe & Central Asia
## 9  ESP       ES          Spain       ECS Europe & Central Asia
## 10 EST       EE        Estonia       ECS Europe & Central Asia
##    adminregion.id adminregion.value incomeLevel.id incomeLevel.value
## 1                                              HIC       High income
## 2                                              HIC       High income
## 3                                              HIC       High income
## 4                                              HIC       High income
## 5                                              HIC       High income
## 6                                              HIC       High income
## 7                                              HIC       High income
## 8                                              HIC       High income
## 9                                              HIC       High income
## 10                                             HIC       High income
##    lendingType.id lendingType.value capitalCity longitude latitude
## 1             LNX    Not classified    Canberra   149.129  -35.282
## 2             LNX    Not classified      Vienna   16.3798  48.2201
## 3             LNX    Not classified    Brussels   4.36761  50.8371
## 4             LNX    Not classified      Ottawa  -75.6919  45.4215
## 5             LNX    Not classified        Bern   7.44821   46.948
## 6             LNX    Not classified      Prague   14.4205  50.0878
## 7             LNX    Not classified      Berlin   13.4115  52.5235
## 8             LNX    Not classified  Copenhagen   12.5681  55.6763
## 9             LNX    Not classified      Madrid  -3.70327  40.4167
## 10            LNX    Not classified     Tallinn   24.7586  59.4392

Further, the structured vector response obtained from the json reponse using content method can be used to create own data table using dplyr and R base packages.

# verify if the obtained object is list or not
is.list(jsonResponseParsed[[2]][[1]]) 
## [1] TRUE
# convert the parsed json response list to data table
df <- lapply(jsonResponseParsed[[2]],as.data.table)

Using rbindlist to convert the parsed json list into one data table

dt <- rbindlist(df, fill = TRUE)

# Using dplyr and base R to retrieve the data frame with selected columns
dt%>% bind_rows %>% select(id,iso2Code,name,region,incomeLevel,lendingType,capitalCity,longitude,latitude)
##      id iso2Code           name                region incomeLevel
##  1: AUS       AU      Australia                   EAS         HIC
##  2: AUS       AU      Australia   East Asia & Pacific High income
##  3: AUT       AT        Austria                   ECS         HIC
##  4: AUT       AT        Austria Europe & Central Asia High income
##  5: BEL       BE        Belgium                   ECS         HIC
##  6: BEL       BE        Belgium Europe & Central Asia High income
##  7: CAN       CA         Canada                   NAC         HIC
##  8: CAN       CA         Canada         North America High income
##  9: CHE       CH    Switzerland                   ECS         HIC
## 10: CHE       CH    Switzerland Europe & Central Asia High income
## 11: CZE       CZ Czech Republic                   ECS         HIC
## 12: CZE       CZ Czech Republic Europe & Central Asia High income
## 13: DEU       DE        Germany                   ECS         HIC
## 14: DEU       DE        Germany Europe & Central Asia High income
## 15: DNK       DK        Denmark                   ECS         HIC
## 16: DNK       DK        Denmark Europe & Central Asia High income
## 17: ESP       ES          Spain                   ECS         HIC
## 18: ESP       ES          Spain Europe & Central Asia High income
## 19: EST       EE        Estonia                   ECS         HIC
## 20: EST       EE        Estonia Europe & Central Asia High income
##        lendingType capitalCity longitude latitude
##  1:            LNX    Canberra   149.129  -35.282
##  2: Not classified    Canberra   149.129  -35.282
##  3:            LNX      Vienna   16.3798  48.2201
##  4: Not classified      Vienna   16.3798  48.2201
##  5:            LNX    Brussels   4.36761  50.8371
##  6: Not classified    Brussels   4.36761  50.8371
##  7:            LNX      Ottawa  -75.6919  45.4215
##  8: Not classified      Ottawa  -75.6919  45.4215
##  9:            LNX        Bern   7.44821   46.948
## 10: Not classified        Bern   7.44821   46.948
## 11:            LNX      Prague   14.4205  50.0878
## 12: Not classified      Prague   14.4205  50.0878
## 13:            LNX      Berlin   13.4115  52.5235
## 14: Not classified      Berlin   13.4115  52.5235
## 15:            LNX  Copenhagen   12.5681  55.6763
## 16: Not classified  Copenhagen   12.5681  55.6763
## 17:            LNX      Madrid  -3.70327  40.4167
## 18: Not classified      Madrid  -3.70327  40.4167
## 19:            LNX     Tallinn   24.7586  59.4392
## 20: Not classified     Tallinn   24.7586  59.4392

Note: one may ask why can’t we use rbind instead of bind_rows. There is one main reason to use bind_rows over rbind is to combine two data frames having a different number of column. bind_rows assigns “NA” to those rows of columns missing in one data frame where the value is not provided whereas rbind throws an error.

Finally, rlist package provides list.select and list.stack method to filter columns and create tibble from the structured data frame.

list.select(df,id,iso2Code)
## [[1]]
## [[1]]$id
## [1] "AUS" "AUS"
## 
## [[1]]$iso2Code
## [1] "AU" "AU"
## 
## 
## [[2]]
## [[2]]$id
## [1] "AUT" "AUT"
## 
## [[2]]$iso2Code
## [1] "AT" "AT"
## 
## 
## [[3]]
## [[3]]$id
## [1] "BEL" "BEL"
## 
## [[3]]$iso2Code
## [1] "BE" "BE"
## 
## 
## [[4]]
## [[4]]$id
## [1] "CAN" "CAN"
## 
## [[4]]$iso2Code
## [1] "CA" "CA"
## 
## 
## [[5]]
## [[5]]$id
## [1] "CHE" "CHE"
## 
## [[5]]$iso2Code
## [1] "CH" "CH"
## 
## 
## [[6]]
## [[6]]$id
## [1] "CZE" "CZE"
## 
## [[6]]$iso2Code
## [1] "CZ" "CZ"
## 
## 
## [[7]]
## [[7]]$id
## [1] "DEU" "DEU"
## 
## [[7]]$iso2Code
## [1] "DE" "DE"
## 
## 
## [[8]]
## [[8]]$id
## [1] "DNK" "DNK"
## 
## [[8]]$iso2Code
## [1] "DK" "DK"
## 
## 
## [[9]]
## [[9]]$id
## [1] "ESP" "ESP"
## 
## [[9]]$iso2Code
## [1] "ES" "ES"
## 
## 
## [[10]]
## [[10]]$id
## [1] "EST" "EST"
## 
## [[10]]$iso2Code
## [1] "EE" "EE"
list.stack(df)
##     id iso2Code           name                region adminregion
## 1  AUS       AU      Australia                   EAS            
## 2  AUS       AU      Australia   East Asia & Pacific            
## 3  AUT       AT        Austria                   ECS            
## 4  AUT       AT        Austria Europe & Central Asia            
## 5  BEL       BE        Belgium                   ECS            
## 6  BEL       BE        Belgium Europe & Central Asia            
## 7  CAN       CA         Canada                   NAC            
## 8  CAN       CA         Canada         North America            
## 9  CHE       CH    Switzerland                   ECS            
## 10 CHE       CH    Switzerland Europe & Central Asia            
## 11 CZE       CZ Czech Republic                   ECS            
## 12 CZE       CZ Czech Republic Europe & Central Asia            
## 13 DEU       DE        Germany                   ECS            
## 14 DEU       DE        Germany Europe & Central Asia            
## 15 DNK       DK        Denmark                   ECS            
## 16 DNK       DK        Denmark Europe & Central Asia            
## 17 ESP       ES          Spain                   ECS            
## 18 ESP       ES          Spain Europe & Central Asia            
## 19 EST       EE        Estonia                   ECS            
## 20 EST       EE        Estonia Europe & Central Asia            
##    incomeLevel    lendingType capitalCity longitude latitude
## 1          HIC            LNX    Canberra   149.129  -35.282
## 2  High income Not classified    Canberra   149.129  -35.282
## 3          HIC            LNX      Vienna   16.3798  48.2201
## 4  High income Not classified      Vienna   16.3798  48.2201
## 5          HIC            LNX    Brussels   4.36761  50.8371
## 6  High income Not classified    Brussels   4.36761  50.8371
## 7          HIC            LNX      Ottawa  -75.6919  45.4215
## 8  High income Not classified      Ottawa  -75.6919  45.4215
## 9          HIC            LNX        Bern   7.44821   46.948
## 10 High income Not classified        Bern   7.44821   46.948
## 11         HIC            LNX      Prague   14.4205  50.0878
## 12 High income Not classified      Prague   14.4205  50.0878
## 13         HIC            LNX      Berlin   13.4115  52.5235
## 14 High income Not classified      Berlin   13.4115  52.5235
## 15         HIC            LNX  Copenhagen   12.5681  55.6763
## 16 High income Not classified  Copenhagen   12.5681  55.6763
## 17         HIC            LNX      Madrid  -3.70327  40.4167
## 18 High income Not classified      Madrid  -3.70327  40.4167
## 19         HIC            LNX     Tallinn   24.7586  59.4392
## 20 High income Not classified     Tallinn   24.7586  59.4392

The R object thus obtained can be further manipulated for the analysis.

References

DataScienePlus 2018, Accessing Web Data (JSON) in R using httr, Getting Data in R, viewed 27 March 2019, https://datascienceplus.com/accessing-web-data-json-in-r-using-httr/

Json, Introducing JSON, viewed 27 March 2019, https://www.json.org/

Perry Eising 2018, What exactly IS an API?, viewed 27 March 2019, https://medium.com/@perrysetgo/what-exactly-is-an-api-69f36968a41f

Stackoverflow 2017, Difference between rbind() and bind_rows() in R, viewed 27 March 2019, https://stackoverflow.com/questions/42887217/difference-between-rbind-and-bind-rows-in-r

R Documentation 2019, as.data.table, viewed 28 March 2019, https://www.rdocumentation.org/packages/data.table/versions/1.12.0/topics/as.data.table

R Documentation 2019, rbindlist, viewed 28 March 2019, https://www.rdocumentation.org/packages/data.table/versions/1.12.0/topics/rbindlist