Data Processing: Varsity Tutors, Cumberland

Author

Alexis Davila

Clean Data

Import

Packages selected for inspecting and cleaning Varsity Tutors, district data from Cumberland schools.

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

library(ggplot2)

Import roster.

roster <- read_csv("roster_vt_cumber.csv")

Rows: 14447 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): school
dbl (3): school_code, student_id, grade

ℹ 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(roster)

[1] "school_code" "school"      "student_id"  "grade"

Change column headers for roster.

roster = roster %>%
  rename(
    grade_ros = "grade",
    school_ros = "school",
  )

Inspect schools.

table(roster$school_ros)


                   Anne Chesnutt Middle         Cumberland Virtual Academy 6-12 
                                    642                                     149 
                    Douglas Byrd Middle                     Gray's Creek Middle 
                                   1038                                    1322 
                      Hope Mills Middle   Howard Learning Academy Middle School 
                                    576                                     676 
                  John R Griffin Middle                     Lewis Chapel Middle 
                                   1178                                    1112 
             Luther Nick Jeralds Middle                     Mac Williams Middle 
                                    793                                    1478 
                                  NCDPI New Century International Middle School 
                                     30                                     403 
                     Pine Forest Middle                     R Max Abbott Middle 
                                    771                                    1245 
      Reid Ross Classical Middle School   Seventy-First Classical Middle School 
                                    223                                     418 
                      South View Middle                      Spring Lake Middle 
                                    696                                     699 
                        Westover Middle 
                                    998

Import Middle of Year data request.

moy_map = read_csv("moy_map_vt_cumber.csv")

Rows: 10572 Columns: 14
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (5): School Name, MOY MAP Math Math Test Date*, MOY MAP ELA Test Date*, ...
dbl (7): Student ID, Student Grade Level, MOY MAP Math Composite Scaled Scor...
lgl (2): Treatment, FRPL

ℹ 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.

print(moy_map)

# A tibble: 10,572 × 14
   `Student ID` `Student Grade Level` `School Name`        Treatment
          <dbl>                 <dbl> <chr>                <lgl>    
 1   2287512462                     6 Anne Chesnutt Middle NA       
 2   4431265988                     6 Anne Chesnutt Middle NA       
 3   9779738622                     6 Anne Chesnutt Middle NA       
 4   3968556194                     6 Anne Chesnutt Middle NA       
 5   1925959163                     6 Anne Chesnutt Middle NA       
 6   2638992577                     6 Anne Chesnutt Middle NA       
 7   8925824655                     6 Anne Chesnutt Middle NA       
 8   4634477858                     6 Anne Chesnutt Middle NA       
 9   9836237186                     6 Anne Chesnutt Middle NA       
10   1961537885                     6 Anne Chesnutt Middle NA       
# ℹ 10,562 more rows
# ℹ 10 more variables: `MOY MAP Math Composite Scaled Score*` <dbl>,
#   `MOY MAP Math Math Test Date*` <chr>,
#   `MOY MAP ELA Composite Scaled Score*` <dbl>,
#   `MOY MAP ELA Test Date*` <chr>, Gender <chr>, IEP <dbl>, ELL <dbl>,
#   FRPL <lgl>, `Race/Ethnicity` <chr>, `Academically at-risk` <dbl>

Change column headers for moy_map.

moy_map = moy_map %>%
  rename(
    student_id = "Student ID",
    grade_moy = "Student Grade Level", 
    school_moy = "School Name", 
    treat_moy = "Treatment", 
    moy_math_comp = "MOY MAP Math Composite Scaled Score*", 
    moy_math_date = "MOY MAP Math Math Test Date*", 
    moy_ela_comp = "MOY MAP ELA Composite Scaled Score*", 
    moy_ela_date = "MOY MAP ELA Test Date*", 
    gender_moy = "Gender", 
    iep_moy = "IEP", 
    ell_moy = "ELL", 
    frpl_moy = "FRPL", 
    race_eth_moy = "Race/Ethnicity", 
    at_risk_moy = "Academically at-risk"
    
  )

Import End of Year data request.

eoy_map = read_csv("eoy_map_vt_cumber.csv")

Rows: 8810 Columns: 14
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (5): School Name, EOY MAP Math Math Test Date*, EOY MAP ELA Test Date*, ...
dbl (7): Student ID, Student Grade Level, EOY MAP Math Composite Scaled Scor...
lgl (2): Treatment, FRPL

ℹ 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.

print(eoy_map)

# A tibble: 8,810 × 14
   `Student ID` `Student Grade Level` `School Name`                   Treatment
          <dbl>                 <dbl> <chr>                           <lgl>    
 1   7729773724                     8 Cumberland Virtual Academy 6-12 NA       
 2   4694886258                     8 Cumberland Virtual Academy 6-12 NA       
 3   1836192339                     8 Cumberland Virtual Academy 6-12 NA       
 4   7362151357                     8 Cumberland Virtual Academy 6-12 NA       
 5   4215536561                     8 Cumberland Virtual Academy 6-12 NA       
 6   3532188732                     8 Cumberland Virtual Academy 6-12 NA       
 7   3482512388                     8 Cumberland Virtual Academy 6-12 NA       
 8   4294541369                     8 Cumberland Virtual Academy 6-12 NA       
 9   4855429317                     8 Cumberland Virtual Academy 6-12 NA       
10   1582791228                     8 Cumberland Virtual Academy 6-12 NA       
# ℹ 8,800 more rows
# ℹ 10 more variables: `EOY MAP Math Composite Scaled Score*` <dbl>,
#   `EOY MAP Math Math Test Date*` <chr>,
#   `EOY MAP ELA Composite  Scaled Score*` <dbl>,
#   `EOY MAP ELA Test Date*` <chr>, Gender <chr>, IEP <dbl>, ELL <dbl>,
#   FRPL <lgl>, `Race/Ethnicity` <chr>, `Academically at-risk` <dbl>

Change column headers for eoy_map.

eoy_map = eoy_map %>%
  rename(
    student_id = "Student ID",
    grade_eoy = "Student Grade Level", 
    school_eoy = "School Name", 
    treat_eoy = "Treatment", 
    eoy_math_comp = "EOY MAP Math Composite Scaled Score*", 
    eoy_math_date = "EOY MAP Math Math Test Date*", 
    eoy_ela_comp = "EOY MAP ELA Composite  Scaled Score*", 
    eoy_ela_date = "EOY MAP ELA Test Date*", 
    gender_eoy = "Gender", 
    iep_eoy = "IEP", 
    ell_eoy = "ELL", 
    frpl_eoy = "FRPL", 
    race_eth_eoy = "Race/Ethnicity", 
    at_risk_eoy = "Academically at-risk"
    
  )

Inspect duplicates

Roster

Identify duplicate student records within roster file.

# 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: 1,425 × 2
   student_id     n
        <dbl> <int>
 1 2257769627    16
 2 3284674675    16
 3 5761715848    15
 4 7877815913    15
 5 8922727837    15
 6 4254112513    14
 7 5251233515    14
 8 3568287471    13
 9 4526196894    13
10 6564849621    13
# ℹ 1,415 more rows

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

Inspect duplicate student records in roster. Observation: for a given duplicate student record, it appears to be the same individual (e.g., same school, same grade)

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

print(dup_preview_roster)

# A tibble: 5,315 × 4
   school_code school_ros           student_id grade_ros
         <dbl> <chr>                     <dbl>     <dbl>
 1      260336 Anne Chesnutt Middle 9581845356         6
 2      260336 Anne Chesnutt Middle 9581845356         6
 3      260336 Anne Chesnutt Middle 9581845356         6
 4      260336 Anne Chesnutt Middle 5396535695         6
 5      260336 Anne Chesnutt Middle 5396535695         6
 6      260336 Anne Chesnutt Middle 4978175585         6
 7      260336 Anne Chesnutt Middle 4978175585         6
 8      260336 Anne Chesnutt Middle 6429225941         6
 9      260336 Anne Chesnutt Middle 6429225941         6
10      260336 Anne Chesnutt Middle 6429225941         6
# ℹ 5,305 more rows

Decision: keep first occurrence of student ID based on current row order (N=10,557).

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

print(roster_unique)

# A tibble: 10,557 × 4
   school_code school_ros           student_id grade_ros
         <dbl> <chr>                     <dbl>     <dbl>
 1      260336 Anne Chesnutt Middle 2287512462         6
 2      260336 Anne Chesnutt Middle 4431265988         6
 3      260336 Anne Chesnutt Middle 9779738622         6
 4      260336 Anne Chesnutt Middle 3968556194         6
 5      260336 Anne Chesnutt Middle 1925959163         6
 6      260336 Anne Chesnutt Middle 2638992577         6
 7      260336 Anne Chesnutt Middle 8925824655         6
 8      260336 Anne Chesnutt Middle 4634477858         6
 9      260336 Anne Chesnutt Middle 9836237186         6
10      260336 Anne Chesnutt Middle 1961537885         6
# ℹ 10,547 more rows

MOY

Identify duplicate student records within MOY file.

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

# filter only IDs that appear > 1
dupes_moy = id_counts_moy %>%
  filter(n > 1)

# view list of duplicate IDs
print(dupes_moy)

# A tibble: 1 × 2
  student_id     n
       <dbl> <int>
1 7117828382     2

#export
write.csv(dupes_moy, file = "duplicate_ids_moy_vt_cumber.csv", row.names = FALSE)

Inspect duplicate student record in the MOY file. Observation: Student records contain the same demographic info and math test info, however ELA test info (date and score) differ. Decision: Considering the student has complete test info in both observations, keep only the first student record (i.e., take the duplicate row with the earlier ELA test info).

dup_preview_moy = moy_map %>%
  group_by(student_id) %>%
  filter(n() > 1) %>%
  ungroup()

print(dup_preview_moy)

# A tibble: 2 × 14
  student_id grade_moy school_moy          treat_moy moy_math_comp moy_math_date
       <dbl>     <dbl> <chr>               <lgl>             <dbl> <chr>        
1 7117828382         7 Lewis Chapel Middle NA                  204 1/8/2026     
2 7117828382         7 Lewis Chapel Middle NA                  204 1/8/2026     
# ℹ 8 more variables: moy_ela_comp <dbl>, moy_ela_date <chr>, gender_moy <chr>,
#   iep_moy <dbl>, ell_moy <dbl>, frpl_moy <lgl>, race_eth_moy <chr>,
#   at_risk_moy <dbl>

Remove duplicate student record in MOY file following above decision.

# remove duplicates entirely
moy_no_dup = moy_map %>%
  group_by(student_id) %>%
  filter(n() == 1) %>%   # keep only IDs that appear once
  ungroup()

# pull out duplicate record with earliest ELA info to keep
moy_dup_keep = moy_map %>%
  filter(student_id == "7117828382",
         moy_ela_date == "1/7/2026")

# append moy w/out dupes to selected dup record
moy_unique = bind_rows(moy_no_dup, moy_dup_keep)

