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

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

Create school_num for each school.

roster = roster %>%
  mutate(school_num = recode(school_ros,
                             "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(roster$school_num)


   1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16 
 642  149 1038 1322  576  676 1178 1112  793 1478   30  403  771 1245  223  418 
  17   18   19 
 696  699  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.

view(moy_map)

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.

view(eoy_map)

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 × 5
   school_code school_ros           student_id grade_ros school_num
         <dbl> <chr>                     <dbl>     <dbl>      <dbl>
 1      260336 Anne Chesnutt Middle 9581845356         6          1
 2      260336 Anne Chesnutt Middle 9581845356         6          1
 3      260336 Anne Chesnutt Middle 9581845356         6          1
 4      260336 Anne Chesnutt Middle 5396535695         6          1
 5      260336 Anne Chesnutt Middle 5396535695         6          1
 6      260336 Anne Chesnutt Middle 4978175585         6          1
 7      260336 Anne Chesnutt Middle 4978175585         6          1
 8      260336 Anne Chesnutt Middle 6429225941         6          1
 9      260336 Anne Chesnutt Middle 6429225941         6          1
10      260336 Anne Chesnutt Middle 6429225941         6          1
# ℹ 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 × 5
   school_code school_ros           student_id grade_ros school_num
         <dbl> <chr>                     <dbl>     <dbl>      <dbl>
 1      260336 Anne Chesnutt Middle 2287512462         6          1
 2      260336 Anne Chesnutt Middle 4431265988         6          1
 3      260336 Anne Chesnutt Middle 9779738622         6          1
 4      260336 Anne Chesnutt Middle 3968556194         6          1
 5      260336 Anne Chesnutt Middle 1925959163         6          1
 6      260336 Anne Chesnutt Middle 2638992577         6          1
 7      260336 Anne Chesnutt Middle 8925824655         6          1
 8      260336 Anne Chesnutt Middle 4634477858         6          1
 9      260336 Anne Chesnutt Middle 9836237186         6          1
10      260336 Anne Chesnutt Middle 1961537885         6          1
# ℹ 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 × 31
   school_code school_ros   student_id grade_ros school_num grade_moy school_moy
         <dbl> <chr>             <dbl>     <dbl>      <dbl>     <dbl> <chr>     
 1      260336 Anne Chesnu… 2287512462         6          1         6 Anne Ches…
 2      260336 Anne Chesnu… 4431265988         6          1         6 Anne Ches…
 3      260336 Anne Chesnu… 9779738622         6          1         6 Anne Ches…
 4      260336 Anne Chesnu… 3968556194         6          1         6 Anne Ches…
 5      260336 Anne Chesnu… 1925959163         6          1         6 Anne Ches…
 6      260336 Anne Chesnu… 2638992577         6          1         6 Anne Ches…
 7      260336 Anne Chesnu… 8925824655         6          1         6 Anne Ches…
 8      260336 Anne Chesnu… 4634477858         6          1         6 Anne Ches…
 9      260336 Anne Chesnu… 9836237186         6          1         6 Anne Ches…
10      260336 Anne Chesnu… 1961537885         6          1         6 Anne Ches…
# ℹ 8,638 more rows
# ℹ 24 more variables: treat_moy <lgl>, 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>, …

Check for requested data elements.

colnames(merge_vt_cumber_unclean)

 [1] "school_code"   "school_ros"    "student_id"    "grade_ros"    
 [5] "school_num"    "grade_moy"     "school_moy"    "treat_moy"    
 [9] "moy_math_comp" "moy_math_date" "moy_ela_comp"  "moy_ela_date" 
[13] "gender_moy"    "iep_moy"       "ell_moy"       "frpl_moy"     
[17] "race_eth_moy"  "at_risk_moy"   "grade_eoy"     "school_eoy"   
[21] "treat_eoy"     "eoy_math_comp" "eoy_math_date" "eoy_ela_comp" 
[25] "eoy_ela_date"  "gender_eoy"    "iep_eoy"       "ell_eoy"      
[29] "frpl_eoy"      "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(school_num, 
                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] "school_num"    "student_id"    "grade_moy"     "school_moy"   
 [5] "moy_math_comp" "moy_math_date" "moy_ela_comp"  "moy_ela_date" 
 [9] "eoy_math_comp" "eoy_math_date" "eoy_ela_comp"  "eoy_ela_date" 
[13] "gender_eoy"    "iep_moy"       "ell_moy"       "frpl_moy"     
[17] "race_eth_eoy"  "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] "school_num"    "student_id"    "grade"         "school"       
 [5] "moy_math_comp" "moy_math_date" "moy_ela_comp"  "moy_ela_date" 
 [9] "eoy_math_comp" "eoy_math_date" "eoy_ela_comp"  "eoy_ela_date" 
[13] "gender"        "iep"           "ell"           "frpl"         
[17] "race_eth"      "at_risk"

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.csv")

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

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

colnames(vt_usage_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"

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"
  )
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"

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

# 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: 697 × 2
   vt_acct_id     n
        <dbl> <int>
 1    4459726    24
 2    4484817    23
 3    4484829    23
 4   13156606    22
 5   13156624    20
 6    4503017    19
 7    4484796    18
 8    4485040    18
 9    4502731    18
10    4491050    17
# ℹ 687 more rows

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

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

dup_preview_usage_cumber = vt_usage_cumber %>%
  group_by(vt_acct_id) %>%
  filter(n() > 1) %>%
  ungroup()

print(dup_preview_usage_cumber)

# A tibble: 4,081 × 10
   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/2026        4/14/2026         13158251               6
 2 Luther Nick Jeral… 2/2/2026        4/14/2026          4502225              10
 3 Luther Nick Jeral… 2/2/2026        4/14/2026         13158251               6
 4 Luther Nick Jeral… 2/2/2026        4/14/2026          4482381               3
 5 Luther Nick Jeral… 2/2/2026        4/14/2026          4482381               3
 6 Luther Nick Jeral… 2/2/2026        4/14/2026          4505683              14
 7 Luther Nick Jeral… 2/2/2026        4/14/2026         13155952              14
 8 Luther Nick Jeral… 2/2/2026        4/14/2026         13160999               6
 9 Luther Nick Jeral… 2/2/2026        4/14/2026          4482466               6
10 Luther Nick Jeral… 2/2/2026        4/14/2026          4502186              10
# ℹ 4,071 more rows
# ℹ 5 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
#   num_classesviewed <dbl>, num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>

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

Basic check of randomly selected student, 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)

