6th May, 2020
Michael Forrest

Happiness vs Sleep

Today let’s investigate how sleep affects your mood.

I track my sleep using my Withings Steel HR Smart Watch. I put this watch on every night when I get into bed in a slightly absurd ritual that allows me to wear my Apple Watch in the day time…

I track my happiness every day using an app I made myself called Changes which is available on the App Store.

1. Export data from Withings

To export your data from the Withings app go to your profile → Download your data → Start my archive.

After a few minutes you’ll receive an email with a link to download your data as a zip file. Inside this zip file you’ll find sleep.csv.

2. Export data from Changes app

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.

3. Upload the data into new spreadsheet

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.

Now repeat the process to upload your sleep.csv ***file. Be sure to select *Insert new sheet(s) instead of Create new spreadsheet or Replace current sheet this time.

Each row in this sheet represents one night

• From / To - Sleep to wake up time
• light / deep / rem / awake - Total time in seconds in each phase (my rem data is always zero - maybe you need a different device?)
• wake up - number of times you woke up in the night

• Duration to sleep / wake - I think it detects when you get in bed
  

• Snoring / Snoring episodes - I don’t have any data for this

• Average heart rate / Heart rate (min) / Heart rate (max) - Heart rate stuff. Not sure how this will factor into our exploration

Before I go any further I’ll clean up my data. I got the watch in July 2018 so I’ll delete any happiness data from before that. I’ll also delete all the columns I won’t be using.

Also let’s make sense of the sleep from and to dates. They are in a format that Sheets doesn’t understand. Select the to column and launch Find and replace.

1. Search for all occurrences of T and replace with a space.
2. Switch to Search using regular expressions and use this formula: \+\d+:\d+, replacing with nothing, to strip the time zone. Replace all.
3. Then you can change the format to Date / Time.

4. Consolidating all our data

Our first job is to cross-reference all the data in our sleep sheet with our happiness sheet.

We can find out how much sleep we got the night before each entry with some VLOOKUP-fu, but first we need to separate out the relevant dates and times into separate columns.

We want to match each entry date in happiness to the to date - the date of the morning we woke up.

Here’s a trick to make clean date columns

First we’ll create a Wake Up Date column in our sleep spreadsheet.

1. Format the to column as a Date
2. Add a column to the right called Wake Up Date with a formula =TO_TEXT(B2) and fill that down.
3. Select the new column, copy and then Paste values only in the same place.

Format this new column as a Date and we can continue.

In the happiness tab, do the same with the Date column.

This gives us dates we can match, but we also need values to look up

In the sleep sheet, add new columns for Hours Slept and Wake Up Time.

Hours Slept will add the together the light + deep columns (H and I in this case) and convert from seconds to hours.
=(H2+I2) / 60 / 60

Wake Up Time is created the same way as Wake Up Date - format the to column as a time, create a new =TO_TEXT(A2) column and then copy and Paste values only in-place.

Here’s what the sleep tab should contain when we’re done.

Now we can VLOOKUP

A VLOOKUP needs a search_key, a range and an index.

• search_key in our case connects Entry Date to Wake Up Date. We want to look up the value of our Just Date column in sleep→Wake Up Date.
• range should start with the value of our search_key but span additional columns containing any values we might want to reference. In our sleep sheet Wake Up Date column is column D and the values we want to look up are to its right.
• index tells the VLOOKUP how many columns to jump to the right to find the result we want.

With the selection you see above:
Wake Up Time = index 2
Hours Slept = index 3

We take these into our happiness sheet and continue.

Creating individual series for Happy, Neutral and Unhappy entries

If we want a chart showing our different moods in different colours, we’ll need to break out our Rating into three separate columns with containing a value that will make sense on our chart. In this case we want the time of day that each entry occurred.

Create a new Time column in happiness and do the whole TO_TEXT trick to create values (format date as Time, use this as a TO_TEXT source in a new column then Paste values only in-place).

Here’s how the Unhappy column is set up:

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 ("")

One final bit of housekeeping

I find Google Sheets behaves a bit better if I take any transformed data and Paste values only into a new sheet before I start using filters.

Here’s how my new sheet looks once I’ve plucked the columns I find most interesting and turned on Filter mode.

5. Let’s plot this data!

We’ll start by plotting our happiness against Hours Slept. Select the columns we need in the chart.

Select Scatter chart
Set the X-Axis to Hours Slept
Add a Series for each of Unhappy, Neutral and Happy
Pick appropriate colours for each of the series under Customize → Series.

Now you should see something like this.

There doesn’t seem to be much of a pattern in my data here…

Let’s try Wake Up Time.

In this chart we can see what time I usually wake up but not much else!

Let’s try imposing a bit more structure on our data and group these values into bands with some aggregated charts.

Aggregating Hours Slept
Let’s group Hours Slept to the nearest hour with a new column that simply ROUNDs the Hours Slept value. We’ll use this new column as the X-axis of a 100% stacked column chart, adding series in the same way as before.

Here’s how that looks (don’t forget to sort the X-axis column!)

This seems to show that with an average of 4 hours sleep I don’t do so well… But otherwise it’s a pretty even spread!

Aggregated Wake Up Time
We can segment our Wake Up Time into groups of 15 minutes with the formula
=TIME(HOUR(H2),ROUND(MINUTE(H2)/15)*15,0) (this extracts the hour and minute values from our time then creates a new time with the minutes rounded to the nearest 15 minutes).

Create a new 100% stacked column chart with this new X-axis column.

Again, this seems pretty inconclusive.

6. Drawing conclusions

It seems to me, counter to my intuition, that the amount of sleep I get doesn’t consistently affect my mood one way or another.

Maybe this is a good thing! Next time I’m having trouble getting to sleep, I can remember that a bad night’s sleep isn’t the end of the world.

7. Did you try this?

Let me know! It will take a while to collect enough data but it’s amazing what you’ll discover when you do. 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.