Rowing Data Analytics: Reducing and Studying the Rowing Workout

In my last post (“Rowing Data…”) I discussed the steps associated with downloading the Garmin Vivoactive HR data from Garmin Connect to an Excel spreadsheet. In this post, I’m going to take the reader through the analysis of the data as a tutorial and guide for assessing certain elements of these data.

Raw data in Excel format are shown in Figure 1. I am going to focus on distance (column M), speed (column N), and heart rate (column O).

Figure 1: Downloaded rowing worksheet from Garmin Connect

I normally like to study discrete, time-based data by translating the time component from the Zulu time (column L) into a relative time from the start of the workout. Furthermore, I like to translate these into units of seconds as the base unit.

To do so, we can take advantage of some powerful capabilities contained within formulas inside of Microsoft Excel. For example, the start time listed in column L begins with the entry:

2017-07-08T14:09:31.000Z

The next entry is:

2017-07-08T14:09:34.000Z

These are “Zulu” time or absolute time references. We wish all future times to be keyed or made in reference to the first time. In order to do so, we need to translate this entry into a time in seconds. We can do so by parsing each element of the entry. These entries are listed sequentially in column L2 and L3, respectively.

Each element is translated into seconds by parsing the hours, minutes and seconds using the following formula:

=MID($A$2,12,2)*60*60+MID($A$2,15,2)*60+MID($A$2,18,2)

The first component extracts the time in hours and translates into seconds. The second component extracts the “minutes” and translates into seconds. The third component extracts the “seconds” element by itself. The total time is the superposition of all three individual components.

Thus, what I normally do is to copy the contents of the initial spreadsheet into a new sheet adjacent to the original and then begin working on the data. Presently, I am in the process of developing an application that will perform this function automatically. Yet, here I am “walking the track” associated with analyzing the data in order to chronicle the mathematics surrounding the process.

The hour, minute and second can be extracted as separate columns. Let us copy the contents of column L in the original spreadsheet into a new sheet within the existing workbook and place the time in column A of that new sheet. Thus, the entries in this sheet would appear as follows:

ns1:Time Absolute Time (seconds) Relative Time (seconds)
2017-07-08T14:09:31.000Z 50971 0
2017-07-08T14:09:34.000Z 50974 3
2017-07-08T14:09:35.000Z 50975 4

The Absolute time in the middle column is the time in seconds represented by the left-hand column relative to Midnight Zulu time. The right-hand column is the time relative to the first cell entry in the middle column. Thus, zero corresponds to 50971-50971. The entry for three seconds corresponds to the difference between 50974 (second entry) and 50971 (first entry), and so on.

I also created some columns to validate parameter entries. For instance, the reported total distance and speed (in units of meters and meters per second, respectively), in column M and N and the heart rate, in column O, are referred to next. I created a new column O in the new spreadsheet to provide a derived estimate of total distance, which I computed as the integral of speed over time. The incremental distance, dS, is equal to the speed at that time, dV, multiplied by the time differential between the current time and the previous time stamp, dt. Then, the total distance is the integral, or the summation of this incremental distance and all prior distances. I reflect this as column G in the new worksheet, shown in Figure 2.

Figure 2: Modified rowing spreadsheet with derived time, range and longitude-latitude calculations.

What follows now are plots of the raw and derived data. First, the heart rate measurement over time is shown in Figure 3. Note that the resting rate is shown at first. Once the workout intensifies, heart rate increases and remains relatively high throughout the duration of the workout.

Figure 3: Workout heart rate (pulse) versus time.

The total distance covered over time is shown in Figure 4. This tends to imply a relatively constant speed during the workout due to the linear behavior over the 8700+ meters.

Figure 4: Workout range versus time. Note linear behavior, indicating relatively constant speed.

The reported speed, as measured via GPS, shows variability but is typically centered about 1.85 meters per second. The speed over time is shown in Figure 5.

Figure 5: Workout measured speed versus time. Average is 1.85 meters per second.

The GPS coordinates are also available through the Excel data. I have subtracted out the starting location in order to provide a relative longitude-latitude plot of the workout, shown in Figure 6.

Figure 6: relative longitude and altitude of the workout.

In my next post I will focus on the athletic aspects of the workout related to training.

Rowing Data: Accessing Heart Rate, Distance and GPS Location from Garmin Vivoactive HR

An Introduction to Garmin Connect

For those who use the Garmin Connect Dashboard (https://connect.garmin.com), to synchronize their Garmin fitness devices, there is a fairly straightforward method for downloading higher-frequency data from the workout relative to heart rate, distance and GPS location that can be directly imported into Microsoft Excel for further analysis.

Getting Started with the Download

From inside of Garmin Connect (Figure 1), select a specific activity. In this example, I am picking my latest rowing workout, shown by the red arrow.

Garmin Connect: Accessing workout data within your Garmin Vivoactive HR.

Once you have selected the specific workout, click on it and this will take you to the details of that workout. Once there, navigate over to the gear on the right-hand side, as shown by the red arrow.

Detailed view of workout within Garmin Connect.

The drop-down box from the arrow shows a number of export options. Select “Export to TCX”, shown by red arrow in Figure 3.

Accessing export function within Garmin Connect.

Upon selection, the file will be downloaded, as shown in Figure 4. On a Windows platform, this will be downloaded by default to the user’s downloads folder.

File download in .TCX format from Garmin Connect.

Once the file is downloaded, go to the file directory and locate the file you just downloaded, as shown in Figure 5.

Locating .TCX file in downloads directory on Windows platform

Then, change the suffix from .TCX to .XML, as shown in Figure 6. Accept the change when prompted.

Renaming .TCX file to .XML.

Now, open Microsoft Excel. Select the Data tab, as shown in Figure 7.

Microsoft Excel: selecting Data tab in preparation for .XML file import.

On the left-hand side, select Get External Data “From Other Sources”, and scroll down to “From XML Data Import”, as shown in Figure 8.

Importing the .XML file into Microsoft Excel using the import
XML file option.

A dialog box will open. Navigate to your newly-created XML file. Select it, and click the series of “OK” buttons in the dialogs that come up, including the one placing the location of the start in cell $A$1. Once completed, the contents of the file will be imported into your spreadsheet. Heart rate data will be contained in column O, as shown in Figure 9. Distance & speed are contained in columns M & N, respectively. GPS latitude & longitude are contained in columns P & Q, respectively. Average speed is contained in column R.

Viewing available columns of data within Microsoft Excel.

In my next post on the subject I will describe how to manipulate these data for further analysis.