[1] 16

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)

# A tibble: 828 × 10
   vt_acct_id num_sessions_AI num_sessions_human num_classesviewed
        <dbl>           <dbl>              <dbl>             <dbl>
 1    4453277               0                  0                 0
 2    4454019               0                  0                 0
 3    4457033               1                  0                 0
 4    4457181               5                  0                 0
 5    4457225               0                  0                 0
 6    4458676               5                  0                 0
 7    4459067               0                  0                 0
 8    4459170               0                  0                 0
 9    4459172               3                  0                 0
10    4459173               0                  0                 0
# ℹ 818 more rows
# ℹ 6 more variables: num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, school_vt <chr>,
#   school_BOY_date <chr>, school_EOY_date <chr>, num_days_active <dbl>

Check number of IDs that appear more than once.

sum(table(vt_usage_cumber_collapsed$vt_acct_id) > 1)

[1] 0

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)

[1] 16

Append student ID from crosswalk file to usage file.

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

# A tibble: 828 × 11
   vt_acct_id num_sessions_AI num_sessions_human num_classesviewed
        <dbl>           <dbl>              <dbl>             <dbl>
 1    4453277               0                  0                 0
 2    4454019               0                  0                 0
 3    4457033               1                  0                 0
 4    4457181               5                  0                 0
 5    4457225               0                  0                 0
 6    4458676               5                  0                 0
 7    4459067               0                  0                 0
 8    4459170               0                  0                 0
 9    4459172               3                  0                 0
10    4459173               0                  0                 0
# ℹ 818 more rows
# ℹ 7 more variables: num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, school_vt <chr>,
#   school_BOY_date <chr>, school_EOY_date <chr>, num_days_active <dbl>,
#   student_id <dbl>

##Merge usage data to student Cumberland data.

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

print(merge_vt_cumber_usage)

# A tibble: 8,648 × 28
   school_num student_id grade school   moy_math_comp moy_math_date moy_ela_comp
        <dbl>      <dbl> <dbl> <chr>            <dbl> <chr>                <dbl>
 1          1 2287512462     6 Anne Ch…           225 1/14/2026              215
 2          1 4431265988     6 Anne Ch…           201 1/14/2026              200
 3          1 9779738622     6 Anne Ch…           235 1/15/2026              235
 4          1 3968556194     6 Anne Ch…           211 1/14/2026              225
 5          1 1925959163     6 Anne Ch…           226 1/14/2026              226
 6          1 2638992577     6 Anne Ch…           213 1/14/2026              209
 7          1 8925824655     6 Anne Ch…           201 1/14/2026              209
 8          1 4634477858     6 Anne Ch…           228 1/14/2026              217
 9          1 9836237186     6 Anne Ch…           194 1/14/2026              198
10          1 1961537885     6 Anne Ch…           249 1/14/2026              237
# ℹ 8,638 more rows
# ℹ 21 more variables: 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>,
#   vt_acct_id <dbl>, num_sessions_AI <dbl>, num_sessions_human <dbl>,
#   num_classesviewed <dbl>, num_essaysreviewed <dbl>,
#   num_AI_practiceproblemsessions <dbl>, school_vt <chr>, …

