# Half Marathon Data Exploration and Visualization: using Python Plotly Library

Last Updated on June 28, 2023 by Editorial Team

**Author(s): David Cullen**

Originally published on Towards AI.

## London Landmarks Half Marathon 2023

In April of this year, I ran the London Landmarks Half Marathon for the first time. The race included 17,225 registered participants between the ages of 17β89 and managed to raise over Β£10 million for 450 charities (well-done everyone!).

Following the race, I was emailed helpful summary statistics, such as position in the race, average speed, and split times, including a link to the race data in.xlsx format.

In addition to the summary statistics already available, I thought it would be interesting to explore the data in a bit more detail, which led me to write this article, covering:

**Data Extraction:**download data and store it in Pandas DataFrame.**Data Preparation**: data cleaning using NumPy and Pandas.**Data Overview:**data overview, visualized using Plotly.**Data Distribution:**kernel density estimation and empirical cumulative distribution function visualized using Plotly.**Average Speed Analysis:**analysis of participant speed across a variety of chiptime splits, visualized using Plotly.

Each analysis in this article includes the relevant Python code to enable the reader to follow along programmatically.

*Important Note: The majority of the visuals in this article are interactive β just follow the link in each respective title. For this, I have used Plotly Chart Studio: **https://chart-studio.plotly.com**. A tutorial on implementing interactive plots is outside the scope of this article. The reader will be able to replicate the visuals in this article directly from their interactive computing environment (i.e., Jupyter Notebook).*

## Dataset

The** **dataset [1] consists of 26 attributes, of which the following has been used in this article:

**Chiptime:**Finishing chiptime.**Category:**Age group by gender.**Gender:**Male or Female.**Avg speed:**Runner km/hr average race speed.**Split β 5K β Cumulative time:**Runner 5k time.**Split β 10K β Cumulative time:**Runner 10k time.**Split β 15K β Cumulative time:**Runner 15k time.**Split β 20K β Cumulative time:**Runner 20k time.

I chose the above attributes, as I personally have found these data most interesting from a performance perspective.

Below details the process for data extraction.

## Import Libraries:

We will first import the required libraries:

`import requests # html requests`

import numpy as np # data analysis

import pandas as pd # dataframe creation and analysis

from scipy import stats # central tendency anaysis

import dash # plots

import jupyter_dash # open plots in notebook

import plotly.express as px # plots

import plotly.figure_factory as ff # plots

import locale # comma separators in plot

## Data Extraction:

We will now inspect the link html and find the relevant href. From here we extract the url to get the required data.

`# url `

url = "https://mel-active-eventresults-webdynamiccontent.azurewebsites.net/data/downloadexcel?eventId=7037394564091167232&raceId=485022" # href url to dataset

# open the .xlsx file

with open('LLHM2023.xlsx', 'wb') as out_file:

response = requests.get(url, stream=True) # get data

content = response.content # get content

out_file.write(content) # write content

# dataframe

df = pd.read_excel('LLHM2023.xlsx').fillna(0) # create dataframe and fill na with 0

df1 = (df[['Chiptime','Split - 5K - Cumulative time','Split - 10K - Cumulative time','Split - 15K - Cumulative time','Split - 20K - Cumulative time','Gender','Avg speed','Category']])# create dataframe for analysis, selecting specific attributes.

df1.head() # inspect first 5 rows of the data

By running the above code, the data should now have been successfully stored in a DataFrame (df1) for cleansing.

## Data Preparation:

We will now clean df1 to make it a bit more workable:

**Datetime columns:**

- Convert time objects to second integers for later analysis.
- Remove data that does not indicate Chiptime, to include recorded completion times only.

**Category Column:**

- Convert Category elements to a sortable format for later analysis (i.e. remove gender prefix).

**Gender Column:**

- Convert Gender elements to a readable format (from m and f to Male and Female).
- Remove entries that do not specify the participantβs gender.

**Average Speed Column:**

- Round the average speed for each participant to 2 decimal places.

**General Column Renaming:**

- Rename columns to a clearer format.

The following function has been used to complete the above:

`def clean_dataset(data):`

# format time related columns

