Thursday, 2 April 2015

Exercise Analytics Using Raspberry Pi, MySQL and APIs

Many of the posts I've been doing since the end of 2014 have been about using exercise API data and exercise database data to ensure I keep fit and healthy.  Key examples are:

This has taught me a lot about myself, in particular the need to set myself targets and periodically measure myself against these targets.  Back in early January 2015 I sent myself an email with this set of targets in it:

It's one quarter into the year, it's time to see how I'm doing against the targets.  First I needed to calculate a set of targets for the quarter.  I did this as Year / 4 rounded up to the nearest integer.  This makes the targets:

(Fitbit based Steps - St and Sleep - Sl have been "pro-rated" as I only got the device at the end of January).

So taking each exercise type in turn, here's how I got the data to compare with the targets.

Strava Data
This is cycling, swimming and running data logged using my Garmin sports watch and uploaded to Strava.

For this I wrote a Python script to extract information from the Strava API.  Full code at the bottom of this post but here's some highlights.

A obtained all the data with a single URL request to the Strava API:
urllib2.urlopen('' + StravaToken + '&per_page=200&after=' + TheUnixTime).read()

Here the per_page=200 entity reference means give me 200 records (more than enough for my Strava efforts) and after= means give all records after the specified period (which is defined in Unix time).

The resulting JSON defeated all my ham-fisted attempts to parse using simple methods so I ended up using the json Python module.  This made life a lot easier!  So with:

StravaJSON = json.loads(StravaText)to access the JSON structures.

...and the likes of ...

StravaJSON[i]['type'] to access specific fields. was easy enough to loop through the whole set of records, pick out all the swims, runs and cycles, add together the distances and write the results.  The output was:

pi@raspberrypi ~/exercise $ sudo python
Swim Count: 13. Swim Distance: 15600.0
Bike Count: 28. Bike Distance: 282693.4
Run Count: 12. Run Distance: 68337.6

(All distances in metres)

Jerks Exercises
Previously I blogged on how I used a MySQL database on my raspberry Pi to log "physical jerks" (e.g. press ups and sits ups).  I communicate these using a simple code sent in a tweet.

Getting all the data is as simple as running an SQL query.  Here's what I got:

mysql> SELECT exercise, SUM(count) FROM exercise where tdate >= "2015-01-01" GROUP BY exercise order by sum(count);
| exercise           | SUM(count) |
| Pilates            |         13 |
| Yoga               |         13 |
| Leg Weights        |         16 |
| Hundred Ups        |         19 |
| General Stretching |         23 |
| Foam Rolling       |         25 |
| Squatting          |        112 |
| Arm Raises         |        322 |
| Side Raises        |        322 |
| Clap Press Ups     |        328 |
| Bicep Curls        |        342 |
| Shoulder Press     |        367 |
| Tricep Curls       |        374 |
| Sit Ups            |        789 |
| Abdominal Crunches |       1429 |
| Press Ups          |       1501 |
| Calf Raises        |       1839 |


Fitbit Data
For the sleep data from my Fitbit Charge HR I re-used code that I used for my sleep infographic.  This gave me a per day sleep figure that I simply summed to give me the total sleep for the period.

For steps data (and floors climbed data) I simply modified the sleep code to 1)access the activities resource and 2)pull out the steps and floors data.  Full code below.  Key parts were getting activity data from the API using fitbit-python:

fitbit_data = authd_client._COLLECTION_RESOURCE('activities',DateForAPI)

.and extracting steps and floors from the resulting JSON:

#Get the total steps value
TotalSteps = fitbit_data['summary']['steps']

#Get the total floors value
TotalFloors = fitbit_data['summary']['floors']

Again I simply summed the data from the summary file to give me the single figure I needed.

Overall Result
Here's a table with the overall result.  Green means target met or exceeded, red means not met.


  • I nailed all the "counting" targets
  • I missed all the harder qualitative targets (e.g. cycling and running speed).
  • Some counting targets I only just sneaked in (e.g. Yoga and Pilates).  This is generally stuff I don't like doing.
  • Some counting targets I exceeded by a fair amount (e.g. press ups).  These are things I like doing!

