2nd July, 2020
Michael Forrest

Happiness vs Exercise

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).

Mood Data

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.

1. Exporting data from Changes

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.

2. Uploading to a new sheet

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:

• Date / Time of each entry
• Text of each entry
• Rating ******of each entry: 🙂 = 1 😐 = 0 😞 = -1
• Starred ******- whether the entry was marked as a “high point” or “low point”
• Score - if a rating was starred its score is doubled, otherwise it’s the same as its rating
• Mood Trend - a smoothed trend line showing how your happiness has changed over time
• Place Names and Locations show where you were
• Changes shows the text of the life changes you tracked within the app, e.g. “Quit drinking”
• Hashtags - there is a column for each hashtag you’ve added, filled in with the rating for that hashtag wherever it was used, e.g. -1, 0 or 1

Rename this sheet to Happiness before you continue.

Runkeeper

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.

Myzone

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…

“Activity Report” icon

Date Button

Switch to “Date Range”
Navigate back and click the earliest date you want, then the latest date.

Finally, click the “Download CSV” icon

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 MYZONE.cleaned.csv.

Upload this resulting file using Insert new sheet(s) and you should see something like this:

Combining the Data Sets

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:

• Date
• Source
• Calories
• Duration

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)

Fill down Runkeeper dates Different formula for Myzone dates

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(IFERROR(REGEXEXTRACT(J2,"(\d+)\:\d+\:\d+"), 0),LEFT(RIGHT(J2,5),2),RIGHT(J2,2))

The **TIME** formula takes three arguments: TIME(Hours,Minutes,Seconds)
So, working backwards:

• Seconds is always the last two digits: RIGHT(J2,2)
• Minutes is two digits from the 5th digit from the left: LEFT(RIGHT,J2,5),2)
• Hours looks for a value where there are three sets of digits separated by colons ("**(\d+)**\:\d+\:\d+") and takes the first digits (in brackets, in bold) using REGEXEXTRACT. If no match is found by REGEXEXTRACT, we make that a 0 using 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
=TIME(0,Y69,0) where 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).

Good News, We’ve Got Our Data

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 function.

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 A, B, C and D, corresponding to Date(of the workout), Source (Runkeeper or MYZONE), Calories and Duration.

We are looking for the nearest workout so we say A < [DATE] and we only want 1 result so we add LIMIT 1.

I found it easiest to create a separate column to build this query, which you can see here:

The formula in column B is =CONCATENATE("select A,B,C,D where A < ", $A2, " LIMIT 1")

In Column C we have the formula =query(Workouts!A$2:D$457,B2,-1)
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.

IMPORTANT
This 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.

• Time Since Last Workout =A2-C2 where A2 is the entry date time and C2 is the last workout date time.
• During Workout? =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 + F2 where C2 is the workout time and F2 is the workout duration.

Breaking mood into columns

Let’s do our trick of creating an Unhappy, Neutral and Happy column for each entry to look like this:

• Unhappy - If the Rating is less than 0, use the Time value, otherwise use an empty string ("")
• Neutral - If the Rating equals 0, use the Time value, otherwise use an empty string ("")
• Happy - If the Rating is greater than 0, use the Time value, otherwise use an empty string ("")

Copying everything we care about somewhere more stable

Copy and paste everything we want to chart to somewhere more stable, using Paste Values Only.

Time for charts!

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.

Try this for yourself!

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.