Empirical Project 8 Working in Excel

Part 8.1 Cleaning and summarizing the data

So far we have been working with data that was formatted correctly. However, sometimes the datasets will be messier and we will need to ‘clean’ the data before starting any analysis. In the data we will use, the European Values Study (EVS) data has been converted to Excel from another program, so there are some entries that were not converted correctly and some variables that need to be recoded (for example, replacing words with numbers, or replacing one number with another).

Download the EVS data and documentation:

  1. The data spreadsheet contains an incomplete Data dictionary and four tabs with the data collected from each wave of the survey. The variable names currently do not tell us what the variable is, so we need to relabel them to avoid confusion.

Throughout this project we will refer to the variables using their original names, so the Data dictionary tab will come in handy.

  1. Now we will take a closer look at how some of the variables were measured.
  1. We will now check and clean the dataset so it is ready to use. Make the following changes to all relevant data tabs:

Excel walk-through 8.1 Cleaning data and splitting variables

Follow the walk-through in the CORE video, or in Figure 8.1, to find out how to clean data and split variables in Excel.

How to clean data using find and replace, and split variables using Excel’s LEFT and RIGHT functions.

Figure 8.1 How to clean data using find and replace, and split variables using Excel’s LEFT and RIGHT functions.

The data

This is what the data looks like. Currently some cells have entries ‘.a’ where the data is missing. We will find and replace the variables A009 to X047D (not pictured here).

Figure 8.1a This is what the data looks like. Currently some cells have entries ‘.a’ where the data is missing. We will find and replace the variables A009 to X047D (not pictured here).

Find and replace specific cell entries

‘Find and Replace’ in Excel is similar to this feature in Microsoft Word, except that it searches for text in individual cells rather than in lines of text. You can adapt steps 1-3 to change specific values of cells in a column, for example replacing a particular word with a particular number.

Figure 8.1b ‘Find and Replace’ in Excel is similar to this feature in Microsoft Word, except that it searches for text in individual cells rather than in lines of text. You can adapt steps 1-3 to change specific values of cells in a column, for example replacing a particular word with a particular number.

Extract a specific part of a string of text

Excel’s LEFT and RIGHT functions can help you extract parts of a long string of text, which is useful if you want to shorten variables or only keep particular information in a cell. Here, we are going to save variable X025A as two variables: ‘Education_1’ will contain the numbers before the colon, and ‘Education_2’ will contain the words after the colon.

Figure 8.1c Excel’s LEFT and RIGHT functions can help you extract parts of a long string of text, which is useful if you want to shorten variables or only keep particular information in a cell. Here, we are going to save variable X025A as two variables: ‘Education_1’ will contain the numbers before the colon, and ‘Education_2’ will contain the words after the colon.

Extract a specific part of a string of text

The RIGHT function counts the specified number of characters starting from the right end of the string of text. Since words have different lengths, we use LEN() to calculate the length of the string, then subtract the colon’s position in the string (using the FIND function). For example, if the string is 10 characters long, and the colon is the third character, we want Excel to extract characters 4 to 10 (the first 6 characters from the right).

Figure 8.1d The RIGHT function counts the specified number of characters starting from the right end of the string of text. Since words have different lengths, we use LEN() to calculate the length of the string, then subtract the colon’s position in the string (using the FIND function). For example, if the string is 10 characters long, and the colon is the third character, we want Excel to extract characters 4 to 10 (the first 6 characters from the right).

Although the paper we are following only considered individuals aged 25–80 who were not students, we will retain these observations in our analysis. However, we need to remove any observations that have missing values for A170, X028, or any of the other variables except X047D.

  1. Remove all rows with missing values from your spreadsheet. Excel walk-through 8.2 gives guidance on how to do this. (Remember that not all questions were asked in each wave, so refer to the data documentation PDF to check whether there should be missing values in an entire column.) (Note: Excel may take some time to process the commands.)

Excel walk-through 8.2 Dropping observations that satisfy particular conditions

How to drop observations that satisfy particular conditions using ‘filter’, ‘select’ and ‘delete’.

Figure 8.2 How to drop observations that satisfy particular conditions using ‘filter’, ‘select’ and ‘delete’.

The data.

This is what the data looks like. In this example, we will remove any rows with the variable A170 recorded as missing (blank). First, we will filter the data so that only rows with blank cells in this column are showing.

Figure 8.2a This is what the data looks like. In this example, we will remove any rows with the variable A170 recorded as missing (blank). First, we will filter the data so that only rows with blank cells in this column are showing.

