Fetching data from OECD into R via SDMX(XML)

I want to extract data from the OECD website particularily the dataset "REGION_ECONOM" with the dimensions "GDP" (GDP of the respective regions) and "POP_AVG" (the average population of the respective region).

This is the first time I am doing this: I picked all the required dimensions on the OECD website and copied the SDMX (XML) link.

I tried to load them into R and convert them to a data frame with the following code: (in the link I replaced the list of all regions with "ALL" as otherwise the link would have been six pages long)

if (!require(rsdmx)) install.packages('rsdmx') + library(rsdmx)

    url2 <- "https://stats.oecd.org/restsdmx/sdmx.ashx/GetData/REGION_ECONOM/1+2.ALL.SNA_2008.GDP+POP_AVG.REAL_PPP.ALL.1990+1991+1992+1993+1994+1995+1996+1997+1998+1999+2000+2001+2002+2003+2004+2005+2006+2007+2008+2009+2010+2011+2012+2013+2014+2015+2016+2017+2018/all?"
    sdmx2 <- readSDMX(url2)
    stats2 <- as.data.frame(sdmx2)
    head(stats2)

Unfortunately, this returns a "400 Bad request" error.

When just selecting a couple of regions the error does not appear:

if (!require(rsdmx)) install.packages('rsdmx') + library(rsdmx)

    url1 <- "https://stats.oecd.org/restsdmx/sdmx.ashx/GetData/REGION_ECONOM/1+2.AUS+AU1+AU101+AU103+AU104+AU105.SNA_2008.GDP+POP_AVG.REAL_PPP.ALL.1990+1991+1992+1993+1994+1995+1996+1997+1998+1999+2000+2001+2002+2003+2004+2005+2006+2007+2008+2009+2010+2011+2012+2013+2014+2015+2016+2017+2018/all?"
    sdmx1 <- readSDMX(url1)
    stats1 <- as.data.frame(sdmx1)
    head(stats1)

I also tried to use the "OECD" package to get the data. There I had the same problem. ("400 Bad Request")

if (!require(OECD)) install.packages('OECD') + library(OECD)

df1<-get_dataset("REGION_ECONOM", filter = "GDP+POP_AVG", 
             start_time = 2008, end_time = 2009, pre_formatted = TRUE)

However, when I use the package for other data sets it does work:

df <- get_dataset("FTPTC_D", filter = "FRA+USA", pre_formatted = TRUE)

Does anyone know where my mistake could lie?

Solution

the sdmx-ml api does not seem to work as explained (using the all parameter), whereas the json API works just fine. The following query returns the values for all countries and returns them as json - I simply replaced All by an empty field.

query <- https://stats.oecd.org/sdmx-json/data/REGION_ECONOM/1+2..SNA_2008.GDP+POP_AVG.REAL_PPP.ALL.1990+1991+1992+1993+1994+1995+1996+1997+1998+1999+2000+2001+2002+2003+2004+2005+2006+2007+2008+2009+2010+2011+2012+2013+2014+2015+2016+2017+2018/all?

Transforming it to a readable format is not so trivial. I played around a bit to find the following work-around:

# send a GET request using httr
library(httr)

query <- "https://stats.oecd.org/sdmx-json/data/REGION_ECONOM/1+2..SNA_2008.GDP+POP_AVG.REAL_PPP.ALL.1990+1991+1992+1993+1994+1995+1996+1997+1998+1999+2000+2001+2002+2003+2004+2005+2006+2007+2008+2009+2010+2011+2012+2013+2014+2015+2016+2017+2018/all?"
dat_raw <- GET(query)
dat_parsed <- parse_json(content(dat_raw, "text"))  # parse the content

Next, access the observations from the nested list and transform them to a matrix. Also extract the features from the keys:

dat_obs <- dat_parsed[["dataSets"]][[1]][["observations"]]
dat0 <- do.call(rbind, dat_obs)  # get a matrix
new_features <- matrix(as.numeric(do.call(rbind, strsplit(rownames(dat0), ":"))), nrow = nrow(dat0))
dat1 <- cbind(new_features, dat0)  # add feature columns
dat1_df <- as.data.frame(dat1)  # optionally transform to data frame

Finally you want to find out about the keys. Those are hidden in the "structure". This one you also need to parse correctly, so I wrote a function for you to easier extract the values and ids:

## Get keys of features
keys <- dat_parsed[["structure"]][["dimensions"]][["observation"]]
for (i in 1:length(keys)) print(paste("id position:", i, "is feature", keys[[i]]$id))

# apply keys
get_features <- function(data_input, keys_input, feature_index, value = FALSE) {
  keys_temp <- keys_input[[feature_index]]$values
  keys_temp_matrix <- do.call(rbind, keys_temp)
  keys_temp_out <- keys_temp_matrix[, value + 1][unlist(data_input[, feature_index])+1]  # column 1 is id, 2 is value
  return(unlist(keys_temp_out))
}

head(get_features(dat1_df, keys, 7))
head(get_features(dat1_df, keys, 2, value = FALSE))
head(get_features(dat1_df, keys, 2, value = TRUE))

I hope that helps you in your project.

Best, Tobias