R XML - combining parent and child nodes(w same name) into data frame

Question

I have XML like this:

<SoccerFeed timestamp="20181123T153249+0000">
  <SoccerDocument season_name="Season 2016/2017" season_id="2016" competition_name="French Ligue 1" competition_id="24" competition_code="FR_L1" Type="SQUADS Latest">
    <Team web_address="www.angers-sco.fr" uID="t2128" short_club_name="Angers" region_name="Europe" region_id="17" country_iso="FR" country_id="8" country="France">
      <Founded>1919</Founded>
      <Name>Angers</Name>
      <Player uID="p40511">
        <Name>Denis Petric</Name>
        <Position>Goalkeeper</Position>
        <Stat Type="first_name">Denis</Stat>
        <Stat Type="last_name">Petric</Stat>
        <Stat Type="birth_date">1988-05-24</Stat>
        <Stat Type="weight">83</Stat>
        <Stat Type="height">187</Stat>
        <Stat Type="jersey_num">1</Stat>
        <Stat Type="real_position">Goalkeeper</Stat>
        <Stat Type="real_position_side">Unknown</Stat>
        <Stat Type="join_date">2016-01-02</Stat>
        <Stat Type="country">Slovenia</Stat>
      </Player>
      <Player uID="p119744">
        <Name>Mathieu Michel</Name>
        <Position>Goalkeeper</Position>
        <Stat Type="first_name">Mathieu</Stat>
        <Stat Type="last_name">Michel</Stat>
        <Stat Type="birth_date">1991-09-04</Stat>
        <Stat Type="birth_place">Nîmes</Stat>
        <Stat Type="first_nationality">France</Stat>
        <Stat Type="preferred_foot">Right</Stat>
        <Stat Type="weight">84</Stat>
        <Stat Type="height">189</Stat>
        <Stat Type="jersey_num">1</Stat>
        <Stat Type="real_position">Goalkeeper</Stat>
        <Stat Type="real_position_side">Unknown</Stat>
        <Stat Type="join_date">2016-08-18</Stat>
        <Stat Type="country">France</Stat>
      </Player>

So far I ran the following code:

library(tidyverse)
library(xml2)

x <- read_xml('player.xml')

Players3 <- x %>% 
  xml_find_all('//Player') %>% 
  map_df(~flatten(c(xml_attrs(.x), 
                map(xml_children(.x), 
                    ~set_names(as.list(xml_text(.x)), xml_name(.x)))))) %>%
type_convert()

But by Player_id I got only the Name, Position, Loan and ONLY ONE Stat.

I am stuck because for each player I got the same node name multiple time. I'd like to obtain a dataframe from this XML file with the Type of the stat node.

something like:

In bonus if I can have in addition the parent node information like the Team uID and short_club_name it would be great

Have you looked at this question/answer: https://stackoverflow.com/questions/48244963/r-xml-combining-parent-and-child-nodes-into-data-frame — Dave2e, Mar 06 '19 at 13:47
Yes I took a look at that question. But there is a difference between both. In my case I got multiple node with same name "Stat". With for exemple. I want to have first_name as column name and not Stat. — Thibault Senegas, Mar 06 '19 at 13:57

Dave2e · Accepted Answer · 2019-03-06T17:39:47.407

Here is a solution to try. See comments for an explanation of the process steps:

library(xml2)
library(dplyr)

x <- read_xml('player.xml')

Players3 <- x %>% xml_find_all('//Player') 

dfs<-lapply(Players3, function(node){
   #find names of all children nodes
   childnodes<-node %>% xml_children() %>% xml_name()
   #find the attr value from all child nodes
   names<-node %>% xml_children() %>% xml_attr("Type")
   #create columns names based on either node name or attr value
   names<-ifelse(is.na(names), childnodes, names)

   #find all values
   values<-node %>% xml_children() %>% xml_text()

   #create data frame and properly label the columns
   df<-data.frame(t(values), stringsAsFactors = FALSE)
   names(df)<-names
   df
})

#bind together and add uid to final dataframe.
answer<-bind_rows(dfs)
answer$UID<- Players3 %>% xml_attr("uID")
answer

#             Name   Position first_name last_name birth_date weight height jersey_num real_position
# 1   Denis Petric Goalkeeper      Denis    Petric 1988-05-24     83    187          1    Goalkeeper
# 2 Mathieu Michel Goalkeeper    Mathieu    Michel 1991-09-04     84    189          1    Goalkeeper
#   real_position_side  join_date  country birth_place first_nationality preferred_foot     UID
# 1            Unknown 2016-01-02 Slovenia        <NA>              <NA>           <NA>  p40511
# 2            Unknown 2016-08-18   France       Nimes            France          Right p119744

R XML - combining parent and child nodes(w same name) into data frame

1 Answers1

Linked