Data Processing: Varsity Tutors, Kent SD

Author

Alexis Davila

Clean Data

Import

Packages selected for inspecting and cleaning Varsity Tutors, district data from Kent School District.

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

Import roster.6/29/26 Note: Using latest roster, Kent SD_Varsity Tutors request 5-12-2026.xlxs.

roster <- read_csv("roster_vt_kent.csv")

Rows: 5634 Columns: 19
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (13): Grade, School, MOY i-Ready Math Composite Scaled Score*, MOY i-Rea...
dbl  (5): Student Number, Special Ed, MLE, Low Income, LAP
lgl  (1): Treatment

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Inspect data variables. Data file appears to contain all requested elements. MLE, or multilingual education status, presents in place of ELL status. Low Income presents in place of Free/Reduced Price Lunch status. Sped, or Special Education, presents in place of IEP status.

colnames(roster)

 [1] "Student Number"                          
 [2] "Grade"                                   
 [3] "School"                                  
 [4] "Treatment"                               
 [5] "MOY i-Ready Math Composite Scaled Score*"
 [6] "MOY i-Ready Math Math Test Date*"        
 [7] "EOY i-Ready Math Composite Scaled Score*"
 [8] "EOY i-Ready Math Math Test Date*"        
 [9] "MOY i-Ready ELA Composite Scaled Score*" 
[10] "MOY i-Ready ELA Test Date*"              
[11] "EOY i-Ready ELA Composite  Scaled Score*"
[12] "EOY i-Ready ELA Test Date*"              
[13] "Gender Code"                             
[14] "Special Ed"                              
[15] "MLE"                                     
[16] "Low Income"                              
[17] "Race/Ethnicity"                          
[18] "LAP"                                     
[19] "MLE Level"

Change column headers for roster.

roster = roster %>%
  rename(
    student_id = "Student Number",
    grade = "Grade", 
    school = "School", 
    treat = "Treatment", 
    
    moy_math_comp = "MOY i-Ready Math Composite Scaled Score*", 
    moy_math_date = "MOY i-Ready Math Math Test Date*", 
    moy_ela_comp = "MOY i-Ready ELA Composite Scaled Score*", 
    moy_ela_date = "MOY i-Ready ELA Test Date*", 
    
    eoy_math_comp = "EOY i-Ready Math Composite Scaled Score*", 
    eoy_math_date = "EOY i-Ready Math Math Test Date*", 
    eoy_ela_comp = "EOY i-Ready ELA Composite  Scaled Score*", 
    eoy_ela_date = "EOY i-Ready ELA Test Date*", 
    
    gender = "Gender Code", 
    sped = "Special Ed", 
    mll = "MLE", #multilingual education/multilingual learners
    mll_lvl = "MLE Level",
    low_inc = "Low Income", 
    race_eth = "Race/Ethnicity", 
    low_ap = "LAP" #low academic performance
    
  )
colnames(roster)

 [1] "student_id"    "grade"         "school"        "treat"        
 [5] "moy_math_comp" "moy_math_date" "eoy_math_comp" "eoy_math_date"
 [9] "moy_ela_comp"  "moy_ela_date"  "eoy_ela_comp"  "eoy_ela_date" 
[13] "gender"        "sped"          "mll"           "low_inc"      
[17] "race_eth"      "low_ap"        "mll_lvl"

Inspect schools.

table(roster$school)


 Canyon Ridge Middle Cedar Heights Middle       Mattson Middle 
                 834                  844                  763 
       Meeker Middle      Meridian Middle    Mill Creek Middle 
                 832                  637                  840 
    Northwood Middle 
                 884

Create school_num for each school.

roster = roster %>%
  mutate(school_num = recode(school,
                             "Canyon Ridge Middle" = 1,
                             "Cedar Heights Middle" = 2,
                             "Mattson Middle" = 3, 
                             "Meeker Middle" = 4, 
                             "Meridian Middle" = 5, 
                             "Mill Creek Middle" = 6, 
                             "Northwood Middle" = 7))

table(roster$school_num)


  1   2   3   4   5   6   7 
834 844 763 832 637 840 884

Inspect duplicates

Identify duplicate student records within roster file. No duplicates to report.

# frequency table of student_id occurrences
id_counts_roster = roster %>%
  group_by(student_id) %>%
  summarise(n = n()) %>%
  arrange(desc(n))

# filter only IDs that appear > 1
dupes_roster = id_counts_roster %>%
  filter(n > 1)

# view results
print(dupes_roster)

# A tibble: 0 × 2
# ℹ 2 variables: student_id <dbl>, n <int>

#export
write.csv(dupes_roster, file = "duplicate_ids_roster_vt_kent.csv", row.names = FALSE)

6/29/2026 Note: Not Applicable after importing 5/12 roster.Archive code. // Inspect duplicate student records in roster.

#dup_preview_roster = roster %>%
  #group_by(student_id) %>%
  #filter(n() > 1) %>%
  #ungroup()

#print(dup_preview_roster)

6/29/2026 Notes: Archive code. // Decision: keep first occurrence of student ID based on current row order.

roster_unique = roster
#roster_unique = roster %>%
  #distinct(student_id, .keep_all = TRUE)

#print(roster_unique)

Merge Usage Data to District Data

Clean Kent Usage Data

Import roster from VT that details which students have accounts in their system (cross walk file).

#import ID crosswalk from VT
vt_roster = read_csv("vt_roster_all_districts.csv")

Rows: 1496 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (2): District, School
dbl (2): District ID, Varsity Tutors ID

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

# rename vars
vt_roster = vt_roster %>%
  rename(
    district_vt = "District",
    school_vt = "School",
    student_id = "District ID", 
    vt_acct_id = "Varsity Tutors ID"
  )

Import usage data from VT.

#import kent usage data from VT
vt_usage_kent = read_csv("vt_usage_kent.csv")

Rows: 1792 Columns: 10
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (3): Sch_name*, Sch_BOY_date*, Sch_EOY_date
dbl (7): Stu_ID_VT*, Num_days_active*, Num_sessions_AI, Num_sessions_human, ...

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

colnames(vt_usage_kent)

 [1] "Sch_name*"                      "Sch_BOY_date*"                 
 [3] "Sch_EOY_date"                   "Stu_ID_VT*"                    
 [5] "Num_days_active*"               "Num_sessions_AI"               
 [7] "Num_sessions_human"             "Num_classesviewed"             
 [9] "Num_essaysreviewed"             "Num_AI_practiceproblemsessions"

Rename columns in usage file. Prepare for merging.

# rename vars
vt_usage_kent = vt_usage_kent %>%
  rename(
    school_vt = "Sch_name*",
    school_BOY_date = "Sch_BOY_date*",
    school_EOY_date = "Sch_EOY_date", 
    vt_acct_id = "Stu_ID_VT*", 
    num_days_active = "Num_days_active*",
    num_sessions_AI = "Num_sessions_AI",
    num_sessions_human = "Num_sessions_human",
    num_classesviewed = "Num_classesviewed",
    num_essaysreviewed = "Num_essaysreviewed",
    num_AI_practiceproblemsessions = "Num_AI_practiceproblemsessions"
  )
print(vt_usage_kent)

