Analyzing the Barkley Fall Classic 50K

Every September, just in time for my birthday, a race takes place in the woods of the Frozen Head State Park.

The Barkley Fall Classic is a 50K ultra that takes place on and off the trails within the park…and a little bit outside the park too.

It gives mortals like me a small taste of what the real Barkley Marathons (a 100+ mile, 80 hour, world-class nightmare) are all about.

A lot of people sign up for the Fall Classic, and a lot of us end up waiting many months before we get an invite to actually register.

I’ve had a 50K finish in 2021 as well as a marathon finish in 2022.

I just got in for this coming weekend’s race after being on the waitlist since the end of last September…

In the meantime, let’s crunch some data from the results on UltraSignup since the Fall Classic started back in 2014.

Resources

Here’s a video walkthrough of what we’ll touch on below:

Here’s the Google Sheet where you can follow along and/or make a copy to mess with yourself: https://docs.google.com/spreadsheets/d/1NmyFqEldiYcqaHr14RQqKbMMginHDLirvEdXtLDqhUE/edit#gid=1257498594

Warm Up Lap: Let’s Get Started

We need the data. I thought I’d be clever and use IMPORTHTML or IMPORTXML to grab all the results. However, this didn’t work because of the structure of UltraSignup’s site.

At any rate, I’ve got them all now after copying and pasting into one big ol’ sheet.

Each race’s results are broken into two pages: the 50K results and the Marathon results. So I’ve included links to the page URLs I used to get all this.

In my sheet, I also made some additional columns for organization’s sake:

1. Full Name: concatenated the First and Last name columns.

2. Year: I added this since it wasn’t included

3. Race: I added this to specify between 50K, Marathon, DNF 50K, DNF Marathon and DNS…(yes, there are some fun, detailed rules around these differences and even a 30 Miler and DNF 30 Miler in the first year of the race!)

And that gives us a total of 3012 rows of race data from 2014 to 2022’s race.

The Aid Stations: What Questions to Answer?

This project was mainly about grabbing, cleaning and displaying some data about the race, but we do need to define what exactly we’d like to find out.

Here’s what I came up with:

1. List of folks with the most finishes

2. List of fastest & slowest finishes

3. States represented with the most finishes

4. Number of unique starters

5. Ratio of finishes to starts

6. List of all the winners

The Climbs: Let’s Use SQL in Sheets

To sift through our data, now that it’s in a neat spreadsheet, we could use a variety of tools. We could throw down some pivot tables, but instead, I’d like to use the QUERY() function to practice a little SQL.

Query lets us use several of the prominent SQL commands right in our spreadsheet. Mostly, I’ve used the following to wrangle our data and get the answers we’re seeking:

• SELECT

• WHERE

• ORDER BY

• GROUP BY

• COUNT

• LIMIT

As an example, here’s the QUERY statement to retrieve all the winners (Male and Female) from each year:

=QUERY(race_data,"SELECT Col1, Col2, Col4, Col7, Col11, Col12, Col13, Col14 WHERE Col12 = 1 AND Col2='"&N2&"'")

The QUERY takes the race_data named range from our 🔥data sheet which simply includes all 3012 rows of data.

Then, we write the actual query command enclosed in quotation marks. SELECT tells it which columns we want to display.

You can see from our 🔥data sheet that I numbered the columns for easier reference.

Then we have two conditions defined by the WHERE keyword. Col12 is the DP (Division Place) column. This returns both the top male and top female. AND Col2 is the Race column which we match to the range of N2… which is where I’ve put a dropdown selector to toggle between the 50K or the marathon:

For a couple of the questions, I also used COUNT, GROUP BY, and ORDER BY.

For example, this is how I retrieved the top 10 States and the number of finishes represented by that state. Column 9 was the state column, and COUNT(Col9) counts up all those finishes.

=QUERY(race_data,"SELECT Col9,COUNT(Col9) WHERE Col2='"&N2&"' GROUP BY Col9 Order By COUNT(Col9) desc LIMIT 10", TRUE)

Then I GROUP BY state and ORDER BY the count of the state finishes. Typing desc makes it in descending order (largest to smallest), and LIMIT 10 simply returns only the first 10 results.

Decision Point: Let’s Make it Pretty

For each analysis, I inserted a chart by selecting Insert → Chart from the menu. Google Sheets gives you a fair amount of options for customizing, and you’ll typically have to adjust things a bit when playing with the chart editor options.

You can double click on each chart to see the options I’ve set in this spreadsheet.