title: Unnesting Lenex XML Files
body: <p>I'm looking to extract data from swimming results using python by un-nesting Lenex files (a form of xml).</p>
  <p>The Lenex tree can be found <a href="https://wiki.swimrankings.net/images/6/62/Lenex_3.0_Technical_Documentation.pdf" rel="nofollow noreferrer">here</a>.<br />
  An example of a full file that I want to unnest can be found <a href="https://www.omegatiming.com/File/0001170000FFFFFFFFFFFFFFFFFFFFC0.lef" rel="nofollow noreferrer">here</a>.</p>
  <p>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.</p>
  <p>For example, the following code can get me a dataframe for Athletes with athleteid, lastname, firstname, gender, and birthdate as the columns:</p>
  <pre><code>    tree = ET.parse(filepath)
  root = tree.getroot()
  
  athletes = pd.DataFrame()
  for ele in root.findall(&quot;.//ATHLETE&quot;):
      df_dictionary = pd.DataFrame([ele.attrib])
      athletes = pd.concat([athletes, df_dictionary], ignore_index=True)
  </code></pre>
  <p>The result looks like this (I've swapped names for initials):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th>athleteid</th>
  <th>lastname</th>
  <th>firstname</th>
  <th>gender</th>
  <th>birthdate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0</td>
  <td>145</td>
  <td>E</td>
  <td>J</td>
  <td>M</td>
  <td>2004-08-25</td>
  </tr>
  <tr>
  <td>1</td>
  <td>147</td>
  <td>M</td>
  <td>C</td>
  <td>M</td>
  <td>2003-12-16</td>
  </tr>
  <tr>
  <td>2</td>
  <td>146</td>
  <td>H</td>
  <td>C</td>
  <td>F</td>
  <td>2005-10-26</td>
  </tr>
  <tr>
  <td>3</td>
  <td>112</td>
  <td>P</td>
  <td>J</td>
  <td>M</td>
  <td>2004-11-22</td>
  </tr>
  <tr>
  <td>4</td>
  <td>111</td>
  <td>F</td>
  <td>A</td>
  <td>F</td>
  <td>2004-08-22</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>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 &lt; Result &lt; Athlete &lt; Club &lt; Meet).  I want to keep these associations when I turn this into dataframes.</p>
  <p>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.</p>
  <p>In terms of database design - Is this actually the most sensible way to do this?</p>
  <p>More importantly, how would I add a column to my Athletes dataframe that includes their associated club name?</p>
  <p>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:</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; standalone=&quot;no&quot;?&gt;
  &lt;LENEX version=&quot;3.0&quot;&gt;
    &lt;CONSTRUCTOR name=&quot;Swiss Timing Swimming Data Handling&quot; version=&quot;60.0.0.0&quot;&gt;
      &lt;CONTACT name=&quot;Swiss Timing Ltd.&quot; zip=&quot;CH-2606&quot; city=&quot;Corgemont&quot; email=&quot;info@swisstiming.com&quot; internet=&quot;http://www.swisstiming.com&quot; /&gt;
    &lt;/CONSTRUCTOR&gt;
    &lt;MEETS&gt;
      &lt;MEET name=&quot;Phillips 66 International Team Trials&quot; city=&quot;Greensboro, NC&quot; nation=&quot;USA&quot; course=&quot;LCM&quot; timing=&quot;AUTOMATIC&quot;&gt;
        &lt;SESSIONS&gt;
          &lt;SESSION number=&quot;1&quot; date=&quot;2022-04-26&quot; daytime=&quot;09:00&quot;&gt;
            &lt;EVENTS&gt;
              &lt;EVENT eventid=&quot;112&quot; number=&quot;12&quot; preveventid=&quot;12&quot; gender=&quot;M&quot; round=&quot;FIN&quot; daytime=&quot;18:57&quot;&gt;
                &lt;SWIMSTYLE distance=&quot;200&quot; relaycount=&quot;1&quot; stroke=&quot;BACK&quot; /&gt;
                &lt;HEATS&gt;
                  &lt;HEAT daytime=&quot;18:57&quot; final=&quot;B&quot; heatid=&quot;20112&quot; number=&quot;2&quot; /&gt;
                  &lt;HEAT daytime=&quot;19:02&quot; final=&quot;A&quot; heatid=&quot;10112&quot; number=&quot;1&quot; /&gt;
                  &lt;HEAT daytime=&quot;19:40&quot; final=&quot;C&quot; heatid=&quot;30112&quot; number=&quot;3&quot; /&gt;
                &lt;/HEATS&gt;
      &lt;/EVENT&gt;
      &lt;/EVENTS&gt;
      &lt;/SESSION&gt;
      &lt;/SESSIONS&gt;
      &lt;CLUBS&gt;
      &lt;CLUB name=“An Aquatic Club” shortname=&quot;AAC&quot; code=&quot;AAC&quot; nation=&quot;USA&quot; type=&quot;&quot;&gt;
          &lt;ATHLETES&gt;
              &lt;ATHLETE athleteid=&quot;145&quot; lastname=“SMITH” firstname=&quot;John” gender=&quot;M&quot; birthdate=&quot;2000-07-25&quot;&gt;
                &lt;ENTRIES&gt;
                  &lt;ENTRY entrytime=&quot;00:01:55.31&quot; eventid=&quot;12&quot; heat=&quot;1&quot; lane=&quot;4&quot; /&gt;
                &lt;/ENTRIES&gt;
                &lt;RESULTS&gt;
                  &lt;RESULT eventid=&quot;112&quot; place=&quot;12&quot; lane=&quot;3&quot; heat=&quot;2&quot; heatid=&quot;20112&quot; swimtime=&quot;00:02:00.20&quot; reactiontime=&quot;+51&quot;&gt;
                    &lt;SPLITS&gt;
                      &lt;SPLIT distance=&quot;50&quot; swimtime=&quot;00:00:28.31&quot; /&gt;
                      &lt;SPLIT distance=&quot;100&quot; swimtime=&quot;00:00:58.62&quot; /&gt;
                      &lt;SPLIT distance=&quot;150&quot; swimtime=&quot;00:01:29.61&quot; /&gt;
                      &lt;SPLIT distance=&quot;200&quot; swimtime=&quot;00:02:00.20&quot; /&gt;
                    &lt;/SPLITS&gt;
                  &lt;/RESULT&gt;
      &lt;/RESULTS&gt;  
      &lt;/ATHLETE&gt;
      &lt;/ATHLETES&gt;
      &lt;/CLUB&gt;
      &lt;/CLUBS&gt;
      &lt;/MEET&gt;
  &lt;/MEETS&gt;
  &lt;/LENEX&gt;
  </code></pre>
  <p>The end goal should hopefully look something like this by modifying the code I'm currently using to generate the dataframe listed above:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th>athleteid</th>
  <th>lastname</th>
  <th>firstname</th>
  <th>gender</th>
  <th>birthdate</th>
  <th>club name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0</td>
  <td>145</td>
  <td>Smith</td>
  <td>John</td>
  <td>M</td>
  <td>2004-07-25</td>
  <td>An Aquatic Club</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Thank you!</p>