data['Chiptime Seconds'] = pd.TimedeltaIndex(data['Chiptime'].astype("str")).total_seconds().astype(int) # create seconds column

data = data[data['Chiptime Seconds'] > 0] # remove 0 value rows to only show registered completed particpants

data.loc[:, 'Split - 20K - Cumulative time'] = pd.TimedeltaIndex(data['Split - 20K - Cumulative time'].astype("str")).total_seconds().astype(int) # convert to seconds

data.loc[:, 'Split - 10K - Cumulative time'] = pd.TimedeltaIndex(data['Split - 10K - Cumulative time'].astype("str")).total_seconds().astype(int)# convert to seconds

data.loc[:, 'Split - 15K - Cumulative time'] = pd.TimedeltaIndex(data['Split - 15K - Cumulative time'].astype("str")).total_seconds().astype(int) # convert to seconds

data.loc[:, 'Split - 5K - Cumulative time'] = pd.TimedeltaIndex(data['Split - 5K - Cumulative time'].astype("str")).total_seconds().astype(int) # convert to seconds

# format 'Category' column

data['Category'] = data['Category'].str.slice(1)# slice first character (M/F), to enable category sorting of age category.

data.sort_values(by='Category', inplace=True) # category sorting

data['Category'].replace({'BC': 'Unknown'}, inplace=True) # preprocess

data = data[data['Category'] != 'Unknown'] # drop Unknown

# format "Gender" column

data["Gender"].replace(to_replace = "m",value="Male", inplace = True) # replace elemments in Gender column

data["Gender"].replace(to_replace = "f",value="Female", inplace = True) # replace elemments in Gender column

data["Gender"].replace(to_replace = 0,value="Not Specifed", inplace = True) # replace elemments in Gender column

data = data[data['Gender'] != 'Not Specifed'] # drop not specifed

# format 'Avg speed' column

data['Avg speed'] = data['Avg speed'].round(2) # round average speed

# general column renaming

data = data.rename(columns = {'Avg speed':'Avg speed km/hr','Category':'Age Category'}) # rename specified columns

return data

`# clean and show the data`

cleaned_df = clean_dataset(df1)

cleaned_df

## Data Overview:

Now we can inspect the cleansed data.

## Participants by Gender and Age Category:

Following data cleaning, 17,081 participant entries are usable. From this total, the majority of the participants were female, making up 58.6%. The remaining 41.4% of participants were male.

`# calculate count and percentage of each 'Gender'`

gender = cleaned_df.groupby(['Gender']).size().reset_index()

gender = gender.rename(columns={0: 'Count'})

gender['Percentage'] = gender.groupby(['Gender']).apply(lambda x: 100 * x / gender['Count'].sum()).round(1)

# add total row

total_row = gender[['Count']].sum().rename({0: 'Total'})

total_row['Percentage'] = (total_row['Count'] / total_row['Count'].sum() * 100).round(1)

gender = pd.concat([gender, total_row.to_frame().T])

# format percentage column elements

gender['Percentage'] = gender['Percentage'].astype(str) + '%'

# format count column elements

gender['Count'] = gender['Count'].map('{:,.0f}'.format)

gender = gender.reset_index()

gender = gender.drop(columns=['index'])

gender.loc[gender.index[-1], 'Gender'] = 'Total'

# show

gender

## Count and Percentage of Participants by Age Category β Bar Chart:

Figure 1 shows the count and percentage of participants by age category across males and females.

25β29 is the most common age category, with 2,948 participants. This is then followed by age categories 30β34, totaling 2,434 participants, and 40β44, totaling 2,410 participants.

Less common age categories are 17β19, which sees 189 participants, and 65β69, which sees 153 participants. A substantial reduction in participants can also be seen from age 70 on.

`# create percentage by 'Age Category' dataframe`

perc_age_group = cleaned_df.groupby(["Age Category"]).size().reset_index() # group by 'Age Category'

perc_age_group['Percentage'] = cleaned_df.groupby(["Age Category"]).size().groupby(level=0).apply(

lambda x: x / perc_age_group[0].sum()).values.round(4) *100 # add percentage column

