6th May, 2020 Michael Forrest
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.
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.
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.
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
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.
Tand replace with a space.
\+\d+:\d+, replacing with nothing, to strip the time zone. Replace all.
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.
First we’ll create a Wake Up Date column in our sleep spreadsheet.
=TO_TEXT(B2)and fill that down.
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 (
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.
VLOOKUP needs a search_key, a range and an index.
Dand the values we want to look up are to its right.
VLOOKUPhow 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.
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 (
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.
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.
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.
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.
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.