```{r setup, include=FALSE}
# This is a good place to put libraries required for future work
knitr::opts_chunk$set(echo = TRUE, cache=T)
set.seed(09092020)
options(scipen=999)
library(tidyverse)    #Loads the tidyverse suite of packages
library(xtable)       #Alternate to Kable package for printing html tables
```

# Introduction

The [Cleveland school district](http://www.clevelandmetroschools.org/) is made up of 20 high schools uniquely identified by numbers 1 to 20. Within each high school, a random sample of 20 students are selected and are uniquely identified by numbers 1 to 20. All 400 students selected are assessed on their mathematics skills based on district designed standardized tests in the years 2017 and 2018. The scores and corresponding percentiles of these selected students for both years are simulated in the following R code.

```{r,echo=T}
school.id=rep(1:20,each=20*2)
student.id=rep(rep(1:20,each=2),20)
type=rep(c("Score","Percentile"),20*20)
score2017=round(rnorm(20*20,mean=50,sd=10),0)
percentile2017=round(100*pnorm(score2017,mean=mean(score2017),sd=sd(score2017)),0)
score2018=round(rnorm(20*20,mean=75,sd=4),0)
percentile2018=round(100*pnorm(score2018,mean=mean(score2018),sd=sd(score2018)),0)
value2017=c(rbind(score2017,percentile2017))
value2018=c(rbind(score2018,percentile2018))

untidy.school = tibble(
                  school=school.id,
                  student=student.id,
                  type=type,
                  value2017=value2017,
                  value2018=value2018) %>% 
                filter(!(school==1 & student==4)) %>% filter(!(school==12 & student==18)) %>%
                mutate(value2018=ifelse((school==1 & student==3)|(school==15 & student==18)|
                                          (school==5 & student==12),NA,value2018))
```

Below is an HTML table generated using the `xtable` package in R. For more information regarding this package, see the [xtable gallery](https://cran.r-project.org/web/packages/xtable/vignettes/xtableGallery.pdf). The R code in the code chunk converts an R data frame object to an HTML table. HTML table attributes can be specified within the function `print()`. The code chunk option `echo=F` prevents the code from showing and the option `results="asis"` ensures that the resulting HTML table is displayed when knitted to HTML. The table provides a preview of the first 10 rows of the simulated data.

```{r,echo=F,results="asis"}
tab.untidy.school=untidy.school %>%
                  head(10) %>%
                  xtable(digits=0,align="cccccc")

print(tab.untidy.school,type="html",include.rownames=F,
      html.table.attributes="align='center',
                             rules='rows',
                             width=50%,
                             frame='hsides',
                             border-spacing=5px"
      )
```

```{r}
head(untidy.school,10)
```

The data is not recorded data in a format that is immediately usable. Using our understanding of the `tidyr` package, we can easily convert this table into a form that is useful for data analysis.

# Part 1: Creation of a Unique Student ID

The variable `school` uniquely identifies the school, but the variable `student` only uniquely identifies the student within the school. The problem is best illustrated by the `filter()` function in `dplyr`.

```{r}
untidy.school %>% filter(student==1) %>% head(4)
```

The subsetted table contains scores and percentiles for two completely different children identified by `student==1`. We need to create a unique identifier for each student in the Cleveland school district. The `unite()` function can be utilized to create a new variable called `CID` by concatenating the identifiers for `school` and `student`. We want `CID` to follow the general form *SCHOOL.STUDENT*. Create a new tibble called `untidy2.school` that fixes this problem without dropping the original variables `school` or `student`. Read the documentation for `unite()` either by searching on google or using `?unite` to prevent the loss of original variables in the creation of a new variable. 

```{r,eval=F}
untidy2.school = untidy.school %>%
                    unite(CID,school,student,MORE)
glimpse(untidy2.school)
```

# Part 2: Gather Variables With Yearly Values

The variables `value2017` and `value2018` contain the scores and percentiles for two different years. In a new tibble called `untidy3.school`, based on `untidy2.school`, we want to create a new variable called `Year` and a new variable called `Value` that display the year and the result from that year, respectively. The variable `Year` should be a numeric vector containing either *2017* or *2018*. The most efficient way to modify the data in this manner is to start by renaming `value2017` and `value2018` to nonsynctactic names `2017` and `2018`. Remember that you need to surround nonsyncactic names with backticks to achieve this result. 

```{r,eval=F}
untidy3.school = untidy2.school %>%
                    rename(NEWVAR=OLDVAR,NEWVAR=OLDVAR) %>%
                    pivot_longer('2017':'2018',names_to=FILL,values_to=FILL) %>%
                    mutate(VAR=as.integer(VAR))
glimpse(untidy3.school)
```

# Part 3: Spread Type of Value Into Multiple Columns

The variable `type` in `untidy3.school` indicates that two completely different variables are contained in the recently created variable called `Value`. Both the scores and percentiles of students are contained in `Value`. Using the function `spread()` we can create two new variables, `Score` and `Percentile`, that display the information contained in `Value` in separate columns. Using `untidy3.school`, create a new tibble called `tidy.school` that accomplishes these tasks.

```{r,eval=F}
tidy.school = untidy3.school %>%
                    pivot_wider(names_from=FILL,values_from=FILL) 
glimpse(tidy.school)
```

# Part 4: Missing Data Analysis

The original data contains explicitly missing and implicitly missing values. Instances of both can be visibly seen in the first ten observations. Below is a table showing the first 10 observations in the cleaned dataset we called `tidy.school`. To appropriately, view this we have to sort our observations by `school` and `student` as seen in the original dataset `untidy.school`.

```{r,echo=F,results="asis",eval=F}
tab.tidy.school = tidy.school %>%
                  arrange(school,student) %>%
                  head(10) %>%
                  xtable(digits=0,align="ccccccc")

print(tab.tidy.school,type="html",include.rownames=F,
      html.table.attributes="align='center',
                             rules='rows',
                             width=50%,
                             frame='hsides',
                             border-spacing=5px"
      )
```

```{r, eval=F}
head(tidy.school,10)
```

Based on the table above, you can see that student *3* from school *1* has a missing score and percentile for the year 2018. This is an example of explicitly missing information. 

Based on the table above, you can see that student *4* from school *1* is clearly missing scores and percentiles from both years *2017* and *2018*. This is an example of implicitly missing information.  

Use the `complete()` function to convert all implicitly missing to explicitly missing. Create a new table called `tidy2.school` that reports missing values as `NA` for all combinations of school, student, and year.

```{r,eval=F}
tidy2.school=tidy.school %>%
  complete(VARIABLES)
```

The first 10 rows of `tidy2.school` are displayed below.

```{r,results="asis",eval=F}
tab.tidy2.school = tidy2.school %>%
  head(10) %>%
  xtable(digits=0,align="ccccccc")

print(tab.tidy2.school,type="html",include.rownames=F,
      html.table.attributes="align='center',
      rules='rows',
      width=50%,
      frame='hsides',
      border-spacing=5px"
)
```

```{r, eval=F}
head(tidy2.school, 10)
```

If you inspect the first 10 rows of `tidy2.school`, you should see that the variable `CID` is missing for student *4* from school *1* even though we know that this students unique district ID should be *"1.4"*. Using the pipe `%>%`, combine all previous statements in an order where this will not occur. Create a tibble named `final.tidy.school` using a chain of commands that begins with calling the original tibble `untidy.school`

```{r,eval=F}
final.tidy.school = untidy.school %>%
                      MORE %>%
                      MORE %>%
                      ...
```

# Part 5: Summarizing Figures

The figure below uses boxplots to show the distribution of scores in the 20 schools for the years 2017 and 2018. How would you interpret it?

```{r,eval=F}
ggplot(final.tidy.school) +
  geom_boxplot(aes(x=as.factor(Year),y=Score,fill=as.factor(school))) + 
  guides(fill=F)+
  theme_minimal()
```

Using different colors for each student, the next two pictures show the change in test scores and percentiles for all students (without missing values) sampled from the district. Both of these pictures are necessary in understanding the improvement in mathematical knowledge on the student level. As you can see, they are very different from each other. Hypothesize a reason that would have caused this phenomenon to occur.

```{r,eval=F}
ggplot(final.tidy.school) + 
  geom_line(aes(x=Year,y=Score,color=as.factor(CID))) +
  guides(color=F) +
  scale_x_discrete(breaks=c(2017,2018),labels=c(2017,2018)) +
  theme_minimal()

ggplot(final.tidy.school) + 
  geom_line(aes(x=Year,y=Percentile,color=as.factor(CID))) +
  guides(color=F) +
  scale_x_discrete(breaks=c(2017,2018),labels=c(2017,2018)) +
  theme_minimal()
```


