R/fetch_attendance.R

In ekholme-ccps/ccpsr: Tools for Working with CCPS Data

#' Fetch Attendance
#'
#' Retrieve the total days present and enrolled as well as % days attended for all students in a given year.
#'
#' This function works for either regular or year round schools. If you want both, use \code{\link{fetch_all_attendance}}
#'
#' @param year The year to query. Earliest year available is 2014 for regular and 2018 for year round.
#' @param school_type either "reg" or "yr". Use "reg" for regular schools and "yr" for year-round schools
#'
#' @return
#' @export
#'
#' @importFrom magrittr %>%
#'
#' @examples \dontrun{
#' fetch_attendance(2018)
#' }
fetch_attendance <- function(year, school_type = "reg") {

  #check school type
  if (!school_type %in% c("reg", "yr")) {
    rlang::abort(paste0("`school_type` must be either 'reg' or 'yr', not ", school_type))
  }

  #check year type
  if (!is.numeric(year)) {
    rlang::abort(paste0("`year` must be a single number, not ", typeof(year)))
  }

  #check year values
  if (year < 2014 & school_type == "reg") {
    rlang::abort(paste0("Regular school attendance is only available from 2014 onwards"))
  }
  if (year < 2018 & school_type == "yr") {
    rlang::abort(paste0("Year round school attendance is only available from 2018 onwards"))
  }

  #set database table stem
  stem <- if (school_type == "reg") {
    'CCPS_RE.RE_AttendanceMatrix_Regular_'
  } else 'CCPS_RE.RE_AttendanceMatrix_YearRound_'

  #set connection to database
  con <- ccpsr::set_con()

  #define query
  query <- function(year) {
    odbc::dbGetQuery(con, paste0('
                       SELECT *
                       FROM ', stem, year)) %>%
      janitor::clean_names() %>%
      tidyr::pivot_longer(cols = -c("student_id"),
                          names_to = "date",
                          values_to = "val") %>%
      dplyr::mutate(date = stringr::str_remove_all(date, "^x"),
                    date = lubridate::mdy(stringr::str_replace_all(date, "_", "/")),
                    att_type = dplyr::case_when(
                      val %in% c("A", "NK", "O", "R", "IL", "NU", "C", "U") ~ "Absent",
                      val %in% c("D", "T", "F", "H", "I", "Z", "X", "P") ~ "Present",
                      TRUE ~ "Other"
                    ))
  }

  #this will limit the attendance period to dates before 3/12/20 if the year is 2019.
  tmp <- if (year == 2019) {
    query(year) %>%
      dplyr::filter(date <= lubridate::mdy("3/12/2020"))
  } else {
    query(year)
  }

  days_enroll <- tmp %>%
    dplyr::filter(att_type != "Other") %>%
    dplyr::count(student_id, name = "days_enrolled")

  days_present <- tmp %>%
    dplyr::filter(att_type == "Present") %>%
    dplyr::count(student_id, name = "days_present")

  ret <- days_present %>%
    dplyr::left_join(days_enroll, by = "student_id") %>%
    dplyr::mutate(perc_present = days_present/days_enrolled)

  return(ret)

}


#' Fetch All Attendance
#'
#' A wrapper around \code{\link{fetch_attendance}} to get student-level attendance for both regular and year-round schools
#'
#' @param year Year to get attendance for. Earliest possible is 2018.
#'
#' @return
#' @export
#'
#' @examples \dontrun{
#' fetch_all_attendance(2018)
#' }
fetch_all_attendance <- function(year) {

  types <- c("reg", "yr")

  wrapper <- function(year, school_type) {
    fetch_attendance(year, school_type) %>%
      dplyr::mutate(sch_type = school_type)
  }

  ret <- map(types, ~wrapper(year = year, school_type = .x)) %>%
    dplyr::bind_rows()

  return(ret)
}