6.323
Load Simple XML into Dataframe in R using xmlToDataFrame()

Introduction

This lessons demonstrates how to load simple row/column (2D) XML files directly into a data frame using xmlToDataFrame(). Once the data is loaded it can be further manipulated, analyzed, or stored in a database or other storage structure.

xmlToDataFrame() is a convenient way to load simple XML files into R for analysis. Such XML files are often the result of data exports from application, databases, systems, or third-parties.

Load 2L XML to Dataframe

Here is an example of a 2-dimensional XML that has a single level of child nodes in the same order for all nodes under the root.

<?xml version="1.0"?>

<document>
  <row>
    <Girth>8.3</Girth>
    <Height>70</Height>
    <Volume>10.3</Volume>
  </row>
  <row>
    <Girth>8.6</Girth>
    <Height>65</Height>
    <Volume>10.3</Volume>
  </row>
  
  ...

Let’s load this simple XML with a row column structure into a data frame using xmlToDataFrame(). The structure must be a root with child nodes that each have a single level child with a single value in the same order – essentially a row/column arrangement expressed in XML.

library(XML)

fn <- "treedata2L.xml"

# load from local file
df <- xmlToDataFrame(fn)

head(df, 3)

##   Girth Height Volume
## 1   8.3     70   10.3
## 2   8.6     65   10.3
## 3   8.8     63   10.2

By default, all columns are converted to character (text) variables. To keep them as text, use the parameter stringsAsFactors = F, for any other data type, you must use explicit coercion,

library(XML)

fn <- "treedata2L.xml"

# load from local file
df <- xmlToDataFrame(fn, stringsAsFactors = F)

df$Girth <- as.numeric(df$Girth)
df$Height <- as.numeric(df$Height)
df$Volume <- as.numeric(df$Volume)

head(df, 3)

##   Girth Height Volume
## 1   8.3     70   10.3
## 2   8.6     65   10.3
## 3   8.8     63   10.2

Load 3L XML to Dataframe

Attempting to load a multi-level XML into a dataframe will not work properly as the example below illustrates.

<?xml version="1.0"?>

<document>
  <row>
    <Girth>8.3</Girth>
    <dim>
      <Height>70</Height>
      <Width>65</Width>
    </dim>
    <Volume>10.3</Volume>
  </row>
  
  ...

library(XML)

fn <- "treedata3L.xml"

df <- xmlToDataFrame(fn)
head(df)

##   Girth  dim Volume
## 1   8.3 7065   10.3
## 2   8.6 6555   10.3
## 3   8.8 7369   10.2
## 4  10.5 7266   16.4

Notice how any XML elements beyond the second level are concatenated into a single string.

Load XML from URL

Rather than loading a local XML file, xmlToDataFrame() can also load an XML file from a URL. There are several ways to accomplish this; this is one way. Note that getURL() only supports http and not https.

library(XML)
library(RCurl)

# URL to XML file; must be http://
xml.url <- "http://artificium.us/lessons/06.r/l-6-323-load-xml-xmlToDataFrame/plant_catalog.xml"

xData <- getURL(xml.url)

df <- xmlToDataFrame(xData)
head(df, 2)

Again, all data is converted to text and must be explicitly converted to an appropriate data type.

df <- xmlToDataFrame(xData, stringsAsFactors = FALSE)
head(df)

df$BOTANICAL <- as.factor(df$BOTANICAL)
df$PRICE <- as.numeric(substring(df$PRICE, 2))
head(df)

Node Traversal

A complex XML must be parsed and loaded into a data frame through a combination of XPath expressions and node traversal of the DOM (Document Object Model). Below, we will illustrate how to traverse a multi-level XML using node traversal. It is not meant to be an exhaustive and complete treatment.

Recall that each node has this structure:

<document>
  <row>
    <Girth>8.3</Girth>
    <dim>
      <Height>70</Height>
      <Width>65</Width>
    </dim>
    <Volume>10.3</Volume>
  </row>

Let’s look at the code…

library(XML)

# load the XML into memory as a tree
xml.dom <- xmlParse("treedata3L.xml")

# get the root node  <document>
root <- xmlRoot(xml.dom)

# get the number of <row> nodes under the root
n <- xmlSize(root)

# set up a data frame to store the data; initially contains zero rows
# but will expand dynamically as rows are added
df <- data.frame(girth = numeric(),
                 height = integer(),
                 width = integer(),
                 volume = numeric()
                 )

# iterate over the <row> nodes
for (i in 1:n) {
  # get node i from the list of child nodes
  aRow <- root[[i]]
  
  # get value of <Girth> child
  girth <- xmlValue(aRow[[1]])
  
  # get value of <Height> and <Width> which are children of <dim>
  height <- xmlValue(aRow[[2]][[1]])
  width <- xmlValue(aRow[[2]][[2]])
  
  # get the value of <Volume>
  volume <- xmlValue(aRow[[3]])
  
  # add the values to a new row in the data frame
  # coerce text to appropriate data type
  df[i,'girth'] <- as.numeric(girth)
  df[i,'height'] <- as.integer(height)
  df[i,'width'] <- as.integer(width)
  df[i,'volume'] <- as.numeric(volume)
}

# print part of data frame to ensure it contains data from XML
head(df,2)

##   girth height width volume
## 1   8.3     70    65   10.3
## 2   8.6     65    55   10.3

Note that we set up an initial data frame with zero rows and as we add a new row at the end, the data frame’s size grows dynamically. Of course, we could have pre-allocated the data frame with the correct number of rows as each row corresponds to one child node.

The data is read from the XML as text (character) and must be explicitly coerced.

The root node is a list structure of child nodes and list elements are accessed with the [[]] operator. So, aRow[[2]][[1]] returns the first child of the second child of the node in aRow.

The function xmlValue() returns the value of a node, e.g., it returns foo for foo.

CAREFUL

The statement df[i,‘girth’] <- girth adds a new row but the “equivalent” statement df$girth[i] <- girth results in an error.

Summary

xmlToDataFrame() is a convenient way to load simple XML files into R for analysis. Such XML files are often the result of data exports from application, databases, systems, or third-parties.

More complex XML files must be processed with a combination of node traversal and XPath. See Lesson 6.303 Data Retrieval from XML via XPath in R.

Tutorial

The video tutorial below is a narration with examples of the concepts in this lesson.

---

Files & Resources

All Files for Lesson 6.323

---

References

No references.

Errata

None collected yet. Let us know.