Search code examples
rxmlxml2

Extracting data from XML files with messy path/node names


I am trying to extract values from institutional XML files with R. Here is an example of such file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<missions xmlns="http://sensored">
    <mission missiontype="2" year="2012" platform="4135" missionnumber="1" missiontypename="Referanseflåten-Hav" callsignal="LJBD" platformname="Nybo">
        <startdate>30/01/2012</startdate>
        <stopdate>28/11/2012</stopdate>
        <purpose></purpose>
        <fishstation serialno="86001">
            <nation>58</nation>
            <platform>4135</platform>
            <station>1</station>
            <startdate>30/01/2012</startdate>
            <starttime>18:56:00</starttime>
            <stopdate>30/01/2012</stopdate>
            <stoptime>23:19:00</stoptime>
            <latitudestart>65.13833</latitudestart>
            <longitudestart>-11.04833</longitudestart>
            <system>2</system>
            <area>59</area>
            <location>07</location>
            <bottomdepthstart>653.0</bottomdepthstart>
            <bottomdepthstop>653.0</bottomdepthstop>
            <fishingdepthmax>85.0</fishingdepthmax>
            <fishingdepthmin>40.0</fishingdepthmin>
            <gear>3711</gear>
            <gearcount>1</gearcount>
            <gearcondition>4</gearcondition>
            <trawlquality>7</trawlquality>
            <dataquality>2</dataquality>
            <catchsample species="161722.G03" samplenumber="1" noname="sild'G03" aphia="126417">
                <sampletype>20</sampletype>
                <conservation>1</conservation>
                <producttype>1</producttype>
                <weight>10.0</weight>
                <count>46</count>
                <sampleproducttype>1</sampleproducttype>
                <lengthmeasurement>E</lengthmeasurement>
                <lengthsampleweight>0.863</lengthsampleweight>
                <lengthsamplecount>4</lengthsamplecount>
                <specimensamplecount>4</specimensamplecount>
                <individual specimenno="1">
                    <producttype>1</producttype>
                    <weight>0.222</weight>
                    <lengthunit>2</lengthunit>
                    <length>0.28</length>
                </individual>
                <individual specimenno="2">
                    <producttype>1</producttype>
                    <weight>0.127</weight>
                    <lengthunit>2</lengthunit>
                    <length>0.245</length>
                </individual>
                <individual specimenno="3">
                    <producttype>1</producttype>
                    <weight>0.172</weight>
                    <lengthunit>2</lengthunit>
                    <length>0.26</length>
                </individual>
                <individual specimenno="4">
                    <producttype>1</producttype>
                    <weight>0.342</weight>
                    <lengthunit>2</lengthunit>
                    <length>0.325</length>
                </individual>
            </catchsample>
        </fishstation>
    </mission>
</missions>

I manage to parse the file with no problems. While the names appear correct in the parsed list, I cannot get them parsed correctly for usage of XPath language:

library(xml2)
library(xmltools)
doc <- xml2::read_xml("test.xml")
doc %>% xmltools::xml_view_trees()
#└── mission
#  ├── startdate
#  ├── stopdate
#  ├── purpose
#  └── fishstation
#    ├── nation
#    ├── platform
#    ├── station
#    ├── startdate
#    ├── starttime
#    ├── stopdate
#    ├── stoptime
#    ├── latitudestart
#    ├── longitudestart
#    ├── system
#    ├── area
#    ├── location
#    ├── bottomdepthstart
#    ├── bottomdepthstop
#    ├── fishingdepthmax
#    ├── fishingdepthmin
#    ├── gear
#    ├── gearcount
#    ├── gearcondition
#    ├── trawlquality
#    ├── dataquality
#    └── catchsample
#      ├── sampletype
#      ├── conservation
#      ├── producttype
#      ├── weight
#      ├── count
#      ├── sampleproducttype
#      ├── lengthmeasurement
#      ├── lengthsampleweight
#      ├── lengthsamplecount
#      ├── specimensamplecount
#      ├── individual
#        ├── producttype
#        ├── weight
#        ├── lengthunit
#        └── length
#      ├── individual
#        ├── producttype
#        ├── weight
#        ├── lengthunit
#        └── length
#      ├── individual
#        ├── producttype
#        ├── weight
#        ├── lengthunit
#        └── length
#      └── individual
#        ├── producttype
#        ├── weight
#        ├── lengthunit
#        └── length
doc %>% xmltools::xml_get_paths()
#[[1]]
# [1] "/*/*"         "/*/*/*"       "/*/*/*"       "/*/*/*"       #"/*/*/*"       "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     #"/*/*/*/*"     "/*/*/*/*"    
# [12] "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     #"/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     #"/*/*/*/*"     "/*/*/*/*"    
# [23] "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     "/*/*/*/*"     #"/*/*/*/*"     "/*/*/*/*/*"   "/*/*/*/*/*"   "/*/*/*/*/*"   "/*/*/*/*/*"   #"/*/*/*/*/*"   "/*/*/*/*/*"  
# [34] "/*/*/*/*/*"   "/*/*/*/*/*"   "/*/*/*/*/*"   "/*/*/*/*/*"   #"/*/*/*/*/*"   "/*/*/*/*/*/*" "/*/*/*/*/*/*" "/*/*/*/*/*/*" "/*/*/*/*/*/*" #"/*/*/*/*/*"   "/*/*/*/*/*/*"
# [45] "/*/*/*/*/*/*" "/*/*/*/*/*/*" "/*/*/*/*/*/*" "/*/*/*/*/*"   #"/*/*/*/*/*/*" "/*/*/*/*/*/*" "/*/*/*/*/*/*" "/*/*/*/*/*/*" "/*/*/*/*/*"   #"/*/*/*/*/*/*" "/*/*/*/*/*/*"
# [56] "/*/*/*/*/*/*" "/*/*/*/*/*/*"

