Data Cleaning in R

# load libraries
library(readr)
library(dplyr)

1.Introduction

A huge part of data science involves acquiring raw data and getting it into a form ready for analysis. Some have estimated that data scientists spend 80% of their time cleaning and manipulating data, and only 20% of their time actually analyzing it or building models from it.

When we receive raw data, we have to do a number of things before we’re ready to analyze it, possibly including:

1.diagnosing the “tidiness” of the data — how much data cleaning we will have to do

2.reshaping the data — getting the right rows and columns for effective analysis

3.combining multiple files

4.changing the types of values — how we fix a column where numerical values are stored as Preview: Docs Loading link description strings, for example

5.dropping or filling missing values - how we deal with data that is incomplete or missing

6.manipulating strings to represent the data better

We will go through the techniques data scientists use to accomplish these goals by looking at some “unclean” datasets and trying to get them into a good, clean state. Along the way we will use the powerful tidyverse packages dplyr and tidyr to get our data squeaky clean!

Instuctions

We have provided an example of data representing exam scores from 1000 students in an online math class.

These data frames, which you can view in the rendered notebook, are hard to work with. They’re separated into multiple tables, and the values don’t lend themselves well to analysis. Try to think about how you would plot the exam score average against the age of the students in the class. This would not be easy!

In the next exercises, we’ll transform this data so that performing a task like that visualization would be simple.

# load data frame
students_1 <- read_csv('exams_0.csv')
students_2 <- read_csv('exams_1.csv')

# inspect data frame
head(students_1)

head(students_2)

2.Diagnose the Data

We often describe data that is easy to analyze and visualize as “tidy data”. What does it mean to have tidy data?

For data to be tidy, it must have:

1.Each variable as a separate column

2.Each row as a separate observation

The first step of diagnosing whether or not a dataset is tidy is using base R and dplyr functions to explore and probe the dataset.

You’ve seen most of the functions we often use to diagnose a dataset for cleaning. Some of the most useful ones are:

1.head() — display the first 6 rows of the table

2.summary() — display the summary statistics of the table

3.colnames() — display the column names of the table

# read CSVs
grocery_1 <- read_csv('grocery_1.csv')
grocery_2 <- read_csv('grocery_2.csv')

Instructions

1.Provided in notebook.Rmd are two data frames, grocery_1 and grocery_2.

Begin by viewing the head() of both grocery_1 and grocery_2.

# inspect data frames
head(grocery_1)

head(grocery_2)

summary(grocery_1)

 Grocery Item        Cake Recipe    Pancake Recipe Cookie Recipe  
 Length:3           Min.   :1.000   Min.   :1.0    Min.   :1.000  
 Class :character   1st Qu.:1.500   1st Qu.:1.5    1st Qu.:1.000  
 Mode  :character   Median :2.000   Median :2.0    Median :1.000  
                    Mean   :1.667   Mean   :2.0    Mean   :1.333  
                    3rd Qu.:2.000   3rd Qu.:2.5    3rd Qu.:1.500  
                    Max.   :2.000   Max.   :3.0    Max.   :2.000  

summary(grocery_2)

 Grocery Item          Recipe              Number     
 Length:9           Length:9           Min.   :1.000  
 Class :character   Class :character   1st Qu.:1.000  
 Mode  :character   Mode  :character   Median :2.000  
                                       Mean   :1.667  
                                       3rd Qu.:2.000  
                                       Max.   :3.000  

colnames(grocery_1)

[1] "Grocery Item"   "Cake Recipe"    "Pancake Recipe" "Cookie Recipe" 

colnames(grocery_2)

[1] "Grocery Item" "Recipe"       "Number"      

2.Explore the data frames using the other functions listed.

Which data frame is “clean”, tidy, and ready for analysis? Create a variable named clean_data_frame and assign it the value 1 if grocery_1 is a clean and tidy data frame or 2 if grocery_2 is a clean and tidy data frame.

# clean data frame
clean_data_frame <- 2

3.Dealing with Multiple Files

Often, you have the same data separated out into multiple files.

