---
title: 'Technical Appendix A: Initial EDA'
output: html_document
---

# Data Wrangling

```{r, message=FALSE, warning=FALSE}
# Library packages
library(plyr)
library(dplyr)
library(ggplot2)
library(readxl)
library(rvest)
library(robotstxt)
library(tidyverse)
```

## Data Sets for Figure 1-4 & Figure 6

```{r}
# Import scholarship data 
scholarship <- read_excel("DR-826 Pittsburgh Promise scholarship use.xlsx", sheet = "Sheet1")

# Clean scholarship data 
names(scholarship)[1]<-"RandomID"
scholarship$GradYear <- as.character(scholarship$GradYear)
```

```{r, message=FALSE}
# Import demographics data
demo1415 <- read_excel("DR-827 Demographics.xlsx", sheet = "Demographics for cohort 1415")
demo1516 <- read_excel("DR-827 Demographics.xlsx", sheet = "Demographics for cohort 1516")
demo1617 <- read_excel("DR-827 Demographics.xlsx", sheet = "Demographics for cohort 1617")

# Clean demographics data 
demo1416 <- join(demo1415,demo1516,type="full")
demo <- join(demo1416,demo1617,type="full")

# Filter observations for students' senior year
demo_senior <- demo %>%
  select(-c("Cohort")) %>%
  mutate(GradYear = substr(as.character(SchoolYear),5,8)) %>%
  filter(GradeCode == 12)
demo_senior$RandomID <- as.character(demo_senior$RandomID)
```

```{r}
# Join demographic data in senior year and scholarship data together
# Only senior year since qualification is evaluated after graduating from high school
demo_scholarship <- inner_join(demo_senior, scholarship, by = c("RandomID", "GradYear"))

# Filter repetitive observations out 
demo_scholarship_final <- distinct(demo_scholarship) #2223 obs in total
```


## Data Sets for Figure 5

```{r}
# Import CTE data
 CTE <- read_excel("DR-837 Career and Tech. Education (CTE) outcomes (industrial-recognized certificates).xlsx", sheet = "CTE data")

# Generate column that calculates the total number of certifications earned by each student
cte_id_check <- CTE %>%
  group_by(RandomID) %>%
  dplyr::summarise(num_cte = n())

# Generate new CTE data that includes a column showing the total number of certifications earned by each student. 
CTE_new = left_join(CTE, cte_id_check, by = "RandomID")
```

```{r}
# Manipulate CTE and Scholarship data to eliminate to prepare for later joint data set
CTE_new <- CTE_new %>%
  select(-c("Cohort"))
CTE_new$RandomID <- as.character(CTE_new$RandomID)

# Join CTE and Scholarship data 
CTE_scholarship = inner_join(scholarship, CTE_new, by = "RandomID")
CTE_scholarship <- distinct(CTE_scholarship)
```


## Data Sets for Figure 7 & Figure 8

```{r}
# Import attendance data
attendance <- read_excel("DR-830 Attendance.xlsx", 
    sheet = "Attendance data")
```

```{r, message=FALSE}
# Clean attendance data
attendance_new <- attendance %>%
  mutate_at(c(1, 3:9), as.factor)
names(attendance_new)[10] <- "CountofDays"
# Create attendance_scholarship table: attendance left_join scholarship
scholarship_new <- scholarship %>%
  mutate_at(c(2:8), as.factor)
attendance_scholarship <- attendance_new %>%
  left_join(scholarship_new, by = c("RandomID"="RandomID"))
# Create a long table where sum each student's recorded days based on attendance status
attendance_scholarship_long <- attendance_scholarship %>% 
  dplyr::select(RandomID, AttendanceStatus, CountofDays) %>%
  group_by(RandomID, AttendanceStatus) %>% 
  dplyr::summarise(TotalDays = sum(CountofDays))
# Create a wide table that each student takes 1 row
attendance_scholarship_wide <- attendance_scholarship_long %>% 
  spread(AttendanceStatus, TotalDays) %>%
  dplyr::select(-'NULL') %>%
  replace(is.na(.), 0)
# Add total count of days
attendance_scholarship_wide$totalDays <- rowSums(attendance_scholarship_wide[2:6])
```

```{r}
# Create attendance_rate data
# attendance data is the joined data set between scholarship and attendance
wide_scholarship <- attendance_scholarship_wide %>%
  left_join(scholarship_new, by = c("RandomID"="RandomID")) %>%
  na.omit(.)
# Add excused percentage
wide_scholarship <- wide_scholarship %>%
  mutate(excused_pct = (`Present`+`Present Excused`+`Present Unexcused`+`Absent Excused`)/totalDays)
# Categorize excused absent rate
wide_scholarship <- wide_scholarship %>%
  mutate(excused_pct_cate = if_else(excused_pct >= 0.9, ">=90%", "<90%"))

attendance_rate <- wide_scholarship
```

```{r}
# Import GPA data
gpa <- read_excel("DR-831 GPA.xlsx", sheet = "GPA")
```

