Clean Data
Import
Packages selected for inspecting and cleaning ALTER-Math, Seminole 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. Observation : Comparing data columns to what was listed in the data request, the following variables were not received: Attendance rate, Num_session, TH_supplied, TH_opened, and Transfer (an important outcome variable) .
<- read_csv ("ALTER-Math_Data_Mathematica.csv" )
Rows: 1562 Columns: 33
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (5): Student_ID*, School_Name*, Teacher_ID*, Gender, Race/Ethnicity
dbl (25): Student_Grade*, Treatment*, PM2 _Score*, PM3_Score*, ELL, FRPL, N...
lgl (1): IEP
dttm (2): PM2_Date*, PM3_Date*
ℹ 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*"
[3] "School_Name*" "Teacher_ID*"
[5] "Treatment*" "PM2 _Score*"
[7] "PM2_Date*" "PM3_Score*"
[9] "PM3_Date*" "Gender"
[11] "IEP" "ELL"
[13] "FRPL" "Race/Ethnicity"
[15] "Num_minutes" "Hints_supplied"
[17] "Hints_opened" "Hints_engaged"
[19] "STT_used" "STT_revised"
[21] "Conceptual_understanding" "Procedural_fluency"
[23] "Prop_tutor_giving_explanation" "Prop_tutor_giving_instruction"
[25] "Prop_tutor_giving_answer" "Prop_tutor_polite_expressions"
[27] "Prop_tutor_praising_and_encouraging" "Prop_tutor_confusion"
[29] "Prop_tutor_confirmatory_feedback" "Prop_accepting_reject"
[31] "Prop_commanding" "Total_messages"
[33] "Total_conversations"
Change column headers for roster.
= roster %>% rename (student_id = "Student_ID*" ,teacher_id = "Teacher_ID*" ,grade = "Student_Grade*" , school = "School_Name*" , treat = "Treatment*" , moy_score = "PM2 _Score*" , moy_date = "PM2_Date*" , eoy_score = "PM3_Score*" , eoy_date = "PM3_Date*" , gender = "Gender" , iep = "IEP" , ell = "ELL" , frpl = "FRPL" , race = "Race/Ethnicity" ,num_min = "Num_minutes" , hint_supplied = "Hints_supplied" , hints_opened = "Hints_opened" , hints_engaged = "Hints_engaged" ,stt_used = "STT_used" , stt_revised = "STT_revised" , concept_under = "Conceptual_understanding" , procedural_flu = "Procedural_fluency" , prop_tutor_give_explan = "Prop_tutor_giving_explanation" ,prop_tutor_give_instruct = "Prop_tutor_giving_instruction" , prop_tutor_give_answer = "Prop_tutor_giving_answer" ,prop_tutor_polite_expres = "Prop_tutor_polite_expressions" , prop_tutor_praise_encour = "Prop_tutor_praising_and_encouraging" , prop_tutor_confusion = "Prop_tutor_confusion" ,prop_tutor_confirm_feed = "Prop_tutor_confirmatory_feedback" ,prop_accept_reject = "Prop_accepting_reject" , prop_command = "Prop_commanding" , total_msg = "Total_messages" ,total_convers = "Total_conversations" colnames (roster)
[1] "student_id" "grade"
[3] "school" "teacher_id"
[5] "treat" "moy_score"
[7] "moy_date" "eoy_score"
[9] "eoy_date" "gender"
[11] "iep" "ell"
[13] "frpl" "race"
[15] "num_min" "hint_supplied"
[17] "hints_opened" "hints_engaged"
[19] "stt_used" "stt_revised"
[21] "concept_under" "procedural_flu"
[23] "prop_tutor_give_explan" "prop_tutor_give_instruct"
[25] "prop_tutor_give_answer" "prop_tutor_polite_expres"
[27] "prop_tutor_praise_encour" "prop_tutor_confusion"
[29] "prop_tutor_confirm_feed" "prop_accept_reject"
[31] "prop_command" "total_msg"
[33] "total_convers"
Inspect school names.
Casselberry Elementary School English Estates Elementary School
86 48
Goldsboro Elementary School Greenwood Lakes Middle School
48 167
Highlands Elementary School Keeth Elementary School
53 47
Lake Mary Elementary School Midway Elementary School
112 143
Millennium Middle School Partin Elementary School
194 35
Pine Crest Elementary School Rainbow Elementary School
33 125
Red Bug Elementary School Sterling Park Elementary School
67 44
Teague Middle School Wekiva Elementary School
241 55
Wilson Elementary School
64
Create school_num for each school.
= roster %>% mutate (school_num = recode (school,"Casselberry Elementary School" = 1 ,"English Estates Elementary School" = 2 ,"Goldsboro Elementary School" = 3 , "Greenwood Lakes Middle School" = 4 , "Highlands Elementary School" = 5 , "Keeth Elementary School" = 6 , "Lake Mary Elementary School" = 7 ,"Midway Elementary School" = 8 , "Millennium Middle School" = 9 , "Partin Elementary School" = 10 , "Pine Crest Elementary School" = 11 , "Rainbow Elementary School" = 12 , "Red Bug Elementary School" = 13 ,"Sterling Park Elementary School" = 14 ,"Teague Middle School" = 15 , "Wekiva Elementary School" = 16 , "Wilson Elementary School" = 17 table (roster$ school_num)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
86 48 48 167 53 47 112 143 194 35 33 125 67 44 241 55 64
Inspect duplicates
Identify duplicate student records within roster file. No duplicates identified.
# 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 <chr>, n <int>
Missing Data Inspections
Percentage missing for each variable. Observations: IEP status is missing for all students. Many treatment-related variables are missing, as expected (repeating proportion of 52.11%); this is a merged file with treated and non-treated students. All students appears to have complete test data and demographics, including teacher ID.
= roster %>% 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: 34 × 2
variable percent_missing
<chr> <dbl>
1 iep 100
2 num_min 52.1
3 hint_supplied 52.1
4 hints_opened 52.1
5 hints_engaged 52.1
6 stt_used 52.1
7 stt_revised 52.1
8 concept_under 52.1
9 procedural_flu 52.1
10 prop_tutor_give_explan 52.1
# ℹ 24 more rows
write.csv (missing_summary, file = "alter_sem_missing_summary.csv" , row.names = FALSE )
Percentage missing for each variable, by treatment . Exported.
= roster %>% group_by (treat) %>% summarise (across (everything (),~ mean (is.na (.)) * 100 ,.names = "pct_miss_{.col}" ),.groups = "drop" ) %>% pivot_longer (cols = - treat,names_to = "variable" ,values_to = "pct_missing" %>% mutate (variable = sub ("^pct_miss_" , "" , variable))print (missing_summary_by_treat)
# A tibble: 66 × 3
treat variable pct_missing
<dbl> <chr> <dbl>
1 0 student_id 0
2 0 grade 0
3 0 school 0
4 0 teacher_id 0
5 0 moy_score 0
6 0 moy_date 0
7 0 eoy_score 0
8 0 eoy_date 0
9 0 gender 0
10 0 iep 100
# ℹ 56 more rows
#export write.csv (missing_summary_by_treat, file = "alter_sem_pct_missing_vars_by_treat.csv" , row.names = FALSE )
Percentage of student who have both MOY and EOY data . All students across all schools and all treatment groups have all test scores in. No need to create dummies for missing test info.
#indicator for having BOTH MOY and EOY scores = roster %>% mutate (all_math = ! is.na (moy_score) & ! is.na (eoy_score)#pct across schools = roster %>% summarise (total_students = n (),students_with_all_tests = sum (all_math),percent_with_all_tests = (students_with_all_tests / total_students) * 100 %>% mutate (percent_with_all_tests = round (percent_with_all_tests, 2 ))print (overall_pct)
# A tibble: 1 × 3
total_students students_with_all_tests percent_with_all_tests
<int> <int> <dbl>
1 1562 1562 100
Percentage of students who have both MOY and EOY data by treatment
= roster %>% group_by (treat) %>% summarise (total_students = n (),students_with_all_tests = sum (all_math),percent_with_all_tests = (students_with_all_tests / total_students) * 100 %>% mutate (percent_with_all_tests = round (percent_with_all_tests, 2 ))print (by_treat_pct)
# A tibble: 2 × 4
treat total_students students_with_all_tests percent_with_all_tests
<dbl> <int> <int> <dbl>
1 0 814 814 100
2 1 748 748 100
Data Summary
District Data
Count of treatment students versus non-treated student.
Count of treatment and comparison students per teacher (N = 30). Exported. Clean assignment of treatment per teacher (100% or 0%).
= roster %>% group_by (teacher_id) %>% summarise (treatment_students = sum (treat == 1 ),comparison_students = sum (treat == 0 ),total_students = n (),pct_treatment = treatment_students / total_students * 100 ,pct_comparison = comparison_students / total_students * 100 ,.groups = "drop" print (teacher_summary)
# A tibble: 30 × 6
teacher_id treatment_students comparison_students total_students
<chr> <int> <int> <int>
1 018fe280-1975-72e0-bd3… 45 0 45
2 018fe280-1fc6-7093-a30… 0 33 33
3 018fe280-6a11-739e-993… 72 0 72
4 018fe281-2b21-7281-961… 0 64 64
5 018fe281-9bc9-71f3-981… 53 0 53
6 018fe281-a6d5-7199-8e0… 0 46 46
7 018fe281-a902-71c3-834… 0 29 29
8 018fe281-abcf-737a-a86… 50 0 50
9 018fe282-3eec-71c0-b06… 56 0 56
10 018fe282-5542-7222-a05… 5 0 5
# ℹ 20 more rows
# ℹ 2 more variables: pct_treatment <dbl>, pct_comparison <dbl>
write.csv (teacher_summary, file = "alter_sem_teacher_summary.csv" )
Generate school-level means for MOY 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 = roster %>% mutate (across (c (moy_score, #transform all scores to numeric value = roster %>% group_by (school)%>% summarise (n_students = n (),mean_moy_math = mean (moy_score, na.rm = TRUE ),mean_eoy_math = mean (eoy_score, na.rm = TRUE )# DEMOGRAPHICS # race & ethnicity = roster %>% 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_" #gender = roster %>% 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 = roster %>% 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 = roster %>% 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 = roster %>% 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: 17 × 18
school n_students mean_moy_math mean_eoy_math race_Asian race_Black
<chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Casselberry Ele… 86 237. 252. 10.5 1.16
2 English Estates… 48 235. 255. 18.8 14.6
3 Goldsboro Eleme… 48 238. 250. 12.5 2.08
4 Greenwood Lakes… 167 239. 248. 2.99 13.2
5 Highlands Eleme… 53 236. 255. 30.2 11.3
6 Keeth Elementar… 47 237. 247. 25.5 8.51
7 Lake Mary Eleme… 112 233. 251. 6.25 6.25
8 Midway Elementa… 143 237. 250 18.9 18.9
9 Millennium Midd… 194 239. 244. 3.61 23.7
10 Partin Elementa… 35 236. 249. 22.9 8.57
11 Pine Crest Elem… 33 239. 248. 12.1 3.03
12 Rainbow Element… 125 240. 254. 1.6 10.4
13 Red Bug Element… 67 236. 247. 5.97 NA
14 Sterling Park E… 44 234. 253. 4.55 25
15 Teague Middle S… 241 236. 239. 4.15 18.7
16 Wekiva Elementa… 55 238. 257. 30.9 3.64
17 Wilson Elementa… 64 237. 247. 3.12 3.12
# ℹ 12 more variables: race_Hispanic <dbl>,
# `race_Native Hawaiian/Pacific Islander` <dbl>,
# `race_Two or More Races` <dbl>, race_White <dbl>,
# `race_American Indian/Alaska Native` <dbl>, gender_Female <dbl>,
# gender_Male <dbl>, frpl_0 <dbl>, frpl_1 <dbl>, ell_0 <dbl>, ell_1 <dbl>,
# iep_NA <dbl>
write.csv (school_summary, "alter_sem_overall_district_data_summary_by_school.csv" , row.names = FALSE )
Treatment versus comparison : Export means MOY assessment data, number of students, plus distribution across demographics.
= roster %>% group_by (treat)%>% summarise (n_students = n (),mean_moy_math = mean (moy_score, na.rm = TRUE ),mean_eoy_math = mean (eoy_score, na.rm = TRUE )# DEMOGRAPHICS # race & ethnicity = roster %>% 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_" #gender = roster %>% 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 = roster %>% 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 = roster %>% 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 = roster %>% 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_means %>% left_join (race_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_summary %>% mutate (across (where (is.numeric), ~ round (.x, 2 )))print (treat_summary)
# A tibble: 2 × 18
treat n_students mean_moy_math mean_eoy_math race_American Indian…¹ race_Asian
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 0 814 237. 245. 0.86 9.21
2 1 748 237. 251. 0.53 9.63
# ℹ abbreviated name: ¹`race_American Indian/Alaska Native`
# ℹ 12 more variables: race_Black <dbl>, race_Hispanic <dbl>,
# `race_Two or More Races` <dbl>, race_White <dbl>,
# `race_Native Hawaiian/Pacific Islander` <dbl>, gender_Female <dbl>,
# gender_Male <dbl>, frpl_0 <dbl>, frpl_1 <dbl>, ell_0 <dbl>, ell_1 <dbl>,
# iep_NA <dbl>
write.csv (treat_summary, "alter_sem_district_data_summary_by_treat.csv" , row.names = FALSE )
Attrition
Percent of students who did not take the EOY assessment, within treatment group.
= roster %>% filter (treat == 1 ) %>% summarise (n_total = n (),# Math test missing n_missing_eoy_math = sum (is.na (eoy_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 748 0 0
Usage Data
Count of students with any usage logged (defined as num_min => 1) by treatment . Exported.
= roster %>% mutate (any_usage = as.integer (num_min >= 1 ),any_usage = replace_na (any_usage, 0 ))= any_usage %>% group_by (treat, any_usage) %>% summarise (n = n (), .groups = "drop" ) %>% group_by (treat) %>% 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_treat)
# A tibble: 2 × 5
treat n_usage_0 n_usage_1 pct_usage_0 pct_usage_1
<dbl> <int> <int> <dbl> <dbl>
1 0 814 0 100 0
2 1 0 748 0 100
write.csv (any_usage_by_treat, file = "alter_sem_any_usage_by_school.csv" )
Summary of usage variables, by treatment . Including Q1, Median, Mean, Q3, and SD. Exported.
= c ("num_min" ,"hint_supplied" , "hints_opened" , "hints_engaged" ,"stt_used" , "stt_revised" , "concept_under" , "procedural_flu" , "prop_tutor_give_explan" , "prop_tutor_give_instruct" , "prop_tutor_give_answer" ,"prop_tutor_polite_expres" , "prop_tutor_praise_encour" , "prop_tutor_confusion" ,"prop_tutor_confirm_feed" , "prop_accept_reject" , "prop_command" , "total_msg" , "total_convers" )= roster %>% group_by (treat) %>% 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_treat)
# A tibble: 2 × 96
treat num_min_Q1 num_min_median num_min_mean num_min_Q3 num_min_sd
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 0 NA NA NaN NA NA
2 1 216 313 330. 480. 161.
# ℹ 90 more variables: hint_supplied_Q1 <dbl>, hint_supplied_median <dbl>,
# hint_supplied_mean <dbl>, hint_supplied_Q3 <dbl>, hint_supplied_sd <dbl>,
# hints_opened_Q1 <dbl>, hints_opened_median <dbl>, hints_opened_mean <dbl>,
# hints_opened_Q3 <dbl>, hints_opened_sd <dbl>, hints_engaged_Q1 <dbl>,
# hints_engaged_median <dbl>, hints_engaged_mean <dbl>,
# hints_engaged_Q3 <dbl>, hints_engaged_sd <dbl>, stt_used_Q1 <dbl>,
# stt_used_median <dbl>, stt_used_mean <dbl>, stt_used_Q3 <dbl>, …
write.csv (summary_usage_by_treat, file = "alter_sem_summary_usage_by_treat.csv" )
Export files for e2i Coach
Save e2i file with all students but only select the columns needed for analysis.
#select columns ready for e2i = roster %>% :: select (student_id, #export e2i write.csv (roster_e2i, file = "alter_sem_all_students_complete_tests.csv" , row.names = FALSE )
Since all student have complete test info, there’s no need to save e2i file with only containing students with complete test info.