S10 Data Cleaning

setwd("/Users/isaiahmireles/Desktop/Misconceptions")

# Midterm : 
# F23 
F23_M_VA <- 
  read.csv("F23_Midterm_Version_Set_Scores/Midterm_Version_A_scores.csv")
F23_M_VB <- 
  read.csv("F23_Midterm_Version_Set_Scores/Midterm_Version_B_scores.csv")

# W24 
W24_M_VA <- 
  read.csv("W24_Midterm_Version_Set_Scores/Midterm_Version_A_scores.csv")
W24_M_VB <- 
  read.csv("W24_Midterm_Version_Set_Scores/Midterm_Version_B_scores.csv")

# S24 
S24_M <- 
  read.csv("24S-STATS-10-LEC-4_Midterm/S24_Midterm_student_responses copy.csv")

# ------------------------------------------------------------------------------- #

# Final :
# F23
F23_F_VA <- 
  read.csv("F23_Final_Exam_Version_Set_Scores/Final_Exam_Version_A_scores.csv")
F23_F_VB <- 
  read.csv("F23_Final_Exam_Version_Set_Scores/Final_Exam_Version_B_scores.csv")

# W24 
W24_F_VA <- 
  read.csv("W24_Final_Exam_Version_Set_Scores/Final_Exam_Version_A_scores.csv")
W24_F_VB <- 
  read.csv("W24_Final_Exam_Version_Set_Scores/Final_Exam_Version_B_scores.csv")

# S24 
S24_F_VA <- 
  read.csv("S24_Final_Exam_Version_Set_Scores/Final_Exam_Version_A_scores.csv")
S24_F_VB <- 
  read.csv("S24_Final_Exam_Version_Set_Scores/Final_Exam_Version_B_scores.csv")

1 Fall Cohort

1.1 Midterm

Count how many times a user occurs multiple times

library(tidyverse)
bind_rows(
  F23_M_VA |> mutate(dataset = "A"),
  F23_M_VB |> mutate(dataset = "B")
) |>
  group_by(First.Name, Last.Name) |>
  summarise(
    times = n(),
    datasets = paste(unique(dataset), collapse = ", "),
    .groups = "drop"
  ) |>
  filter(times > 1) 


bind_rows(
  F23_M_VA |> mutate(dataset = "A"),
  F23_M_VB |> mutate(dataset = "B")
) |>
  group_by(First.Name, Last.Name) |>
  summarise(
    times = n(),
    datasets = paste(unique(dataset), collapse = ", "),
    .groups = "drop"
  ) |>
  filter(times > 1) |>
  count(datasets)

• unfortunately, people are appearing for both version A, B

Perhaps its only because non-graded are repeated in both?

bind_rows(
  F23_M_VA |> filter(Status == "Graded") |> mutate(dataset = "A"),
  F23_M_VB |> filter(Status == "Graded") |> mutate(dataset = "B")
) |>
  group_by(First.Name, Last.Name) |>
  summarise(
    times = n(),
    datasets = paste(unique(dataset), collapse = ", "),
    .groups = "drop"
  ) |>
  filter(times > 1)

• yes, as we can see only unidentified student, Graded students appear in both – which makes sense given its non-identifiable

1.1.1 Combine into F23 Midterm Versions (A,B)

F23_M <-
  bind_rows(
  F23_M_VA |> filter(Status == "Graded") |> mutate(version = "A"),
  F23_M_VB |> filter(Status == "Graded") |> mutate(version = "B")
)

1.2 irregular SID Func

check_sid <- function(df) {
  
  # Check whether SID column exists
  if (is.null(df$SID)) {
    return("SID column is NULL or does not exist")
  }
  
  df |>
    mutate(idx = row_number()) |>
    filter(
      is.na(SID) |
      trimws(as.character(SID)) == "" |
      !grepl("^[0-9]+$", as.character(SID))
    ) |>
    select(idx, SID)
}

check_sid(F23_M) |> count(SID)

• irregular SID are all NA

F23_M |> filter(is.na(SID)) |> select(SID, First.Name, Last.Name) |> count(First.Name)

• notice all the NA SID are all unidentified students, that makes sense.

F23_M <- F23_M |> mutate(unidentified = First.Name == "unidentified" & Last.Name == "student") 

• here i added a binary indicator

1.3 Anonymize & Split id./non-id Students

F23_M <- 
  F23_M |>
  mutate(
    student_id = if_else(
      unidentified,
      paste0("unidentified_", cumsum(unidentified)),
      paste0(
        "student_",
        match(SID, unique(SID[!is.na(SID)]))
      )
    )
  )

1.3.1 Create dictionary :

F23_M_dictionary <- F23_M |>
  select(
    student_id,
    SID,
    Submission.ID,
    First.Name,
    Last.Name,
    Email
  )

• the purpose is so we can connect students across exams anonymously

1.4 Find Duplicates Func

