20170424 PSWRR.Rmd

---
output:
  pdf_document:
    fig_caption: yes
    keep_tex: yes
    latex_engine: xelatex
date: "`r format(Sys.time(), '%B %d, %Y')`"
title: "Postsecondary and Workforce Readiness Reports"
geometry: margin = .75in
fontfamily: mathpazo
fontsize: 11pt
spacing: double
# reference_docx: ./Footemp.docx
---

```{r setup, include = FALSE}
knitr::opts_chunk$set(echo = FALSE, warning = FALSE)

library(tidyverse)
library(scales)
library(extrafont)
library(extrafontdb)
library(ggthemes)
library(haven)
library(reshape2)

dist <- read_csv("K:/Research and Policy/projects/hs_feedback_report/Derived Data File/Mock District Data 20170412.csv") %>%
    filter(districtno == 350) %>%
    mutate(pct_enroll = round(100 * enroll_bin/hsgrad_denom, 1),
        pct_enroll_state = round(100 * enroll_state/hsgrad_state),
        GradYear = cohortyear + 4,
        pctElab = as.character(ifelse(pct_enroll >= 5, pct_enroll, "")),
        Elab = as.factor(ifelse(hsgrad_denom < 10, "< 10", enroll_bin)),
        institution_type = ordered(institution_level, levels = c("Four-year University", "Community College", "Technical College", "Did not enroll")))


distname <- subset(dist, page == "4" & institution_type == "Four-year University")

comp1 <- distname$similar_district_1
comp2 <- distname$similar_district_2
comp3 <- distname$similar_district_3
comp4 <- distname$similar_district_4
comp5 <- distname$similar_district_5
districtname <- distname$districtname
# rm(distname)

gradcohort <- read_csv("K:/ORP_accountability/data/2015_graduation_rate/grad_data_2014-15/2011 Cohort_110615.csv")


act_dist <- read_csv("K:/Research and Policy/projects/hs_feedback_report/Derived Data File/Average Highest ACT District.csv")  %>% 
  filter(system == 350) %>% 
  summarise(`Avg. ACT (Highest)`=round(composite,1))
```

A glimpse at the 2015 On-time Graduates

```{r Grad_Cohort_Table, echo=FALSE}
stategradrate<- gradcohort %>%
    filter(`Included in cohort` == "Y") %>%
 mutate(`N in Cohort` = n_distinct(`Student key` )) %>% 
  filter(`Completion type`==1) %>% 
  transmute(`N in Cohort`,`N Graduates` = n_distinct(`Student key`), `Graduation Rate` = `N Graduates`/`N in Cohort`, `% BHN` = if_else(`Race b` == "Y" | `Race i` == "Y" | `Ethnicity` == "H", 1,0), `% ED` =if_else(`Econ dis`=="Y",1,0), `% EL` = if_else(Ell == "Y", 1,0), `% SWD` = if_else(Sped =="Y",1,0)) %>% 
  summarise_all(funs(mean)) %>% summarise_at(vars(3:7), funs(percent)) %>% 
  as_data_frame()

dorder<-c("District Name" , "Graduation Rate", "% BHN","% ED", "% SWD", "% EL", "Avg. ACT (Highest)", "% EPSO") %>%
    as_data_frame()

epso_cov <- dist %>%
    filter(page == "9a") %>%
    group_by(districtno, districtname) %>%
    summarise(`Number who took EPSO`= max(epso_new), `Number Grads` = sum(hsgrad_denom)) %>%
    ungroup() %>%
    transmute(`% EPSO` = percent(`Number who took EPSO` / `Number Grads`))



gradcohort %>%
    filter(`District no` == 350 & `Included in cohort` == "Y") %>%
    mutate(`N in Cohort` = n_distinct(`Student key`)) %>% 
  filter(`Completion type`==1) %>% 
  transmute(`N in Cohort`, `N Graduates` = n_distinct(`Student key`), 
            `Graduation Rate` = `N Graduates`/`N in Cohort`, 
            `% CTE` = if_else(`Cte` == "Y", 1,0),
            `% BHN` = if_else(`Race b` == "Y" | `Race i` == "Y" | `Ethnicity` == "H", 1,0), 
            `% ED` =if_else(`Econ dis`=="Y",1,0), 
            `% EL` = if_else(Ell == "Y", 1,0), 
            `% SWD` = if_else(Sped =="Y",1,0)) %>%
  summarise_all(funs(mean)) %>% summarise_at(vars(3:8), funs(percent)) %>% bind_cols(epso_cov, act_dist) %>% mutate(`District Name`=districtname) %>%
  select(`District Name`,`Graduation Rate`, `% CTE`, `% BHN`,`% ED`, `% SWD`, `% EL`, `Avg. ACT (Highest)`, `% EPSO`) %>%  knitr::kable(col.names = c("District Name","Grad. Rate", "% CTE", "% BHN","% ED", "% SWD", "% EL", "Avg. ACT", "% EPSO"),align="lrrrrrrrr", caption="2015 Graduates")
```

