26th May, 2020
Michael Forrest
My Happiness vs The Pandemic. In Spreadsheets.
It's been strange trying to promote a business centred around happiness in the midst of a global pandemic. Every time I look at any social media platform it feels like everybody is yelling about how terrible things are, or who is responsible for which injustice. Meanwhile, in the real world, my life has been largely the same as before. Obviously the pandemic has had an impact, but I had been saving up my happiness data until I had enough to make a judgement about all this. Today I'm ready to share what I learned, offering you my techniques in the process.
How I Remember the Pandemic, So Far
The pandemic has been a strange, guilty experience for me. I spend most of my time in a studio on my own so my day-to-day life hasn’t been affected much. But it has had a positive effect on my medium-term future. Suddenly loads of people got into live-streaming music, something I’ve been doing for years. I have a small camera utility app in the App Store and since the pandemic, sales have sky-rocketed. This means the business I started last year is starting to feel viable; I’m living my dream by working for myself. I’ve also had more conversations with friends and family since the lockdown, so I’m feeling less isolated than I was before.
I feel a bit guilty that I’ve profited from something like this but I feel like overall, weirdly, it’s been a positive experience so far.
That’s how I remember it anyway.
But my human brain is very squishy and unreliable. All of our brains are a bit squishy and unreliable. So I suspect there is more to this story. Luckily, I have data to investigate.
Analysing My Happiness Data
I have been tracking my mood every day throughout this pandemic. I use an app that I released myself called Changes. It lets me write a little diary entry and rate my mood and add hashtags.
I’ll analyse this data and see what actually happened. I’ll be using Google Sheets to help you try the same with your data in future.
1. Setting up the Spreadsheet
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.
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.
Uploading to a new sheet
Now import this data into a spreadsheet. 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.
Cleaning up the data
I’m going to compare the 65 days before the Lockdown to the 65 days since. I’ll start my deleting any data from before that period.
I’ll add a new column called “Period” and fill this with BEFORE and AFTER.
Create a filter and you should have something like this:
2. Following the Overall Mood Trend
Let’s start with a mood trend chart. Select the Date and Mood Trend columns and Insert → Chart.
We can mark the “Coronavirus” change on this chart by first adding a column named “Changes” and adding a number in this column wherever a Change appears in our data. Mood Trend is between 0 and 1 so let’s mark the change at 0.8.
Edit the chart, Add Series, select the new Changes column.
Add a label to this series with the Changes column that came from the CSV.
Customize the Vertical Axis with a Maximum value of 1.0 so we can see our label.
You should now have something like this:
Moving to a new sheet and fiddling with the styling, here’s how my data looks:
My mood had been on the up until the pandemic started to affect me directly, at which point things became a little more complicated. Let’s do a couple more charts to see what factors were involved.
3. Comparing Hashtags Before and After Lockdown
I want to count the number of different ratings for different moods before and after lockdown. To do this I’ll use DCOUNT.
How DCOUNT works
This function took my a while to get my head around but I think I can explain it clearly enough.
DCOUNT takes a query defined as table of criteria with one row; something that looks like this:
And here is DCOUNT in action, creating that query table inline, instead of in the spreadsheet.
The first argument is our “database”. This will be the full range of our happiness sheet. I’m using happiness!$A:$CW. The $ characters specify “absolute” ranges so that if we copy this formula to another cell, it won’t adjust any values we don’t want it to.
This sets up a database of values that can be accessed via the text of the column headings, so instead of saying Column N we can reference the #Anxiety column (aka field) using that label.
Each hashtag has its own column in our database, sparsely populated with ratings of -1, 0 or 1 for Unhappy, Neutral or Happy wherever it is added to an entry.
We want to look up the appropriate field for each of our hashtag rows. We achieve this by dynamically selecting the second DCOUNT argument to be whatever’s in our local hashtag column,
In our data here, $A2 translates to the #Anxious field, and $A18 will reference the #coffee field.
Finally we need to define our criteria. Again, DCOUNT expects criteria as a rows and columns of values in the following format:
We have a column for each hashtag, and then the rating we want to filter by. So this example would find every occurrence of -1 in the #coffee column of our Happiness sheet.
We can reproduce this little query table with the syntax {"#coffee","Period";-1,"BEFORE"}. This is in the format {[column headings];[row values]}.
That lets us dynamically generate our query based on the contents of our local Hashtag and Period columns like so:
Our criteria becomes {$A2,"Period";-1,$B2} where $A2 gives us the hashtag and $B2 gives us BEFORE or AFTER. We are looking for Unhappy ratings so we use -1 for the rating.
The only difference between our three columns becomes this rating value.
- Unhappy
=DCOUNT(happiness!$A:$CW,$A2,{$A2,"Period";**-1**,$B2}) - Neutral
=DCOUNT(happiness!$A:$CW,$A2,{$A2,"Period";**0**,$B2}) - Happy
=DCOUNT(happiness!$A:$CW,$A2,{$A2,"Period";**1**,$B2})
I’m going to add a Total column to help filter out anything where there were no ratings for our time range.
Creating charts
Select the Hashtag and Unhappy Before, Neutral Before and Happy Before columns and use Insert Chart. Select Radar Chart. We can filter out rows where total is 0 or 1 to get a clearer layout, and tinker with the series and axis formatting to get something like this.
Duplicate this chart, add ranges for the After columns and change the series to get two charts that we can compare by switching between tabs.
4. Comparing Diary Entries Before and After Lockdown
It’s easy to have a look at the diary entries that pulled our mood down. Filtering entries by Rating = -1 allows scanning through.
You can filter data by positive and negative entries, before and after, to see what was going on. You could also focus in on particular hashtags by filtering on those columns.
Note that my Changes app will hide entries and hashtags marked “Sensitive” within the app. This is a privacy feature to make it easier to have conversations about our data without worrying people will see our darkest deepest thoughts. These entries will be blurred out in your timeline by default but can be unlocked at any time using Face ID, an exported accordingly.
Comparing my actual experience to my “remembered experience”
What did I remember correctly?
- Overall, my mood has been relatively positive.
- I was definitely reporting a lot of elated moods as a result of my newfound freedom.
- I remembered the new social interactions that came about.
What did I forget?
- I’d forgotten the whole period of uncertainty at the start. It was a bumpy road as I had to adapt to new circumstances.
- I didn’t know if I was going to have to keep paying for services I couldn’t use.
- I didn’t like the feeling that I would get into trouble by continuing to come to my studio even though there’s nobody really here.
- I didn’t like the busybody behaviour of another person in my building.
- I forgot that I’d been waking up at all hours checking my app sales and feeling weird and stressed in weird new says.
What does this mean for you?
It’s interesting how much we worry about change when we’re so good at adapting to new situations. In this case, I think my brain did me a favour by forgetting most of the painful and annoying bits. But it’s good to see how easy it is to forget the unpleasant parts of a big change, and only really remember the new equilibrium.
Want to know more about mood tracking?
If you found this interesting, I have an eBook called Tracking Happiness and Why It Works free by signing up for my newsletter below.
Bye, see you next time!