Geek Answers Handbook

Reading data from an XML file using R

From reading, then having tried several previous examples in StackOverflow related to reading an XML file in R, it seems that due to the “uneven” nature of the next file, I cannot use XPath-related methods.

https://www.dropbox.com/s/jz8sj2fifuobkva/Data.xml?oref=e&n=305307914

Therefore, it seems to me that I need to use a combination of xmlToList () and ldply () to read data from the next file.

In particular, for all 20 events in the file (i.e. event.1, event.2, ... event.20), I want to get the following variables (structured as)

  • $movements$movement$clips$clip$data$event$begin (vector)
  • $movements$movement$clips$clip$data$event$end (vector)
  • $movements$movement$clips$clip$data$event$max$cells (data frame)
  • As indicated above, but $rollover$data$quant$cellswhere in the event there are several samples (n data frames)

Based on other StackOverflow code examples (using R v3.1.2) I tried to read the data "start" as follows: -

library(XML)
library(plyr)

datfile <- "D:/Data.xml"
xmlfile <- xmlTreeParse(datfile,useInternal = TRUE)
sampledata <- xmlToList(xmlfile)
startdata <- ldply(sampledata$movements$movement$clips$clip$data$event$begin)

When I do this, I get the first variable (0.240) in the .1 event. Now I got to the point of being stuck and exhausted my research on how to do this.

+4
source share
2 answers

If you are ready to give away xml2per turn, you can get to beginin a few lines:

library(xml2)
library(magrittr)

# get a vector

doc <- read_xml("~/Dropbox/Data.xml")

doc %>%
  xml_find_all("//d1:event/d1:begin", ns=xml_ns(doc)) %>%
  xml_text() %>%
  as.numeric()

##  [1] 0.24 0.73 1.25 1.75 2.24 2.75 3.27 3.76 4.30 4.77 5.28 5.78 6.32 6.82
## [15] 7.34 7.85 8.37 8.86 9.39 9.89

# get data frames

library(stringr)

make_df <- function(txt) {

  txt %>%
    str_split("\n") %>% extract2(1) %>%
    str_trim() %>%
    textConnection() -> con

  dat <- read.table(con)
  close(con)

  dat

}

doc %>%
  xml_find_all("//d1:max/d1:cells", ns=xml_ns(doc)) %>%
  xml_text() %>%
  lapply(make_df) -> df_list

df_list[[1]]

##     V1   V2   V3   V4   V5   V6   V7   V8   V9 V10 V11 V12
## 1  0.0  0.0  1.5  3.5  3.0  1.5  0.0  0.0  0.0 0.0 0.0   0
## 2  0.0  1.0  5.5  8.5  7.0  3.5  2.0  2.0  1.0 0.0 0.0   0
## 3  0.0  3.0  9.0 13.0  9.0  4.0  3.0  3.5  2.5 1.0 0.0   0
## 4  0.0  4.5 11.0 14.0  9.0  4.0  3.0  4.0  4.0 2.0 0.0   0
## 5  0.0  4.0 10.5 12.0  7.5  4.0  3.0  4.0  4.5 3.0 0.0   0
## 6  0.0  4.5  8.5 10.0  8.0  7.5  6.5  4.5  4.0 2.5 0.0   0
## 7  2.0  8.0 14.5 16.0 14.0 13.5 13.0  9.5  5.5 2.5 0.0   0
## 8  3.5 12.0 20.0 20.5 18.0 18.0 18.0 14.5  9.0 4.0 1.5   0
## 9  4.5 12.5 20.5 21.0 18.0 18.0 18.5 16.0 11.5 6.5 2.5   0
## 10 4.5 12.0 19.0 20.0 17.5 17.5 18.0 16.5 12.5 7.5 3.5   0
## 11 3.5  9.5 15.5 16.5 15.0 14.5 14.5 14.0 11.5 8.0 4.0   1
## 12 2.0  6.5 10.0 12.0 11.0 11.0 12.0 12.0 10.5 7.5 4.0   0
## 13 1.5  4.5  6.5  7.0  7.0  7.0  8.0  9.0  8.0 6.5 3.5   0
## 14 1.0  4.0  5.5  5.5  5.5  5.5  6.0  6.0  6.0 4.5 2.5   0
## 15 1.5  4.5  6.0  5.5  5.5  5.5  5.5  5.5  5.5 4.0 2.0   0
## 16 2.0  5.0  7.0  7.0  6.0  6.0  6.0  6.0  5.5 4.0 1.5   0
## 17 2.5  5.5  7.5  7.5  7.0  7.0  6.5  6.5  5.5 4.0 1.5   0
## 18 2.0  5.5  7.0  7.5  7.5  7.5  7.5  6.5  5.5 3.5 0.0   0
## 19 2.5  5.5  7.5  8.0  7.5  8.0  7.5  6.5  5.0 2.5 0.0   0
## 20 2.0  5.0  6.5  7.5  7.5  8.0  7.5  6.5  4.5 2.0 0.0   0
## 21 1.5  4.0  6.0  7.5  8.5  8.5  8.0  6.0  3.5 1.0 0.0   0
## 22 1.0  3.5  6.5  8.5  9.5  9.5  8.0  5.5  3.0 0.0 0.0   0
## 23 0.0  4.0  8.0 11.0 12.5 11.0  8.5  5.5  2.5 0.0 0.0   0
## 24 0.0  4.5  9.5 13.5 14.5 12.0  8.5  5.5  2.0 0.0 0.0   0
## 25 0.0  5.5 13.0 17.5 17.0 14.5  9.5  5.5  1.5 0.0 0.0   0
## 26 0.0  6.5 16.0 21.0 19.5 15.5 10.0  5.0  1.0 0.0 0.0   0
## 27 0.0  7.0 17.0 22.5 21.0 16.0 10.0  5.0  0.0 0.0 0.0   0
## 28 0.0  7.0 17.5 22.5 20.5 15.5  9.0  3.5  0.0 0.0 0.0   0
## 29 0.0  5.5 14.5 20.5 18.5 14.0  8.0  2.5  0.0 0.0 0.0   0
## 30 0.0  3.5 10.0 14.5 14.0 10.0  5.0  1.0  0.0 0.0 0.0   0
## 31 0.0  1.5  5.5  8.5  8.0  5.5  2.5  0.0  0.0 0.0 0.0   0
## 32 0.0  0.0  0.0  2.5  2.5  0.0  0.0  0.0  0.0 0.0 0.0   0

