2nd July, 2020 Michael Forrest
Mark Fisher once told me his theory that exercise is good for you not only for the endorphins or for general health improvements, but because the simple knowledge that you have done some exercise for the day makes you feel good. (He said lots of interesting things. You should find out more about him.)
Today I will put this theory to the test by cross-referencing my happiness with my physical activity.
I track my happiness every day using an app I made myself called Changes which is available on the App Store. I have tracked my activity with various apps and devices over the years but today I will be focusing on Runkeeper and Myzone.
I would use Apple Health data but it is difficult to bring into a spreadsheet. I’ll save that for integration into Changes at some point in the future (join the mailing list if you want to hear when that’s ready).
I’ll be using Google Sheets to analyse my data. You can follow along now or some time in the future if you want to look at your data the same way.
First go to Settings → Export Data → Export Spreadsheet to get a CSV file. I’m leaving Filter sensitive data switched on so you don’t see my location or sensitive diary entries. I usually transfer this file to my laptop using AirDrop but you can email or whatever you like.
Now import this data into a spreadsheet. I’m using Google Sheets, so in Chrome type
sheets.new into the location bar, go to File → Import → Upload.
Start by dragging in your Changes CSV file.
Here’s what’s in the Changes spreadsheet:
Rename this sheet to Happiness before you continue.
If you use Runkeeper you can get your data via this link https://runkeeper.com/exportData
You’ll get a zip file that contains a file called
cardioActivities.csv . Upload this file and use Insert New Sheet to place it alongside your Happiness sheet.
Warning: Google Sheets will incorrectly interpret Runkeeper’s duration values, so set “Convert text to numbers, dates, and formulas” to NO.
To get your Myzone data, log into the website and navigate to your profile at http://www.myzonemoves.com/user Then click the icons shown in this sequence…
This file needs to be modified to make it easy to bring into a spreadsheet. It contains summary rows separated by big lists of minute-by-minute activity. We just want the summary rows.
I had to do some programming to convert this but all you have to do is go to https://goodtohear.co.uk/tools/myzone
Just drop your CSV file here and it will send you back a file called
Upload this resulting file using Insert new sheet(s) and you should see something like this:
Myzone needs a proper Date Time column. Insert a new column to the left of column A and in row 2, enter the formula
=M2+B2, where column M is Date and column B is Start. Fill this column down and format it as Date time.
Now create a new sheet called Workouts. Paste in the entire contents of the cardioActivities sheet. Fix the first row so the column headings stay on screen. Then paste the entire contents of the Myzone sheet at the bottom-right of the other data, before moving those Myzone headings to the top.
Now we’re going to create the columns we need at the left of this new sheet. Add four new columns:
Enter Runkeeper alongside all the Runkeeper rows.
Add Myzone for the Myzone rows.
Fill out the Date column for the Runkeeper rows by referencing the appropriate column (F in my case)
Continue with this technique, filling in the unified Calories cells with the column data appropriate to each set until you have this:
Finally, we’ll deal with the Duration column.
Runkeeper provides durations in the format
25:03 (24 minutes, 3 seconds) or
1:03:23 (1 hour, 3 minutes, 23 seconds). Google Sheets will normally incorrectly interpret the former example
25:03 as 25 hours, 3 minutes.
If we slice up the text we can eventually get the real value. Here’s the formula, where
J2 is the Runkeeper Duration column.
**TIME** formula takes three arguments:
So, working backwards:
\:\d+\:\d+") and takes the first digits (in brackets, in bold) using
REGEXEXTRACT. If no match is found by
REGEXEXTRACT, we make that a
IFERROR, giving the resulting formula
IFERROR(REGEXEXTRACT(J2,"(\d+)\:\d+\:\d+"), 0)for the hours value.
Ouch. That wasn’t much fun.
Myzone provides the duration in minutes so thankfully we can just use
Y69 is Myzone’s Duration column.
Here’s what you should have by the end of all this (with the Date column formatted as Date time and the Duration column formatted as Duration).
Now let’s create a Time since last workout column to our Happiness sheet.
I haven’t found a way to do this without resorting to the
QUERY() takes the following parameters:
data: the range of cells to perform the query on
query:The query to perform, written in the Google Visualization API Query Language.
headers- [optional] The number of header rows at the top of 'data'. If omitted or set to -1, the value is guessed based on the content of 'data'.
Here’s what we want our query to look like:
select A,B,C,D where A < [DATE] LIMIT 1
We want four columns of data, expressed here as
D, corresponding to
Date(of the workout),
Source (Runkeeper or MYZONE),
We are looking for the nearest workout so we say
A < [DATE] and we only want 1 result so we add
I found it easiest to create a separate column to build this query, which you can see here:
The formula in column
=CONCATENATE("select A,B,C,D where A < ", $A2, " LIMIT 1")
In Column C we have the formula
The last argument here,
-1, tells the
QUERY function to omit column headings in the results.
Three empty columns are needed to the right of the
QUERY column to leave enough space for the results.
QUERY function will only work if the Workouts sheet is sorted in reverse date order, with the date formatted as a plain number.
Now we can add some new columns Time Since Last Workout, During Workout? and Hours Since Workout.
A2is the entry date time and
C2is the last workout date time.
=IF(AND(A2>C2, A2 < C2+F2), "YES", "NO"). We want to know if the entry was after the start of the last entry and before the end (calculated with
C2 + F2where
C2is the workout time and
F2is the workout duration.
Let’s do our trick of creating an Unhappy, Neutral and Happy column for each entry to look like this:
Copy and paste everything we want to chart to somewhere more stable, using Paste Values Only.
Select the Time Since Last Workout ***column and apply a “Custom number format” via the menus *Format→More Formats. Set this format to
[h] to make this column display the number of hours since the last workout.
Now select the columns Time since last workout, Unhappy, Neutral and Happy and say “Insert Chart”. Set the X-axis to Time since last workout, check the box marked “aggregate” and you should get something we can start making sense of…
Set the series colours and you can see something like this:
It feels like there are slightly more happy entries closer to a workout but I think we can make this a lot simpler by adding a new Did I Work Out Today? column. Since dates are represented with the number 1 = a day, it’s a simple calculation. Round down the days with
FLOOR and check if they’re the same and then make sure that the workout was before or after using
Date > Workout Date.
=IF(AND(FLOOR(A2)=FLOOR(B2),A2 > B2),"YES","NO")
Now we can get a direct comparison.
Insert a chart with our new column and the ratings columns selected. Aggregate the X-Axis, select 100% Stacked Column Chart, use Count instead of Sum so we don’t lose the Neutral entries, set some appropriate series colours and we get this:
Looks relatively conclusive! The green areas are 66.4% for days after I’ve worked out, and 58.8% for days when I hadn’t.
Changes is available for iOS in the App Store now or you can learn more about happiness tracking by signing up for my free eBook below.
If you chart your own data, let me know! I’d love to see your charts and hear your stories. Tweet me @michaelforrest - it will really give me a kick to see what you come up with.
Sign up for my newsletter to get weekly tips on mood tracking, happiness data analysis and forming better habits.
I will send you a copy of my eBook about mood tracking.