# A tibble: 1,792 × 10
   school_vt       school_BOY_date school_EOY_date vt_acct_id num_days_active
   <chr>           <chr>           <chr>                <dbl>           <dbl>
 1 Meridian Middle 2/2/26          4/14/26           13151928              18
 2 Meridian Middle 2/2/26          4/14/26           13151799              27
 3 Meridian Middle 2/2/26          4/14/26           13151928              18
 4 Meridian Middle 2/2/26          4/14/26           13151849              21
 5 Meridian Middle 2/2/26          4/14/26           13151928              18
 6 Meridian Middle 2/2/26          4/14/26           13152086              24
 7 Meridian Middle 2/2/26          4/14/26           13151694              26
 8 Meridian Middle 2/2/26          4/14/26           13152119              23
 9 Meridian Middle 2/2/26          4/14/26           13151685              28
10 Meridian Middle 2/2/26          4/14/26           13152119              23
# ℹ 1,782 more rows
# ℹ 5 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
#   num_classesviewed <dbl>, num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>

Identify duplicate student records within usage data file (N=88).

# frequency table of vt_acct_id occurrences
id_counts_vt_usage_kent = vt_usage_kent %>%
  group_by(vt_acct_id) %>%
  summarise(n = n()) %>%
  arrange(desc(n))

# filter only acct that appear > 1
dupes_vt_usage_kent = id_counts_vt_usage_kent %>%
  filter(n > 1)

# view results
print(dupes_vt_usage_kent)

# A tibble: 88 × 2
   vt_acct_id     n
        <dbl> <int>
 1   13151682    37
 2   13151686    30
 3   13151692    30
 4   13151845    30
 5   13151945    30
 6   13152099    29
 7   13151685    28
 8   13151808    28
 9   13151698    27
10   13151799    27
# ℹ 78 more rows

#export
write.csv(dupes_vt_usage_kent, file = "duplicate_acct_ids_usage_vt_kent.csv", row.names = FALSE)

Inspect duplicate student records in usage file (based on VT Account ID - not student ID). Observation: for a given duplicate student record, school name,num_days_active, BOY date and EOY date will match but usage data differs across the duplicate records.

dup_preview_usage_kent = vt_usage_kent %>%
  group_by(vt_acct_id) %>%
  filter(n() > 1) %>%
  ungroup()

print(dup_preview_usage_kent)

# A tibble: 1,792 × 10
   school_vt       school_BOY_date school_EOY_date vt_acct_id num_days_active
   <chr>           <chr>           <chr>                <dbl>           <dbl>
 1 Meridian Middle 2/2/26          4/14/26           13151928              18
 2 Meridian Middle 2/2/26          4/14/26           13151799              27
 3 Meridian Middle 2/2/26          4/14/26           13151928              18
 4 Meridian Middle 2/2/26          4/14/26           13151849              21
 5 Meridian Middle 2/2/26          4/14/26           13151928              18
 6 Meridian Middle 2/2/26          4/14/26           13152086              24
 7 Meridian Middle 2/2/26          4/14/26           13151694              26
 8 Meridian Middle 2/2/26          4/14/26           13152119              23
 9 Meridian Middle 2/2/26          4/14/26           13151685              28
10 Meridian Middle 2/2/26          4/14/26           13152119              23
# ℹ 1,782 more rows
# ℹ 5 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
#   num_classesviewed <dbl>, num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>

Decision: Collapse rows for each student ID. Per VT, these instances are rows that only capture one of the students multiple days active and need to be compressed.

Basic check of randomly selected student, 13151681. Inspect sum of Num_sessions_AI before collapsing.

sum(vt_usage_kent$num_sessions_AI[vt_usage_kent$vt_acct_id == 13151681], na.rm = TRUE)

[1] 22

Collapse rows.

vt_usage_kent_collapsed = vt_usage_kent %>%
  group_by(vt_acct_id) %>%
  summarise(
    num_sessions_AI = sum(num_sessions_AI, na.rm = TRUE),
    num_sessions_human = sum(num_sessions_human, na.rm = TRUE),
    num_classesviewed = sum(num_classesviewed, na.rm = TRUE),
    num_essaysreviewed = sum(num_essaysreviewed, na.rm = TRUE),
    num_AI_practiceproblemsessions = sum(num_AI_practiceproblemsessions, na.rm = TRUE),
    
    # keep other columns (takes first non-NA value)
    across(-c(num_sessions_AI,
              num_sessions_human,
              num_classesviewed,
              num_essaysreviewed,
              num_AI_practiceproblemsessions),
           ~ first(.x[!is.na(.x)])),
    
    .groups = "drop"
  )

print(vt_usage_kent_collapsed)

# A tibble: 88 × 10
   vt_acct_id num_sessions_AI num_sessions_human num_classesviewed
        <dbl>           <dbl>              <dbl>             <dbl>
 1   13151681              22                  3                 9
 2   13151682              62                  0                29
 3   13151683              33                  0                35
 4   13151684              20                  0                 0
 5   13151685              38                  0                10
 6   13151686              60                  0                57
 7   13151688              31                  0                 0
 8   13151690              24                  0                 0
 9   13151691              23                  2                 6
10   13151692              71                  1                 6
# ℹ 78 more rows
# ℹ 6 more variables: num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, school_vt <chr>,
#   school_BOY_date <chr>, school_EOY_date <chr>, num_days_active <dbl>

Check number of IDs that appear more than once.

sum(table(vt_usage_kent_collapsed$vt_acct_id) > 1)

[1] 0

Basic check of randomly selected student, 13151681. Inspect sum of Num_sessions_AI after collapsing.

sum(vt_usage_kent_collapsed$num_sessions_AI[vt_usage_kent_collapsed$vt_acct_id == 13151681], na.rm = TRUE)

[1] 22

Append student ID from crosswalk file to usage file.

vt_usage_kent_collapsed = vt_usage_kent_collapsed %>%
  left_join(vt_roster %>% select(vt_acct_id, student_id),
            by = "vt_acct_id")
print(vt_usage_kent_collapsed)

# A tibble: 88 × 11
   vt_acct_id num_sessions_AI num_sessions_human num_classesviewed
        <dbl>           <dbl>              <dbl>             <dbl>
 1   13151681              22                  3                 9
 2   13151682              62                  0                29
 3   13151683              33                  0                35
 4   13151684              20                  0                 0
 5   13151685              38                  0                10
 6   13151686              60                  0                57
 7   13151688              31                  0                 0
 8   13151690              24                  0                 0
 9   13151691              23                  2                 6
10   13151692              71                  1                 6
# ℹ 78 more rows
# ℹ 7 more variables: num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, school_vt <chr>,
#   school_BOY_date <chr>, school_EOY_date <chr>, num_days_active <dbl>,
#   student_id <dbl>

##Merge usage data to Kent student data

merge_vt_kent_usage = roster_unique %>%
  left_join(vt_usage_kent_collapsed, by = "student_id")

print(merge_vt_kent_usage)

