时间戳

首页 > 解决方案 > 从具有不同数量元素的xml创建数据框

r - 从具有不同数量元素的xml创建数据框

问题描述

我对 R 相当陌生，试图从 xml 文档中获取一些数据到数据框中。当每个节点具有相同数量的子节点时它工作正常，但是当它没有时我遇到了问题。

我正在使用的 R 代码如下：

library(XML)

xml.m_data <- xmlParse(file="mdata.xml")

df.m_data <- xmlToDataFrame(xml.m_data,nodes = getNodeSet(xml.m_data,"//Row"),collectNames=T)

nodeset <- getNodeSet(xml.m_data,"//Row")[[1]]
    colnames <- c()
    i <- NULL
    for(i in 1:(length(df.m_data))){
        x <- toString.XMLNode(nodeset[i])
        x <- strsplit(x,"\"")[[1]][2]
        colnames[i] <- x
        }
    colnames(df.m_data) <- colnames
    rm(colnames)

我试图获得的结果看起来像这样（来自第二个 XML）：

 CompanyID ProdConsID      UnitID  UnitName Commodity Facility     Source Commercialisation           StartDate
1 COMPANY001    E000001 E000001-001  Name_001     Power Producer Fossil Gas             False 2010-01-31T23:00:00
2 COMPANY002    E000002 E000002-001  Name_002     Power Producer Fossil Gas             False 2010-01-31T23:00:00
3 COMPANY003    E000003 E000003-001 Name_003A     Power Producer Fossil Gas              True 2009-10-25T23:00:00
4 COMPANY003    E000003 E000003-002 Name_003B     Power Producer Fossil Gas              True 2009-10-25T23:00:00

有两个xml，第一个我可以处理，第二个不能。

对于第二个，我收到以下错误：

Error in `[<-.data.frame`(`*tmp*`, i, names(nodes[[i]]), value = c("COMPANY001",  : duplicate subscripts for columns

示例 1：

<Results>
    <Result>
        <Row>
            <Field Name="CompanyID">COMPANY001</Field>
            <Field Name="ProdConsID">E000001</Field>
            <Field Name="UnitID">E000001-001</Field>
            <Field Name="UnitName">Name_001</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">False</Field>
            <Field Name="StartDate">2010-01-31T23:00:00</Field>
        </Row>
        <Row>
            <Field Name="CompanyID">COMPANY002</Field>
            <Field Name="ProdConsID">E000002</Field>
            <Field Name="UnitID">E000002-001</Field>
            <Field Name="UnitName">Name_002</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">False</Field>
            <Field Name="StartDate">2010-01-31T23:00:00</Field>
        </Row>
        <Row>
            <Field Name="CompanyID">COMPANY003</Field>
            <Field Name="ProdConsID">E000003</Field>
            <Field Name="UnitID">E000003-001</Field>
            <Field Name="UnitName">Name_003A</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">True</Field>
            <Field Name="StartDate">2009-10-25T23:00:00</Field>
        </Row>
        <Row>
            <Field Name="CompanyID">COMPANY003</Field>
            <Field Name="ProdConsID">E000003</Field>
            <Field Name="UnitID">E000003-002</Field>
            <Field Name="UnitName">Name_003B</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">True</Field>
            <Field Name="StartDate">2009-10-25T23:00:00</Field>
        </Row>  
    </Result>
</Results>

示例 2：

<Results>
    <Result>
        <Row>
            <Field Name="CompanyID">COMPANY001</Field>
            <Field Name="ProdConsID">E000001</Field>
            <Field Name="UnitID">E000001-001</Field>
            <Field Name="UnitName">Name_001</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">False</Field>
            <Field Name="StartDate">2010-01-31T23:00:00</Field>
            <Field Name="EndDate">2015-12-09T23:00:00</Field>
        </Row>
        <Row>
            <Field Name="CompanyID">COMPANY002</Field>
            <Field Name="ProdConsID">E000002</Field>
            <Field Name="UnitID">E000002-001</Field>
            <Field Name="UnitName">Name_002</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">False</Field>
            <Field Name="StartDate">2010-01-31T23:00:00</Field>
            <Field Name="EndDate">2015-12-09T23:00:00</Field>
        </Row>
        <Row>
            <Field Name="CompanyID">COMPANY003</Field>
            <Field Name="ProdConsID">E000003</Field>
            <Field Name="UnitID">E000003-001</Field>
            <Field Name="UnitName">Name_003A</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">True</Field>
            <Field Name="StartDate">2009-10-25T23:00:00</Field>
        </Row>
        <Row>
            <Field Name="CompanyID">COMPANY003</Field>
            <Field Name="ProdConsID">E000003</Field>
            <Field Name="UnitID">E000003-002</Field>
            <Field Name="UnitName">Name_003B</Field>
            <Field Name="Commodity">Power</Field>
            <Field Name="Facility">Producer</Field>
            <Field Name="Source">Fossil Gas</Field>
            <Field Name="Commercialisation">True</Field>
            <Field Name="StartDate">2009-10-25T23:00:00</Field>
        </Row>  
    </Result>
</Results>

非常感谢任何有用的见解。

标签： rxmldataframe

解决方案

由于语法更简单，我更喜欢 xml2 包而不是 XML。这里的解决方案读取所有“行”父节点，然后解析每个父节点以获得一系列 1 行数据帧，然后将所有结果合并为最终答案。dplyr 包中
的函数可以处理丢失的列。 有关详细信息，请参阅代码注释。bind_rows()

library(xml2)
library(dplyr)

#list of files to process
fnames<-"results2.xml"

doc<-read_xml(fnames)

#find parent nodes
parents<-xml_find_all(doc, ".//Row")

dfs<-lapply(parents, function(parent) {

  #find all of the nodes/records under each parent node
  titles <- xml_children(parent) %>% html_attr("Name")
  values <- xml_children(parent) %>% html_text()

  #make data frame of the values and column headings
  df<-as.data.frame(t(values), stringsAsFactors = FALSE)
  names(df)<-titles
  df
})

#Make combinded dataframe
answer<-bind_rows(dfs)
answer



   CompanyID ProdConsID      UnitID  UnitName Commodity Facility     Source Commercialisation           StartDate             EndDate
1 COMPANY001    E000001 E000001-001  Name_001     Power Producer Fossil Gas             False 2010-01-31T23:00:00 2015-12-09T23:00:00
2 COMPANY002    E000002 E000002-001  Name_002     Power Producer Fossil Gas             False 2010-01-31T23:00:00 2015-12-09T23:00:00
3 COMPANY003    E000003 E000003-001 Name_003A     Power Producer Fossil Gas              True 2009-10-25T23:00:00                <NA>
4 COMPANY003    E000003 E000003-002 Name_003B     Power Producer Fossil Gas              True 2009-10-25T23:00:00                <NA>

推荐阅读