将sdmx-xml文件读入R中的数据

Question

我想知道是否有人成功地将SDMX文件读入数据文件。我想读的文件是funds.xml (1mb)。我将文件保存为“pensions_funds.xml”到pwd，并尝试使用XML包读取它：

fileName <- system.file("pensions", "pensions_funds.xml", package="XML")
parsed<-xmlTreeParse("pension_funds.xml",getDTD=F)
r<-xmlRoot(parsed)
tmp = xmlSApply(r, function(x) xmlSApply(x, xmlValue))

上面的几行代码基本上遵循这里的示例，http://www.omegahat.org/RSXML/gettingStarted.html，但我认为我首先需要忽略标题(我已经粘贴到了文件的前几页下面，我正在试图读取)。因此，我认为上述方法可能有效，但就我的目的而言，它从错误的节点开始。我想抓住obs_values，由他们的time_period和ref_area索引。

第一件事是找到正确的节点并从那里开始，但是我怀疑我可能在执行一项愚蠢的任务，因为我对数据格式的知识有限，而且我不确定XML包是否可以用于SDMX文件。更聪明的人似乎尝试过做这个http://opensdmxdevelopers.wikispaces.com/RSDMX，我在https://r-forge.r-project.org/projects/rsdmx/的主页上找不到这个包供下载(我看不到任何链接/下载部分，但可能我是瞎子)，这似乎是早期阶段。rsdmx的存在意味着使用xml包读取sdmx可能并不容易，所以我准备在这个阶段放弃，除非有人在这方面取得了成功。实际上，我主要感兴趣的是读取这个文件，amounts.xml，但这是一个10 is文件，所以我开始比较小。

edit3试图使用Mischa的注释库(“XML”)中的更改在大文件上回答sgibb问题

url <- "http://www.ecb.europa.eu/stats/sdmx/bsi/1/data/outstanding_amounts.xml"

    sdmxHandler <- function() {
  ## data.frame which stores results
  data <- data.frame(stringsAsFactors=FALSE)
  ## counter to store current row
  i <- 1
  ## temp value to store current REF_AREA
  ## temp value to store current REF_AREA
  refArea <- NA
  bsItem <- NA
  bsCountSector <- NA

  ## handler subroutine for Obs tag
  Obs <- function(name, attr) {
    ## found an Obs tag and now fill data.frame
    data[i, "refArea"] <<- refArea
    data[i, "timePeriod"] <<- as.numeric(attr["TIME_PERIOD"])
    data[i, "obsValue"] <<- as.numeric(attr["OBS_VALUE"])
    data[i, "bsItem"] <<- bsItem
    data[i, "bsCountSector"] <<- bsCountSector
    i <<- i + 1
  }

  ## handler subroutine for Series tag
  Series <- function(name, attr) {
    refArea <<- attr["REF_AREA"]
    bsItem <<- as.character(attr["BS_ITEM"])
    bsCountSector <<- as.numeric(attr["BS_ITEM"])
  }
  return(list(getData=function() {return(data)},
              Obs=Obs, Series=Series))
}

## run parser
df <- xmlEventParse(file(url), handlers=sdmxHandler())$getData()
Specification mandate value for attribute OBS_VALUE
attributes construct error
Couldn't find end of Start Tag Obs line 15108
Premature end of data in tag Series line 15041
Premature end of data in tag DataSet line 91
Premature end of data in tag CompactData line 2
Error: 1: Specification mandate value for attribute OBS_VALUE
2: attributes construct error
3: Couldn't find end of Start Tag Obs line 15108
4: Premature end of data in tag Series line 15041
5: Premature end of data in tag DataSet line 91
6: Premature end of data in tag CompactData line 2
In addition: There were 50 or more warnings (use warnings() to see the first 50)

edit2:来自sgibb的答案看起来很理想，并且在较小的文件上工作得很好。我试着用它

url <- http://www.ecb.europa.eu/stats/sdmx/bsi/1/data/outstanding_amounts.xml

(10 of文件，原始链接更正)，唯一的修改是增加两行：

data[i, "bsItem"] <<- as.character(attr["BS_ITEM"])

