knitr::opts_chunk$set(echo = TRUE)

library(ggplot2)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(tidyr)
library(tidyverse)
## -- Attaching packages --------------------------------------- tidyverse 1.3.0 --
## v tibble  3.0.4     v stringr 1.4.0
## v readr   1.4.0     v forcats 0.5.0
## v purrr   0.3.4
## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()
library(knitr)

#reading in merged Retention File -- with only the columns needed

fileret <- "Retention.csv"

ret.sch <- read.csv(fileret)

### How to set your working directory 
## In quotation marks, ("C: /Users/BlaiseSevier/Desktop/R Work")

setwd("C:/Users/BlaiseSevier/Desktop/R Work")

els.k12 <-5296851
nonel.k12 <- 45625438

CRDC Survey Information

For more information about the school form see: https://www2.ed.gov/about/offices/list/ocr/docs/2017-18-crdc-school-form.pdf.

Change Code from 0s to 9s

#Code to change the -9s to 0s

ret.sch[ret.sch==-9]<-0

## important columns 27:30

TOT_RET_KG_M Total number of students retained in kindergarten: Calculated Male Total TOT_RET_KG_F Total number of students retained in kindergarten: Calculated Female Total SCH_RET_KG_LEP_M Students who were retained in Kindergarten: LEP Male SCH_RET_KG_LEP_F Students who were retained in Kindergarten: LEP Female

Non-English Learner Male Students Retained in Kindergarten

#Computes the total number of a non-English Learner male students in pre school 

#dat$NonELM<- (dat$TOT_ENR_M - dat$TOT_LEPENR_M)

ret.sch$NonELM <- (ret.sch$TOT_RET_KG_M - ret.sch$SCH_RET_KG_LEP_M)

sum(ret.sch$NonELM)
## [1] 49010
# 49,010 Non English learner males Retained in Kindergarten

Non-English Learner Female Students Retained in Kindergarten

#Total female non-EL 
#dat$NonELF <-dat$TOT_ENR_F-dat$TOT_LEPENR_F

ret.sch$NonELF <- (ret.sch$TOT_RET_KG_F - ret.sch$SCH_RET_KG_LEP_F)

sum(ret.sch$NonELF)
## [1] 30967
## There are 30,967 Non English Learner Females  Retained in Kindergarten

Total Non ELs in Retention in KG

#total non EL
#dat$NonEL<-dat$NonELF + dat$NonELM

ret.sch$NonEL <- ret.sch$NonELF + ret.sch$NonELM

non.el.ret_KG <- sum(ret.sch$NonEL)

non.el.ret_KG
## [1] 79977
#There are 79,977 non english learners in Retention

English Learners in Retention

English Learner Males in Retention

#Total Lep Male in Retention

#dat$ELMGT <- dat$SCH_GTENR_LEP_M

ret.sch$ELMret <- ret.sch$SCH_RET_KG_LEP_M

sum(ret.sch$ELMret)
## [1] 7164
#There are 7,164 English learner males in Retention in KG

English Learner Females in Retention

#Total LEP Female in GT
#dat$ELFGT <- dat$SCH_GTENR_LEP_F

ret.sch$ELFret <- ret.sch$SCH_RET_KG_LEP_F

sum(ret.sch$ELFret)
## [1] 4782
#There are 4,782 English learner females in Retention in KG

Total English Learners in Retention in KG

#dat$ELEnrol <- dat$TOT_LEPENR_M + dat$TOT_LEPENR_F

ret.sch$ELEnrolret <- ret.sch$ELMret + ret.sch$ELFret

els.ret.KG <- sum(ret.sch$ELEnrolret)

els.ret.KG
## [1] 11946

Grade 01

TOT_RET_G01_M Total number of students retained in grade 1: Calculated Male Total TOT_RET_G01_F Total number of students retained in grade 1: Calculated Female Total SCH_RET_G01_LEP_M Students who were retained in Grade 1: LEP Male SCH_RET_G01_LEP_F Students who were retained in Grade 1: LEP Female

