How to transform XML data into a data.frame?

by

Ordinarily, I would suggest trying the xmlToDataFrame() function, but I believe that this will actually be fairly tricky because it isn’t well structured to begin with.

I would recommend working with this function:

xmlToList(books)

One problem is that there are multiple authors per book, so you will need to decide how to handle that when you’re structuring your data frame.

Once you have decided what to do with the multiple authors issue, then it’s fairly straight forward to turn your book list into a data frame with the ldply() function in plyr (or just use lapply and convert the return value into a data.frame by using do.call(“rbind”…).

Here’s a complete example (excluding author):

library(XML)
books <-  "w3schools.com/xsl/books.xml"
library(plyr)
ldply(xmlToList(books), function(x) { data.frame(x[!names(x)=="author"]) } )

   .id        title.text title..attrs year price   .attrs
 1 book  Everyday Italian           en 2005 30.00  COOKING
 2 book      Harry Potter           en 2005 29.99 CHILDREN
 3 book XQuery Kick Start           en 2003 49.99      WEB
 4 book      Learning XML           en 2003 39.95      WEB

Here’s what it looks like with author included. You need to use ldply in this instance since the list is “jagged”…lapply can’t handle that properly. [Otherwise you can use lapply with rbind.fill (also courtesy of Hadley), but why bother when plyr automatically does it for you?]:

ldply(xmlToList(books), data.frame)

   .id        title.text title..attrs              author year price   .attrs
1 book  Everyday Italian           en Giada De Laurentiis 2005 30.00  COOKING
2 book      Harry Potter           en        J K. Rowling 2005 29.99 CHILDREN
3 book XQuery Kick Start           en      James McGovern 2003 49.99      WEB
4 book      Learning XML           en         Erik T. Ray 2003 39.95      WEB
     author.1   author.2   author.3               author.4
1        <NA>       <NA>       <NA>                   <NA>
2        <NA>       <NA>       <NA>                   <NA>
3 Per Bothner Kurt Cagle James Linn Vaidyanathan Nagarajan
4        <NA>       <NA>       <NA>                   <NA>

More Related Contents:

  1. R: convert XML data to data frame
  2. How to parse XML to R data frame
  3. How to parse an XML file to an R data frame?
  4. Scrape password-protected website in R
  5. Combine values in huge XML-files
  6. Read XML in spark
  7. How to webscrape secured pages in R (https links) (using readHTMLTable from XML package)?
  8. extract data from raw html in R
  9. Unable to install R package in Ubuntu 11.04 [closed]
  10. “Non Zero Exit Status” R 3.0.1 ‘XML’ and ‘RCurl’
  11. How can I use xpath querying using R’s XML library?
  12. How to create xml from R objects, e.g., is there a ‘listToXml’ function?
  13. How to link XML to XSD using schemaLocation or noNamespaceSchemaLocation?
  14. How to succinctly write a formula with many variables from a data frame?
  15. subtract value from previous row by group
  16. How to add a newline (line break) in XML file?
  17. calculate the mean for each column of a matrix in R
  18. Must an XML namespace name URI be retrievable?
  19. How to force wrap on table entries
  20. Change value of variable with dplyr
  21. Creating a new variable from a lookup table
  22. Escape double quote character in XML
  23. XML Namespace URI with HTTPS?
  24. Read and rbind multiple csv files
  25. How can I convert row names into the first column?
  26. R- how to dynamically name data frames? [duplicate]
  27. Removing specific rows from a dataframe
  28. Add a new column to a dataframe using matching values of another dataframe [duplicate]
  29. Edit installed XML file according to user preferences in Inno Setup
  30. Return pmin or pmax of data.frame with multiple columns

Leave a Comment