find_duplicates <- function(df) {
  df |>
    mutate(idx = row_number()) |>
    group_by(SID) |> # grp by student id 
    filter(n() > 1) |>
    ungroup() |> 
    pull(idx) # make vector
}

F23_M[find_duplicates(F23_M),] |> count(SID)

• meaning the only repeated SID are NA – so those are the unidentified students

1.5 Anonymize

F23_M <- 
  F23_M |>
  select(
    student_id,
    version,
    Total.Score,
    Max.Points,
    everything(),
    -Status,
    -SID,
    -Submission.Count,
    -Submission.ID,
    -First.Name,
    -Last.Name,
    -Email,
    -Lateness..H.M.S.,
    -View.Count,
    -Submission.Time
  )

F23_M <- F23_M |> mutate(exam = "Midterm", term = "F23")

rm(list=c("F23_M_VA", "F23_M_VB"))

F23_M <- 
  F23_M |> select(unidentified, term, exam, version, student_id, Sections, Total.Score, everything())

1.6 Final

bind_rows(
  F23_F_VA |> filter(Status == "Graded") |> mutate(dataset = "A"),
  F23_F_VB |> filter(Status == "Graded") |> mutate(dataset = "B")
) |>
  group_by(First.Name, Last.Name) |>
  summarise(
    times = n(),
    datasets = paste(unique(dataset), collapse = ", "),
    .groups = "drop"
  ) |>
  filter(times > 1)

• in other words, graded students only appear once in each data set

1.6.1 Combine Versions

F23_F <- 
  bind_rows(
  F23_F_VA |> filter(Status == "Graded") |> mutate(version = "A"),
  F23_F_VB |> filter(Status == "Graded") |> mutate(version = "B")
)

1.6.2 Check SID

check_sid(F23_F)

• nothing weird going on

1.6.3 unidentified feature

F23_F |> 
  mutate(unidentified = First.Name == "unidentified" & Last.Name == "student") |> count(unidentified)

F23_F <- 
  F23_F |> 
  mutate(unidentified = First.Name == "unidentified" & Last.Name == "student")

• there arent any unidentified students

F23_F <- F23_F |> mutate(exam = "Final", term = "F23")

1.6.4 student_id

F23_M |> count(unidentified)

• there can be at most 297 students whom took the final

1.7 Connect student ids

F23_F <- F23_F |>
  left_join(
    F23_M_dictionary |>
      filter(!is.na(SID)) |>
      distinct(SID, student_id),
    by = "SID"
  )

F23_F |>
  count(is.na(student_id))

# F23_F |> filter(is.na(student_id)) 
# F23_F |> filter(is.na(student_id)) |> select(Total.Score)

• Notice 1 student did not appear in the identifiable midterm dictionary

1.8 Provide std with student id

max_id <- F23_M_dictionary |>
  filter(grepl("^student_", student_id)) |>
  pull(student_id) |>
  readr::parse_number() |>
  max()

F23_F <- F23_F |>
  mutate(
    student_id = if_else(
      is.na(student_id),
      paste0("student_", max_id + 1),
      student_id
    )
  )

1.9 Anonymize

F23_F <- 
  F23_F |>
  select(
    student_id,
    version,
    Total.Score,
    Max.Points,
    everything(),
    -Status,
    -SID,
    -Submission.Count,
    -Submission.ID,
    -First.Name,
    -Last.Name,
    -Email,
    -Lateness..H.M.S.,
    -View.Count,
    -Submission.Time
  )

1.10 Combine into one cohort

# reconfigure questions
F23_M <- F23_M |>
  rename_with(
    ~ paste0(
      "Q",
      sub("^X([0-9]+).*", "\\1", .x),
      "_F23M"
    ),
    matches("^X[0-9]+\\.\\.Question")
  )

F23_F <- 
  F23_F |>
  rename_with(
    ~ paste0(
      "Q",
      sub("^X([0-9]+).*", "\\1", .x),
      "_F23F"
    ),
    matches("^X[0-9]+\\.\\.Question")
  )

F23_F <- F23_F |>
  select(
    unidentified,
    term,
    exam,
    version,
    student_id,
    Sections,
    Total.Score,
    Max.Points,
    everything()
  )

# actually just change Questions to Qx :
F23_M <- F23_M |>
  rename_with(
    ~ sub("_F23M$", "", .x),
    starts_with("Q")
  )

F23_F <- F23_F |>
  rename_with(
    ~ sub("_F23F$", "", .x),
    starts_with("Q")
  )

2 F23 Cohort

F23 <- bind_rows(F23_M, F23_F)

So the final has 31Qs and Midterm has 34, so im checking what those values become :

F23 |> filter(exam=="Final") |> select(Q32:Q34) |> head()

rm(F23_F, F23_F_VA, F23_F_VB, F23_M)
rm(max_id)

3 Winter Cohort