Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert xml to a nested data frame
``` r
library(xml2)
library(dplyr)
library(purrr)
library(stringr)
# From the root node:
# If has_children, then recurse.
# Otherwise, attributes, value and children (nested) to data frame.
xml_to_df <- function(doc, ns = xml_ns(doc)) {
node_to_df <- function(node) {
# Filter the attributes for ones that aren't namespaces
# x <- list(.index = 0, .name = xml_name(node, ns))
x <- list(.name = xml_name(node, ns))
# Attributes as column headers, and their values in the first row
attrs <- xml_attrs(node)
if (length(attrs) > 0) {attrs <- attrs[!grepl("xmlns", names(attrs))]}
if (length(attrs) > 0) {x <- c(x, attrs)}
# Build data frame manually, to avoid as.data.frame's good intentions
children <- xml_children(node)
if (length(children) >= 1) {
x <-
children %>%
# Recurse here
map(node_to_df) %>%
split_by(".name") %>%
map(bind_rows) %>%
map(list) %>%
{c(x, .)}
attr(x, "row.names") <- 1L
class(x) <- c("tbl_df", "data.frame")
} else {
x$.value <- xml_text(node)
}
x
}
node_to_df(doc)
}
xml <- "
<messages>
<note id=\"501\">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
<note id=\"502\">
<to>Jani</to>
<from>Tove</from>
<heading>Re: Reminder</heading>
<body>I will not</body>
</note>
</messages>"
xml_df <- xml_to_df(read_xml(xml))
xml_df
#> Source: local data frame [1 x 2]
#>
#> .name note
#> (chr) (chr)
#> 1 messages <tbl_df [2,6]>
xml_df$note
#> [[1]]
#> Source: local data frame [2 x 6]
#>
#> .name id body from heading to
#> (chr) (chr) (chr) (chr) (chr) (chr)
#> 1 note 501 <tbl_df [1,2]> <tbl_df [1,2]> <tbl_df [1,2]> <tbl_df [1,2]>
#> 2 note 502 <tbl_df [1,2]> <tbl_df [1,2]> <tbl_df [1,2]> <tbl_df [1,2]>
tidyr::unnest(xml_df$note[[1]], body)
#> Source: local data frame [2 x 7]
#>
#> .name id from heading to .name
#> (chr) (chr) (chr) (chr) (chr) (chr)
#> 1 note 501 <tbl_df [1,2]> <tbl_df [1,2]> <tbl_df [1,2]> body
#> 2 note 502 <tbl_df [1,2]> <tbl_df [1,2]> <tbl_df [1,2]> body
#> Variables not shown: .value (chr)
x <- read_xml('
<root>
<doc1 xmlns = "http://foo.com"><baz /></doc1>
<doc2 xmlns = "http://bar.com"><baz /></doc2>
</root>
')
xml_to_df(x)
#> Source: local data frame [1 x 3]
#>
#> .name d1:doc1 d2:doc2
#> (chr) (chr) (chr)
#> 1 root <tbl_df [1,2]> <tbl_df [1,2]>
# jennybc's linked code to import xml from Google sheets
library(googlesheets)
library(httr)
pts_ws_feed <- "https://spreadsheets.google.com/feeds/worksheets/1amnxLg9VVDoE6KSIZvutYkEGNgQyJSnLJgHthehruy8/public/full"
(ss <- gs_ws_feed(pts_ws_feed))
#> Spreadsheet title: test-gs-public-testing-sheet
#> Spreadsheet author: rpackagetest
#> Date of googlesheets registration: 2016-06-03 00:02:56 GMT
#> Date of last spreadsheet update: 2016-03-11 05:37:48 GMT
#> visibility: public
#> permissions: rw
#> version: new
#>
#> Contains 8 worksheets:
#> (Title): (Nominal worksheet extent as rows x columns)
#> embedded_empty_cells: 8 x 7
#> special_chars: 1000 x 26
#> diabolical_column_names: 4 x 8
#> shipwrecks: 1000 x 26
#> for_resizing: 1799 x 30
#> for_updating: 1000 x 26
#> empty: 1000 x 26
#> colnames_only: 1000 x 26
#>
#> Key: 1amnxLg9VVDoE6KSIZvutYkEGNgQyJSnLJgHthehruy8
#> Browser URL: https://docs.google.com/spreadsheets/d/1amnxLg9VVDoE6KSIZvutYkEGNgQyJSnLJgHthehruy8/
col_names <- TRUE
ws <- "embedded_empty_cells"
index <- match(ws, ss$ws$ws_title)
the_url <- ss$ws$listfeed[index]
req <- GET(the_url)
rc <- read_xml(content(req, as = "text", encoding = "UTF-8"))
ns <- xml_ns_rename(xml_ns(rc), d1 = "feed")
# Parsing jennybc's xml with xml_to_df
x <- xml_to_df(rc, ns)
x$`feed:entry`[[1]] %>%
select(starts_with("gsx:")) %>%
map(bind_rows, .id = "row") %>%
bind_rows
#> Source: local data frame [23 x 3]
#>
#> row .name .value
#> (chr) (chr) (chr)
#> 1 1 gsx:_chk2m Americas
#> 2 3 gsx:_chk2m Americas
#> 3 4 gsx:_chk2m Americas
#> 4 1 gsx:country Argentina
#> 5 2 gsx:country Argentina
#> 6 3 gsx:country
#> 7 4 gsx:country Argentina
#> 8 1 gsx:gdppercap 5911.315053
#> 9 2 gsx:gdppercap 6856.856212
#> 10 3 gsx:gdppercap 7133.166023
#> .. ... ... ...
```
@jennybc

