Wrangling Client Data

Project Background

BYU-Idaho Peer-Success Mentoring provides short-term mentoring to a variety of BYU-Idaho student populations with a goal to improve student success and retention. We’re interested in looking for possible correlation between participation in mentoring and defined measures of student success, including improved GPA.

The project will be focused on answering the following question: Do students who participate in Peer-Success Mentoring have more success (improvement in GPA) than students who don’t participate in Peer-Success Mentoring?

This question will be explored through various forms of data analysis, hypothesis testing, and inferential statistics, using data about students who met defined mentoring criteria. A Control group of students who met the criteria but did not participate in mentoring will be compared with a Mentored group of students who met the same criteria and did participate in mentoring. Click here to read more about Peer-Success Mentoring and the background for this project.

Provided Data

The data is stored in an excel workbook, where each tab represents a different group of potential mentee.

1. Control (Students who met Tier 3 mentoring criteria but opted out of mentoring)

2. Mentored (Students who met Tier 3 mentoring criteria and participated in mentoring)

3. Referrals (Students who were referred to the mentoring program by a faculty member)

(Note: Mentored and Referrals are both made up of students who did participate in mentoring, so we will actually only have two groups: Control and Mentored.)

Each tab in the workbook follows the same format, where each row is one student with the following columns:

1. Semester Mentored : Semester they participated in (or opted out of) mentoring (i.e. Fall 2018)

2. Mentee Name: Student’s name (Lastname, Firstname)

3. Status : Mentoring Status (Active Mentoring, Dropped, Fulfilled Program, or Referred to HJG/LS)

4. I-Number : Student’s unique ID number

5. Student Type : Mentoring criteria that student falls under (Tier 3 or Referral)

6. Pre 1 : Student’s GPA the semester before the semester mentored

7. Semester Mentored : Student’s GPA the semester mentored

8. Post 1 : Student’s GPA the semester after the semester mentored

9. Post 2 : Student’s GPA 2 semesters after the semester mentored

Even before reading in the data, I noticed some changes that needed to be made:

• Two columns have the same name, “Semester Mentored”.
• There are 3 tabs, but there are really two overlapping sets of two groups: Control vs Mentored and Tier 3 vs Referral. The three tabs don’t group the students in the way we need.
• This data includes student identifiers, such as their name and their student ID number, which need to be removed for privacy reasons.
• Some rows don’t have any student data in them.
• The column names contain spaces, which will make referencing them harder.

Read in the Data using read_excel

The read_excel() function from the readxl package can read in data from an excel workbook to store in a dataframe.

#Read in the data from the client's excel sheet
Mentored <- read_excel("StudentGPAs.xlsx", sheet = "Tier 3 Mentored", skip = 1)
Control <- read_excel("StudentGPAs.xlsx", sheet = "Tier 3 Control", skip = 1)
Referrals <- read_excel("StudentGPAs.xlsx", sheet = "Referrals", skip = 1)

Mentored %>% head() %>% pander::pander()

(For the sake of this post, I created fabricated data in the same format as the real data).

Combine the 3 Datasets

Because the three groups overlap a bit, let’s add a new column to specify whether that student is Mentored or Control. I used the mutate() function from the dplyr package for this.

#Add a column to specify which group that student belongs to.
Mentored <- Mentored %>%
  mutate("Group" = "Mentored")

Control <- Control %>%
  mutate("Group" = "Control")

Referrals <- Referrals %>%
  mutate("Group" = "Mentored")

Now that all three datasets have a Group column, let’s combine them into one dataframe using rbind() function. While we’re at it, let’s also rename the columns to make things a bit easier going forward.

#Combine both groups into one dataframe
Students <- rbind(Mentored, Control, Referrals) %>%
  
  #rename some columns
  rename(semester_mentored = `Semester Mentored...1`,
         `Pre_1` = `Pre 1`, 
         `During` = `Semester Mentored...7`, 
         `Post_1` = `Post 1`, 
         `Post_2` = `Post 2`,
        `Student_Type` = `Student Type`)

Finally, we need to remove the rows that have no data in them. The main reason for doing this is that later on, I’ll want to count how many NA’s are in each column. If there are complete rows of NA, that will mess with those counts. The full NA rows shouldn’t be in there at all. To make sure we remove rows that have no student data at all, we’ll check for rows where the name is “NA” and remove those. Then we know for sure that we didn’t accidentally remove a student who is in the data, but is missing a GPA in some semesters.

#Remove rows that do not have any actual mentee info
Students <- Students %>%
  filter(`Mentee Name` != "NA")

Here’s what the dataset looks like so far:

#Show row 1, 30, and 50 (for a variety of different types of mentees)
Students[c(1,30, 50),] %>% pander()

Optional Wrangling to Aid in Plotting

Later on when I create plots, I want the Status column to appear in a specific order in facets or on an axis. I can specify this order using fct_relevel() from the forcats package, which is included in the tidyverse.

#Reorder the status values so they are in the order we want for plots
Students <- Students %>%
  mutate(Status = fct_relevel(Status,
                              "Attempted",
                              "Fulfilled Program",
                              "Dropped",
                              "Referred to HJG/LS",
                              "Active Mentoring"))

#Number of students in each Type
sample_size_type = Students %>%
  group_by(Student_Type) %>%
  summarise(Type_size = n()) 

sample_size_type %>% 
  pander()