```{r `District by Type P1`, fig.height=3.5,  fig.align = 'center'}
StateRate3b <- dist %>%
    filter(page == "3b" & institution_type!= "Did not enroll" & cohortyear == 2011) %>%
    select(pct_enroll_state, institution_type)

dist %>%
    filter(page == "3b" & institution_type!= "Did not enroll") %>%
    mutate(ps_pct_enroll = round(100 * enroll_bin/hsgrad_denom, 1)) %>%
    mutate(pctElab = as.factor(ifelse(ps_pct_enroll>= 5, ps_pct_enroll, "*")),
        Elab = as.factor(ifelse(hsgrad < 10, "< 10",enroll_bin))) %>%
    ggplot(aes(x = institution_type, y = ps_pct_enroll, fill = institution_type)) +
        geom_bar(stat = "identity", position = position_dodge(-1), width = .75, colour="black") +
        geom_text(aes(label = paste0(pctElab, "%\n N=", Elab), vjust = -.5)) +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text( family = "serif"),
            plot.title = element_text(family = "serif")) +
        scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595")) +
        annotate("text", x = StateRate3b$institution_type, y = 95,
            label = paste("TN:", StateRate3b$pct_enroll_state, "%"), fontface="italic") +
        ylab("Percent of high school graduates \n who enrolled in postsecondary institution") +
        xlab("Institution Type") +
        ggtitle("Postsecondary Enrollment by Institution Type: 2015 On-time Graduates") +
        ylim(0, 100)
```
*   Percent of most recent graduates who enrolled in any postsecondary institution, by type of PS institution
<!-- *   Regional Alignment of Program of Studies available -->

**Top Five Most Common Insitutions**
```{r `First Page List`, echo = FALSE}
# Do I want to show this by school?
dist %>%
    filter(page == "8" & enroll_bin >= 5 & institution_type!= "Did not enroll") %>%
    arrange(desc(enroll_bin)) %>%
    slice(1:5) %>%
    transmute(`Institution Name` = ps_institution_name, `Number of Enrollees` = enroll_bin) %>%
    knitr::kable()  
```

\newpage 

\newpage  

### Definitions  

**Graduation Year**: This report uses the term Graduation Year to monitor a cohort of students. This set of graduates would align with the On-Time Graduates with a regular diploma, based on when the group of students entered high school. For example, this report provides data on the 2011 freshman cohort, which is a group of students who entered high school in the fall of 2011. The vast majority of these students graduated in the spring of 2015. The most recent postsecondary enrollment data available is for the 2015 graduates fromn the 2011 freshman cohor. 

**Postsecondary Enrollment**: A student is identified as having enrolled in a postsecondary institution if they enroll within 12 months of expectred graduation year for students in the 2011 ninth grade cohort. Eligible institutions include Tennessee Board of Regents' schools, UT System, Tennessee Colleges of Applied Technology, TICUA institutions, and any non-Tennessee institution that shares enrollment information with the National Student Clearinghouse. 

**Postsecondary Remediation**: A student is identified as being assigned to a remedial course if they are designated as having non-zero remedial hours by their postsecondary institution. This information is only available from Tennessee public postsecondary institutions. At this time, the data are not able to be disaggregated at the subject level (e.g. we are unable to see whether a student took a remedial course in English or Math).

**Postsecondary Completion**: Postsecondary completion documentation is shown for students in the 2007 and 2008 ninth grade graduating cohorts, the earliest group of students that the Tennessee Longitudinal Data System can track from secondary into postsecondary. Any degree-granting public institution that submits completion information to THEC is included in this set of data points. For students in the 2007 ninth grade cohort, we display a 5 year completion rate and for students in the 2008 ninth grade cohort, we show a 4 year completion rate. As of `r format(Sys.time(), '%B %d, %Y')`, the most recent term with completion information available is Summer 2016. A student's most advanced degree is shown. 

### District comparisons  
For some topics, comparing a school district's data to statewide trends is not illuminating because the characteristics of the district's student population are very different from the characteristics of the student population across Tennessee. For ACT and postsecondary enrollment data, this report provides comparisons to Tennessee school districts whose populations share similar demographic characteristics. Be respectful in your use of other school districts' information; do not share this information publicly.

|Your District | Comparison 1 | Comparison 2 | Comparison 3 | Comparison 4 | Comparison 5 |
|:-------------|:---------:|:---------:|:---------:|:---------:|:---------:|
|`r districtname`| `r comp1` | `r comp2` | `r comp3` | `r comp4` | `r comp5` |

\newpage
## SECTION I: Postsecondary Enrollment 

### Postsecondary Enrollment over time
  *   What are the general enrollment trends in my district? Have these percentages changed over time? 
```{r Enrollment over time, fig.height = 3.75, fig.width = 6, fig.align = 'center'}

StateRate <- dist %>%
    filter(page == "3a" & cohortyear == 2011) %>%
    mutate(max_rate = if_else(pct_enroll >= pct_enroll_state, pct_enroll, pct_enroll_state)) %>%
    select(pct_enroll_state, GradYear, max_rate)

dist %>%
    filter(page == "3a")  %>% mutate(`Percent Enroll` = round(100*enroll_bin/hsgrad,1),
                                     pctElab = as.character(ifelse(`Percent Enroll` >= 5, `Percent Enroll`, "")),         Elab = as.factor(ifelse(hsgrad < 10, "< 10", enroll_bin))) %>% 
    ggplot(aes(x = as.factor(GradYear), y = `Percent Enroll`, fill = as.factor(GradYear))) +
        geom_bar(stat = "identity", position = position_dodge(-1), width = .75, colour = "black") +
        geom_text(aes(label = paste0(pctElab, "%\n N =", hsgrad), vjust = -.5)) +
        geom_hline(aes(yintercept = StateRate$pct_enroll_state), linetype=2) +
        # geom_bar(aes(x=as.factor(GradYear), y=pct_enroll_state),stat="identity",position=position_dodge(.5), width = .25) +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text( family = "serif"),
            plot.title = element_text(family = "serif")) +
        scale_fill_manual(name = "Graduation Year", values = c("#e02f11", "#24336b", "#959595")) +
        annotate("text", x = "2014", y = 95,
            label = paste0("2015 TN 12 mo. Enrollment Rate: ", StateRate$pct_enroll_state, "% (Dashed line)"), fontface = "italic") +
        ylab("Percent of graduates who enrolled \n in any postsecondary institution") +
        xlab("Graduation Year") +
        ggtitle("Postsecondary Enrollment by Graduation Year") +
        ylim(0, 100)
```
*   What type of institutions are my students enrolling in?  
```{r District by Type, fig.height = 3.75, fig.width = 6, fig.align = 'center'}
StateRate3b <- dist %>%
    filter(page == "3b" & institution_type!= "Did not enroll" & cohortyear == 2011) %>%
    select(pct_enroll_state, institution_type)

dist %>%
    filter(page == "3b" & institution_type!= "Did not enroll") %>%
    mutate(ps_pct_enroll = round(100 * enroll_bin/hsgrad_denom, 1)) %>%
    mutate(pctElab = as.factor(ifelse(ps_pct_enroll>= 5, ps_pct_enroll, "*")),
        Elab = as.factor(ifelse(hsgrad < 10, "< 10",enroll_bin))) %>%
    ggplot(aes(x = institution_type, y = ps_pct_enroll, fill = institution_type)) +
        geom_bar(stat = "identity", position = position_dodge(-1), width = .75, colour="black") +
        geom_text(aes(label = paste0(pctElab, "%\n N=", Elab), vjust = -.5)) +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text( family = "serif"),
            plot.title = element_text(family = "serif")) +
        scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595")) +
        annotate("text", x = StateRate3b$institution_type, y = 95,
            label = paste("TN:", StateRate3b$pct_enroll_state, "%"), fontface="italic") +
        ylab("Percent of high school graduates\n who enrolled in postsecondary institution") +
        xlab("Institution Type") +
        ggtitle("Postsecondary Enrollment by Institution Type:\n 2015 On-time Graduates") +
        ylim(0, 100)
```