```{r}
# Clean GPA data
gpa_no_cohort <- gpa %>% dplyr::select(-Cohort)
gpa_no_cohort <- gpa_no_cohort[!duplicated(gpa_no_cohort),]

# Create senior_gpa data that includes cumulative GPA of each student
senior_students <- (gpa_no_cohort %>% 
                     group_by(RandomID) %>%
                     dplyr::summarise(n = n()) %>%
                     filter(n==4))$RandomID
senior_gpa <- gpa_no_cohort %>% 
  filter(RandomID %in% senior_students) %>%
  group_by(RandomID) %>%
  filter(SchoolYear == max(SchoolYear))
```

\newpage

# Visualizations

## Figure 1: Qualification for Promise Scholarship vs. Race 

```{r}
ggplot(demo_scholarship_final, 
       aes(fill=Race, x=QualifiedforCorePromise)) + 
  geom_bar(position="fill") + 
  ggtitle("Qualified for Promise and Race") +
  labs(y="Proportion",
       x="Qualified for Promise") +
  theme_bw()
```


## Figure 2: Receipt of Promise Scholarship vs. Race

```{r}
# Filter qualified students in the data set 
qualified <- demo_scholarship_final %>%
  filter(QualifiedforCorePromise == "yes") #1560 observations

ggplot(qualified, aes(fill=Race, x=EverReceivedPromiseAward)) + 
  geom_bar(position="fill") + 
  ggtitle("Ever Received Promise and Race") +
  labs(y="Proportion",
       x="Ever Received Promise") +
  theme_bw()
```


## Figure 3: Qualification for Promise Scholarship vs. Gender

```{r}
ggplot(demo_scholarship_final, 
       aes(fill=Gender, x=QualifiedforCorePromise)) + 
  geom_bar(position="fill") + 
  ggtitle("Qualified for Promise and Gender") +
  labs(y="Proportion",
       x="Qualified for Promise") +
  theme_bw()
```


## Figure 4: Receipt of Promise Scholarship vs. Gender

```{r}
ggplot(qualified, 
       aes(fill= Gender, x=EverReceivedPromiseAward)) + 
  geom_bar(position="fill") + 
  ggtitle("Ever Received Promise and Gender") +
  labs(y="Proportion",
       x="Ever Received Promise") +
  theme_bw()
```


## Figure 5: Receipt of Promise Scholarship vs. Career Certifications

```{r}
ggplot(data = CTE_scholarship, 
       aes(x = EverReceivedPromiseAward, y = num_cte)) +
  geom_boxplot(alpha = 0) +
  geom_jitter(alpha = 0.5, color = "green", width = 0.2, height = 0.2) +
  scale_y_continuous(breaks = c(0,2,4,6,8,10,12,14))+
  labs(title = "Boxplots of Number of Career Certifications Earned in terms of Whether Students Received Award",
       x = "Whether Students Received the Promise Scholarship",
       y = "Number of Certifications Earned") +
  theme_bw()
```


## Figure 6: Receipt of Promise Scholarship vs. Economic Status

```{r, warning=FALSE, message=FALSE}
# Prepare data set for later visualization
join_data2c <-demo_scholarship_final %>%
  filter(QualifiedforCorePromise == "yes") %>%
  group_by(EconDisad,EverReceivedPromiseAward) %>%
  dplyr::summarise(counts = n())

ggplot(join_data2c, aes(x = EverReceivedPromiseAward, fill = EconDisad)) +
  geom_bar(position = "fill")+
  labs(title = "Barplots of Whether Received Promise under Different Economic Status",
       y = "Proportion")+
  theme_bw()
```


## Figure 7: Cutoff vs. Qualification for Promise Scholarship

```{r}
# Prepare data set for later visualization
senior_gpa$RandomID <- as.character(senior_gpa$RandomID)
final_data <- attendance_rate %>%
  left_join(senior_gpa, by = c("RandomID"="RandomID"))
final_data_clean <- final_data %>%
  na.omit(final_data)

ggplot(data = final_data_clean, 
       aes(x = excused_pct, 
           y = CumulativeGPA, 
           color = QualifiedforCorePromise)) + 
  geom_point() +
  geom_hline(aes(yintercept=2.5), linetype="dashed") +
  geom_vline(aes(xintercept=0.9), linetype="dashed") +
  xlab("Attendance Rate") + 
  ylab("Cumulative GPA") +
  ggtitle("GPA vs Attendance Rate") +
  guides(color = guide_legend("Qualified for Promise"))+
  theme_bw()
```


## Figure 8: Cutoff vs. Receipt of Promise Scholarship

```{r}
# Filter qualified student in the data set
final_data_clean_qualifiedYes <- final_data_clean %>%
  filter(QualifiedforCorePromise == "yes")

ggplot(data = final_data_clean_qualifiedYes, 
       aes(x = excused_pct, 
           y = CumulativeGPA, 
           color = EverReceivedPromiseAward)) + 
  geom_point() +
  geom_hline(aes(yintercept=2.5), linetype="dashed") +
  geom_vline(aes(xintercept=0.9), linetype="dashed") +
  xlab("Attendance Rate") + 
  ylab("Cumulative GPA") +
  ggtitle("GPA vs Attendance Rate") +
  guides(color = guide_legend("Ever Received Promise"))+
  theme_bw()
```