Let’s say that you have a ton of files following the filename structure: ‘file_1.csv’, ‘file_2.csv’, ‘file_3.csv’, and so on. The power of dplyr and tidyr is mainly in being able to manipulate large amounts of structured data, so you want to be able to get all of the relevant information into one table so that you can analyze the aggregate data.

You can combine the base R functions list.files() and lapply() with readr and dplyr to organize this data better, as shown below: ” files <- list.files(pattern = “file_.*csv”) df_list <- lapply(files,read_csv) df <- bind_rows(df_list) ” 1.The first line uses list.files() and a regular expression, a sequence of characters describing a pattern of text that should be matched, to find any file in the current directory that starts with ‘file_’ and has an extension of csv, storing the name of each file in a vector files

2.The second line uses lapply() to read each file in files into a data frame with read_csv(), storing the data frames in df_list

3.The third line then concatenates all of those data frames together with dplyr’s bind_rows() function

Instructions

1.You have 10 different files containing 100 students each. These files follow the naming structure:

exams_0.csv exams_1.csv … up to exams_9.csv

You are going to read each file into an individual data frame and then combine all of the entries into one data frame.

First, create a variable called student_files and set it equal to the list.files() of all of the CSV files we want to import.

# list files
student_files <- list.files(pattern = "exams_.*csv")
student_files

 [1] "exams_0.csv" "exams_1.csv" "exams_2.csv" "exams_3.csv" "exams_4.csv" "exams_5.csv"
 [7] "exams_6.csv" "exams_7.csv" "exams_8.csv" "exams_9.csv"

2.Read each file in student_files into a data frame using lapply() and save the result to df_list.

# read files
df_list <- lapply(student_files, read_csv)
df_list

[[1]]

[[2]]

[[3]]

[[4]]

[[5]]

[[6]]

[[7]]

[[8]]

[[9]]

[[10]]

NA

3.Concatenate all of the data frames in df_list into one data frame called students.

# concatenate data frames
students <- bind_rows(df_list)
students

4.Inspect students. Save the number of rows in students to nrow_students. Did you get all of them

# number of rows in students
nrow_students <- nrow(students)
print(nrow_students)

[1] 1000

4.Reshaping your Data

Since we want

1.Each variable as a separate column

2.Each row as a separate observation

We would want to reshape a table like:

Account Checking Savings

“12456543” 8500 8900

“12283942” 6410 8020

“12839485” 78000 92000

Into a table that looks more like:

Account Account Type Amount

“12456543” “Checking” 8500

“12456543” “Savings” 8900

“12283942” “Checking” 6410

“12283942” “Savings” 8020

“12839485” “Checking” 78000

“12839485” “Savings” 920000

We can use tidyr’s gather() function to do this transformation. gather() takes a data frame and the columns to unpack:

library(tidyr)

” df %>% gather(‘Checking’,‘Savings’,key=‘Account Type’,value=‘Amount’) ” The arguments you provide are:

1.df: the data frame you want to gather, which can be piped into gather()

2.Checking and Savings: the columns of the old data frame that you want to turn into variables

3.key: what to call the column of the new data frame that stores the variables

4.value: what to call the column of the new data frame that stores the values

Instructions

1.The students data frame from the previous exercise has been loaded into the notebook for you. Save the columns names to original_col_names and print it.

# original column names
original_col_names <- colnames(students)
original_col_names

[1] "id"          "full_name"   "gender_age"  "fractions"   "probability" "grade"      

2.There is a column for the scores on the fractions exam, and a column for the scores on the probability exam.

We want to make each row an observation, so we want to transform this table to look like:

full_name exam score gender_age grade

“First Student” “fractions” score% … …

“First Student” “probability” score% … …

“Second Student” “fractions” score% … …

“Second Student” “probability” score% … …

Use gather to create a new table (still called students) that follows this structure. Then view the head() of students.

# gather columns
students <- students %>%
  gather('fractions', 'probability', key = 'exam', value = 'score')
head(students)

3.Save the columns names of the updated students data frame to gathered_col_names and print it.

