Loop URL in SDMX format and combine the result into R dataframe-CodePudding

I'm trying to download World Bank's WITS data and convert them into R dataframe. The site's API (ref. pp. 12-13) does not seem to allow users to call all "reporters," "partners," and "products" at once, so one may need to loop through a list of country (either as "reporters" or "partners") using their XML request format:

http://wits.worldbank.org/API/V1/SDMX/V21/datasource/tradestats-tariff/reporter/usa/year/2000/partner/all/product/all/indicator/AHS-WGHTD-AVRG

(One will need to loop through the "usa" part by a list of "reporters" (country abbreviation)) My goal is to loop through a list a country abbreviation, generate a dataframe for each run, and then bind them together into a larger dataframe. So I referenced this post and use the following code, but it didn't get me any further. I posted my code at below and it will be really appreciated if someone could take a look at and share some tips on this.

# load required packages
library(RCurl)
library(XML)
devtools::install_github("opensdmx/rsdmx")
library(rsdmx)

# if just for one reporter (usa)
myUrl <- "http://wits.worldbank.org/API/V1/SDMX/V21/datasource/tradestats-tariff/reporter/usa/year/2000/partner/all/product/all/indicator/AHS-WGHTD-AVRG"

dataset <- readSDMX(myUrl)
stats <- as.data.frame(dataset)

dim(stats)
[1] 5142    8

# looks like this
head(stats)
  FREQ REPORTER PARTNER     PRODUCTCODE
1    A      USA     ABW    01-05_Animal
2    A      USA     ABW 06-15_Vegetable
3    A      USA     ABW  16-24_FoodProd

## loop through a list of reporters (countries)

library(rvest)

# teams
reporters <- c("aus", "usa", "ukr")

# init
df <- data.frame()

# loop
for(i in reporters){
    # find url
    myUrl <- paste0("http://wits.worldbank.org/API/V1/SDMX/V21/datasource/tradestats-tariff/reporter/", i,"/year/2000/partner/all/product/all/indicator/AHS-WGHTD-AVRG")
    dataset <- readSDMX(myUrl)
    stats <- as.data.frame(dataset)
    # bind to dataframe
    df <- rbind(df, stats)
}

# view captured data
View(df)

# NOTHING!

CodePudding user response：

Consider R's built-in utils::download.file() and then parse with XML. Because your data is attribute-centric with no element text in <Series> and <Obs> nodes, consider the undocumented xmlAttrsToDataFrame, requiring triple colon qualifier, :::.

Finally, use an apply function like sapply and avoid the bookkeeping of for loops and inefficiently growing an object in a loop by calling rbind iteratively on same dataframe. Below even wraps download and XML parsing in tryCatch for potential errors like ukr.

library(XML)

build_df <- function(i) {
  url <- paste0(
    "http://wits.worldbank.org/API/V1/SDMX/V21/datasource/",
    "tradestats-tariff/reporter/", i,
    "/year/2000/partner/all/product/all/indicator/AHS-WGHTD-AVRG"
  )
  
  df <- NULL
  tryCatch({
    tmp <- tempfile()          # DOWNLOAD XML TO TEMP FILE
    download.file(url, tmp)
    
    # PARSE XML AND EXTRACT ATTRIBUTES TO DATA FRAME
    doc <- XML::xmlParse(tmp)
    df <- cbind(
      XML:::xmlAttrsToDataFrame(getNodeSet(doc, "//Series")),
      XML:::xmlAttrsToDataFrame(getNodeSet(doc, "//Obs"))
    )
    unlink(tmp)               # DELETE TEMP FILE
  }, warning = function(w) print(w)
  , error = function(e) print(e)
  )
  
  return(df)
}

reporters <- c("aus", "usa", "ukr")

# NAMED LIST OF DATA FRAMES
df_list <- sapply(reporters, build_df)

# COMPILE ALL INTO SINGLE DATA FRAME (CALL rbind ONCE)
final_df <- do.call(rbind, unname(df_list))

Output

df_list

head(df_list$aus)
  FREQ REPORTER PARTNER     PRODUCTCODE      INDICATOR TIME_PERIOD        OBS_VALUE DATASOURCE
1    A      AUS     AFG 50-63_TextCloth AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
2    A      AUS     AFG           manuf AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
3    A      AUS     AFG        Textiles AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
4    A      AUS     AFG           Total AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
5    A      AUS     AFG     UNCTAD-SoP3 AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
6    A      AUS     AGO 41-43_HidesSkin AHS-WGHTD-AVRG        2000               20   WITS-TRN

head(df_list$usa)
  FREQ REPORTER PARTNER     PRODUCTCODE      INDICATOR TIME_PERIOD OBS_VALUE DATASOURCE
1    A      USA     ABW    01-05_Animal AHS-WGHTD-AVRG        2000         0   WITS-TRN
2    A      USA     ABW 06-15_Vegetable AHS-WGHTD-AVRG        2000         0   WITS-TRN
3    A      USA     ABW  16-24_FoodProd AHS-WGHTD-AVRG        2000         0   WITS-TRN
4    A      USA     ABW     27-27_Fuels AHS-WGHTD-AVRG        2000         0   WITS-TRN
5    A      USA     ABW 28-38_Chemicals AHS-WGHTD-AVRG        2000         0   WITS-TRN
6    A      USA     ABW 39-40_PlastiRub AHS-WGHTD-AVRG        2000         0   WITS-TRN

head(df_list$ukr)
NULL

final_df

head(final_df)
  FREQ REPORTER PARTNER     PRODUCTCODE      INDICATOR TIME_PERIOD        OBS_VALUE DATASOURCE
1    A      AUS     AFG 50-63_TextCloth AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
2    A      AUS     AFG           manuf AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
3    A      AUS     AFG        Textiles AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
4    A      AUS     AFG           Total AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
5    A      AUS     AFG     UNCTAD-SoP3 AHS-WGHTD-AVRG        2000 0.46377738685431   WITS-TRN
6    A      AUS     AGO 41-43_HidesSkin AHS-WGHTD-AVRG        2000               20   WITS-TRN

tail(final_df)
     FREQ REPORTER PARTNER PRODUCTCODE      INDICATOR TIME_PERIOD          OBS_VALUE DATASOURCE
8966    A      USA     ZWE       Total AHS-WGHTD-AVRG        2000   5.65257483778078   WITS-TRN
8967    A      USA     ZWE      Transp AHS-WGHTD-AVRG        2000  0.249632882835876   WITS-TRN
8968    A      USA     ZWE UNCTAD-SoP1 AHS-WGHTD-AVRG        2000   29.5531507778593   WITS-TRN
8969    A      USA     ZWE UNCTAD-SoP2 AHS-WGHTD-AVRG        2000    2.7243509937362   WITS-TRN
8970    A      USA     ZWE UNCTAD-SoP3 AHS-WGHTD-AVRG        2000   5.12801568054237   WITS-TRN
8971    A      USA     ZWE UNCTAD-SoP4 AHS-WGHTD-AVRG        2000 0.0082396181212962   WITS-TRN