print(moy_unique)

# A tibble: 10,571 × 14
   student_id grade_moy school_moy         treat_moy moy_math_comp moy_math_date
        <dbl>     <dbl> <chr>              <lgl>             <dbl> <chr>        
 1 2287512462         6 Anne Chesnutt Mid… NA                  225 1/14/2026    
 2 4431265988         6 Anne Chesnutt Mid… NA                  201 1/14/2026    
 3 9779738622         6 Anne Chesnutt Mid… NA                  235 1/15/2026    
 4 3968556194         6 Anne Chesnutt Mid… NA                  211 1/14/2026    
 5 1925959163         6 Anne Chesnutt Mid… NA                  226 1/14/2026    
 6 2638992577         6 Anne Chesnutt Mid… NA                  213 1/14/2026    
 7 8925824655         6 Anne Chesnutt Mid… NA                  201 1/14/2026    
 8 4634477858         6 Anne Chesnutt Mid… NA                  228 1/14/2026    
 9 9836237186         6 Anne Chesnutt Mid… NA                  194 1/14/2026    
10 1961537885         6 Anne Chesnutt Mid… NA                  249 1/14/2026    
# ℹ 10,561 more rows
# ℹ 8 more variables: moy_ela_comp <dbl>, moy_ela_date <chr>, gender_moy <chr>,
#   iep_moy <dbl>, ell_moy <dbl>, frpl_moy <lgl>, race_eth_moy <chr>,
#   at_risk_moy <dbl>

EOY

Identify duplicate student records within EOY file.

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

# filter only IDs that appear > 1
dupes_eoy = id_counts_eoy %>%
  filter(n > 1)

# view list of duplicate IDs
print(dupes_eoy)

# A tibble: 1 × 2
  student_id     n
       <dbl> <int>
1 8354674797     2

#export
write.csv(dupes_eoy, file = "duplicate_ids_eoy_vt_cumber.csv", row.names = FALSE)

dup_preview_eoy = eoy_map %>%
  group_by(student_id) %>%
  filter(n() > 1) %>% 
  ungroup()

print(dup_preview_eoy)

# A tibble: 2 × 14
  student_id grade_eoy school_eoy         treat_eoy eoy_math_comp eoy_math_date
       <dbl>     <dbl> <chr>              <lgl>             <dbl> <chr>        
1 8354674797         6 Spring Lake Middle NA                  206 4/17/2026    
2 8354674797         6 Spring Lake Middle NA                  206 4/17/2026    
# ℹ 8 more variables: eoy_ela_comp <dbl>, eoy_ela_date <chr>, gender_eoy <chr>,
#   iep_eoy <dbl>, ell_eoy <dbl>, frpl_eoy <lgl>, race_eth_eoy <chr>,
#   at_risk_eoy <dbl>

Remove duplicate student record in EOY file.

# remove duplicates entirely
eoy_no_dup = eoy_map %>%
  group_by(student_id) %>%
  filter(n() == 1) %>%   # keep only IDs that appear once
  ungroup()

# pull out duplicate record with earliest ELA info to keep
eoy_dup_keep = eoy_map %>%
  filter(student_id == "8354674797",
         eoy_ela_date == "4/20/2026")

# append eoy w/out dupes to selected dup record
eoy_unique = bind_rows(eoy_no_dup, eoy_dup_keep)

# preview result
print(eoy_unique)

# A tibble: 8,809 × 14
   student_id grade_eoy school_eoy         treat_eoy eoy_math_comp eoy_math_date
        <dbl>     <dbl> <chr>              <lgl>             <dbl> <chr>        
 1 7729773724         8 Cumberland Virtua… NA                  229 4/16/2026    
 2 4694886258         8 Cumberland Virtua… NA                  240 4/15/2026    
 3 1836192339         8 Cumberland Virtua… NA                  268 4/15/2026    
 4 7362151357         8 Cumberland Virtua… NA                  224 4/15/2026    
 5 4215536561         8 Cumberland Virtua… NA                  199 4/15/2026    
 6 3532188732         8 Cumberland Virtua… NA                  252 4/15/2026    
 7 3482512388         8 Cumberland Virtua… NA                  283 4/15/2026    
 8 4294541369         8 Cumberland Virtua… NA                  236 4/15/2026    
 9 4855429317         8 Cumberland Virtua… NA                  241 4/15/2026    
10 1582791228         8 Cumberland Virtua… NA                  239 4/15/2026    
# ℹ 8,799 more rows
# ℹ 8 more variables: eoy_ela_comp <dbl>, eoy_ela_date <chr>, gender_eoy <chr>,
#   iep_eoy <dbl>, ell_eoy <dbl>, frpl_eoy <lgl>, race_eth_eoy <chr>,
#   at_risk_eoy <dbl>

Merge District Files

Merge MOY to roster.

merge_roster_moy = roster_unique %>%
  left_join(moy_unique, by = "student_id")

Check unmatched records in intermediary data: 257 students in the roster with no appearance in MOY.

#check students in roster with NO match in MOY
missing_moy = roster_unique %>%
  anti_join(moy_unique, by = "student_id")
cat("Number of roster students missing MOY data:", nrow(missing_moy), "\n")

Number of roster students missing MOY data: 257

Check unexpected records (MOY): 271 students present in the MOY file that do NOT present in the roster.

# check in MOY data NOT in roster
unexpected_moy_records = moy_unique %>%
  anti_join(roster_unique, by = "student_id")

cat("Number of MOY records not in roster:", nrow(unexpected_moy_records), "\n")

Number of MOY records not in roster: 271

Match intermediary with EOY records by student ID.

merge_cumber = merge_roster_moy %>%
  left_join(eoy_unique, by = "student_id")

Check unmatched records in final merge: 1753 students in the intermediary file, merge_roster_moy, with no appearance in EOY.

#check students in merge_roster_moy with NO match in EOY
missing_eoy = merge_roster_moy %>%
  anti_join(eoy_unique, by = "student_id")

cat("Number of students missing EOY data from merge_roster_moy:", nrow(missing_eoy), "\n")

Number of students missing EOY data from merge_roster_moy: 1753

Check unexpected records (EOY): 5 students present in the EOY file that not NOT present in the roster.

# check in EOY file NOT in roster (unexpected records)
unexpected_eoy_records = eoy_unique %>%
  anti_join(roster_unique, by = "student_id")


cat("Number of EOY records not in roster:", nrow(unexpected_eoy_records), "\n")

Number of EOY records not in roster: 5

Export unmatched (missing) and unexpected (extra) records.

write.csv(missing_moy, file =  "missing_moy_records_vt_cumber.csv", row.names = FALSE)
write.csv(unexpected_moy_records, file = "extra_moy_records_vt_cumber.csv", row.names = FALSE)
write.csv(missing_eoy, file = "missing_eoy_records_vt_cumber.csv", row.names = FALSE)
write.csv(unexpected_eoy_records, file = "extra_eoy_records_vt_cumber.csv", row.names = FALSE)

Clean merge

Keep rows where ID presents across the de-duped roster, moy file, and eoy file (N=8,646).

roster_moy_matched = roster_unique %>%
  inner_join(moy_unique, by = "student_id")

merge_vt_cumber_unclean = roster_moy_matched %>%
  inner_join(eoy_unique, by = "student_id")

print(merge_vt_cumber_unclean)

# A tibble: 8,648 × 30
   school_code school_ros    student_id grade_ros grade_moy school_moy treat_moy
         <dbl> <chr>              <dbl>     <dbl>     <dbl> <chr>      <lgl>    
 1      260336 Anne Chesnut… 2287512462         6         6 Anne Ches… NA       
 2      260336 Anne Chesnut… 4431265988         6         6 Anne Ches… NA       
 3      260336 Anne Chesnut… 9779738622         6         6 Anne Ches… NA       
 4      260336 Anne Chesnut… 3968556194         6         6 Anne Ches… NA       
 5      260336 Anne Chesnut… 1925959163         6         6 Anne Ches… NA       
 6      260336 Anne Chesnut… 2638992577         6         6 Anne Ches… NA       
 7      260336 Anne Chesnut… 8925824655         6         6 Anne Ches… NA       
 8      260336 Anne Chesnut… 4634477858         6         6 Anne Ches… NA       
 9      260336 Anne Chesnut… 9836237186         6         6 Anne Ches… NA       
10      260336 Anne Chesnut… 1961537885         6         6 Anne Ches… NA       
# ℹ 8,638 more rows
# ℹ 23 more variables: moy_math_comp <dbl>, moy_math_date <chr>,
#   moy_ela_comp <dbl>, moy_ela_date <chr>, gender_moy <chr>, iep_moy <dbl>,
#   ell_moy <dbl>, frpl_moy <lgl>, race_eth_moy <chr>, at_risk_moy <dbl>,
#   grade_eoy <dbl>, school_eoy <chr>, treat_eoy <lgl>, eoy_math_comp <dbl>,
#   eoy_math_date <chr>, eoy_ela_comp <dbl>, eoy_ela_date <chr>,
#   gender_eoy <chr>, iep_eoy <dbl>, ell_eoy <dbl>, frpl_eoy <lgl>, …

Check for requested data elements.

colnames(merge_vt_cumber_unclean)

 [1] "school_code"   "school_ros"    "student_id"    "grade_ros"    
 [5] "grade_moy"     "school_moy"    "treat_moy"     "moy_math_comp"
 [9] "moy_math_date" "moy_ela_comp"  "moy_ela_date"  "gender_moy"   
[13] "iep_moy"       "ell_moy"       "frpl_moy"      "race_eth_moy" 
[17] "at_risk_moy"   "grade_eoy"     "school_eoy"    "treat_eoy"    
[21] "eoy_math_comp" "eoy_math_date" "eoy_ela_comp"  "eoy_ela_date" 
[25] "gender_eoy"    "iep_eoy"       "ell_eoy"       "frpl_eoy"     
[29] "race_eth_eoy"  "at_risk_eoy"

Inspect demographics (MOY v EOY)

GENDER: From MOY to EOY, there was 1 additional F student and 1 less M student, which suggests 1 student changed their gender identification.

gender_summary  = merge_vt_cumber_unclean  %>%
  pivot_longer(
    cols = c(gender_moy, gender_eoy),
    names_to = "time",
    values_to = "gender"
  ) %>%
  mutate(time = ifelse(time == "gender_moy", "moy", "eoy")) %>%
  filter(gender %in% c("M", "F")) %>%
  count(time, gender) %>%
  group_by(time) %>%
  mutate(
    total = sum(n),
    pct = n / total
  ) %>%
  arrange(time, gender)

print(gender_summary)

# A tibble: 4 × 5
# Groups:   time [2]
  time  gender     n total   pct
  <chr> <chr>  <int> <int> <dbl>
1 eoy   F       4188  8648 0.484
2 eoy   M       4460  8648 0.516
3 moy   F       4187  8648 0.484
4 moy   M       4461  8648 0.516