# A tibble: 5,634 × 30
   student_id grade school       treat moy_math_comp moy_math_date eoy_math_comp
        <dbl> <chr> <chr>        <lgl> <chr>         <chr>         <chr>        
 1     391033 07    Meridian Mi… NA    484           1/6/2026      490          
 2     412115 08    Meridian Mi… NA    384           1/7/2026      409          
 3     425285 06    Meridian Mi… NA    468           1/6/2026      #N/A         
 4     373185 07    Meridian Mi… NA    533           1/7/2026      549          
 5     394412 06    Meridian Mi… NA    571           1/7/2026      568          
 6     389860 07    Meridian Mi… NA    530           1/7/2026      549          
 7     389442 07    Meridian Mi… NA    467           1/6/2026      454          
 8     384402 07    Meridian Mi… NA    506           1/7/2026      496          
 9     382167 07    Meridian Mi… NA    481           1/7/2026      477          
10     417702 06    Meridian Mi… NA    505           1/6/2026      499          
# ℹ 5,624 more rows
# ℹ 23 more variables: eoy_math_date <chr>, moy_ela_comp <chr>,
#   moy_ela_date <chr>, eoy_ela_comp <chr>, eoy_ela_date <chr>, gender <chr>,
#   sped <dbl>, mll <dbl>, low_inc <dbl>, race_eth <chr>, low_ap <dbl>,
#   mll_lvl <chr>, school_num <dbl>, vt_acct_id <dbl>, num_sessions_AI <dbl>,
#   num_sessions_human <dbl>, num_classesviewed <dbl>,
#   num_essaysreviewed <dbl>, num_AI_practiceproblemsessions <dbl>, …

Check for unmatched students.

merge_vt_kent_usage %>% filter(is.na(student_id)) # no NAs; no unmatched students.

# A tibble: 0 × 30
# ℹ 30 variables: student_id <dbl>, grade <chr>, school <chr>, treat <lgl>,
#   moy_math_comp <chr>, moy_math_date <chr>, eoy_math_comp <chr>,
#   eoy_math_date <chr>, moy_ela_comp <chr>, moy_ela_date <chr>,
#   eoy_ela_comp <chr>, eoy_ela_date <chr>, gender <chr>, sped <dbl>,
#   mll <dbl>, low_inc <dbl>, race_eth <chr>, low_ap <dbl>, mll_lvl <chr>,
#   school_num <dbl>, vt_acct_id <dbl>, num_sessions_AI <dbl>,
#   num_sessions_human <dbl>, num_classesviewed <dbl>, …

Recode “#N/A” to NA, missing value, in R.

merge_vt_kent_usage[merge_vt_kent_usage == "#N/A"] = NA

sum(merge_vt_kent_usage == "#N/A", na.rm = TRUE)

[1] 0

Add VT Acct indicator. Equals 1 if student has VT account ID (N=88). All else equals 0.

merge_vt_kent_usage$vt_present = ifelse(is.na(merge_vt_kent_usage$vt_acct_id), 0, 1)
table(merge_vt_kent_usage$vt_present)


   0    1 
5546   88

Create treatment status

Step 1. Construct num_weeks.

Append start date and end date following district schedule.

merge_vt_kent_usage = merge_vt_kent_usage %>% 
  mutate(
    #start of implementation, per usage file
    SOI_date = as.Date("2026-02-02"),
    #end of implementation Fri 4/10/2026 (last school day before EOY testing week)
    EOI_date = as.Date("2026-04-10")
    )

#transform other dates as.dates
merge_vt_kent_usage = merge_vt_kent_usage %>%
  mutate(
    SOI_date = as.Date(SOI_date,format = "%m/%d/%Y"),
    school_BOY_date = as.Date(school_BOY_date, format = "%m/%d/%Y"),
    school_EOY_date = as.Date(school_EOY_date, format = "%m/%d/%Y"),
    eoy_math_date = as.Date(eoy_math_date, format = "%m/%d/%Y"), 
    moy_math_date = as.Date(moy_math_date, format = "%m/%d/%Y"), 
    eoy_ela_date = as.Date(eoy_ela_date, format = "%m/%d/%Y"), 
    moy_ela_date = as.Date(moy_ela_date, format = "%m/%d/%Y")
  )

Take time difference between start and end of implementation.

merge_vt_kent_usage = merge_vt_kent_usage %>%
  mutate(
    num_weeks = as.numeric(difftime(EOI_date, SOI_date, units = "weeks")))
#round to the nearest whole week
merge_vt_kent_usage$num_weeks = round(merge_vt_kent_usage$num_weeks)
#subtract 2 weeks for spring break 
merge_vt_kent_usage$num_weeks = merge_vt_kent_usage$num_weeks - 2
#inspect avg
mean(merge_vt_kent_usage$num_weeks, na.rm = TRUE)

[1] 8

Step 2. Create num_days_active_week for number of days active per week per student.

Inspect num_days_active_week.

merge_vt_kent_usage = merge_vt_kent_usage %>%
  mutate(num_days_active_week = num_days_active / num_weeks)
summary(merge_vt_kent_usage$num_days_active_week, na.rm = TRUE)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  0.500   2.125   2.625   2.545   3.000   4.625    5546

sd(merge_vt_kent_usage$num_days_active_week, na.rm = TRUE)

[1] 0.7717586

Number of students where num_days_active_week equal to or greater than 3.

sum(merge_vt_kent_usage$num_days_active_week >= 3, na.rm = TRUE)

[1] 27

REVISIT the above. Avg days active per week is low!

Statistical summary for num_days_active.

summary(merge_vt_kent_usage$num_days_active, na.rm = TRUE)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
   4.00   17.00   21.00   20.36   24.00   37.00    5546

sd(merge_vt_kent_usage$num_days_active, na.rm = TRUE)

[1] 6.174069

Num_days_active, by school.

school_percentiles = merge_vt_kent_usage %>%
  group_by(school) %>%
  summarise(
    p25 = round(quantile(num_days_active_week, 0.25, na.rm = TRUE), 2),
    p50 = round(quantile(num_days_active_week, 0.50, na.rm = TRUE), 2),
    p75 = round(quantile(num_days_active_week, 0.75, na.rm = TRUE), 2),
    .groups = "drop"
  )

print(school_percentiles)

# A tibble: 7 × 4
  school                 p25   p50   p75
  <chr>                <dbl> <dbl> <dbl>
1 Canyon Ridge Middle  NA    NA       NA
2 Cedar Heights Middle NA    NA       NA
3 Mattson Middle       NA    NA       NA
4 Meeker Middle        NA    NA       NA
5 Meridian Middle       2.12  2.62     3
6 Mill Creek Middle    NA    NA       NA
7 Northwood Middle     NA    NA       NA

write.csv(school_percentiles, file = "vt_kent_num_days_active_week_summary_by_school.csv", row.names = FALSE)

Step 3. Filter out students who have less than 3 sessions (N = 5634 to 5573). They are not part of the final sample group and should not be used as comparison students. Among students where vt_present = 1, exclude those where num_session is less than 3.

7/6/2026: Since very few students meet the threshold for usage, this will impact the data summaries that follow.

merge_vt_kent_usage_final = merge_vt_kent_usage %>%
# keep those who don't appear in usage OR students that meet usage threshold.
   filter(vt_present != 1 | num_days_active_week >= 3) 
print(merge_vt_kent_usage_final)