# updated column names
gathered_col_names <- colnames(students)
gathered_col_names

[1] "id"         "full_name"  "gender_age" "grade"      "exam"       "score"     

4.The dplyr function count() takes a data frame and a column as arguments and returns a table with counts of the unique values in the named column.

Find the count of each unique value in the exam column. Save the result to exam_counts and view exam_counts.

# unique value counts of exam
exam_counts <- students %>%
  count(exam)
exam_counts

5.Dealing with Duplicates

Often we see duplicated rows of data in the data frames we are working with. This could happen due to errors in data collection or in saving and loading the data.

To check for duplicates, we can use the base R function duplicated(), which will return a logical vector telling us which rows are duplicate rows.

Let’s say we have a data frame fruits that represents this table:

item price calories

“banana” “$1” 105

“apple” “$0.75” 95

“apple” “$0.75” 95

“peach” “$3” 55

“peach” “$4” 55

“clementine” “$2.5” 35

If we call fruits %>% duplicated(), we would get the following vector:

“>> [1] FALSE FALSE TRUE FALSE FALSE FALSE”

We can see that the third row, which represents an “apple” with price “$0.75” and 95 calories, is a duplicate row. Every value in this row is the same as in another row (the previous row).

We can use the dplyr distinct() function to remove all rows of a data frame that are duplicates of another row.

If we call fruits %>% distinct(), we would get the table:

item price calories

“banana” “$1” 105

“apple” “$0.75” 95

“peach” “$3” 55

“peach” “$4” 55

“clementine” “$2.5” 35

The “apple” row was deleted because it was exactly the same as another row. But the two “peach” rows remain because there is a difference in the price column.

If we wanted to remove every row with a duplicate value in the item column, we could specify a subset:

“fruits %>% distinct(item,.keep_all=TRUE)”

By default, this keeps the first occurrence of the duplicate:

item price calories

“banana” “$1” 105

“apple” “$0.75” 95

“peach” “$3” 55

“clementine” “$2.5” 35

Make sure that the columns you drop duplicates from are specifically the ones where duplicates don’t belong. You wouldn’t want to drop duplicates with the price column as a subset, for example, because it’s okay if multiple items cost the same amount!

Instructions

1.The students data frame has a column id that is neither unique nor required for our analysis. Drop the id column from the data frame and save the result to students. View the head() of students.

# drop id column
students <- students %>%
  select(-id)
head(students)

2. It seems like in the data collection process, some rows may have been recorded twice. Use the duplicated() function on the students data frame to make a vector object called duplicates.

# find duplicated rows
duplicates <- students %>%
  duplicated()
head(duplicates)

[1] FALSE FALSE FALSE FALSE FALSE FALSE

3.table() is a base R function that takes any R object as an argument and returns a table with the counts of each unique value in the object.

Pipe the result from the previous checkpoint into table() to see how many rows are exact duplicates. Make sure to save the result to duplicate_counts, and view duplicate_counts.

# count duplicated rows
duplicate_counts <- duplicates %>%
  table()
duplicate_counts

.
FALSE  TRUE 
 1976    24 

4.Get rid of the duplicate rows in the students data frame and save this new data frame as unique_students.

# remove duplicated rows, keep only unique rows
unique_students <- students %>%
  distinct()
unique_students

5.Use the duplicated() function again to make an object called updated_duplicates after dropping the duplicates. Pipe the result into table() to see if any duplicates remain, and view updated_duplicates. Are there any TRUEs left?

# find and count duplicated rows in updated data frame
updated_duplicates <- unique_students %>%
  duplicated() %>%
  table()
updated_duplicates

.
FALSE 
 1976 

6.Splitting By Index

In trying to get clean data, we want to make sure each column represents one type of measurement. Often, multiple measurements are recorded in the same column, and we want to separate these out so that we can do individual analysis on each variable.

Let’s say we have a column “birthday” with data formatted in MMDDYYYY format. In other words, “11011993” represents a birthday of November 1, 1993. We want to split this data into day, month, and year so that we can use these columns as separate features.

