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 Ideal Scenarios

n_rw <-
  F23_F_VA |> nrow() + F23_F_VB |> nrow() + W24_F_VA |> nrow() + W24_F_VB |> nrow() + S24_F_VA |> nrow() + S24_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

Preface :

for the data cleaning process we will filter for Graded observations ( Status=="Graded" )

2 Fall Cohort/Class

2.1 Midterm

F23_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."

library(tidyverse)
print("F23_M_VA :")

## [1] "F23_M_VA :"

F23_M_VA |> count(Status)

print("F23_M_VB :")

## [1] "F23_M_VB :"

F23_M_VB |> count(Status)

As we can see in each data set theres about 150+ ungraded students
There are about 250 graded students in each
- ~ 500 Combined

n_F23_M_VA <- F23_M_VA |> nrow(); n_F23_M_VA

## [1] 420

n_F23_M_VB <- F23_M_VB |> nrow(); n_F23_M_VB

## [1] 398

About 400 rows for each

F23_M_VA |> distinct(First.Name, Last.Name) |> nrow()

## [1] 301

F23_M_VA$SID |> unique() |> length()

## [1] 301

paste0("n_F23_M_VA = ", n_F23_M_VA)

## [1] "n_F23_M_VA = 420"

F23_M_VB |> distinct(First.Name, Last.Name) |> nrow()

## [1] 301

F23_M_VB$SID |> unique() |> length()

## [1] 301

paste0("n_F23_M_VB = ", n_F23_M_VB)

## [1] "n_F23_M_VB = 398"

As we can see for both VA, B there are equal amt SID and (First, Last) pairs (301)

loss <- c(n_F23_M_VA, n_F23_M_VB) - 301
names(loss) <- c("n_F23_M_VA", "n_F23_M_VB")
paste0(names(loss)," loss = ", loss)

## [1] "n_F23_M_VA loss = 119" "n_F23_M_VB loss = 97"

notice we lost many observations in this
- Each ~100

F23_M_VA |>
  group_by(First.Name, Last.Name) |>
  summarize(n_sid = n_distinct(SID)) |>
  filter(n_sid != 1)

F23_M_VB |>
  group_by(First.Name, Last.Name) |>
  summarize(n_sid = n_distinct(SID)) |>
  filter(n_sid != 1)

Each (First, Last) pair maps to one unique SID.

Do students only appear in their respective versions?

bind_rows(
  F23_M_VA |> mutate(version = "A"),
  F23_M_VB |> mutate(version = "B")
) |>
  ungroup() |> 
  group_by(First.Name, Last.Name) |>
  summarise(
    times = n(),
    version = paste(unique(version), collapse = ", "),
    .groups = "drop"
  ) |>
  filter(times > 1) |>
  arrange(desc(times)) |>
  mutate(
    id = row_number(),
    unidentified = First.Name == "unidentified" & Last.Name == "student",
    First.Name = if_else(
      unidentified,
      "unidentified",
      paste0("identity_", id - 1)
    ),
    Last.Name = if_else(
      unidentified,
      "student",
      "hidden"
    )
  ) |>
  select(-id, -unidentified)

unfortunately, people are appearing for both version A, B
- Most of these types of obs. are unidentified student ( 216 Obs. )
- Some students appear in both data ( 300 Obs. )

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

2.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")
)

2.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

2.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)]))
      )
    )
  )

2.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

2.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

2.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())

2.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

2.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")
)

2.6.2 Check SID

check_sid(F23_F)

nothing weird going on

2.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")

2.6.4 student_id

F23_M |> count(unidentified)

there can be at most 297 students whom took the final

2.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

2.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
    )
  )

2.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
  )

2.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")
  )

3 F23 Class

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)

4 Winter Cohort/Class

4.1 Midterm

W24_M_VA |> count(Status == "Graded")

W24_M_VB |> count(Status == "Graded")

W24_M_VA <- W24_M_VA |> 
  filter(Status == "Graded") |> 
  mutate(version = "A", exam = "Midterm", term = "W24")

W24_M_VB <- W24_M_VB |> 
  filter(Status == "Graded") |> 
  mutate(version = "B", exam = "Midterm", term = "W24")

W24_M <- bind_rows(W24_M_VA, W24_M_VB)

W24_M <- W24_M |> select(-Status)

check_sid(W24_M)

nrow(W24_M)

## [1] 297

W24_M |> count(SID) |> nrow()

## [1] 297

beautiful