Check for unmatched students.

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

# A tibble: 0 × 28
# ℹ 28 variables: school_num <dbl>, 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>, vt_acct_id <dbl>,
#   num_sessions_AI <dbl>, num_sessions_human <dbl>, num_classesviewed <dbl>,
#   num_essaysreviewed <dbl>, num_AI_practiceproblemsessions <dbl>, …

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

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

Create treatment status

Step 1. Construct num_weeks.

Append start date and end date following district schedule.

merge_vt_cumber_usage = merge_vt_cumber_usage %>% 
  mutate(
    #start of implementation, per usage file
    SOI_date = as.Date("2026-02-02"),
    #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.

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] 13

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

Inspect num_days_active_week.

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

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  0.077   0.154   0.308   0.401   0.538   1.846    7886

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

[1] 0.3155854

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

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

[1] 0

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

Statistical summary for num_days_active.

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

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  1.000   2.000   4.000   5.215   7.000  24.000    7886

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

[1] 4.10261

Num_days_active, by school.

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

print(school_percentiles)

# A tibble: 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                    0.12  0.15  0.19
 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                    0.19  0.31  0.35
 9 Luther Nick Jeralds Middle             0.15  0.23  0.31
10 Mac Williams Middle                    1.08  1.08  1.08
11 Pine Forest Middle                    NA    NA    NA   
12 R Max Abbott Middle                   NA    NA    NA   
13 Reid Ross Classical Middle School      0.31  0.31  0.31
14 Seventy-First Classical Middle School NA    NA    NA   
15 South View Middle                     NA    NA    NA   
16 Spring Lake Middle                     0.31  0.54  0.77
17 Westover Middle                        0.12  0.15  0.19

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

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

7/6/2026: Since no students meet the threshold for usage, in the next step all students will be coded treat = 0.This will also impact the data summaries that follow.

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_days_active_week >= 3) 
print(merge_vt_cumber_usage_final)

# A tibble: 7,886 × 33
   school_num student_id grade school   moy_math_comp moy_math_date moy_ela_comp
        <dbl>      <dbl> <dbl> <chr>            <dbl> <date>               <dbl>
 1          1 2287512462     6 Anne Ch…           225 2026-01-14             215
 2          1 4431265988     6 Anne Ch…           201 2026-01-14             200
 3          1 9779738622     6 Anne Ch…           235 2026-01-15             235
 4          1 3968556194     6 Anne Ch…           211 2026-01-14             225
 5          1 1925959163     6 Anne Ch…           226 2026-01-14             226
 6          1 2638992577     6 Anne Ch…           213 2026-01-14             209
 7          1 8925824655     6 Anne Ch…           201 2026-01-14             209
 8          1 4634477858     6 Anne Ch…           228 2026-01-14             217
 9          1 9836237186     6 Anne Ch…           194 2026-01-14             198
10          1 1961537885     6 Anne Ch…           249 2026-01-14             237
# ℹ 7,876 more rows
# ℹ 26 more variables: moy_ela_date <date>, 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>, vt_acct_id <dbl>, num_sessions_AI <dbl>,
#   num_sessions_human <dbl>, num_classesviewed <dbl>,
#   num_essaysreviewed <dbl>, num_AI_practiceproblemsessions <dbl>, …

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

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


   0 
7886

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: 34 × 2
   variable                       percent_missing
   <chr>                                    <dbl>
 1 frpl                                       100
 2 vt_acct_id                                 100
 3 num_sessions_AI                            100
 4 num_sessions_human                         100
 5 num_classesviewed                          100
 6 num_essaysreviewed                         100
 7 num_AI_practiceproblemsessions             100
 8 school_vt                                  100
 9 school_BOY_date                            100
10 school_EOY_date                            100
# ℹ 24 more rows

