Load xml "rows" into R data table

Question

I have some data shaped like this:

<people>
  <person first="Mary" last="Jane" sex="F" />
  <person first="Susan" last="Smith" sex="F" height="168" />
  <person last="Black" first="Joseph" sex="M" />
  <person first="Jessica" last="Jones" sex="F" />
</people>

I would like a data frame that looks like this:

    first  last sex height
1    Mary  Jane   F     NA
2   Susan Smith   F    168
3  Joseph Black   M     NA
4 Jessica Jones   F     NA

I've gotten this far:

library(XML)
xpeople <- xmlRoot(xmlParse(xml))
lst <- xmlApply(xpeople, xmlAttrs)
names(lst) <- 1:length(lst)

But I can't for the life of me figure out how to get the list into the data frame. I can get the list to be "square" (i.e. fill in the gaps) and then put it into a data frame:

lst <- xmlApply(xpeople, function(node) {
  attrs = xmlAttrs(node)
  if (!("height" %in% names(attrs))) {
    attrs[["height"]] <- NA
  }
  attrs
})
df = as.data.frame(lst)

But I have the following problems:

The data frame is transposed
first and last are Factors, not chr
height is a Factor, not numeric
the first and last names got swapped around for Joseph Black (not a big issue since my data is normally consistent, but annoying nonetheless)

How can I get the data frame in the correct form?

`plyr::rbind.fill(lapply(xmlToList(txt), function(x) as.data.frame(t(x), stringsAsFactors=FALSE)))` is perhaps a wee bit easier, although not an xml solution. You can then convert factor height to numeric with `as.numeric(` — user20650, Sep 30 '15 at 15:13
Actually http://stackoverflow.com/questions/2067098/how-to-transform-xml-data-into-a-data-frame?answertab=votes#tab-top might be useful — user20650, Sep 30 '15 at 15:22
There is a function to get attributes into a data.frame `XML:::xmlAttrsToDataFrame(xml["//person"])` — Chris S., Sep 30 '15 at 19:18

jlhoward · Accepted Answer · 2015-09-30T15:56:45.797

txt <- '<people>
          <person first="Mary" last="Jane" sex="F" />
          <person first="Susan" last="Smith" sex="F" height="168" />
          <person last="Black" first="Joseph" sex="M" />
          <person first="Jessica" last="Jones" sex="F" />
        </people>'
library(XML)         # for xmlTreeParse
library(data.table)  # for rbindlist(...)
xml <- xmlTreeParse(txt, asText=TRUE, useInternalNodes = TRUE)
rbindlist(lapply(xml["//person"],function(x)as.list(xmlAttrs(x))),fill=TRUE)
#      first  last sex height
# 1:    Mary  Jane   F     NA
# 2:   Susan Smith   F    168
# 3:  Joseph Black   M     NA
# 4: Jessica Jones   F     NA

You need as.list(xmlAttrs(...)) instead of just xmlAttrs(...) because rbindlist(...) wants each argument to be a list, not a vector.

Load xml "rows" into R data table

1 Answers1