Non ELs

non.el.ret_KG 
## [1] 79977
non.el.ret_01 <- sum((ret.sch$TOT_RET_G01_M-ret.sch$SCH_RET_G01_LEP_M),(ret.sch$TOT_RET_G01_F-ret.sch$SCH_RET_G01_LEP_F))

non.el.ret_02 <- sum((ret.sch$TOT_RET_G02_M-ret.sch$SCH_RET_G02_LEP_M),(ret.sch$TOT_RET_G02_F-ret.sch$SCH_RET_G02_LEP_F))

non.el.ret_03 <- sum((ret.sch$TOT_RET_G03_M-ret.sch$SCH_RET_G03_LEP_M),(ret.sch$TOT_RET_G03_F-ret.sch$SCH_RET_G03_LEP_F))

non.el.ret_04 <- sum((ret.sch$TOT_RET_G04_M-ret.sch$SCH_RET_G04_LEP_M),(ret.sch$TOT_RET_G04_F-ret.sch$SCH_RET_G04_LEP_F))

non.el.ret_05 <- sum((ret.sch$TOT_RET_G05_M-ret.sch$SCH_RET_G05_LEP_M),(ret.sch$TOT_RET_G05_F-ret.sch$SCH_RET_G05_LEP_F))

non.el.ret_06 <- sum((ret.sch$TOT_RET_G06_M-ret.sch$SCH_RET_G06_LEP_M),(ret.sch$TOT_RET_G06_F-ret.sch$SCH_RET_G06_LEP_F))
  
non.el.ret_07 <-  sum((ret.sch$TOT_RET_G07_M-ret.sch$SCH_RET_G07_LEP_M),(ret.sch$TOT_RET_G07_F-ret.sch$SCH_RET_G07_LEP_F))

non.el.ret_08 <- sum((ret.sch$TOT_RET_G08_M-ret.sch$SCH_RET_G08_LEP_M),(ret.sch$TOT_RET_G08_F-ret.sch$SCH_RET_G08_LEP_F))

non.el.ret_09 <- sum((ret.sch$TOT_RET_G09_M-ret.sch$SCH_RET_G09_LEP_M),(ret.sch$TOT_RET_G09_F-ret.sch$SCH_RET_G09_LEP_F))

non.el.ret_10 <- sum((ret.sch$TOT_RET_G10_M-ret.sch$SCH_RET_G10_LEP_M),(ret.sch$TOT_RET_G10_F-ret.sch$SCH_RET_G10_LEP_F))

non.el.ret_11 <- sum((ret.sch$TOT_RET_G11_M-ret.sch$SCH_RET_G11_LEP_M),(ret.sch$TOT_RET_G11_F-ret.sch$SCH_RET_G11_LEP_F))

non.el.ret_12 <- sum((ret.sch$TOT_RET_G12_M-ret.sch$SCH_RET_G12_LEP_M),(ret.sch$TOT_RET_G12_F-ret.sch$SCH_RET_G12_LEP_F))


sum(non.el.ret_KG +
      non.el.ret_01 +
non.el.ret_02 +
non.el.ret_03 +
non.el.ret_04 +
non.el.ret_05 +
non.el.ret_06 +
non.el.ret_07 +
non.el.ret_08 +
non.el.ret_09 +
non.el.ret_10 +
non.el.ret_11 +
non.el.ret_12)
## [1] 740046
non.el.grade <- c("Kindergarten",
"Grade 01",
"Grade 02",
"Grade 03",
"Grade 04",
"Grade 05",
"Grade 06",
"Grade 07",
"Grade 08",
"Grade 09",
"Grade 10",
"Grade 11",
"Grade 12")


non.el.number.retained <- c(non.el.ret_KG,
non.el.ret_01 ,
non.el.ret_02 ,
non.el.ret_03 ,
non.el.ret_04 ,
non.el.ret_05 ,
non.el.ret_06 ,
non.el.ret_07 ,
non.el.ret_08 ,
non.el.ret_09 ,
non.el.ret_10 ,
non.el.ret_11 ,
non.el.ret_12)