#Number of students in each Type/semester mentored
sample_size_type_sem = Students %>%
  group_by(Student_Type, semester_mentored) %>%
  summarise(Type_sem_size = n()) 

sample_size_type_sem %>% 
  pander()

## Group Sizes ##

#Number of students in each Group
sample_size_group = Students %>%
  group_by(Group) %>%
  summarise(Group_size = n())

sample_size_group %>% 
  pander()

#Number of students in each Group/semester mentored
sample_size_group_sem = Students %>%
  group_by(Group, semester_mentored) %>%
  summarise(Group_sem_size = n()) 

sample_size_group_sem %>% 
  pander()

## Status Sizes ##

#Number of students in each Status
sample_size_status = Students %>%
  group_by(Status) %>%
  summarise(Status_size = n()) 

sample_size_status %>% 
  pander()

#Number of students in each Status/semester mentored
sample_size_status_sem = Students %>%
  group_by(Status, semester_mentored) %>%
  summarise(Status_sem_size = n()) 

sample_size_status_sem %>% 
  pander()

#### Add sample size labels to my dataset ####

Students <- Students %>%
 
  ## Student Type ##
  
  #Add on Type sizes
  left_join(sample_size_type) %>%
  
  #make new column with Type size labels
  mutate(Type_size_label = paste0(Student_Type, "\n", "n = ", Type_size)) %>%
  
  #Add on Type size / semester mentored
  left_join(sample_size_type_sem) %>%
  
  #make new column with Type size/semester mentored labels
  mutate(Type_sem_size_label = paste0(Student_Type, "\n", semester_mentored, "\n", "n = ", Type_sem_size)) %>%
  
  
  ## Group ##
  
  #Add on Group sizes
  left_join(sample_size_group) %>%
  
  #make new column with Group size labels
  mutate(Group_size_label = paste0(Group, "\n", "n = ", Group_size)) %>%
  
  #Add on Groups size / semester mentored
  left_join(sample_size_group_sem) %>%
  
  #make new column with Group size/semester mentored labels
  mutate(Group_sem_size_label = paste0(Group, "\n", semester_mentored, "\n", "n = ", Group_sem_size)) %>%
  
  
  ## Status ##
  
  #Add on Status sizes
  left_join(sample_size_status) %>%
  
  #make new column with Status size labels
  mutate(Status_size_label = paste0(Status, "\n", "n = ", Status_size)) %>%
  
  #Add on Status sizes / semester mentored
  left_join(sample_size_status_sem) %>%
  
  #make new column with Status size labels
  mutate(Status_sem_size_label = paste0(Status, "\n", semester_mentored, "\n", "n = ", Status_sem_size))



### Remove the sample size datasets to clean up my environment ###
rm(sample_size_type,
   sample_size_type_sem,
   sample_size_group,
   sample_size_group_sem,
   sample_size_status,
   sample_size_status_sem)

Students[c(1,30),] %>% pander()

Students_new <- Students %>%
  select(Pre_1,
         During,
         Post_1,
         Post_2,
         semester_mentored,
         Student_Type,
         Group,
         Status,
         Type_size_label,
         Type_sem_size_label,
         Group_size_label,
         Group_sem_size_label,
         Status_size_label,
         Status_sem_size_label)

Wide and Long Formats

Our data is currently in a “wide” format. It is technically not tidy, but the wide format will be useful in some cases, so I want to keep this. Later on, I’ll be looking at each student’s change in GPA from Pre_1 to Post_1. I’ll create a column with those values now so they are ready to go when I need them.

# Wide format data with a new column that is the change in GPA from pre1 to post 1
Students_wide <- Students_new %>%
  mutate(Change = Post_1 - Pre_1)

Students_wide[c(1,30),] %>% pander()

The tidy-est version of our data will be the “long” format. Pre_1, During, Post_1, and Post_2 could be considered variables, but they are really values for the variable semester. I’ll use pivot_longer() to turn those 4 semester GPA columns into two columns - semester and GPA, and then fct_relevel() to specify the order of the semesters.

#We want pivot the data so that each row is only one GPA measurement (one student+one semester)
Students_long <- Students_new  %>%
  pivot_longer(cols = c(`Pre_1`, `During`, `Post_1`, `Post_2`), 
               names_to = "semester", 
               values_to = "GPA")

#Reorder the semester values so they are in the right order
Students_long <- Students_long %>%
  mutate(semester = fct_relevel(semester,
                                "Pre_1",
                                "During",
                                "Post_1",
                                "Post_2"))
Students_long %>% head(4) %>% pander()

Our new tidy dataframe Student_long holds all students with the following columns:

• semester_mentored : Semester they participated in (or opted out of) mentoring (i.e. Fall 2018)

• Status : Mentoring Status (Attempted, Active Mentoring, Dropped, Fulfilled Program, or Referred to HJG/LS)

• Student Type : Mentoring criteria that student falls under (Tier 3 or Referral)

• Group : Control or Mentored

• semester : The semester of that GPA measurement (Pre_1, During, Post_1, or Post_2)

• GPA : Student’s GPA that semester

(Plus the size labels for Type, Group, and Status)

Moving Forward

Now that the data is tidy and ready to go, I can use it to make visuals or run hypothesis tests. Wrangling can be a big part of any data science project (sometimes even the biggest part!), but it is so necessary for the rest of the project to go smoothly.

Check out the rest of my work on this project here.