IEPs: 5 additional students gained IEPs at the end of year.

iep_summary = merge_vt_cumber_unclean %>%
  summarise(
    moy_iep_n = sum(iep_moy == 1, na.rm = TRUE),
    eoy_iep_n = sum(iep_eoy == 1, na.rm = TRUE),

    total_students = n(),

    moy_iep_pct =  moy_iep_n / total_students,
    eoy_iep_pct =  eoy_iep_n / total_students,

    change_n = eoy_iep_n - moy_iep_n,
    change_pct = eoy_iep_pct - moy_iep_pct
  )

print(iep_summary)

# A tibble: 1 × 7
  moy_iep_n eoy_iep_n total_students moy_iep_pct eoy_iep_pct change_n change_pct
      <int>     <int>          <int>       <dbl>       <dbl>    <int>      <dbl>
1      1245      1250           8648       0.144       0.145        5   0.000578

ELLs: 2 additional students gained ELL status by EOY.

ell_summary = merge_vt_cumber_unclean %>%
  summarise(
    moy_ell_n = sum(ell_moy == 1, na.rm = TRUE),
    eoy_ell_n = sum(ell_eoy == 1, na.rm = TRUE),

    total_students = n(),

    moy_ell_pct =  moy_ell_n / total_students,
    eoy_ell_pct =  eoy_ell_n / total_students,

    change_n = eoy_ell_n - moy_ell_n,
    change_pct = eoy_ell_pct - moy_ell_pct
  )

print(ell_summary)

# A tibble: 1 × 7
  moy_ell_n eoy_ell_n total_students moy_ell_pct eoy_ell_pct change_n change_pct
      <int>     <int>          <int>       <dbl>       <dbl>    <int>      <dbl>
1       372       374           8648      0.0430      0.0432        2   0.000231

FRPL: No students identified as having FRPL in MOY or EOY.

frpl_summary = merge_vt_cumber_unclean %>%
  summarise(
    moy_frpl_n = sum(frpl_moy == 1, na.rm = TRUE),
    eoy_frpl_n = sum(frpl_eoy == 1, na.rm = TRUE),

    total_students = n(),

    moy_frpl_pct =  moy_frpl_n / total_students,
    eoy_frpl_pct =  eoy_frpl_n / total_students,

    change_n = eoy_frpl_n - moy_frpl_n,
    change_pct = eoy_frpl_pct - moy_frpl_pct
  )

print(frpl_summary)

# A tibble: 1 × 7
  moy_frpl_n eoy_frpl_n total_students moy_frpl_pct eoy_frpl_pct change_n
       <int>      <int>          <int>        <dbl>        <dbl>    <int>
1          0          0           8648            0            0        0
# ℹ 1 more variable: change_pct <dbl>

AT-RISK: No change in status between MOY to EOY.

atrisk_summary = merge_vt_cumber_unclean %>%
  summarise(
    moy_atrisk_n = sum(at_risk_moy == 1, na.rm = TRUE),
    eoy_atrisk_n = sum(at_risk_eoy == 1, na.rm = TRUE),

    total_students = n(),

    moy_atrisk_pct =  moy_atrisk_n / total_students,
    eoy_atrisk_pct =  eoy_atrisk_n / total_students,

    change_n = eoy_atrisk_n - moy_atrisk_n,
    change_pct = eoy_atrisk_pct - moy_atrisk_pct
  )

print(atrisk_summary)

# A tibble: 1 × 7
  moy_atrisk_n eoy_atrisk_n total_students moy_atrisk_pct eoy_atrisk_pct
         <int>        <int>          <int>          <dbl>          <dbl>
1         2669         2669           8648          0.309          0.309
# ℹ 2 more variables: change_n <int>, change_pct <dbl>

RACE-ETHNICITY: Following categories appeared to have stayed more or less the same: A, B, W. There was a significant drop in the count of H students, suggesting that the district and/or families re-identified away from H in favor of other new categories: I, Multi, and P.

MOY: inspect racial categories and basic counts

table(merge_vt_cumber_unclean$race_eth_moy)


   A    B    H    P    W 
 180 4075 2385   46 1962

EOY: inspect racial categories and basic counts

table(merge_vt_cumber_unclean$race_eth_eoy)


    A     B     H     I Multi     P     W 
  181  4075  1467   100   817    46  1962

Decision: Keep racial/ethnicity categorizations and gender identifications from EOY file. All other demographic data keep from MOY file.

merge_vt_cumber_unclean = merge_vt_cumber_unclean %>% #7/1: used to be final_vt_cumber
  #will need to change final_vt_cumber in other sections.
  dplyr::select(
                student_id, 
                grade_moy, 
                school_moy, 
                moy_math_comp, moy_math_date, 
                moy_ela_comp,moy_ela_date, 
                eoy_math_comp, eoy_math_date,
                eoy_ela_comp, eoy_ela_date,
                gender_eoy, iep_moy, ell_moy,frpl_moy, race_eth_eoy, at_risk_moy)

colnames(merge_vt_cumber_unclean)

 [1] "student_id"    "grade_moy"     "school_moy"    "moy_math_comp"
 [5] "moy_math_date" "moy_ela_comp"  "moy_ela_date"  "eoy_math_comp"
 [9] "eoy_math_date" "eoy_ela_comp"  "eoy_ela_date"  "gender_eoy"   
[13] "iep_moy"       "ell_moy"       "frpl_moy"      "race_eth_eoy" 
[17] "at_risk_moy"

Clean column names for selected demographics.

merge_vt_cumber = merge_vt_cumber_unclean %>%
  rename(
    grade = "grade_moy", 
    school = "school_moy", 
    gender = "gender_eoy", 
    iep = "iep_moy", 
    ell = "ell_moy", 
    frpl = "frpl_moy", 
    race_eth = "race_eth_eoy", 
    at_risk = "at_risk_moy"
  )
colnames(merge_vt_cumber) # where final_vt_cumber below change to merge_vt_cumber

 [1] "student_id"    "grade"         "school"        "moy_math_comp"
 [5] "moy_math_date" "moy_ela_comp"  "moy_ela_date"  "eoy_math_comp"
 [9] "eoy_math_date" "eoy_ela_comp"  "eoy_ela_date"  "gender"       
[13] "iep"           "ell"           "frpl"          "race_eth"     
[17] "at_risk"

School number code

Inspect school names

table(merge_vt_cumber$school)


                 Anne Chesnutt Middle       Cumberland Virtual Academy 6-12 
                                  492                                   126 
                  Douglas Byrd Middle                   Gray's Creek Middle 
                                  644                                   979 
                    Hope Mills Middle Howard Learning Academy Middle School 
                                  407                                    55 
                John R Griffin Middle                   Lewis Chapel Middle 
                                  559                                   509 
           Luther Nick Jeralds Middle                   Mac Williams Middle 
                                  515                                   805 
                   Pine Forest Middle                   R Max Abbott Middle 
                                  631                                   699 
    Reid Ross Classical Middle School Seventy-First Classical Middle School 
                                  201                                   387 
                    South View Middle                    Spring Lake Middle 
                                  564                                   428 
                      Westover Middle 
                                  647

Create school_num, a number code for each school.

merge_vt_cumber = merge_vt_cumber %>%
  mutate(school_num = recode(school,
                             "Anne Chesnutt Middle" = 1,
                             "Cumberland Virtual Academy 6-12" = 2,
                             "Douglas Byrd Middle" = 3, 
                             "Gray's Creek Middle" = 4, 
                             "Hope Mills Middle" = 5, 
                             "Howard Learning Academy Middle School" = 6, 
                             "John R Griffin Middle" = 7,
                             "Lewis Chapel Middle" = 8, 
                             "Luther Nick Jeralds Middle" = 9, 
                             "Mac Williams Middle" = 10, 
                             "NCDPI"= 11, 
                             "New Century International Middle School" = 12, 
                             "Pine Forest Middle" = 13,
                             "R Max Abbott Middle" = 14, 
                             "Reid Ross Classical Middle School" = 15, 
                             "Seventy-First Classical Middle School" = 16, 
                             "South View Middle" = 17, 
                             "Spring Lake Middle" = 18, 
                             "Westover Middle" = 19))

table(merge_vt_cumber$school_num)


  1   2   3   4   5   6   7   8   9  10  13  14  15  16  17  18  19 
492 126 644 979 407  55 559 509 515 805 631 699 201 387 564 428 647

Standardize test scores

Using the MAP Growth Norms Technical Manual (2025) to standardize test scores.

MATH MOY

# Winter NWEA Math norms for Grades 6-8
moy_math_norms = tibble(
  grade = c(6, 7, 8),
  moy_mean_math = c(216.00, 221.00, 225.95),
  moy_sd_math = c(17.35, 18.36, 19.31)
)

# Merge norms and calculate standardized scores
merge_vt_cumber = merge_vt_cumber %>%
  left_join(moy_math_norms, by = "grade") %>%
  mutate(
    moy_math_z = round((moy_math_comp - moy_mean_math) / moy_sd_math, 2)
  ) %>%
  select(-moy_mean_math, -moy_sd_math)
summary(merge_vt_cumber$moy_math_z)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
-3.9300 -0.7700 -0.0500 -0.0691  0.6500  4.8500     599

MATH EOY

# Spring NWEA Math norms for Grades 6-8
eoy_math_spring_norms = tibble(
  grade = c(6, 7, 8),
  eoy_mean_math = c(220.33, 223.85, 228.87),
  eoy_sd_math = c(18.47, 19.29, 20.16)
)

# Merge norms and calculate standardized scores
merge_vt_cumber = merge_vt_cumber %>%
  left_join(eoy_math_spring_norms, by = "grade") %>%
  mutate(
    eoy_math_z = round((eoy_math_comp - eoy_mean_math) / eoy_sd_math, 2)
  ) %>%
  select(-eoy_mean_math, -eoy_sd_math)
summary(merge_vt_cumber$eoy_math_z)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
-4.0200 -0.8200  0.0100 -0.0337  0.7400  4.4800    1098

ELA MOY

# Winter NWEA Reading norms for Grades 6-8
moy_ela_norms = tibble(
  grade = c(6, 7, 8),
  moy_mean_ela = c(210.72, 213.77, 216.92),
  moy_sd_ela = c(16.70, 16.83, 17.04)
)

# Merge norms and calculate standardized scores
merge_vt_cumber = merge_vt_cumber %>%
  left_join(moy_ela_norms, by = "grade") %>%
  mutate(
    moy_ela_z = round((moy_ela_comp - moy_mean_ela) / moy_sd_ela, 2)
  ) %>%
  select(-moy_mean_ela, -moy_sd_ela)
summary(merge_vt_cumber$moy_ela_z)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
-3.3100 -0.6400  0.0800 -0.0376  0.6800  2.7600     736

ELA EOY

# Spring NWEA Reading norms for Grades 6-8
eoy_ela_spring_norms = tibble(
  grade = c(6, 7, 8),
  eoy_mean_ela = c(212.04, 214.83, 217.82),
  eoy_sd_ela = c(16.67, 17.00, 17.16)
)