Filter the rows of interest

After step 3, you will see all the data that needs to be deleted.

Figure 8.2b After step 3, you will see all the data that needs to be deleted.

Delete these rows from the dataset

Excel will remove all the selected rows from the dataset. If there are many rows to delete, this command may take some time to process.

Figure 8.2c Excel will remove all the selected rows from the dataset. If there are many rows to delete, this command may take some time to process.

Clear the filter to see the rest of the data

After removing the specified rows, clear the filter to see the rest of your data. You will see that the number of rows has decreased.

Figure 8.2d After removing the specified rows, clear the filter to see the rest of your data. You will see that the number of rows has decreased.

The modified data

This is what the data looks like after removing rows with missing entries in A170. You can adapt steps 1–7 to remove rows that satisfy other conditions, such as containing a particular value or range of values.

Figure 8.2e This is what the data looks like after removing rows with missing entries in A170. You can adapt steps 1–7 to remove rows that satisfy other conditions, such as containing a particular value or range of values.

  1. We will now create two variables, work ethic and relative income, which we will use in our comparison of wellbeing.

Excel walk-through 8.3 Calculating percentiles from actual values

How to calculate percentiles from actual values.

Figure 8.3 How to calculate percentiles from actual values.

The data

This is what the data looks like. In this example, we will calculate the percentile of X047D (log income), according to the country (S003). You will need to use two new columns (R and S in this case).

Figure 8.3a This is what the data looks like. In this example, we will calculate the percentile of X047D (log income), according to the country (S003). You will need to use two new columns (R and S in this case).

Calculate the rank of each observation

The intuition behind our rank calculation is that an observation’s rank is the number of observations larger than it, plus 1. (For example, if there were only 2 numbers, the smaller number should be ranked 2 and the larger number ranked 1). By default, Excel will rank observations from largest to smallest (higher numbers will have lower ranks). We need this setting to calculate percentiles. In this example, we need to rank observations only within the country it belongs to, not the entire dataset, so we use this as a condition in COUNTIFS.

Figure 8.3b The intuition behind our rank calculation is that an observation’s rank is the number of observations larger than it, plus 1. (For example, if there were only 2 numbers, the smaller number should be ranked 2 and the larger number ranked 1). By default, Excel will rank observations from largest to smallest (higher numbers will have lower ranks). We need this setting to calculate percentiles. In this example, we need to rank observations only within the country it belongs to, not the entire dataset, so we use this as a condition in COUNTIFS.

Use the calculated rank to calculate the percentile.

The intuition behind the percentile calculation is that an observation’s percentile is the percentage of observations smaller than it (the +1 in the formula is needed to exclude the observation itself). The 1- at the start of the formula is because we ranked observations from largest to smallest (so we need to ‘reverse’ all the numbers). Here we have multiplied by 100 so the numbers correspond to actual percentiles (e.g. 92 percentile). You can see that higher incomes have lower ranks and higher percentiles, indicating that we have done this correctly. This example was based on observations with non-missing income only (not the fully-cleaned dataset), so the numbers you get will be slightly different.

Figure 8.3c The intuition behind the percentile calculation is that an observation’s percentile is the percentage of observations smaller than it (the +1 in the formula is needed to exclude the observation itself). The 1- at the start of the formula is because we ranked observations from largest to smallest (so we need to ‘reverse’ all the numbers). Here we have multiplied by 100 so the numbers correspond to actual percentiles (e.g. 92 percentile). You can see that higher incomes have lower ranks and higher percentiles, indicating that we have done this correctly. This example was based on observations with non-missing income only (not the fully-cleaned dataset), so the numbers you get will be slightly different.

  1. Now we have all the variables we need in the format we need. We will make some tables to summarize the data. Using the data for Wave 4:
Male Female
Mean Standard deviation Mean Standard deviation
Wellbeing
Self-reported health
Work ethic
Age
Education
Number of children

Summary statistics by gender, European Values Study.

Figure 8.4 Summary statistics by gender, European Values Study.

Part 8.2 Visualizing the data

Note

You will need to have done Questions 1–5 in Part 8.1 before doing this part.

We will now create some summary charts of the self-reported measures (work ethic and wellbeing), starting with column charts to show the distributions of values. Along with employment status, these are the main variables of interest, so it is important to look at them carefully before doing further data analysis.

