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.

https://www.youtube.com/watch?v=nE0mBQvCMH4

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:

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 “Activity Report” icon

Date Button Date Button

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

Finally, click the “Download CSV” icon 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:

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 Fill down Runkeeper dates Different  formula for Myzone 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:

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.

Breaking mood into columns

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

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.

Get your free stuff

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.

Find out more

*
indicates required