# Merge norms and calculate standardized scores
merge_vt_cumber = merge_vt_cumber %>%
  left_join(eoy_ela_spring_norms, by = "grade") %>%
  mutate(
    eoy_ela_z = round((eoy_ela_comp - eoy_mean_ela) / eoy_sd_ela, 2)
  ) %>%
  select(-eoy_mean_ela, -eoy_sd_ela)
summary(merge_vt_cumber$eoy_ela_z)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
-3.1200 -0.7200  0.0100 -0.0886  0.6600  2.6400     589

Merge Usage Data to District Data

Clean Cumberland 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 cumber usage data from VT
vt_usage_cumber = read_csv("vt_usage_cumber_updated.csv")

New names:
Rows: 998 Columns: 26
── Column specification
──────────────────────────────────────────────────────── Delimiter: "," chr
(3): Sch_name*, Sch_BOY_date*, Sch_EOY_date dbl (8): Stu_ID_VT*,
Num_days_active*, Num_sessions_AI, Num_sessions_human,... lgl (15): ...12,
...13, ...14, ...15, ...16, ...17, ...18, ...19, ...20, ......
ℹ 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.
• `` -> `...12`
• `` -> `...13`
• `` -> `...14`
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`
• `` -> `...24`
• `` -> `...25`
• `` -> `...26`

# 7/14/26: new usage data, already collapsed at student level. 

colnames(vt_usage_cumber)

 [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"
[11] "Total"                          "...12"                         
[13] "...13"                          "...14"                         
[15] "...15"                          "...16"                         
[17] "...17"                          "...18"                         
[19] "...19"                          "...20"                         
[21] "...21"                          "...22"                         
[23] "...23"                          "...24"                         
[25] "...25"                          "...26"

Rename columns in usage file. Prepare for final merging.

# rename vars
vt_usage_cumber = vt_usage_cumber %>%
  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", 
    total_sessions = "Total"
  )
colnames(vt_usage_cumber)

 [1] "school_vt"                      "school_BOY_date"               
 [3] "school_EOY_date"                "vt_acct_id"                    
 [5] "num_days_active"                "num_sessions_AI"               
 [7] "num_sessions_human"             "num_classesviewed"             
 [9] "num_essaysreviewed"             "num_AI_practiceproblemsessions"
[11] "total_sessions"                 "...12"                         
[13] "...13"                          "...14"                         
[15] "...15"                          "...16"                         
[17] "...17"                          "...18"                         
[19] "...19"                          "...20"                         
[21] "...21"                          "...22"                         
[23] "...23"                          "...24"                         
[25] "...25"                          "...26"

Remove empty columns from csv conversion.

vt_usage_cumber = vt_usage_cumber %>%
  select(-(12:26))
colnames(vt_usage_cumber)

 [1] "school_vt"                      "school_BOY_date"               
 [3] "school_EOY_date"                "vt_acct_id"                    
 [5] "num_days_active"                "num_sessions_AI"               
 [7] "num_sessions_human"             "num_classesviewed"             
 [9] "num_essaysreviewed"             "num_AI_practiceproblemsessions"
[11] "total_sessions"

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

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

# filter only acct that appear > 1
dupes_vt_usage_cumber = id_counts_vt_usage_cumber %>%
  filter(n > 1)

# view results
print(dupes_vt_usage_cumber)

# A tibble: 1 × 2
  vt_acct_id     n
       <dbl> <int>
1         NA   170

#archive code
#write.csv(dupes_vt_usage_cumber, file = "duplicate_acct_ids_usage_vt_cumber.csv", row.names = FALSE)

Inspect rows where Account ID is missing. Observation: empty rows from csv conversion.

vt_usage_cumber %>%
  filter(is.na(vt_acct_id))

# A tibble: 170 × 11
   school_vt school_BOY_date school_EOY_date vt_acct_id num_days_active
   <chr>     <chr>           <chr>                <dbl>           <dbl>
 1 <NA>      <NA>            <NA>                    NA              NA
 2 <NA>      <NA>            <NA>                    NA              NA
 3 <NA>      <NA>            <NA>                    NA              NA
 4 <NA>      <NA>            <NA>                    NA              NA
 5 <NA>      <NA>            <NA>                    NA              NA
 6 <NA>      <NA>            <NA>                    NA              NA
 7 <NA>      <NA>            <NA>                    NA              NA
 8 <NA>      <NA>            <NA>                    NA              NA
 9 <NA>      <NA>            <NA>                    NA              NA
10 <NA>      <NA>            <NA>                    NA              NA
# ℹ 160 more rows
# ℹ 6 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
#   num_classesviewed <dbl>, num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>

Remove empty rows. Actual student observations in usage file equals 828.

vt_usage_cumber = vt_usage_cumber %>%
  filter(!is.na(vt_acct_id))

print(vt_usage_cumber)

# A tibble: 828 × 11
   school_vt          school_BOY_date school_EOY_date vt_acct_id num_days_active
   <chr>              <chr>           <chr>                <dbl>           <dbl>
 1 Luther Nick Jeral… 2/2/26          4/14/26            4484817              23
 2 Luther Nick Jeral… 2/2/26          4/14/26            4484829              23
 3 Luther Nick Jeral… 2/2/26          4/14/26            4503017              19
 4 Luther Nick Jeral… 2/2/26          4/14/26            4484796              18
 5 Luther Nick Jeral… 2/2/26          4/14/26           13156155              17
 6 Luther Nick Jeral… 2/2/26          4/14/26            4485040              17
 7 Luther Nick Jeral… 2/2/26          4/14/26            4465043              16
 8 Luther Nick Jeral… 2/2/26          4/14/26           13156474              16
 9 Luther Nick Jeral… 2/2/26          4/14/26            4505710              15
10 Luther Nick Jeral… 2/2/26          4/14/26            4484954              15
# ℹ 818 more rows
# ℹ 6 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
#   num_classesviewed <dbl>, num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>

No duplicates // Archive: 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_cumber = vt_usage_cumber %>%
#  group_by(vt_acct_id) %>%
#  filter(n() > 1) %>%
#  ungroup()

#print(dup_preview_usage_cumber)

No duplicates // Archive: 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.

No duplicates // Archive: Basic check of randomly selected student, 13159482. Inspect sum of Num_sessions_AI before collapsing.

#sum(vt_usage_cumber$num_sessions_AI[vt_usage_cumber$vt_acct_id == 13159482], na.rm = TRUE)

No duplicates // Archive: Collapse rows.

#vt_usage_cumber_collapsed = vt_usage_cumber %>%
#  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_cumber_collapsed)

No duplicates // Archive: Check number of IDs that appear more than once.

#sum(table(vt_usage_cumber_collapsed$vt_acct_id) > 1)

No duplicates // Archive: Basic check of randomly selected student, 13159482. Inspect sum of Num_sessions_AI after collapsing.

#sum(vt_usage_cumber_collapsed$num_sessions_AI[vt_usage_cumber_collapsed$vt_acct_id == 13159482], na.rm = TRUE)

Append student ID from crosswalk file to usage file.

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

# A tibble: 828 × 12
   school_vt          school_BOY_date school_EOY_date vt_acct_id num_days_active
   <chr>              <chr>           <chr>                <dbl>           <dbl>
 1 Luther Nick Jeral… 2/2/26          4/14/26            4484817              23
 2 Luther Nick Jeral… 2/2/26          4/14/26            4484829              23
 3 Luther Nick Jeral… 2/2/26          4/14/26            4503017              19
 4 Luther Nick Jeral… 2/2/26          4/14/26            4484796              18
 5 Luther Nick Jeral… 2/2/26          4/14/26           13156155              17
 6 Luther Nick Jeral… 2/2/26          4/14/26            4485040              17
 7 Luther Nick Jeral… 2/2/26          4/14/26            4465043              16
 8 Luther Nick Jeral… 2/2/26          4/14/26           13156474              16
 9 Luther Nick Jeral… 2/2/26          4/14/26            4505710              15
10 Luther Nick Jeral… 2/2/26          4/14/26            4484954              15
# ℹ 818 more rows
# ℹ 7 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
#   num_classesviewed <dbl>, num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>,
#   student_id <dbl>

##Merge usage data to student Cumberland data.

merge_vt_cumber_usage = merge_vt_cumber %>%
  left_join(vt_usage_cumber, by = "student_id")

print(merge_vt_cumber_usage)

# A tibble: 8,648 × 33
   student_id grade school moy_math_comp moy_math_date moy_ela_comp moy_ela_date
        <dbl> <dbl> <chr>          <dbl> <chr>                <dbl> <chr>       
 1 2287512462     6 Anne …           225 1/14/2026              215 1/13/2026   
 2 4431265988     6 Anne …           201 1/14/2026              200 1/13/2026   
 3 9779738622     6 Anne …           235 1/15/2026              235 1/13/2026   
 4 3968556194     6 Anne …           211 1/14/2026              225 1/13/2026   
 5 1925959163     6 Anne …           226 1/14/2026              226 1/13/2026   
 6 2638992577     6 Anne …           213 1/14/2026              209 1/13/2026   
 7 8925824655     6 Anne …           201 1/14/2026              209 1/14/2026   
 8 4634477858     6 Anne …           228 1/14/2026              217 1/13/2026   
 9 9836237186     6 Anne …           194 1/14/2026              198 1/13/2026   
10 1961537885     6 Anne …           249 1/14/2026              237 1/13/2026   
# ℹ 8,638 more rows
# ℹ 26 more variables: eoy_math_comp <dbl>, eoy_math_date <chr>,
#   eoy_ela_comp <dbl>, eoy_ela_date <chr>, gender <chr>, iep <dbl>, ell <dbl>,
#   frpl <lgl>, race_eth <chr>, at_risk <dbl>, school_num <dbl>,
#   moy_math_z <dbl>, eoy_math_z <dbl>, moy_ela_z <dbl>, eoy_ela_z <dbl>,
#   school_vt <chr>, school_BOY_date <chr>, school_EOY_date <chr>,
#   vt_acct_id <dbl>, num_days_active <dbl>, num_sessions_AI <dbl>, …

Check for unmatched students.

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

# A tibble: 0 × 33
# ℹ 33 variables: student_id <dbl>, grade <dbl>, school <chr>,
#   moy_math_comp <dbl>, moy_math_date <chr>, moy_ela_comp <dbl>,
#   moy_ela_date <chr>, eoy_math_comp <dbl>, eoy_math_date <chr>,
#   eoy_ela_comp <dbl>, eoy_ela_date <chr>, gender <chr>, iep <dbl>, ell <dbl>,
#   frpl <lgl>, race_eth <chr>, at_risk <dbl>, school_num <dbl>,
#   moy_math_z <dbl>, eoy_math_z <dbl>, moy_ela_z <dbl>, eoy_ela_z <dbl>,
#   school_vt <chr>, school_BOY_date <chr>, school_EOY_date <chr>, …

Add VT Acct indicator. Equals 1 if student has VT account ID (N=762). All else equals 0. Number of potential comparison students equals 7886. Number of student with VT Account IDs who matched to a student ID equals 762.

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


   0    1 
7886  762

Among students with VT account IDs, recode num_days_active to equal zero if num_days_active is NA.

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  mutate(
    num_days_active_recoded = ifelse(
      vt_present == 1 & is.na(num_days_active),
      0,
      num_days_active
    )
  )

Remove cases with unexpected usage

Inspect students with any usage by school.

merge_vt_cumber_usage %>%
  mutate(any_usage = ifelse(num_days_active >= 1, 1, 0)) %>%
  group_by(school_num) %>%
  summarize(
    total_students = n(),
    n_with_usage = sum(any_usage, na.rm = TRUE),
    .groups = "drop")

# A tibble: 17 × 3
   school_num total_students n_with_usage
        <dbl>          <int>        <dbl>
 1          1            492            0
 2          2            126            0
 3          3            644            2
 4          4            979            0
 5          5            407            0
 6          6             55            0
 7          7            559            0
 8          8            509            2
 9          9            515          377
10         10            805            1
11         13            631            0
12         14            699            0
13         15            201            1
14         16            387            0
15         17            564            0
16         18            428          358
17         19            647            2

Decision: Remove students in comparison school with unexpected usage.

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  filter( 
    #keep students in 18 or 9 regardless of usage, 
    school_num %in% c(9, 18) | # OR
      #keep students who do not attend treat schools & 
        #exhibit no usage
      (!(school_num %in% c(9, 18)) & is.na(num_days_active)))

Re-inspect students with any usage by school.

merge_vt_cumber_usage %>%
  mutate(any_usage = ifelse(num_days_active >= 1, 1, 0)) %>%
  group_by(school_num) %>%
  summarize(
    total_students = n(),
    n_with_usage = sum(any_usage, na.rm = TRUE),
    .groups = "drop")

# A tibble: 17 × 3
   school_num total_students n_with_usage
        <dbl>          <int>        <dbl>
 1          1            492            0
 2          2            126            0
 3          3            642            0
 4          4            979            0
 5          5            407            0
 6          6             55            0
 7          7            559            0
 8          8            506            0
 9          9            515          377
10         10            804            0
11         13            631            0
12         14            699            0
13         15            200            0
14         16            387            0
15         17            564            0
16         18            428          358
17         19            645            0

Reformat Data Variables

Clean data variables for e2i Coach.

Gender dummy construction

table(merge_vt_cumber_usage$gender)


   F    M 
4185 4454

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


   0    1 
4185 4454

Race category construction

Decision: Collapse smallest groups to one level within race category. Keep to 5 levels max.

table(merge_vt_cumber_usage$race_eth)


    A     B     H     I Multi     P     W 
  181  4069  1465   100   816    46  1962

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  mutate(race_recoded = case_when(race_eth %in% c(
    "I",
    "P",
    "A") ~ "Other",
      TRUE ~ race_eth
    ))
table(merge_vt_cumber_usage$race_recoded)


    B     H Multi Other     W 
 4069  1465   816   327  1962

Race dummy construction

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  mutate(
    race_B     = ifelse(race_recoded == "B", 1, ifelse(is.na(race_recoded), NA, 0)),
    race_H     = ifelse(race_recoded== "H", 1, ifelse(is.na(race_recoded), NA, 0)),
    race_Multi = ifelse(race_recoded== "Multi", 1, ifelse(is.na(race_recoded), NA, 0)),
    race_Other     = ifelse(race_recoded == "Other", 1, ifelse(is.na(race_recoded), NA, 0)),
    race_W     = ifelse(race_recoded== "W", 1, ifelse(is.na(race_recoded), NA, 0))
  )
table(merge_vt_cumber_usage$race_Other) #quick check coding on race_Other


   0    1 
8312  327

Grade dummy construction

table(merge_vt_cumber_usage$grade)


   6    7    8 
2985 2892 2762

Create dummies for each grade.

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  mutate(
    grade6 = if_else(grade == 6, 1, 0),
    grade7 = if_else(grade == 7, 1, 0),
    grade8 = if_else(grade == 8, 1, 0)
  )

Check coding for grade 6.

table(merge_vt_cumber_usage$grade6)


   0    1 
5654 2985

School-grade category construction

Inspect school names.

table(merge_vt_cumber_usage$school)


                 Anne Chesnutt Middle       Cumberland Virtual Academy 6-12 
                                  492                                   126 
                  Douglas Byrd Middle                   Gray's Creek Middle 
                                  642                                   979 
                    Hope Mills Middle Howard Learning Academy Middle School 
                                  407                                    55 
                John R Griffin Middle                   Lewis Chapel Middle 
                                  559                                   506 
           Luther Nick Jeralds Middle                   Mac Williams Middle 
                                  515                                   804 
                   Pine Forest Middle                   R Max Abbott Middle 
                                  631                                   699 
    Reid Ross Classical Middle School Seventy-First Classical Middle School 
                                  200                                   387 
                    South View Middle                    Spring Lake Middle 
                                  564                                   428 
                      Westover Middle 
                                  645

Create new categorical variable for grade by school

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  mutate(school_grade = paste(sub(" .*", "", school), grade))

table(merge_vt_cumber_usage$school_grade)


         Anne 6          Anne 7          Anne 8    Cumberland 6    Cumberland 7 
            157             177             158              34              37 
   Cumberland 8       Douglas 6       Douglas 7       Douglas 8        Gray's 6 
             55             245             234             163             336 
       Gray's 7        Gray's 8          Hope 6          Hope 7          Hope 8 
            318             325             146             125             136 
       Howard 6        Howard 7        Howard 8          John 6          John 7 
             14              13              28             151             201 
         John 8         Lewis 6         Lewis 7         Lewis 8        Luther 6 
            207             186             153             167             199 
       Luther 7        Luther 8           Mac 6           Mac 7           Mac 8 
            174             142             284             274             246 
         Pine 6          Pine 7          Pine 8             R 6             R 7 
            206             219             206             248             231 
            R 8          Reid 6          Reid 7          Reid 8 Seventy-First 6 
            220              62              67              71             129 
Seventy-First 7 Seventy-First 8         South 6         South 7         South 8 
            121             137             190             196             178 
       Spring 6        Spring 7        Spring 8      Westover 6      Westover 7 
            165             145             118             233             207 
     Westover 8 
            205

Dummies for test info

#indicator for having BOTH MOY and EOY scores
merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  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
  )

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

table(merge_vt_cumber_usage$both_math)


FALSE  TRUE 
 1606  7033

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


   0    1 
1606 7033

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

table(merge_vt_cumber_usage$both_ela)


FALSE  TRUE 
 1209  7430

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


   0    1 
1209 7430

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

table(merge_vt_cumber_usage$all_tests)


FALSE  TRUE 
 2331  6308

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


   0    1 
2331 6308

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

merge_vt_cumber_usage <- merge_vt_cumber_usage %>%
  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_cumber_usage$math_moy_missing_eoy_present)


   0    1 
7033  509

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

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  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_cumber_usage$ela_moy_missing_eoy_present)


   0    1 
7430  622

Recode at-risk status

Inspect Cumberland’s coding for at-risk status. Cumberland coded 1 for at-risk, all else NA (missing and/or no at-risk status): assume NA is zero? Investigate/confirm that all Kent students offered Live+AI were marked low_ap (at-risk var) == 1.

table(merge_vt_cumber_usage$at_risk)


   1 
2666

Decision: Recode Cumberland’s NAs under at_risk to zero.

merge_vt_cumber_usage$at_risk[is.na(merge_vt_cumber_usage$at_risk)] <- 0
table(merge_vt_cumber_usage$at_risk)


   0    1 
5973 2666

Create treatment status

Step 1. Construct num_weeks.

Append start date and end date following district schedule. Decision: Luther Nick Jerald school started much later than the other schools. Entered start of implementation date 2/17/2026 for students who attended Luther. All other schools started on 2/2/2026 following the start date in the usage file.

merge_vt_cumber_usage = merge_vt_cumber_usage %>% 
  mutate(
    SOI_date = as.Date(if_else(school_num == 9,
                               "2026-02-17",#start of Impl for Luther
                               "2026-02-02")),#start of Impl for other schools,
                                               #per usage file
    #end of implementation Fri 5/8/2026 (last school day before EOY testing week)
    EOI_date = as.Date("2026-05-08")
  )
#transform other dates as.dates
merge_vt_cumber_usage = merge_vt_cumber_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. Note: Luther num_weeks equal 10. All other schools’ num_weeks equal 13.

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

[1] 12.82116

Step 2. Create num_sessions_week for number of active sessions per week per student.

Inspect total_sessions.

summary(merge_vt_cumber_usage$total_sessions, na.rm = TRUE)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
   0.00    4.00   10.00   19.07   23.00  256.00    7886

sd(merge_vt_cumber_usage$total_sessions, na.rm = TRUE)

[1] 27.79062

Inspect num_sessions_week.

merge_vt_cumber_usage = merge_vt_cumber_usage %>%
  mutate(num_sessions_week = total_sessions / num_weeks)
summary(merge_vt_cumber_usage$num_sessions_week, na.rm = TRUE)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  0.000   0.385   0.846   1.575   1.900  19.692    7886

sd(merge_vt_cumber_usage$num_sessions_week, na.rm = TRUE)

[1] 2.154686

Inspect usage based on number of active session per week.

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

sum(merge_vt_cumber_usage$num_sessions_week >= 3, na.rm = TRUE)

[1] 103

Number of students where num_sessions_week equal to or greater than 2.

sum(merge_vt_cumber_usage$num_sessions_week >= 2, na.rm = TRUE)

[1] 184

Number of students where num_sessions_week equal to or greater than 1.

sum(merge_vt_cumber_usage$num_sessions_week >= 1, na.rm = TRUE)

[1] 350

Preliminary inspections

Number of active days

Statistical summary for num_days_active (recoded so that 0 days of usage among the student with VT Account IDs are represented).

summary(merge_vt_cumber_usage$num_days_active_recoded, na.rm = TRUE)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  0.000   2.000   4.000   4.847   7.000  23.000    7886

sd(merge_vt_cumber_usage$num_days_active_recoded, na.rm = TRUE)

[1] 4.059962

Number of active days (overall), by school.

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

print(overall_school_percentiles)

# A tibble: 17 × 4
   school                                  p25   p50   p75
   <chr>                                 <dbl> <dbl> <dbl>
 1 Anne Chesnutt Middle                     NA    NA    NA
 2 Cumberland Virtual Academy 6-12          NA    NA    NA
 3 Douglas Byrd Middle                      NA    NA    NA
 4 Gray's Creek Middle                      NA    NA    NA
 5 Hope Mills Middle                        NA    NA    NA
 6 Howard Learning Academy Middle School    NA    NA    NA
 7 John R Griffin Middle                    NA    NA    NA
 8 Lewis Chapel Middle                      NA    NA    NA
 9 Luther Nick Jeralds Middle                1     2     4
10 Mac Williams Middle                      NA    NA    NA
11 Pine Forest Middle                       NA    NA    NA
12 R Max Abbott Middle                      NA    NA    NA
13 Reid Ross Classical Middle School        NA    NA    NA
14 Seventy-First Classical Middle School    NA    NA    NA
15 South View Middle                        NA    NA    NA
16 Spring Lake Middle                        3     6    10
17 Westover Middle                          NA    NA    NA

write.csv(overall_school_percentiles, file = "vt_cumber_num_days_active_summary_by_school.csv", row.names = FALSE)

Frequency distribution of num_active_days (recoded so that 0 days of usage among the students with VT Account IDs are represented).

ggplot(merge_vt_cumber_usage, aes(x = num_days_active_recoded)) +
  geom_histogram(binwidth = 1,  fill = "lightblue", color = "black") +
  stat_bin( binwidth = 1, geom = "text",
            aes(label = after_stat(count)),
            vjust = -0.5) +
  scale_y_continuous(expand = expansion(mult = c(0, 0.1))) +
  theme_minimal()

Warning: Removed 7886 rows containing non-finite outside the scale range (`stat_bin()`).
Removed 7886 rows containing non-finite outside the scale range (`stat_bin()`).

Total sessions

Statistical summary for total_sessions.

summary(merge_vt_cumber_usage$total_sessions, na.rm = TRUE)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
   0.00    4.00   10.00   19.07   23.00  256.00    7886

sd(merge_vt_cumber_usage$total_sessions, na.rm = TRUE)

[1] 27.79062

Number of total_sessions, by school.

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

print(school_percentiles)

# A tibble: 17 × 4
   school                                  p25   p50   p75
   <chr>                                 <dbl> <dbl> <dbl>
 1 Anne Chesnutt Middle                     NA    NA    NA
 2 Cumberland Virtual Academy 6-12          NA    NA    NA
 3 Douglas Byrd Middle                      NA    NA    NA
 4 Gray's Creek Middle                      NA    NA    NA
 5 Hope Mills Middle                        NA    NA    NA
 6 Howard Learning Academy Middle School    NA    NA    NA
 7 John R Griffin Middle                    NA    NA    NA
 8 Lewis Chapel Middle                      NA    NA    NA
 9 Luther Nick Jeralds Middle                3     6    12
10 Mac Williams Middle                      NA    NA    NA
11 Pine Forest Middle                       NA    NA    NA
12 R Max Abbott Middle                      NA    NA    NA
13 Reid Ross Classical Middle School        NA    NA    NA
14 Seventy-First Classical Middle School    NA    NA    NA
15 South View Middle                        NA    NA    NA
16 Spring Lake Middle                        9    19    36
17 Westover Middle                          NA    NA    NA

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

Frequency distribution of total_sessions. Note: Bin width is 5!

ggplot(merge_vt_cumber_usage, aes(x = total_sessions)) +
  geom_histogram(binwidth = 5,  fill = "lightblue", color = "black") +
  stat_bin( binwidth = 5, geom = "text",
            aes(label = after_stat(count)),
            vjust = -0.5) +
  scale_y_continuous(expand = expansion(mult = c(0, 0.1))) +
  theme_minimal()

Warning: Removed 7886 rows containing non-finite outside the scale range (`stat_bin()`).
Removed 7886 rows containing non-finite outside the scale range (`stat_bin()`).

Number of active sessions per week

Statistical summary for num_sessions_week.

summary(merge_vt_cumber_usage$num_sessions_week, na.rm = TRUE)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  0.000   0.385   0.846   1.575   1.900  19.692    7886

sd(merge_vt_cumber_usage$num_sessions_week, na.rm = TRUE)

[1] 2.154686

Number of active sessions per week, by school.

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

print(school_percentiles)

# A tibble: 17 × 4
   school                                  p25   p50   p75
   <chr>                                 <dbl> <dbl> <dbl>
 1 Anne Chesnutt Middle                  NA    NA    NA   
 2 Cumberland Virtual Academy 6-12       NA    NA    NA   
 3 Douglas Byrd Middle                   NA    NA    NA   
 4 Gray's Creek Middle                   NA    NA    NA   
 5 Hope Mills Middle                     NA    NA    NA   
 6 Howard Learning Academy Middle School NA    NA    NA   
 7 John R Griffin Middle                 NA    NA    NA   
 8 Lewis Chapel Middle                   NA    NA    NA   
 9 Luther Nick Jeralds Middle             0.3   0.6   1.2 
10 Mac Williams Middle                   NA    NA    NA   
11 Pine Forest Middle                    NA    NA    NA   
12 R Max Abbott Middle                   NA    NA    NA   
13 Reid Ross Classical Middle School     NA    NA    NA   
14 Seventy-First Classical Middle School NA    NA    NA   
15 South View Middle                     NA    NA    NA   
16 Spring Lake Middle                     0.69  1.46  2.77
17 Westover Middle                       NA    NA    NA

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

Step 3. Filter out students who do not meet usage threshold (N = 8639 to 8236). 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_sessions_week is less than once per week.

merge_vt_cumber_usage_final = merge_vt_cumber_usage %>%
# keep those who don't appear in usage OR students that meet usage threshold.
   filter(vt_present != 1 | num_sessions_week >= 1) 
print(merge_vt_cumber_usage_final)

# A tibble: 8,236 × 59
   student_id grade school moy_math_comp moy_math_date moy_ela_comp moy_ela_date
        <dbl> <dbl> <chr>          <dbl> <date>               <dbl> <date>      
 1 2287512462     6 Anne …           225 2026-01-14             215 2026-01-13  
 2 4431265988     6 Anne …           201 2026-01-14             200 2026-01-13  
 3 9779738622     6 Anne …           235 2026-01-15             235 2026-01-13  
 4 3968556194     6 Anne …           211 2026-01-14             225 2026-01-13  
 5 1925959163     6 Anne …           226 2026-01-14             226 2026-01-13  
 6 2638992577     6 Anne …           213 2026-01-14             209 2026-01-13  
 7 8925824655     6 Anne …           201 2026-01-14             209 2026-01-14  
 8 4634477858     6 Anne …           228 2026-01-14             217 2026-01-13  
 9 9836237186     6 Anne …           194 2026-01-14             198 2026-01-13  
10 1961537885     6 Anne …           249 2026-01-14             237 2026-01-13  
# ℹ 8,226 more rows
# ℹ 52 more variables: eoy_math_comp <dbl>, eoy_math_date <date>,
#   eoy_ela_comp <dbl>, eoy_ela_date <date>, gender <chr>, iep <dbl>,
#   ell <dbl>, frpl <lgl>, race_eth <chr>, at_risk <dbl>, school_num <dbl>,
#   moy_math_z <dbl>, eoy_math_z <dbl>, moy_ela_z <dbl>, eoy_ela_z <dbl>,
#   school_vt <chr>, school_BOY_date <date>, school_EOY_date <date>,
#   vt_acct_id <dbl>, num_days_active <dbl>, num_sessions_AI <dbl>, …

Step 4. Create treat: Equals 1 if students had at least 1 session per week. All else equals 0.

merge_vt_cumber_usage_final = merge_vt_cumber_usage_final %>%
  mutate(treat = ifelse(!is.na(num_sessions_week) &  num_sessions_week >= 1, 1, 0))
table(merge_vt_cumber_usage_final$treat)


   0    1 
7886  350

Inspect Merged Data

Missing Data Summary

Percentage missing for each variable.

missing_summary_overall = merge_vt_cumber_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_overall)

# A tibble: 60 × 2
   variable           percent_missing
   <chr>                        <dbl>
 1 frpl                         100  
 2 school_vt                     95.8
 3 school_BOY_date               95.8
 4 school_EOY_date               95.8
 5 vt_acct_id                    95.8
 6 num_days_active               95.8
 7 num_sessions_AI               95.8
 8 num_sessions_human            95.8
 9 num_classesviewed             95.8
10 num_essaysreviewed            95.8
# ℹ 50 more rows

write.csv(missing_summary_overall, file = "vt_cumber_summary_missing_vars.csv", row.names = FALSE)

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

missing_summary_by_treat = merge_vt_cumber_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: 118 × 3
   treat variable           percent_missing
   <dbl> <chr>                        <dbl>
 1     0 frpl                           100
 2     0 school_vt                      100
 3     0 school_BOY_date                100
 4     0 school_EOY_date                100
 5     0 vt_acct_id                     100
 6     0 num_days_active                100
 7     0 num_sessions_AI                100
 8     0 num_sessions_human             100
 9     0 num_classesviewed              100
10     0 num_essaysreviewed             100
# ℹ 108 more rows

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

Percentage of student who have both MOY and EOY data overall

#pct across schools
overall_pct = merge_vt_cumber_usage_final %>%
  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>                   <dbl>                  <dbl>
1           8236                    6015                   73.0

Percentage of student who have both MOY and EOY data by school

by_school_pct = merge_vt_cumber_usage_final %>%
  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: 17 × 4
   school           total_students students_with_all_te…¹ percent_with_all_tests
   <chr>                     <int>                  <dbl>                  <dbl>
 1 Seventy-First C…            387                    384                   99.2
 2 Cumberland Virt…            126                    124                   98.4
 3 Anne Chesnutt M…            492                    482                   98.0
 4 South View Midd…            564                    525                   93.1
 5 R Max Abbott Mi…            699                    635                   90.8
 6 Lewis Chapel Mi…            506                    439                   86.8
 7 Gray's Creek Mi…            979                    829                   84.7
 8 Luther Nick Jer…            248                    184                   74.2
 9 Douglas Byrd Mi…            642                    475                   74.0
10 Hope Mills Midd…            407                    300                   73.7
11 Pine Forest Mid…            631                    464                   73.5
12 Westover Middle             645                    469                   72.7
13 Spring Lake Mid…            292                    180                   61.6
14 Mac Williams Mi…            804                    412                   51.2
15 Howard Learning…             55                     19                   34.6
16 John R Griffin …            559                     94                   16.8
17 Reid Ross Class…            200                      0                    0  
# ℹ 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_cumber_usage_final %>%
  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: 19 × 5
   school                                treat     n n_both_math pct_both_math
   <chr>                                 <dbl> <int>       <int>         <dbl>
 1 Anne Chesnutt Middle                      0   492         489          99.4
 2 Cumberland Virtual Academy 6-12           0   126         126         100  
 3 Douglas Byrd Middle                       0   642         542          84.4
 4 Gray's Creek Middle                       0   979         903          92.2
 5 Hope Mills Middle                         0   407         384          94.3
 6 Howard Learning Academy Middle School     0    55          35          63.6
 7 John R Griffin Middle                     0   559         138          24.7
 8 Lewis Chapel Middle                       0   506         463          91.5
 9 Luther Nick Jeralds Middle                0   123          98          79.7
10 Luther Nick Jeralds Middle                1   125         111          88.8
11 Mac Williams Middle                       0   804         480          59.7
12 Pine Forest Middle                        0   631         532          84.3
13 R Max Abbott Middle                       0   699         660          94.4
14 Reid Ross Classical Middle School         0   200           0           0  
15 Seventy-First Classical Middle School     0   387         384          99.2
16 South View Middle                         0   564         533          94.5
17 Spring Lake Middle                        0    67          51          76.1
18 Spring Lake Middle                        1   225         196          87.1
19 Westover Middle                           0   645         556          86.2

#export
write.csv(both_math_by_school_treat, file = "vt_cumber_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_cumber_usage_final %>%
  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: 19 × 5
   school                                treat     n n_both_ela pct_both_ela
   <chr>                                 <dbl> <int>      <int>        <dbl>
 1 Anne Chesnutt Middle                      0   492        483         98.2
 2 Cumberland Virtual Academy 6-12           0   126        124         98.4
 3 Douglas Byrd Middle                       0   642        560         87.2
 4 Gray's Creek Middle                       0   979        882         90.1
 5 Hope Mills Middle                         0   407        320         78.6
 6 Howard Learning Academy Middle School     0    55         24         43.6
 7 John R Griffin Middle                     0   559        491         87.8
 8 Lewis Chapel Middle                       0   506        462         91.3
 9 Luther Nick Jeralds Middle                0   123         90         73.2
10 Luther Nick Jeralds Middle                1   125        112         89.6
11 Mac Williams Middle                       0   804        703         87.4
12 Pine Forest Middle                        0   631        543         86.1
13 R Max Abbott Middle                       0   699        650         93.0
14 Reid Ross Classical Middle School         0   200          0          0  
15 Seventy-First Classical Middle School     0   387        387        100  
16 South View Middle                         0   564        544         96.5
17 Spring Lake Middle                        0    67         50         74.6
18 Spring Lake Middle                        1   225        158         70.2
19 Westover Middle                           0   645        521         80.8

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

###Test info summary

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

summary_math_tests_present = merge_vt_cumber_usage_final %>%
  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       7886      7354    0.933      6833    0.866
2     1        350       324    0.926       325    0.929

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

summary_ela_tests_present = merge_vt_cumber_usage_final %>%
  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       7886      7255    0.920      7379    0.936
2     1        350       294    0.84        313    0.894

District Data Summary

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.

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

#school-level means and counts for TEST data
school_means = merge_vt_cumber_usage_final %>%
  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 = merge_vt_cumber_usage_final %>%
  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_cumber_usage_final %>%
  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_"
  )

#free reduced price lunch
frpl_pct = merge_vt_cumber_usage_final %>%
  group_by(school, frpl) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = frpl,
    values_from = pct,
    names_prefix = "frpl_"
  )

#english language learner
ell_pct = merge_vt_cumber_usage_final %>%
  group_by(school, ell) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = ell,
    values_from = pct,
    names_prefix = "ell_"
  )

#at-risk students
atrisk_pct = merge_vt_cumber_usage_final %>%
  group_by(school, at_risk) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = at_risk,
    values_from = pct,
    names_prefix = "atrisk_"
  )

#iep 
iep_pct = merge_vt_cumber_usage_final %>%
  group_by(school, iep) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = iep,
    values_from = pct,
    names_prefix = "iep_"
  )

# COMBINE summaries
school_summary = school_means %>%
  left_join(race_pct,   by = "school") %>%
  left_join(gender_pct, by = "school") %>%
  left_join(frpl_pct,   by = "school") %>%
  left_join(ell_pct,    by = "school") %>%
  left_join(atrisk_pct, by = "school") %>%
  left_join(iep_pct, by = "school")

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

print(school_summary)

# A tibble: 17 × 20
   school      n_students mean_moy_math mean_moy_ela race_A race_B race_H race_I
   <chr>            <dbl>         <dbl>        <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 Anne Chesn…        492          220.         214.   0.41   62.6   19.7   0.81
 2 Cumberland…        126          239.         226.   1.59   43.6   23.0   0.79
 3 Douglas By…        642          214.         210.   1.25   50.2   22.4   1.4 
 4 Gray's Cre…        979          222.         215.   1.33   28.4   15.1   2.25
 5 Hope Mills…        407          221.         214.   0.74   41.0   18.4   1.23
 6 Howard Lea…         55          204.         202.  NA      74.6   16.4  NA   
 7 John R Gri…        559          227.         216.   7.51   25.8   18.1   1.61
 8 Lewis Chap…        506          213.         207.   1.19   67     15.8   0.59
 9 Luther Nic…        248          215.         209.   0.81   72.2   13.7   0.4 
10 Mac Willia…        804          222.         213.   0.87   24.4   13.2   2.24
11 Pine Fores…        631          219.         213.   2.85   39.3   16.5   0.63
12 R Max Abbo…        699          219.         214.   3.72   42.8   15.3   0.86
13 Reid Ross …        200          NaN          NaN   NA      64     11    NA   
14 Seventy-Fi…        387          228.         220.   5.43   53.8   15.5   0.26
15 South View…        564          215.         211.   1.06   54.6   19.3   1.77
16 Spring Lak…        292          217.         211.   1.03   57.9   23.3   0.34
17 Westover M…        645          218.         212.   2.79   63.0   17.7   0.62
# ℹ 12 more variables: race_Multi <dbl>, race_P <dbl>, race_W <dbl>,
#   gender_F <dbl>, gender_M <dbl>, frpl_NA <dbl>, ell_0 <dbl>, ell_1 <dbl>,
#   atrisk_0 <dbl>, atrisk_1 <dbl>, iep_0 <dbl>, iep_1 <dbl>

write.csv(school_summary, "vt_cumber_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_cumber_usage_final %>%
  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_cumber_usage_final %>%
  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_cumber_usage_final %>%
  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_"
  )

#free reduced price lunch
frpl_pct_treat = merge_vt_cumber_usage_final %>%
  filter(treat == 1) %>% 
  group_by(school, frpl) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = frpl,
    values_from = pct,
    names_prefix = "frpl_"
  )

#english language learner
ell_pct_treat = merge_vt_cumber_usage_final %>%
  filter(treat == 1) %>% 
  group_by(school, ell) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = ell,
    values_from = pct,
    names_prefix = "ell_"
  )

#at-risk students
atrisk_pct_treat = merge_vt_cumber_usage_final %>%
  filter(treat == 1) %>% 
  group_by(school, at_risk) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = at_risk,
    values_from = pct,
    names_prefix = "atrisk_"
  )

#iep 
iep_pct_treat = merge_vt_cumber_usage_final %>%
  filter(treat == 1) %>% 
  group_by(school, iep) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = iep,
    values_from = pct,
    names_prefix = "iep_"
  )

# COMBINE summaries
school_summary_treat = school_means_treat %>%
  left_join(race_pct_treat,   by = "school") %>%
  left_join(gender_pct_treat, by = "school") %>%
  left_join(frpl_pct_treat,   by = "school") %>%
  left_join(ell_pct_treat,    by = "school") %>%
  left_join(atrisk_pct_treat, by = "school") %>%
  left_join(iep_pct_treat, by = "school")

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

print(school_summary_treat)

# A tibble: 2 × 20
  school       n_students mean_moy_math mean_moy_ela race_A race_B race_H race_I
  <chr>             <dbl>         <dbl>        <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1 Luther Nick…        125          217.         213.   0.8    70.4   15.2   0.8 
2 Spring Lake…        225          222.         215.   1.33   56.9   23.1   0.44
# ℹ 12 more variables: race_Multi <dbl>, race_W <dbl>, race_P <dbl>,
#   gender_F <dbl>, gender_M <dbl>, frpl_NA <dbl>, ell_0 <dbl>, ell_1 <dbl>,
#   atrisk_0 <dbl>, atrisk_1 <dbl>, iep_0 <dbl>, iep_1 <dbl>

write.csv(school_summary_treat, "vt_cumber_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_cumber_usage_final %>%
  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_cumber_usage_final %>%
  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_cumber_usage_final %>%
  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_"
  )

#free reduced price lunch
frpl_pct_comp = merge_vt_cumber_usage_final %>%
  filter(treat == 0) %>% 
  group_by(school, frpl) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = frpl,
    values_from = pct,
    names_prefix = "frpl_"
  )

#english language learner
ell_pct_comp = merge_vt_cumber_usage_final %>%
  filter(treat == 0) %>% 
  group_by(school, ell) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = ell,
    values_from = pct,
    names_prefix = "ell_"
  )

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

#iep 
iep_pct_comp = merge_vt_cumber_usage_final %>%
  filter(treat == 0) %>% 
  group_by(school, iep) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(school) %>%
  mutate(pct = (n / sum(n)) * 100) %>%
  select(-n) %>%
  pivot_wider(
    names_from = iep,
    values_from = pct,
    names_prefix = "iep_"
  )

# COMBINE summaries
school_summary_comp = school_means_comp %>%
  left_join(race_pct_comp,   by = "school") %>%
  left_join(gender_pct_comp, by = "school") %>%
  left_join(frpl_pct_comp,   by = "school") %>%
  left_join(ell_pct_comp,    by = "school") %>%
  left_join(atrisk_pct_comp, by = "school") %>%
  left_join(iep_pct_comp, by = "school")

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

print(school_summary_comp)

# A tibble: 17 × 20
   school      n_students mean_moy_math mean_moy_ela race_A race_B race_H race_I
   <chr>            <dbl>         <dbl>        <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 Anne Chesn…        492          220.         214.   0.41   62.6   19.7   0.81
 2 Cumberland…        126          239.         226.   1.59   43.6   23.0   0.79
 3 Douglas By…        642          214.         210.   1.25   50.2   22.4   1.4 
 4 Gray's Cre…        979          222.         215.   1.33   28.4   15.1   2.25
 5 Hope Mills…        407          221.         214.   0.74   41.0   18.4   1.23
 6 Howard Lea…         55          204.         202.  NA      74.6   16.4  NA   
 7 John R Gri…        559          227.         216.   7.51   25.8   18.1   1.61
 8 Lewis Chap…        506          213.         207.   1.19   67     15.8   0.59
 9 Luther Nic…        123          213.         205.   0.81   74.0   12.2  NA   
10 Mac Willia…        804          222.         213.   0.87   24.4   13.2   2.24
11 Pine Fores…        631          219.         213.   2.85   39.3   16.5   0.63
12 R Max Abbo…        699          219.         214.   3.72   42.8   15.3   0.86
13 Reid Ross …        200          NaN          NaN   NA      64     11    NA   
14 Seventy-Fi…        387          228.         220.   5.43   53.8   15.5   0.26
15 South View…        564          215.         211.   1.06   54.6   19.3   1.77
16 Spring Lak…         67          201.         197.  NA      61.2   23.9  NA   
17 Westover M…        645          218.         212.   2.79   63.0   17.7   0.62
# ℹ 12 more variables: race_Multi <dbl>, race_P <dbl>, race_W <dbl>,
#   gender_F <dbl>, gender_M <dbl>, frpl_NA <dbl>, ell_0 <dbl>, ell_1 <dbl>,
#   atrisk_0 <dbl>, atrisk_1 <dbl>, iep_0 <dbl>, iep_1 <dbl>

write.csv(school_summary_comp, "vt_cumber_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_cumber_usage_final %>%
  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     350                 25                 7.14                37
# ℹ 1 more variable: pct_missing_eoy_ela <dbl>

Usage Data Summary

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

merge_vt_cumber_usage_final$total_sessions = as.numeric(merge_vt_cumber_usage_final$total_sessions)

#create new any_usage for new tabulation                                                           
any_usage_by_treat = merge_vt_cumber_usage_final %>%
  mutate(any_usage = ifelse(total_sessions >= 1, 1, 0)) 
  #for treat=0 cases, any_usage will be NA

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

any_usage_by_treat = any_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(any_usage_by_treat)

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

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

any_usage_by_school = merge_vt_cumber_usage_final %>%
  mutate(any_usage = ifelse(total_sessions >= 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: 17 × 3
   school                                students_with_usage students_no_usage
   <chr>                                               <int>             <int>
 1 Anne Chesnutt Middle                                    0                 0
 2 Cumberland Virtual Academy 6-12                         0                 0
 3 Douglas Byrd Middle                                     0                 0
 4 Gray's Creek Middle                                     0                 0
 5 Hope Mills Middle                                       0                 0
 6 Howard Learning Academy Middle School                   0                 0
 7 John R Griffin Middle                                   0                 0
 8 Lewis Chapel Middle                                     0                 0
 9 Luther Nick Jeralds Middle                            125                 0
10 Mac Williams Middle                                     0                 0
11 Pine Forest Middle                                      0                 0
12 R Max Abbott Middle                                     0                 0
13 Reid Ross Classical Middle School                       0                 0
14 Seventy-First Classical Middle School                   0                 0
15 South View Middle                                       0                 0
16 Spring Lake Middle                                    225                 0
17 Westover Middle                                         0                 0

write.csv(any_usage_by_school, file = "vt_cumber_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_cumber_usage_final %>%
  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: 17 × 26
   school         num_sessions_AI_Q1 num_sessions_AI_median num_sessions_AI_mean
   <chr>                       <dbl>                  <dbl>                <dbl>
 1 Anne Chesnutt…                 NA                     NA               NaN   
 2 Cumberland Vi…                 NA                     NA               NaN   
 3 Douglas Byrd …                 NA                     NA               NaN   
 4 Gray's Creek …                 NA                     NA               NaN   
 5 Hope Mills Mi…                 NA                     NA               NaN   
 6 Howard Learni…                 NA                     NA               NaN   
 7 John R Griffi…                 NA                     NA               NaN   
 8 Lewis Chapel …                 NA                     NA               NaN   
 9 Luther Nick J…                  0                      1                 3.09
10 Mac Williams …                 NA                     NA               NaN   
11 Pine Forest M…                 NA                     NA               NaN   
12 R Max Abbott …                 NA                     NA               NaN   
13 Reid Ross Cla…                 NA                     NA               NaN   
14 Seventy-First…                 NA                     NA               NaN   
15 South View Mi…                 NA                     NA               NaN   
16 Spring Lake M…                  0                      1                 3.54
17 Westover 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_cumber_summary_usage_by_school.csv")

Export files

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

#select columns ready for e2i
vt_cumber_e2i_usage_test_info = merge_vt_cumber_usage_final %>%
  dplyr::select(student_id,
                school_num, school,  
                grade, school_grade,
                grade6, grade7, grade8,
                
                male, iep, ell, frpl, at_risk,
                race_recoded, race_B, race_H, race_Other, race_Multi, race_W,
                
                moy_math_comp, moy_math_z, 
                moy_ela_comp, moy_ela_z,
                eoy_math_comp, eoy_math_z,
                eoy_ela_comp, eoy_ela_z, 
                
                treat, vt_acct_id, vt_present,
                num_days_active, num_days_active_recoded, 
                num_weeks, num_sessions_week,total_sessions,
                num_sessions_AI, num_sessions_human,
                num_classesviewed, num_essaysreviewed, num_AI_practiceproblemsessions, 
                
                both_math, both_ela, all_tests, 
                math_moy_missing_eoy_present, ela_moy_missing_eoy_present)

#export e2i with test indicators, all matched students from the merge, regardless of missing test info (all_tests == 1 or 0)
write.csv(vt_cumber_e2i_usage_test_info, file = "vt_cumber_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_cumber_e2i_usage_test_info$both_math)


   0    1 
1555 6681

#subset: keep only students with complete test info 
vt_cumber_e2i_both_math = vt_cumber_e2i_usage_test_info %>% filter(both_math == 1)
print(vt_cumber_e2i_both_math) # N=6374

# A tibble: 6,681 × 45
   student_id school_num school    grade school_grade grade6 grade7 grade8  male
        <dbl>      <dbl> <chr>     <dbl> <chr>         <dbl>  <dbl>  <dbl> <dbl>
 1 2287512462          1 Anne Che…     6 Anne 6            1      0      0     1
 2 4431265988          1 Anne Che…     6 Anne 6            1      0      0     0
 3 9779738622          1 Anne Che…     6 Anne 6            1      0      0     1
 4 3968556194          1 Anne Che…     6 Anne 6            1      0      0     1
 5 1925959163          1 Anne Che…     6 Anne 6            1      0      0     1
 6 2638992577          1 Anne Che…     6 Anne 6            1      0      0     0
 7 8925824655          1 Anne Che…     6 Anne 6            1      0      0     0
 8 4634477858          1 Anne Che…     6 Anne 6            1      0      0     1
 9 9836237186          1 Anne Che…     6 Anne 6            1      0      0     1
10 1961537885          1 Anne Che…     6 Anne 6            1      0      0     1
# ℹ 6,671 more rows
# ℹ 36 more variables: iep <dbl>, ell <dbl>, frpl <lgl>, at_risk <dbl>,
#   race_recoded <chr>, race_B <dbl>, race_H <dbl>, race_Other <dbl>,
#   race_Multi <dbl>, race_W <dbl>, moy_math_comp <dbl>, moy_math_z <dbl>,
#   moy_ela_comp <dbl>, moy_ela_z <dbl>, eoy_math_comp <dbl>, eoy_math_z <dbl>,
#   eoy_ela_comp <dbl>, eoy_ela_z <dbl>, treat <dbl>, vt_acct_id <dbl>,
#   vt_present <dbl>, num_days_active <dbl>, num_days_active_recoded <dbl>, …

write.csv(vt_cumber_e2i_both_math, file = "vt_cumber_e2i_complete_math_tests.csv", row.names = FALSE)

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

table(vt_cumber_e2i_usage_test_info$both_ela)


   0    1 
1132 7104

#subset: keep only students with complete test info
vt_cumber_e2i_both_ela = vt_cumber_e2i_usage_test_info %>% filter(both_ela == 1)
print(vt_cumber_e2i_both_ela) # N=6834

# A tibble: 7,104 × 45
   student_id school_num school    grade school_grade grade6 grade7 grade8  male
        <dbl>      <dbl> <chr>     <dbl> <chr>         <dbl>  <dbl>  <dbl> <dbl>
 1 2287512462          1 Anne Che…     6 Anne 6            1      0      0     1
 2 4431265988          1 Anne Che…     6 Anne 6            1      0      0     0
 3 9779738622          1 Anne Che…     6 Anne 6            1      0      0     1
 4 3968556194          1 Anne Che…     6 Anne 6            1      0      0     1
 5 1925959163          1 Anne Che…     6 Anne 6            1      0      0     1
 6 2638992577          1 Anne Che…     6 Anne 6            1      0      0     0
 7 8925824655          1 Anne Che…     6 Anne 6            1      0      0     0
 8 4634477858          1 Anne Che…     6 Anne 6            1      0      0     1
 9 9836237186          1 Anne Che…     6 Anne 6            1      0      0     1
10 1961537885          1 Anne Che…     6 Anne 6            1      0      0     1
# ℹ 7,094 more rows
# ℹ 36 more variables: iep <dbl>, ell <dbl>, frpl <lgl>, at_risk <dbl>,
#   race_recoded <chr>, race_B <dbl>, race_H <dbl>, race_Other <dbl>,
#   race_Multi <dbl>, race_W <dbl>, moy_math_comp <dbl>, moy_math_z <dbl>,
#   moy_ela_comp <dbl>, moy_ela_z <dbl>, eoy_math_comp <dbl>, eoy_math_z <dbl>,
#   eoy_ela_comp <dbl>, eoy_ela_z <dbl>, treat <dbl>, vt_acct_id <dbl>,
#   vt_present <dbl>, num_days_active <dbl>, num_days_active_recoded <dbl>, …

write.csv(vt_cumber_e2i_both_ela, file = "vt_cumber_e2i_complete_ela_tests.csv", row.names = FALSE)

##Prepare file for pooling

Using the data frame where comparison students with unexpected usage have been removed, but treatment students of any usage level are included (), recreate treatment variable.

merge_vt_cumber_usage$treat <- ifelse(merge_vt_cumber_usage$vt_present == 1 & merge_vt_cumber_usage$num_sessions_week >= 1, 1, 0)
table(merge_vt_cumber_usage_final$treat)


   0    1 
7886  350

Standardize raw race var name for pooling.

merge_vt_cumber_usage$race = merge_vt_cumber_usage$race_eth
table(merge_vt_cumber_usage$race)


    A     B     H     I Multi     P     W 
  181  4069  1465   100   816    46  1962

Save a file for conducting analyses in R. Includes students offered Live+AI but who do not meet the usage threshold, i.e., no treat var in this export.

#select columns 
vt_cumber_all_students = merge_vt_cumber_usage %>%
  dplyr::select(student_id,
                school_num, school,  
                grade, school_grade,
                grade6, grade7, grade8,
                
                male, iep, ell, frpl, at_risk,
                race, #original race for pooled coding later 
                race_recoded, race_B, race_H, race_Other, race_Multi, race_W,
                
                moy_math_comp, moy_math_z, 
                moy_ela_comp, moy_ela_z,
                eoy_math_comp, eoy_math_z,
                eoy_ela_comp, eoy_ela_z, 
                
                treat,vt_acct_id, vt_present,
                num_days_active, num_days_active_recoded, 
                num_weeks, num_sessions_week,total_sessions,
                num_sessions_AI, num_sessions_human,
                num_classesviewed, num_essaysreviewed, num_AI_practiceproblemsessions, 
                
                both_math, both_ela, all_tests, 
                math_moy_missing_eoy_present, ela_moy_missing_eoy_present)

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