CODEWITHSUNDEEP
javarxmlapi
Daycent
Daycent

Reputation: 575

get XML data from API using RJSDMX R

I am trying to get the XML data imported into R. Specifically, the World Integrated Trade Solution (WITS) to obtain bilateral trade levels between all countries. On the WITS website, it says that the official package that connects to their API is RJSDMX, but I'm having a hard time downloading the data.

Package use example: https://rstudio-pubs-static.s3.amazonaws.com/593878_b305636e73314eba87615097421034b3.html

WITS API documentation: http://wits.worldbank.org/data/public/WITSAPI_UserGuide.pdf

This is my code so far:

library(RJSDMX)
org <- "WITS"

# Get trade flows using the RJSDMX package
# https://rstudio-pubs-static.s3.amazonaws.com/593878_b305636e73314eba87615097421034b3.html
flow <- getFlows("WITS")
flow$`WBG_WITS,DF_WITS_TradeStats_Trade,1.0`

#parse "WITS" dataflow
fas_dataflow <- strsplit(names(flow[3]),",")
idx <- 1
#if Case 2 found, then get the second element
ifelse(length(fas_dataflow[[1]]) > 1, idx <-2, idx <-1)
fas_dataflow <- fas_dataflow[[1]][idx]

#get list of dimensions of "Average duration of unemployment" DSD
fas_dimensions <- getDimensions(org, fas_dataflow)

#use flatten to remove first level of indices
fas_codelist <- map(.x = names(flatten(fas_dimensions)),
                    flow = fas_dataflow,
                    provider = org,
                    .f = getCodes) %>% set_names(names(flatten(fas_dimensions)))
getCodes(org, fas_dataflow, "FREQ")
getCodes(org, fas_dataflow, "REPORTER")
getCodes(org, fas_dataflow, "PARTNER")
getCodes(org, fas_dataflow, "PRODUCTCODE")
getCodes(org, fas_dataflow, "INDICATOR")
# this doesn't work    
emoney_trx_id <- as_tibble(sdmxdf(getTimeSeries(org,"A.MPRT-PRTNR-SHR")))
# neither does this
emoney_trx_id <- as_tibble(sdmxdf(getTimeSeries(org,"A....MPRT-PRTNR-SHR")))
# and neither does this
emoney_trx_id <- as_tibble(sdmxdf(getTimeSeries(org,"A....MPRT-PRTNR-SHR.reported")))
# or this
emoney_trx_id <- as_tibble(sdmxdf(getTimeSeries(org,"A.MPRT-PRTNR-SHR.reported")))

Forgive me if this is an obvious question, but I have limited experience working with XML APIs.

Thank you in advance!!

Upvotes: 0

Views: 83

Answers (1)

Daycent
Daycent

Reputation: 575

EDIT: I was able to get the WITS bilateral trade flows directly through the URL on the API documentation page. See also Parsing XML to DATA FRAME

The resulting code looks like so:

# Set link to website
link1 <-"http://wits.worldbank.org/API/V1/SDMX/V21/datasource/tradestats-trade/reporter/all/year/2018/partner/all/indicator/MPRT-PRTNR-SHR"

# Get data from webpage
data_prices <- getURL(link1)

# Parse XML data
xmlfile <- xmlParse(data_prices)

# Get place nodes
places <- getNodeSet(xmlfile, "//Series")


# Get values for each place
values <- lapply(places, function(x){
  # Get current place id
  pid <- xmlAttrs(x)
  
  # Get values for each gas type for current place
  newrows <- lapply(xmlChildren(x), function(y){
    # Get type and update time values
    attrs <- xmlAttrs(y)
    
    # Get price value
    price <- xmlValue(y)
    names(price) <- "price"
    
    # Return values
    return(c(pid, attrs, price))
  })
  # Combine rows to single list
  newrows <- do.call(rbind, newrows)
  
  # Return rows
  return(newrows)
})

# Combine all values into a single dataframe
df <- as.data.frame(do.call(rbind, values), stringsAsFactors = FALSE)

Upvotes: 1

Related Questions