1 Preface

Purpose :

  • Clean, Join & Report Missingness

  • Define : What is my Data

Deliverable(s) :

  • Create Docu. for data (data_dictionary.csv,README.md)

  • Create Dat. (cohorts.csv)

  • Create Report (S10_Data_Cleaning.Rmd)

Meaning :

  • data_dictionary.csv : Defines each variable in the final dataset, including its meaning, type, coding, source, and missingness.

  • README.md : Summarizes the dataset, its provenance, structure, limitations, access restrictions, and how to use the project files.

  • cohorts.csv : Final cleaned and de-identified analytic dataset used for analysis.

  • S10_Data_Cleaning.Rmd : Reproducible record of how the raw data were cleaned, linked, anonymized, validated, and converted into cohorts.csv.

Source(s) of Guidance :

Data Privacy Statement :

  • here we require that the data be anonymized – ie. shared data may not be linked to particular student. Direct & Indirect identifiers must be removed.

1.1 Data Sets

ls() |> as.data.frame()

  • Quarter : Fall (F), Winter (W), Spring (S)

  • Exam : Midterm (M), Final (F)

  • Version (VA, VB)

For example :

  • “F_F_VA” means Version A (VA) of the Fall (F) Final (F).
  • “F_M_VA” means Version A (VA) of the Fall (F) Midterm (M).

1.2 Anonymization

F_M_VA[,1:16] |> colnames()
##  [1] "First.Name"               "Last.Name"               
##  [3] "SID"                      "Email"                   
##  [5] "Sections"                 "Total.Score"             
##  [7] "Max.Points"               "Status"                  
##  [9] "Submission.ID"            "Submission.Time"         
## [11] "Lateness..H.M.S."         "View.Count"              
## [13] "Submission.Count"         "X1..Question.1..1.0.pts."
## [15] "X2..Question.2..1.0.pts." "X3..Question.3..1.0.pts."
  • Data here contains uniquely identifiable information – both directly ( First.Name, Last.Name, Submission.ID) & indirectly (Sections, Email). Therefore we are to strip that information to generate properly anonymized Data.

1.3 Abbreviations

  • ct : Count

  • inf. : Information

  • ID : Identification

  • rw : Row(s)

  • obs. : Observation(s)

  • func. : Function(s)

  • pct : Percent

  • num. : Number(s)

  • mult. : Multiple(s)

library(tidyverse)

2 Data Cleaning

2.1 Ideal Scenarios

n_rw <-
  F_F_VA |> nrow() +
  F_F_VB |> nrow() + 
  W_F_VA |> nrow() + 
  W_F_VB |> nrow() + 
  S_F_VA |> nrow() + 
  S_F_VB |> nrow()  

paste0("Sum of all Rows = ", n_rw, " rows")
## [1] "Sum of all Rows = 1520 rows"
  • ideally each row is a student but realistically not.
    • At most we may have 1520 Students

2.2 Colnames

data_sets <- ls() |>
  keep(~ is.data.frame(get(.x)))

?keep
# check how keep works
x <- 1:10
# purrr::keep()
# x |> keep(\(x){x>=5})
# [1]  5  6  7  8  9 10
# keeps all those w/ cond. 
rm(x)
colnames_all <- set_names(
  map(
    data_sets,
    ~ colnames(get(.x))
  ),
  data_sets
)
unique_col <- 
  colnames_all |> unlist(use.names = FALSE) |> unique()
unique_col |> as.data.frame()

3 Fall Cohort/Class

3.1 Midterm

3.1.1 Direct ID inf.

# Comb. Versions
F_M <-
  bind_rows(
  F_M_VA |> mutate(version="A", recency = 3),
  F_M_VB |> mutate(version="B", recency = 3)
)
paste0(F_M |> nrow(), " rw for Fall Midterm")
## [1] "818 rw for Fall Midterm"
paste0("Therefore ", round(F_M |> nrow()/n_rw, 2), "% (most) obs. are from Fall")
## [1] "Therefore 0.54% (most) obs. are from Fall"

3.1.1.1 First.Name, Last.Name

all(F_M$First.Name == toupper(F_M$First.Name))
## [1] FALSE
all(F_M$Last.Name == toupper(F_M$Last.Name))
## [1] FALSE
  • notice not all are upper case.
# make all upper
F_M <-
  F_M |> mutate(
  First.Name = toupper(First.Name),
  Last.Name = toupper(Last.Name)
  )

# toupper("a")
# rw id
F_M <- F_M |> mutate(rw_idx = row_number()) 
all(F_M$First.Name == toupper(F_M$First.Name))
## [1] TRUE
all(F_M$Last.Name == toupper(F_M$Last.Name))
## [1] TRUE
3.1.1.1.1 First.Name, Last.Name pairs ct
F_M |> distinct(First.Name, Last.Name) |> nrow()
## [1] 301
F_M |> nrow()
## [1] 818
  • Despite 818 rows, only 301 unique First.Name, Last.Name pairs were observed.
  • Meaning we have the same person(s) repeated mult. times
  • To further inspect this, we must anonymize our std. of identifiable features.

3.1.2 key_tbl_F_M

# add unique std. id. for each unique FL-Name
key_tbl_F_M <-
  F_M |>
  distinct(First.Name, Last.Name) |>
  mutate(std_id = paste0("student_", row_number()))

# Join std_id
F_M <-
  F_M |>
  left_join(
    key_tbl_F_M,
    by = c("First.Name", "Last.Name")
  )
F_M |> 
  group_by(std_id) |> 
  summarize(ct = n()) |> 
  arrange(desc(ct))
  • Most std. appear twice (2), one appears 4 times (student_110), one 216 times (student_40)
F_M |> filter(std_id=="student_40") 
F_M |> filter(std_id=="student_40") |> count(Status)
round(F_M |> filter(std_id=="student_40") |> nrow() /nrow(F_M), 2)
## [1] 0.26

  • Notice some std. are so called UNIDENTIFIED STUDENT (26%)

  • All students were graded

  • These students scores cannot be connected across exams