\newpage
### Most Common Institutions

The following table shows the most common institutions that students from the 2015 graduating class enrolled in within 12 months of graduation.  

Consider opportunities for an external partnership with the institution and where students have been successful. Some questions that you may want to consider:

*   Are students enrolling in institutions that are nearest to your school? 
*   Have students taken dual enrollment courses or attended summer programs at these institutions while in high school?
*   Are students in your school aware of the majors offered at these institutions? 
*   Do your programs of study align with opportunities at these institutions?  
```{r most_common_institutions, echo = FALSE}
EnrOther<-dist %>%
    filter(page == "8" & institution_type!= "Did not enroll" & enroll_bin<5) %>%
    group_by(institution_type, hsgrad_denom) %>% 
    summarise(`Number of Enrollees` = sum(enroll_bin)) %>%
    ungroup() %>% 
    mutate(`Percent of Graduates`=round(100 * `Number of Enrollees`/hsgrad_denom, 1),`Institution Name` = if_else(institution_type =="Four-year University", "Other Four-year Universities", paste("Other ",institution_type,"s", sep=""))) %>% 
    group_by(`Institution Name`) %>% 
  arrange(desc(`Number of Enrollees`)) %>% select(-institution_type,-hsgrad_denom) 
# Do I want to show this by school?
dist %>%
    filter(page == "8" & enroll_bin >= 5 & institution_type!= "Did not enroll") %>%
    arrange(desc(enroll_bin)) %>%
    slice(1:15) %>%
    transmute(`Institution Name` = ps_institution_name, `Number of Enrollees` = enroll_bin, `Percent of Graduates`= pct_enroll) %>%
  arrange(desc(`Number of Enrollees`)) %>%
    bind_rows(EnrOther) %>%
    knitr::kable()
```

\newpage 

### District Comparisons  
*   How does the overall enrollment compare to similar districts in terms of overall postsecondary enrollment?  
*   How does the overall enrollment compare to the state overall enrollment (First and last bars)?  
*   How does the enrollment into different institution types compare to similar districts in terms of overall postsecondary enrollment?  
*   How does the enrollment into different institution types compare to the state distribution of enrollment into institution types (First and last bars)?  