This comment has been minimized.

Copy link

@jennybc jennybc commented Jun 2, 2016

Yes, I obviously agree that XML can be productively wrangled in a data frame :)

https://github.com/jennybc/manipulate-xml-with-purrr-dplyr-tidyr

I think you need a library(xml2) up top?

The more XML I look at, the more I realize that, in general, it's going to be really hard to make this general. I see data in attributes only, data in element nodes only, data at the top level, data several layers down, and all possible combinations of those things. And OMG namespaces. But yes I agree it would be awesome to identify patterns that keep coming up when doing "nested thing --> data frame", whether it's a native list, XML, or JSON.

@nacnudus

This comment has been minimized.

Copy link
Owner Author

@nacnudus nacnudus commented Jun 2, 2016

Thanks for your comments!

library(xml2) added.

You make good point about data at the top level vs deeper. An earlier draft added the data at every level, rather than only leaf nodes:

xml_to_df <- function(node) {
  x <- c(.name = xml_name(node),
         as.list(xml_attrs(node)),
         .value = xml_text(node)) %>% # data added here instead of in the 'else' statement
       as.data.frame(stringsAsFactors = FALSE)

But the trouble is that libxml2, which xml2 and XML both depend on, returns all the data in a node, including child nodes, so in this case the root node would have "ToveJaniReminder...I will not". In some cases that's useful (e.g. parsing in-cell-formatted text in xlsx), but more often it's a pain.

Another hitch is that unnest only copes with columns of identically-structured data frames, so NULLs scupper it.

One way to handle namespaces is to use them as part of the names, which xml2 already does. I've updated the code accordingly.

@nacnudus

This comment has been minimized.

Copy link
Owner Author

@nacnudus nacnudus commented Jun 3, 2016

@jennybc Your parsing code can now be reduced somewhat using xml_to_df (see gist for full code):

x <- xml_to_df(rc, ns)
x$`feed:entry`[[1]] %>% 
  select(starts_with("gsx:")) %>%
  map(bind_rows, .id = "row") %>% 
  bind_rows
#> Source: local data frame [23 x 3]
#> 
#>      row         .name      .value
#>    (chr)         (chr)       (chr)
#> 1      1    gsx:_chk2m    Americas
#> 2      3    gsx:_chk2m    Americas
#> 3      4    gsx:_chk2m    Americas
#> 4      1   gsx:country   Argentina
#> 5      2   gsx:country   Argentina
#> 6      3   gsx:country            
#> 7      4   gsx:country   Argentina
#> 8      1 gsx:gdppercap 5911.315053
#> 9      2 gsx:gdppercap 6856.856212
#> 10     3 gsx:gdppercap 7133.166023
#> ..   ...           ...         ...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment