Convert XML to dataframe

Question

I'm aware that this has been an issue many times. However, I don't succeed in converting my xml properly.

This is a data extract of my data. There are several thousand more cases.

  
  
  
  5224 
  TSKWE 
  834734 
  kd-3465-3456-df 
  kdsdf-9834 
  99 
  
  Test AG 
  Test 
  6.2478.3285 
  
  2021-04-14T09:42:23.194+02:00 
  2021-03-31 
  1 
  false 
  true 
  false 
  
  
  
  
  
  258698554895 
  
  NA.8934 
  578789 
  
  
  KI.4785 
  5787893 
  
  
  KI.4755 
  57878987 
  
  Muster 
  Karl 
  Muster 
  1 
  
  1901-01-01 
  
  
  
  Muster 
  Karl 
  Muster 
  Karl 
  
  
  
  1901-01-01 
  
  
  
  657888 
  Test 
  FP 
  45778 
  
  
  1 
  
  
  22 
  1901-01-01 
  
  
  9 
  1901-01-01 
  
  
  27 
  
  
  978754 
  ZZ 
  ZUZI 
  
  
  
  TE 
  
  Lachmanan 
  ZI 
  45345 
  
  
  
  
  
  567567 
  Tschicki 
  ZI 
  246768 
  
  1901-01-01 
  
  
   
   
   
   
  
  
  
  457575454545 
  7 
  54678967864/4 
  
  Isadorastr. 
  6 
  Tschidi 
  899663 
  00 
  ZI 
  
  787 
  1908-04-17

I would like to have a dataframe where each person is mapped in one row. The key element is "reportedPerson".

I manage to get each person in a row, but the values are all in one column without any separator.

doc <- xmlParse('/path/to/Quelle/Response.xml')
nsDefs <- xmlNamespaceDefinitions(doc)
ns <- structure(sapply(nsDefs, function(x) x$uri), names = names(nsDefs))
nsDefs[[1]][["id"]] <- "x"
names(ns)[1] <- "x"

xpathSApply(doc, "//x:reportedPerson", namespaces=ns)

reportedPerson <- getNodeSet(doc, "//x:reportedPerson", namespaces=ns, fun=xmlToList)
df <- data.frame(do.call(rbind, reportedPerson))
head(df)

Do you have any ideas on how to get the multitude of variables into individual columns?

user_5792244 · Answer

Here is a solution using the xml2 package. The strategy is to find the reported persons nodes and the parse out all of the subnodes. There are few duplicated node names and I attempted to reduce the number of conflicts, see the comments for more details. You may have to use the node's paths as the dataframe' column names.

library(dplyr)
library(xml2)

#identify namespace
ns<-xml_ns(doc)

#strip  namespace for ease of handling
#xml_ns_strip(doc)

#find the reportedPerson Nodes
reportedPersons <- xml_find_all(doc, ".//d1:reportedPerson")

dfs<- lapply(reportedPersons, function(reportedPerson) {
  
  #find all the children nodes
  allchildren <- reportedPerson %>% xml_find_all( './/*') 
  
  #count the number of childern in each node
  branches <-reportedPerson %>% xml_find_all( './/*') %>% xml_length(only_elements = TRUE)
  
  #filter to keep the lowest level - nodes with values - no children
  leafs <- allchildren[which(branches==0)]
  
  #get valuse and names of the leafs
  values <- leafs %>% xml_text()
  names <- leafs %>% xml_name() #xml_name(ns=ns)
  #get names of the parents - still results in a few duplicates
  parentNames <-  sapply(leafs, function(leaf){xml_parent(leaf) %>% xml_name()})
 
  #put into a dataframe 
  df <- as.data.frame(t(values))
  names(df) = paste(parentNames, names, sep="_")
  #there are still a few duplicates could use full path names to avoid dulicates
  # names(df) = xml_path(leafs)
  
  df
})

answer <- bind_rows(dfs)

Convert XML to dataframe

1 Answers