```{r comparison_districts}
district_order <- dist %>%
    filter(page == "4" & institution_type == "Four-year University") %>%
    select(districtname, similar_district_1, similar_district_2, similar_district_3, similar_district_4, similar_district_5) %>%
    distinct() %>% 
    t() %>%
    as_data_frame() %>%
    magrittr::extract2(1) %>%
    c("State")

master <- dist %>%
    filter(page == "4" & institution_type != "Did not enroll") %>%
    transmute(institution_type, `Similar District` = districtname, pct_enroll, `NumEnroll`=sum(enroll_bin), pctElab = as.factor(ifelse(pct_enroll >= 5, pct_enroll, "")))

similar_1 <- dist %>%
    filter(page == "4" & institution_type != "Did not enroll") %>%
    transmute(institution_type, `Similar District` = similar_district_1, pct_enroll = round(100 * enroll_bin1/hsgrad_denom1, 1), `NumEnroll`=sum(enroll_bin1), pctElab = as.factor(ifelse(pct_enroll>= 5, pct_enroll, "")))

similar_2 <- dist %>%
    filter(page == "4" & institution_type != "Did not enroll") %>%
    transmute(institution_type, `Similar District` = similar_district_2, pct_enroll = round(100 * enroll_bin2/hsgrad_denom2, 1), `NumEnroll`=sum(enroll_bin2), pctElab = as.factor(ifelse(pct_enroll>= 5, pct_enroll, "")))

similar_3 <- dist %>%
    filter(page == "4" & institution_type != "Did not enroll") %>%
    transmute(institution_type, `Similar District` = similar_district_3, pct_enroll = round(100 * enroll_bin3/hsgrad_denom3, 1), `NumEnroll`=sum(enroll_bin3), pctElab = as.factor(ifelse(pct_enroll>= 5, pct_enroll, "")))

if (length(na.omit(dist$similar_district_4)) != 0) {
    similar_4 <- dist %>%
        filter(page == "4" & institution_type != "Did not enroll") %>%
        transmute(institution_type, `Similar District` = similar_district_4, pct_enroll = round(100 * enroll_bin4/hsgrad_denom4, 1), `NumEnroll`=sum(enroll_bin4), pctElab = as.factor(ifelse(pct_enroll>= 5, pct_enroll, "")))
}

if (length(na.omit(dist$similar_district_5)) != 0) {
    similar_5 <- dist %>%
        filter(page == "4" & institution_type != "Did not enroll") %>%
        transmute(institution_type, `Similar District` = similar_district_5, pct_enroll = round(100 * 
            as.numeric(enroll_bin5)/as.numeric(hsgrad_denom5), 1), NumEnroll = sum(as.numeric(enroll_bin5)), pctElab = as.factor(ifelse(pct_enroll >= 5, pct_enroll, "")))
}

state <- dist %>%
    filter(page == "4" & institution_type != "Did not enroll") %>%
    transmute(institution_type, `Similar District` = "State", pct_enroll = pct_enroll_state, NumEnroll = sum(enroll_state), pctElab = as.factor(ifelse(pct_enroll>= 5, pct_enroll, "")))

if (exists("similar_4") & exists("similar_5")) {
    all_districts <- bind_rows(master, similar_1, similar_2, similar_3, similar_4, similar_5, state) %>%
      filter(institution_type != "Did not enroll") %>% 
        select(-c(pctElab, NumEnroll)) %>% 
        spread(institution_type, pct_enroll) 
}

if (exists("similar_4") & !exists("similar_5")) {
    all_districts <- bind_rows(master, similar_1, similar_2, similar_3, similar_4,  state) %>%
      filter(institution_type != "Did not enroll") %>% 
      select(-c(pctElab, NumEnroll)) %>% 
        spread(institution_type, pct_enroll)
}

if (!exists("similar_4") & !exists("similar_5")) {
    all_districts <- bind_rows(master, similar_1, similar_2, similar_3, similar_4, state) %>%
      filter(institution_type != "Did not enroll") %>% 
        select(-c(pctElab, NumEnroll)) %>% 
        spread(institution_type, pct_enroll) %>% select(-institution_type)
}

# Generating labels
if (exists("similar_4") & exists("similar_5")) {
    all_districts_lab <- bind_rows(master, similar_1, similar_2, similar_3, similar_4, similar_5, state) %>%
      filter(institution_type != "Did not enroll") %>% 
        select(-pct_enroll) %>% 
        spread(institution_type, pctElab)
}

if (exists("similar_4") & !exists("similar_5")) {
    all_districts_lab <- bind_rows(master, similar_1, similar_2, similar_3, similar_4, state) %>%
      filter(institution_type != "Did not enroll") %>% 
        select(-pct_enroll) %>% 
        spread(institution_type, pctElab)
}

if (!exists("similar_4") & !exists("similar_5")) {
    all_districts_lab <- bind_rows(master, similar_1, similar_2, similar_3, similar_4, state) %>%
      filter(institution_type != "Did not enroll") %>% 
        select(-pct_enroll) %>% 
        spread(institution_type, pctElab)
}

all_districts_lab1 <- all_districts_lab %>%
    gather(institution_type, `Percent Enrolled`, -c(`Similar District`, NumEnroll))

all_districts %>%
      gather(institution_type, `Percent Enrolled`, -`Similar District`) %>%
    ggplot(aes(x = `Similar District`, y = `Percent Enrolled`, fill = institution_type)) +
    geom_bar(stat = "identity", colour = "black") +
    geom_text(aes(label = all_districts_lab1$`Percent Enrolled`), vjust = 1, position = "stack") +
    annotate("text", x= all_districts_lab$`Similar District`, y=95, label = paste0("N=", all_districts_lab$`NumEnroll`)) +
    theme_hc() +
    theme(axis.text.x = element_text(hjust = .75, angle = 15, family = "serif")) +
    scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595")) +
    ggtitle(paste("Comparison of",districtname,"to Similar Districts")) +
    scale_x_discrete(limits = district_order) +
    ylim(0, 100)

all_districts[match(district_order, all_districts$`Similar District`), ] %>%
    knitr::kable()
```

 \newpage

### School-level Enrollment  
*   How do the overall enrollment rates differ across schools in my district?
*   How does the distribution of students to different institution types compare across schools in my district?  

```{r Enrollment by School, fig.height = 5, fig.width = 8}

StateRate3c <- dist %>% 
    filter(page == "3c" & institution_type!= "Did not enroll" & cohortyear == 2011) %>%
    select(pct_enroll_state, institution_type, schoolname)

dist %>%
    filter(page == "3c" & institution_type!= "Did not enroll") %>%
    mutate(ps_pct_enroll = if_else(hsgrad_denom >= 10, round(100 * enroll_bin/hsgrad_denom, 1), NaN),
        pctElab = as.factor(ifelse(ps_pct_enroll >= 5 | hsgrad_denom < 10, ps_pct_enroll, "*")), 
        Elab = as.factor(ifelse(hsgrad_denom < 10, "< 10", enroll_bin))) %>%
    ggplot(aes(x = institution_type, y = ps_pct_enroll, fill = institution_type)) +
        geom_bar(stat = "identity", colour = "black", show.legend = FALSE) +
        geom_text(aes(label = paste0(pctElab, "%\n N=", Elab), vjust = -.5)) +
        theme_hc() +
        theme(axis.text.y = element_text( family = "serif"),
            axis.text.x = element_text(hjust = .75, angle = 15, family = "serif", face = "bold"),
            plot.title = element_text(family = "serif")) +
        facet_grid(. ~ schoolname) +
        scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595")) +
        geom_text(data=StateRate3c, aes(y=95, x=StateRate3c$institution_type,
            label = paste("TN: ", StateRate3c$pct_enroll_state, "%", sep="")), fontface="italic") +
        ylab("Percent of graduates who enrolled \n in any postsecondary institution") +
        xlab("Institution Type") +
        ggtitle("Postsecondary Enrollment by High School 2015 On-time Graduates") +
        ylim(0, 100)
```

