How to Convert XML to DataFrame in R?

A Data Frame is a two-dimensional and tabular data structure in the R that is similar to a table in the database or an Excel spreadsheet. It is one of the most commonly used data structures for the data analysis in R with the columns representing the various and rows representing the observations.

XML(Extensible Markup Language)

XML is the markup language that can define the rules for encoding documents in a format that is both human-readable and machine-readable. It is commonly used for storing and transporting data and it can, especially in web services and configuration files.

Using the XML package for Conversion

The XML package in R can provide the tools for parsing and generating the XML documents. It allows for the extraction of the data nodes and attributes, making it possible to convert the XML data into the DataFrame.

1. Install the XML package

We can use the below command to install the XML package in the R programming.

install.packages("XML")

2. Load the XML package

We can use the below command to load the XML package in the R programming.

library(XML)

Step 3: Prepare the XML file

We can create the simple XML file for converting the Dataframe to save as example.xml in your local system.

<root>
  <record>
    <name>John</name>
    <age>30</age>
    <city>New York</city>
  </record>
  <record>
    <name>Jane</name>
    <age>25</age>
    <city>Los Angeles</city>
  </record>
  <record>
    <name>Mike</name>
    <age>35</age>
    <city>Chicago</city>
  </record>
</root>

Step 4: R Code to Convert XML to DataFrame

Here the R code to Convert XML to DataFrame of the R programming.

# Load the XML package
library(XML)

# Parse the XML file
xml_file <- xmlParse("C:/Users/Syam/OneDrive/Desktop/PHP Projects/example.xml")

# Extract the root node
root_node <- xmlRoot(xml_file)

# Convert XML to DataFrame
xml_data <- xmlToDataFrame(nodes = getNodeSet(root_node, "//record"))

# Print the DataFrame
print(xml_data)

Output:

  name age        city email phone
1 John  30    New York  <NA>  <NA>
2 Jane  25 Los Angeles  <NA>  <NA>
3 Mike  35     Chicago  <NA>  <NA>

• xmlParse: It can parses the XML file.
• xmlRoot: It can extracts the root node of XML document.
• getNodeSet: It can retrieves the nodes that match the given Xpath expression, in this case, all the <record> nodes.
• xmlToDataFrame: It can be converts the list of the XML nodes into the DataFrame.

Handling the XML Data Structure

XML files can have the complex structures with nested the nodes and attributes. It can handling such as the structures requires understanding the XML hierarchy and appropriately using the functions from the XML package.

<root>
  <record>
    <name>John</name>
    <age>30</age>
    <city>New York</city>
    <contacts>
      <email>[email protected]</email>
      <phone>1234567890</phone>
    </contacts>
  </record>
  <record>
    <name>Jane</name>
    <age>25</age>
    <city>Los Angeles</city>
    <contacts>
      <email>[email protected]</email>
      <phone>0987654321</phone>
    </contacts>
  </record>
  <record>
    <name>Mike</name>
    <age>35</age>
    <city>Chicago</city>
    <contacts>
      <email>[email protected]</email>
      <phone>1122334455</phone>
    </contacts>
  </record>
  <record>
    <name>Susan</name>
    <age>28</age>
    <city>San Francisco</city>
    <contacts>
      <email>[email protected]</email>
      <phone>5566778899</phone>
    </contacts>
  </record>
  <record>
    <name>Tom</name>
    <age>40</age>
    <city>Houston</city>
    <contacts>
      <email>[email protected]</email>
      <phone>2233445566</phone>
    </contacts>
  </record>
  <record>
    <name>Linda</name>
    <age>32</age>
    <city>Seattle</city>
    <contacts>
      <email>[email protected]</email>
      <phone>6677889900</phone>
    </contacts>
  </record>
</root>

Implementation of R Code to Handle Nested XML

Now we will Implemented R code for Handle Nested XML.

# Load the XML package
library(XML)

# Define the file path
file_path <- "C:/Users/Syam/OneDrive/Desktop/PHP Projects/example.xml"

# Parse the XML file
xml_file <- xmlParse(file_path)

# Extract the root node
root_node <- xmlRoot(xml_file)

# Extract individual elements including nested nodes
records <- getNodeSet(root_node, "//record")

# Initialize an empty list to store data
data_list <- list()

# Loop through each record node
for (i in 1:length(records)) {
  record <- records[[i]]
  
  # Extract data from the record
  name <- xmlValue(record[["name"]])
  age <- xmlValue(record[["age"]])
  city <- xmlValue(record[["city"]])
  email <- xmlValue(record[["contacts"]][["email"]])
  phone <- xmlValue(record[["contacts"]][["phone"]])
  
  # Combine the data into a list
  data_list[[i]] <- list(name = name, age = age, city = city, email = email, 
                         phone = phone)
}

# Convert the list to a DataFrame
xml_data <- do.call(rbind, lapply(data_list, as.data.frame))

# Print the DataFrame
print(xml_data)

Output:

   name age          city             email      phone
1  John  30      New York  [email protected] 1234567890
2  Jane  25   Los Angeles  [email protected] 0987654321
3  Mike  35       Chicago  [email protected] 1122334455
4 Susan  28 San Francisco [email protected] 5566778899
5   Tom  40       Houston   [email protected] 2233445566
6 Linda  32       Seattle [email protected] 6677889900

• getNodeSet: It can retrieves the nodes matching the XPath expression.
• xmlValue: It can extracts the value of the XML value.
• do.call: It can combines the list of the data frames into the single DataFrame.

Conclusion

Converting the XML to a DataFrame in the R involves parsing the XML file and extracting the relevant nodes, converting them into the structured the DataFrame. The XML package in R can provides the powerful tools to handle the XML data and it can allowing for the straightforward the data extraction and manipulation. Whether dealing with the simple or nested XML structures. Understanding the XML hierarchy and utilizing the appropriate the functionsn from the XML package are key to effectively converting the XML data to DataFrames in R.