2.4. Case Study 1: Comparing Happiness Data across Years¶
We have two files of happiness data, one for 2017 which you have been using, and another for 2012, so you can make some comparisons across a span of five years.
Start a new workbook, and get each of the csv files for the happiness data loaded into a separate sheet.
Now, let’s create a table on a new sheet that shows the happiness rank for each country for each year. You’ll have 3 columns: country name, 2012 rank, and 2017 rank. (Hint: Use
VLOOKUP.) But wait! What is the deal with these
#N/Avalues? Shouldn’t the happiness report have the same countries for every year? Apparently, just like regular people, not all countries participated in the survey every year. Let’s press on and hope for the best for the moment.
Now, create a column where you calculate the change between the 2017 rank and the 2012 rank. Then create a new cell where you find the maximum value of this new column.
Oh dear, hoping for the best is rarely a good strategy. You will notice that the
result of looking for the maximum value in a column that contains one of these
#N/A values results in the function returning
#N/A as well. It appears
we will have to find a better strategy.
The right way to handle this problem is to use the
IFERROR function. This is
one area where Google Sheets and Excel are slightly different. The
function takes two parameters: a function or calculation, and a value to insert
in the case of an error. In our case, we want to adjust our subtraction so that
if there is an error, we will set the difference to be 0. We’ll change the
calculation to look something like
=IFERROR(C2-P2, 0). Now, you will see
that wherever we had an
#N/A value before, we now have a 0. You will also
see that we get interesting values for maximum and minimum.
3. Next, let’s find the biggest changes in the happiness scores from 2012 to 2017.
Q-4: Give an explanation for why you think the two are different. Outline an experiment or calculation that you can do with a spreadsheet to back up your answer.
For the five countries with the largest changes in ranking between 2012 and 2017, what are the factors that changed the most? For this part, you can do this by making comparisons between sheets rather than creating a huge number of new columns on this summary sheet.
Q-5: What did you learn in the previous investigation? What were the factors that changed the most?
The choropleth gave us some insight into how happiness may be related to the continent. It was pretty clear that African nations were less happy than many others. Let’s see if we can quantify that.
First, we need to find a file that helps us map from country to continent. Let’s add this to our file as a new worksheet.
How can we add a column (or a few columns) to our happiness spreadsheet from this spreadsheet? We can use
Once we have the continent name added to the spreadsheet, can we find the average happiness score for each continent?
During this lesson I was primarily in my...
- 1. Comfort Zone
- 2. Learning Zone
- 3. Panic Zone
Completing this lesson took...
- 1. Very little time
- 2. A reasonable amount of time
- 3. More time than is reasonable
Based on my own interests and needs, the things taught in this lesson...
- 1. Don't seem worth learning
- 2. May be worth learning
- 3. Are definitely worth learning
For me to master the things taught in this lesson feels...
- 1. Definitely within reach
- 2. Within reach if I try my hardest
- 3. Out of reach no matter how hard I try