write.csv(missing_summary_overall, file = "vt_cumber_summary_missing_vars", 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: 33 × 3
   treat variable                       percent_missing
   <dbl> <chr>                                    <dbl>
 1     0 frpl                                       100
 2     0 vt_acct_id                                 100
 3     0 num_sessions_AI                            100
 4     0 num_sessions_human                         100
 5     0 num_classesviewed                          100
 6     0 num_essaysreviewed                         100
 7     0 num_AI_practiceproblemsessions             100
 8     0 school_vt                                  100
 9     0 school_BOY_date                            100
10     0 school_EOY_date                            100
# ℹ 23 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

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

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

print(overall_pct)

# A tibble: 1 × 3
  total_students students_with_all_tests percent_with_all_tests
           <int>                   <int>                  <dbl>
1           7886                    5772                   73.2

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

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

print(by_school_pct)

# A tibble: 17 × 4
   school           total_students students_with_all_te…¹ percent_with_all_tests
   <chr>                     <int>                  <int>                  <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 Douglas Byrd Mi…            642                    475                   74.0
 9 Hope Mills Midd…            407                    300                   73.7
10 Pine Forest Mid…            631                    464                   73.5
11 Westover Middle             645                    469                   72.7
12 Luther Nick Jer…            123                     80                   65.0
13 Spring Lake Mid…             67                     41                   61.2
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 = final_vt_cumber_test_info %>%
  group_by(school, treat) %>%
  summarise(
    n = n(),
    n_both_math = sum(both_math == TRUE, na.rm = TRUE),
    pct_both_math = (n_both_math / n) * 100,
    .groups = "drop"
  )
print(both_math_by_school_treat)

# A tibble: 17 × 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 Mac Williams Middle                       0   804         480          59.7
11 Pine Forest Middle                        0   631         532          84.3
12 R Max Abbott Middle                       0   699         660          94.4
13 Reid Ross Classical Middle School         0   200           0           0  
14 Seventy-First Classical Middle School     0   387         384          99.2
15 South View Middle                         0   564         533          94.5
16 Spring Lake Middle                        0    67          51          76.1
17 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 = final_vt_cumber_test_info %>%
  group_by(school, treat) %>%
  summarise(
    n = n(),
    n_both_ela = sum(both_ela == TRUE, na.rm = TRUE),
    pct_both_ela = (n_both_ela / n) * 100,
    .groups = "drop"
  )
print(both_ela_by_school_treat)

# A tibble: 17 × 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 Mac Williams Middle                       0   804        703         87.4
11 Pine Forest Middle                        0   631        543         86.1
12 R Max Abbott Middle                       0   699        650         93.0
13 Reid Ross Classical Middle School         0   200          0          0  
14 Seventy-First Classical Middle School     0   387        387        100  
15 South View Middle                         0   564        544         96.5
16 Spring Lake Middle                        0    67         50         74.6
17 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)

Dummies for test info

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

table(final_vt_cumber_test_info$both_math)


FALSE  TRUE 
 1512  6374

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


   0    1 
1512 6374

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

table(final_vt_cumber_test_info$both_ela)


FALSE  TRUE 
 1052  6834

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


   0    1 
1052 6834

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

table(final_vt_cumber_test_info$all_tests)


FALSE  TRUE 
 2114  5772

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


   0    1 
2114 5772

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

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


   0    1 
6374  459

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

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


   0    1 
6834  545

###Test info summary

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

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

# View result
print(summary_math_tests_present)

# A tibble: 1 × 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

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

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

# View result
print(summary_ela_tests_present)

# A tibble: 1 × 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

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 = final_vt_cumber_test_info %>%
  group_by(school) %>%
  summarise(
    n_students = n(),
    mean_moy_math = mean(moy_math_comp, na.rm = TRUE),
    mean_moy_ela  = mean(moy_ela_comp, na.rm = TRUE)
  )

# DEMOGRAPHICS

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

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

#free reduced price lunch
frpl_pct = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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…        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_1 <dbl>, atrisk_NA <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 = final_vt_cumber_test_info %>%
  filter(treat == 1) %>% 
  group_by(school) %>%
  summarise(
    n_students = n(),
    mean_moy_math = mean(moy_math_comp, na.rm = TRUE),
    mean_moy_ela  = mean(moy_ela_comp, na.rm = TRUE)
  )

# DEMOGRAPHICS

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

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

#free reduced price lunch
frpl_pct_treat = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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: 0 × 4
# ℹ 4 variables: school <chr>, n_students <dbl>, mean_moy_math <dbl>,
#   mean_moy_ela <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 = final_vt_cumber_test_info %>%
  filter(treat == 0) %>% 
  group_by(school) %>%
  summarise(
    n_students = n(),
    mean_moy_math = mean(moy_math_comp, na.rm = TRUE),
    mean_moy_ela  = mean(moy_ela_comp, na.rm = TRUE)
  )

# DEMOGRAPHICS

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

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

#free reduced price lunch
frpl_pct_comp = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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 = final_vt_cumber_test_info %>%
  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_1 <dbl>, atrisk_NA <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 = final_vt_cumber_test_info %>%
  filter(treat == 1) %>%
  summarise(
    n_total = n(),
    
    # Math test missing
    n_missing_eoy_math = sum(is.na(eoy_math_comp)),
    pct_missing_eoy_math = (n_missing_eoy_math / n_total) * 100,
    
    # ELA test missing
    n_missing_eoy_ela = sum(is.na(eoy_ela_comp)),
    pct_missing_eoy_ela = (n_missing_eoy_ela / n_total) * 100
  )
print(summary_attrition)

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

Usage Data Summary

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

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

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

#for many treat=0 cases, recode any_usage from NA to 0. 
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: 1 × 3
  treat students_with_usage students_no_usage
  <dbl>               <int>             <int>
1     0                   0              7886

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

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

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

print(any_usage_by_school)

# A tibble: 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                              0                 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                                      0                 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 =  final_vt_cumber_test_info %>%
  group_by(school) %>%
  summarise(across(
    all_of(usage_vars),
    list(
      Q1 = ~ quantile(., 0.25, na.rm = TRUE),
      median = ~ median(., na.rm = TRUE),
      mean = ~ mean(., na.rm = TRUE),
      Q3 = ~ quantile(., 0.75, na.rm = TRUE),
      sd = ~ sd(., na.rm = TRUE)
    ),
    .names = "{.col}_{.fn}"
  ),
  .groups = "drop")
print(summary_usage_by_school)

# A tibble: 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…                 NA                     NA                  NaN
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…                 NA                     NA                  NaN
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")

Archived Data Summaries

ARCHIVE: Used merge_vt_cumber_usage containing all students, including students who some usage but do not meet the threshold. Original treat var is now vt_present, based on how treatment was originally defined (Equals 1 if student has account ID from crosswalk file).

Archived Missing Data Summary

Percentage missing for each variable. Note: percentage missing for at_risk probably indicates the percent NOT at-risk. Observations: FRPL may be missing for all students or district forgot to add to file. All other demographic info is present for all students. Some missing data for test info.

missing_summary_overall_archive = merge_vt_cumber_usage %>%
  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_archive)

# A tibble: 33 × 2
   variable                       percent_missing
   <chr>                                    <dbl>
 1 frpl                                     100  
 2 school_vt                                 91.4
 3 school_BOY_date                           91.4
 4 school_EOY_date                           91.4
 5 vt_acct_id                                91.2
 6 num_sessions_AI                           91.2
 7 num_sessions_human                        91.2
 8 num_classesviewed                         91.2
 9 num_essaysreviewed                        91.2
10 num_AI_practiceproblemsessions            91.2
# ℹ 23 more rows

write.csv(missing_summary_overall_archive, file = "vt_cumber_summary_missing_vars_archive", row.names = FALSE)

Percentage missing for each variable, grouped by [vt_present] treatment. Exported.

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

print(missing_summary_by_treat_archive)

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

#export
write.csv(missing_summary_by_treat_archive, file = "vt_cumber_pct_missing_vars_by_treat_archive.csv")

Percentage of student who have both MOY and EOY data overall

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

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

print(overall_pct_archive)

# A tibble: 1 × 3
  total_students students_with_all_tests percent_with_all_tests
           <int>                   <int>                  <dbl>
1           8648                    6311                   73.0

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

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

print(by_school_pct_archive)

# A tibble: 17 × 4
   school           total_students students_with_all_te…¹ percent_with_all_tests
   <chr>                     <int>                  <int>                  <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…            509                    441                   86.6
 7 Gray's Creek Mi…            979                    829                   84.7
 8 Luther Nick Jer…            515                    389                   75.5
 9 Douglas Byrd Mi…            644                    476                   73.9
10 Hope Mills Midd…            407                    300                   73.7
11 Pine Forest Mid…            631                    464                   73.5
12 Westover Middle             647                    469                   72.5
13 Spring Lake Mid…            428                    268                   62.6
14 Mac Williams Mi…            805                    412                   51.2
15 Howard Learning…             55                     19                   34.6
16 John R Griffin …            559                     94                   16.8
17 Reid Ross Class…            201                      0                    0  
# ℹ abbreviated name: ¹students_with_all_tests

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

both_math_by_school_treat_archive = archive_vt_cumber_test_info %>%
  group_by(school, vt_present) %>%
  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_archive)

# A tibble: 24 × 5
   school                             vt_present     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 Douglas Byrd Middle                         1     2           2         100  
 5 Gray's Creek Middle                         0   979         903          92.2
 6 Hope Mills Middle                           0   407         384          94.3
 7 Howard Learning Academy Middle Sc…          0    55          35          63.6
 8 John R Griffin Middle                       0   559         138          24.7
 9 Lewis Chapel Middle                         0   506         463          91.5
10 Lewis Chapel Middle                         1     3           3         100  
# ℹ 14 more rows

#export
write.csv(both_math_by_school_treat_archive, file = "vt_cumber_pct_both_math_by_school_treat_archive.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_archive = archive_vt_cumber_test_info %>%
  group_by(school, vt_present) %>%
  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_archive)

# A tibble: 24 × 5
   school                               vt_present     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 Douglas Byrd Middle                           1     2          1         50  
 5 Gray's Creek Middle                           0   979        882         90.1
 6 Hope Mills Middle                             0   407        320         78.6
 7 Howard Learning Academy Middle Scho…          0    55         24         43.6
 8 John R Griffin Middle                         0   559        491         87.8
 9 Lewis Chapel Middle                           0   506        462         91.3
10 Lewis Chapel Middle                           1     3          2         66.7
# ℹ 14 more rows