### **Notes**  
Use this space to record any thoughts or questions about the overall levels of postsecondary enrollment in your district. 

\newpage 
## SECTION II: Postsecondary Enrollment by Subgroup

### Subgroup comparisons
*   To what extent do we see overall equitable access by race in the same school to postsecondary institutions? 
*   Within each school, to what extent are students of different racial backgrounds enrolling in different types of postsecondary institutions (e.g. a higher distribution of one group is enrolling in four-year universities than two year institutions)? 
*   If your district has more than one school, to what extent do the overall enrollment rates differ across schools in your districts for racial and ethnic groups?
*   If your district has more than one school, to what extent do enrollment rates for racial and ethnic subggroups into different types of postecondary institutions differ across schools?
*   Given this information, do you feel that all students are receiving the same opportunities in your district?

```{r subgroup_enrollment, fig.height = 9, fig.align = "center", warning=FALSE}
subgroup_order <- dist %>%
  filter(page %in% c("5a", "5c", "5e",  "5i")) %>% 
    select(page,subgroup) %>%
    as_data_frame() %>%
    mutate(ordervar = if_else(grepl('Non',subgroup),1, 2)) %>% 
  distinct() %>% 
  arrange(page,desc(ordervar)) %>% 
  select(-ordervar,-page) %>% 
    magrittr::extract2(1)

enr2015 <- dist %>%
    filter(page == "3a" & cohortyear == 2011) %>% 
    mutate(`Percent Enrolled` = enroll_bin/hsgrad) %>% 
    distinct()

sub2015 <-  dist %>%
    filter(page %in% c("5a", "5c", "5e", "5i")) %>%
  group_by(subgroup, schoolname) %>% 
  summarise(`Percent Enrolled Total` = sum(pct_enroll),`Percent Enrolled State` = sum(pct_enroll_state), `N Subgroup`=sum(hsgrad)) 

#Generate a new variable to create the "Did not enroll percentage"
dist %>%
    filter(page %in% c("5a", "5c", "5e", "5i")) %>%
  mutate(new_enroll = if_else(enroll_bin!=0, enroll_bin, hsgrad), 
         new_perc = round(100*new_enroll/hsgrad_denom,1),labPerc = as.character(if_else(new_perc<5,"" ,as.character(new_perc)))) %>%
  group_by(schoolname, subgroup) %>% 
    mutate(`Percent Enrolled` = round(sum(pct_enroll),0),`N Subgroup`=sum(hsgrad), `Percent Subgroup` = sum(new_perc)) %>%
  # left_join(sub2015, by= c("subgroup", "schoolname")) %>%   
  mutate(subgroup_label=ifelse(`N Subgroup`>=10,
                 paste(`Percent Enrolled`,"% of\n",`N Subgroup`," enr.", sep = ""),"")) %>% 
    ggplot(aes(x = subgroup, y = new_perc, fill = institution_type)) +
        geom_bar(stat = "identity", color = "black") +
        geom_text(aes(label = labPerc), vjust = 1.5, position = "stack") +
        geom_text(aes(x=subgroup , y= `Percent Subgroup`, label=subgroup_label), vjust= -.75)  + 
        geom_hline(aes(yintercept = 100*enr2015$`Percent Enrolled`), linetype =2) +
   facet_grid(schoolname ~ .) +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text(hjust = .75, angle = 15, family = "serif", face = "bold"),
            plot.title = element_text(family = "serif")) +
        scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595", "transparent"),
            limits = c("Four-year University", "Community College", "Technical College", "Did not enroll"), 
            guide = guide_legend(ncol=2)) +
      # annotate("text", x = "2014", y = enr2015$pct_enroll+5,
      #       label = paste0("District Average Enrollment Rate"), fontface = "italic") +
        ylab("Percent of graduates who enrolled \n in any postsecondary institution in 2015") +
        xlab("Subgroup by School") +
        scale_x_discrete(limits = subgroup_order) +
        ggtitle("Postsecondary Enrollment by Subgroup") +
        ylim(0, 101)

```

<!-- ```{r Subgroup Table, results=FALSE} -->
<!-- subgroup_order <- dist %>% -->
<!--   filter(page %in% c("5a", "5c", "5e",  "5i")) %>%  -->
<!--     select(page,subgroup) %>% -->
<!--     as_data_frame() %>% -->
<!--     mutate(ordervar = if_else(grepl('Non',subgroup),1, 2)) %>%  -->
<!--   distinct() %>%  -->
<!--   arrange(page,desc(ordervar)) %>%  -->
<!--   select(-ordervar,-page) %>%  -->
<!--     magrittr::extract2(1) -->

<!-- all_subgroups <- dist %>% -->
<!--     filter(page %in% c("5a", "5c", "5e", "5i")) %>% -->
<!--     group_by(schoolname,subgroup) %>%  mutate(`N Subgroup` = sum(hsgrad)) %>%  ungroup() %>%  -->
<!--   mutate(new_enroll = if_else(enroll_bin!=0, enroll_bin, hsgrad), new_perc = round(100*new_enroll/hsgrad_denom,1),labPerc = as.character(if_else(`N Subgroup` < 10,"***" ,as.character(new_perc))), `N Subgroup` = as.character(if_else(`N Subgroup` <10 ,"<10 Students" ,as.character(`N Subgroup`)))) %>% -->
<!--   select(schoolname, subgroup, institution_type, `N Subgroup`, labPerc) %>% -->
<!--   dcast(schoolname + subgroup + `N Subgroup` ~ institution_type)  %>%  -->
<!--   arrange(schoolname) %>% rename(`School Name`=schoolname, `Subgroup`=subgroup) -->

<!-- all_subgroups[match(subgroup_order, all_subgroups$Subgroup),]   %>%  knitr::kable() -->
<!-- ``` -->