# A tibble: 5,573 × 35
   student_id grade school       treat moy_math_comp moy_math_date eoy_math_comp
        <dbl> <chr> <chr>        <lgl> <chr>         <date>        <chr>        
 1     391033 07    Meridian Mi… NA    484           2026-01-06    490          
 2     412115 08    Meridian Mi… NA    384           2026-01-07    409          
 3     425285 06    Meridian Mi… NA    468           2026-01-06    <NA>         
 4     373185 07    Meridian Mi… NA    533           2026-01-07    549          
 5     394412 06    Meridian Mi… NA    571           2026-01-07    568          
 6     389860 07    Meridian Mi… NA    530           2026-01-07    549          
 7     389442 07    Meridian Mi… NA    467           2026-01-06    454          
 8     384402 07    Meridian Mi… NA    506           2026-01-07    496          
 9     382167 07    Meridian Mi… NA    481           2026-01-07    477          
10     417702 06    Meridian Mi… NA    505           2026-01-06    499          
# ℹ 5,563 more rows
# ℹ 28 more variables: eoy_math_date <date>, moy_ela_comp <chr>,
#   moy_ela_date <date>, eoy_ela_comp <chr>, eoy_ela_date <date>, gender <chr>,
#   sped <dbl>, mll <dbl>, low_inc <dbl>, race_eth <chr>, low_ap <dbl>,
#   mll_lvl <chr>, school_num <dbl>, vt_acct_id <dbl>, num_sessions_AI <dbl>,
#   num_sessions_human <dbl>, num_classesviewed <dbl>,
#   num_essaysreviewed <dbl>, num_AI_practiceproblemsessions <dbl>, …

Step 4. Create treat: Equals 1 if students had more than 2 sessions. All else equals 0.

merge_vt_kent_usage_final = merge_vt_kent_usage_final %>%
  mutate(treat = ifelse(!is.na(num_days_active) &  num_days_active_week >= 3, 1, 0))
table(merge_vt_kent_usage_final$treat)


   0    1 
5546   27

Inspect Merged Data

Missing Data Summary

Percentage missing for each variable. Observations: All other demographic info is present for all students. Some missing data for test info.

missing_summary = merge_vt_kent_usage_final %>%
  summarise(across(
    everything(),
    ~ mean(is.na(.)) * 100
  )) %>%
  pivot_longer(
    cols = everything(),
    names_to = "variable",
    values_to = "percent_missing"
  ) %>%
  arrange(desc(percent_missing))

print(missing_summary)

# A tibble: 35 × 2
   variable                       percent_missing
   <chr>                                    <dbl>
 1 vt_acct_id                                99.5
 2 num_sessions_AI                           99.5
 3 num_sessions_human                        99.5
 4 num_classesviewed                         99.5
 5 num_essaysreviewed                        99.5
 6 num_AI_practiceproblemsessions            99.5
 7 school_vt                                 99.5
 8 school_BOY_date                           99.5
 9 school_EOY_date                           99.5
10 num_days_active                           99.5
# ℹ 25 more rows

Percentage missing for each variable, grouped by treatment. Exported.

missing_summary_by_treat = merge_vt_kent_usage_final %>%
  group_by(treat) %>%
  summarise(across(
    everything(),
    ~ mean(is.na(.)) * 100, 
    .names = "{.col}"
  )) %>%
  pivot_longer(
    cols = -treat,
    names_to = "variable",
    values_to = "percent_missing"
  ) %>%
  arrange(desc(percent_missing))

print(missing_summary_by_treat)

# A tibble: 68 × 3
   treat variable                       percent_missing
   <dbl> <chr>                                    <dbl>
 1     0 vt_acct_id                                 100
 2     0 num_sessions_AI                            100
 3     0 num_sessions_human                         100
 4     0 num_classesviewed                          100
 5     0 num_essaysreviewed                         100
 6     0 num_AI_practiceproblemsessions             100
 7     0 school_vt                                  100
 8     0 school_BOY_date                            100
 9     0 school_EOY_date                            100
10     0 num_days_active                            100
# ℹ 58 more rows

#export
write.csv(missing_summary_by_treat, file = "vt_kent_pct_missing_vars_by_treat.csv")

Percentage of student who have both MOY and EOY data overall

#indicator for having BOTH MOY and EOY scores
merge_vt_kent_test_info = merge_vt_kent_usage_final %>%
  mutate(
    both_math = !is.na(moy_math_comp) & !is.na(eoy_math_comp),
    both_ela  = !is.na(moy_ela_comp)  & !is.na(eoy_ela_comp),
    all_tests = both_math & both_ela
  )

#pct across schools
overall_pct = merge_vt_kent_test_info %>%
  summarise(
    total_students = n(),
    students_with_all_tests = sum(all_tests),
    percent_with_all_tests = (students_with_all_tests / total_students) * 100
  ) %>%
  mutate(percent_with_all_tests = round(percent_with_all_tests, 2))

print(overall_pct)

# A tibble: 1 × 3
  total_students students_with_all_tests percent_with_all_tests
           <int>                   <int>                  <dbl>
1           5573                    4476                   80.3

Percentage of student who have both MOY and EOY data by school. Note: only 1 school site from Kent SD, Meridian Middle.

by_school_pct = merge_vt_kent_test_info %>%
  group_by(school) %>%
  summarise(
    total_students = n(),
    students_with_all_tests = sum(all_tests),
    percent_with_all_tests = (students_with_all_tests / total_students) * 100
  ) %>%
  mutate(percent_with_all_tests = round(percent_with_all_tests, 2)) %>%
  arrange(desc(percent_with_all_tests))

print(by_school_pct)

# A tibble: 7 × 4
  school            total_students students_with_all_te…¹ percent_with_all_tests
  <chr>                      <int>                  <int>                  <dbl>
1 Meridian Middle              576                    536                   93.1
2 Meeker Middle                832                    743                   89.3
3 Canyon Ridge Mid…            834                    744                   89.2
4 Northwood Middle             884                    765                   86.5
5 Mattson Middle               763                    596                   78.1
6 Cedar Heights Mi…            844                    623                   73.8
7 Mill Creek Middle            840                    469                   55.8
# ℹ abbreviated name: ¹students_with_all_tests

Percentage of student who have both MOY and EOY MATH data by treatment group, per school. Exported.

both_math_by_school_treat = merge_vt_kent_test_info %>%
  group_by(school, treat) %>%
  summarise(
    n = n(),
    n_both_math = sum(both_math == TRUE, na.rm = TRUE),
    pct_both_math = (n_both_math / n) * 100,
    .groups = "drop"
  )
print(both_math_by_school_treat)

# A tibble: 8 × 5
  school               treat     n n_both_math pct_both_math
  <chr>                <dbl> <int>       <int>         <dbl>
1 Canyon Ridge Middle      0   834         767          92.0
2 Cedar Heights Middle     0   844         673          79.7
3 Mattson Middle           0   763         640          83.9
4 Meeker Middle            0   832         759          91.2
5 Meridian Middle          0   549         516          94.0
6 Meridian Middle          1    27          26          96.3
7 Mill Creek Middle        0   840         553          65.8
8 Northwood Middle         0   884         797          90.2

#export
write.csv(both_math_by_school_treat, file = "vt_kent_pct_both_math_by_school_treat.csv", row.names = FALSE)

Percentage of student who have both MOY and EOY ELA data by treatment group, per school. Exported.

both_ela_by_school_treat = merge_vt_kent_test_info %>%
  group_by(school, treat) %>%
  summarise(
    n = n(),
    n_both_ela = sum(both_ela == TRUE, na.rm = TRUE),
    pct_both_ela = (n_both_ela / n) * 100,
    .groups = "drop"
  )
print(both_ela_by_school_treat)

# A tibble: 8 × 5
  school               treat     n n_both_ela pct_both_ela
  <chr>                <dbl> <int>      <int>        <dbl>
1 Canyon Ridge Middle      0   834        768         92.1
2 Cedar Heights Middle     0   844        705         83.5
3 Mattson Middle           0   763        657         86.1
4 Meeker Middle            0   832        758         91.1
5 Meridian Middle          0   549        518         94.4
6 Meridian Middle          1    27         27        100  
7 Mill Creek Middle        0   840        575         68.5
8 Northwood Middle         0   884        806         91.2

#export
write.csv(both_ela_by_school_treat, file = "vt_kent_pct_both_ela_by_school_treat.csv", row.names = FALSE)

Dummies for test info

both_math: dummy for students with complete math test info across MOY and EOY.

table(merge_vt_kent_test_info$both_math)


FALSE  TRUE 
  842  4731

#complete math: MOY and EOY present
merge_vt_kent_test_info$both_math = ifelse(merge_vt_kent_test_info$both_math, 1, 0)
table(merge_vt_kent_test_info$both_math) #check counts


   0    1 
 842 4731

both_ela: dummy for students with complete ELA test info across MOY and EOY..

table(merge_vt_kent_test_info$both_ela)


FALSE  TRUE 
  759  4814

#complete ELA: MOY and EOY present
merge_vt_kent_test_info$both_ela = ifelse(merge_vt_kent_test_info$both_ela, 1, 0)
table(merge_vt_kent_test_info$both_ela) #check counts


   0    1 
 759 4814

all_tests: dummy for students with both math and ELA test info complete across MOY and EOY.

table(merge_vt_kent_test_info$all_tests)


FALSE  TRUE 
 1097  4476

#complete math and ELA: MOY and EOY present
merge_vt_kent_test_info$all_tests = ifelse(merge_vt_kent_test_info$all_tests, 1, 0)
table(merge_vt_kent_test_info$all_tests) #check counts


   0    1 
1097 4476

math_moy_missing_eoy_present: dummy for students who have EOY math test info but MOY test info is missing.

merge_vt_kent_test_info = merge_vt_kent_test_info %>%
  mutate(
    moy_math_present = !is.na(moy_math_comp),
    eoy_math_present = !is.na(eoy_math_comp),
    
    math_moy_missing_eoy_present = case_when(
      !moy_math_present &  eoy_math_present ~ 1,   # moy missing, eoy present
       moy_math_present &  eoy_math_present ~ 0,   # both present
      !moy_math_present & !eoy_math_present ~ NA_real_  # both missing
    )
  )
table(merge_vt_kent_test_info$math_moy_missing_eoy_present)


   0    1 
4731  428

ela_moy_missing_eoy_present: dummy for students who have EOY ELA test info but MOY test info is missing.

merge_vt_kent_test_info = merge_vt_kent_test_info %>%
  mutate(
    moy_ela_present = !is.na(moy_ela_comp),
    eoy_ela_present = !is.na(eoy_ela_comp),
    
    ela_moy_missing_eoy_present = case_when(
      !moy_ela_present &  eoy_ela_present ~ 1,   # moy missing, eoy present
       moy_ela_present &  eoy_ela_present ~ 0,   # both present
      !moy_ela_present & !eoy_ela_present ~ NA_real_  # both missing
    )
  )
table(merge_vt_kent_test_info$ela_moy_missing_eoy_present)


   0    1 
4814  327

###Test info summary

Proportion of Math test info present (MOY present and EOY present, grouped by treatment status).

summary_math_tests_present = merge_vt_kent_test_info %>%
  mutate(
    moy_math_present = !is.na(moy_math_comp),
    eoy_math_present = !is.na(eoy_math_comp)
  ) %>%
  group_by(treat) %>%
  summarise(
    n_students = n(),
    
    moy_count = sum(moy_math_present),
    moy_prop  = mean(moy_math_present),
    
    eoy_count = sum(eoy_math_present),
    eoy_prop  = mean(eoy_math_present),
    
    .groups = "drop"
  )

# View result
print(summary_math_tests_present)

# A tibble: 2 × 6
  treat n_students moy_count moy_prop eoy_count eoy_prop
  <dbl>      <int>     <int>    <dbl>     <int>    <dbl>
1     0       5546      4881    0.880      5133    0.926
2     1         27        27    1            26    0.963

Proportion of ELA test info present (MOY present and EOY present, grouped by treatment status).

summary_ela_tests_present = merge_vt_kent_test_info %>%
  mutate(
    moy_ela_present = !is.na(moy_ela_comp),
    eoy_ela_present = !is.na(eoy_ela_comp)
  ) %>%
  group_by(treat) %>%
  summarise(
    n_students = n(),
    
    moy_count = sum(moy_ela_present),
    moy_prop  = mean(moy_ela_present),
    
    eoy_count = sum(eoy_ela_present),
    eoy_prop  = mean(eoy_ela_present),
    
    .groups = "drop"
  )

# View result
print(summary_ela_tests_present)

# A tibble: 2 × 6
  treat n_students moy_count moy_prop eoy_count eoy_prop
  <dbl>      <int>     <int>    <dbl>     <int>    <dbl>
1     0       5546      5013    0.904      5114    0.922
2     1         27        27    1            27    1

District Data Summary

Generate school-level means for MOY ELA and Math assessment data, number of students, plus distribution across demographics.

#create function to streamline calculations
calc_pct = function(x) {
  prop.table(table(x)) * 100
}

#school-level means and counts for TEST data
merge_vt_kent_test_info = merge_vt_kent_test_info %>%
  mutate(across(c(moy_math_comp, #transform all scores to numeric value
                  moy_ela_comp, 
                  eoy_math_comp, 
                  eoy_ela_comp), as.numeric))

school_means = merge_vt_kent_test_info %>%
  group_by(school)%>%
  summarise(
    n_students = n(),
    mean_moy_math = mean(moy_math_comp, na.rm = TRUE),
    mean_moy_ela  = mean(moy_ela_comp, na.rm = TRUE), 
    mean_eoy_math = mean(eoy_math_comp, na.rm = TRUE),
    mean_eoy_ela  = mean(eoy_ela_comp, na.rm = TRUE), 
  )

# DEMOGRAPHICS

# race/ethnicity
race_pct = merge_vt_kent_test_info %>%
  group_by(school, race_eth) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = race_eth,
    values_from = pct,
    names_prefix = "race_"
  )

#gender
gender_pct = merge_vt_kent_test_info %>%
  group_by(school, gender) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = gender,
    values_from = pct,
    names_prefix = "gender_"
  )

#low income status
low_inc_pct = merge_vt_kent_test_info %>%
  group_by(school, low_inc) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = low_inc,
    values_from = pct,
    names_prefix = "low_inc_"
  )

#multilingual learner
mll_pct = merge_vt_kent_test_info %>%
  group_by(school, mll) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = mll,
    values_from = pct,
    names_prefix = "mll_"
  )