# create a new column with concatenated values of 'Count' and 'Percentage' rounded to 2decimal place

perc_age_group.rename(columns = {0: 'Count'}, inplace = True)

perc_age_group['Count and Perc'] = perc_age_group['Count'].astype(str) + ' U+007C ' + perc_age_group['Percentage'].round(2).astype(str)+'%'

# set the locale for comma separators

locale.setlocale(locale.LC_ALL, '')

# create a new column with separate lines for 'Count' and 'Percentage'

perc_age_group['Text'] = (

perc_age_group['Count'].apply(lambda x: locale.format_string("%d", x, grouping=True)) + '<br>' +

perc_age_group['Percentage'].round(2).astype(str) + '%'

)

# plot

fig = px.bar(

perc_age_group,

x="Age Category",

y="Percentage",

text="Text",

template="simple_white"

)

# update traces

fig.update_traces(

textposition="outside",

textfont=dict(size=12)

)

# set plot colors and bar width

fig.update_traces(marker_color='grey')

fig.update_traces(width=0.90)

# set y-axis tick suffix as percentage

fig.update_layout(yaxis_ticksuffix='%')

# set y-axis range

fig.update_layout(yaxis_range=[0, 20])

# show

fig.show()

## Percentage of Male and Female Participants by Age Category β Stacked Bar Chart:

Figure 2 shows that female participants represent the majority in most age categories.

`# percentage of 'Male' and 'Female' participants by 'Age Category':`

perc_by_gender_and_age=cleaned_df.groupby(["Age Category","Gender"]).size().reset_index() # group

perc_by_gender_and_age['Percentage'] = cleaned_df.groupby(["Age Category","Gender"]).size().groupby(level=0).apply(lambda

x:100 * x/float(x.sum())).values.round(0) # add percentage of 'Gender'

# plot

fig=px.bar(perc_by_gender_and_age, x='Age Category', y='Percentage',color='Gender',text='Percentage',template="simple_white",text_auto=True)

# update layout

fig.update_layout(yaxis_ticksuffix = "%")

# update traces

fig.update_traces (textfont=dict(size=10,color='white'))

# show

fig.show()

## Data Distribution:

## Chiptime Kernel Density Estimation:

We will now perform Kernel Density Estimation (KDE) on the chiptime for all participants to estimate the probability density function. This will give us a good indication of the distribution of the data.

As shown, the distribution is right-skewed. The mode (most common value) and median (central value) are less than the mean (average). This is a characteristic of a right-skewed distribution due to the large spread in the right tail, representing slower half marathon chiptimes.

Inspection of the mean indicates an average completion time of 2 hours, 15 minutes. An increasing number of participants took longer to complete the race, as indicated by the elongated right tail, which pulls the average (mean) completion time upwards.

Following the examination of the median and mode, a different picture emerges. For example, 50% of all runners ran the race in 2 hours, 10 minutes, or less (median), and the most frequent participation completion time was 1 hour, 57 minutes (mode).

`# create dataframe for plot`

hist_data = [cleaned_df['Chiptime Seconds']]

group_labels = ['Density'] # group

colors = ['#333F44'] # colour

# create distplot

fig = ff.create_distplot(hist_data, group_labels,colors=colors,curve_type='kde',show_hist=False,show_curve = True) # kde plot

# calculate the mean, mode and median based on 'Chiptime' seconds

mean_chiptime = np.mean(cleaned_df['Chiptime Seconds'])

mode_chiptime = stats.mode(cleaned_df['Chiptime Seconds'])

median_chiptime = np.median(cleaned_df['Chiptime Seconds'])

# mean, mode and median as datetime object

mean_chiptime_text = pd.to_datetime(mean_chiptime, unit='s').strftime('%H:%M:%S')

mode_chiptime_text = pd.to_datetime(mode_chiptime[0].item(), unit='s').strftime('%H:%M:%S')

median_chiptime_text = pd.to_datetime(median_chiptime, unit='s').strftime('%H:%M:%S')

# add a vertical line for the mean, mode and median on plot