The distribution of work ethic and wellbeing may vary across countries but may also change over time within a country, especially since the surveys are conducted around once a decade. To compare distributions for a particular country over time, we have to use the same horizontal axis, so we will first need to make a frequency table for each distribution of interest. Also, since the number of people surveyed in each wave may differ, we will use percentages instead of frequencies as the vertical axis variable.

  1. Using the data from Wave 3 and Wave 4 only, for three countries of your choice:
Range of work ethic score Frequency Percentage of individuals (%)
1.00    
1.20    
   
4.80    
5.00    

Frequency table for self-reported work ethic.

Figure 8.5 Frequency table for self-reported work ethic.

  1. We will use line charts to make a similar comparison for wellbeing over time. Using data for countries that are present in Waves 1 to 4:
correlation coefficient
A numerical measure of how closely associated two variables are and whether they tend to take similar or dissimilar values, ranging from a value of 1, indicating that the variables take similar values (positively correlated), to −1, indicating that the variables take dissimilar variables (negative or inverse correlation). A value of 1 or −1 indicates that knowing the value of one of the variables would allow you to perfectly predict the value of the other. A value of 0 indicates that knowing one of the variables provides no information about the value of the other.

After describing patterns in our main variables over time, we will use correlation coefficients and scatterplots to look at the relationship between these variables and the other variables in our dataset.

  1. Using the Wave 4 data:
Variable Wellbeing Work ethic
Age
Education
Employment status (= 1 if full-time employed, = 0 if unemployed)
Gender (= 0 if male, = 1 if female)
Self-reported health
Income
Number of children
Relative income
Wellbeing 1
Work ethic 1

Correlation between wellbeing, work ethic and other variables, Wave 4.

Figure 8.6 Correlation between wellbeing, work ethic and other variables, Wave 4.

Next, we will look at the relationship between employment status and wellbeing and investigate the paper’s hypothesis that this relationship varies with the average work ethic in a country.

  1. Using the data from Wave 4, carry out the following:

So far we have described the data using tables and charts, but have not made any statements about whether what we observe is likely to be due to chance or not. In the next part, we will assess the statistical significance of the relationship between employment status and wellbeing.

Part 8.3 Confidence intervals for difference in the mean

Note

You will need to have done Questions 1–5 in Part 8.1 before doing this part.

The aim of this project was to look at the empirical relationship between employment and wellbeing. In other words, is the mean wellbeing of employed people (statistically) significantly different from the mean wellbeing of unemployed people (as we might expect from economic theory)?

When we calculate differences between groups, we might also want to know if these differences are statistically significant. In Part 6.2 of Empirical Project 6, we constructed confidence intervals for means, and used a rule of thumb to assess statistical significance from confidence intervals displayed on a chart. Now we will learn how to construct confidence intervals for the difference in two means, which allows us to assess statistical significance directly instead of using the rule of thumb.

Remember that the width of a confidence interval depends on the standard deviation and number of observations. (Read Part 6.2 of Empirical Project 6 to understand why.) When making a confidence interval for a sample mean (such as the mean wellbeing of the unemployed), we use the standard deviation and number of observations in that sample (unemployed people).

When we look at the difference in means (such as wellbeing of employed minus unemployed), we are using data from two groups (the unemployed and the employed) to make one confidence interval, so the number of observations is the sum of observations across both groups. To calculate the standard deviation for the difference in means, we use the standard deviations (SD) of each group:

This formula requires the two groups of data to be independent, meaning that the data from one group is not related, paired, or matched with data from the other group. This assumption is reasonable for the wellbeing data we are using. However, if the two groups of data are not independent, for example if the same people generated both groups of data (as in the public goods experiments of Empirical Project 2), then we cannot use this formula.

Once we have the new standard deviation and number of observations, we can calculate the width of the confidence interval (distance from the mean to one end of the interval) as before. The difference in means is statistically significant if the confidence interval for the difference in means does not contain 0.

  1. We will apply this method to make confidence intervals for differences in wellbeing. Choose three countries: one with an average work ethic in the top third of scores, one in the middle third, and one in the lower third. (See Excel walk-through 6.4 for help on calculating confidence intervals and adding them to a chart.)
  1. The method we used to compare wellbeing relied on making comparisons between people with different employment statuses, but a person’s employment status is not entirely random. We cannot therefore make causal statements such as ‘being unemployed causes wellbeing to decrease’. Describe how we could use the following methods to assess better the effect of being unemployed on wellbeing, and make some statements about causality: