KIPP Chicago attendance approach

attendance.R

library(tidyverse)      # tidyvese
library(silounloadr)    # KIPP Chicago data acess package 
library(lubridate)      # data stuff
library(janitor)        # munging stuff
lirbrary(googlesheets4) # for tossing the data into a Google Sheet

# Need to get get the 2 digit PowerSchool Year id (2019-2020  = 29)
# this function returns the 4 digit year id (2019-2020 = 2900) and divides by 100 to get 2 digit
ps_yearid_4_years_ago <- silounloadr::calc_ps_termid(2019-3)/100

# This gets everyone who was enrolled on each calendar day. It's s record for every student on that data
membership <- get_powerschool("ps_membership_reg") %>%
  select(studentid, schoolid, date = calendardate, yearid) %>%
  filter(yearid>=ps_yearid_4_years_ago)

# This gets _recorded_ attendence codes. If a student is present then they won't appear in this table.  They 
# only appear if the were absetn, suspended, hospitalized, late, left early, weere leate _and_ left early_
attendance <- get_powerschool("attendance") %>%
  select(schoolid, studentid, date = att_date, yearid, attendance_codeid, att_mode_code, ada_value_code,
         ada_value_time, yearid) %>%
  filter(yearid>=ps_yearid_4_years_ago, 
         att_mode_code == "ATT_ModeDaily") # there are many ways to record attendnace (by period, by day).  This is daily.


# This has the actual codes (A, S, I ...) and their descriptions (Absent, Out of School Suspensions, In-school Suspensions, ...)
att_codes <- get_powerschool("attendance_code") %>%
  select(attendance_codeid = id, att_code, description)

# Just pulling a subest of current studnets here 
stus_8th <- get_powerschool("students") %>%
  filter(grade_level==8) %>%
  select(studentid = id, student_number, last_name, first_name, schoolid)

# Leffj oin membership (evryone) with the absence data
mem_att <- membership %>%
  left_join(attendance, by = c("yearid", "date", "schoolid", "studentid")) %>%
  left_join(att_codes, by = c("attendance_codeid")) %>%
  inner_join(stus_8th %>% select(studentid), by = "studentid") %>%
  collect() #pull the data down from the warehouse.
  
View(mem_att)

#the NAs _and_ blanks (i.e., "") here indicate present days (so does P, of which there's only one student in these data)
tabyl(mem_att, att_code) 

# Let's change the NAs to ""
mem_att %>%
  mutate(att_code = if_else(is.na(att_code), "", att_code)) %>%
  tabyl(att_code)
  
# Let's calcualte ADA over 4 years for 8th graders

# we only used it above to filter data in the warehouse before collecting it
# So let's pull these student data down
stus_8th <- collect(stus_8th) 

perfect_ada <- mem_att %>%
  dplyr::mutate(att_code = if_else(is.na(att_code), "", att_code),                  # change NAS to ""
         present = att_code %in% c("", "I", "T", "H", "P", "L", "E"),               # All of these codes = Present
         present_value2 = as.integer(present),                                      # Change True/False to 1/0
         present_value = if_else(att_code == "H", 0.5, as.numeric(present_value2)), # hopsitalization = 1/2 day present
         enrolled= 1) %>%                                                           # all students in this list are enrolle
  group_by(studentid) %>%
  dplyr::summarise(ada = sum(present_value)/sum(enrolled),                          # Average daily attendance rate
            enrolled = sum(enrolled),                                               # total days enrolled
            present = sum(present_value),                                           # total days present
            years = n_distinct(yearid)) %>%                                         # count of school years in which student was enrolled 
  filter(ada == 1) %>%                                                              # filter for perfect attendence 
  inner_join(stus_8th, by = "studentid") %>%                                        # add student info 
  arrange(desc(ada), desc(years), last_name, first_name)                            # sort nicely
  
  # now you can toss into a Google sheet
  ss <- gs4_create("19-20 Perfect Attendance Student List", sheets = list(KOA = percect_ada_koa))



percect_ada_koa <- percect_ada %>% 
  filter(schoolid == 400180) %>%
  select(student_number,
         last_name,
         first_name,
         n_years = years,
         days_present = present,
         days_enrolled = enrolled,
         ADA = ada)

percect_ada_koa