Python for Coaches Part 4:Wrangling your Training Peaks Data with Pandas¶
Alan Couzens, M.Sc. (Sports Science)
In my last post on Python for Coaches, we went through how to pull training data from Strava's API (application programming interface). While API's provide an ideal route to gather data, especially streaming data that changes over time, not all platforms offer an API. Training Peaks for one, does not offer a public API. They used to but elected to discontinue it (not that I'm bitter about that or anything :-) Never mind, there are other ways to access your data from Training Peaks. We briefly looked at one of those ways to pull data from non-API url's in my last post. This week, we'll look at another - pulling data from csv files.
Like the other posts in the series, you can follow along via an interactive notebook that you can make a copy of and upload your own training peaks csv files to, to analyze your own data here
Many websites that collect data will offer a csv export of that data. Training Peaks, for one, offers a really handy csv export of your data...
If you click on your profile, you'll see there's an export option in the menu that provides the ability to export raw files (see my first post on how to read and analyze these) but also a csv export of your workout and your metrics data.
Simply enter 2 dates in the boxes of either, click 'export' and you will find a nice csv export of your workout data or your metrics data (resting heart rate, sleep etc) in your downloads folder on your PC.
A csv file, or comma-separated-value file, is just an easy to read text file of data that is, predictably enough, separated by commas :-) You can easily open a csv file with your notepad app on your machine or, more conveniently with any spreadsheet program like Excel. If you use Excel to open your metrics file, here's what you'll see...
A treasure trove of wellness data! Including sleep metrics, heart rate variability, stress, injury and overall feeling. If you open the workouts csv, you'll see a similar wealth of mineable data!
Via its csv exports, Training Peaks offers us a ton of data from the workouts in an easily accessible format! - The workout title, time, planned duration, actual duration, date, coach comments, distance, average power... Oh heck, it's going to take me ages to list out every data field, let's do it the easier way! Now would be a great time to introduce Pandas!
Pandas is a Python library that makes pulling, analyzing, cleaning and processing of data super easy. You've probably heard the expression "garbage in, garbage out". When it comes to working with and modeling data, nothing could be more true. It's essential that before we begin the process of analyzing and building models around data, we make sure that the data is good! I.e. that the data is in a format that we can process and that there are no weird errors or outliers that might pollute things and lead to erroneous conclusions. This is a very important part of data science and Pandas makes it as painless as possible.
Let's introduce pandas by using it to view all of the available fields in our workouts csv
import pandas as pd
df = pd.read_csv('workouts.csv')
list(df.columns)
In the above code we import the pandas library calling it pd (because we're lazy and we don't want to have to type "pandas" every time we call a function :-), then we create a dataframe from our spreadsheet that we simply call "df" (this is a common convention when working with pandas) by calling pandas "read_csv" function.
You can think of a dataframe as pandas version of a spreadsheet, i.e. it's a 2 dimensional set of data with columns across the top - whose name we accessed with the df.columns call above, and rows down the side of the page. Another convenient way to think of a pandas dataframe is as a python dictionary. You'll remember from the last post that a dictionary is a named list denoted by curly brackets {} with key:value combinations for each field e.g....
my_dictionary = {'first_name':'Alan', 'last_name':'Couzens', 'age':'45'}
One thing that you can do with a dictionary that you'll often see with json formatted data is to combine a dictionary with lists, e.g. if I was to add "favorite songs" to my dictionary above (of course I can't pick just one :-) I could create a list of my favorite songs within my dictionary by doing something like...
my_dictionary['favorite_songs'] = ['Round Here - Counting Crows', 'Let Down - Radiohead']
print(my_dictionary)
You can see my dictionary now also has a list within it (denoted by the square brackets []) I bring this up because your dataframe is essentially the same structure, it is a dictionary of column names and each column name has a list with all of the values in that column underneath it, e.g. if we want to pull a list of all of the workout names, we can reference the 'Title' column of our dataframe in the same way that we would any dictionary...
df['Title']
This returns a list of all of the workout titles & if we want to access a particular row within our list, we can do it in the same way that we would access any item within a regular list by passing the index, e.g. the 50th row on the list would be (remember lists begin at index 0)...
df['Title'][49]
Cool! So, we have 2 massive python dictionaries - one with the athlete's wellness metrics, the other with the athlete's workouts. What can we do with them?
Well, we can do a lot with them. But, for starters, one of the most important things for a coach to understand about an athlete is the relationship between the training load and the level of fatigue experienced by the athlete, i.e. how much is "too much" for this athlete? At what point does the training start to encroach negatively on the athlete's heart rate variability, soreness, mood and overall wellness?
You can see that, here, with the ability to export both training and wellness data from Training Peaks, we have the ability to answer this question. However, as it stands, the source for each of these components of the data is in 2 separate spots, 2 separate spreadsheets, i.e. the data currently lacks the ability to 'talk with each other' & frankly, in its current format, it's hard to make a lot of sense of it. Sounds like a perfect case for some good ol' data wrangling! :-)
In the words of wikipedia..
Data wrangling is the process of gathering, selecting, and transforming data to answer an analytical question
Which is exactly what we want to do here - we want to investigate the relationship between training load and athlete wellness but in order to do that, we will need to gather, select and transform the data...
To begin with, if we take a look back at the way the metrics csv is formatted, you'll see it's kind of weird. We have 3 columns - a timestamp, a data field (HRV, soreness, mood etc) & the value for that data field. It would be much easier to understand and to work with if the data instead had each row as one day and then columns for HRV, soreness, mood, stress, fatigue etc across the top. So let's start by using pandas to put it in that format...
metrics_df = pd.read_csv('metrics.csv')
print(metrics_df)
So, the above is the current format of our metrics dataframe. What we want is to group all of the different types together in their own column. There are a few ways that we can do this with pandas, but let's go for the most explicit: We'll pull each of the metrics that we want into its own column and then merge them together...
HRV = metrics_df[metrics_df.Type=='HRV']
HRV = HRV.set_index('Timestamp')
HRV = HRV.drop('Type', axis=1)
HRV = HRV.rename(columns={'Value':'HRV'})
print(HRV)
Above, we're introducing a new very powerful pandas feature called "masking" Masking is a way of searching your dataframe to return only the rows that match a given condition. This condition could be numerical e.g. "Give me all the days that HRV was below 8" or it could be text based e.g. give me all the rows corresponding to a given athlete's name etc. In this case, we want to separate each of the different metric types beginning by selecting only the rows that have "HRV" as their type. We then set the index to timestamp so that we have a common index to build our big metrics dataframe and, finally, we drop the type (since we know all the values are HRV) & rename the column to 'HRV'. Since we want to do this multiple times (for HRV, sleep, mood etc), let's build a simple function to do the same for each of the other key metrics and join them together into our new, improved metrics dataframe...
def build_series(metric):
new_df = metrics_df[metrics_df.Type==str(metric)]
new_df = new_df.set_index('Timestamp')
new_df = new_df.drop('Type', axis=1)
new_df = new_df.rename(columns={'Value':str(metric)})
return new_df
HRV = build_series('HRV')
pulse = build_series('Pulse')
stress = build_series('Stress')
sleep_quality = build_series('Sleep Qualilty')
sleep_hours = build_series('Sleep Hours')
mood = build_series('Mood')
new_improved_metrics = pd.concat([HRV, pulse, stress, sleep_quality, sleep_hours, mood], axis=1)
print(new_improved_metrics)
Ahh, much better! We built our little function above to separate all of the values into their own column and then we "concatenated", i.e. joined each of these columns together into its own big dataframe that is formatted the way we want, i.e. with each day as a row and then the various wellness features each having their own column for the day. Beautiful!
Next, let's bring our training data into the mix. Let's start by adding up the total TSS for a given date.
workouts_df = pd.read_csv('workouts.csv')
TSS_day = workouts_df.groupby(['WorkoutDay'])['TSS'].sum()
print(TSS_day)
Here we return to our workouts dataframe (the exported csv of workouts) from above and we use pandas grouby function to tell it to group all of the workouts with the same WorkoutDay together and then add up the total TSS for that day. This gives us a nice series with the workout date as the index and the total TSS for that day as a column.
Now, let's add that daily training load to our new_improved_metrics dataframe.
In order to bring these 2 dataframes together, we want a common index. The first thing you'll notice if you look at the metrics and workouts dataframes is that the index (the first column) is a little different. For our training load we have just the date in Y-m-d format. For our metrics dataframe, we have the whole timestamp. We want to make these the same if we're going to be able to join the two dataframes together. Let's do that...
new_improved_metrics['Date'] = new_improved_metrics.index
new_improved_metrics['Date'].astype(str)
new_improved_metrics['Date'] = new_improved_metrics['Date'].str.slice(0,10)
print(new_improved_metrics['Date'])
new_improved_metrics = new_improved_metrics.set_index('Date')
Easy enough.. we used pandas to pull the index into its own column called 'Date'. Then we converted the datetime object to a string. Then we sliced the string so that it contained only the first 10 characters yyyy-mm-dd. Then we set that chopped down date as the index for our dataframe so that it matches the index of our workouts dataframe. Now let's use our old favorite concat function to join these 2 dataframes of training load and wellness metrics into one...
new_improved_metrics = pd.concat([TSS_day, new_improved_metrics], axis=1)
print(new_improved_metrics)
Now that we have our new and improved dataframe that contains both athlete wellness and training load data, we can have a bit of fun analyzing the data...
new_improved_metrics.info()
To start with, the info() method returns a count of how many non-null values we have for each. For this athlete, he has completed 365 days worth of values for TSS, HRV, Pulse and Stress and only a couple missing for Sleep Quality, Hours and Mood, A commendable job of data collection! :-)
One little problem that we see immediately when we look at the data info is that all of the data apart from TSS has Dtype "object". This is the standard data type for when we are dealing with text but, if we want to be able to perform calculations on the data, we need to change it to a 'float' (a number in decimal format) so let's start by doing that...
metrics = ['HRV', 'Pulse', 'Stress', 'Sleep Qualilty', 'Sleep Hours', 'Mood']
for metric in metrics:
new_improved_metrics[metric] = new_improved_metrics[metric].astype(float)
new_improved_metrics.info()
Perfect! Now all of our data is a number. We need numbers to compute statisitics so, that was a good first step. Let's compute some basic descriptive statistics for our data now..
new_improved_metrics.describe()
This, in itself is a really handy feature of pandas. Not only does this provide a really easy way to identify funky data, e.g. if we saw that our max pulse was 200, we would know that we need to go in and clean the funky values before applying to any models. But, also, we can get a lot of descriptive data about this athlete. We can see that he averaged 152 TSS/day of training load, with a standadrd deviation of ~72TSS/d. We can also see that the athlete has a mean HRV score of ~9.8 and a standard deviation of only 0.4, i.e. all scores are narrowly dispersed. The stats go a step further in explaining this dispersion by setting quartiles, i.e. the cut off for the bottom 25% of scores is a HRV of 9.5, and the top 25% of scores are above an HRV of 10.1. This kind of context is very handy when assessing just how low a score needs to be before raising the red flag. If you run this same describe function on many other athletes, you'll see a much wider range of HRV scores. This is the reason that sticking to arbitrary cut-offs when changing the plan in accordance with HRV is a poor strategy.
Another neat way to get a quick overview of the data is to plot it.
import matplotlib.pyplot as plt
new_improved_metrics.hist(figsize=(20,15))
plt.show()
Here we have a frequency histogram of the data showing how many occurences for the year occured in each 'bin'. Again, very helpful in providing context. Clearly, this athlete is a very stable athlete, meaning any excursions from the norm - a rating of poor sleep, or a bad mood etc are worthy of taking note!
On the training side, we can see that the vast majority of days were between 80 and 150 TSS and and impressive 50 days, i.e. one day each week was between 225 and 275 TSS! It seems with consistency of training load comes a nice, stable physiology!
Another really useful thing to do in the sense of determining which features are important is to see which features are correlated. This couldn't be easier with Pandas..
corr_matrix = new_improved_metrics.corr()
corr_matrix['HRV'].sort_values(ascending=False)
We can see that for this athlete, his sleep quality is most strongly correlated with a high HRV. Mood is also positively correlated & Stress is negatively correlated, i.e. as self reported stress goes up, HRV goes down.
It may come as a surprise that TSS is positively correlated with HRV but remember, this is the TSS of training done for that day after the morning metrics are collected. Let's see if the TSS performed on the day before creates more of a negative shift
new_improved_metrics['yesterday_TSS'] = new_improved_metrics['TSS'].shift(1)
print(new_improved_metrics)
Here we simply apply pandas shift function to create a new column called "yesterday_TSS" that moves all training load forward by one day. Let's see if yesterday's TSS is better correlated...
corr_matrix = new_improved_metrics.corr()
corr_matrix['HRV'].sort_values(ascending=False)
As expected, it's now slightly negatively correlated, i.e. higher training load on the day before leads to slightly lower HRV.
A couple of other useful features to add might be the long term dynamics of chronic and acute training load, i.e. CTL and ATL. Again, this is super easy with pandas..
import math
def calc_ctl(TSS:list, start_ctl, exponent):
ctl = [start_ctl]
for i in range(len(TSS)):
ctl_value = TSS[i] * (1-math.exp(-1/exponent)) + ctl[-1] * math.exp(-1/exponent)
ctl.append(ctl_value)
return ctl
ctl = calc_ctl(new_improved_metrics['TSS'], 103, 42)
atl = calc_ctl(new_improved_metrics['TSS'], 50, 7)
tsb = []
for i in range(len(ctl)):
tsb.append(ctl[i]-atl[i])
print(ctl)
print(atl)
print(tsb)
Excellent! We built a quick function above to calculate the exponentially weighted chronic and acute training load from the TSS values that we pulled from TP. We supply the starting value for each, along with the exponent that we wish to use (42 & 7 are the default numbers in training peaks). We then return a list of ctl, atl and tsb numbers that we can add to our dataframe....
new_improved_metrics['ctl'] = ctl[1:]
new_improved_metrics['atl'] = atl[1:]
new_improved_metrics['tsb'] = tsb[1:]
print(new_improved_metrics)
new_improved_metrics.describe()
You can see that we now have the additional context added by the long term dynamics of chronic load, acute load and training stress balance to compare against the athlete's wellness scores - all in the one place!
In this case, the athlete peaked out at 191CTL coming from a start point of 94. A solid year all things considered!
We can also run our correlation analysis again to see how the longer term load dynamics relate to HRV (or anything else for that matter)
corr_matrix = new_improved_metrics.corr()
corr_matrix['HRV'].sort_values(ascending=False)
Adding CTL, ATL and TSB to the correlation matrix we see a slight negative correlation with HRV for each, i.e. as absolute chronic load goes up, the athlete's HRV tends to go slightly down. Though, it's worth noting (for this athlete) that the power of this relationship is not even close to the relationship between self reported stress or sleep quality!
Hopefully, by now you're seeing the power of Pandas in pulling all of your data together from various places so that we can clearly see the relationships. Being able to pull the data together & 'see' the relationships is only the first step in developing powerful individual models that allow us to use machine learning to predict fitness and fatigue for an individual athlete & pandas is perfectly suited to talk to some of these machine learning libraries that I will introduce in future posts.
But, for now, we started with a couple of messy csv's so let's end by saving our dataframe into a much more useful csv that we can use to feed our machine learning models in future posts...
new_improved_metrics.to_csv('new_improved_metrics.csv')
Much better! Long and short term training load & wellness data all wrangled together, ready for the fun stuff of building some models. We'll tackle that next time around. Until then...
Code smart,
AC