#export
write.csv(both_ela_by_school_treat_archive, file = "vt_cumber_pct_both_ela_by_school_treat_archive.csv", row.names = FALSE)

Archived Dummies for test info

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

table(archive_vt_cumber_test_info$both_math)


FALSE  TRUE 
 1610  7038

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


   0    1 
1610 7038

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

table(archive_vt_cumber_test_info$both_ela)


FALSE  TRUE 
 1215  7433

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


   0    1 
1215 7433

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

table(archive_vt_cumber_test_info$all_tests)


FALSE  TRUE 
 2337  6311

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


   0    1 
2337 6311

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

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


   0    1 
7038  512

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

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


   0    1 
7433  626

Archived Test info summary

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

summary_math_tests_presen_archive <- archive_vt_cumber_test_info %>%
  mutate(
    moy_math_present = !is.na(moy_math_comp),
    eoy_math_present = !is.na(eoy_math_comp)
  ) %>%
  group_by(vt_present) %>%
  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_presen_archive)

# A tibble: 2 × 6
  vt_present 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        762       695    0.912       717    0.941

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

summary_ela_tests_present_archive <- archive_vt_cumber_test_info %>%
  mutate(
    moy_ela_present = !is.na(moy_ela_comp),
    eoy_ela_present = !is.na(eoy_ela_comp)
  ) %>%
  group_by(vt_present) %>%
  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_archive)

# A tibble: 2 × 6
  vt_present 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        762       657    0.862       680    0.892

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

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

# DEMOGRAPHICS

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

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

#free reduced price lunch
frpl_pct_archive = archive_vt_cumber_test_info %>%
  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_archive = archive_vt_cumber_test_info %>%
  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_archive = archive_vt_cumber_test_info %>%
  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_archive = archive_vt_cumber_test_info %>%
  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_archive = school_means_archive %>%
  left_join(race_pct_archive,   by = "school") %>%
  left_join(gender_pct_archive, by = "school") %>%
  left_join(frpl_pct_archive,   by = "school") %>%
  left_join(ell_pct_archive,    by = "school") %>%
  left_join(atrisk_pct_archive, by = "school") %>%
  left_join(iep_pct_archive, by = "school")

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

print(school_summary_archive)

# 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…        644          214.         210.   1.24   50     22.7   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…        509          213.         207.   1.18   67.2   15.7   0.59
 9 Luther Nic…        515          216.         210.   0.78   71.1   13.4   0.58
10 Mac Willia…        805          222.         213.   0.87   24.5   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 …        201          NaN          NaN   NA      63.7   11.0  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…        428          216.         211.   1.17   59.8   21.3   0.23
17 Westover M…        647          218.         212.   2.78   63.1   17.6   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_1 <dbl>, atrisk_NA <dbl>, iep_0 <dbl>, iep_1 <dbl>