fig.add_vline(x=mean_chiptime, line_color='red', line_dash='dot',annotation_text= (f'Mean:{mean_chiptime_text}'),annotation_font=dict(color='red'))

fig.add_vline(x=mode_chiptime[0].item(), line_color='blue', line_dash='dot', annotation_text= (f'Mode:{mode_chiptime_text}'),annotation_position="top left",annotation_font=dict(color='blue'))

fig.add_vline(x=median_chiptime, line_color='yellow', line_dash='dot', annotation_text= (f'Median:{median_chiptime_text}'),annotation_position="bottom right",annotation_font=dict(color='yellow'))

# add datetime to x axis

fig.update_xaxes(

title='Chiptime',

showline=True,

tickvals=list(range(0, int(cleaned_df['Chiptime Seconds'].max()) + 5, 800)),

ticktext=pd.to_datetime(list(range(0, int(cleaned_df['Chiptime Seconds'].max()) + 5, 800)), unit='s').strftime('%H:%M:%S'),

tickangle=45

)

# update density trace attributes

density_trace = fig['data'][0]

density_trace['fill'] = 'tozeroy'

density_trace['line']['width'] = 2

density_trace['line']['color'] = 'black'

# update x-axis ticks with datetime labels

fig.update_xaxes(

title='Chiptime',

showline=True,

tickvals=list(range(0, int(cleaned_df['Chiptime Seconds'].max()) + 5, 800)),

ticktext=pd.to_datetime(list(range(0, int(cleaned_df['Chiptime Seconds'].max()) + 5, 800)), unit='s').strftime('%H:%M:%S'),

tickangle=45

)

# update layout for gridlines

fig.update_layout(

yaxis=dict(showgrid=True, gridcolor='lightgrey'),

xaxis=dict(showgrid=True, gridcolor='lightgrey')

)

# update background colors

fig.update_layout(

plot_bgcolor='white',

paper_bgcolor='white'

)

# show

fig.show()

## Empirical Cumulative Distribution Function (ECDF) β Chiptime and Gender:

Next, we will plot the ECDF by gender. As shown in Figure 4, there is a higher probability for males to complete the half marathon faster than females. For example, approximately 80% of all males and approximately 50% of all females completed the race in under 2 hours and 20 minutes.

`# create dataframe `

ecdf = cleaned_df.loc[:,['Gender','Chiptime Seconds','Chiptime']]

ecdf.rename(columns = {'Chiptime Seconds': 'LLHM23 Chiptime'}, inplace = True)

# plot ecdf

fig = px.ecdf(ecdf, x="LLHM23 Chiptime", color="Gender", markers=True, lines=False, marginal="histogram",template="simple_white")

# update x-axis ticks with datetime ticks

fig.update_xaxes(

showline=True,

tickvals=list(range(0, int(cleaned_df['Chiptime Seconds'].max()) + 10, 400)),

ticktext=pd.to_datetime(list(range(0, int(cleaned_df['Chiptime Seconds'].max()) + 10, 400)), unit='s').strftime('%H:%M:%S'),

tickangle=45

)

# show gridline and space ticks

fig.update_layout(

xaxis = dict(showgrid = True),

yaxis = dict(showgrid = True),

)

# update axes title

fig.update_yaxes(title_text='Probability',row=1, col=1)

fig.update_xaxes(title_text='Chiptime',row=1, col=1)

# show

fig.show()

## Participant Average Race Speed and Age Category β Box Plot:

Letβs now explore the central tendency, spread, and outliers based on average participant race speed (y-axis) and age category (x-axis).

As shown in Figure 5, the 1st and 3rd interquartile ranges (50% of the participants for each age category) *generally *look to be slowly declining in participant average race speed with age category.

The upper box (75th Percentile) in the age category 30β34 is the fastest, when compared to other age category upper boxes. The presence of the outlier in this category, represented by the race winner, further emphasizes the age category's high performance. The fastest median belongs to the age category 25β29, with a median average race speed of 10.01 km/hr. Both of these findings are particularly interesting, considering the view that runners tend to peak between the ages of 26 and 35 [2].

