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/A values? 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 IFERROR 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-2: Are the countries that with the largest change in rank the same as the countries with the largest change in score?

False
Surprisingly yes
True
No, the countries are different

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?

Challenge

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.
1. 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.
2. How can we add a column (or a few columns) to our happiness spreadsheet from this spreadsheet? We can use VLOOKUP.
3. Once we have the continent name added to the spreadsheet, can we find the average happiness score for each continent?

Lesson Feedback

1. Comfort Zone
2. Learning Zone
3. Panic Zone

1. Very little time
2. A reasonable amount of time
3. More time than is reasonable

1. Don't seem worth learning
2. May be worth learning
3. Are definitely worth learning

1. Definitely within reach
2. Within reach if I try my hardest
3. Out of reach no matter how hard I try

You have attempted of activities on this page