So I need to find a way to beat the speed targets I set.  How can technology help me with that??

(I've also added a new target for floors climbed based upon my Q1 daily average).

Strava - Code

import urllib2
import json

#Constants - For Strava
StravaToken = '<Key Here>'

TheUnixTime = '1420070400'

#Access the Strava API using a URL
StravaText = urllib2.urlopen('' + StravaToken + '&per_page=200&after=' + TheUnixTime).read()
#print StravaText

#Parse the output to get all the information.  Set up some variables
SwimCount = 0
SwimDistance = 0
RunCount = 0
RunDistance = 0
BikeCount = 0
BikeDistance = 0

#See how many Stravas there are.  Count the word 'name' as there's one per record
RecCount = StravaText.count('name')

#Load the string as a JSON to parse
StravaJSON = json.loads(StravaText)

#Loop through each one
for i in range(0,RecCount):
  #See what type it was and process accordingly
  if (StravaJSON[i]['type'] == 'Swim'):
    SwimCount = SwimCount + 1
    SwimDistance = SwimDistance + StravaJSON[i]['distance']
  elif (StravaJSON[i]['type'] == 'Ride'):
    BikeCount = BikeCount + 1
    BikeDistance = BikeDistance + StravaJSON[i]['distance']
  elif (StravaJSON[i]['type'] == 'Run'):
    RunCount = RunCount + 1
    RunDistance = RunDistance + StravaJSON[i]['distance']

#Print results
print 'Swim Count: ' + str(SwimCount) + '. Swim Distance: ' + str(SwimDistance)
print 'Bike Count: ' + str(BikeCount) + '. Bike Distance: ' + str(BikeDistance)
print 'Run Count: ' + str(RunCount) + '. Run Distance: ' + str(RunDistance)

Fitbit - Code

import fitbit
from datetime import datetime, timedelta
import time

CLIENT_KEY = '<Yours Here>'
CLIENT_SECRET = '<Yours Here>'
USER_KEY = '<Yours Here>'
#USER_KEY = '<Yours Here>'
USER_SECRET = '<Yours Here>'

#The first date I used Fitbit
FirstFitbitDate = '2015-01-27'

#Determine how many days to process for.  First day I ever logged was 2015-01-27
def CountTheDays():
  #See how many days there's been between today and my first Fitbit date.
  now =                                         #Todays date
  FirstDate = datetime.strptime(FirstFitbitDate,"%Y-%m-%d")    #First Fitbit date as a Python date object

  #Calculate difference between the two and return it
  return abs((now - FirstDate).days)

#Produce a date in yyyy-mm-dd format that is n days before today's date (where n is a passed parameter)
def ComputeADate(DaysDiff):
  #Get today's date
  now =

  #Compute the difference betwen now and the day difference paremeter passed
  DateResult = now - timedelta(days=DaysDiff)
  return DateResult.strftime("%Y-%m-%d")

#Get a client
authd_client = fitbit.Fitbit(CLIENT_KEY, CLIENT_SECRET, resource_owner_key=USER_KEY, resource_owner_secret=USER_SECRET)

#Find out how many days to compute for
DayCount = CountTheDays()

#Open a file to write the output - minute by minute and summary
SummaryFileToWrite = '/home/pi/exercise/' + 'summary_' +"%Y-%m-%d") + '.csv'
SummaryFile = open(SummaryFileToWrite,'w')

#Process each one of these days stepping back in the for loop and thus stepping up in time
for i in range(DayCount,-1,-1):
  #Get the date to process
  DateForAPI = ComputeADate(i)

  #Tell the user what is happening
  print 'Processing this date: ' + DateForAPI

  #Get sleep
  fitbit_data = authd_client._COLLECTION_RESOURCE('activities',DateForAPI)

  #Get the total steps value
  TotalSteps = fitbit_data['summary']['steps']

  #Get the total floors value
  TotalFloors = fitbit_data['summary']['floors']

  #Write a log of summary data
  SummaryFile.write(DateForAPI + ',' + str(TotalSteps) + ',' + str(TotalFloors) + ',' '\r\n')

  #Wait a bit (for API rate limit)

#We're now at the end of the loop.  Close the file