Up to age categories 70β74, a number of participants can be seen to fall outside of the upper (Quartile 3+1.5 * Interquartile Range) whiskers. These are outliers, so donβt feel bad if you canβt/couldnβt average these speeds for your age category over the entire 21km!

*Important Note: for the later age categories (80β84 and 85β89), there are no whiskers or outliers, which is expected due to the limited sample size (as detailed earlier in this post).*

`# create box plot`

fig = px.box(cleaned_df, x="Age Category", y="Avg speed km/hr",template="simple_white",color_discrete_sequence=['#333F44'])

# update axis

fig.update_layout(yaxis_ticksuffix = 'km/hr')

# show boxplot

fig.show()

## Average Speed Analysis:

## Average Speed by Gender β Bar Chart:

We will now show the average race speed by gender.

Across both genders, the average race speed *slowly* declines per age category. This slow decline is a great metric to see. From a performance perspective, this is a testament to what the human body is capable of!

# group data by 'Age' and 'Gender' - calculate the mean

speed_data = cleaned_df.groupby(by=["Age Category", "Gender"]).mean().round(2).reset_index()

# create plot

fig = px.bar(

speed_data,

x='Age Category',

y='Avg speed km/hr',

color='Gender',

facet_row='Gender',

text_auto=True,

barmode='group',

template="simple_white"

)

# update layout

fig.update_layout(

bargap=1,

uniformtext_minsize=8,

height = 800

)

#update ticks

fig.update_layout(yaxis_ticksuffix = 'km/hr')

fig.update_layout(yaxis2_ticksuffix = 'km/hr')

# update trace

fig.update_traces(width=0.8)

fig.update_traces(textfont=dict(size=10, color='white'))

# show

fig.show()

## Change in Average Split Speed:

We now show the average percentage change between the each 5k interval (4 splits):

**Split 1:**0β5k Average Speed (km/hr).**Split 2 :**5k β 10k Average Speed (km/hr).**Split 3**: 10k β 15k Average Speed (km/hr).**Split 4:**15k β 20k Average Speed (km/hr).

First, we create a DataFrame with the average speeds (km/hr) for each of the above split times.

We then perform a quantile cut, to bin data based on chiptime** **seconds** **across 4 discrete intervals. The 1st interval (Bin 1) approximately represents the fastest 25% runners and the 4th interval (Bin 4) approximately represents 25% of the slower runners.

**β -Bin 1 β -Bin 2 β β β Bin 3 β β Bin 4**

**U+007C<- c.25% ->U+007C<- c.25% ->U+007C<- c.25% ->U+007C<- c.25% ->U+007C**

As shown in Table 2, the faster runners (Bin 1), the more consistent their average speed is across 5k,10k,15k and 20k splits. This deteriorates as we move up the quantiles (Bins 2,3 and 4).

`# create average speed dataframe `

average_speed = cleaned_df.loc[(cleaned_df['Split - 20K - Cumulative time'] > 0)&(cleaned_df['Split - 15K - Cumulative time'] > 0) & (cleaned_df['Split - 10K - Cumulative time'] > 0) & (cleaned_df['Split - 5K - Cumulative time'] > 0)].copy()

# calculate average speed for each cumulative split time

average_speed['split_1_avg_km/hr'] = average_speed['Split - 5K - Cumulative time'].apply(lambda x:5/(x/3600)).round(2)

average_speed['split_2_avg_km/hr'] = average_speed['Split - 10K - Cumulative time'].apply(lambda x:10/(x/3600)).round(2)

average_speed['split_3_avg_km/hr'] = average_speed['Split - 15K - Cumulative time'].apply(lambda x:15/(x/3600)).round(2)

average_speed['split_4_avg_km/hr'] = average_speed['Split - 20K - Cumulative time'].apply(lambda x:20/(x/3600)).round(2)

# calculate the average percentage change from 5k to 20k

average_speed[['five_k_avg_km_perc', 'ten_k_avg_km_perc', 'fifthteen_k_avg_km_perc','twenty_k_avg_km_perc']] = average_speed[['split_1_avg_km/hr', 'split_2_avg_km/hr', 'split_3_avg_km/hr','split_4_avg_km/hr']].pct_change(axis=1)*100