<!-- *    Economic Disadvantage (School level)   -->
<!--     +   To what extent do we see equitable access by level of economic disadvantage in my district to postsecondary institutions? Given this information, do you feel that all students are receiving the same opportunities in your district? -->

<!-- ```{r ED Only, fig.height = 6, fig.width = 8, results='hide'} -->
<!-- dist %>% -->
<!--     filter(page == "5c" & hsgrad_denom >= 10 & institution_type!= "Did not enroll") %>% -->
<!--     ggplot(aes(x = econ_dis, y = pct_enroll, fill = as.factor(institution_type))) + -->
<!--         geom_bar(stat = "identity") + -->
<!--         geom_text(aes(label = pct_enroll), vjust = 1, position = "stack") + -->
<!--         theme_hc() + -->
<!--         theme(axis.text.y = element_text(family = "serif"), -->
<!--             axis.text.x = element_text(family = "serif", face = "bold"), -->
<!--             plot.title = element_text(family = "serif")) + -->
<!--         facet_grid(. ~ schoolname) + -->
<!--         scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595", "#000000")) + -->
<!--         ylab("Percent of graduates who enrolled \n in any postsecondary institution in 2015") + -->
<!--         xlab("Economic Disadvantage Status by School") + -->
<!--         ggtitle("Postsecondary Enrollment by Subgroup") + -->
<!--         ylim(0, 100) -->
<!-- ``` -->

\newpage

## SECTION III: Postsecondary Enrollment By Academic Achievement  

#### Postsecondary Enrollment By ACT Scores
*   Consider the distribution of ACT composite scores for the students in your district.  
    <!-- *   Students who score below a 17 often require learning support (remedial) courses in postsecondary. -->
    <!-- *   Students below a 21 fall below the "college-ready" threshold and are not eligible for the Hope Scholarship.   -->
    <!-- \newpage -->
<!-- *   To what extent do ACT Scores relate to the postsecondary enrollment of the students in your district?   -->
*   Is the percent of students who did not enroll in a postsecondary institution higher than you expected? 
*   To what extent do the ACT scores relate to the type of postsecondary institution into which a student enrolled?  

<!-- See Appendix for strategies to improve ACT performance and create a stronger postsecondary readiness culture in your school.  -->

```{r ACT_distribution, fig.height = 3.75, fig.width = 8}
dist %>%
    filter(page == "7a") %>%
    group_by(districtno, districtname, schoolname, schoolno, act_recode) %>%
    summarise(`Number at ACT score` = sum(hsgrad),`Number Grads` = max(hsgrad_denom)) %>%
    # group_by(schoolno) %>%
    # mutate(`Number Grads` = sum(`Number at ACT score`),
    mutate(`Percent at ACT score` = round(100 * `Number at ACT score`/`Number Grads`, 1), labPerc = as.character(if_else(`Percent at ACT score`<5,"" ,as.character(`Percent at ACT score`)))) %>% 
    select(districtname, districtno, schoolno, schoolname, act_recode,
        `Percent at ACT score`,labPerc) %>%
    ggplot(aes(x = act_recode, y = `Percent at ACT score`, fill = act_recode)) +
        geom_bar(stat = "identity", color="black") +
        geom_text(aes(label = paste(`Percent at ACT score`,"%", sep="")), vjust = -1, position = "stack") +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text(family = "serif", hjust = .5, angle = 15),
            plot.title = element_text(family = "serif")) +
        facet_grid(. ~ schoolname) +
        scale_fill_manual(name = "ACT Composite Score", values = c("#fb8072", "#e87722","#5d7975", "#d2d755","#2dccd3" )) +
        ylab("Percent of graduates who scored at \n at different ACT score bands in 2015") +
        xlab("Students' Highest ACT Composite Score") +
        ggtitle("Distribution of ACT Composite Scores") +
        ylim(0, 100)
```

```{r Figure7a, fig.height = 3.75, fig.width = 8}
dist %>%
    filter(page == "7a" & hsgrad_denom >= 5) %>%
    mutate(new_enroll = if_else(enroll_bin!=0, enroll_bin, hsgrad), new_perc = round(100*new_enroll/hsgrad_denom,1),labPerc = as.character(if_else(new_perc<5,"" ,as.character(new_perc)))) %>% 
  select(schoolname, institution_type,act_recode, pct_enroll, new_enroll, hsgrad, hsgrad_denom,new_perc,labPerc) %>%  
    ggplot(aes(x = act_recode, y = new_perc, fill = institution_type)) +
    geom_bar(stat = "identity", color="black") +
    geom_text(aes(label = labPerc), vjust = 1, position = "stack") +
    theme_hc() +
    theme(axis.text.y = element_text(family = "serif"),
          axis.text.x = element_text(family = "serif", hjust = .5, angle = 15),
          plot.title = element_text(family = "serif")) +
    facet_grid(. ~ schoolname) +
    scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595", "white"), 
                      limits=c("Four-year University", "Community College","Technical College", "Did not enroll"), 
            guide = guide_legend(ncol=2)) +
    ylab("Percent of graduates who enrolled \n in any postsecondary institution in 2015") +
    xlab("Students' Highest ACT Composite Score") +
    ggtitle("Postsecondary Enrollment by ACT Score") +
    ylim(0, 75)
```
    
* Other possible questions    
    +   To what extent does the relationship between ACT scores and postsecondary enrollment in your district differ from the state average?
    +   To what extent does the relationship between ACT scores and the type of postsecondary institution where students are enrolling in your district differ from the state average?
    
