Create data frame from xml with different number of elements

user_13004916 0
Viewed 194

I am fairly new to R, trying to get some data from an xml-document into a data frame. It works fine when each node has the same number of children, but I am running into problems when it doesn't.

The R code i am using is as follows:

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)

The result i am trying to obtain looks something like this (result from the second 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

There are two xmls, the first of which I can handle, the second not.

For the second one i get the following error: 

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

Example 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>

Example 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>

Any helpful insight is greatly appreciated.

dataframe r xml
2 Answers

I prefer the xml2 package over XML due to a simpler syntax. The solution here reads all of the "row" parent nodes and then parses each one of these to obtain a series of 1 line dataframes and then merges all of the results into the final answer.
The bind_rows() function from the dplyr package can handle the missing columns.
See the code comments for more details.

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>
user_57922440

Consider the internal XML method xmlAttrsToDataframe() using the triple colon operator to extract attributes to name fields with setNames:

df <- setNames(XML::xmlToDataFrame(xml.m_data, nodes=getNodeSet(xml.m_data, path="//Row")),
               unique(XML:::xmlAttrsToDataFrame(getNodeSet(xml.m_data, path='//Field')))$Name)

df

#    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:0
user_14224510
Related