In this case, we know the exact structure of these strings. The first two characters will always correspond to the month, the second two to the day, and the rest of the string will always correspond to year. We can easily break the data into three separate columns by splitting the strings into substrings using str_sub(), a helpful function from the stringr package:

// Create the ‘month’ column df %>% mutate(month = str_sub(birthday,1,2))

// Create the ‘day’ column df %>% mutate(day = str_sub(birthday,3,4))

// Create the ‘year’ column df %>% mutate(year = str_sub(birthday,5))

1.The first command takes the characters starting at index 1 and ending at index 2 of each value in the birthday column and puts it into a month column.

2.The second command takes the characters starting at index 3 and ending at index 4 of each value in the birthday column and puts it into a day column.

3.The third command takes the characters starting at index 5 and ending at the end of the value in the birthday column and puts it into a year column.

This would transform a table like:

id birthday

1011 “12241989”

1112 “10311966”

1113 “01052011”

into a table like:

id birthday month day year

1011 “12241989” “12” “24” “1989”

1112 “10311966” “10” “31” “1966”

1113 “01052011” “01” “05” “2011”

We will practice changing string columns into numerical columns (like converting “10” to 10) in a future exercise.

Instructions

1.Print out the columns of the students data frame.

# print columns of students
print(colnames(students))

[1] "full_name"  "gender_age" "grade"      "exam"       "score"     

2.The column gender_age sounds like it contains both gender and age!

View the head() of students to see what kind of data gender_age contains.

# view head of students
head(students)

3.It looks like the first character of the values in gender_age contains the gender, while the rest of the string contains the age. Let’s separate out the gender data into a new column called gender. Save the result to students, and view the head().

# add gender and age columns
library(stringr)
students <- students %>%
  mutate(gender = str_sub(gender_age, 1, 1))
head(students)

4.Now, separate out the age data into a new column called age. Save the updated data frame to students, and view the head().

# add gender and age columns
students <- students %>%
  mutate(age = str_sub(gender_age, 2))
head(students)

5.Now, we don’t need that gender_age column anymore. Drop gender_age from students, and save the result to students. View the head() of students.

# drop gender_age column
students <- students %>%
  select(-gender_age)
head(students)

7.Splitting By Character

Let’s say we have a column called “type” with data entries in the format “admin_US” or “user_Kenya”, as shown in the table below.

id type

1011 “user_Kenya”

1112 “admin_US”

1113 “moderator_UK”

Just like we saw before, this column actually contains two types of data. One seems to be the user type (with values like “admin” or “user”) and one seems to be the country this user is in (with values like “US” or “Kenya”).

We can no longer just split along the first 4 characters because admin and user are of different lengths. Instead, we know that we want to split along the “”. We can thus use the tidyr function separate() to split this column into two, separate columns: ” Create the ‘user_type’ and ‘country’ columns //df %>% // separate(type,c(‘user_type’,‘country’),’’)”

1.type is the column to split

2.c(‘user_type’,‘country’) is a vector with the names of the two new columns

3.’_’ is the character to split on

This would transform the table above into a table like:

id user_type country

1011 “user” “Kenya”

1112 “admin” “US”

1113 “moderator” “UK”

Instructions

1.View the head() of students. Notice that the students’ names are stored in a column called full_name.

# view the head of students
head(students)

2.Separate the full_name column into two new columns, first_name and last_name, by splitting on the ’ ’ character .

Provide as an extra argument to the separate() function extra =‘merge’. This will ensure that middle names or two-word last names will all end up in the last_name column.

Save the result to students, and view the head().

# separate the full_name column
students <- students %>%
  separate('full_name', c('first_name', 'last_name'), ' ', extra ='merge')
head(students)

8.Looking at Data Types

Each column of a data frame can hold items of the same data type. The data types that R uses are: character, numeric (real or decimal), integer, logical, or complex. Often, we want to convert between types so that we can do better analysis. If a numerical category like “num_users” is stored as a vector of characters instead of numerics, for example, it makes it more difficult to do something like make a line graph of users over time.