\newpage

## Section IV: Postsecondary Enrollment By Coursework  

#### Access to EPSOs (Early Postsecondary Opportunities)
*   To what extent do all students have access to rigorous coursework that can result in early postsecondary credit in the 2011 graduating cohort who graduated in spring 2015? 
*   What types of EPSOs are offered in your schools?
*   How are students placed into EPSOs in your schools?

```{r Figure9a, fig.height = 6, fig.width = 8}

epso <- dist %>%
    filter(page == "9a") %>%
    group_by(districtno, districtname, schoolname, schoolno) %>%
    summarise(`Number who took EPSO`= max(epso_new), `Number Grads` = sum(hsgrad_denom)) %>%
    ungroup() %>%
    transmute(districtname, districtno, schoolno, schoolname,
        Percent = `Number who took EPSO` / `Number Grads`, `EPSO type` = "Any EPS")
    
AP <- dist %>%
    filter(page == "9a") %>%
    group_by(districtno, districtname, schoolname, schoolno) %>%
    summarise(`Number who took AP`= max(ap_stu), `Number Grads` = sum(hsgrad_denom)) %>%
    ungroup() %>%
    transmute(districtname, districtno, schoolno, schoolname,
        `Percent` = `Number who took AP` / `Number Grads`, `EPSO type` = "AP Course")

DE <- dist %>%
    filter(page == "9a") %>%
    group_by(districtno, districtname, schoolname, schoolno) %>%
    summarise(`Number who took Dual Enrollment`= max(de_ever), `Number Grads` = sum(hsgrad_denom)) %>%
    ungroup() %>%
    transmute(districtname, districtno, schoolno, schoolname,
        Percent = `Number who took Dual Enrollment` / `Number Grads`, `EPSO type` = "DE Course")

EPS_ANY <- bind_rows(epso, AP, DE)

EPS_ANY %>%
    mutate(Percent = round(100 * Percent, 1)) %>%
    ggplot(aes(x = `EPSO type`, y = Percent, fill = `EPSO type`)) +
        geom_bar(stat = "identity") +
        geom_text(aes(label = Percent), vjust = -1, position = "stack") +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text(family = "serif", hjust = .5, angle = 15),
            plot.title = element_text(family = "serif")) +
        facet_grid(. ~ schoolname) +
        scale_fill_manual(name = "EPS course taking", values = c("#e87722","#5d7975", "#d2d755")) +
        ylab("Percent of graduates who participated in EPS courses \n in 2015") +
        xlab("EPS course taken") +
        ggtitle("Percent of Grads who took Early Postsecondary Course") +
        ylim(0, 100)
```

\newpage 

#### EPS Access by subgroup

*   To what extent does access to EPS courses differ by student race?

```{r Figure9b, fig.height=3.5}

epso1 <- dist %>%
    filter(page == "9b" & hsgrad >= 10) %>% 
    transmute(districtname, districtno, schoolno, schoolname, subgroup,
        Percent = epso_new/hsgrad_denom, `EPSO type` = "Any EPS")
  
AP1 <- dist %>%
    filter(page == "9b" & hsgrad >= 10) %>%
    transmute(districtname, districtno, schoolno, schoolname, subgroup,
        Percent = ap_stu/hsgrad_denom, `EPSO type` = "AP Course")

DE1 <- dist %>%
    filter(page == "9b" & hsgrad >= 10) %>%
    transmute(districtname, districtno, schoolno, schoolname, subgroup,
        Percent = de_ever/hsgrad_denom, `EPSO type` = "DE Course", `Number Grads` = hsgrad)
    
EPS_ANY1 <- bind_rows(epso1, AP1, DE1)
    
EPS_ANY1 %>%
    mutate(Percent = round(100 * Percent, 1)) %>%
    ggplot(aes(x = subgroup, y = Percent, fill = `EPSO type`)) +
        geom_bar(stat = "identity", position = "dodge") +
        geom_text(aes(label = Percent),  position = position_dodge(0.9), vjust = -1) +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text(family = "serif", hjust = .5, angle = 15),
            plot.title = element_text(family = "serif")) +
        facet_grid(. ~ schoolname) +
        scale_fill_manual(name = "EPS course taking", values = c("#e87722","#5d7975", "#d2d755")) +
        ylab("Percent of graduates who participated \n in EPS courses in 2015") +
        xlab("Race") +
        ggtitle("Percent of Grads who took Early Postsecondary Course") +
        ylim(0, 100)
```

*   To what extent does access to EPSOs differ by the economic disadvantage status of the students? 

```{r Figure9c, fig.height=3.5}

epso2 <- dist %>%
    filter(page == "9c" & hsgrad >= 10) %>%
    transmute(districtname, districtno, schoolno, schoolname, econ_dis,
        Percent = epso_new/hsgrad_denom, `EPSO type` = "Any EPS")

AP2 <- dist %>%
    filter(page == "9c" & hsgrad >= 10) %>%
    transmute(districtname, districtno, schoolno, schoolname, econ_dis,
        Percent = ap_stu/hsgrad_denom, `EPSO type` = "AP Course")

DE2 <- dist %>%
    filter(page == "9c" & hsgrad >= 10) %>%
    transmute(districtname, districtno, schoolno, schoolname, econ_dis,
        Percent = de_ever/hsgrad_denom, `EPSO type` = "DE Course", `Number Grads` = hsgrad)

EPS_ANY2 <- bind_rows(epso2, AP2, DE2)

EPS_ANY2 %>%
    mutate(Percent = round(100 * Percent, 1)) %>%
    ggplot(aes(x = econ_dis, y = Percent, fill = `EPSO type`)) +
        geom_bar(stat = "identity", position="dodge") +
        geom_text(aes(label = Percent),  position = position_dodge(0.9), vjust = -1) +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text(family = "serif", hjust = .5, angle = 15),
            plot.title = element_text(family = "serif")) +
        facet_grid(. ~ schoolname) +
        scale_fill_manual(name = "EPS course taking", values = c("#e87722","#5d7975", "#d2d755")) +
        ylab("Percent of graduates who participated \n in EPS courses in 2015") +
        xlab("Economic Disadvantage Status") +
        ggtitle("Percent of Grads who took Early Postsecondary Course") +
        ylim(0, 100)
```