W24_M <-
  W24_M |>
  mutate(
    student_id = paste0(
      "student_",
      match(SID, unique(SID))
    )
  )

4.1.1 Dictionary

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

find_duplicates(W24_M)

## integer(0)

find_duplicates

## function (df) 
## {
##     pull(ungroup(filter(group_by(mutate(df, idx = row_number()), 
##         SID), n() > 1)), idx)
## }
## <bytecode: 0x10944aae0>

life is good — no duplicate SID

4.1.1.1 Anonymize

W24_M <-
  W24_M |> 
  select(student_id, everything(),
                -c(
                  SID,
                  Submission.ID,
                  First.Name,
                  Last.Name,
                  Email,
                  Lateness..H.M.S.,
                  Submission.Time,
                  View.Count, 
                  Submission.Count
                  ))

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

W24_M

rm(list=c("W24_M_VA", "W24_M_VB"))

4.2 Final

W24_F_VA |> count(Status)

W24_F_VB |> count(Status)

W24_F_VA <- 
  W24_F_VA |> 
  filter(Status == "Graded") |> 
  mutate(version = "A", exam = "Final", term = "W24")

W24_F_VB <-
  W24_F_VB |> 
  filter(Status == "Graded") |> 
  mutate(version = "B", exam = "Final", term = "W24")

W24_F <- bind_rows(W24_F_VA, W24_F_VB)

rm(list=c("W24_F_VA", "W24_F_VB"))

W24_F |> check_sid()

perfect!

find_duplicates(W24_F)

## integer(0)

perfect!

4.2.1 Combine Cohort

W24_F <-
  W24_F |>
  left_join(
    W24_M_dictionary |>
      distinct(SID, student_id),
    by = "SID"
  )

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

notice 6 students didnt take the final

4.2.1.1 Anonymize

W24_F <-
  W24_F |>
  select(
    student_id,
    everything(),
    -c(
      Status,
      SID,
      Submission.ID,
      First.Name,
      Last.Name,
      Email,
      Lateness..H.M.S.,
      Submission.Time,
      View.Count,
      Submission.Count
    )
  ) |>
  rename_with(
    ~ paste0(
      "Q",
      sub("^X([0-9]+).*", "\\1", .x)
    ),
    matches("^X[0-9]+\\.\\.Question")
  )

W24 <-
  bind_rows(
    W24_M,
    W24_F
  )

W24 <- W24 |> mutate(unidentified=FALSE)

rm(list=c("W24_M", "W24_F"))

bind_rows(F23, W24)

accidentally forgot to put unidentified binary indicator

bind_rows(F23, W24) |>
  filter(is.na(unidentified)) |>
  mutate(
    student_id_type = grepl("^student_[0-9]+$", student_id)
  ) |>
  count(student_id_type)

okay, so all are identified

cohort <- bind_rows(F23, W24)

cohort <-
  bind_rows(F23, W24) |>
  mutate(
    unidentified = replace_na(unidentified, FALSE)
  )

cohort |> count(unidentified)

fantastic

5 Spring Cohort/Class

S24_F_VA |> count(Status == "Graded")

S24_F_VA |> summarize(pct=mean(Status == "Graded"))

S24_F_VB |> count(Status == "Graded")

S24_F_VB |> summarize(pct=mean(Status == "Graded"))

many ungraded

S24_M |> select(contains("Q")) |> select(1:4)

unfortunately this one doesnt fit the same pattern of col-names so coding is difficult
However note the ability to view typical student responses

5.1 Midterm

Notice the richness of the data :

S24_M |> 
  select(contains("Q")) |> 
  select(1:4) |> 
  colnames()

## [1] "Question.1.Score"               "Question.1.Weight"             
## [3] "Question.1.Student.Response.s." "Question.1.Correct.Response"

S24_M |> 
  select(contains("Response")) |> 
  select(1:2)

here we see that we also have the student answer choices
- This can be used to identify common misconceptions of students.
- What patterns emerge in the pattern of student responses
  - Take for example the specific questions which persist across exams – what changes?

6 Data Sets

We may therefore have multiple data sets for different modeling purposes – we may compare the performance of each to better understand what better predicts the performance of student on the most recent final exam.

Binary Data Set
Binary & MCQ Data Set
Binary & Text Embedding (Classification)
Binary & MCQ Data Set & Text Embedding (Classification)

6.1 Link Data Sets (Midterm & Final)

6.1.1 Binary Form