Say I want to extract longitude and latitude for each fishstation together with species code and individual weight for each sampled fish. I can do this using not-so memory efficient way by converting the XML document to a list and using standard R code:

library(XML)

doc <- xmlInternalTreeParse("test.xml")

getNodeSet(doc, "//fishstation") # Does not work
getNodeSet(doc, "/*/*/*/*") # Works

tmp <- xmlToList(doc)[[1]]
tmp <- tmp[names(tmp) == "fishstation"]

lapply(tmp, function(k) {

  sp <- k$catchsample$.attrs
  inds <- k$catchsample
  inds <- inds[names(inds) == "individual"]

  data.frame(lon = k$longitudestart, lat = k$latitudestart, 
sp = unname(sp[names(sp) == "species"]), 
weight = unname(sapply(inds, function(j) j$weight)))
})

# $fishstation
#        lon             lat         sp   weight
# 1 -11.04833        65.13833 161722.G03  0.222
# 2 -11.04833        65.13833 161722.G03  0.127
# 3 -11.04833        65.13833 161722.G03  0.172
# 4 -11.04833        65.13833 161722.G03  0.342

It would, however, be extremelly convenient to be able to use the XPath language for this as such an approach would make the process a lot quicker (my files can be several gigabytes). So far I have not had luck in tweaking the extraction procedure such that using XPath language would work for these files. I am well aware that this might be due to a fundamental user error. I have not worked with XML files previously.

1) How do I read the example file to R such that node names are preserved?

2) How do I extract the values in the example above a more memory efficient way?


Solution

  • What makes this file tricky is the name space in the xml text. The xml2 package has a function to strip out the namespace which makes the process easier.
    Once that is done it is a matter of finding the individuals and then moving up the tree to find the parent nodes and then extract the requested information.

    library(xml2)
    #read the file in as xml
    page<-read_xml("fish.xml")
    #strip the name space out
    page<-xml_ns_strip(page)
    
    #find all of the individuals
    individuals<-xml_find_all(page, ".//individual")
    #find the parent catchsample nodes for the individuals
    catchnodes<-xml_find_first( individuals, ".//parent::catchsample")
    #find the parents for the catchsample node the fishstation
    fishstations<-xml_find_first( catchnodes, ".//parent::fishstation ")
    
    #individuals, catchnodes and fishstations should all have the same length
    
    #extract the desired information from each set of nodes
    weights<-xml_text(xml_find_first(individuals, ".//weight"))
    lat<-xml_text(xml_find_first(fishstations, ".//latitudestart"))
    long<-xml_text(xml_find_first(fishstations, ".//longitudestart"))
    species<-xml_attr(catchnodes, "species")  #information is an attribute
    
    #package together
    data.frame(lat, long, species, weights)
    #        lat      long    species weights
    # 1 65.13833 -11.04833 161722.G03   0.222
    # 2 65.13833 -11.04833 161722.G03   0.127
    # 3 65.13833 -11.04833 161722.G03   0.172
    # 4 65.13833 -11.04833 161722.G03   0.342
    

    Here is the same code but without stripping out the namespace:

    page<-read_xml("fish.xml")
    ns <- xml_ns(page)
    
    #find all of the individuals
    individuals<-xml_find_all(page, ".//d1:individual", ns)
    
    catchnodes<-xml_find_first(individuals, ".//parent::d1:catchsample", ns)
    fishstations<-xml_find_first(catchnodes, ".//parent::d1:fishstation", ns)
    
    weights<-xml_text(xml_find_first(individuals, ".//d1:weight", ns))
    lat<-xml_text(xml_find_first(fishstations, ".//d1:latitudestart", ns))
    long<-xml_text(xml_find_first(fishstations, ".//d1:longitudestart", ns))
    species<-xml_attr(catchnodes, "species")