write.csv(school_summary_archive, "vt_cumber_overall_district_data_summary_by_school_archive.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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = school_means_treat_archive %>%
  left_join(race_pct_treat_archive,   by = "school") %>%
  left_join(gender_pct_treat_archive, by = "school") %>%
  left_join(frpl_pct_treat_archive,   by = "school") %>%
  left_join(ell_pct_treat_archive,    by = "school") %>%
  left_join(atrisk_pct_treat_archive, by = "school") %>%
  left_join(iep_pct_treat_archive, by = "school")

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

print(school_summary_treat_archive)

# A tibble: 7 × 20
  school       n_students mean_moy_math mean_moy_ela race_H race_B race_A race_I
  <chr>             <dbl>         <dbl>        <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1 Douglas Byr…          2          222.         217   100     NA    NA     NA   
2 Lewis Chape…          3          206          199    NA    100    NA     NA   
3 Luther Nick…        392          217.         212.   13.8   70.2   0.77   0.77
4 Mac William…          1          NaN          NaN    NA    100    NA     NA   
5 Reid Ross C…          1          NaN          NaN    NA     NA    NA     NA   
6 Spring Lake…        361          219.         213.   20.8   59.6   1.39   0.28
7 Westover Mi…          2          NaN          NaN    NA    100    NA     NA   
# ℹ 12 more variables: race_Multi <dbl>, race_P <dbl>, race_W <dbl>,
#   gender_M <dbl>, gender_F <dbl>, frpl_NA <dbl>, ell_0 <dbl>, ell_1 <dbl>,
#   atrisk_NA <dbl>, atrisk_1 <dbl>, iep_0 <dbl>, iep_1 <dbl>

write.csv(school_summary_treat_archive, "vt_cumber_district_data_summary_by_school_treat_students_archive.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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = archive_vt_cumber_test_info %>%
  filter(vt_present == 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_archive = school_means_comp_archive %>%
  left_join(race_pct_comp_archive,   by = "school") %>%
  left_join(gender_pct_comp_archive, by = "school") %>%
  left_join(frpl_pct_comp_archive,   by = "school") %>%
  left_join(ell_pct_comp_archive,    by = "school") %>%
  left_join(atrisk_pct_comp_archive, by = "school") %>%
  left_join(iep_pct_comp_archive, by = "school")

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

print(school_summary_comp_archive)

# A tibble: 17 × 4
   school                                n_students mean_moy_math mean_moy_ela
   <chr>                                      <dbl>         <dbl>        <dbl>
 1 Anne Chesnutt Middle                         492          220.         214.
 2 Cumberland Virtual Academy 6-12              126          239.         226.
 3 Douglas Byrd Middle                          642          214.         210.
 4 Gray's Creek Middle                          979          222.         215.
 5 Hope Mills Middle                            407          221.         214.
 6 Howard Learning Academy Middle School         55          204.         202.
 7 John R Griffin Middle                        559          227.         216.
 8 Lewis Chapel Middle                          506          213.         207.
 9 Luther Nick Jeralds Middle                   123          213.         205.
10 Mac Williams Middle                          804          222.         213.
11 Pine Forest Middle                           631          219.         213.
12 R Max Abbott Middle                          699          219.         214.
13 Reid Ross Classical Middle School            200          NaN          NaN 
14 Seventy-First Classical Middle School        387          228.         220.
15 South View Middle                            564          215.         211.
16 Spring Lake Middle                            67          201.         197.
17 Westover Middle                              645          218.         212.

write.csv(school_summary_comp_archive, "vt_cumber_district_data_summary_by_school_comp_students_archive.csv", row.names = FALSE)

Archived Attrition

Percent of students who did not take the EOY assessment, per subject, within [vt_present=1] treatment group.

archive_summary_attrition = archive_vt_cumber_test_info %>%
  filter(vt_present == 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(archive_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     762                 45                 5.91                82
# ℹ 1 more variable: pct_missing_eoy_ela <dbl>

Archived Usage Data Summary

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

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

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

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

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

print(archive_any_usage_by_treat)

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

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

arhive_any_usage_by_school = archive_vt_cumber_test_info %>%
  mutate(any_usage = ifelse(num_days_active >= 1, 1, 0)) 

arhive_any_usage_by_school = arhive_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(arhive_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                                     2                 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                                     3                 0
 9 Luther Nick Jeralds Middle                            392                 0
10 Mac Williams Middle                                     1                 0
11 Pine Forest Middle                                      0                 0
12 R Max Abbott Middle                                     0                 0
13 Reid Ross Classical Middle School                       1                 0
14 Seventy-First Classical Middle School                   0                 0
15 South View Middle                                       0                 0
16 Spring Lake Middle                                    361                 0
17 Westover Middle                                         2                 0

write.csv(arhive_any_usage_by_school, file = "vt_cumber_any_usage_by_school_archive.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"
)


archive_summary_usage_by_school =  archive_vt_cumber_test_info %>%
  group_by(school) %>%
  summarise(across(
    all_of(usage_vars),
    list(
      Q1 = ~ quantile(., 0.25, na.rm = TRUE),
      median = ~ median(., na.rm = TRUE),
      mean = ~ mean(., na.rm = TRUE),
      Q3 = ~ quantile(., 0.75, na.rm = TRUE),
      sd = ~ sd(., na.rm = TRUE)
    ),
    .names = "{.col}_{.fn}"
  ),
  .groups = "drop")
print(archive_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 …               1.25                    2.5                2.5  
 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 …               0                       0                  0.333
 9 Luther Nick J…               0                       1                  1.88 
10 Mac Williams …              19                      19                 19    
11 Pine Forest M…              NA                      NA                NaN    
12 R Max Abbott …              NA                      NA                NaN    
13 Reid Ross Cla…               0                       0                  0    
14 Seventy-First…              NA                      NA                NaN    
15 South View Mi…              NA                      NA                NaN    
16 Spring Lake M…               0                       0                  2.47 
17 Westover Midd…               0.5                     1                  1    
# ℹ 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(archive_summary_usage_by_school, file = "vt_cumber_summary_usage_by_school_archive.csv")

END ARCHIVE

Reformat Data

Create dummy variables

Create numeric binary for gender.

table(final_vt_cumber_test_info$gender)


   F    M 
3805 4081

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


   0    1 
3805 4081

Create numeric binaries for each race.

table(final_vt_cumber_test_info$race_eth)


    A     B     H     I Multi     P     W 
  173  3579  1336    96   755    43  1904

final_vt_cumber_test_info = final_vt_cumber_test_info %>%
  mutate(
    race_A     = ifelse(race_eth == "A", 1, ifelse(is.na(race_eth), NA, 0)),
    race_B     = ifelse(race_eth == "B", 1, ifelse(is.na(race_eth), NA, 0)),
    race_H     = ifelse(race_eth == "H", 1, ifelse(is.na(race_eth), NA, 0)),
    race_I     = ifelse(race_eth == "I", 1, ifelse(is.na(race_eth), NA, 0)),
    race_Multi     = ifelse(race_eth == "Multi", 1, ifelse(is.na(race_eth), NA, 0)),
    race_P     = ifelse(race_eth == "P", 1, ifelse(is.na(race_eth), NA, 0)),
    race_W     = ifelse(race_eth == "W", 1, ifelse(is.na(race_eth), NA, 0))
  )
table(final_vt_cumber_test_info$race_B) #quick check coding on race_B


   0    1 
4307 3579

Export files for e2i Coach

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

#select columns ready for e2i
vt_cumber_e2i_usage_test_info = final_vt_cumber_test_info %>%
  dplyr::select(school_num, 
                student_id, 
                grade, 
                moy_math_comp, moy_math_date, 
                moy_ela_comp,moy_ela_date, 
                eoy_math_comp, eoy_math_date,
                eoy_ela_comp, eoy_ela_date,
                male, iep, ell,frpl, at_risk,
                treat,
                race_A, race_B, race_H, race_I, race_Multi, race_P, race_W,
                both_math, both_ela, all_tests, 
                math_moy_missing_eoy_present, ela_moy_missing_eoy_present,
                
                vt_acct_id,
                num_days_active,num_sessions_AI, num_sessions_human,
                num_classesviewed, num_essaysreviewed, num_AI_practiceproblemsessions, 
                school_BOY_date, school_EOY_date)

#export e2i with test indicators, all matched students from the merge, regardless of missing test info (all_tests == 1 or 0)
write.csv(vt_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 
1512 6374

#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,374 × 38
   school_num student_id grade moy_math_comp moy_math_date moy_ela_comp
        <dbl>      <dbl> <dbl>         <dbl> <date>               <dbl>
 1          1 2287512462     6           225 2026-01-14             215
 2          1 4431265988     6           201 2026-01-14             200
 3          1 9779738622     6           235 2026-01-15             235
 4          1 3968556194     6           211 2026-01-14             225
 5          1 1925959163     6           226 2026-01-14             226
 6          1 2638992577     6           213 2026-01-14             209
 7          1 8925824655     6           201 2026-01-14             209
 8          1 4634477858     6           228 2026-01-14             217
 9          1 9836237186     6           194 2026-01-14             198
10          1 1961537885     6           249 2026-01-14             237
# ℹ 6,364 more rows
# ℹ 32 more variables: moy_ela_date <date>, eoy_math_comp <dbl>,
#   eoy_math_date <date>, eoy_ela_comp <dbl>, eoy_ela_date <date>, male <dbl>,
#   iep <dbl>, ell <dbl>, frpl <lgl>, at_risk <dbl>, treat <dbl>, race_A <dbl>,
#   race_B <dbl>, race_H <dbl>, race_I <dbl>, race_Multi <dbl>, race_P <dbl>,
#   race_W <dbl>, both_math <dbl>, both_ela <dbl>, all_tests <dbl>,
#   math_moy_missing_eoy_present <dbl>, ela_moy_missing_eoy_present <dbl>, …

write.csv(vt_cumber_e2i_both_math, file = "vt_cumber_e2i_complete_math_tests.csv")

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 
1052 6834

#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: 6,834 × 38
   school_num student_id grade moy_math_comp moy_math_date moy_ela_comp
        <dbl>      <dbl> <dbl>         <dbl> <date>               <dbl>
 1          1 2287512462     6           225 2026-01-14             215
 2          1 4431265988     6           201 2026-01-14             200
 3          1 9779738622     6           235 2026-01-15             235
 4          1 3968556194     6           211 2026-01-14             225
 5          1 1925959163     6           226 2026-01-14             226
 6          1 2638992577     6           213 2026-01-14             209
 7          1 8925824655     6           201 2026-01-14             209
 8          1 4634477858     6           228 2026-01-14             217
 9          1 9836237186     6           194 2026-01-14             198
10          1 1961537885     6           249 2026-01-14             237
# ℹ 6,824 more rows
# ℹ 32 more variables: moy_ela_date <date>, eoy_math_comp <dbl>,
#   eoy_math_date <date>, eoy_ela_comp <dbl>, eoy_ela_date <date>, male <dbl>,
#   iep <dbl>, ell <dbl>, frpl <lgl>, at_risk <dbl>, treat <dbl>, race_A <dbl>,
#   race_B <dbl>, race_H <dbl>, race_I <dbl>, race_Multi <dbl>, race_P <dbl>,
#   race_W <dbl>, both_math <dbl>, both_ela <dbl>, all_tests <dbl>,
#   math_moy_missing_eoy_present <dbl>, ela_moy_missing_eoy_present <dbl>, …

write.csv(vt_cumber_e2i_both_ela, file = "vt_cumber_e2i_complete_ela_tests.csv")