Cleaning the data

When you load in your data set, you will notice a few things about the data set that suggest we would benefit from data cleaning. Data cleaning is a broad term that relates to getting a data set ready to work with. This can include creating new features, handling missing data, merging data frames, changing the names of columns, formatting features, and so on. We will practice a few common data cleaning steps today, and we will learn more as we move through the course.

Renaming a data frame and storing things

Our first data cleaning step has to do with the name of the file. The name of the data set (data frame) we have just loaded is really long: DataForFigureWHR2023. You probably don’t want to type that all through the lab today. Because of this, our first data cleaning step is to rename our data set.

We are going to change the name of our data set to Happiness. Doing this in R requires (1) creating a new copy of the data set under the new name that you want and (2) removing the old data set. Let’s see how to do that.

# Step 1: 
# Create a new copy of the data set
# under the new code name we want
Happiness <- PUT THE CURRENT NAME OF YOUR DATA SET HERE
Happiness <- data.frame(Happiness)

# Step 2: 
# Remove the original data set
rm(PUT THE ORIGINAL NAME OF YOUR DATA SET HERE)

Your Happiness data set should have \(n = 137\) rows and 19 columns.

The code for Step 1 is particularly important. In this code, we are introduced to the <- operator. I think of this as a storage operator. We take whatever stuff is to the right of the <- (<- stuff) and we store it under the name on the left of the operator (whereWePutIt <- stuff)

Removing Columns

The next step in our data cleaning process is to remove columns from our data set that we don’t need. It turns out that we only need some of the columns in the data set for our analysis today, and it can be easier to work with data if we remove columns we don’t need.

• Columns we don’t need: 3 - 5, 12 - 19

Let’s practice by removing the columns 3, 4, and 5.

When we work with data frames in R, we use the following notation to access rows and columns:

dataframe[rows, columns]

So, if I want to print out the first row in the third column, I use:

Happiness[1,3]

If we leave either the rows or the columns part of the notation blank, this just means all. For instance, the following code tells R to print out the entire first row (so information on all the columns for this row).

Happiness[ 1 , ]

Similarly, this code prints out the first 3 rows (1, 2, and 3), with information on all the columns for those rows.

Happiness[ c(1:3) , ]

The c part of the code tells R we want a collection of things. In this case. it tells R we want to print out more than one row. The same notation would work for columns, just on the other side of the ,.

We want to remove columns 3, 4, and 5. To do this, we need to run this code. NOTE: YOU NEED TO RUN THIS CODE EXACTY ONCE!

# Remove columns 3 -5 
Happiness <- Happiness[,-c(3:5)]

Look, there’s the <- again!! This symbol and the idea of storing things will be very helpful as we move through our course.

Changing column names

At this point, we should have a data frame called Happiness with the following column names:

• “Country name”
  
• “Ladder score”
  
• “Logged GDP per capita”
  
• “Social support”
  
• “Healthy life expectancy”
  
• “Freedom to make life choices”
• “Generosity”
  
• “Perceptions of corruption”

Like the name of the data set, these column names are tricky to work with. In R, we want columns names with no spaces and no special characters (^, &, etc.). This means that before we proceed, we want to change the names of our columns.

There are a few ways to do this, but we will use the function colnames today. This function prints out the names of the columns in a data set:

# Print out the column names in the data set
colnames(Happiness)

However, this function can also be used to change the names of columns. To do so, you just need to adapt the code below by filling in the name you want for each column in place of Column1, Column2, etc.

colnames(Happiness) <- c("Column1", "Column2", "Column3", "Column4", "Column5", "Column6","Column7","Column8")

Why didn’t you do that for us???

Wow, that was quite a few steps!!! Why didn’t I format that for you??

Well, in this course our goal is to help you get prepared to use statistics in a career, in an internship, in research, etc. In the real world, data is messy, and data cleaning is an absolutely essential skill. Because of this, we will practice data cleaning in this course so you are ready to perform similar cleaning tasks on real data you encounter.

Techinque 1: Unconditional Mean

The first technique for imputation we will try is unconditional mean imputation (UMI). As a reminder, this means that we find the mean of happiness score in our happiness25 data set, and we use that mean to fill in all the missing values for happiness score.

Let’s start off with finding the mean, which is the value we are going use for all our imputations. We learned the code for how to do this in class, and the skeleton is provided below.

# Find the mean of happiness score
# in the happiness25 data set
mean(      , na.rm = TRUE)

Now that we have the mean, we want to fill in this value for all the missing values in happiness score. The first step is to determine which rows in the happiness variable have missing data. In R, a code called is.na checks each row in a column and responds TRUE if the row has missing data and FALSE otherwise.

is.na(happiness25$happiness)

Ideally, we do not want to go through this list and find all the TRUE results. Luckily, R can do this for us using the which command. This asks R to look at a vector and find which entries are equal to a certain value.

which( is.na( happiness25$happiness) == TRUE)