data[i, "bsCountSector"] <<- as.numeric(attr["BS_COUNT_SECTOR"])

(这些是标识此较大数据集中一行所需的附加id变量)。它运行了几分钟，然后结束了这个错误：

错误: 1:属性TIME_PE的规格要求值 2:属性构造错误 3:找不到开始标签Obs行20743的结尾 4:标签系列20689中数据的过早结束 5:标记DataSet第91行中的数据过早结束第2行中的数据过早结束

此外:有50个或更多的警告(使用警告()来查看前50)

数据的基本格式似乎非常相似，所以我认为这可能有用。10 is文件的基本格式如下：

    <Series FREQ="M" REF_AREA="AT" ADJUSTMENT="N" BS_REP_SECTOR="A" BS_ITEM="A20" MATURITY_ORIG="A" DATA_TYPE="1" COUNT_AREA="U2" BS_COUNT_SECTOR="0000" CURRENCY_TRANS="Z01" BS_SUFFIX="E" TIME_FORMAT="P1M" COLLECTION="E">
        <Obs TIME_PERIOD="1997-09" OBS_VALUE="275.3" OBS_STATUS="A" OBS_CONF="F"/>
        <Obs TIME_PERIOD="1997-10" OBS_VALUE="275.9" OBS_STATUS="A" OBS_CONF="F"/>
        <Obs TIME_PERIOD="1997-11" OBS_VALUE="276.6" OBS_STATUS="A" OBS_CONF="F"/>

edit1:

所需的数据格式：

Ref_area    time_period obs_value

At  2006    118    
At  2007    119    
…    
Be  2006    101    
…

这是数据的第一部分。

    </Header>
    DataSet xsi:schemaLocation="https://www.ecb.europa.eu/vocabulary/stats/icpf/1 https://www.ecb.europa.eu/stats/sdmx/icpf/1/structure/2011-08-11/sdmx-compact.xsd" xmlns="https://www.ecb.europa.eu/vocabulary/stats/icpf/1"> 
<Group DECIMALS="0" TITLE_COMPL="Austria, reporting institutional sector Insurance corporations and pension funds - Closing balance sheet - All financial assets and liabilities - counterpart area World (all entities), counterpart institutional sector Total economy including Rest of the World (all sectors) - Credit (resources/liabilities) - Non-consolidated, Current prices - Euro, Neither seasonally nor working day adjusted - ESA95 TP table Not applicable" UNIT_MULT="9" UNIT="EUR" ESA95TP_SUFFIX="Z" ESA95TP_DENOM="E" ESA95TP_CONS="N" ESA95TP_DC_AL="2" ESA95TP_CPSECTOR="S" ESA95TP_CPAREA="A1" ESA95TP_SECTOR="S125" ESA95TP_ASSET="F" ESA95TP_TRANS="LE" ESA95TP_PRICE="V" ADJUSTMENT="N" REF_AREA="AT"/><Series ESA95TP_SUFFIX="Z" ESA95TP_DENOM="E" ESA95TP_CONS="N" ESA95TP_DC_AL="2" ESA95TP_CPSECTOR="S" ESA95TP_CPAREA="A1" ESA95TP_SECTOR="S125" ESA95TP_ASSET="F" ESA95TP_TRANS="LE" ESA95TP_PRICE="V" ADJUSTMENT="N" REF_AREA="AT" COLLECTION="E" TIME_FORMAT="P1Y" FREQ="A"><Obs OBS_CONF="F" OBS_STATUS="E" OBS_VALUE="112" TIME_PERIOD="2008"/><Obs OBS_CONF="F" OBS_STATUS="E" OBS_VALUE="119" TIME_PERIOD="2009"/><Obs OBS_CONF="F" OBS_STATUS="E" OBS_VALUE="125" TIME_PERIOD="2010"/><Obs OBS_CONF="F" OBS_STATUS="E" OBS_VALUE="127" TIME_PERIOD="2011"/></Series><Group D

Answer 1

RSDMX似乎处于早期开发状态。国际水文学组织目前还没有一揽子计划。但是您可以使用XML包轻松地实现它。我建议使用xmlEventParse (详见?xmlEventParse )：

编辑：使示例适应改变了的outstanding_amounts.xml需求