\newpage
#### Postseconary Enrollment for EPS Students
*   To what extent do students who took EPS courses enroll in a postsecondary institution?
*   To what extent do students who took EPS courses enroll in different types of postsecondary institutions?

```{r Figure9d, fig.height = 3.75, fig.width = 6}
dist %>%
    filter(page == "9d") %>%
    group_by(schoolno) %>%
    mutate(`Number Grads` = sum(hsgrad)) %>%
    filter(`Number Grads` >= 10) %>% 
    ungroup() %>%
    transmute(districtname, districtno, schoolno, schoolname, institution_type,
        EPS_cat = ifelse(epso_new == 1, "EPS taken", "No EPS taken"),
        new_enroll = if_else(enroll_bin!=0, enroll_bin, hsgrad), new_perc = round(100*new_enroll/hsgrad_denom,1),labPerc = as.character(if_else(new_perc<5,"" ,as.character(new_perc))),
        `Percent Enroll` = round(100 * new_enroll / `Number Grads`, 1),
        `Number Grads`, label_percent = if_else(`Percent Enroll`<=5, "",as.character(`Percent Enroll`))) %>%
    ggplot(aes(x = EPS_cat, y = `Percent Enroll`, fill = institution_type)) +
        geom_bar(stat = "identity", color = "black") +
        geom_text(aes(label = label_percent), vjust = 1, position = "stack") +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text(family = "serif", hjust = .5, angle = 15),
            plot.title = element_text(family = "serif")) +
        facet_grid(. ~ schoolname) +
        scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595", "transparent"), 
                      limits=c("Four-year University", "Community College","Technical College", "Did not enroll"), 
            guide = guide_legend(ncol=2)) +
        ylab("Percent of graduates who enrolled in a \n PS institution by EPS in 2015") +
        xlab("Whether Student took EPS course") +
        ggtitle("Postsecondary Enrollment by EPS coursetaking") +
        ylim(0, 100)
```

* To what extent do Economically Disadvantaged students benefit from EPS courses?

```{r Figure9e, fig.height = 3.75, fig.width = 6}
dist %>%
    filter(page == "9e") %>%
    group_by(schoolno) %>%
    mutate(`Number Grads` = sum(hsgrad)) %>%
    filter(`Number Grads` >= 10 ) %>%
    ungroup() %>%
    transmute(districtname, districtno, schoolno, schoolname, institution_type, epso_frpl_cat,
new_enroll = if_else(enroll_bin!=0, enroll_bin, hsgrad), new_perc = round(100*new_enroll/hsgrad_denom,1),labPerc = as.character(if_else(new_perc<5,"" ,as.character(new_perc))),
        `Percent Enroll` = round(100 * new_enroll / `Number Grads`, 1), `Number Grads`, label_percent = if_else(`Percent Enroll`<=5, "",as.character(`Percent Enroll`))) %>%
    ggplot(aes(x = epso_frpl_cat, y = `Percent Enroll`, fill = institution_type)) +
        geom_bar(stat = "identity", color = "black") +
        geom_text(aes(label = label_percent), vjust = 1, position = "stack") +
        theme_hc() +
        theme(axis.text.y = element_text(family = "serif"),
            axis.text.x = element_text(family = "serif", hjust = .5, angle = 15),
            plot.title = element_text(family = "serif")) +
        facet_grid(. ~ schoolname) +
        scale_fill_manual(name = "Institution Type", values = c("#e02f11", "#24336b", "#959595", "transparent"), 
                      limits=c("Four-year University", "Community College","Technical College", "Did not enroll"), 
            guide = guide_legend(ncol=2)) +
        ylab("Percent of graduates who enrolled in a \n PS institution by EPS in 2015") +
        xlab("Whether Student took EPS course, \n by Economic Disadvantage status") +
        ggtitle("Postsecondary Enrollment by EPS coursetaking") +
        ylim(0, 100)
```

\newpage

## Persistence of all students (Not included in initial release)
*   Earning 1 year worth of credits in two years
*   Remediation (by subject) 
  *   WILL BE INCLUDED IN INITITAL REPORT

## Completion Rates by Institution Type (5 year (2007 cohort), 4 year (2008 cohort))

\newpage

# APPENDICES

## Appendix A: Strategies??? 
CCTE Team would develop a series of strategies that would target potential stories that would arise from the data

## Appendix B: CTE Data

For the next set of figures, we would focus on the same data that was provided at the school and district level, but focus at the concentrator level. We will compare across program areas where applicable. 


## Appendix C: Business Rules/Data Sources 

When we release data, we have to make sure that we clear set of business rules defined. 

# Preliminary timeline

### April 20
Based on CORE feedback, we would put together the final mock-ups to share within the department, to THEC, TOSS, etc
Bring in communications team for state level communication plans

### May 1
Integration of strategies

### May 15
Continuing TDOE feedback, CORE Feedback and maybe trusted partners
Data Validated

### June 
To Directors for feedback, Validation period begins with CORE Data Analysts and CTE Consultants

June 30: Deadline for receiving 2016 enrollment data from P20 for SSC reports

### June/July 
CORE Data analysts set up meetings with district teams
CTE trainings

### August
Superintendent Study Council Preparation