Clean Data
Import
Packages selected for inspecting and cleaning Varsity Tutors, district data from William Penn School District.
── 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.
<- read_csv ("roster_vt_penn.csv" )
New names:
Rows: 1279 Columns: 41
── Column specification
──────────────────────────────────────────────────────── Delimiter: "," chr
(10): School Name*, MOY MAP Math Math Test Date*, EOY MAP Math Math Test... dbl
(7): Student ID*, Student Grade Level*, Treatment*, MOY MAP Math Compos... lgl
(24): ...18, ...19, ...20, ...21, ...22, ...23, ...24, ...25, ...26, ......
ℹ 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.
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`
• `` -> `...24`
• `` -> `...25`
• `` -> `...26`
• `` -> `...27`
• `` -> `...28`
• `` -> `...29`
• `` -> `...30`
• `` -> `...31`
• `` -> `...32`
• `` -> `...33`
• `` -> `...34`
• `` -> `...35`
• `` -> `...36`
• `` -> `...37`
• `` -> `...38`
• `` -> `...39`
• `` -> `...40`
• `` -> `...41`
Inspect data variables. Data file appears to contain all requested elements - except for Academically at-risk.
[1] "Student ID*"
[2] "Student Grade Level*"
[3] "School Name*"
[4] "Treatment*"
[5] "MOY MAP Math Composite Scaled Score*"
[6] "MOY MAP Math Math Test Date*"
[7] "EOY MAP Math Composite Scaled Score*"
[8] "EOY MAP Math Math Test Date*"
[9] "MOY MAP ELA Composite Scaled Score*"
[10] "MOY MAP ELA Test Date*"
[11] "EOY MAP ELA Composite Scaled Score*"
[12] "EOY MAP ELA Test Date*"
[13] "Gender"
[14] "IEP"
[15] "ELL"
[16] "FRPL"
[17] "Race/Ethnicity"
[18] "...18"
[19] "...19"
[20] "...20"
[21] "...21"
[22] "...22"
[23] "...23"
[24] "...24"
[25] "...25"
[26] "...26"
[27] "...27"
[28] "...28"
[29] "...29"
[30] "...30"
[31] "...31"
[32] "...32"
[33] "...33"
[34] "...34"
[35] "...35"
[36] "...36"
[37] "...37"
[38] "...38"
[39] "...39"
[40] "...40"
[41] "...41"
Change column headers for roster.
= roster [, - c (18 : 41 )] #drop empty columns = roster %>% rename (student_id = "Student ID*" ,grade = "Student Grade Level*" , school = "School Name*" , treat = "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*" , 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 = "Gender" , ell = "ELL" , frpl = "FRPL" , race_eth = "Race/Ethnicity" , iep = "IEP" colnames (roster)
[1] "student_id" "grade" "school" "treat"
[5] "moy_math_comp" "moy_math_date" "eoy_math_comp" "eoy_math_date"
[9] "moy_ela_comp" "moy_ela_date" "eoy_ela_comp" "eoy_ela_date"
[13] "gender" "iep" "ell" "frpl"
[17] "race_eth"
Inspect duplicates
Identify duplicate student records within roster file. No duplicates to report.
# frequency table of student_id occurrences = roster %>% group_by (student_id) %>% summarise (n = n ()) %>% arrange (desc (n))# filter only IDs that appear > 1 = id_counts_roster %>% filter (n > 1 )# view results print (dupes_roster)
# A tibble: 0 × 2
# ℹ 2 variables: student_id <dbl>, n <int>
#export - NA: no duplicates to report #write.csv(dupes_roster, file = "duplicate_ids_roster_vt_penn.csv", row.names = FALSE)
6/29/2026 Notes: Archive code. // Decision: keep first occurrence of student ID based on current row order.
= roster#roster_unique = roster %>% #distinct(student_id, .keep_all = TRUE) #print(roster_unique)
School number code
Inspect schools.
table (roster_unique$ school)
Aldan Elementary School Ardmore Avenue Elementary School
117 311
Bell Avenue Elementary School Colwyn Elementary School
128 72
East Lansdowne Elementary School Park Lane Elementary School
155 170
W.B. Evans Elementary School Walnut Street Elementary School
179 147
Create school_num for each school.
= roster_unique %>% mutate (school_num = recode (school,"Aldan Elementary School" = 1 ,"Ardmore Avenue Elementary School" = 2 ,"Bell Avenue Elementary School" = 3 , "Colwyn Elementary School" = 4 , "East Lansdowne Elementary School" = 5 , "Park Lane Elementary School" = 6 , "W.B. Evans Elementary School" = 7 ,"Walnut Street Elementary School" = 8 , table (roster$ school_num)
Warning: Unknown or uninitialised column: `school_num`.
Standardize test scores
Using the MAP Growth Norms Technical Manual (2025) to standardize test scores.
table (roster_unique$ grade)
MATH MOY
# Winter NWEA Math norms for Grades 3-6 = tibble (grade = c (3 , 4 , 5 , 6 ),moy_mean_math = c (192.65 , 204.48 , 211.82 , 216.00 ),moy_sd_math = c (16.34 , 17.02 , 17.42 , 17.35 )# Merge norms and calculate standardized scores = 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
-3.7200 -0.7300 -0.1700 -0.2246 0.3500 2.3800 65
MATH EOY
# Spring NWEA Math norms for Grades 3-6 = tibble (grade = c (3 , 4 , 5 , 6 ),eoy_mean_math = c (199.10 , 210.07 , 216.01 , 220.33 ),eoy_sd_math = c (17.03 , 18.04 , 18.44 , 18.47 )# Merge norms and calculate standardized scores = roster_unique %>% left_join (eoy_math_spring_norms, by = "grade" ) %>% mutate (eoy_math_z = round ((eoy_math_comp - eoy_mean_math) / eoy_sd_math, 2 )%>% select (- eoy_mean_math, - eoy_sd_math)summary (roster_unique$ eoy_math_z)
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
-3.7700 -0.7600 -0.1200 -0.1503 0.5400 2.5200 40
ELA MOY
# Winter NWEA Reading norms for Grades 3-6 = tibble (grade = c (3 , 4 , 5 , 6 ),moy_mean_ela = c (189.89 , 199.45 , 206.36 , 210.72 ),moy_sd_ela = c (18.13 , 17.76 , 17.21 , 16.70 )# Merge norms and calculate standardized scores = roster_unique %>% left_join (moy_ela_norms, by = "grade" ) %>% mutate (moy_ela_z = round ((moy_ela_comp - moy_mean_ela) / moy_sd_ela, 2 )%>% select (- moy_mean_ela, - moy_sd_ela)summary (roster_unique$ moy_ela_z)
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
-3.1000 -0.9300 -0.2050 -0.3113 0.3900 1.9400 69
ELA EOY
# Spring NWEA Reading norms for Grades 3-6 = tibble (grade = c (3 , 4 , 5 , 6 ),eoy_mean_ela = c (193.79 , 202.09 , 208.37 , 212.04 ),eoy_sd_ela = c (18.15 , 17.74 , 17.15 , 16.67 )# Merge norms and calculate standardized scores = roster_unique %>% left_join (eoy_ela_spring_norms, by = "grade" ) %>% mutate (eoy_ela_z = round ((eoy_ela_comp - eoy_mean_ela) / eoy_sd_ela, 2 )%>% select (- eoy_mean_ela, - eoy_sd_ela)summary (roster_unique$ eoy_ela_z)
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
-3.2900 -1.0200 -0.3000 -0.3848 0.3300 2.3700 46
Merge Usage Data to District Data
Clean Penn Usage Data
Import roster from VT that details which students have accounts in their system.
#import ID crosswalk from VT = 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 %>% rename (district_vt = "District" ,school_vt = "School" ,student_id = "District ID" , vt_acct_id = "Varsity Tutors ID"
Import usage data from VT.
#import penn usage data from VT = read_csv ("vt_usage_penn_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_penn)
[1] "Sch_name*" "Sch_BOY_date*"
[3] "Sch_EOY_date" "Stu_ID_VT*"
[5] "Num_days_active*" "Num_sessions_AI"
[7] "Num_sessions_human" "Num_classesviewed"
[9] "Num_essaysreviewed" "Num_AI_practiceproblemsessions"
[11] "Total"
Rename columns in usage file. Prepare for merging.
# rename vars = vt_usage_penn %>% rename (school_vt = "Sch_name*" ,school_BOY_date = "Sch_BOY_date*" ,school_EOY_date = "Sch_EOY_date" , vt_acct_id = "Stu_ID_VT*" , num_days_active = "Num_days_active*" ,num_sessions_AI = "Num_sessions_AI" ,num_sessions_human = "Num_sessions_human" ,num_classesviewed = "Num_classesviewed" ,num_essaysreviewed = "Num_essaysreviewed" ,num_AI_practiceproblemsessions = "Num_AI_practiceproblemsessions" ,total_sessions = "Total" print (vt_usage_penn)
# A tibble: 998 × 11
school_vt school_BOY_date school_EOY_date vt_acct_id num_days_active
<chr> <chr> <chr> <dbl> <dbl>
1 Colwyn Elementary… 1/5/2026 5/24/2026 13143440 20
2 Colwyn Elementary… 1/5/2026 5/24/2026 13143446 17
3 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144304 17
4 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144297 17
5 Bell Avenue Eleme… 1/5/2026 5/24/2026 13193600 16
6 Bell Avenue Eleme… 1/5/2026 5/24/2026 13155895 16
7 Bell Avenue Eleme… 1/5/2026 5/24/2026 13155898 16
8 Colwyn Elementary… 1/5/2026 5/24/2026 13144034 16
9 Colwyn Elementary… 1/5/2026 5/24/2026 13143438 15
10 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144302 15
# ℹ 988 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>
Identify duplicate student records within usage data file (N=0).
# frequency table of vt_acct_id occurrences = vt_usage_penn %>% group_by (vt_acct_id) %>% summarise (n = n ()) %>% arrange (desc (n))# filter only acct that appear > 1 = id_counts_vt_usage_penn %>% filter (n > 1 )# view results print (dupes_vt_usage_penn)
# A tibble: 1 × 2
vt_acct_id n
<dbl> <int>
1 NA 643
#export #write.csv(dupes_vt_usage_penn, file = "duplicate_acct_ids_usage_vt_penn.csv", row.names = FALSE)
Inspect rows where Account ID is missing. Observation: empty rows from csv conversion.
%>% filter (is.na (vt_acct_id))
# A tibble: 643 × 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
# ℹ 633 more rows
# ℹ 6 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
# num_classesviewed <dbl>, num_essaysreviewed <dbl>,
# num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>
Remove empty rows.
= vt_usage_penn %>% filter (! is.na (vt_acct_id))print (vt_usage_penn)
# A tibble: 355 × 11
school_vt school_BOY_date school_EOY_date vt_acct_id num_days_active
<chr> <chr> <chr> <dbl> <dbl>
1 Colwyn Elementary… 1/5/2026 5/24/2026 13143440 20
2 Colwyn Elementary… 1/5/2026 5/24/2026 13143446 17
3 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144304 17
4 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144297 17
5 Bell Avenue Eleme… 1/5/2026 5/24/2026 13193600 16
6 Bell Avenue Eleme… 1/5/2026 5/24/2026 13155895 16
7 Bell Avenue Eleme… 1/5/2026 5/24/2026 13155898 16
8 Colwyn Elementary… 1/5/2026 5/24/2026 13144034 16
9 Colwyn Elementary… 1/5/2026 5/24/2026 13143438 15
10 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144302 15
# ℹ 345 more rows
# ℹ 6 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
# num_classesviewed <dbl>, num_essaysreviewed <dbl>,
# num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>
No duplicates // Archive: Inspect duplicate student records in usage file (based on VT Account ID - not student ID). Observation : for a given duplicate student record, school name,num_days_active, BOY date and EOY date will match but usage data differs across the duplicate records.
#dup_preview_usage_penn = vt_usage_penn %>% # group_by(vt_acct_id) %>% # filter(n() > 1) %>% #ungroup() #print(dup_preview_usage_penn)
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.
Basic check of randomly selected student, 13130591. Inspect sum of Num_sessions_AI before collapsing.
#sum(vt_usage_penn$num_sessions_AI[vt_usage_penn$vt_acct_id == 13130591], na.rm = TRUE)
No duplicates // Archive: Collapse rows.
#vt_usage_penn_collapsed = vt_usage_penn %>% # 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 (take 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_penn_collapsed)
No duplicates // Archive: Check number of IDs that appear more than once.
#sum(table(vt_usage_penn_collapsed$vt_acct_id) > 1)
No duplicates // Archive: Basic check of randomly selected student, 13130591. Inspect sum of Num_sessions_AI after collapsing.
#sum(vt_usage_penn_collapsed$num_sessions_AI[vt_usage_penn_collapsed$vt_acct_id == 13130591], na.rm = TRUE)
Append student ID from crosswalk file to usage file.
= vt_usage_penn %>% left_join (vt_roster %>% select (vt_acct_id, student_id),by = "vt_acct_id" )print (vt_usage_penn)
# A tibble: 355 × 12
school_vt school_BOY_date school_EOY_date vt_acct_id num_days_active
<chr> <chr> <chr> <dbl> <dbl>
1 Colwyn Elementary… 1/5/2026 5/24/2026 13143440 20
2 Colwyn Elementary… 1/5/2026 5/24/2026 13143446 17
3 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144304 17
4 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144297 17
5 Bell Avenue Eleme… 1/5/2026 5/24/2026 13193600 16
6 Bell Avenue Eleme… 1/5/2026 5/24/2026 13155895 16
7 Bell Avenue Eleme… 1/5/2026 5/24/2026 13155898 16
8 Colwyn Elementary… 1/5/2026 5/24/2026 13144034 16
9 Colwyn Elementary… 1/5/2026 5/24/2026 13143438 15
10 Bell Avenue Eleme… 1/5/2026 5/24/2026 13144302 15
# ℹ 345 more rows
# ℹ 7 more variables: num_sessions_AI <dbl>, num_sessions_human <dbl>,
# num_classesviewed <dbl>, num_essaysreviewed <dbl>,
# num_AI_practiceproblemsessions <dbl>, total_sessions <dbl>,
# student_id <dbl>
##Merge usage data to Penn student data
= roster_unique %>% left_join (vt_usage_penn, by = "student_id" )print (merge_vt_penn_usage)
# A tibble: 1,279 × 33
student_id grade school treat moy_math_comp moy_math_date eoy_math_comp
<dbl> <dbl> <chr> <dbl> <dbl> <chr> <dbl>
1 203802 6 Colwyn Elem… 1 186 1/9/2026 190
2 203862 6 Colwyn Elem… 1 223 1/8/2026 242
3 204106 6 Colwyn Elem… 1 230 1/13/2026 226
4 204166 6 Colwyn Elem… 1 210 1/8/2026 214
5 204211 6 Colwyn Elem… 1 181 1/8/2026 195
6 204755 5 Colwyn Elem… 1 226 1/6/2026 234
7 204759 5 Colwyn Elem… 1 205 1/6/2026 211
8 204769 5 Colwyn Elem… 1 209 1/6/2026 214
9 204787 5 Colwyn Elem… 1 170 1/6/2026 173
10 204843 5 Colwyn Elem… 1 213 1/6/2026 221
# ℹ 1,269 more rows
# ℹ 26 more variables: eoy_math_date <chr>, moy_ela_comp <dbl>,
# moy_ela_date <chr>, eoy_ela_comp <dbl>, eoy_ela_date <chr>, gender <chr>,
# iep <chr>, ell <chr>, frpl <chr>, race_eth <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 <chr>, school_EOY_date <chr>,
# vt_acct_id <dbl>, num_days_active <dbl>, num_sessions_AI <dbl>, …
Check for unmatched students.
%>% filter (is.na (student_id)) # no NAs; no unmatched students.
# A tibble: 0 × 33
# ℹ 33 variables: student_id <dbl>, grade <dbl>, school <chr>, treat <dbl>,
# moy_math_comp <dbl>, moy_math_date <chr>, eoy_math_comp <dbl>,
# eoy_math_date <chr>, moy_ela_comp <dbl>, moy_ela_date <chr>,
# eoy_ela_comp <dbl>, eoy_ela_date <chr>, gender <chr>, iep <chr>, ell <chr>,
# frpl <chr>, race_eth <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 <chr>, school_EOY_date <chr>, vt_acct_id <dbl>, …
Add VT Acct indicator. Equals 1 if student has VT account ID (N=353). All else equals 0. Number of potential comparison students equals 926.
$ vt_present = ifelse (is.na (merge_vt_penn_usage$ vt_acct_id), 0 , 1 )table (merge_vt_penn_usage$ vt_present)
Among students with VT account IDs, recode num_days_active to equal zero if num_days_active is NA.
= merge_vt_penn_usage %>% mutate (num_days_active_recoded = ifelse (== 1 & is.na (num_days_active),0 ,
Remove cases with unexpected usage
Inspect students with any usage by school. Usage only occurs in treatment schools. No removals needed.
%>% mutate (any_usage = ifelse (num_days_active >= 1 , 1 , 0 )) %>% group_by (school_num) %>% summarize (total_students = n (),n_with_usage = sum (any_usage, na.rm = TRUE ),.groups = "drop" )
# A tibble: 8 × 3
school_num total_students n_with_usage
<dbl> <int> <dbl>
1 1 117 77
2 2 311 0
3 3 128 103
4 4 72 69
5 5 155 0
6 6 170 0
7 7 179 0
8 8 147 82
Create treatment status
Step 1. Construct num_weeks.
Append start date and end date following district schedule.
= merge_vt_penn_usage %>% mutate (#start of implementation, per usage file SOI_date = as.Date ("2026-01-05" ),#end of implementation Mon 5/25/2026 (last school day before EOY testing week) EOI_date = as.Date ("2026-05-25" )#transform other dates as.dates = merge_vt_penn_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_penn_usage %>% mutate (num_weeks = as.numeric (difftime (EOI_date, SOI_date, units = "weeks" )))#round to the nearest whole week $ num_weeks = round (merge_vt_penn_usage$ num_weeks)#subtract 1 week for spring break $ num_weeks = merge_vt_penn_usage$ num_weeks - 1 #inspect avg mean (merge_vt_penn_usage$ num_weeks, na.rm = TRUE )
Step 2. Create num_sessions_week for number of active sessions per week per student.
Inspect total_sessions.
summary (merge_vt_penn_usage$ total_sessions, na.rm = TRUE )
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
0.00 3.00 9.00 15.63 22.00 243.00 926
sd (merge_vt_penn_usage$ total_sessions, na.rm = TRUE )
Inspect num_sessions_week .
= merge_vt_penn_usage %>% mutate (num_sessions_week = total_sessions / num_weeks)summary (merge_vt_penn_usage$ num_sessions_week, na.rm = TRUE )
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
0.0000 0.1579 0.4737 0.8224 1.1579 12.7895 926
sd (merge_vt_penn_usage$ num_sessions_week, na.rm = TRUE )
Inspect usage based on number of active session per week.
Number of students where num_sessions_week equal to or greater than 3.
sum (merge_vt_penn_usage$ num_sessions_week >= 3 , na.rm = TRUE )
Number of students where num_sessions_week equal to or greater than 2.
sum (merge_vt_penn_usage$ num_sessions_week >= 2 , na.rm = TRUE )
Number of students where num_sessions_week equal to or greater than 1.
sum (merge_vt_penn_usage$ num_sessions_week >= 1 , na.rm = TRUE )
Preliminary inspections
Number of active days
Statistical summary for num_days_active (recoded so that 0 days of usage among the students with VT Account IDs are represented).
summary (merge_vt_penn_usage$ num_days_active_recoded, na.rm = TRUE )
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
0.000 2.000 4.000 4.674 7.000 20.000 926
sd (merge_vt_penn_usage$ num_days_active_recoded, na.rm = TRUE )
Number of active days (overall), by school.
= merge_vt_penn_usage %>% group_by (school) %>% summarise (p25 = round (quantile (num_days_active, 0.25 , na.rm = TRUE ), 2 ),p50 = round (quantile (num_days_active, 0.50 , na.rm = TRUE ), 2 ),p75 = round (quantile (num_days_active, 0.75 , na.rm = TRUE ), 2 ),.groups = "drop" print (overall_school_percentiles)
# A tibble: 8 × 4
school p25 p50 p75
<chr> <dbl> <dbl> <dbl>
1 Aldan Elementary School 1 3 5
2 Ardmore Avenue Elementary School NA NA NA
3 Bell Avenue Elementary School 2.5 5 9
4 Colwyn Elementary School 3 5 8
5 East Lansdowne Elementary School NA NA NA
6 Park Lane Elementary School NA NA NA
7 W.B. Evans Elementary School NA NA NA
8 Walnut Street Elementary School 1 2 4
write.csv (overall_school_percentiles, file = "vt_penn_num_days_active_summary_by_school.csv" , row.names = FALSE )
Frequency distribution of num_active_days (recoded so that 0 days of usage among the students with VT Account IDs are represented).
ggplot (merge_vt_penn_usage, aes (x = num_days_active_recoded)) + geom_histogram (binwidth = 1 , fill = "lightblue" , color = "black" ) + stat_bin ( binwidth = 1 , geom = "text" ,aes (label = after_stat (count)),vjust = - 0.5 ) + scale_y_continuous (expand = expansion (mult = c (0 , 0.1 ))) + theme_minimal ()
Warning: Removed 926 rows containing non-finite outside the scale range (`stat_bin()`).
Removed 926 rows containing non-finite outside the scale range (`stat_bin()`).
Total sessions
Statistical summary for total_sessions .
summary (merge_vt_penn_usage$ total_sessions, na.rm = TRUE )
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
0.00 3.00 9.00 15.63 22.00 243.00 926
sd (merge_vt_penn_usage$ total_sessions, na.rm = TRUE )
Number of total_sessions, by school.
= merge_vt_penn_usage %>% group_by (school) %>% summarise (p25 = round (quantile (total_sessions, 0.25 , na.rm = TRUE ), 2 ),p50 = round (quantile (total_sessions, 0.50 , na.rm = TRUE ), 2 ),p75 = round (quantile (total_sessions, 0.75 , na.rm = TRUE ), 2 ),.groups = "drop" print (school_percentiles)
# A tibble: 8 × 4
school p25 p50 p75
<chr> <dbl> <dbl> <dbl>
1 Aldan Elementary School 3 9 16
2 Ardmore Avenue Elementary School NA NA NA
3 Bell Avenue Elementary School 4 11 24.5
4 Colwyn Elementary School 8 12 29.5
5 East Lansdowne Elementary School NA NA NA
6 Park Lane Elementary School NA NA NA
7 W.B. Evans Elementary School NA NA NA
8 Walnut Street Elementary School 2 4 10.8
write.csv (school_percentiles, file = "vt_penn_total_sessions_summary_by_school.csv" , row.names = FALSE )
Frequency distribution of total_sessions. Note: Bin width is 5!
ggplot (merge_vt_penn_usage, aes (x = total_sessions)) + geom_histogram (binwidth = 5 , fill = "lightblue" , color = "black" ) + stat_bin ( binwidth = 5 , geom = "text" ,aes (label = after_stat (count)),vjust = - 0.5 ) + scale_y_continuous (expand = expansion (mult = c (0 , 0.1 ))) + theme_minimal ()
Warning: Removed 926 rows containing non-finite outside the scale range (`stat_bin()`).
Removed 926 rows containing non-finite outside the scale range (`stat_bin()`).
Number of active sessions per week
Statistical summary for num_sessions_week .
summary (merge_vt_penn_usage$ num_sessions_week, na.rm = TRUE )
Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
0.0000 0.1579 0.4737 0.8224 1.1579 12.7895 926
sd (merge_vt_penn_usage$ num_sessions_week, na.rm = TRUE )
Number of active days per week, by school.
= merge_vt_penn_usage %>% group_by (school) %>% summarise (p25 = round (quantile (num_sessions_week, 0.25 , na.rm = TRUE ), 2 ),p50 = round (quantile (num_sessions_week, 0.50 , na.rm = TRUE ), 2 ),p75 = round (quantile (num_sessions_week, 0.75 , na.rm = TRUE ), 2 ),.groups = "drop" print (school_percentiles)
# A tibble: 8 × 4
school p25 p50 p75
<chr> <dbl> <dbl> <dbl>
1 Aldan Elementary School 0.16 0.47 0.84
2 Ardmore Avenue Elementary School NA NA NA
3 Bell Avenue Elementary School 0.21 0.58 1.29
4 Colwyn Elementary School 0.42 0.63 1.55
5 East Lansdowne Elementary School NA NA NA
6 Park Lane Elementary School NA NA NA
7 W.B. Evans Elementary School NA NA NA
8 Walnut Street Elementary School 0.11 0.21 0.57
write.csv (school_percentiles, file = "vt_penn_num_sessions_week_summary_by_school.csv" , row.names = FALSE )
Step 3. Filter out students who do not meet usage threshold (N = 1279 to 1025). 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_penn_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_penn_usage_final)
# A tibble: 1,025 × 60
student_id grade school treat moy_math_comp moy_math_date eoy_math_comp
<dbl> <dbl> <chr> <dbl> <dbl> <date> <dbl>
1 203802 6 Colwyn Elem… 1 186 2026-01-09 190
2 203862 6 Colwyn Elem… 1 223 2026-01-08 242
3 204106 6 Colwyn Elem… 1 230 2026-01-13 226
4 204769 5 Colwyn Elem… 1 209 2026-01-06 214
5 204787 5 Colwyn Elem… 1 170 2026-01-06 173
6 204896 5 Colwyn Elem… 1 230 2026-01-06 227
7 204671 5 Colwyn Elem… 1 209 2026-01-06 225
8 205051 5 Colwyn Elem… 1 194 2026-01-06 196
9 205898 6 Colwyn Elem… 1 217 2026-01-08 230
10 206039 6 Colwyn Elem… 1 228 2026-01-08 249
# ℹ 1,015 more rows
# ℹ 53 more variables: eoy_math_date <date>, moy_ela_comp <dbl>,
# moy_ela_date <date>, eoy_ela_comp <dbl>, eoy_ela_date <date>, gender <chr>,
# iep <dbl>, ell <dbl>, frpl <dbl>, race_eth <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>, num_sessions_AI <dbl>, …
Step 4. Create treat: Equals 1 if students had more than 2 sessions. All else equals 0.
= merge_vt_penn_usage_final %>% mutate (treat = ifelse (! is.na (num_sessions_week) & num_sessions_week >= 1 , 1 , 0 ))table (merge_vt_penn_usage_final$ treat)
Inspect Merged Data
Missing Data Summary
Percentage missing for each variable. Observations: All other demographic info is present for all students. Some missing data for test info.
= merge_vt_penn_usage_final %>% summarise (across (everything (),~ mean (is.na (.)) * 100 %>% pivot_longer (cols = everything (),names_to = "variable" ,values_to = "percent_missing" %>% arrange (desc (percent_missing))print (missing_summary)
# A tibble: 60 × 2
variable percent_missing
<chr> <dbl>
1 school_vt 90.3
2 school_BOY_date 90.3
3 school_EOY_date 90.3
4 vt_acct_id 90.3
5 num_days_active 90.3
6 num_sessions_AI 90.3
7 num_sessions_human 90.3
8 num_classesviewed 90.3
9 num_essaysreviewed 90.3
10 num_AI_practiceproblemsessions 90.3
# ℹ 50 more rows
Percentage missing for each variable, grouped by treatment. Exported.
= merge_vt_penn_usage_final %>% group_by (treat) %>% summarise (across (everything (),~ mean (is.na (.)) * 100 , .names = "{.col}" %>% pivot_longer (cols = - treat,names_to = "variable" ,values_to = "percent_missing" %>% arrange (desc (percent_missing))print (missing_summary_by_treat)
# A tibble: 118 × 3
treat variable percent_missing
<dbl> <chr> <dbl>
1 0 school_vt 100
2 0 school_BOY_date 100
3 0 school_EOY_date 100
4 0 vt_acct_id 100
5 0 num_days_active 100
6 0 num_sessions_AI 100
7 0 num_sessions_human 100
8 0 num_classesviewed 100
9 0 num_essaysreviewed 100
10 0 num_AI_practiceproblemsessions 100
# ℹ 108 more rows
#export write.csv (missing_summary_by_treat, file = "vt_penn_pct_missing_vars_by_treat.csv" )
Percentage of student who have both MOY and EOY data overall
#pct across schools = merge_vt_penn_usage_final %>% summarise (total_students = n (),students_with_all_tests = sum (all_tests),percent_with_all_tests = (students_with_all_tests / total_students) * 100 %>% mutate (percent_with_all_tests = round (percent_with_all_tests, 2 ))print (overall_pct)
# A tibble: 1 × 3
total_students students_with_all_tests percent_with_all_tests
<int> <dbl> <dbl>
1 1025 947 92.4
Percentage of student who have both MOY and EOY data by school
= merge_vt_penn_usage_final %>% group_by (school) %>% summarise (total_students = n (),students_with_all_tests = sum (all_tests),percent_with_all_tests = (students_with_all_tests / total_students) * 100 %>% mutate (percent_with_all_tests = round (percent_with_all_tests, 2 )) %>% arrange (desc (percent_with_all_tests))print (by_school_pct)
# A tibble: 8 × 4
school total_students students_with_all_te…¹ percent_with_all_tests
<chr> <int> <dbl> <dbl>
1 Colwyn Elementar… 29 29 100
2 East Lansdowne E… 155 148 95.5
3 Park Lane Elemen… 170 161 94.7
4 W.B. Evans Eleme… 179 166 92.7
5 Bell Avenue Elem… 55 51 92.7
6 Ardmore Avenue E… 311 284 91.3
7 Walnut Street El… 73 65 89.0
8 Aldan Elementary… 53 43 81.1
# ℹ abbreviated name: ¹students_with_all_tests
Percentage of student who have both MOY and EOY MATH data by treatment group, per school. Exported.
= merge_vt_penn_usage_final %>% group_by (school, treat) %>% summarise (n = n (),n_both_math = sum (both_math == TRUE , na.rm = TRUE ),pct_both_math = (n_both_math / n) * 100 ,.groups = "drop" print (both_math_by_school_treat)
# A tibble: 12 × 5
school treat n n_both_math pct_both_math
<chr> <dbl> <int> <int> <dbl>
1 Aldan Elementary School 0 32 22 68.8
2 Aldan Elementary School 1 21 21 100
3 Ardmore Avenue Elementary School 0 311 289 92.9
4 Bell Avenue Elementary School 0 13 9 69.2
5 Bell Avenue Elementary School 1 42 42 100
6 Colwyn Elementary School 0 1 1 100
7 Colwyn Elementary School 1 28 28 100
8 East Lansdowne Elementary School 0 155 148 95.5
9 Park Lane Elementary School 0 170 163 95.9
10 W.B. Evans Elementary School 0 179 166 92.7
11 Walnut Street Elementary School 0 65 57 87.7
12 Walnut Street Elementary School 1 8 8 100
#export write.csv (both_math_by_school_treat, file = "vt_penn_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.
= merge_vt_penn_usage_final %>% group_by (school, treat) %>% summarise (n = n (),n_both_ela = sum (both_ela == TRUE , na.rm = TRUE ),pct_both_ela = (n_both_ela / n) * 100 ,.groups = "drop" print (both_ela_by_school_treat)
# A tibble: 12 × 5
school treat n n_both_ela pct_both_ela
<chr> <dbl> <int> <int> <dbl>
1 Aldan Elementary School 0 32 22 68.8
2 Aldan Elementary School 1 21 21 100
3 Ardmore Avenue Elementary School 0 311 284 91.3
4 Bell Avenue Elementary School 0 13 10 76.9
5 Bell Avenue Elementary School 1 42 42 100
6 Colwyn Elementary School 0 1 1 100
7 Colwyn Elementary School 1 28 28 100
8 East Lansdowne Elementary School 0 155 148 95.5
9 Park Lane Elementary School 0 170 161 94.7
10 W.B. Evans Elementary School 0 179 166 92.7
11 Walnut Street Elementary School 0 65 57 87.7
12 Walnut Street Elementary School 1 8 8 100
#export write.csv (both_ela_by_school_treat, file = "vt_penn_pct_both_ela_by_school_treat.csv" , row.names = FALSE )
Test info summary
Proportion of Math test info present (MOY present and EOY present, grouped by treatment status).
= merge_vt_penn_usage_final %>% mutate (moy_math_present = ! is.na (moy_math_comp),eoy_math_present = ! is.na (eoy_math_comp)%>% group_by (treat) %>% summarise (n_students = n (),moy_count = sum (moy_math_present),moy_prop = mean (moy_math_present),eoy_count = sum (eoy_math_present),eoy_prop = mean (eoy_math_present),.groups = "drop" # View result print (summary_math_tests_present)
# A tibble: 2 × 6
treat n_students moy_count moy_prop eoy_count eoy_prop
<dbl> <int> <int> <dbl> <int> <dbl>
1 0 926 865 0.934 887 0.958
2 1 99 99 1 99 1
Proportion of ELA test info present (MOY present and EOY present, grouped by treatment status).
= merge_vt_penn_usage_final %>% mutate (moy_ela_present = ! is.na (moy_ela_comp),eoy_ela_present = ! is.na (eoy_ela_comp)%>% group_by (treat) %>% summarise (n_students = n (),moy_count = sum (moy_ela_present),moy_prop = mean (moy_ela_present),eoy_count = sum (eoy_ela_present),eoy_prop = mean (eoy_ela_present),.groups = "drop" # View result print (summary_ela_tests_present)
# A tibble: 2 × 6
treat n_students moy_count moy_prop eoy_count eoy_prop
<dbl> <int> <int> <dbl> <int> <dbl>
1 0 926 862 0.931 881 0.951
2 1 99 99 1 99 1
District Data Summary
Generate school-level means for MOY ELA and Math assessment data, number of students, plus distribution across demographics.
#create function to streamline calculations = function (x) {prop.table (table (x)) * 100 #school-level means and counts for TEST data = merge_vt_penn_usage_final %>% mutate (across (c (moy_math_comp, #transform all scores to numeric value = merge_vt_penn_usage_final %>% group_by (school)%>% summarise (n_students = n (),mean_moy_math = mean (moy_math_comp, na.rm = TRUE ),mean_moy_ela = mean (moy_ela_comp, na.rm = TRUE ), mean_eoy_math = mean (eoy_math_comp, na.rm = TRUE ),mean_eoy_ela = mean (eoy_ela_comp, na.rm = TRUE ), # DEMOGRAPHICS # race/ethnicity = merge_vt_penn_usage_final %>% group_by (school, race_eth) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = race_eth,values_from = pct,names_prefix = "race_" #gender = merge_vt_penn_usage_final %>% group_by (school, gender) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = gender,values_from = pct,names_prefix = "gender_" #frpl = merge_vt_penn_usage_final %>% group_by (school, frpl) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = frpl,values_from = pct,names_prefix = "frpl_" #ell = merge_vt_penn_usage_final %>% group_by (school, ell) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = ell,values_from = pct,names_prefix = "ell_" #iep = merge_vt_penn_usage_final %>% group_by (school, iep) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = iep,values_from = pct,names_prefix = "iep_" # COMBINE summaries = school_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 (iep_pct, by = "school" )= school_summary %>% mutate (across (where (is.numeric), ~ round (.x, 2 )))print (school_summary)
# A tibble: 8 × 21
school n_students mean_moy_math mean_moy_ela mean_eoy_math mean_eoy_ela
<chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Aldan Elemen… 53 201. 199. 202. 199.
2 Ardmore Aven… 311 202. 194. 208. 196.
3 Bell Avenue … 55 201. 191. 206. 192.
4 Colwyn Eleme… 29 201. 197. 207. 200.
5 East Lansdow… 155 206. 202. 214. 205.
6 Park Lane El… 170 202. 195. 207. 193.
7 W.B. Evans E… 179 205. 199. 213. 201.
8 Walnut Stree… 73 195. 188. 201. 190.
# ℹ 15 more variables: `race_AMERICAN INDIAN/ALASKAN` <dbl>, race_ASIAN <dbl>,
# `race_BLACK/AFRICAN AMERICAN` <dbl>, `race_WHITE/CAUCASIAN` <dbl>,
# race_HISPANIC <dbl>, `race_NATIVE HAWAIIAN` <dbl>,
# `race_MULTI RACIAL - NON HISPANIC` <dbl>, gender_F <dbl>, gender_M <dbl>,
# frpl_0 <dbl>, frpl_1 <dbl>, ell_0 <dbl>, ell_1 <dbl>, iep_0 <dbl>,
# iep_1 <dbl>
write.csv (school_summary, "vt_penn_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 = merge_vt_penn_usage_final %>% filter (treat == 1 ) %>% group_by (school) %>% summarise (n_students = n (),mean_moy_math = mean (moy_math_comp, na.rm = TRUE ),mean_moy_ela = mean (moy_ela_comp, na.rm = TRUE )# DEMOGRAPHICS # race/ethnicity = merge_vt_penn_usage_final %>% filter (treat == 1 ) %>% group_by (school, race_eth) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = race_eth,values_from = pct,names_prefix = "race_" #gender = merge_vt_penn_usage_final %>% filter (treat == 1 ) %>% group_by (school, gender) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = gender,values_from = pct,names_prefix = "gender_" #free reduced price lunch = merge_vt_penn_usage_final %>% filter (treat == 1 ) %>% group_by (school, frpl) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = frpl,values_from = pct,names_prefix = "frpl_" #english language learner = merge_vt_penn_usage_final %>% filter (treat == 1 ) %>% group_by (school, ell) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = ell,values_from = pct,names_prefix = "ell_" #iep = merge_vt_penn_usage_final %>% filter (treat == 1 ) %>% group_by (school, iep) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = iep,values_from = pct,names_prefix = "iep_" # COMBINE summaries = school_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 (iep_pct_treat, by = "school" )= school_summary_treat %>% mutate (across (where (is.numeric), ~ round (.x, 2 )))print (school_summary_treat)
# A tibble: 4 × 15
school n_students mean_moy_math mean_moy_ela race_BLACK/AFRICAN A…¹
<chr> <dbl> <dbl> <dbl> <dbl>
1 Aldan Elementary… 21 207. 202. 76.2
2 Bell Avenue Elem… 42 204. 195. 95.2
3 Colwyn Elementar… 28 202. 198. 92.9
4 Walnut Street El… 8 191. 190. 100
# ℹ abbreviated name: ¹`race_BLACK/AFRICAN AMERICAN`
# ℹ 10 more variables: `race_WHITE/CAUCASIAN` <dbl>,
# `race_AMERICAN INDIAN/ALASKAN` <dbl>, gender_F <dbl>, gender_M <dbl>,
# frpl_0 <dbl>, frpl_1 <dbl>, ell_0 <dbl>, ell_1 <dbl>, iep_0 <dbl>,
# iep_1 <dbl>
write.csv (school_summary_treat, "vt_penn_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 = merge_vt_penn_usage_final %>% filter (treat == 0 ) %>% group_by (school) %>% summarise (n_students = n (),mean_moy_math = mean (moy_math_comp, na.rm = TRUE ),mean_moy_ela = mean (moy_ela_comp, na.rm = TRUE )# DEMOGRAPHICS # race/ethnicity = merge_vt_penn_usage_final %>% filter (treat == 0 ) %>% group_by (school, race_eth) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = race_eth,values_from = pct,names_prefix = "race_" #gender = merge_vt_penn_usage_final %>% filter (treat == 0 ) %>% group_by (school, gender) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = gender,values_from = pct,names_prefix = "gender_" #free reduced price lunch = merge_vt_penn_usage_final %>% filter (treat == 0 ) %>% group_by (school, frpl) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = frpl,values_from = pct,names_prefix = "frpl_" #english language learner = merge_vt_penn_usage_final %>% filter (treat == 0 ) %>% group_by (school, ell) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = ell,values_from = pct,names_prefix = "ell_" #iep = merge_vt_penn_usage_final %>% filter (treat == 0 ) %>% group_by (school, iep) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = iep,values_from = pct,names_prefix = "iep_" # COMBINE summaries = school_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 (iep_pct_comp, by = "school" )= school_summary_comp %>% mutate (across (where (is.numeric), ~ round (.x, 2 )))print (school_summary_comp)
# A tibble: 8 × 19
school n_students mean_moy_math mean_moy_ela race_AMERICAN INDIAN…¹ race_ASIAN
<chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Aldan… 32 196. 196. 9.38 6.25
2 Ardmo… 311 202. 194. 1.61 3.86
3 Bell … 13 188. 177. NA 7.69
4 Colwy… 1 184 159 NA NA
5 East … 155 206. 202. 0.65 2.58
6 Park … 170 202. 195. 1.18 1.76
7 W.B. … 179 205. 199. 0.56 3.35
8 Walnu… 65 196. 188. NA NA
# ℹ abbreviated name: ¹`race_AMERICAN INDIAN/ALASKAN`
# ℹ 13 more variables: `race_BLACK/AFRICAN AMERICAN` <dbl>,
# `race_WHITE/CAUCASIAN` <dbl>, race_HISPANIC <dbl>,
# `race_NATIVE HAWAIIAN` <dbl>, `race_MULTI RACIAL - NON HISPANIC` <dbl>,
# gender_F <dbl>, gender_M <dbl>, frpl_0 <dbl>, frpl_1 <dbl>, ell_0 <dbl>,
# ell_1 <dbl>, iep_0 <dbl>, iep_1 <dbl>
write.csv (school_summary_comp, "vt_penn_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.
= merge_vt_penn_usage_final %>% filter (treat == 1 ) %>% summarise (n_total = n (),# Math test missing n_missing_eoy_math = sum (is.na (eoy_math_comp)),pct_missing_eoy_math = (n_missing_eoy_math / n_total) * 100 ,# ELA test missing n_missing_eoy_ela = sum (is.na (eoy_ela_comp)),pct_missing_eoy_ela = (n_missing_eoy_ela / n_total) * 100 print (summary_attrition)
# A tibble: 1 × 5
n_total n_missing_eoy_math pct_missing_eoy_math n_missing_eoy_ela
<int> <int> <dbl> <int>
1 99 0 0 0
# ℹ 1 more variable: pct_missing_eoy_ela <dbl>
Usage Data Summary
Count of students with any usage logged (defined as total_sessions => 1) by treatment.
$ total_sessions = as.numeric (merge_vt_penn_usage_final$ total_sessions)#create new any_usage for new tabulation = merge_vt_penn_usage_final %>% mutate (any_usage = ifelse (total_sessions >= 1 , 1 , 0 )) #for treat=0 cases, any_usage will be NA #for many treat=0 cases, recode any_usage from NA to 0. $ any_usage[is.na (summary_usage_by_treat$ any_usage)] <- 0 = summary_usage_by_treat %>% group_by (treat) %>% summarise (students_with_usage = sum (any_usage == 1 , na.rm = TRUE ),students_no_usage = sum (any_usage == 0 , na.rm = TRUE ),.groups = "drop" print (summary_usage_by_treat)
# A tibble: 2 × 3
treat students_with_usage students_no_usage
<dbl> <int> <int>
1 0 0 926
2 1 99 0
Count of students with any usage logged (defined as total_sessions => 1) by school . Exported.
= merge_vt_penn_usage_final %>% mutate (any_usage = ifelse (total_sessions >= 1 , 1 , 0 )) $ any_usage[is.na (any_usage_by_school$ any_usage)] <- 0 = 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: 8 × 3
school students_with_usage students_no_usage
<chr> <int> <int>
1 Aldan Elementary School 21 32
2 Ardmore Avenue Elementary School 0 311
3 Bell Avenue Elementary School 42 13
4 Colwyn Elementary School 28 1
5 East Lansdowne Elementary School 0 155
6 Park Lane Elementary School 0 170
7 W.B. Evans Elementary School 0 179
8 Walnut Street Elementary School 8 65
write.csv (any_usage_by_school, file = "vt_penn_any_usage_by_school.csv" )
School-level summary of usage variables . Including Q1, Median, Mean, Q3, and SD. Exported.
= c ("num_sessions_AI" ,"num_sessions_human" ,"num_classesviewed" ,"num_essaysreviewed" ,"num_AI_practiceproblemsessions" = merge_vt_penn_usage_final %>% group_by (school) %>% summarise (across (all_of (usage_vars),list (Q1 = ~ quantile (., 0.25 , na.rm = TRUE ),median = ~ median (., na.rm = TRUE ),mean = ~ mean (., na.rm = TRUE ),Q3 = ~ quantile (., 0.75 , na.rm = TRUE ),sd = ~ sd (., na.rm = TRUE ).names = "{.col}_{.fn}" .groups = "drop" )print (summary_usage_by_school)
# A tibble: 8 × 26
school num_sessions_AI_Q1 num_sessions_AI_median num_sessions_AI_mean
<chr> <dbl> <dbl> <dbl>
1 Aldan Elementa… 0 2 1.95
2 Ardmore Avenue… NA NA NaN
3 Bell Avenue El… 0.25 2 2.19
4 Colwyn Element… 0 0 1.39
5 East Lansdowne… NA NA NaN
6 Park Lane Elem… NA NA NaN
7 W.B. Evans Ele… NA NA NaN
8 Walnut Street … 0 0 0.25
# ℹ 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_penn_summary_usage_by_school.csv" )
Export files
Save e2i file with all matched students from the merge, regardless of missing test info.
#select columns ready for e2i = merge_vt_penn_usage_final %>% :: select (student_id,#export e2i with test indicators, all matched students from the merge, regardless of missing test info (all_tests == 1 or 0) write.csv (vt_penn_e2i_usage_test_info, file = "vt_penn_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_penn_e2i_usage_test_info$ both_math)
#subset: keep only students with complete test info = vt_penn_e2i_usage_test_info %>% filter (both_math == 1 )print (vt_penn_e2i_both_math)
# A tibble: 954 × 45
student_id school_num school grade school_grade grade3 grade4 grade5 grade6
<dbl> <dbl> <chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 203802 4 Colwyn … 6 Colwyn 6 0 0 0 1
2 203862 4 Colwyn … 6 Colwyn 6 0 0 0 1
3 204106 4 Colwyn … 6 Colwyn 6 0 0 0 1
4 204769 4 Colwyn … 5 Colwyn 5 0 0 1 0
5 204787 4 Colwyn … 5 Colwyn 5 0 0 1 0
6 204896 4 Colwyn … 5 Colwyn 5 0 0 1 0
7 204671 4 Colwyn … 5 Colwyn 5 0 0 1 0
8 205051 4 Colwyn … 5 Colwyn 5 0 0 1 0
9 205898 4 Colwyn … 6 Colwyn 6 0 0 0 1
10 206039 4 Colwyn … 6 Colwyn 6 0 0 0 1
# ℹ 944 more rows
# ℹ 36 more variables: male <dbl>, iep <dbl>, ell <dbl>, frpl <dbl>,
# race_recoded <chr>, race_A <dbl>, race_B <dbl>, race_H <dbl>, race_O <dbl>,
# race_W <dbl>, moy_math_comp <dbl>, moy_math_z <dbl>, moy_ela_comp <dbl>,
# moy_ela_z <dbl>, eoy_math_comp <dbl>, eoy_math_z <dbl>, eoy_ela_comp <dbl>,
# eoy_ela_z <dbl>, treat <dbl>, vt_acct_id <dbl>, vt_present <dbl>,
# num_days_active <dbl>, num_days_active_recoded <dbl>, num_weeks <dbl>, …
write.csv (vt_penn_e2i_both_math, file = "vt_penn_e2i_complete_math_tests.csv" )
Save e2i file with only containing matched students from the merge with complete test info for ELA.
table (vt_penn_e2i_usage_test_info$ both_ela)
#subset: keep only students with complete test info = vt_penn_e2i_usage_test_info %>% filter (both_ela == 1 )print (vt_penn_e2i_both_ela)
# A tibble: 948 × 45
student_id school_num school grade school_grade grade3 grade4 grade5 grade6
<dbl> <dbl> <chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 203802 4 Colwyn … 6 Colwyn 6 0 0 0 1
2 203862 4 Colwyn … 6 Colwyn 6 0 0 0 1
3 204106 4 Colwyn … 6 Colwyn 6 0 0 0 1
4 204769 4 Colwyn … 5 Colwyn 5 0 0 1 0
5 204787 4 Colwyn … 5 Colwyn 5 0 0 1 0
6 204896 4 Colwyn … 5 Colwyn 5 0 0 1 0
7 204671 4 Colwyn … 5 Colwyn 5 0 0 1 0
8 205051 4 Colwyn … 5 Colwyn 5 0 0 1 0
9 205898 4 Colwyn … 6 Colwyn 6 0 0 0 1
10 206039 4 Colwyn … 6 Colwyn 6 0 0 0 1
# ℹ 938 more rows
# ℹ 36 more variables: male <dbl>, iep <dbl>, ell <dbl>, frpl <dbl>,
# race_recoded <chr>, race_A <dbl>, race_B <dbl>, race_H <dbl>, race_O <dbl>,
# race_W <dbl>, moy_math_comp <dbl>, moy_math_z <dbl>, moy_ela_comp <dbl>,
# moy_ela_z <dbl>, eoy_math_comp <dbl>, eoy_math_z <dbl>, eoy_ela_comp <dbl>,
# eoy_ela_z <dbl>, treat <dbl>, vt_acct_id <dbl>, vt_present <dbl>,
# num_days_active <dbl>, num_days_active_recoded <dbl>, num_weeks <dbl>, …
write.csv (vt_penn_e2i_both_ela, file = "vt_penn_e2i_complete_ela_tests.csv" )
##Prepare file for pooling
Using the data frame where comparison students with unexpected usage have been removed, but treatment students of any usage level are included (merge_vt_penn_usage), recreate treatment variable.
$ treat <- ifelse (merge_vt_penn_usage$ vt_present == 1 & merge_vt_penn_usage$ num_sessions_week >= 1 , 1 , 0 )table (merge_vt_penn_usage_final$ treat)
Standardize raw race var name for pooling.
$ race = merge_vt_penn_usage$ race_ethtable (merge_vt_penn_usage$ race)
AMERICAN INDIAN/ALASKAN ASIAN
22 31
BLACK/AFRICAN AMERICAN HISPANIC
1101 43
MULTI RACIAL - NON HISPANIC NATIVE HAWAIIAN
1 8
WHITE/CAUCASIAN
73
Save a file for conducting analyses in R. Includes students offered Live+AI but who do not meet the usage threshold, i.e., no treat var in this export.
#select columns = merge_vt_penn_usage %>% :: select (student_id,#original race for pooled coding later #export file with all matched students from the merge, regardless of missing test info (all_tests == 1 or 0) write.csv (vt_penn_all_students, file = "vt_penn_all_students.csv" , row.names = FALSE )
