Using Painsled with Excel

The story goes back to rowpro.  A rowpro user (danburpee) on what was then the concept2 UK user forums invented a spreadsheet to process the csv files that rowpro can export for a workout.  This template was improved and modified over time and is a very useful tool to analyze workouts.  I started with the spreadsheet and adapted it to be used with crewnerd,  RIM, speedcoach and the yahoo fitness app.  When I found out that someone was creating an app for the iphone (and android i guess) that allowed one to log data from the PM3 and PM4 (via USB) or PM5 (via BT), I thought I needed to give it a try.  The app is called Painsled and it is still in beta, but is quite functional as far as I can see.

Right now the app is a bit primitive.  It essentially just logs data from the PM and stores it.  Then that data can be exported as tcx, json, or csv files.  Getting the data off the iphone requires you to plug into itunes and move files to your computer, but I imagine that this will be improved in the future.

I did some workouts with the app and exported the CSV and took a look at what you get.  It’s very different from the exports from the other  tools in a number of ways.

  1.  There is essentially a record for each “event” that comes from the PM.  So, every update of HR, or change of stroke state generates a record in the file
  2. Not all the parameters are logged in each record
  3. There are 31 (31!) fields of data

The result of this is that even short workouts generate really long csv files.  All of my tools assume that the CSV data is formatted in a more concise format.  Essentially one record per stroke, or per set distance or per set time.  Also that every field has valid data for each record.

I experimented with a couple of ideas.  I tried to read it all into excel and concatentate the data there, but because of the sheer number of rows, the performance was miserable.  It was also a pain to do what was essentially a sequential task in a programming environment that was sheet and formula based.  Ultimately I decided that I needed to preprocess the data.

I decided to make the data look as much like the export from my speedcoach as possible.  That is the excel workbook that I have done the most work on, and the file format is very straightforward.  I decided that I would tailor the workbook for painsled, but try to keep it as close as possible to the speedcoach starting point as possible.

I built a script in python called pspack2.py.  You might guess that the name means that it was my second attempt.  Actually, it is more like my fourth, but that’s not important.  I’ve posted the script here : pspack2 (apologies about posting it as PDF, apparently wordpress doesn’t like me posting text files.)

The script is straightforward.  You pass it the filename you want processed, and it does the following.

  1. Scan the file for records that indicate that the stroke state is recovery and save the indices
  2. Read each record of the file and maintain the last valid numerical value for each field.  Basically carrying the last valid value down through all the following records
  3. When you hit a record  that is a “recovery record”, write the current values for the fields to the output file.  I massaged the data field order and formatting to match the speedcoach template.

Then in the excel file, I just adjusted the axis scaling on the graphs to line up with erg splits instead of boat splits, and it worked great.  Here is the current file: Painsled Template Rev – (note:  you need to change the file extension from .xlsx to .xlsm for the file to function properly.  Also note, this works only on excel 2010 and later on windows)

This is not completely done.  There are three enhancements that I want to work on.

  1. Painsled logs distances on a per interval basis, versus accumulating like rowpro.  I will put some math in the script to change that, but it hurt my head to think it through right now.
  2. I like to look at watts, so I will add graphs  to the spreadsheet for that
  3. I want to build a time based interval text summary tab.

But, it works well enough for now and I had some fun playing with python again.

Screen Shot 2016-01-29 at 9.00.43 AMScreen Shot 2016-01-29 at 9.01.00 AM

 

 

3 thoughts on “Using Painsled with Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s