# find the mean percentage change for each runner

average_speed['Average Percentage Change'] = average_speed[['ten_k_avg_km_perc', 'fifthteen_k_avg_km_perc','twenty_k_avg_km_perc',]].mean(axis=1).round(3)

# create quantile cuts - 4 quantiles to represent U+007C<- 25% ->U+007C<- 25% ->U+007C<- 25% ->U+007C<- 25% ->U+007C

labels = 'Bin 1', 'Bin 2','Bin 3','Bin 4'

average_speed['Bin'] = pd.qcut(average_speed['Chiptime Seconds'], len(labels), labels=labels)

# quantile cut

quantile_df = average_speed.groupby(['Bin']).mean().round(3).reset_index()

qcut_t1 = quantile_df[['Bin','split_1_avg_km/hr','split_2_avg_km/hr','split_3_avg_km/hr','split_4_avg_km/hr','Average Percentage Change']]

qcut_t1 = qcut_t1.round(2)

qcut_t1['Average Percentage Change'] = qcut_t1['Average Percentage Change'].apply( lambda x : str(x) + '%')

# show

qcut_t1

## Average Race Speed versus Change in Average Speed between Splits 1 and 4 β Scatter Plot:

A visual representation of the average speed change between split 1 and split 4 (x axis), compared to the overall average race speed (y axis) for each participant, has been provided in Figure 7. This visual shows that the slower runners display a slower split 4 average speed when compared to split 1 average speed, across both genders. This means they generally slow down as the race progresses, which is also evident in Table 2 at the quantile level.

`# split 1 and 4 speed difference `

average_speed ['split_1_to_split_4_change_in_avg_km/hr'] = average_speed ['split_4_avg_km/hr'] - average_speed ['split_1_avg_km/hr']

# plot

fig = px.scatter(average_speed, y='Avg speed km/hr', x ='split_1_to_split_4_change_in_avg_km/hr', color='Gender', facet_col='Gender', opacity=0.4,template="simple_white")

# update layout

fig.update_layout(xaxis_ticksuffix = 'km/hr')

fig.update_layout(xaxis2_ticksuffix = 'km/hr')

fig.update_layout(yaxis_ticksuffix = 'km/hr')

fig.update_layout(yaxis2_ticksuffix = 'km/hr')

fig.update_layout(

yaxis=dict(showgrid=True, gridcolor='lightgrey'),

xaxis=dict(showgrid=True, gridcolor='lightgrey')

)

fig.update_layout(

yaxis2=dict(showgrid=True, gridcolor='lightgrey'),

xaxis2=dict(showgrid=True, gridcolor='lightgrey')

)

# update x-axes

fig.update_xaxes(

title='Average Speed between Splits 1 and 4')

#show

fig.show()

*Note: The analysis completed in Table 2 and Fig 7 required additional cleansing to only include recorded split times. The total recorded usable entries for these analyses are as follows:*

`# create count column`

gender2 = average_speed.groupby('Gender').size().reset_index(name='Count')

# create percentage column

gender2['Percentage'] = (gender2['Count'] / gender2['Count'].sum() * 100).round(1)

# add total row

total_row2 = gender2[['Count']].sum().rename({0: 'Total'})

total_row2['Percentage'] = (total_row2['Count'] / total_row2['Count'].sum() * 100).round(1)

gender2 = pd.concat([gender2, total_row2.to_frame().T])

# format percentage column elements

gender2['Percentage'] = gender2['Percentage'].astype(str) + '%'

# format Count column elements

gender2['Count'] = gender2['Count'].map('{:,.0f}'.format)

gender2 = gender2.reset_index()

gender2 = gender2.drop(columns=['index'])

gender2.loc[gender2.index[-1], 'Gender'] = 'Total'

# show

gender2

## Now itβs your time to run!

I hope you have enjoyed reading this article, and even better, I hope it has motivated you to run!

`References`

[1] https://results.sporthive.com/events/7037394564091167232

[2]https://www.runnersworld.com/uk/training/a774272/rules-of-running-runners-at-their-peak/#

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming aΒ sponsor.

Published via Towards AI