S24_M <- S24_M |> 
  select(-contains("Weight")) |> 
  filter(Status == "Graded") |> 
  select(1:9, Sections, contains(".Score")) |>
  rename_with(
    ~ str_replace(.x, "^Question\\.(\\d+)\\.Score$", "Q\\1"),
    contains(".Score")
  )

6.1.2 Anonymize

S24_M|> colnames()

##  [1] "First.Name"    "Last.Name"     "Student.ID"    "Email"        
##  [5] "Sections"      "Status"        "Submission.ID" "Max.Points"   
##  [9] "Total.Score"   "Q1"            "Q2"            "Q3"           
## [13] "Q4"            "Q5"            "Q6"            "Q7"           
## [17] "Q8"            "Q9"            "Q10"           "Q11"          
## [21] "Q12"           "Q13"           "Q14"           "Q15"          
## [25] "Q16"           "Q17"           "Q18"           "Q19"          
## [29] "Q20"           "Q21"           "Q22"           "Q23"          
## [33] "Q24"           "Q25"           "Q26"           "Q27"          
## [37] "Q28"           "Q29"           "Q30"           "Q31"          
## [41] "Q32"           "Q33"           "Q34"

S24_M <- S24_M |> mutate(version = "AB", exam = "Midterm", term = "S24")

S24_M$Student.ID |> unique() |> length()

## [1] 149

S24_M$Submission.ID |> unique() |> length()

## [1] 149

nrow(S24_M)

## [1] 149

1 unique student id for each row – each row is a unique student

S24_M |> distinct(First.Name, Last.Name) |> nrow()

## [1] 149

S24_M <- S24_M |> 
  mutate(unidentified = FALSE) |> 
  rename(SID=Student.ID) 

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

S24_dictionary <- S24_M |>
  select(
    student_id,
    SID,
    First.Name,
    Last.Name,
    Email
  )

S24_M <- 
  S24_M |>
  select(
    everything(), 
    -c(1:4),
    -c(Status, Submission.ID)
  ) |>
  select(
    unidentified,
    term,
    exam,
    version,
    student_id,
    Sections,
    Total.Score,
    Max.Points,
    Q1:Q34
  )

S24_M

6.2 Final

S24_F <-
  bind_rows(
  S24_F_VA |> mutate(version = "A", exam = "Final", term = "S24") |> filter(Status=="Graded"),
  S24_F_VB |> mutate(version = "B", exam = "Final", term = "S24") |> filter(Status=="Graded")
)

S24_F <- 
  S24_F |>
  left_join(
    S24_dictionary |>
      distinct(SID, student_id),
    by = "SID"
  )

S24_F <- 
  S24_F |> mutate(unidentified = FALSE) |>
  rename_with(
    ~ paste0("Q", seq_along(.x)),
    matches("^X\\d+\\.\\.Question")
  )

S24_F <-
  S24_F |>
  select(
    unidentified,
    term,
    exam,
    version,
    student_id,
    Sections,
    Total.Score,
    Max.Points,
    Q1:Q32
  )

S24_M <-
  S24_M |> mutate(
  Total.Score = as.numeric(Total.Score), 
  Max.Points = as.numeric(Max.Points))

S24 <- bind_rows(S24_M, S24_F)

7 Combine into cohorts object

cohorts <- bind_rows(cohort, S24)

cohorts <- cohorts |> mutate(pct = Total.Score/Max.Points)

8 Brief EDA

cohorts |>
  ggplot(aes(x = pct, color = term)) +
  geom_density(linewidth = 1) +
  facet_wrap(~ exam) +
  labs(
    x = "Percent Score",
    y = "Density",
    color = "Term"
  )

cohort_pairs <- cohorts |>
  filter(exam %in% c("Midterm", "Final")) |>
  select(term, student_id, exam, pct) |>
  pivot_wider(
    names_from = exam,
    values_from = pct
  )

cohort_pairs |>
  add_count(term, Midterm, Final, name = "density") |>
  ggplot(aes(x = Midterm, y = Final, color = density)) +
  geom_point(alpha = 0.7) +
  facet_wrap(~ term) +
  scale_x_continuous(labels = scales::percent) +
  scale_y_continuous(labels = scales::percent) +
  labs(
    x = "Midterm Percent",
    y = "Final Percent",
    color = "Students",
    title = "Midterm vs. Final Performance by Quarter"
  )

## Warning: Removed 230 rows containing missing values or values outside the scale range
## (`geom_point()`).

Conversation History

Pressure Test Data (Conversation History)

write.csv(cohorts,"cohorts.csv")

S10 Data Cleaning

Isaiah C. Mireles