Packages selected for inspecting and cleaning Varsity Tutors, district data from Kent School District.
library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.2.1 ✔ readr 2.2.0
✔ forcats 1.0.1 ✔ stringr 1.6.0
✔ ggplot2 4.0.3 ✔ tibble 3.3.1
✔ lubridate 1.9.5 ✔ tidyr 1.3.2
✔ purrr 1.2.2
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
Import roster.6/29/26 Note: Using latest roster, Kent SD_Varsity Tutors request 5-12-2026.xlxs.
roster <-read_csv("roster_vt_kent.csv")
Rows: 5634 Columns: 19
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (13): Grade, School, MOY i-Ready Math Composite Scaled Score*, MOY i-Rea...
dbl (5): Student Number, Special Ed, MLE, Low Income, LAP
lgl (1): Treatment
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Inspect data variables. Data file appears to contain all requested elements. MLE, or multilingual education status, presents in place of ELL status. Low Income presents in place of Free/Reduced Price Lunch status. Sped, or Special Education, presents in place of IEP status.
colnames(roster)
[1] "Student Number"
[2] "Grade"
[3] "School"
[4] "Treatment"
[5] "MOY i-Ready Math Composite Scaled Score*"
[6] "MOY i-Ready Math Math Test Date*"
[7] "EOY i-Ready Math Composite Scaled Score*"
[8] "EOY i-Ready Math Math Test Date*"
[9] "MOY i-Ready ELA Composite Scaled Score*"
[10] "MOY i-Ready ELA Test Date*"
[11] "EOY i-Ready ELA Composite Scaled Score*"
[12] "EOY i-Ready ELA Test Date*"
[13] "Gender Code"
[14] "Special Ed"
[15] "MLE"
[16] "Low Income"
[17] "Race/Ethnicity"
[18] "LAP"
[19] "MLE Level"
Change column headers for roster.
roster = roster %>%rename(student_id ="Student Number",grade ="Grade", school ="School", treat ="Treatment", moy_math_comp ="MOY i-Ready Math Composite Scaled Score*", moy_math_date ="MOY i-Ready Math Math Test Date*", moy_ela_comp ="MOY i-Ready ELA Composite Scaled Score*", moy_ela_date ="MOY i-Ready ELA Test Date*", eoy_math_comp ="EOY i-Ready Math Composite Scaled Score*", eoy_math_date ="EOY i-Ready Math Math Test Date*", eoy_ela_comp ="EOY i-Ready ELA Composite Scaled Score*", eoy_ela_date ="EOY i-Ready ELA Test Date*", gender ="Gender Code", sped ="Special Ed", mll ="MLE", #multilingual education/multilingual learnersmll_lvl ="MLE Level",low_inc ="Low Income", race_eth ="Race/Ethnicity", low_ap ="LAP"#low academic performance )colnames(roster)
Warning: There were 4 warnings in `mutate()`.
The first warning was:
ℹ In argument: `across(...)`.
Caused by warning:
! NAs introduced by coercion
ℹ Run `dplyr::last_dplyr_warnings()` to see the 3 remaining warnings.
Using the i-Ready Diagnostic National Norms Tables for Reading and Mathematics 2025-2026 to standardize test scores.
MATH MOY
# Winter i-Ready Math norms for Grades 6-8moy_math_norms =tibble(grade =c(6, 7, 8),moy_mean_math =c(481.74, 490.87, 499.36),moy_sd_math =c(35.80, 38.18, 40.64))# Merge norms and calculate standardized scoresroster_unique = roster_unique %>%left_join(moy_math_norms, by ="grade") %>%mutate(moy_math_z =round((moy_math_comp - moy_mean_math) / moy_sd_math, 2) ) %>%select(-moy_mean_math, -moy_sd_math)summary(roster_unique$moy_math_z)
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
-5.2400 -0.9100 -0.1100 -0.1792 0.5800 4.3000 665
MATH EOY
# Spring i-Ready Math norms for Grades 6-8eoy_ela_norms =tibble(grade =c(6, 7, 8),eoy_mean_math =c(489.19, 497.22, 503.92),eoy_sd_math =c(39.04, 41.14, 44.29))# Merge norms and calculate standardized scoresroster_unique = roster_unique %>%left_join(eoy_ela_norms, by ="grade") %>%mutate(eoy_math_z =round((eoy_math_comp - eoy_mean_math) / eoy_sd_math, 2) ) %>%select(-eoy_mean_math, -eoy_sd_math)summary(roster_unique$eoy_math_z)
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
-6.0700 -1.0000 -0.1800 -0.2633 0.4500 3.8400 418
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
-5.9000 -0.9600 -0.0800 -0.2768 0.6100 3.2100 533
ELA EOY
# Spring i-Ready Reading norms for Grades 6-8eoy_ela_norms =tibble(grade =c(6, 7, 8),eoy_mean_ela =c(583.99, 569.61, 607.86),eoy_sd_ela =c(56.74, 57.20, 56.12))# Merge norms and calculate standardized scoresroster_unique = roster_unique %>%left_join(eoy_ela_norms, by ="grade") %>%mutate(eoy_ela_z =round((eoy_ela_comp - eoy_mean_ela) / eoy_sd_ela, 2) ) %>%select(-eoy_mean_ela, -eoy_sd_ela)summary(roster_unique$eoy_ela_z)
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
-5.4000 -0.8800 0.0600 -0.1506 0.7500 2.9400 433
Merge Usage Data to District Data
Clean Kent Usage Data
Import roster from VT that details which students have accounts in their system (cross walk file).
#import ID crosswalk from VTvt_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.
#import kent usage data from VTvt_usage_kent =read_csv("vt_usage_kent_updated.csv")
Rows: 998 Columns: 11
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (3): Sch_name*, Sch_BOY_date*, Sch_EOY_date
dbl (8): Stu_ID_VT*, Num_days_active*, Num_sessions_AI, Num_sessions_human, ...
ℹ 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.
# 7/14/26: new usage data, already collapsed at student level. colnames(vt_usage_kent)
Inspect rows where Account ID is missing. Observation: empty rows from csv conversion.
vt_usage_kent %>%filter(is.na(vt_acct_id))
# A tibble: 910 × 11
school_vt school_BOY_date school_EOY_date vt_acct_id num_days_active
<chr> <chr> <chr> <dbl> <dbl>
1 <NA> <NA> <NA> NA NA
2 <NA> <NA> <NA> NA NA
3 <NA> <NA> <NA> NA NA
4 <NA> <NA> <NA> NA NA
5 <NA> <NA> <NA> NA NA
6 <NA> <NA> <NA> NA NA
7 <NA> <NA> <NA> NA NA
8 <NA> <NA> <NA> NA NA
9 <NA> <NA> <NA> NA NA
10 <NA> <NA> <NA> NA NA
# ℹ 900 more rows
# ℹ 6 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
# num_classesviewed <dbl>, num_essaysreviewed <dbl>,
# num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>
# A tibble: 88 × 11
school_vt school_BOY_date school_EOY_date vt_acct_id num_days_active
<chr> <chr> <chr> <dbl> <dbl>
1 Meridian Middle 2/2/2026 4/14/2026 13151682 37
2 Meridian Middle 2/2/2026 4/14/2026 13151686 30
3 Meridian Middle 2/2/2026 4/14/2026 13151692 30
4 Meridian Middle 2/2/2026 4/14/2026 13152099 29
5 Meridian Middle 2/2/2026 4/14/2026 13151845 29
6 Meridian Middle 2/2/2026 4/14/2026 13151945 29
7 Meridian Middle 2/2/2026 4/14/2026 13151685 28
8 Meridian Middle 2/2/2026 4/14/2026 13151823 27
9 Meridian Middle 2/2/2026 4/14/2026 13151799 27
10 Meridian Middle 2/2/2026 4/14/2026 13152097 27
# ℹ 78 more rows
# ℹ 6 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
# num_classesviewed <dbl>, num_essaysreviewed <dbl>,
# num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>
No duplicates // Archive: Inspect duplicate student records in usage file (based on VT Account ID - not student ID). Observation: for a given duplicate student record, school name,num_days_active, BOY date and EOY date will match but usage data differs across the duplicate records.
No duplicates // Archive: Decision: Collapse rows for each student ID. Per VT, these instances are rows that only capture one of the students multiple days active and need to be compressed.
No duplicates // Archive: Basic check of randomly selected student, 13151681. Inspect sum of Num_sessions_AI before collapsing.
Decision: Collapse smallest groups to one level within race category. Keep to 5 levels max.
table(merge_vt_kent_usage$race_eth)
American Indian or Alaska Native
21
Asian
1365
Black or African American
760
Hispanic/Latino
1312
Native Hawaiian or Other Pacific Islander
189
Two or More Races
512
White
1475
merge_vt_kent_usage = merge_vt_kent_usage %>%mutate(race_recoded =case_when(race_eth %in%c("American Indian or Alaska Native","Native Hawaiian or Other Pacific Islander", "Two or More Races") ~"Native/Other",TRUE~ race_eth ))table(merge_vt_kent_usage$race_recoded)
Asian Black or African American Hispanic/Latino
1365 760 1312
Native/Other White
722 1475
#indicator for having BOTH MOY and EOY scoresmerge_vt_kent_usage = merge_vt_kent_usage %>%mutate(both_math =!is.na(moy_math_comp) &!is.na(eoy_math_comp),both_ela =!is.na(moy_ela_comp) &!is.na(eoy_ela_comp),all_tests = both_math & both_ela )
both_math: dummy for students with complete math test info across MOY and EOY.
all_tests: dummy for students with both math and ELA test info complete across MOY and EOY.
table(merge_vt_kent_usage$all_tests)
FALSE TRUE
1101 4533
#complete math and ELA: MOY and EOY presentmerge_vt_kent_usage$all_tests =ifelse(merge_vt_kent_usage$all_tests, 1, 0)table(merge_vt_kent_usage$all_tests) #check counts
0 1
1101 4533
math_moy_missing_eoy_present: dummy for students who have EOY math test info but MOY test info is missing.
Append start date and end date following district schedule.
merge_vt_kent_usage = merge_vt_kent_usage %>%mutate(#start of implementation, per usage fileSOI_date =as.Date("2026-02-02"),#end of implementation Fri 4/10/2026 (last school day before EOY testing week)EOI_date =as.Date("2026-04-10") )#transform other dates as.datesmerge_vt_kent_usage = merge_vt_kent_usage %>%mutate(SOI_date =as.Date(SOI_date,format ="%m/%d/%Y"),school_BOY_date =as.Date(school_BOY_date, format ="%m/%d/%Y"),school_EOY_date =as.Date(school_EOY_date, format ="%m/%d/%Y"),eoy_math_date =as.Date(eoy_math_date, format ="%m/%d/%Y"), moy_math_date =as.Date(moy_math_date, format ="%m/%d/%Y"), eoy_ela_date =as.Date(eoy_ela_date, format ="%m/%d/%Y"), moy_ela_date =as.Date(moy_ela_date, format ="%m/%d/%Y") )
Take time difference between start and end of implementation.
merge_vt_kent_usage = merge_vt_kent_usage %>%mutate(num_weeks =as.numeric(difftime(EOI_date, SOI_date, units ="weeks")))#round to the nearest whole weekmerge_vt_kent_usage$num_weeks =round(merge_vt_kent_usage$num_weeks)#subtract 2 weeks for spring break merge_vt_kent_usage$num_weeks = merge_vt_kent_usage$num_weeks -2#inspect avgmean(merge_vt_kent_usage$num_weeks, na.rm =TRUE)
[1] 8
Step 2. Create num_sessions_week for number of active sessions per week per student.
# A tibble: 7 × 4
school p25 p50 p75
<chr> <dbl> <dbl> <dbl>
1 Canyon Ridge Middle NA NA NA
2 Cedar Heights Middle NA NA NA
3 Mattson Middle NA NA NA
4 Meeker Middle NA NA NA
5 Meridian Middle 17 21 24
6 Mill Creek Middle NA NA NA
7 Northwood Middle NA NA NA
# A tibble: 7 × 4
school p25 p50 p75
<chr> <dbl> <dbl> <dbl>
1 Canyon Ridge Middle NA NA NA
2 Cedar Heights Middle NA NA NA
3 Mattson Middle NA NA NA
4 Meeker Middle NA NA NA
5 Meridian Middle 21.8 30 36
6 Mill Creek Middle NA NA NA
7 Northwood Middle NA NA NA
# A tibble: 7 × 4
school p25 p50 p75
<chr> <dbl> <dbl> <dbl>
1 Canyon Ridge Middle NA NA NA
2 Cedar Heights Middle NA NA NA
3 Mattson Middle NA NA NA
4 Meeker Middle NA NA NA
5 Meridian Middle 2.72 3.75 4.5
6 Mill Creek Middle NA NA NA
7 Northwood Middle NA NA NA
Step 3. Filter out students who do not meet usage threshold (N = 5634 to 5633). They are not part of the final sample group and should not be used as comparison students. Among students where vt_present = 1, exclude those where num_sessions_week is less than once per week.
merge_vt_kent_usage_final = merge_vt_kent_usage %>%# keep those who don't appear in usage OR students that meet usage threshold.filter(vt_present !=1| num_sessions_week >=1) print(merge_vt_kent_usage_final)
# A tibble: 5,633 × 61
student_id grade school treat moy_math_comp moy_math_date eoy_math_comp
<dbl> <dbl> <chr> <lgl> <dbl> <date> <dbl>
1 391033 7 Meridian Mi… NA 484 2026-01-06 490
2 412115 8 Meridian Mi… NA 384 2026-01-07 409
3 425285 6 Meridian Mi… NA 468 2026-01-06 NA
4 373185 7 Meridian Mi… NA 533 2026-01-07 549
5 394412 6 Meridian Mi… NA 571 2026-01-07 568
6 389860 7 Meridian Mi… NA 530 2026-01-07 549
7 389442 7 Meridian Mi… NA 467 2026-01-06 454
8 384402 7 Meridian Mi… NA 506 2026-01-07 496
9 382167 7 Meridian Mi… NA 481 2026-01-07 477
10 417702 6 Meridian Mi… NA 505 2026-01-06 499
# ℹ 5,623 more rows
# ℹ 54 more variables: eoy_math_date <date>, moy_ela_comp <dbl>,
# moy_ela_date <date>, eoy_ela_comp <dbl>, eoy_ela_date <date>, gender <chr>,
# sped <dbl>, mll <dbl>, low_inc <dbl>, race_eth <chr>, low_ap <dbl>,
# mll_lvl <chr>, school_num <dbl>, moy_math_z <dbl>, eoy_math_z <dbl>,
# moy_ela_z <dbl>, eoy_ela_z <dbl>, school_vt <chr>, school_BOY_date <date>,
# school_EOY_date <date>, vt_acct_id <dbl>, num_days_active <dbl>, …
Step 4. Create treat: Equals 1 if students had more than 2 sessions. All else equals 0.
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.
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.
# A tibble: 7 × 26
school num_sessions_AI_Q1 num_sessions_AI_median num_sessions_AI_mean
<chr> <dbl> <dbl> <dbl>
1 Canyon Ridge M… NA NA NaN
2 Cedar Heights … NA NA NaN
3 Mattson Middle NA NA NaN
4 Meeker Middle NA NA NaN
5 Meridian Middle 19 23 25.0
6 Mill Creek Mid… NA NA NaN
7 Northwood Midd… NA NA NaN
# ℹ 22 more variables: num_sessions_AI_Q3 <dbl>, num_sessions_AI_sd <dbl>,
# num_sessions_human_Q1 <dbl>, num_sessions_human_median <dbl>,
# num_sessions_human_mean <dbl>, num_sessions_human_Q3 <dbl>,
# num_sessions_human_sd <dbl>, num_classesviewed_Q1 <dbl>,
# num_classesviewed_median <dbl>, num_classesviewed_mean <dbl>,
# num_classesviewed_Q3 <dbl>, num_classesviewed_sd <dbl>,
# num_essaysreviewed_Q1 <dbl>, num_essaysreviewed_median <dbl>, …
Save e2i file with all matched students from the merge, regardless of missing test info.
#select columns ready for e2ivt_kent_e2i_usage_test_info = merge_vt_kent_usage_final %>% dplyr::select(student_id, school_num, school, grade, school_grade, grade6, grade7, grade8, male, sped, mll, low_inc,low_ap, race_recoded, race_A, race_B, race_H, race_O, race_W, moy_math_comp, moy_math_z, moy_ela_comp, moy_ela_z, eoy_math_comp, eoy_math_z, eoy_ela_comp, eoy_ela_z, treat, vt_acct_id, vt_present, num_days_active, num_days_active_recoded, num_weeks, num_sessions_week,total_sessions, num_sessions_AI, num_sessions_human, num_classesviewed, num_essaysreviewed, num_AI_practiceproblemsessions, both_math, both_ela, all_tests, math_moy_missing_eoy_present, ela_moy_missing_eoy_present )#export e2i with test indicators, all matched students from the merge, regardless of missing test info (all_tests == 1 or 0)write.csv(vt_kent_e2i_usage_test_info, file ="vt_kent_e2i_all_students.csv", row.names =FALSE)
Save e2i file with only containing matched students from the merge with complete test info for Math.
table(vt_kent_e2i_usage_test_info$both_math)
0 1
846 4787
#subset: keep only students with complete test info vt_kent_e2i_both_math = vt_kent_e2i_usage_test_info %>%filter(both_math ==1)print(vt_kent_e2i_both_math)
Save e2i file with only containing matched students from the merge with complete test info for ELA.
table(vt_kent_e2i_usage_test_info$both_ela)
0 1
760 4873
#subset: keep only students with complete test infovt_kent_e2i_both_ela = vt_kent_e2i_usage_test_info %>%filter(both_ela ==1)print(vt_kent_e2i_both_ela)
Using the data frame where comparison students with unexpected usage have been removed, but treatment students of any usage level are included (merge_vt_kent_usage), recreate treatment variable.
American Indian or Alaska Native
21
Asian
1365
Black or African American
760
Hispanic/Latino
1312
Native Hawaiian or Other Pacific Islander
189
Two or More Races
512
White
1475
Save a file for conducting analyses in R. Includes students offered Live+AI but who do not meet the usage threshold, i.e., no treat var in this export.Contains treatment student who do not meet the usage threshold, i.e., no treat var in this export.
#select columns ready for e2ivt_kent_all_students = merge_vt_kent_usage %>% dplyr::select(student_id, school_num, school, grade, school_grade, grade6, grade7, grade8, male, sped, mll, low_inc,low_ap, race, #original race for pooled coding later race_recoded, race_A, race_B, race_H, race_O, race_W, moy_math_comp, moy_math_z, moy_ela_comp, moy_ela_z, eoy_math_comp, eoy_math_z, eoy_ela_comp, eoy_ela_z, vt_acct_id, vt_present, num_days_active, num_days_active_recoded, num_weeks, num_sessions_week,total_sessions, num_sessions_AI, num_sessions_human, num_classesviewed, num_essaysreviewed, num_AI_practiceproblemsessions, both_math, both_ela, all_tests, math_moy_missing_eoy_present, ela_moy_missing_eoy_present )#export e2i with test indicators, all matched students from the merge, regardless of missing test info (all_tests == 1 or 0)write.csv(vt_kent_all_students, file ="vt_kent_all_students.csv", row.names =FALSE)
