In 2024, I was foolish enough to participate in the Vätternrundan   , which is a 315km distance bike sportive around Sweden’s second biggest lake. It took me and my friends a little less than 11.5 hours of cycling (15h including breaks).

Since this event is a sportive   , not a race, the organizers only publish finishing times via a bib number search on their website   . However, I was curious to understand how well my group did overall. To get my hands on the data I wrote a small scraping script to collect all finish times from the results page.

I can use this data to find out:

• information about the participants, e.g. what countries had a lot of starters
• distribution of race times, e.g. how fast is the median rider
• what percentage of people finished the race

Scraping the dataset

Since getting the timing information from the results page involves several clicks for each start number, I use selenium   to automate the browser actions. The script performs the actions for each start number between 1 and 30,000 and collects the timing information. Some start numbers are not used and many people didn’t finish the race. All the data is stored in a parquet file that is used in the next section. I had to do some light data cleaning, e.g. converting scraped strings to timedelta, removing data for unused startnumbers, etc.

Loading the dataset

I load the data from the parquet file into a pandas dataframe. I perform a few additional data cleaning steps, so it is easier to work with the dataset.

1import pandas as pd
2import numpy as np
3import datetime as dt
4import matplotlib.pyplot as plt
5import seaborn as sns
6from matplotlib.ticker import FuncFormatter

1df = pd.read_parquet("results_vatternrundan24.parquet")
2df['result_time_minutes'] = df['result_time'].dt.total_seconds() / 60
3df['average_speed'] = 1/df['result_time_minutes'] * 315 * 60
4df['has_finished'] = ~df['result_time'].isna() & (df['timing_consistent'])

1print(f'The dataset contains {df.shape[0]} entries.')

The dataset contains 15813 entries.

Analysing the dataset

The dataset has the following columns:

• startnumber
• initials: initials of the rider
• club: club of the rider
• city: home city of the rider
• country: home country of the rider
• result_time: full duration between crossing start and finish lines (includes breaks at food stations)
• average_speed
• is_sub9: whether the rider was part of a sub9 group that started later in the day (after 11:30 on Saturday)
• has_finished: a boolean whether a rider completed the event or dropped out

1display(df[['startnumber', 'initials', 'club','city', 'country', 'result_time', 'average_speed', 'has_finished']].head(5))
2df.dtypes

startnumber                      int64
initials                        object
club                            object
city                            object
country                         object
result_time            timedelta64[ns]
start_time                      object
station_records                 object
timing_consistent                 bool
is_sub9                           bool
result_time_minutes            float64
average_speed                  float64
has_finished                      bool
dtype: object

The riders

Let’s first look at the top countries, cities, and clubs that are in this dataset. Not surprisingly, Sweden (SE) has the most participants, followed by Germany (DE), Norway (NO), and Finnland (FI).

1df.groupby('country').agg(num_starters = ('startnumber', 'count'), num_finishers = ('has_finished', 'sum'), perc_finished = ('has_finished', 'mean'), mean_result_time = ('result_time', 'mean'), fastest_finisher = ('result_time', 'min')).sort_values('num_starters', ascending=False).head(10)

The cities with most participants are all Swedish: Stockholm, Göteborg, Linköping, Uppsala, Malmö.

1df.groupby('city').agg(num_starters = ('startnumber', 'count'), num_finishers = ('has_finished', 'sum'), perc_finished = ('has_finished', 'mean'), mean_result_time = ('result_time', 'mean'), fastest_finisher = ('result_time', 'min')).sort_values('num_starters', ascending=False).head(10)

The following clubs had the most riders.

1df.groupby('club').agg(city = ('city', 'first'), num_starters = ('startnumber', 'count'), num_finishers = ('has_finished', 'sum'), perc_finished = ('has_finished', 'mean'), mean_result_time = ('result_time', 'mean'), fastest_finisher = ('result_time', 'min')).sort_values('num_starters', ascending=False)[1:10]

Number of finishers

315km is a long event. Let’s check how many of the participants finished the event:

1num_starters = df.shape[0]
2num_finishers = df.query('has_finished').shape[0]
3print(f"Among the {num_starters} starting riders in the dataset, we found recorded and consistent finishing times for {num_finishers}. \
4This is a finishing rate of {100 * num_finishers / num_starters:.2f}%.")

Among the 15813 starting riders in the dataset, we found recorded and consistent finishing times for 12920. This is a finishing rate of 81.70%.

The podium and top 10