#low ap students
low_ap_pct = merge_vt_kent_test_info %>%
  group_by(school, low_ap) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = low_ap,
    values_from = pct,
    names_prefix = "low_ap_"
  )

#sped students
sped_pct = merge_vt_kent_test_info %>%
  group_by(school, sped) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = sped,
    values_from = pct,
    names_prefix = "sped_"
  )

# COMBINE summaries
school_summary = school_means %>%
  left_join(race_pct,   by = "school") %>%
  left_join(gender_pct, by = "school") %>%
  left_join(low_inc_pct,   by = "school") %>%
  left_join(mll_pct,    by = "school") %>%
  left_join(low_ap_pct, by = "school")%>%
  left_join(sped_pct, by = "school")

school_summary = school_summary %>%
  mutate(across(where(is.numeric), ~ round(.x, 2)))

print(school_summary)

# A tibble: 7 × 24
  school        n_students mean_moy_math mean_moy_ela mean_eoy_math mean_eoy_ela
  <chr>              <dbl>         <dbl>        <dbl>         <dbl>        <dbl>
1 Canyon Ridge…        834          471.         560.          472.         560.
2 Cedar Height…        844          489.         585.          491.         590.
3 Mattson Midd…        763          492.         589.          494.         587.
4 Meeker Middle        832          488.         576.          491.         582.
5 Meridian Mid…        576          490.         587.          495.         591.
6 Mill Creek M…        840          456.         534.          459.         540.
7 Northwood Mi…        884          499.         598.          501.         602.
# ℹ 18 more variables: `race_American Indian or Alaska Native` <dbl>,
#   race_Asian <dbl>, `race_Black or African American` <dbl>,
#   `race_Hispanic/Latino` <dbl>,
#   `race_Native Hawaiian or Other Pacific Islander` <dbl>,
#   `race_Two or More Races` <dbl>, race_White <dbl>, gender_F <dbl>,
#   gender_M <dbl>, gender_NB <dbl>, low_inc_0 <dbl>, low_inc_1 <dbl>,
#   mll_0 <dbl>, mll_1 <dbl>, low_ap_0 <dbl>, low_ap_1 <dbl>, sped_0 <dbl>, …

write.csv(school_summary, "vt_kent_overall_district_data_summary_by_school.csv", row.names = FALSE)

Treatment students only: Export school-level means for MOY ELA and Math assessment data, number of students, percentages of students by race/ethnicity, gender, iep, and FRPL-status.

#school-level means and counts for TEST data
school_means_treat = merge_vt_kent_test_info %>%
  filter(treat == 1) %>% 
  group_by(school) %>%
  summarise(
    n_students = n(),
    mean_moy_math = mean(moy_math_comp, na.rm = TRUE),
    mean_moy_ela  = mean(moy_ela_comp, na.rm = TRUE)
  )

# DEMOGRAPHICS

# race/ethnicity
race_pct_treat = merge_vt_kent_test_info %>%
  filter(treat == 1) %>%
  group_by(school, race_eth) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = race_eth,
    values_from = pct,
    names_prefix = "race_"
  )

#gender
gender_pct_treat = merge_vt_kent_test_info %>%
  filter(treat == 1) %>%
  group_by(school, gender) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = gender,
    values_from = pct,
    names_prefix = "gender_"
  )

#low income status
low_inc_pct_treat = merge_vt_kent_test_info %>%
  filter(treat == 1) %>%
  group_by(school, low_inc) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = low_inc,
    values_from = pct,
    names_prefix = "low_inc_"
  )

#multilingual learner
mll_pct_treat = merge_vt_kent_test_info %>%
  filter(treat == 1) %>%
  group_by(school, mll) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = mll,
    values_from = pct,
    names_prefix = "mll_"
  )

#low ap students
low_ap_pct_treat = merge_vt_kent_test_info %>%
  filter(treat == 1) %>%
  group_by(school, low_ap) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = low_ap,
    values_from = pct,
    names_prefix = "low_ap_"
  )

#sped students
sped_pct_treat = merge_vt_kent_test_info %>%
  filter(treat == 1) %>%
  group_by(school, sped) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = sped,
    values_from = pct,
    names_prefix = "sped_"
  )

# COMBINE summaries
school_summary_treat = school_means_treat %>%
  left_join(race_pct,   by = "school") %>%
  left_join(gender_pct, by = "school") %>%
  left_join(low_inc_pct,   by = "school") %>%
  left_join(mll_pct,    by = "school") %>%
  left_join(low_ap_pct, by = "school")%>%
  left_join(sped_pct, by = "school")

school_summary_treat = school_summary_treat %>%
  mutate(across(where(is.numeric), ~ round(.x, 2)))

print(school_summary_treat)

# A tibble: 1 × 22
  school n_students mean_moy_math mean_moy_ela race_American Indian…¹ race_Asian
  <chr>       <dbl>         <dbl>        <dbl>                  <dbl>      <dbl>
1 Merid…         27          468.         558.                     NA       35.1
# ℹ abbreviated name: ¹`race_American Indian or Alaska Native`
# ℹ 16 more variables: `race_Black or African American` <dbl>,
#   `race_Hispanic/Latino` <dbl>,
#   `race_Native Hawaiian or Other Pacific Islander` <dbl>,
#   `race_Two or More Races` <dbl>, race_White <dbl>, gender_F <dbl>,
#   gender_M <dbl>, gender_NB <dbl>, low_inc_0 <dbl>, low_inc_1 <dbl>,
#   mll_0 <dbl>, mll_1 <dbl>, low_ap_0 <dbl>, low_ap_1 <dbl>, sped_0 <dbl>, …

write.csv(school_summary_treat, "vt_kent_district_data_summary_by_school_treat_students.csv", row.names = FALSE)

Comparison students only : Export school-level means for MOY ELA and Math assessment data, number of students, percentages of students by race/ethnicity, gender, iep, and FRPL-status.

#school-level means and counts for TEST data
school_means_comp = merge_vt_kent_test_info %>%
  filter(treat == 0) %>% 
  group_by(school) %>%
  summarise(
    n_students = n(),
    mean_moy_math = mean(moy_math_comp, na.rm = TRUE),
    mean_moy_ela  = mean(moy_ela_comp, na.rm = TRUE)
  )

# DEMOGRAPHICS

# race/ethnicity
race_pct_comp = merge_vt_kent_test_info %>%
  filter(treat == 0) %>%
  group_by(school, race_eth) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = race_eth,
    values_from = pct,
    names_prefix = "race_"
  )

#gender
gender_pct_comp = merge_vt_kent_test_info %>%
  filter(treat == 0) %>%
  group_by(school, gender) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = gender,
    values_from = pct,
    names_prefix = "gender_"
  )

#low income status
low_inc_pct_comp = merge_vt_kent_test_info %>%
  filter(treat == 0) %>%
  group_by(school, low_inc) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = low_inc,
    values_from = pct,
    names_prefix = "low_inc_"
  )

#multilingual learner
mll_pct_comp = merge_vt_kent_test_info %>%
  filter(treat == 0) %>%
  group_by(school, mll) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = mll,
    values_from = pct,
    names_prefix = "mll_"
  )

