Clean District Data
Import
Packages selected for inspecting and cleaning Third Learning Space, National Heritage Academy data.
── 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 ("TSL Impact Evaluation Data.csv" )
Rows: 4340 Columns: 18
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (7): School Name*, Date of BOY MAP Math Test*, Date of MOY MAP Math Tes...
dbl (10): Student ID*, Student Grade Level*, Teacher ID*, BOY MAP Math Scale...
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.
[1] "Student ID*" "Student Grade Level*"
[3] "School Name*" "Teacher ID*"
[5] "Treatment*" "BOY MAP Math Scaled Score*"
[7] "Date of BOY MAP Math Test*" "MOY MAP Math Scaled Score*"
[9] "Date of MOY MAP Math Test*" "EOY MAP Math Scaled Score*"
[11] "Date of EOY MAP Math Test*" "Gender"
[13] "IEP" "ELL"
[15] "FRPL" "Race"
[17] "Ethnicity" "Attendance Rate"
Change column headers for roster.
= roster %>% rename (student_id = "Student ID*" ,teacher_id = "Teacher ID*" ,grade = "Student Grade Level*" , school = "School Name*" , treat = "Treatment*" , boy_math_score = "BOY MAP Math Scaled Score*" , boy_math_date = "Date of BOY MAP Math Test*" , moy_math_score = "MOY MAP Math Scaled Score*" , moy_math_date = "Date of MOY MAP Math Test*" , eoy_math_score = "EOY MAP Math Scaled Score*" , eoy_math_date = "Date of EOY MAP Math Test*" , gender = "Gender" , iep = "IEP" , ell = "ELL" , frpl = "FRPL" , race = "Race" ,ethn = "Ethnicity" , attend_rate = "Attendance Rate" colnames (roster)
[1] "student_id" "grade" "school" "teacher_id"
[5] "treat" "boy_math_score" "boy_math_date" "moy_math_score"
[9] "moy_math_date" "eoy_math_score" "eoy_math_date" "gender"
[13] "iep" "ell" "frpl" "race"
[17] "ethn" "attend_rate"
Inspect school names.
Bennett Venture Academy Burton Glen Charter Academy
225 401
Holly Park Academy Laurus Academy
323 492
Linden Charter Academy North Saginaw Charter Academy
488 455
Oakside Prep Academy Stambaugh Charter Academy
612 260
Walton Charter Academy Windemere Park Charter Academy
521 360
Winterfield Venture Academy
203
Create school_num for each school.
= roster %>% mutate (school_num = recode (school,"Bennett Venture Academy" = 1 ,"Burton Glen Charter Academy" = 2 ,"Holly Park Academy" = 3 , "Laurus Academy" = 4 , "Linden Charter Academy" = 5 , "North Saginaw Charter Academy" = 6 , "Oakside Prep Academy" = 7 ,"Stambaugh Charter Academy" = 8 , "Walton Charter Academy" = 9 , "Windemere Park Charter Academy" = 10 , "Winterfield Venture Academy" = 11 ))table (roster$ school_num)
1 2 3 4 5 6 7 8 9 10 11
225 401 323 492 488 455 612 260 521 360 203
Inspect duplicates
Roster
Identify duplicate student records within roster file.
# 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: 8 × 2
student_id n
<dbl> <int>
1 566870 2
2 605643 2
3 632862 2
4 670363 2
5 680297 2
6 732197 2
7 732203 2
8 832717 2
#export write.csv (dupes_roster, file = "duplicate_ids_roster_tsl_nha.csv" , row.names = FALSE )
Observation: Each duplicate student ID appears exactly 2 times. For a given duplicate student record, it appears to be the same individual (e.g., same demographics, same teacher), however, the duplicate record is tied to 2 different schools. These students have taken one test at one school site and completed their EOY test date at a different school site. This suggests that the students have transferred some point between BOY and EOY testing.
= roster %>% group_by (student_id) %>% filter (n () > 1 ) %>% ungroup ()print (dup_preview_roster)
# A tibble: 16 × 19
student_id grade school teacher_id treat boy_math_score boy_math_date
<dbl> <dbl> <chr> <dbl> <lgl> <dbl> <chr>
1 566870 7 Walton Charte… 19525 NA NA <NA>
2 566870 7 Oakside Prep … 19525 NA 216 9/10/2025
3 605643 8 Walton Charte… 21216 NA 229 8/28/2025
4 605643 8 Oakside Prep … 21216 NA NA <NA>
5 632862 7 Oakside Prep … 23257 NA NA <NA>
6 632862 7 Walton Charte… 23257 NA 204 8/28/2025
7 670363 3 Linden Charte… 18378 NA 196 9/29/2025
8 670363 4 Linden Charte… 18378 NA NA <NA>
9 680297 3 Oakside Prep … 23154 NA NA <NA>
10 680297 3 Walton Charte… 23154 NA 181 8/28/2025
11 732197 5 Walton Charte… 17341 NA NA <NA>
12 732197 5 Oakside Prep … 17341 NA 198 9/3/2025
13 732203 8 Walton Charte… 23087 NA NA <NA>
14 732203 8 Oakside Prep … 23087 NA 199 9/10/2025
15 832717 3 Burton Glen C… 18916 NA NA <NA>
16 832717 3 Holly Park Ac… 18916 NA 172 8/21/2025
# ℹ 12 more variables: moy_math_score <dbl>, moy_math_date <chr>,
# eoy_math_score <dbl>, eoy_math_date <chr>, gender <chr>, iep <dbl>,
# ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>, attend_rate <dbl>,
# school_num <dbl>
Decision : Collapse rows to obtain full test data but keep the school, grade, and demographics where EOY test was taken. Total observations reduced from 4340 to 4332 (8 duplicate student records removed).
#placeholder code = roster %>% group_by (student_id) %>% summarize (# Pull BOY values (non-missing) boy_math_score = first (na.omit (boy_math_score)),boy_math_date = first (na.omit (boy_math_date)),# Pull EOY values (non-missing) eoy_math_score = first (na.omit (eoy_math_score)),eoy_math_date = first (na.omit (eoy_math_date)),# Identify the EOY row (where EOY score exists) school = school[! is.na (eoy_math_score)][1 ],school_num = school_num[! is.na (eoy_math_score)][1 ],grade = grade[! is.na (eoy_math_score)][1 ],teacher_id = teacher_id [! is.na (eoy_math_score)][1 ],moy_math_score = moy_math_score[! is.na (eoy_math_score)][1 ],moy_math_date = moy_math_date[! is.na (eoy_math_score)][1 ],gender = gender[! is.na (eoy_math_score)][1 ],iep = iep[! is.na (eoy_math_score)][1 ],ell = ell[! is.na (eoy_math_score)][1 ],frpl = frpl[! is.na (eoy_math_score)][1 ],race = race[! is.na (eoy_math_score)][1 ],ethn = ethn[! is.na (eoy_math_score)][1 ],attend_rate = attend_rate[! is.na (eoy_math_score)][1 ],.groups = "drop" #decision to be made print (roster_unique)
# A tibble: 4,332 × 18
student_id boy_math_score boy_math_date eoy_math_score eoy_math_date school
<dbl> <dbl> <chr> <dbl> <chr> <chr>
1 366259 202 9/3/2025 207 5/18/2026 Laurus …
2 430015 218 9/3/2025 217 5/20/2026 Laurus …
3 445701 204 9/29/2025 217 5/26/2026 Linden …
4 461310 170 9/10/2025 173 6/2/2026 Burton …
5 471757 224 9/24/2025 236 5/20/2026 North S…
6 472379 209 9/8/2025 226 6/2/2026 Oakside…
7 473083 225 9/23/2025 228 5/18/2026 Walton …
8 473087 204 9/3/2025 199 5/18/2026 Walton …
9 474393 212 9/10/2025 NA <NA> <NA>
10 475569 244 8/28/2025 255 5/18/2026 Walton …
# ℹ 4,322 more rows
# ℹ 12 more variables: school_num <dbl>, grade <dbl>, teacher_id <dbl>,
# moy_math_score <dbl>, moy_math_date <chr>, gender <chr>, iep <dbl>,
# ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>, attend_rate <dbl>
Du-dupe check. Confirmed: no more duplicate records.
%>% count (student_id) %>% filter (n > 1 )
# A tibble: 0 × 2
# ℹ 2 variables: student_id <dbl>, n <int>
Merge Usage Data
Clean NHA Usage Data
Import usage data from TSL.
#import NHA usage data from TSL = read_csv ("TSL Usage Data.csv" )
New names:
Rows: 999 Columns: 24
── Column specification
──────────────────────────────────────────────────────── Delimiter: "," chr
(5): SIS ID, School Name, School Clever ID, Session start date, Conf_ra... dbl
(8): TSL Student ID, Count of Check Out Confidence Answer, Total CO Con... num
(1): Num_minutes* lgl (10): ...15, ...16, ...17, ...18, ...19, ...20, ...21,
...22, ...23, ...24
ℹ 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.
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`
• `` -> `...24`
[1] "TSL Student ID"
[2] "SIS ID"
[3] "School Name"
[4] "School Clever ID"
[5] "Session start date"
[6] "Count of Check Out Confidence Answer"
[7] "Total CO Confidence Same Or Improved"
[8] "Conf_rating* % same or improved"
[9] "Conf_rating* same or improved score/5"
[10] "Enjoy_rating*"
[11] "Num_session*"
[12] "Num_minutes*"
[13] "Active_participation*"
[14] "Total LOs Completed"
[15] "...15"
[16] "...16"
[17] "...17"
[18] "...18"
[19] "...19"
[20] "...20"
[21] "...21"
[22] "...22"
[23] "...23"
[24] "...24"
Rename columns in usage file. Prepare for merging.
= tsl_nha_usage [, - c (15 : 24 )] #drop empty columns # rename vars = tsl_nha_usage %>% rename (student_id = "SIS ID" ,tsl_acct_id = "TSL Student ID" , school_tsl = "School Name" ,school_code_tsl = "School Clever ID" ,session_BOY_date = "Session start date" ,n_co_confid = "Count of Check Out Confidence Answer" ,n_co_confid_same_imp = "Total CO Confidence Same Or Improved" ,perc_confid_same_imp = "Conf_rating* % same or improved" ,score_confid_same_imp = "Conf_rating* same or improved score/5" ,enjoy_rate = "Enjoy_rating*" ,num_session = "Num_session*" , num_min = "Num_minutes*" , activ_partic = "Active_participation*" ,n_lo_complete = "Total LOs Completed" colnames (tsl_nha_usage)
[1] "tsl_acct_id" "student_id" "school_tsl"
[4] "school_code_tsl" "session_BOY_date" "n_co_confid"
[7] "n_co_confid_same_imp" "perc_confid_same_imp" "score_confid_same_imp"
[10] "enjoy_rate" "num_session" "num_min"
[13] "activ_partic" "n_lo_complete"
Identify duplicate student records within usage data file (N=0).
# frequency table of tsl_acct_id occurrences = tsl_nha_usage %>% group_by (tsl_acct_id) %>% summarise (n = n ()) %>% arrange (desc (n))# filter only acct that appear > 1 = id_counts_tsl_usage_nha %>% filter (n > 1 )# view results print (dupes_tsl_usage_nha)
# A tibble: 1 × 2
tsl_acct_id n
<dbl> <int>
1 NA 346
#export #write.csv(dupes_tsl_usage_nha, file = "duplicate_acct_ids_usage_tsl_nha.csv", row.names = FALSE)
Inspect rows without TSL Account ID (N=346). Observation: these cases are all blanks carried over from csv conversion.
%>% filter (is.na (tsl_acct_id))
# A tibble: 346 × 14
tsl_acct_id student_id school_tsl school_code_tsl session_BOY_date
<dbl> <chr> <chr> <chr> <chr>
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>
# ℹ 336 more rows
# ℹ 9 more variables: n_co_confid <dbl>, n_co_confid_same_imp <dbl>,
# perc_confid_same_imp <chr>, score_confid_same_imp <dbl>, enjoy_rate <dbl>,
# num_session <dbl>, num_min <dbl>, activ_partic <dbl>, n_lo_complete <dbl>
Decision : Remove rows where TSL Account ID is missing.
= tsl_nha_usage %>% filter (! is.na (tsl_acct_id))print (tsl_nha_usage)
# A tibble: 653 × 14
tsl_acct_id student_id school_tsl school_code_tsl session_BOY_date
<dbl> <chr> <chr> <chr> <chr>
1 1002128 Student Archived Burton Glen Ch… 54c7fe26275d78… 03/02/2026
2 1003094 Student Archived Winterfield Ve… 54c7fe26275d78… 25/02/2026
3 1002836 854674 Holly Park Aca… 54c7fe26275d78… 26/02/2026
4 1002811 737634 Holly Park Aca… 54c7fe26275d78… 26/02/2026
5 1002951 688666 Laurus Academy 54c7fe26275d78… 03/02/2026
6 1002905 625958 Laurus Academy 54c7fe26275d78… 16/03/2026
7 1002113 614413 Burton Glen Ch… 54c7fe26275d78… 02/02/2026
8 1001718 592802 Linden Charter… 54c7fe26275d78… 18/02/2026
9 1001628 Student Archived Oakside Prep A… 54c7fe26275d78… 28/01/2026
10 1002111 Student Archived Burton Glen Ch… 54c7fe26275d78… 02/02/2026
# ℹ 643 more rows
# ℹ 9 more variables: n_co_confid <dbl>, n_co_confid_same_imp <dbl>,
# perc_confid_same_imp <chr>, score_confid_same_imp <dbl>, enjoy_rate <dbl>,
# num_session <dbl>, num_min <dbl>, activ_partic <dbl>, n_lo_complete <dbl>
Inspect rows where student ID notes “Student Archived”.
%>% filter (student_id == "Student Archived" )
# A tibble: 24 × 14
tsl_acct_id student_id school_tsl school_code_tsl session_BOY_date
<dbl> <chr> <chr> <chr> <chr>
1 1002128 Student Archived Burton Glen Ch… 54c7fe26275d78… 03/02/2026
2 1003094 Student Archived Winterfield Ve… 54c7fe26275d78… 25/02/2026
3 1001628 Student Archived Oakside Prep A… 54c7fe26275d78… 28/01/2026
4 1002111 Student Archived Burton Glen Ch… 54c7fe26275d78… 02/02/2026
5 1002834 Student Archived Holly Park Aca… 54c7fe26275d78… 27/01/2026
6 1001779 Student Archived Linden Charter… 54c7fe26275d78… 18/02/2026
7 1002713 Student Archived Walton Charter… 54c7fe26275d78… 29/01/2026
8 1003079 Student Archived Winterfield Ve… 54c7fe26275d78… 12/02/2026
9 1002123 Student Archived Burton Glen Ch… 54c7fe26275d78… 02/02/2026
10 1002120 Student Archived Burton Glen Ch… 54c7fe26275d78… 03/02/2026
# ℹ 14 more rows
# ℹ 9 more variables: n_co_confid <dbl>, n_co_confid_same_imp <dbl>,
# perc_confid_same_imp <chr>, score_confid_same_imp <dbl>, enjoy_rate <dbl>,
# num_session <dbl>, num_min <dbl>, activ_partic <dbl>, n_lo_complete <dbl>
Decision : Remove student rows where student ID notes “Student Archived”(N=629).
= tsl_nha_usage %>% filter (student_id != "Student Archived" )print (tsl_nha_usage)
# A tibble: 629 × 14
tsl_acct_id student_id school_tsl school_code_tsl session_BOY_date
<dbl> <chr> <chr> <chr> <chr>
1 1002836 854674 Holly Park Academy 54c7fe26275d78… 26/02/2026
2 1002811 737634 Holly Park Academy 54c7fe26275d78… 26/02/2026
3 1002951 688666 Laurus Academy 54c7fe26275d78… 03/02/2026
4 1002905 625958 Laurus Academy 54c7fe26275d78… 16/03/2026
5 1002113 614413 Burton Glen Charter … 54c7fe26275d78… 02/02/2026
6 1001718 592802 Linden Charter Acade… 54c7fe26275d78… 18/02/2026
7 1002843 847934 Holly Park Academy 54c7fe26275d78… 04/03/2026
8 1003634 845181 Laurus Academy 54c7fe26275d78… 09/02/2026
9 1003044 843138 Stambaugh Charter Ac… 54c7fe26275d78… 05/02/2026
10 1003096 840127 Winterfield Venture … 54c7fe26275d78… 17/02/2026
# ℹ 619 more rows
# ℹ 9 more variables: n_co_confid <dbl>, n_co_confid_same_imp <dbl>,
# perc_confid_same_imp <chr>, score_confid_same_imp <dbl>, enjoy_rate <dbl>,
# num_session <dbl>, num_min <dbl>, activ_partic <dbl>, n_lo_complete <dbl>
Clean strings
Drop “%” string from perc_confid_same_imp.
$ perc_confid_same_imp = as.numeric (sub ("%$" , "" , tsl_nha_usage$ perc_confid_same_imp))mean (tsl_nha_usage$ perc_confid_same_imp, na.rm = TRUE )
Merge
Create tsl_present: Equals 1 if student id appears in roster from TSL (N=629). All else equals 0.
= roster_unique %>% mutate (tsl_present = ifelse (student_id %in% tsl_nha_usage$ student_id, 1 , 0 )table (roster_unique$ tsl_present)
Inspect for any duplicates ID among those that appear in the TSL roster. Zero duplicates.
%>% filter (tsl_present == 1 ) %>% group_by (student_id) %>% filter (n () > 1 )
# A tibble: 0 × 19
# Groups: student_id [0]
# ℹ 19 variables: student_id <dbl>, boy_math_score <dbl>, boy_math_date <chr>,
# eoy_math_score <dbl>, eoy_math_date <chr>, school <chr>, school_num <dbl>,
# grade <dbl>, teacher_id <dbl>, moy_math_score <dbl>, moy_math_date <chr>,
# gender <chr>, iep <dbl>, ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>,
# attend_rate <dbl>, tsl_present <dbl>
Merge usage data to student NHA data.
#ensure student_id is same format for merging $ student_id = as.character (roster_unique$ student_id)$ student_id = as.character (tsl_nha_usage$ student_id)= roster_unique %>% left_join (tsl_nha_usage, by = "student_id" )print (merge_tsl_nha)
# A tibble: 4,332 × 32
student_id boy_math_score boy_math_date eoy_math_score eoy_math_date school
<chr> <dbl> <chr> <dbl> <chr> <chr>
1 366259 202 9/3/2025 207 5/18/2026 Laurus …
2 430015 218 9/3/2025 217 5/20/2026 Laurus …
3 445701 204 9/29/2025 217 5/26/2026 Linden …
4 461310 170 9/10/2025 173 6/2/2026 Burton …
5 471757 224 9/24/2025 236 5/20/2026 North S…
6 472379 209 9/8/2025 226 6/2/2026 Oakside…
7 473083 225 9/23/2025 228 5/18/2026 Walton …
8 473087 204 9/3/2025 199 5/18/2026 Walton …
9 474393 212 9/10/2025 NA <NA> <NA>
10 475569 244 8/28/2025 255 5/18/2026 Walton …
# ℹ 4,322 more rows
# ℹ 26 more variables: school_num <dbl>, grade <dbl>, teacher_id <dbl>,
# moy_math_score <dbl>, moy_math_date <chr>, gender <chr>, iep <dbl>,
# ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>, attend_rate <dbl>,
# tsl_present <dbl>, tsl_acct_id <dbl>, school_tsl <chr>,
# school_code_tsl <chr>, session_BOY_date <chr>, n_co_confid <dbl>,
# n_co_confid_same_imp <dbl>, perc_confid_same_imp <dbl>, …
Insert treatment status
Add treatment status. First, filter out students who have less than 2 sessions. They are not part of the final sample group and should not be used as comparison students. Among students where tsl_present = 1, exclude those where num_session < 2.
= merge_tsl_nha %>% filter (tsl_present != 1 | num_session > 2 ) print (merge_tsl_nha)
# A tibble: 4,279 × 32
student_id boy_math_score boy_math_date eoy_math_score eoy_math_date school
<chr> <dbl> <chr> <dbl> <chr> <chr>
1 366259 202 9/3/2025 207 5/18/2026 Laurus …
2 430015 218 9/3/2025 217 5/20/2026 Laurus …
3 445701 204 9/29/2025 217 5/26/2026 Linden …
4 461310 170 9/10/2025 173 6/2/2026 Burton …
5 471757 224 9/24/2025 236 5/20/2026 North S…
6 472379 209 9/8/2025 226 6/2/2026 Oakside…
7 473083 225 9/23/2025 228 5/18/2026 Walton …
8 473087 204 9/3/2025 199 5/18/2026 Walton …
9 474393 212 9/10/2025 NA <NA> <NA>
10 475569 244 8/28/2025 255 5/18/2026 Walton …
# ℹ 4,269 more rows
# ℹ 26 more variables: school_num <dbl>, grade <dbl>, teacher_id <dbl>,
# moy_math_score <dbl>, moy_math_date <chr>, gender <chr>, iep <dbl>,
# ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>, attend_rate <dbl>,
# tsl_present <dbl>, tsl_acct_id <dbl>, school_tsl <chr>,
# school_code_tsl <chr>, session_BOY_date <chr>, n_co_confid <dbl>,
# n_co_confid_same_imp <dbl>, perc_confid_same_imp <dbl>, …
# keep students that don't appear in TSL usage OR students that meet usage threshold.
Create treat: Equals 1 if students had more than 2 sessions. All else equals 0.
= merge_tsl_nha %>% mutate (treat = ifelse (! is.na (num_session) & num_session > 2 , 1 , 0 ))table (merge_tsl_nha$ treat)
Missing Data Inspections
Percentage missing for each variable. Observations: Most variables from the TSL file are missing, as expected; after merging the district roster with TSL usage file, not all students will be in the TSL system. Looking at district data columns, most of the data is present (test info, demographics, etc). The repeating missing percentage (6.37%) across EOY test info variables, school, grade, and demographics suggests there is a small group of students missing this info. The large majority of students are missing MOY test info. No student entry is missing an ID or treatment status, though.
= merge_tsl_nha %>% 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: 33 × 2
variable percent_missing
<chr> <dbl>
1 enjoy_rate 89.3
2 n_co_confid 87.7
3 n_co_confid_same_imp 87.7
4 perc_confid_same_imp 87.7
5 score_confid_same_imp 87.7
6 tsl_acct_id 86.5
7 school_tsl 86.5
8 school_code_tsl 86.5
9 session_BOY_date 86.5
10 num_session 86.5
# ℹ 23 more rows
write.csv (missing_summary, file = "tsl_nha_missing_summary_overall.csv" , row.names = FALSE )
Inspect observations where grade is missing (N = 275). All seem to be missing district data except for baseline, BOY test info. Exported.
= merge_tsl_nha %>% filter (is.na (grade))print (missing_grade_district_info)
# A tibble: 275 × 33
student_id boy_math_score boy_math_date eoy_math_score eoy_math_date school
<chr> <dbl> <chr> <dbl> <chr> <chr>
1 474393 212 9/10/2025 NA <NA> <NA>
2 494172 221 9/18/2025 NA <NA> <NA>
3 502666 179 9/23/2025 NA <NA> <NA>
4 503758 212 9/3/2025 NA <NA> <NA>
5 513006 217 9/10/2025 NA <NA> <NA>
6 517572 204 9/8/2025 NA <NA> <NA>
7 522347 237 9/29/2025 NA <NA> <NA>
8 523328 221 9/10/2025 NA <NA> <NA>
9 529588 195 9/24/2025 NA <NA> <NA>
10 533690 199 8/27/2025 NA <NA> <NA>
# ℹ 265 more rows
# ℹ 27 more variables: school_num <dbl>, grade <dbl>, teacher_id <dbl>,
# moy_math_score <dbl>, moy_math_date <chr>, gender <chr>, iep <dbl>,
# ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>, attend_rate <dbl>,
# tsl_present <dbl>, tsl_acct_id <dbl>, school_tsl <chr>,
# school_code_tsl <chr>, session_BOY_date <chr>, n_co_confid <dbl>,
# n_co_confid_same_imp <dbl>, perc_confid_same_imp <dbl>, …
write.csv (missing_grade_district_info, file = "tsl_nha_missing_grade_district_info.csv" , row.names = FALSE )
Of these students with missing data, some do have TSL usage data (N = 10). Exported.
= merge_tsl_nha %>% filter (treat == 1 ) %>% filter (is.na (grade))print (missing_grade_district_info_treat)
# A tibble: 10 × 33
student_id boy_math_score boy_math_date eoy_math_score eoy_math_date school
<chr> <dbl> <chr> <dbl> <chr> <chr>
1 503758 212 9/3/2025 NA <NA> <NA>
2 578514 213 8/22/2025 NA <NA> <NA>
3 579897 199 9/2/2025 NA <NA> <NA>
4 588508 214 9/2/2025 NA <NA> <NA>
5 595757 208 9/2/2025 NA <NA> <NA>
6 602774 192 9/15/2025 NA <NA> <NA>
7 658690 199 9/18/2025 NA <NA> <NA>
8 667967 209 9/2/2025 NA <NA> <NA>
9 703899 170 8/22/2025 NA <NA> <NA>
10 829256 205 9/12/2025 NA <NA> <NA>
# ℹ 27 more variables: school_num <dbl>, grade <dbl>, teacher_id <dbl>,
# moy_math_score <dbl>, moy_math_date <chr>, gender <chr>, iep <dbl>,
# ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>, attend_rate <dbl>,
# tsl_present <dbl>, tsl_acct_id <dbl>, school_tsl <chr>,
# school_code_tsl <chr>, session_BOY_date <chr>, n_co_confid <dbl>,
# n_co_confid_same_imp <dbl>, perc_confid_same_imp <dbl>,
# score_confid_same_imp <dbl>, enjoy_rate <dbl>, num_session <dbl>, …
write.csv (missing_grade_district_info_treat, file = "tsl_nha_missing_grade_district_info_treat.csv" , row.names = FALSE )
Decision : Remove observation where grade and other district data is missing.
= merge_tsl_nha %>% filter (! is.na (grade))print (merge_tsl_nha)
# A tibble: 4,004 × 33
student_id boy_math_score boy_math_date eoy_math_score eoy_math_date school
<chr> <dbl> <chr> <dbl> <chr> <chr>
1 366259 202 9/3/2025 207 5/18/2026 Laurus …
2 430015 218 9/3/2025 217 5/20/2026 Laurus …
3 445701 204 9/29/2025 217 5/26/2026 Linden …
4 461310 170 9/10/2025 173 6/2/2026 Burton …
5 471757 224 9/24/2025 236 5/20/2026 North S…
6 472379 209 9/8/2025 226 6/2/2026 Oakside…
7 473083 225 9/23/2025 228 5/18/2026 Walton …
8 473087 204 9/3/2025 199 5/18/2026 Walton …
9 475569 244 8/28/2025 255 5/18/2026 Walton …
10 475590 202 9/2/2025 212 5/18/2026 Walton …
# ℹ 3,994 more rows
# ℹ 27 more variables: school_num <dbl>, grade <dbl>, teacher_id <dbl>,
# moy_math_score <dbl>, moy_math_date <chr>, gender <chr>, iep <dbl>,
# ell <dbl>, frpl <dbl>, race <chr>, ethn <chr>, attend_rate <dbl>,
# tsl_present <dbl>, tsl_acct_id <dbl>, school_tsl <chr>,
# school_code_tsl <chr>, session_BOY_date <chr>, n_co_confid <dbl>,
# n_co_confid_same_imp <dbl>, perc_confid_same_imp <dbl>, …
Percentage missing for each variable, grouped by grade, among treatment students. Exported.
= merge_tsl_nha %>% filter (treat == 1 ) %>% group_by (grade) %>% summarise (across (everything (),~ mean (is.na (.)) * 100 ,.names = "pct_miss_{.col}" ),.groups = "drop" ) %>% pivot_longer (cols = - grade,names_to = "variable" ,values_to = "pct_missing" %>% mutate (variable = sub ("^pct_miss_" , "" , variable))print (missing_summary_by_grade_treat)
# A tibble: 192 × 3
grade variable pct_missing
<dbl> <chr> <dbl>
1 3 student_id 0
2 3 boy_math_score 0
3 3 boy_math_date 0
4 3 eoy_math_score 0
5 3 eoy_math_date 0
6 3 school 0
7 3 school_num 0
8 3 teacher_id 0
9 3 moy_math_score 89.6
10 3 moy_math_date 89.6
# ℹ 182 more rows
#export write.csv (missing_summary_by_grade_treat, file = "tsl_nha_pct_missing_vars_by_grade_treat.csv" , row.names = FALSE )
Percentage missing for each variable, grouped by grade, among comparison students. Exported.
= merge_tsl_nha %>% filter (treat == 0 ) %>% group_by (grade) %>% summarise (across (everything (),~ mean (is.na (.)) * 100 ,.names = "pct_miss_{.col}" ),.groups = "drop" ) %>% pivot_longer (cols = - grade,names_to = "variable" ,values_to = "pct_missing" %>% mutate (variable = sub ("^pct_miss_" , "" , variable))print (missing_summary_by_grade_comp)
# A tibble: 192 × 3
grade variable pct_missing
<dbl> <chr> <dbl>
1 3 student_id 0
2 3 boy_math_score 7.22
3 3 boy_math_date 7.22
4 3 eoy_math_score 0
5 3 eoy_math_date 0
6 3 school 0
7 3 school_num 0
8 3 teacher_id 0
9 3 moy_math_score 69.9
10 3 moy_math_date 69.9
# ℹ 182 more rows
#export write.csv (missing_summary_by_grade_comp, file = "tsl_nha_pct_missing_vars_by_grade_comp.csv" , row.names = FALSE )
Percentage of students who have both BOY and EOY data and ALL math data (BOY, MOY, EOY) overall
#indicator for having BOTH MOY and EOY scores = merge_tsl_nha %>% mutate (boy_eoy_math = ! is.na (boy_math_score) & ! is.na (eoy_math_score),all_math = ! is.na (boy_math_score) & ! is.na (moy_math_score) # incl. MOY & ! is.na (eoy_math_score),#pct across schools = merge_tsl_nha_test_info %>% summarise (total_students = n (),students_with_boy_eoy = sum (boy_eoy_math), percent_with_boy_eoy = sum (students_with_boy_eoy / total_students) * 100 , students_with_all_tests = sum (all_math),percent_with_all_tests = (students_with_all_tests / total_students) * 100 %>% mutate (percent_with_boy_eoy = round (percent_with_boy_eoy, 2 ))%>% mutate (percent_with_all_tests = round (percent_with_all_tests, 2 ))print (overall_pct)
# A tibble: 1 × 5
total_students students_with_boy_eoy percent_with_boy_eoy
<int> <int> <dbl>
1 4004 3797 94.8
# ℹ 2 more variables: students_with_all_tests <int>,
# percent_with_all_tests <dbl>
Percentage of students who have both BOY and EOY data by school Assuming MOY is not of interest. Only 21% of the sample have all test info, including MOY test.
= merge_tsl_nha_test_info %>% group_by (school) %>% summarise (total_students = n (),students_with_boy_eoy = sum (boy_eoy_math),percent_with_boy_eoy = (students_with_boy_eoy / total_students) * 100 %>% mutate (percent_with_boy_eoy = round (percent_with_boy_eoy, 2 )) %>% arrange (desc (percent_with_boy_eoy))print (by_school_pct)
# A tibble: 11 × 4
school total_students students_with_boy_eoy percent_with_boy_eoy
<chr> <int> <int> <dbl>
1 Walton Charter Aca… 490 482 98.4
2 Linden Charter Aca… 452 442 97.8
3 Burton Glen Charte… 372 360 96.8
4 Holly Park Academy 297 285 96.0
5 Oakside Prep Acade… 542 519 95.8
6 Stambaugh Charter … 236 225 95.3
7 Laurus Academy 449 428 95.3
8 North Saginaw Char… 434 400 92.2
9 Windemere Park Cha… 340 312 91.8
10 Bennett Venture Ac… 204 187 91.7
11 Winterfield Ventur… 188 157 83.5
Percentage of students who have both BOY and EOY data by grade
= merge_tsl_nha_test_info %>% group_by (grade) %>% summarise (total_students = n (),students_with_boy_eoy = sum (boy_eoy_math),percent_with_boy_eoy = (students_with_boy_eoy / total_students) * 100 %>% mutate (percent_with_boy_eoy = round (percent_with_boy_eoy, 2 )) %>% arrange (desc (percent_with_boy_eoy))print (by_grade_pct)
# A tibble: 6 × 4
grade total_students students_with_boy_eoy percent_with_boy_eoy
<dbl> <int> <int> <dbl>
1 7 605 582 96.2
2 4 713 681 95.5
3 5 688 654 95.1
4 8 588 557 94.7
5 3 772 726 94.0
6 6 638 597 93.6
Dummies for test info (complete and missing indicators)
boy_eoy_math : dummy for students with complete math test info for BOY and EOY.
table (merge_tsl_nha_test_info$ boy_eoy_math)
#complete math: BOY and EOY present $ boy_eoy_math = ifelse (merge_tsl_nha_test_info$ boy_eoy_math, 1 , 0 )table (merge_tsl_nha_test_info$ boy_eoy_math) #check counts
math_boy_missing_eoy_present : dummy for students who have EOY math test info but BOY test info is missing.
= merge_tsl_nha_test_info %>% mutate (boy_math_present = ! is.na (boy_math_score) & ! is.na (boy_math_date),eoy_math_present = ! is.na (eoy_math_score) & ! is.na (eoy_math_date),math_boy_missing_eoy_present = case_when (! boy_math_present & eoy_math_present ~ 1 , # moy missing, eoy present & eoy_math_present ~ 0 , # both present ! boy_math_present & ! eoy_math_present ~ NA_real_ # both missing table (merge_tsl_nha_test_info$ math_boy_missing_eoy_present)
##Test Info Summary
Proportion of math test info present (BOY present and EOY present, grouped by treatment status).
= merge_tsl_nha_test_info %>% mutate (boy_math_present = ! is.na (boy_math_score) & ! is.na (boy_math_date),eoy_math_present = ! is.na (eoy_math_score) & ! is.na (eoy_math_date)%>% group_by (treat) %>% summarise (n_students = n (),boy_count = sum (boy_math_present),boy_prop = mean (boy_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 boy_count boy_prop eoy_count eoy_prop
<dbl> <int> <int> <dbl> <int> <dbl>
1 0 3438 3238 0.942 3438 1
2 1 566 559 0.988 566 1
Data Summary
District Data
Generate school-level means for BOY 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, including attend rate avg = merge_tsl_nha_test_info %>% mutate (across (c (boy_math_score, #transform all scores to numeric value = merge_tsl_nha_test_info %>% group_by (school)%>% summarise (n_students = n (),mean_boy_math = mean (boy_math_score, na.rm = TRUE ),mean_eoy_math = mean (eoy_math_score, na.rm = TRUE ),attend_rate = mean (attend_rate, na.rm = TRUE )# DEMOGRAPHICS # race = merge_tsl_nha_test_info %>% group_by (school, race) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = race,values_from = pct,names_prefix = "race_" #ethn BEFORE CLEANING = merge_tsl_nha_test_info %>% group_by (school, ethn) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = ethn,values_from = pct,names_prefix = "ethn_" #gender = merge_tsl_nha_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_" #frpl = merge_tsl_nha_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_" #ell = merge_tsl_nha_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_" #iep students = merge_tsl_nha_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_means %>% left_join (race_pct, by = "school" ) %>% left_join (ethn_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: 11 × 24
school n_students mean_boy_math mean_eoy_math attend_rate
<chr> <dbl> <dbl> <dbl> <dbl>
1 Bennett Venture Academy 204 196. 210. 0.87
2 Burton Glen Charter Acade… 372 198. 212. 0.9
3 Holly Park Academy 297 194. 206. 0.91
4 Laurus Academy 449 201. 213. 0.88
5 Linden Charter Academy 452 202. 213. 0.88
6 North Saginaw Charter Aca… 434 200. 210. 0.9
7 Oakside Prep Academy 542 197. 209. 0.9
8 Stambaugh Charter Academy 236 197. 211. 0.91
9 Walton Charter Academy 490 197. 211. 0.92
10 Windemere Park Charter Ac… 340 200. 215. 0.9
11 Winterfield Venture Acade… 188 197. 210. 0.87
# ℹ 19 more variables: `race_American Indian or Alaskan Native` <dbl>,
# race_Asian <dbl>, `race_Black or African American` <dbl>, race_White <dbl>,
# `race_Native Hawaiian or Pacific Islander` <dbl>,
# `ethn_American Indian or Alaskan Native` <dbl>, ethn_Asian <dbl>,
# `ethn_Black or African American` <dbl>, ethn_Hispanic <dbl>,
# ethn_White <dbl>, `ethn_Native Hawaiian or Pacific Islander` <dbl>,
# gender_Female <dbl>, gender_Male <dbl>, frpl_0 <dbl>, frpl_1 <dbl>, …
write.csv (school_summary, "tsl_nha_overall_district_data_summary_by_school.csv" , row.names = FALSE )
Treatment versus comparison : Export school-level means for BOY Math assessment data, number of students, percentages of students by race/ethnicity, gender, iep, attendance rate, and FRPL-status.
= merge_tsl_nha_test_info %>% group_by (treat)%>% summarise (n_students = n (),mean_boy_math = mean (boy_math_score, na.rm = TRUE ),mean_eoy_math = mean (eoy_math_score, na.rm = TRUE ),attend_rate = mean (attend_rate, na.rm = TRUE )# DEMOGRAPHICS # race = merge_tsl_nha_test_info %>% group_by (treat, race) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (treat) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = race,values_from = pct,names_prefix = "race_" #ethn BEFORE CLEANING = merge_tsl_nha_test_info %>% group_by (treat, ethn) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (treat) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = ethn,values_from = pct,names_prefix = "ethn_" #gender = merge_tsl_nha_test_info %>% group_by (treat, gender) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (treat) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = gender,values_from = pct,names_prefix = "gender_" #frpl = merge_tsl_nha_test_info %>% group_by (treat, frpl) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (treat) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = frpl,values_from = pct,names_prefix = "frpl_" #ell = merge_tsl_nha_test_info %>% group_by (treat, ell) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (treat) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = ell,values_from = pct,names_prefix = "ell_" #iep students = merge_tsl_nha_test_info %>% group_by (treat, iep) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (treat) %>% mutate (pct = (n / sum (n)) * 100 ) %>% select (- n) %>% pivot_wider (names_from = iep,values_from = pct,names_prefix = "iep_" # COMBINE summaries = treat_comp_means %>% left_join (race_pct, by = "treat" ) %>% left_join (ethn_pct, by = "treat" ) %>% left_join (gender_pct, by = "treat" ) %>% left_join (frpl_pct, by = "treat" ) %>% left_join (ell_pct, by = "treat" ) %>% left_join (iep_pct, by = "treat" )= treat_comp_summary %>% mutate (across (where (is.numeric), ~ round (.x, 2 )))print (treat_comp_summary)
# A tibble: 2 × 24
treat n_students mean_boy_math mean_eoy_math attend_rate
<dbl> <dbl> <dbl> <dbl> <dbl>
1 0 3438 198. 211. 0.9
2 1 566 199. 213. 0.91
# ℹ 19 more variables: `race_American Indian or Alaskan Native` <dbl>,
# race_Asian <dbl>, `race_Black or African American` <dbl>,
# `race_Native Hawaiian or Pacific Islander` <dbl>, race_White <dbl>,
# `ethn_American Indian or Alaskan Native` <dbl>, ethn_Asian <dbl>,
# `ethn_Black or African American` <dbl>, ethn_Hispanic <dbl>,
# `ethn_Native Hawaiian or Pacific Islander` <dbl>, ethn_White <dbl>,
# gender_Female <dbl>, gender_Male <dbl>, frpl_0 <dbl>, frpl_1 <dbl>, …
write.csv (treat_comp_summary, "tsl_nha_district_data_summary_by_treat.csv" , row.names = FALSE )
Attrition
Percent of students who did not take the EOY assessment, within treatment group.
= merge_tsl_nha_test_info %>% filter (treat == 1 ) %>% summarise (n_total = n (),# Math test missing n_missing_eoy_math = sum (is.na (eoy_math_score)),pct_missing_eoy_math = (n_missing_eoy_math / n_total) * 100 ,print (summary_attrition)
# A tibble: 1 × 3
n_total n_missing_eoy_math pct_missing_eoy_math
<int> <int> <dbl>
1 566 0 0
Usage Data
Num_weeks construction
Reformatted session_BOY_date given by TSL (start date of treatment), from DD/MM/YYYY → MM/DD/YYYY.
#transform all date vars as dates = merge_tsl_nha_test_info %>% mutate (session_BOY_date = as.Date (session_BOY_date, format = "%d/%m/%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" ), boy_math_date = as.Date (boy_math_date, format = "%m/%d/%Y" ),
Create num_weeks by taking the difference in time between session start date and EOY assessment date at the student level. Average implementation period was about 13 weeks.
= merge_tsl_nha_test_info %>% mutate (num_weeks = as.numeric (difftime (eoy_math_date, session_BOY_date, units = "weeks" ))#subtract 1 week for spring break $ num_weeks = merge_tsl_nha_test_info$ num_weeks - 1 mean (merge_tsl_nha_test_info$ num_weeks, na.rm = TRUE )
Tabulations
Count of students with any usage logged (defined as num_session >= 1) by school . Exported.
= merge_tsl_nha_test_info %>% mutate (any_usage = as.integer (num_session >= 1 ),any_usage = replace_na (any_usage, 0 ))= any_usage %>% group_by (school, any_usage) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (school) %>% mutate (pct = n / sum (n) * 100 ) %>% ungroup () %>% pivot_wider (names_from = any_usage,values_from = c (n, pct),values_fill = 0 ,names_glue = "{.value}_usage_{any_usage}" print (any_usage_by_school)
# A tibble: 11 × 5
school n_usage_0 n_usage_1 pct_usage_0 pct_usage_1
<chr> <int> <int> <dbl> <dbl>
1 Bennett Venture Academy 168 36 82.4 17.6
2 Burton Glen Charter Academy 346 26 93.0 6.99
3 Holly Park Academy 246 51 82.8 17.2
4 Laurus Academy 312 137 69.5 30.5
5 Linden Charter Academy 404 48 89.4 10.6
6 North Saginaw Charter Academy 404 30 93.1 6.91
7 Oakside Prep Academy 481 61 88.7 11.3
8 Stambaugh Charter Academy 201 35 85.2 14.8
9 Walton Charter Academy 432 58 88.2 11.8
10 Windemere Park Charter Academy 280 60 82.4 17.6
11 Winterfield Venture Academy 164 24 87.2 12.8
write.csv (any_usage_by_school, file = "tsl_nha_any_usage_by_school.csv" )
Count of students with any usage logged (defined as num_session >= 1) by grade, among treatment students . Exported.
= any_usage %>% filter (treat == 1 ) %>% group_by (grade, any_usage) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (grade) %>% mutate (pct = n / sum (n) * 100 ) %>% ungroup () %>% pivot_wider (names_from = any_usage,values_from = c (n, pct),values_fill = 0 ,names_glue = "{.value}_usage_{any_usage}" print (any_usage_by_grade_treat)
# A tibble: 6 × 3
grade n_usage_1 pct_usage_1
<dbl> <int> <dbl>
1 3 135 100
2 4 111 100
3 5 105 100
4 6 101 100
5 7 66 100
6 8 48 100
write.csv (any_usage_by_grade_treat, file = "tsl_nha_any_usage_by_grade_treat.csv" )
Count of students with any usage logged (defined as num_session => 1) by grade, among comparison students . Exported.
= any_usage %>% filter (treat == 0 ) %>% group_by (grade, any_usage) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (grade) %>% mutate (pct = n / sum (n) * 100 ) %>% ungroup () %>% pivot_wider (names_from = any_usage,values_from = c (n, pct),values_fill = 0 ,names_glue = "{.value}_usage_{any_usage}" print (any_usage_by_grade_comp)
# A tibble: 6 × 3
grade n_usage_0 pct_usage_0
<dbl> <int> <dbl>
1 3 637 100
2 4 602 100
3 5 583 100
4 6 537 100
5 7 539 100
6 8 540 100
write.csv (any_usage_by_grade_comp, file = "tsl_nha_any_usage_by_grade_comp.csv" )
Summary of usage variables, by school . Including Q1, Median, Mean, Q3, and SD. Exported.
= c ("n_co_confid" , "n_co_confid_same_imp" ,"perc_confid_same_imp" , "score_confid_same_imp" ,"enjoy_rate" ,"num_session" ,"num_min" , "num_weeks" ,"activ_partic" ,"n_lo_complete" )= merge_tsl_nha_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: 11 × 51
school n_co_confid_Q1 n_co_confid_median n_co_confid_mean n_co_confid_Q3
<chr> <dbl> <dbl> <dbl> <dbl>
1 Bennett Ve… 7 13 12.2 15.2
2 Burton Gle… 2 3 4.5 6
3 Holly Park… 1 1 2.13 2
4 Laurus Aca… 3 6 7 11
5 Linden Cha… 4 6 7.66 10
6 North Sagi… 2 2 3.09 3
7 Oakside Pr… 5 9 9.33 12
8 Stambaugh … 2 4 3.88 5
9 Walton Cha… 4 6 7.09 9
10 Windemere … 3 6 9.61 14
11 Winterfiel… 1 2 2.71 4
# ℹ 46 more variables: n_co_confid_sd <dbl>, n_co_confid_same_imp_Q1 <dbl>,
# n_co_confid_same_imp_median <dbl>, n_co_confid_same_imp_mean <dbl>,
# n_co_confid_same_imp_Q3 <dbl>, n_co_confid_same_imp_sd <dbl>,
# perc_confid_same_imp_Q1 <dbl>, perc_confid_same_imp_median <dbl>,
# perc_confid_same_imp_mean <dbl>, perc_confid_same_imp_Q3 <dbl>,
# perc_confid_same_imp_sd <dbl>, score_confid_same_imp_Q1 <dbl>,
# score_confid_same_imp_median <dbl>, score_confid_same_imp_mean <dbl>, …
write.csv (summary_usage_by_school, file = "tsl_nha_summary_usage_by_school.csv" )
Summary of usage variables, by grade, among treatment . Including Q1, Median, Mean, Q3, and SD. Exported.
= merge_tsl_nha_test_info %>% filter (treat == 1 ) %>% group_by (grade) %>% 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_grade_treat)
# A tibble: 6 × 51
grade n_co_confid_Q1 n_co_confid_median n_co_confid_mean n_co_confid_Q3
<dbl> <dbl> <dbl> <dbl> <dbl>
1 3 2 3 4.28 5
2 4 4 10 10.7 14.5
3 5 3 5 6.79 9
4 6 2 5 5.64 7.25
5 7 4 6 6.86 9
6 8 6.75 7 9.31 12.2
# ℹ 46 more variables: n_co_confid_sd <dbl>, n_co_confid_same_imp_Q1 <dbl>,
# n_co_confid_same_imp_median <dbl>, n_co_confid_same_imp_mean <dbl>,
# n_co_confid_same_imp_Q3 <dbl>, n_co_confid_same_imp_sd <dbl>,
# perc_confid_same_imp_Q1 <dbl>, perc_confid_same_imp_median <dbl>,
# perc_confid_same_imp_mean <dbl>, perc_confid_same_imp_Q3 <dbl>,
# perc_confid_same_imp_sd <dbl>, score_confid_same_imp_Q1 <dbl>,
# score_confid_same_imp_median <dbl>, score_confid_same_imp_mean <dbl>, …
write.csv (summary_usage_by_grade_treat, file = "tsl_nha_summary_usage_by_grade_treat.csv" )
Summary of usage variables, by grade, among comparison . Including Q1, Median, Mean, Q3, and SD. Exported. No comparison students had any usage, as expected.
= merge_tsl_nha_test_info %>% filter (treat == 0 ) %>% group_by (grade) %>% 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_grade_comp)
# A tibble: 6 × 51
grade n_co_confid_Q1 n_co_confid_median n_co_confid_mean n_co_confid_Q3
<dbl> <dbl> <dbl> <dbl> <dbl>
1 3 NA NA NaN NA
2 4 NA NA NaN NA
3 5 NA NA NaN NA
4 6 NA NA NaN NA
5 7 NA NA NaN NA
6 8 NA NA NaN NA
# ℹ 46 more variables: n_co_confid_sd <dbl>, n_co_confid_same_imp_Q1 <dbl>,
# n_co_confid_same_imp_median <dbl>, n_co_confid_same_imp_mean <dbl>,
# n_co_confid_same_imp_Q3 <dbl>, n_co_confid_same_imp_sd <dbl>,
# perc_confid_same_imp_Q1 <dbl>, perc_confid_same_imp_median <dbl>,
# perc_confid_same_imp_mean <dbl>, perc_confid_same_imp_Q3 <dbl>,
# perc_confid_same_imp_sd <dbl>, score_confid_same_imp_Q1 <dbl>,
# score_confid_same_imp_median <dbl>, score_confid_same_imp_mean <dbl>, …
write.csv (summary_usage_by_grade_comp, file = "tsl_nha_summary_usage_by_grade_comp.csv" )
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 = merge_tsl_nha_test_info %>% :: select (student_id, #1/0 indicator: boy and eoy math info present #start date of TSL/treat #export e2i with test indicators, all matched students from the merge, regardless of missing test info (all_tests == 1 or 0) write.csv (tsl_nha_e2i_usage_test_info, file = "tsl_nha_e2i_all_students.csv" , row.names = FALSE )
Inspect number of students with complete tests info
table (tsl_nha_e2i_usage_test_info$ boy_eoy_math)
Inspect number of students with complete tests info, by treatment.
%>% group_by (treat) %>% summarise (complete_test = sum (boy_eoy_math == 1 , na.rm = TRUE ),incomplete_test = sum (boy_eoy_math == 0 , na.rm = TRUE ),total = n (),pct_complete = complete_test / total * 100 ,pct_incomplete = incomplete_test / total * 100 ,.groups = "drop"
# A tibble: 2 × 6
treat complete_test incomplete_test total pct_complete pct_incomplete
<dbl> <int> <int> <int> <dbl> <dbl>
1 0 3238 200 3438 94.2 5.82
2 1 559 7 566 98.8 1.24
Save e2i file with only containing matched students from the merge with complete test info (all_tests means student has all BOY and EOY scores and dates).
#subset: keep only students with complete test info = tsl_nha_e2i_usage_test_info %>% filter (boy_eoy_math == 1 )print (tsl_nha_e2i_all_tests) # N=3846
# A tibble: 3,797 × 34
student_id teacher_id grade school_num boy_math_score boy_math_date
<chr> <dbl> <dbl> <dbl> <dbl> <date>
1 366259 19461 7 4 202 2025-09-03
2 430015 13501 8 4 218 2025-09-03
3 445701 4891 8 5 204 2025-09-29
4 461310 4416 7 2 170 2025-09-10
5 471757 13405 8 6 224 2025-09-24
6 472379 6179 8 7 209 2025-09-08
7 473083 23087 8 9 225 2025-09-23
8 473087 23087 8 9 204 2025-09-03
9 475569 23087 8 9 244 2025-08-28
10 475590 23087 8 9 202 2025-09-02
# ℹ 3,787 more rows
# ℹ 28 more variables: eoy_math_score <dbl>, eoy_math_date <date>, male <dbl>,
# iep <dbl>, ell <dbl>, frpl <dbl>, attend_rate <dbl>, treat <dbl>,
# race_AI <dbl>, race_A <dbl>, race_B <dbl>, race_NH <dbl>, race_W <dbl>,
# hisp_eth <dbl>, boy_eoy_math <dbl>, math_boy_missing_eoy_present <dbl>,
# tsl_acct_id <dbl>, session_BOY_date <date>, n_co_confid <dbl>,
# n_co_confid_same_imp <dbl>, perc_confid_same_imp <dbl>, …
Count of treatment students among the subset with complete test info.
table (tsl_nha_e2i_all_tests$ treat)
#export e2i only containing matched students from the merge with complete test info write.csv (tsl_nha_e2i_all_tests, file = "tsl_nha_e2i_complete_tests.csv" , row.names = FALSE )