1fastest_rider = df.query('has_finished').sort_values('result_time').head(1)
2print(f'The fastest rider {fastest_rider.initials.values[0]} from {fastest_rider.city.values[0]} only needed {dt.timedelta(microseconds= float(fastest_rider.result_time.values[0])/1000)} to complete the 315km. \
3That is a mindblowing average speed of {(fastest_rider.average_speed.values[0]):.1f} km/h.')

The fastest rider JL from Lund only needed 7:16:00 to complete the 315km. That is a mindblowing average speed of 43.3 km/h.

The 10 fastest riders were below 7h 32min.

1df.query('has_finished')[['startnumber', 'initials', 'city', 'club', 'country', 'result_time', 'average_speed']].sort_values('result_time', ascending=True).head(10)

The 10 fastest riders that didn’t ride as part of a sub-9 registered teams were:

1df.query('has_finished and not is_sub9')[['startnumber', 'initials', 'city', 'club', 'country', 'result_time', 'average_speed']].sort_values('result_time', ascending=True).head(10)

Lanterne rouge

The slowest finisher took almost 28h.

1df.query('has_finished')[['startnumber', 'initials', 'city', 'country', 'result_time', 'average_speed']].sort_values('result_time', ascending=True).tail(5)

Finishing times (excluding sub-9 groups)

To get a better sense of the finishing times, I plot the histogram and get some summary statistics of the result_time column for every finisher. I only consider the times of the regular riders below. This means excluding the sub-9 cycling teams that start separately later on Saturday. With a little less than 15h total (11.5h moving time) my group was slower than the median rider. Next time, we might want to take shorter breaks.

1times = df.query('has_finished and not is_sub9')
2times['result_time'].describe()

count                        12175
mean     0 days 13:55:05.829979466
std      0 days 02:57:47.200342638
min                0 days 08:02:00
25%                0 days 11:37:00
50%                0 days 13:44:00
75%                0 days 15:48:00
max                1 days 03:44:00
Name: result_time, dtype: object

1print(f"To be among the top 5% fastest finishers, you need to beat {times['result_time'].quantile(q=0.05)}.")
2print(f"To be among the top 10% fastest finishers, you need to beat {times['result_time'].quantile(q=0.1)}.")
3print(f"To be among the top 25% fastest finishers, you need to beat {times['result_time'].quantile(q=0.25)}.")

To be among the top 5% fastest finishers, you need to beat 0 days 09:44:00.
To be among the top 10% fastest finishers, you need to beat 0 days 10:10:00.
To be among the top 25% fastest finishers, you need to beat 0 days 11:37:00.

To plot the distribution of result times, I am binning the result time in minutes and also add some vertical lines indicating the 0.1- and 0.25- percentiles. It’s not surprising that the distribution is right-skewed with many riders that have very long finishing times, but nobody faster than 8 hours.

 1
 2def minutes_to_hours_minutes(x, pos):
 3    """Define a function to convert minutes to HH:MM. Used for the axis labelling."""
 4    hours = int(x // 60)
 5    minutes = int(x % 60)
 6    return f"{hours:02d}:{minutes:02d}"
 7
 8# create histogram plot of result time distribution
 9plt.figure(figsize=(10, 6)) 
10sns.histplot(data=times, x="result_time_minutes", binwidth=15)
11
12plt.gca().xaxis.set_major_formatter(FuncFormatter(minutes_to_hours_minutes))
13
14# draw colored vertical percentile lines
15plt.axvline(times.result_time_minutes.median(), color='red', linestyle='--', linewidth=1.5)
16plt.axvline(times.result_time_minutes.quantile(0.25), color='green', linestyle='--', linewidth=1.5)
17plt.axvline(times.result_time_minutes.quantile(0.1), color='orange', linestyle='--', linewidth=1.5)
18plt.text(times.result_time_minutes.median(), plt.ylim()[1] * 0.95, f'Median',
19         color='red', ha='left', va='bottom')
20plt.text(times.result_time_minutes.quantile(0.25), plt.ylim()[1] * 0.95, f'Top 25%',
21         color='green', ha='center', va='bottom')
22plt.text(times.result_time_minutes.quantile(0.1), plt.ylim()[1] * 0.95, f'Top 10%',
23         color='orange', ha='right', va='bottom');
24plt.xlabel('Finish time (incl. breaks)');

If you are planning to race next year’s Vatternrundan, this distribution might give you an indication of what to expect and a time to aim for.

Jupyter Notebook

You can find the jupyter notebook and the datasets for this post here   .