length(df_list)

## [1] 20

# get the deeply nested ones

quant_cells <- function(node) {
  node %>%
    xml_find_all("./d1:data/d1:quant/d1:cells", ns=xml_ns(doc)) %>%
    xml_text() %>%
    lapply(make_df)
}

doc %>%
  xml_find_all("//d1:rollover", ns=xml_ns(doc)) %>%
  as_list() %>%
  lapply(quant_cells) -> quant_df_list

length(quant_df_list)

## [1] 20

length(quant_df_list[[1]])

## [1] 63

quant_df_list[[1]]

## [[1]]
##    V1  V2  V3  V4  V5 V6
## 1 0.0 0.0 0.0 0.0 0.0  0
## 2 0.0 0.0 0.2 0.0 0.0  0
## 3 0.0 0.5 1.7 0.5 0.0  0
## 4 0.5 2.7 3.4 2.3 0.3  0
## 5 2.3 4.3 4.4 3.0 0.4  0
## 6 3.2 4.8 4.8 3.3 0.4  0
## 7 2.2 4.1 3.8 2.3 0.3  0
## 8 0.3 1.4 1.4 0.4 0.0  0
## 
## [[2]]
##    V1  V2   V3   V4  V5  V6  V7  V8 V9
## 1 0.0 0.0  0.0  0.0 0.0 0.0 0.0 0.0  0
## 2 0.0 0.3  0.9  1.3 1.1 0.4 0.0 0.0  0
## 3 0.2 2.2  4.5  5.9 4.7 2.0 0.2 0.0  0
## 4 1.0 5.3  8.5  9.1 7.1 3.7 0.4 0.0  0
## 5 2.9 8.3 12.0 11.6 9.0 5.4 1.0 0.0  0
## 6 3.5 9.2 13.5 12.9 9.6 5.8 1.5 0.1  0
## 7 3.0 8.2 11.6 11.3 8.3 4.4 0.5 0.0  0
## 8 1.1 3.7  6.4  6.3 4.0 1.8 0.2 0.0  0
## 9 0.0 0.2  1.4  1.5 0.3 0.0 0.0 0.0  0
## ...
## (down to [[63]])
+3
source

I am using a package rvestto solve this problem:

urlpath_parse<-html("https://www.dropbox.com/s/jz8sj2fifuobkva/Data.xml?oref=e&n=305307914")

data_begin<-urlpath_parse%>%
           html_nodes("begin")%>% #if you use only html_node, it will give only 0.240
           html_text()
[1] "0.240" "0.240" "0.730" "0.730" "1.250" "1.250" "1.750" "1.750" "2.240" "2.240" "2.750" "2.750" "3.270" "3.270" "3.760" "3.760" "4.300"
[18] "4.300" "4.770" "4.770" "5.280" "5.280" "5.780" "5.780" "6.320" "6.320" "6.820" "6.820" "7.340" "7.340" "7.850" "7.850" "8.370" "8.370"
[35] "8.860" "8.860" "9.390" "9.390" "9.890" "9.890"

data_end<-urlpath_parse%>%
     html_nodes("end")%>% #if you use only html_node, it will give only 0.240
     html_text()
> data_end
 [1] "0.870"  "1.380"  "1.890"  "2.370"  "2.880"  "3.400"  "3.890"  "4.420"  "4.910"  "5.410"  "5.900"  "6.440"  "6.950"  "7.460"  "7.980" 
[16] "8.490"  "9.000"  "9.520"  "10.010" "10.500"
+2
source

More articles:


All Articles