non.el.retained.df <- data.frame(non.el.grade, non.el.number.retained)

English Learners Retained

els.ret.KG<- sum(ret.sch$SCH_RET_KG_LEP_F) + sum(ret.sch$SCH_RET_KG_LEP_M)
els.ret.01<- sum(ret.sch$SCH_RET_G01_LEP_F) + sum(ret.sch$SCH_RET_G01_LEP_M)
els.ret.02<- sum(ret.sch$SCH_RET_G02_LEP_F) + sum(ret.sch$SCH_RET_G02_LEP_M)
els.ret.03<- sum(ret.sch$SCH_RET_G03_LEP_F) + sum(ret.sch$SCH_RET_G03_LEP_M)
els.ret.04<- sum(ret.sch$SCH_RET_G04_LEP_F) + sum(ret.sch$SCH_RET_G04_LEP_M)
els.ret.05<- sum(ret.sch$SCH_RET_G05_LEP_F) + sum(ret.sch$SCH_RET_G05_LEP_M)
els.ret.06<- sum(ret.sch$SCH_RET_G06_LEP_F) + sum(ret.sch$SCH_RET_G06_LEP_M)
els.ret.07<- sum(ret.sch$SCH_RET_G07_LEP_F) + sum(ret.sch$SCH_RET_G07_LEP_M)
els.ret.08<- sum(ret.sch$SCH_RET_G08_LEP_F) + sum(ret.sch$SCH_RET_G08_LEP_M)
els.ret.09<- sum(ret.sch$SCH_RET_G09_LEP_F) + sum(ret.sch$SCH_RET_G09_LEP_M)
els.ret.10<- sum(ret.sch$SCH_RET_G10_LEP_F) + sum(ret.sch$SCH_RET_G10_LEP_M)
els.ret.11<- sum(ret.sch$SCH_RET_G11_LEP_F) + sum(ret.sch$SCH_RET_G11_LEP_M)
els.ret.12<- sum(ret.sch$SCH_RET_G12_LEP_F) + sum(ret.sch$SCH_RET_G12_LEP_M)


el.grade <- c("els.ret.KG",
  "els.ret.01",
"els.ret.02",
"els.ret.03",
"els.ret.04",
"els.ret.05",
"els.ret.06",
"els.ret.07",
"els.ret.08",
"els.ret.09",
"els.ret.10",
"els.ret.11",
"els.ret.12"
)

el.number.retained <-
  c(els.ret.KG,
    els.ret.01,
els.ret.02,
els.ret.03,
els.ret.04,
els.ret.05,
els.ret.06,
els.ret.07,
els.ret.08,
els.ret.09,
els.ret.10,
els.ret.11,
els.ret.12
)


el.retained.df <- data.frame(el.grade,el.number.retained, non.el.number.retained )

kable(el.retained.df)
el.grade el.number.retained non.el.number.retained
els.ret.KG 11946 79977
els.ret.01 13717 62419
els.ret.02 7709 30857
els.ret.03 9476 36492
els.ret.04 2228 12853
els.ret.05 1868 12247
els.ret.06 1678 17107
els.ret.07 1896 22237
els.ret.08 2362 23094
els.ret.09 24739 143029
els.ret.10 17529 108739
els.ret.11 10801 70577
els.ret.12 17558 120418 
## Number of ELs retained KG-Grade 12

tot.el.retained <- sum(el.retained.df$el.number.retained)


## Number of Non ELs retained KG-Grade 12 

tot.non.el.retained <- sum(el.retained.df$non.el.number.retained)

Percentages

#Percentage of ELs Retained 

tot.el.retained/els.k12*100
## [1] 2.331706
#Percentage of ELs Retained 

tot.non.el.retained/nonel.k12*100
## [1] 1.622003