I can theorize how to best build this database of scores for ages, but of course, it means nothing until some actual data is imported. Based on the database design, the recap table now has to be deconstructed into one row per unit. This would require every detail and score/rank to be listed in a corresponding column, with some of the data repeating for every unit row.
When creating a process for this, it is important to recall that the recap tables are different for a single vs. double panel event. Mainly, double panel events simply have more data to extract. Below is an example of the two different lists of columns.
(This insert below is an absurd amount of columns shoved into a tiny box. But, that is kind of the point, and helps illustrate how many details and values we are working with here.)
Single Panel
| SHOW | LOCATION | DATE | ROUND | CLASS | Effect - Music 1 (judge) | Effect - Visual 1 (judge) | Music 1 (judge) | Visual 1 (judge) | Timing & Penalties (judge) | Head Judge | UNIT | UNIT_LOCATION | EM1_OVR_FX_SCORE | EM1_OVR_FX_RANK | EM1_MUS_FX_SCORE | EM1_MUS_FX_RANK | EM_TOTAL_SCORE | EM_TOTAL_RANK | EV1_OVR_FX_SCORE | EV1_OVR_FX_RANK | EV1_VIS_FX_SCORE | EV1_VIS_FX_RANK | EV_TOTAL_SCORE | EV_TOTAL_RANK | MUS1_COMP_SCORE | MUS1_COMP_RANK | MUS1_PERF_SCORE | MUS1_PERF_RANK | MUS_TOTAL_SCORE | MUS_TOTAL_RANK | VIS1_COMP_SCORE | VIS1_COMP_RANK | VIS1_PERF_SCORE | VIS1_PERF_RANK | VIS_TOTAL_SCORE | VIS_TOTAL_RANK | SUBTOTAL | SUBTOTAL_RANK | TIMING_PENALTIES | TOTAL | TOTAL_RANK | DOUBLE_PANEL | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
Double Panel
| SHOW | LOCATION | DATE | ROUND | CLASS | Effect - Music 1 (judge) | Effect - Music 2 (judge) | Effect - Visual 1 (judge) | Effect - Visual 2 (judge) | Music 1 (judge) | Music 2 (judge) | Visual 1 (judge) | Visual 2 (judge) | Timing & Penalties (judge) | Head Judge | UNIT | UNIT_LOCATION | EM1_OVR_FX_SCORE | EM1_OVR_FX_RANK | EM1_MUS_FX_SCORE | EM1_MUS_FX_RANK | EM1_JUDGE_SCORE | EM1_JUDGE_SCORE_RANK | EM2_OVR_FX_SCORE | EM2_OVR_FX_RANK | EM2_MUS_FX_SCORE | EM2_MUS_FX_RANK | EM2_JUDGE_SCORE | EM2_JUDGE_SCORE_RANK | EM_TOTAL_SCORE | EM_TOTAL_RANK | EV1_OVR_FX_SCORE | EV1_OVR_FX_RANK | EV1_VIS_FX_SCORE | EV1_VIS_FX_RANK | EV1_JUDGE_SCORE | EV1_JUDGE_SCORE_RANK | EV2_OVR_FX_SCORE | EV2_OVR_FX_RANK | EV2_VIS_FX_SCORE | EV2_VIS_FX_RANK | EV2_JUDGE_SCORE | EV2_JUDGE_SCORE_RANK | EV_TOTAL_SCORE | EV_TOTAL_RANK | MUS1_COMP_SCORE | MUS1_COMP_RANK | MUS1_PERF_SCORE | MUS1_PERF_RANK | MUS1_JUDGE_SCORE | MUS1_JUDGE_SCORE_RANK | MUS2_COMP_SCORE | MUS2_COMP_RANK | MUS2_PERF_SCORE | MUS2_PERF_RANK | MUS2_JUDGE_SCORE | MUS2_JUDGE_SCORE_RANK | MUS_TOTAL_SCORE | MUS_TOTAL_RANK | VIS1_COMP_SCORE | VIS1_COMP_RANK | VIS1_PERF_SCORE | VIS1_PERF_RANK | VIS1_JUDGE_SCORE | VIS1_JUDGE_SCORE_RANK | VIS2_COMP_SCORE | VIS2_COMP_RANK | VIS2_PERF_SCORE | VIS2_PERF_RANK | VIS2_JUDGE_SCORE | VIS2_JUDGE_SCORE_RANK | VIS_TOTAL_SCORE | VIS_TOTAL_RANK | SUBTOTAL | SUBTOTAL_RANK | TIMING_PENALTIES | TOTAL | TOTAL_RANK | DOUBLE_PANEL | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
With the first few events that I imported, I will admit that I simply copy and pasted the recap into Excel as plain text and manually modified all of the formatting (with lots of shortcuts!). This allowed me to prove that the concept of the database actually works. Having proven the concept, I knew a quicker and more automated method to parse through all of this data was necessary. Python to the rescue!
Using the Python library BeautifulSoup, I could parse through all of the recap tables, create lists of all of the data, and then use pandas to create DataFrames that can turn into spreadsheets (which are then imported into the database).
Step 1 of this process would be looking through the HTML of the recap tables. I discussed the inherent complexity of these tables previously, and this certainly showed up again when looking through the HTML. Take for example the first recap of the season: https://recaps.competitionsuite.com/8f97bccc-7fcd-4545-a66d-86df10b7d4e5.htm
Below is the hierarchy of tags to get to the first sub-caption rating for Piedmont HS (XPath).
/html/body/div[5]/table/tbody/tr[2]/td/table/tbody/tr[4]/td[3]/table/tbody/tr[1]/td
This single sub-caption is quite deeply nested, which itself is not a huge issue. But, it reveals that the score is in a table, within a table, within a table… Each with their own row and data tags along the way!
For a more detailed example, below are the scores for the Effect Music caption for Piedmont HS, the two sub-captions and total caption score, and respective ranks for each:
<!--
Broken Arrow
Percussion Scholastic A - Piedmont HS
https://recaps.competitionsuite.com/8f97bccc-7fcd-4545-a66d-86df10b7d4e5.htm
-->
<tr>
<td class="content topBorder rightBorderDouble">Piedmont HS</td>
<td class="content topBorder rightBorderDouble" style="padding-left: 5px;padding-right: 5px;">
Piedmont, OK
</td>
<td class="topBorder rightBorder verified" style="width: 30px;">
<table class="scoreTable" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="content score" data-translate-number="69">69</td>
</tr>
<tr>
<td class="content rank">1</td>
</tr>
</tbody>
</table>
</td>
<td class="topBorder rightBorder verified" style="width: 30px;">
<table class="scoreTable" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="content score" data-translate-number="74">74</td>
</tr>
<tr>
<td class="content rank">1</td>
</tr>
</tbody>
</table>
</td>
<td class="topBorder rightBorder subcaptionTotal verified" style="width: 30px;">
<table class="scoreTable" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="content score" data-translate-number="21.45">21.45</td>
</tr>
<tr>
<td class="content rank">1</td>
</tr>
</tbody>
</table>
</td>
</tr>
The <tr> tag that begins this snippet represents the entire row for this unit. One might think… “Great! Now go through and get all of the <td> values and you’re done!” Not quite…
Within the <td> tags is another table, with another set of <tr> and <td> tags, where we finally find the numerical values. To move on to the next sub-caption rating, you have to move back up the hierarchy and over to the next data tag and do the same process again. This style of hierarchy is not limited to just this area, as this trend occurs in several other areas of these recaps as well.
Now, none of this is a ground breaking discovery. However, it would require a bit of strategic code when parsing through certain areas of this HTML. Most importantly, when searching for certain repetitive elements, it should not be a recursive search. This avoids duplicating or adding results with tags of the same name that are nested deeper.
Similarly, there are several steps where blocks of HTML stored in lists need to be parsed multiple layers deep in one step. The above was a perfect example. The main <tr> tag for each unit is stored in a list, each item being a unit’s full row of scores. To extract the unit name and location would be iterating through this main <tr> tag. However, within each main <tr> tag, an additional search and iteration for the next layer of <tr> tags is necessary, so it can parse through all of the numerical values. So within the list being iterated, a new list is created and iterated. Kind of like Inception, but for marching band scores.
Furthermore, a few other small quirks. I lied to you previously, as there are actually more than just six classes that compete at these events. However, for this project at the moment, only the marching percussion classes are being imported. As such, there is a filter to check the main <div> of each class and omit any that are not percussion marching classes.
Additionally, determining whether a show is a single or double panel effects a lot later with how much information to parse through and extract. In the script, once the judges’ names are extracted, there is a check to see how many judges are in the recap. This then determines the correct columns for the DataFrame, allowing the list of information appended later to align properly.
With all of that in mind and no further ado, I present to you… the Python script!