dc_school_report_cards.R

## LOAD LIBRARIES
library(dplyr)
library(tidyr)
library(httr)
library(readxl)

## CUSTOM FUNCTION TO READ ALL SHEETS OF AN EXCEL FILE INTO A LIST OF DATA FRAMES OR TIBBLES THANKS STACK OVERFLOW!!
## https://stackoverflow.com/questions/12945687/read-all-worksheets-in-an-excel-workbook-into-an-r-list-with-data-frames
read_excel_allsheets <- function(filename, tibble = FALSE) {
  sheets <- excel_sheets(filename)
  x <- lapply(sheets, function(X) read_excel(filename, sheet = X))
  if(!tibble) x <- lapply(x, as.data.frame)
  names(x) <- sheets
  x
}

## URL OF THE SCHOOL REPORT CARD DATA
url <- 'https://osse.dc.gov/sites/default/files/dc/sites/osse/page_content/attachments/2018%20DC%20School%20Report%20Card%20and%20STAR%20Framework%20Public%20Data_2.xlsx'

## CREATE A TEMPORARY EXCEL FILE IN MEMORY
tmp <- tempfile(fileext = ".xlsx")

## GET THE FILE FROM THE WEB AND WRITE IT TO THE TEMPORARY EXCEL FILE
GET(url, write_disk(tmp, overwrite = TRUE))

## READ IN ALL THE EXCEL FILES...AS TIBBLES!
osse <- read_excel_allsheets(tmp, tibble = TRUE)

getDataSets <- function() {
  return(
    osse$`Data Notes` %>%
      select(-1) %>%
      slice(c(-1:-2,-16:-20)) %>%
      setNames(c('dataset_name', 'dataset_description')) %>%
      as.data.frame
  )
}

getDataNotes <- function() {
  return(
    osse$`Data Notes` %>%
      slice(16:20) %>%
      select(2) %>%
      unlist %>%
      unname
  )
}

getDataDictionary <- function() {
  return(
    osse$`Data Dictionary` %>%
      setNames(
        osse$`Data Dictionary` %>%
          slice(1) %>%
          unlist %>%
          unname %>%
          tolower %>%
          gsub(" ", "_", .)
      ) %>%
      select(-6) %>%
      slice(-1) %>%
      rename(dataset_name = sheet) %>%
      filter(!is.na(dataset_name)) %>%
      as.data.frame
  )
}

getSchoolDirectory <- function() {
  return(
    osse$`School Directory` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      as.data.frame
  )
}

getSchoolEnrollment <- function() {
  return(
    osse$Enrollment %>%
      spread(Metric, `Metric Score`) %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      mutate_at(vars(contains("enrollment"), school_year), funs(as.numeric(.))) %>%
      mutate(grade = ifelse(is.na(grade), 'All Grades', grade),
      percent_enrollment = ifelse(is.na(percent_enrollment), 1, percent_enrollment / 100)) %>%
      select(9,1:6,8:7,10:11) %>%
      as.data.frame
  )
}

getSchoolSTARMetrics <- function() {
  return(
    osse$`STAR Metric Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      mutate(school_year = 2017) %>%
      select(17,1:16) %>%
      as.data.frame
  )
}

getSchoolSTARSubgroups <- function() {
  return(
    osse$`STAR Student Group Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      as.data.frame
  )
}

getSchoolSTARFrameworks <- function() {
  return(
    osse$`STAR Framework Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      mutate(school_year = 2017) %>%
      select(14,1:13) %>%
      as.data.frame
  )
}

getSchoolSTARScores <- function() {
  return(
    osse$`STAR Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      mutate(school_year = 2017) %>%
      select(9,1:8) %>%
      as.data.frame
  )
}

getSchoolReportCards <- function() {
  return(
    osse$`School Report Card Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      select(11,1:10) %>%
      mutate_at(vars(contains("metric_"), school_year), funs(as.numeric(.))) %>%
      as.data.frame
  )
}

getLEAReportCards <- function() {
  return(
    osse$`LEA Report Card Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      select(6,1:5) %>%
      mutate_at(vars(contains("metric_"), school_year), funs(as.numeric(.))) %>%
      as.data.frame
  )
}

getLEASTARMetrics <- function() {
  return(
    osse$`LEA STAR Metric Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      select(7,1:6) %>%
      mutate_at(vars(contains("metric_"), school_year), funs(as.numeric(.))) %>%
      as.data.frame
  )
}

getLEASTARSubgroups <- function() {
  return(
    osse$`LEA Student Group Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      select(6,1:5) %>%
      mutate_at(vars(contains('score'), school_year), funs(as.numeric(.))) %>%
      as.data.frame
  )
}

getDCReportCards <- function() {
  return(
    osse$`State Report Card Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      select(4,1:3) %>%
      mutate_at(vars(contains("metric_"), school_year), funs(as.numeric(.))) %>%
      as.data.frame
  )
}

getDCSTARMetrics <- function() {
  return(
    osse$`State STAR Metric Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      select(5,1:4) %>%
      mutate_at(vars(contains("metric_"), school_year), funs(as.numeric(.))) %>%
      as.data.frame
  )
}

getDCSTARSubgroups <- function() {
  return(
    osse$`State Student Group Scores` %>%
      setNames(gsub(" ", "_", names(.) %>% tolower)) %>%
      select(4,1:3) %>%
      mutate_at(vars(contains('score'), school_year), funs(as.numeric(.))) %>%
      as.data.frame
  )
}