To see the types of each column of a data frame, we can use:

str(df)

str() displays the internal structure of an R object. Calling str() with a data frame as an argument will return a variety of information, including the data types. For a data frame like this:

item price calories

“banana” “$1” 105

“apple” “$0.75” 95

“peach” “$3” 55

“clementine” “$2.5” 35

the data types would be:

// #> $ item: chr

// #> $ price: chr

// #> $ calories: num

We can see that the price column is made up of characters, which will probably make our analysis of price more difficult. We’ll look at how to convert columns into numeric values in the next few exercises.

Instructions

1.Let’s inspect the data types in the students table.

Print out the structure of students.

# print structure of students
str(students)

tibble [2,000 × 7] (S3: tbl_df/tbl/data.frame)
 $ first_name: chr [1:2000] "Moses" "Timofei" "Silvain" "Lezley" ...
 $ last_name : chr [1:2000] "Kirckman" "Strowan" "Poll" "Pinxton" ...
 $ grade     : num [1:2000] 11 11 9 11 11 11 9 11 12 11 ...
 $ exam      : chr [1:2000] "fractions" "fractions" "fractions" "fractions" ...
 $ score     : chr [1:2000] "69%" "63%" "69%" NA ...
 $ gender    : chr [1:2000] "M" "M" "M" "M" ...
 $ age       : chr [1:2000] "14" "18" "18" "18" ...

2.If we wanted to make a scatterplot of age vs average exam score, would we be able to do it with this type of data?

Paste the following code in the last code block to try and print out the mean of the score column of students.

” students %>% summarise(mean_score = mean(score)) ”

What warning do you see?

# mean of age column
students %>% 
  summarise(mean_score = mean(score))

9.String Parsing

Sometimes we need to modify strings in our data frames to help us transform them into more meaningful metrics. For example, in our fruits table from before:

item price calories

“banana” “$1” 105

“apple” “$0.75” 95

“peach” “$3” 55

“peach” “$4” 55

“clementine” “$2.5” 35

We can see that the ‘price’ column is actually composed of character strings representing dollar amounts. This column could be much better represented as numeric, so that we could take the mean, calculate other aggregate statistics, or compare different fruits to one another in terms of price.

First, we can use a regular expression, a sequence of characters that describe a pattern of text to be matched, to remove all of the dollar signs. The base R function gsub() will remove the $ from the price column, replacing the symbol with an empty string ’’:

“fruit %>% mutate(price=gsub(‘\$’,’’,price))”

Then, we can use the base R function as.numeric() to convert character strings containing numerical values to numeric:

“fruit %>% mutate(price = as.numeric(price))”

Now, we have a data frame that looks like: item price calories

“banana” 1 105

“apple” 0.75 95

“peach” 3 55

“peach” 4 55

“clementine” 2.5 35

Instructions

1.We saw in the last exercise that finding the mean of the score column is hard to do when the data is stored as characters and not numbers.

View the head() of students to take a look at the values in the score column.

# view head of students
head(students)

2.Remove the ‘%’ symbol from the score column, and save the resulting data frame to students. View students.

# remove % from score column
students <- students %>%
  mutate(score = gsub('%', '',score))
students

3.Convert the score column to a numerical type using the as.numeric() function. Save this new data frame to students, and view

# change score column to numeric
students <- students %>%
  mutate(score = as.numeric(score))
students

10.Review

Great! We have looked at a number of different methods we may use to get data into the format we want for analysis.

Specifically, we have covered:

1.diagnosing the “tidiness” of data

2.combining multiple files

3.reshaping data

4.changing the types of values

5.manipulating strings to represent data better

You can use these methods to transform your datasets to be clean and easy to work with!

Instructions

1.The students data frame is nearly cleaned and ready for analysis! There’s one more change that can be made to the ages of the students to help describe and visualize the data. What could that change be? What is the ideal data type for the ages column?

Make the change to age and save the resulting data frame to students.

# update age column
students <- students %>%
  mutate(age = as.numeric(age));
students