#at-risk students
low_ap_pct_comp = merge_vt_kent_test_info %>%
  filter(treat == 0) %>%
  group_by(school, low_ap) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = low_ap,
    values_from = pct,
    names_prefix = "low_ap_"
  )

#sped students
sped_pct_comp = merge_vt_kent_test_info %>%
  filter(treat == 0) %>%
  group_by(school, sped) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = sped,
    values_from = pct,
    names_prefix = "sped_"
  )

# COMBINE summaries
school_summary_comp = school_means_comp %>%
  left_join(race_pct,   by = "school") %>%
  left_join(gender_pct, by = "school") %>%
  left_join(low_inc_pct,   by = "school") %>%
  left_join(mll_pct,    by = "school") %>%
  left_join(low_ap_pct, by = "school")%>%
  left_join(sped_pct, by = "school")

school_summary_comp = school_summary_comp %>%
  mutate(across(where(is.numeric), ~ round(.x, 2)))

print(school_summary_comp)

# A tibble: 7 × 22
  school n_students mean_moy_math mean_moy_ela race_American Indian…¹ race_Asian
  <chr>       <dbl>         <dbl>        <dbl>                  <dbl>      <dbl>
1 Canyo…        834          471.         560.                   0.24       25.9
2 Cedar…        844          489.         585.                   0.59       15.3
3 Matts…        763          492.         589.                   0.52       19.9
4 Meeke…        832          488.         576.                   0.12       34.1
5 Merid…        549          491.         588.                  NA          35.1
6 Mill …        840          456.         534.                   0.36       18.4
7 North…        884          499.         598.                   0.68       24.6
# ℹ abbreviated name: ¹`race_American Indian or Alaska Native`
# ℹ 16 more variables: `race_Black or African American` <dbl>,
#   `race_Hispanic/Latino` <dbl>,
#   `race_Native Hawaiian or Other Pacific Islander` <dbl>,
#   `race_Two or More Races` <dbl>, race_White <dbl>, gender_F <dbl>,
#   gender_M <dbl>, gender_NB <dbl>, low_inc_0 <dbl>, low_inc_1 <dbl>,
#   mll_0 <dbl>, mll_1 <dbl>, low_ap_0 <dbl>, low_ap_1 <dbl>, sped_0 <dbl>, …

write.csv(school_summary_comp, "vt_kent_district_data_summary_by_school_comp_students.csv", row.names = FALSE)

Attrition

Percent of students who did not take the EOY assessment, per subject, within treatment group.

summary_attrition = merge_vt_kent_test_info %>%
  filter(treat == 1) %>%
  summarise(
    n_total = n(),
    
    # Math test missing
    n_missing_eoy_math = sum(is.na(eoy_math_comp)),
    pct_missing_eoy_math = (n_missing_eoy_math / n_total) * 100,
    
    # ELA test missing
    n_missing_eoy_ela = sum(is.na(eoy_ela_comp)),
    pct_missing_eoy_ela = (n_missing_eoy_ela / n_total) * 100
  )
print(summary_attrition)

# A tibble: 1 × 5
  n_total n_missing_eoy_math pct_missing_eoy_math n_missing_eoy_ela
    <int>              <int>                <dbl>             <int>
1      27                  1                 3.70                 0
# ℹ 1 more variable: pct_missing_eoy_ela <dbl>

Usage Data Summary

Count of students with any usage logged (defined as num_days_active => 1) by treatment

merge_vt_kent_test_info$num_days_active = as.numeric(merge_vt_kent_test_info$num_days_active)

#create new any_usage for new tabulation                                                           
summary_usage_by_treat = merge_vt_kent_test_info %>%
  mutate(any_usage = ifelse(num_days_active >= 1, 1, 0)) 
  #for treat=0 cases, any_usage will be NA

#for many treat=0 cases, recode any_usage from NA to 0. 
summary_usage_by_treat$any_usage[is.na(summary_usage_by_treat$any_usage)] <- 0

summary_usage_by_treat = summary_usage_by_treat %>%
  group_by(treat) %>%
  summarise(
    students_with_usage = sum(any_usage == 1, na.rm = TRUE),
    students_no_usage = sum(any_usage == 0, na.rm = TRUE),
    .groups = "drop"
  )

print(summary_usage_by_treat)

# A tibble: 2 × 3
  treat students_with_usage students_no_usage
  <dbl>               <int>             <int>
1     0                   0              5546
2     1                  27                 0

Count of students with any usage logged (defined as num_days_active => 1) by school. Exported.

any_usage_by_school = merge_vt_kent_test_info %>%
  mutate(any_usage = ifelse(num_days_active >= 1, 1, 0)) 

any_usage_by_school = any_usage_by_school %>%
  group_by(school) %>%
  summarise(
    students_with_usage = sum(any_usage == 1, na.rm = TRUE),
    students_no_usage = sum(any_usage == 0, na.rm = TRUE),
    .groups = "drop"
  )

print(any_usage_by_school)

# A tibble: 7 × 3
  school               students_with_usage students_no_usage
  <chr>                              <int>             <int>
1 Canyon Ridge Middle                    0                 0
2 Cedar Heights Middle                   0                 0
3 Mattson Middle                         0                 0
4 Meeker Middle                          0                 0
5 Meridian Middle                       27                 0
6 Mill Creek Middle                      0                 0
7 Northwood Middle                       0                 0

write.csv(any_usage_by_school, file = "vt_kent_any_usage_by_school.csv")

School-level summary of usage variables. Including Q1, Median, Mean, Q3, and SD. Exported.

usage_vars = c(
  "num_sessions_AI",
  "num_sessions_human",
  "num_classesviewed",
  "num_essaysreviewed",
  "num_AI_practiceproblemsessions"
)


summary_usage_by_school =  merge_vt_kent_test_info %>%
  group_by(school) %>%
  summarise(across(
    all_of(usage_vars),
    list(
      Q1 = ~ quantile(., 0.25, na.rm = TRUE),
      median = ~ median(., na.rm = TRUE),
      mean = ~ mean(., na.rm = TRUE),
      Q3 = ~ quantile(., 0.75, na.rm = TRUE),
      sd = ~ sd(., na.rm = TRUE)
    ),
    .names = "{.col}_{.fn}"
  ),
  .groups = "drop")
print(summary_usage_by_school)

# A tibble: 7 × 26
  school          num_sessions_AI_Q1 num_sessions_AI_median num_sessions_AI_mean
  <chr>                        <dbl>                  <dbl>                <dbl>
1 Canyon Ridge M…               NA                       NA                NaN  
2 Cedar Heights …               NA                       NA                NaN  
3 Mattson Middle                NA                       NA                NaN  
4 Meeker Middle                 NA                       NA                NaN  
5 Meridian Middle               28.5                     32                 33.8
6 Mill Creek Mid…               NA                       NA                NaN  
7 Northwood Midd…               NA                       NA                NaN  
# ℹ 22 more variables: num_sessions_AI_Q3 <dbl>, num_sessions_AI_sd <dbl>,
#   num_sessions_human_Q1 <dbl>, num_sessions_human_median <dbl>,
#   num_sessions_human_mean <dbl>, num_sessions_human_Q3 <dbl>,
#   num_sessions_human_sd <dbl>, num_classesviewed_Q1 <dbl>,
#   num_classesviewed_median <dbl>, num_classesviewed_mean <dbl>,
#   num_classesviewed_Q3 <dbl>, num_classesviewed_sd <dbl>,
#   num_essaysreviewed_Q1 <dbl>, num_essaysreviewed_median <dbl>, …