We’ve now learned all the code we need for UMI! A skeleton code is provided below, which means there are some pieces for you to fill in.

# Find the rows missing happiness score in the happiness25 data set
whichMissing <- which(is.na(    ) == TRUE)

# Create a data set called happiness_UMI
happinessUMI <- happiness25

# Fill in the missing values in with the mean
happinessUMI[     , "happiness"] <- 

Now we want to train our LSLR model using these imputations and compare them to what we got using the data set with no missing data.

As a reminder, the confidence interval from the LSLR model trained on no missing data (the full model) is:

Assessing Imputation Accuracy

The final step with our UMI imputations is to assess the quality of our imputations. Because we created the missing data ourselves, we know what the missing values are supposed to be. This means we can check to see how accurate our imputations are! This doesn’t typically happen with missing data, and we’ll talk about how we assess accuracy in those situations next week.

Let \(y_i\) be the true value of happiness for row \(i\) in the data set. Let \(y_i^{*}\) be the value we imputed for happiness score for row \(i\) in the data set. We want to see how close \(y_i\) is to \(y_i^{*}\).

Now, we have \(n_{miss}= 34\) rows of missing data in our happiness25 data set. This means that ideally, we would like some metric (numeric tool) that allows us to summarize how well we are imputing the values for all of these missing values. A common metric we use for this is called the root mean squared error (RMSE).

\[RMSE = \sqrt{\frac{1}{n_{miss}} \sum_{i=1}^{n_{miss}} (y_i - y_i^{*})^2}\] Hmm, this looks like a lot. Let’s break it down.

Part 1

\[y_i - y_i^*\] This is the distance between each true value of happiness \(y_i\) and the imputed value \(y_i^{*}\). In other words, it’s the residual for row \(i\)!

Part 2

\[(y_i - y_i^*)^2\]

We square the values to get rid of any negative numbers; we don’t want a large negative value and a large positive value to cancel each other out, as each would represent a large difference between the truth and the imputation.

Part 3

\[\sum_{i=1}^{n_{miss}} (y_i - y_i^{*})^2\] Hey, this should look familiar! It looks just like the residual sum of squares (RSS). The only difference is that we only sum over the rows \(i\) in the data that had missing data on happiness score, meaning the rows for which we actually had imputation.

Part 4

\[MSE = {\frac{1}{n_{miss}} \sum_{i=1}^{n_{miss}} (y_i - y_i^{*})^2}\] The next step is to divide the RSS by the number of imputed values. This gives us the mean squared error (MSE), which is the average squared imputation error, meaning the average squared distance between the true value and the imputation. We don’t want the squared difference, though. So…

Part 5

\[RMSE = \sqrt{\frac{1}{n_{miss}} \sum_{i=1}^{n_{miss}} (y_i - y_i^{*})^2}\]

We take the square root of the MSE to get the root mean squared error (RMSE). This gives us a measure that describes, on average, how far away the imputations are from the truth.

As we move through the semester, we will learn several different metrics we can use to assess the performance of a model. Each will have a different job and a different interpretation, so it is important to think through how the metric is built so we understand what it means.

Let’s build a quick function to compute the RMSE so we don’t have to it by hand. Some of it is filled in, but you need to fill in the rest!

compute_RMSE <- function(truth, imputations){

  # Part 1
  part1 <- truth - imputations
  
  #Part 2
  part2 <- 
  
  #Part 3: RSS 
  part3 <- 
  
  #Part4: MSE
  part4 <- 1/length(imputations)*
  
  # Part 5:RMSE
  sqrt(        )
}

Let’s use this to compute the RMSE for our UMI imputations.

# Pull the imputations
imputations_UMI <-  happinessUMI[whichMissing, "happiness"]

# Pull the true values of happiness corresponding to those rows
truth <- Happiness[whichMissing, "happiness"]

# Compute the RSME for UMI
compute_RMSE(truth , imputations_UMI )

Imputation: Regression

Okay, so that is what happens when we use UMI. Now, let’s see what happens if we try a regression imputation model instead.

We want to build a model that can impute happiness score as accurately as we can. Since our data set is small today, we want to use all other features in the data set to build a model to impute happiness score.

We learned this code in class, and it is provided again below.

# Build a model for imputation
imputation_engine <- lm(happiness ~ ., data = happiness25[,-c(1)])

# Create a new data set to store our imputations
happinessRegression <- happiness25

# Fill in the missing values in with
# predictions from the regression model
happinessRegression[whichMissing, "happiness"] <-
predict(imputation_engine, newdata = happiness25[whichMissing,-c(1)])

Now that we have checked how the confidence intervals compare, let’s try assessing the predictive accuracy of regression imputation.

The Data

The data set used in this lab is from the World Happiness Report. The data set can be accessed here.

Citation: Helliwell, J. F., Layard, R., Sachs, J. D., Aknin, L. B., De Neve, J.-E., & Wang, S. (Eds.). (2023). World Happiness Report 2023 (11th ed.). Sustainable Development Solutions Network.