EDIT2: add download.file

library("XML")

#url <- "http://www.ecb.europa.eu/stats/sdmx/icpf/1/data/pension_funds.xml"
url <- "http://www.ecb.europa.eu/stats/sdmx/bsi/1/data/outstanding_amounts.xml"

## download xml file to avoid download errors disturbing xmlEventParse
tmp <- tempfile()
download.file(url, tmp) 

sdmxHandler <- function() {
  ## data.frame which stores results
  data <- data.frame(stringsAsFactors=FALSE)
  ## counter to store current row
  i <- 1
  ## temp value to store current REF_AREA, BS_ITEM and BS_COUNT_SECTOR
  refArea <- NA
  bsItem <- NA
  bsCountSector <- NA

  ## handler subroutine for Obs tag
  Obs <- function(name, attr) {
    ## found an Obs tag and now fill data.frame
    data[i, "refArea"] <<- refArea
    data[i, "bsItem"] <<- bsItem
    data[i, "bsCountSector"] <<- bsCountSector
    data[i, "timePeriod"] <<- as.Date(paste(attr["TIME_PERIOD"], "-01", sep=""), format="%Y-%m-%d")
    data[i, "obsValue"] <<- as.double(attr["OBS_VALUE"])
    ## update current row
    i <<- i + 1
  }

  ## handler subroutine for Series tag
  Series <- function(name, attr) {
    refArea <<- attr["REF_AREA"]
    bsItem <<- attr["BS_ITEM"]
    bsCountSector <<- as.numeric(attr["BS_COUNT_SECTOR"])
  }

  return(list(getData=function() {return(data)},
              Obs=Obs, Series=Series))
}

## run parser
df <- xmlEventParse(tmp, handlers=sdmxHandler())$getData()

head(df)
#  refArea bsItem bsCountSector timePeriod obsValue
#1      DE    A20          2210      12053     39.6
#2      DE    A20          2210      12084     46.1
#3      DE    A20          2210      12112     50.2
#4      DE    A20          2210      12143     52.0
#5      DE    A20          2210      12173     52.3
#6      DE    A20          2210      12204     47.3

Answer 2

包rsdmx允许您读取SDMX文件并将它们强制为data.frame。它现在托管在Github，目前在CRAN中可用，但是如果您可以通过以下方法从GitHub轻松地安装它的话：

require("devtools")
install_github("rsdmx", "opensdmx")

应用于您的数据，您可以执行以下操作：

sdmx <- readSDMX("http://www.ecb.europa.eu/stats/sdmx/bsi/1/data/outstanding_amounts.xml")
df <- as.data.frame(sdmx)

在rsdmx wiki中给出了更多的例子

请注意，它的功能当前将xml对象加载到R中，作为由rsdmx实例化的SDMX R对象的槽部分。将来，我们将研究rsdmx如何使用xmlEventParse (如@sgibb所建议的)来读取非常大的数据集。

Answer 3

library(XML)

xmlparsed <- xmlParse(file(url))

## obtain dataset node::
series_data <- getNodeSet(xmlparsed, "//Series")

if(length(series_data)==0){

datasetnode <- xmlChildren( xmlChildren(xmlparsed)[[1]])[[2]]

series_data<-xmlChildren(datasetnode)[ names(xmlChildren(datasetnode))=="Series"]

}

## prepare dataset

dataset.frame <- data.frame(matrix(ncol=3))
colnames(dataset.frame) <- c('REF_AREA', 'TIME_PERIOD', 'OBS_VALUE')
## loop over data

counter=1
for (i in 1: length(series_data)){
  if('Obs'%in%names(xmlChildren(series_data[[i]])) ){ ## To ignore empty //Series nodes
    for (j in 1: length(xmlChildren(series_data[[i]]))){
      dataset.frame[counter,1] <- xmlAttrs(series_data[[i]])['REF_AREA']
      dataset.frame[counter,2] <- xmlAttrs(series_data[[i]][[j]])['TIME_PERIOD']
      dataset.frame[counter,3] <- xmlAttrs(series_data[[i]][[j]])['OBS_VALUE']
      counter=counter+1
    }
  }
}


head(dataset.frame,5)