Unnesting Lenex XML Files

Question

I'm looking to extract data from swimming results using python by un-nesting Lenex files (a form of xml).

The Lenex tree can be found here.
An example of a full file that I want to unnest can be found here.

More specifically, I would like to extract pieces of these files and convert them into dataframes - a table for each element (Athlete, Club, Result, etc.) where the columns are the attributes listed in the technical document linked above.

For example, the following code can get me a dataframe for Athletes with athleteid, lastname, firstname, gender, and birthdate as the columns:

    tree = ET.parse(filepath)
root = tree.getroot()

athletes = pd.DataFrame()
for ele in root.findall(".//ATHLETE"):
    df_dictionary = pd.DataFrame([ele.attrib])
    athletes = pd.concat([athletes, df_dictionary], ignore_index=True)

The result looks like this (I've swapped names for initials):

	athleteid	lastname	firstname	gender	birthdate
0	145	E	J	M	2004-08-25
1	147	M	C	M	2003-12-16
2	146	H	C	F	2005-10-26
3	112	P	J	M	2004-11-22
4	111	F	A	F	2004-08-22

This is a good start, but each athlete is nested within a club. Also, within each athlete, is a collection of entries and results. Within each result, is a collection of splits. i.e. (Split < Result < Athlete < Club < Meet). I want to keep these associations when I turn this into dataframes.

My assumption is that the best way to do this is for the 'Split' dataframe to have an associated 'Resultid', for the 'Result' dataframe to have an associated 'Athleteid, and for the 'Athlete' dataframe to have an associated 'Clubid,' etc. Within any given meet, these relationships are true 'subsets,' i.e., it's a one-to-many relationship between results to splits, athletes to results, clubs to athletes, etc.

In terms of database design - Is this actually the most sensible way to do this?

More importantly, how would I add a column to my Athletes dataframe that includes their associated club name?

As far as a 'representative' example, I'm not sure if this is accurate or helpful, but I did my best to put something together:

The end goal should hopefully look something like this by modifying the code I'm currently using to generate the dataframe listed above:

	athleteid	lastname	firstname	gender	birthdate	club name
0	145	Smith	John	M	2004-07-25	An Aquatic Club

Thank you!

user_9448090 · Answer

If I understand you correctly, you are looking for something like the below. In this case, I'm using lxml instead of ElementTree, because of the former's better xpath support:

from lxml import etree
tree = etree.parse(filepath)
root = tree.getroot()
rows = []
cols = ["athleteid","lastname","firstname","gender","birthdate","club name"]
for athlete in root.xpath('//ATHLETE'):
    club = athlete.xpath('./ancestor::CLUB/@name')
    row = athlete.xpath('./@*')
    row.append(club[0])
    rows.append(row)
df = pd.DataFrame(rows,columns=cols)
df

The output is

    athleteid   lastname    firstname   gender  birthdate   club name
0   164222     ANWARI       Fahim       M     1999-05-05      Afghanistan
1   160991     KADIU        Kledi       M     2003-10-28      Albania
2   110360     MERIZAJ      Nikol       F     1998-08-07      Albania

etc., etc.

Unnesting Lenex XML Files

1 Answers