write.csv(summary_usage_by_school, file = "vt_kent_summary_usage_by_school.csv")

Reformat Data

Create dummy variables

Create numeric binary for gender. Observation: 5 NB student; NB students will present as NA under the male dummy.

table(merge_vt_kent_test_info$gender)


   F    M   NB 
2682 2886    5

merge_vt_kent_test_info = merge_vt_kent_test_info %>%
  mutate(
    male = case_when(
      gender == "M" ~ 1,
      gender == "F" ~ 0,
      TRUE ~ NA_real_   # handles missing or unexpected values
    )
  )
table(merge_vt_kent_test_info$male)


   0    1 
2682 2886

Create numeric binaries for each race.

table(merge_vt_kent_test_info$race_eth)


         American Indian or Alaska Native 
                                       21 
                                    Asian 
                                     1355 
                Black or African American 
                                      745 
                          Hispanic/Latino 
                                     1290 
Native Hawaiian or Other Pacific Islander 
                                      185 
                        Two or More Races 
                                      506 
                                    White 
                                     1471

merge_vt_kent_test_info = merge_vt_kent_test_info %>%
  mutate(
    race_A     = ifelse(race_eth == "Asian", 1, ifelse(is.na(race_eth), NA, 0)),
    race_B     = ifelse(race_eth == "Black or African American", 1, ifelse(is.na(race_eth), NA, 0)),
    race_H     = ifelse(race_eth == "Hispanic/Latino", 1, ifelse(is.na(race_eth), NA, 0)),
    race_Multi     = ifelse(race_eth == "Two or More Races", 1, ifelse(is.na(race_eth), NA, 0)),
    race_P     = ifelse(race_eth == "Native Hawaiian or Other Pacific Islander", 1, ifelse(is.na(race_eth), NA, 0)),
    race_W     = ifelse(race_eth == "White", 1, ifelse(is.na(race_eth), NA, 0))
  )
table(merge_vt_kent_test_info$race_B) #quick check coding on race_B


   0    1 
4828  745

Export files for e2i Coach

Save e2i file with all matched students from the merge, regardless of missing test info.

#select columns ready for e2i
vt_kent_e2i_usage_test_info = merge_vt_kent_test_info %>%
  dplyr::select(student_id, 
                grade, 
                school_num, 
                moy_math_comp, moy_math_date, 
                moy_ela_comp,moy_ela_date, 
                eoy_math_comp, eoy_math_date,
                eoy_ela_comp, eoy_ela_date,
                male, sped, mll, low_inc,low_ap,
                treat,
                race_A, race_B, race_H, race_Multi, race_P, race_W,
                both_math, both_ela, all_tests, 
                math_moy_missing_eoy_present, ela_moy_missing_eoy_present, 
                
                vt_acct_id,
                num_days_active,num_sessions_AI, num_sessions_human,
                num_classesviewed, num_essaysreviewed, num_AI_practiceproblemsessions, 
                school_BOY_date, school_EOY_date, 
                )

#export e2i with test indicators, all matched students from the merge, regardless of missing test info (all_tests == 1 or 0)
write.csv(vt_kent_e2i_usage_test_info, file = "vt_kent_e2i_all_students.csv", row.names = FALSE)

Save e2i file with only containing matched students from the merge with complete test info for Math.

table(vt_kent_e2i_usage_test_info$both_math)


   0    1 
 842 4731

#subset: keep only students with complete test info 
vt_kent_e2i_both_math = vt_kent_e2i_usage_test_info %>% filter(both_math == 1)
print(vt_kent_e2i_both_math)

# A tibble: 4,731 × 37
   student_id grade school_num moy_math_comp moy_math_date moy_ela_comp
        <dbl> <chr>      <dbl>         <dbl> <date>               <dbl>
 1     391033 07             5           484 2026-01-06             616
 2     412115 08             5           384 2026-01-07             474
 3     373185 07             5           533 2026-01-07             666
 4     394412 06             5           571 2026-01-07             641
 5     389860 07             5           530 2026-01-07             620
 6     389442 07             5           467 2026-01-06             529
 7     384402 07             5           506 2026-01-07             593
 8     382167 07             5           481 2026-01-07             576
 9     417702 06             5           505 2026-01-06             584
10     415707 08             5           442 2026-01-06             555
# ℹ 4,721 more rows
# ℹ 31 more variables: moy_ela_date <date>, eoy_math_comp <dbl>,
#   eoy_math_date <date>, eoy_ela_comp <dbl>, eoy_ela_date <date>, male <dbl>,
#   sped <dbl>, mll <dbl>, low_inc <dbl>, low_ap <dbl>, treat <dbl>,
#   race_A <dbl>, race_B <dbl>, race_H <dbl>, race_Multi <dbl>, race_P <dbl>,
#   race_W <dbl>, both_math <dbl>, both_ela <dbl>, all_tests <dbl>,
#   math_moy_missing_eoy_present <dbl>, ela_moy_missing_eoy_present <dbl>, …

write.csv(vt_kent_e2i_both_math, file = "vt_kent_e2i_complete_math_tests.csv")

Save e2i file with only containing matched students from the merge with complete test info for ELA.

table(vt_kent_e2i_usage_test_info$both_ela)


   0    1 
 759 4814

#subset: keep only students with complete test info
vt_kent_e2i_both_ela = vt_kent_e2i_usage_test_info %>% filter(both_ela == 1)
print(vt_kent_e2i_both_ela)

# A tibble: 4,814 × 37
   student_id grade school_num moy_math_comp moy_math_date moy_ela_comp
        <dbl> <chr>      <dbl>         <dbl> <date>               <dbl>
 1     391033 07             5           484 2026-01-06             616
 2     412115 08             5           384 2026-01-07             474
 3     425285 06             5           468 2026-01-06             550
 4     373185 07             5           533 2026-01-07             666
 5     394412 06             5           571 2026-01-07             641
 6     389860 07             5           530 2026-01-07             620
 7     389442 07             5           467 2026-01-06             529
 8     384402 07             5           506 2026-01-07             593
 9     382167 07             5           481 2026-01-07             576
10     417702 06             5           505 2026-01-06             584
# ℹ 4,804 more rows
# ℹ 31 more variables: moy_ela_date <date>, eoy_math_comp <dbl>,
#   eoy_math_date <date>, eoy_ela_comp <dbl>, eoy_ela_date <date>, male <dbl>,
#   sped <dbl>, mll <dbl>, low_inc <dbl>, low_ap <dbl>, treat <dbl>,
#   race_A <dbl>, race_B <dbl>, race_H <dbl>, race_Multi <dbl>, race_P <dbl>,
#   race_W <dbl>, both_math <dbl>, both_ela <dbl>, all_tests <dbl>,
#   math_moy_missing_eoy_present <dbl>, ela_moy_missing_eoy_present <dbl>, …

write.csv(vt_kent_e2i_both_ela, file = "vt_kent_e2i_complete_ela_tests.csv")