##READING FILES
chem1_f18 =     read.csv("~/Teaching/Grades_and_SRT/Fall2018/chem1331_f18.csv",header = TRUE)
chem1_f19 =     read.csv("~/Teaching/Grades_and_SRT/Fall2019/chem1331_f19_grades_canvas.csv",header = TRUE)
chem1_f20 =     read.csv("~/Teaching/Grades_and_SRT/Fall2020/chem1331_f20_grades_canvas.csv",header = TRUE)
chem1_f21 =     read.csv("~/Teaching/Grades_and_SRT/Fall2021/chem1331_f21_grades_canvas.csv",header = TRUE)
chem1_f22 =     read.csv("~/Teaching/Grades_and_SRT/Fall2022/chem1331_f22_grades_canvas.csv",header = TRUE)
chem1_f23 =     read.csv("~/Teaching/Grades_and_SRT/Fall2023/chem1331_f23_grades_canvas.csv",header = TRUE)

chem2_s19 =     read.csv("~/Teaching/Grades_and_SRT/Spring2019/chem1333_s19.csv", header = TRUE)
chem2_s20 =     read.csv("~/Teaching/Grades_and_SRT/Spring2020/chem1333_s20_grades_canvas.csv", header = TRUE)
chem2_s21 =     read.csv("~/Teaching/Grades_and_SRT/Spring2021/chem1333_s21_grades_canvas.csv", header = TRUE)
chem2_s22 =     read.csv("~/Teaching/Grades_and_SRT/Spring2022/chem1333_s22_grades_canvas.csv", header = TRUE)
chem2_s23 =     read.csv("~/Teaching/Grades_and_SRT/Spring2023/chem1333_s23_grades_canvas.csv", header = TRUE)

chem3_f19 =     read.csv("~/Teaching/Grades_and_SRT/Fall2019/chem2231_f19_grades_canvas.csv",header = TRUE)
chem3_f20 =     read.csv("~/Teaching/Grades_and_SRT/Fall2020/chem2131_f20_grades_canvas.csv",header = TRUE)
chem3_f21 =     read.csv("~/Teaching/Grades_and_SRT/Fall2021/chem2131_f21_grades_canvas.csv",header = TRUE)
chem3_f22 =     read.csv("~/Teaching/Grades_and_SRT/Fall2022/chem2131_f22_grades_canvas.csv",header = TRUE)
chem3_f23 =     read.csv("~/Teaching/Grades_and_SRT/Fall2023/chem2131_f23_grades_canvas.csv",header = TRUE)

chem4_s20 =     read.csv("~/Teaching/Grades_and_SRT/Spring2020/chem2333_s20_grades_canvas.csv", header = TRUE)
chem4_s21 =     read.csv("~/Teaching/Grades_and_SRT/Spring2021/chem2335_s21_grades_canvas.csv", header = TRUE)
chem4_s22 =     read.csv("~/Teaching/Grades_and_SRT/Spring2022/chem2335_s22_grades_canvas.csv", header = TRUE)
chem4_s23 =     read.csv("~/Teaching/Grades_and_SRT/Spring2023/chem2335_s23_grades_canvas.csv", header = TRUE)
chem4_s24 =     read.csv("~/Teaching/Grades_and_SRT/Spring2024/chem2335_s24_grades_canvas.csv", header = TRUE)
  
dfs = list(
  chem1_f18 = chem1_f18,
  chem2_s19 = chem2_s19,
  chem3_f19 = chem3_f19,
  chem4_s20 = chem4_s20,
  
  chem1_f19 = chem1_f19,
  chem2_s20 = chem2_s20,
  chem3_f20 = chem3_f20,
  chem4_s21 = chem4_s21,
  
  chem1_f20 = chem1_f20,
  chem2_s21 = chem2_s21,
  chem3_f21 = chem3_f21,
  chem4_s22 = chem4_s22,
  
  chem1_f21 = chem1_f21,
  chem2_s22 = chem2_s22,
  chem3_f22 = chem3_f22,
  chem4_s23 = chem4_s23,
  
  chem1_f22 = chem1_f22,
  chem2_s23 = chem2_s23,
  chem3_f23 = chem3_f23,
  chem4_s24 = chem4_s24,
  
  chem1_f23 = chem1_f23
               
)
getFinalScore = function(dfs){
  #For some reason, some Final.Score is lower than Current.Score. Using the highest
  for (i in seq_along(dfs)) {
    df <- dfs[[i]]
    didItChange=FALSE
    times=0
    for (j in 1:nrow(df)) {
      if (df[j, "Current.Score"] > df[j, "Final.Score"]) {
        df[j, "Final.Score"] <- df[j, "Current.Score"] 
        didItChange=TRUE
        times=times+1
      }
    }
    #if (didItChange){ print(paste("The dataframe",names(dfs)[i]," Current and Final score are not the same in",times,"students"))}
    dfs[[i]] <- df
  }
  return(dfs)
}
dfs = getFinalScore(dfs)

1 All students

We started teaching the new curriculum in Fall 2018. The first cohort (f18) finished in Spring 2020

Let’s consider how students flew through the curriculum and how they did. Some students postpone taking Chem3 or Chem4, we are taking their grade into account even if they didn’t take the course during their sophomore year.

###PREPARING FILES

library(purrr)
combined_df <- dfs %>%
  map(~ select(.x, SIS.Login.ID, Final.Score)) %>%
  reduce(full_join, by = "SIS.Login.ID")


# Rename the Final.Score columns with the names of the original dataframes
names(combined_df)[-1] <- names(dfs)

# Rename the SIS.Login.ID column to student
names(combined_df)[1] <- "student"

f18cohort <- combined_df %>%
  filter(!is.na(chem1_f18))
f19cohort <- combined_df %>%
  filter(!is.na(chem1_f19))
f20cohort <- combined_df %>%
  filter(!is.na(chem1_f20))
f21cohort <- combined_df %>%
  filter(!is.na(chem1_f21))
f22cohort <- combined_df %>%
  filter(!is.na(chem1_f22))

merge_columns <- function(df, prefix) {
  columns <- grep(paste0("^", prefix), names(df), value = TRUE)
  merged_column <- ifelse(rowSums(!is.na(df[columns])) == 0, NA, 
                          do.call(pmax, c(df[columns], na.rm = TRUE)))
  return(merged_column)
}

returnMergedColumns = function(df){
  # Apply the function to merge chem1, chem2, chem3, and chem4 columns
  df <- df %>%
    mutate(
      chem1 = merge_columns(., "chem1"),
      chem2 = merge_columns(., "chem2"),
      chem3 = merge_columns(., "chem3"),
      chem4 = merge_columns(., "chem4")
    ) %>%
    select(student, chem1, chem2, chem3, chem4)
  return(df)
}
prepareAlluvial = function(df){
  new_df <- df %>%
  mutate_at(vars(chem1:chem4), ~case_when(
    is.na(.) ~ "OUT",
    . > 90 ~ "A",
    . >= 80 & . <= 90 ~ "B",
    . >= 70 & . < 80 ~ "C",
    . >= 60 & . < 70 ~ "D",
    TRUE ~ "F"
  ))
long_df <- pivot_longer(new_df, cols = starts_with("chem"), names_to = "course", values_to = "letterGrade")

# Rename columns
colnames(long_df) <- c("student", "course", "letterGrade")
return(long_df)
  
}

f18cohort = returnMergedColumns(f18cohort)
f18letter = prepareAlluvial(f18cohort)

f19cohort = returnMergedColumns(f19cohort)
f19letter = prepareAlluvial(f19cohort)

f20cohort = returnMergedColumns(f20cohort)
f20letter = prepareAlluvial(f20cohort)

f21cohort = returnMergedColumns(f21cohort)
f21letter = prepareAlluvial(f21cohort)

f22cohort = returnMergedColumns(f22cohort)
f22letter = prepareAlluvial(f22cohort)

#f18cohort %>% select(-1) %>% arrange(chem2) %>% write.csv("f18cohort.csv", row.names = FALSE)
#f19cohort %>% select(-1) %>% arrange(chem2) %>% write.csv("f19cohort.csv", row.names = FALSE)
#f20cohort %>% select(-1) %>% arrange(chem2) %>% write.csv("f20cohort.csv", row.names = FALSE)
#f21cohort %>% select(-1) %>% arrange(chem2) %>% write.csv("f21cohort.csv", row.names = FALSE)
#write.csv(f22cohort[order(f22cohort$chem2_s23),],"f22cohort.csv")
## REPRESENT
makeAlluvial_Cohort = function(df,thisTitle){
  ggplot(df,
           aes(x = course, stratum = letterGrade, alluvium = student, fill = letterGrade)) +
      scale_x_discrete(expand = c(.1, .1)) +
      geom_flow() +
      geom_stratum(alpha = .5) +
      theme_minimal() +
      geom_text(stat = "stratum",
                aes(label = percent(after_stat(prop), accuracy = .1)))+
      labs(title = thisTitle, x = "", y = "Number of students")
}
makeAlluvial_Cohort2 = function(df,thisTitle){
  ggplot(df,
           aes(x = course, stratum = letterGrade, alluvium = student, fill = letterGrade)) +
      scale_x_discrete(expand = c(.1, .1)) +
      geom_flow() +
      geom_stratum(alpha = .5) +
      theme_minimal() +
      geom_text(stat = "stratum",
                aes(label = after_stat(count) ))+
      labs(title = thisTitle, x = "", y = "Number of students")
}
create_summary_table <- function(df) {
    summary_table <- df %>%
    group_by(course, letterGrade, .drop = TRUE) %>%
    summarize(count = n(), .groups = 'drop') %>%
    ungroup() %>%
    complete(course, letterGrade, fill = list(count = 0))

  # Pivot the data to have courses as columns
  summary_table <- summary_table %>%
    pivot_wider(names_from = course, values_from = count)
  
  df_sum <- summary_table %>%
  bind_rows(
    data.frame(
      letterGrade = "Total",
      chem1 = sum(.$chem1),
      chem2 = sum(.$chem2),
      chem3 = sum(.$chem3),
      chem4 = sum(.$chem4)
    )
  )

# Calculate percentages and add "%" character
df_percent <- df_sum %>%
  mutate(across(starts_with("chem"), ~ scales::percent(./sum(.)*2 , accuracy = 0.1), .names = "{col}%"))

  return(df_percent)
}

allCohorts = rbind(f18letter,f19letter)
allCohorts = rbind(allCohorts,f20letter)
allCohorts = rbind(allCohorts,f21letter)
allCohorts = rbind(allCohorts,f22letter)
allCohorts = allCohorts[!duplicated(allCohorts, fromLast = TRUE),]
makeAlluvial_Cohort(allCohorts,"All cohorts")

summary_table <- create_summary_table(allCohorts)
kable(summary_table,caption = "All cohort numbers")
All cohort numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 318 234 122 112 32.8% 24.1% 12.6% 11.5%
B 417 284 158 166 43.0% 29.3% 16.3% 17.1%
C 164 163 104 57 16.9% 16.8% 10.7% 5.9%
D 45 27 30 14 4.6% 2.8% 3.1% 1.4%
F 26 6 6 5 2.7% 0.6% 0.6% 0.5%
OUT 0 256 550 616 0.0% 26.4% 56.7% 63.5%
Total 970 970 970 970 100.0% 100.0% 100.0% 100.0%

1.1 Retention across the curriculum and across the years

calculate_percentage <- function(df) {
  df %>%
    mutate(not_out = ifelse(letterGrade != "OUT", 1, 0)) %>%
    group_by(course) %>%
    summarise(percentage = mean(not_out) * 100)
}

# Apply the function to each dataframe
f18_percentage <- calculate_percentage(f18letter)
f19_percentage <- calculate_percentage(f19letter)
f20_percentage <- calculate_percentage(f20letter)
f21_percentage <- calculate_percentage(f21letter)
f22_percentage <- calculate_percentage(f22letter)
allPercentage <- bind_rows(f18_percentage,
                         f19_percentage,
                         f20_percentage,
                         f21_percentage,
                         f22_percentage,
                          .id = "df_id"
                         )%>%
  mutate(df_id = case_when(
    df_id == "1" ~ "Fall18",
    df_id == "2" ~ "Fall19",
    df_id == "3" ~ "Fall20",
    df_id == "4" ~ "Fall21",
    df_id == "5" ~ "Fall22",
    TRUE ~ df_id  # Keep other values unchanged
  ))
ggplot(allPercentage, aes(x = course, y = percentage, group = df_id, color = df_id)) +
  geom_line() +
  geom_point() +
  labs(x = "Course", y = "% students",
       title = "Retention across the Chemistry Curriculum") +
  scale_color_discrete(name = "Cohort first semester") +
  theme_minimal()

spread_df <- spread(allPercentage, df_id, percentage)

library(knitr)
# Print the resulting table
kable(spread_df, format = "markdown",digits = 1, caption = "Percentage of the initial cohort taking the course ")
Percentage of the initial cohort taking the course
course Fall18 Fall19 Fall20 Fall21 Fall22
chem1 100.0 100.0 100.0 100.0 100.0
chem2 71.3 73.6 72.6 74.4 73.1
chem3 43.1 50.0 41.7 38.5 41.8
chem4 36.5 44.0 32.7 31.8 36.1

1.2 Cohort 1: Starting in Fall2018

Took: * CHEM1 in F18 * CHEM2 in S19 * CHEM3 in F19 or later * CHEM4 in S20 or later

makeAlluvial_Cohort(f18letter,"F18 all students cohort")

summary_table <- create_summary_table(f18letter)
kable(summary_table,caption = "F18 numbers")
F18 numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 90 38 18 16 49.7% 21.0% 9.9% 8.8%
B 71 50 36 34 39.2% 27.6% 19.9% 18.8%
C 13 34 19 14 7.2% 18.8% 10.5% 7.7%
D 2 5 4 2 1.1% 2.8% 2.2% 1.1%
F 5 2 1 0 2.8% 1.1% 0.6% 0.0%
OUT 0 52 103 115 0.0% 28.7% 56.9% 63.5%
Total 181 181 181 181 100.0% 100.0% 100.0% 100.0%

1.3 Cohort 2: Starting in Fall2019

Took: * CHEM1 in F19 * CHEM2 in S20 * CHEM3 in F20 or later * CHEM4 in S21 or later

makeAlluvial_Cohort(f19letter,"F19 all students cohort")

summary_table <- create_summary_table(f19letter)
kable(summary_table,caption = "F19 numbers")
F19 numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 52 29 23 13 28.6% 15.9% 12.6% 7.1%
B 95 59 31 46 52.2% 32.4% 17.0% 25.3%
C 25 39 25 19 13.7% 21.4% 13.7% 10.4%
D 7 6 10 2 3.8% 3.3% 5.5% 1.1%
F 3 1 2 0 1.6% 0.5% 1.1% 0.0%
OUT 0 48 91 102 0.0% 26.4% 50.0% 56.0%
Total 182 182 182 182 100.0% 100.0% 100.0% 100.0%

1.4 Cohort 3: Started in Fall2020

Took: * CHEM1 in F20 * CHEM2 in S21 * CHEM3 in F21 or later * CHEM4 in S22 or later

makeAlluvial_Cohort(f20letter,"F20 all students cohort")

summary_table <- create_summary_table(f20letter)
kable(summary_table,caption = "F20 numbers")
F20 numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 48 59 30 27 21.5% 26.5% 13.5% 12.1%
B 80 62 34 30 35.9% 27.8% 15.2% 13.5%
C 65 35 23 12 29.1% 15.7% 10.3% 5.4%
D 22 5 6 3 9.9% 2.2% 2.7% 1.3%
F 8 1 0 1 3.6% 0.4% 0.0% 0.4%
OUT 0 61 130 150 0.0% 27.4% 58.3% 67.3%
Total 223 223 223 223 100.0% 100.0% 100.0% 100.0%

1.5 Cohort 4: Started in Fall2021

Took:

  • CHEM1 in F21
  • CHEM2 in S22
  • CHEM3 in F22 or later
  • CHEM4 in S23
makeAlluvial_Cohort(f21letter,"F21 all students cohort")

summary_table <- create_summary_table(f21letter)
kable(summary_table,caption = "F21 numbers")
F21 numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 71 57 30 30 36.4% 29.2% 15.4% 15.4%
B 76 52 22 24 39.0% 26.7% 11.3% 12.3%
C 28 29 19 4 14.4% 14.9% 9.7% 2.1%
D 14 5 2 2 7.2% 2.6% 1.0% 1.0%
F 6 2 2 2 3.1% 1.0% 1.0% 1.0%
OUT 0 50 120 133 0.0% 25.6% 61.5% 68.2%
Total 195 195 195 195 100.0% 100.0% 100.0% 100.0%

1.6 Cohort 5. Started in Fall 2022

Took:

  • CHEM1 in F22
  • CHEM2 in S23
  • CHEM3 in F23
  • CHEM4 in S24

At the moment of this analysis some students dropped or have not taken ochem2 or chem4, so they may take it in the next year and the results may change

makeAlluvial_Cohort(f22letter,"F22 all students cohort")

summary_table <- create_summary_table(f22letter)
kable(summary_table,caption = "F22 numbers")
F22 numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 60 52 22 26 28.8% 25.0% 10.6% 12.5%
B 102 64 37 34 49.0% 30.8% 17.8% 16.3%
C 37 29 19 8 17.8% 13.9% 9.1% 3.8%
D 4 6 8 5 1.9% 2.9% 3.8% 2.4%
F 5 1 1 2 2.4% 0.5% 0.5% 1.0%
OUT 0 56 121 133 0.0% 26.9% 58.2% 63.9%
Total 208 208 208 208 100.0% 100.0% 100.0% 100.0%

1.7 The fate of C- students

If a student gets less than 74%, what advise should we give them?

Cstud_s23 = chem2_s23[which(chem2_s23$Unposted.Final.Score < 74),]
Cstud_s23 = Cstud_s23
summary_table <- f18cohort %>%
  filter(chem2 < 74) %>%
  summarize(
    students_below_74 = n(),
    avg_chem3_score = mean(chem3, na.rm = TRUE),
    avg_chem4_score = mean(chem4, na.rm = TRUE)
  )

# Print summary table
print(summary_table)
##   students_below_74 avg_chem3_score avg_chem4_score
## 1                18         75.0475            74.6

2 Only students who took the 4 semesters

Now let’s just consider only the students who took the four semesters.

There are some students who transferred in GenChem2, so there are some premed-like students that continued to Biochemistry even if they look to be “OUT” of the curriculum.

TODO: Check Biochem lists.

## PREPARE FILES
select_and_add_name <- function(df, name) {
  if (any(is.na(df$Final.Score))) {
    print(paste("Data frame", name, "contains NA values in the 'grade' column"))
  }
  selected_df <- df %>% 
    #select(ID, Final.Score) %>%
    select(SIS.Login.ID, Final.Score) %>%
    mutate(Final.Score = as.numeric(Final.Score))  # Convert "grade" to numeric type
  selected_df$name <- name
  return(selected_df)
}

# Apply the function to each dataframe in the list
dfs_with_name <- Map(select_and_add_name, dfs, names(dfs))

# Merge all data frames into one
merged_df <- bind_rows(dfs_with_name)

names(merged_df)[names(merged_df)=="SIS.Login.ID"] = "student"
allCohort <- separate(merged_df, name, into = c("course", "semester"), sep = "_")
allCohort$letterGrade = ifelse(allCohort$Final.Score > 90, "A",
                         ifelse(allCohort$Final.Score >= 80, "B",
                                ifelse(allCohort$Final.Score >= 70, "C",
                                       ifelse(allCohort$Final.Score >= 60, "D", "F"))))

# filter just in case they took it twice, just take the highest score
allCohort <- allCohort %>%
  group_by(student, course) %>%
  filter(Final.Score == max(Final.Score))

# What students took the whole sequence
fullFour <- allCohort %>%
  group_by(student) %>%
  filter(all(c("chem1", "chem2", "chem3", "chem4") %in% course))

# filter just in case they took it twice, just take the highest score
fullFour <- fullFour %>%
  group_by(student, course) %>%
  filter(Final.Score == max(Final.Score))
#Writing just in case

## REPRESENTING
selectSemester = function(df,thisSem){
  selected_students <- df %>%
    filter(course == "chem1" & semester == thisSem) %>%
    pull(student) %>%
    unique()

  # Filter the dataframe to include all rows for selected students
  thisSem_df <- df %>%
    filter(student %in% selected_students)
  return(thisSem_df) 
}
plotAlluvialCohort = function(df,thisSem,thisTitle){
  
  thisSem_df = selectSemester(df,thisSem)
  ggplot(thisSem_df,
           aes(x = course, stratum = letterGrade, alluvium = student, fill = letterGrade)) +
      scale_x_discrete(expand = c(.1, .1)) +
      geom_flow() +
      geom_stratum(alpha = .5) +
      theme_minimal() +
      geom_text(stat = "stratum",
                aes(label = percent(after_stat(prop), accuracy = .1)))+
      labs(title = thisTitle, x = "", y = "Number of students")
}
makeAlluvial_Cohort(fullFour,"All cohorts. Students who took the four semesters")

summary_table <- create_summary_table(fullFour)
kable(summary_table,caption = "All cohort numbers. Students who took the four semesters")
All cohort numbers. Students who took the four semesters
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 167 148 106 108 49.1% 43.5% 31.2% 31.8%
B 153 150 139 161 45.0% 44.1% 40.9% 47.4%
C 18 39 79 55 5.3% 11.5% 23.2% 16.2%
D 2 3 16 11 0.6% 0.9% 4.7% 3.2%
F 0 0 0 5 0.0% 0.0% 0.0% 1.5%
Total 340 340 340 340 100.0% 100.0% 100.0% 100.0%

2.1 Cohort 1: Started in Fall2018

plotAlluvialCohort(fullFour,"f18","AllFour: Cohort taking CHEM1 in Fall 2018")

summary_table <- create_summary_table(selectSemester(fullFour,"f18"))
kable(summary_table,caption = "All4 F18 cohort numbers")
All4 F18 cohort numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 46 23 16 15 71.9% 35.9% 25.0% 23.4%
B 18 32 32 33 28.1% 50.0% 50.0% 51.6%
C 0 8 14 14 0.0% 12.5% 21.9% 21.9%
D 0 1 2 2 0.0% 1.6% 3.1% 3.1%
Total 64 64 64 64 100.0% 100.0% 100.0% 100.0%

2.2 Cohort 2: Started in Fall2019

plotAlluvialCohort(fullFour,"f19","AllFour: Cohort taking CHEM1 in Fall 2019")

summary_table <- create_summary_table(selectSemester(fullFour,"f19"))
kable(summary_table,caption = "All4 F18 cohort numbers")
All4 F18 cohort numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 30 21 23 12 38.5% 26.9% 29.5% 15.4%
B 45 40 29 46 57.7% 51.3% 37.2% 59.0%
C 3 16 20 18 3.8% 20.5% 25.6% 23.1%
D 0 1 6 2 0.0% 1.3% 7.7% 2.6%
Total 78 78 78 78 100.0% 100.0% 100.0% 100.0%

2.3 Cohort 3: Started in Fall2020

plotAlluvialCohort(fullFour,"f20","AllFour: Cohort taking CHEM1 in Fall 2020")

summary_table <- create_summary_table(selectSemester(fullFour,"f20"))
kable(summary_table,caption = "All4 F20 cohort numbers")
All4 F20 cohort numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 27 40 24 27 37.5% 55.6% 33.3% 37.5%
B 34 25 28 29 47.2% 34.7% 38.9% 40.3%
C 10 7 17 12 13.9% 9.7% 23.6% 16.7%
D 1 0 3 3 1.4% 0.0% 4.2% 4.2%
F 0 0 0 1 0.0% 0.0% 0.0% 1.4%
Total 72 72 72 72 100.0% 100.0% 100.0% 100.0%

2.4 Cohort 4: Started in Fall2021

plotAlluvialCohort(fullFour,"f21","AllFour: Cohort taking CHEM1 in Fall 2021")

summary_table <- create_summary_table(selectSemester(fullFour,"f21"))
kable(summary_table,caption = "All4 F21 cohort numbers")
All4 F21 cohort numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 32 28 23 28 58.2% 50.9% 41.8% 50.9%
B 19 22 17 20 34.5% 40.0% 30.9% 36.4%
C 3 5 13 4 5.5% 9.1% 23.6% 7.3%
D 1 0 2 1 1.8% 0.0% 3.6% 1.8%
F 0 0 0 2 0.0% 0.0% 0.0% 3.6%
Total 55 55 55 55 100.0% 100.0% 100.0% 100.0%

2.5 Cohort 5: Started in Fall2022

plotAlluvialCohort(fullFour,"f22","AllFour: Cohort taking CHEM1 in Fall 2022")

summary_table <- create_summary_table(selectSemester(fullFour,"f22"))
kable(summary_table,caption = "All4 F22 cohort numbers")
All4 F22 cohort numbers
letterGrade chem1 chem2 chem3 chem4 chem1% chem2% chem3% chem4%
A 32 36 20 26 45.1% 50.7% 28.2% 36.6%
B 37 31 33 33 52.1% 43.7% 46.5% 46.5%
C 2 3 15 7 2.8% 4.2% 21.1% 9.9%
D 0 1 3 3 0.0% 1.4% 4.2% 4.2%
F 0 0 0 2 0.0% 0.0% 0.0% 2.8%
Total 71 71 71 71 100.0% 100.0% 100.0% 100.0%

3 With Biochemistry

chem5_f21 = read.csv("~/Teaching/Grades_and_SRT/BIOC3321\ Grade\ Spreadsheet\ F21\ F22\ F23/Grades-BIOC_3321_(001)_Fall_2021.csv")
chem5_f22 = read.csv("~/Teaching/Grades_and_SRT/BIOC3321\ Grade\ Spreadsheet\ F21\ F22\ F23/Grades-BIOC_3321_(001)_Fall_2022.csv")
chem5_f23 = read.csv("~/Teaching/Grades_and_SRT/BIOC3321\ Grade\ Spreadsheet\ F21\ F22\ F23/Grades-BIOC_3321_(001)_Fall_2023.csv")
chem5_f21 = head(tail(chem5_f21, -1), -1)
chem5_f22 = head(tail(chem5_f22, -1), -1)
chem5_f23 = tail(chem5_f23, -1)
dfs_bio = list(
  chem5_f21 = chem5_f21,
  chem5_f22 = chem5_f22,
  chem5_f23 = chem5_f23
)
dfs_bio = getFinalScore(dfs_bio)

#merged_df = merge(f19cohort,chem5_f21, by.x = "student", by.y = "SIS.Login.ID", all.x = TRUE)
#f19cohort$chem5 = merged_df$Final.Score
addBiochemStudents = function(df1,df2,df3,df4){
  #df1 is the original cohort, df2,3, and 4 are the biochem 
  df1 <- df1 %>%
    left_join(select(df2, SIS.Login.ID, Final.Score), by = c("student" = "SIS.Login.ID")) %>%
    rename(chem5_f21 = Final.Score)
  df1 <- df1 %>%
    left_join(select(df3, SIS.Login.ID, Final.Score), by = c("student" = "SIS.Login.ID")) %>%
    rename(chem5_f22 = Final.Score)
  df1 <- df1 %>%
    left_join(select(df4, SIS.Login.ID, Final.Score), by = c("student" = "SIS.Login.ID")) %>%
    rename(chem5_f23 = Final.Score)

  df1 <- df1 %>%
  mutate(chem5 = coalesce(chem5_f21, chem5_f22, chem5_f23)) %>%
  select(-chem5_f21, -chem5_f22, -chem5_f23)  # Remove intermediate columns
  
  return(df1)

}
addFinalExamBiochemStudents = function(df1,df2,df3,df4){
  #df1 is the original cohort, df2,3, and 4 are the biochem 
  df1 <- df1 %>%
    left_join(select(df2, SIS.Login.ID, Final.Exam.Final.Score), by = c("student" = "SIS.Login.ID")) %>%
    rename(chem5_f21_final = Final.Exam.Final.Score)
  df1 <- df1 %>%
    left_join(select(df3, SIS.Login.ID, Final.Exam.Final.Score), by = c("student" = "SIS.Login.ID")) %>%
    rename(chem5_f22_final = Final.Exam.Final.Score)
  df1 <- df1 %>%
    left_join(select(df4, SIS.Login.ID, Final.Exam.Final.Score), by = c("student" = "SIS.Login.ID")) %>%
    rename(chem5_f23_final = Final.Exam.Final.Score)

  df1 <- df1 %>%
  mutate(chem5_final = coalesce(chem5_f21_final, chem5_f22_final, chem5_f23_final)) %>%
  select(-chem5_f21_final, -chem5_f22_final, -chem5_f23_final)  # Remove intermediate columns
  
  return(df1)

}

f19cohort = addBiochemStudents(f19cohort,chem5_f21,chem5_f22,chem5_f23)
f20cohort = addBiochemStudents(f20cohort,chem5_f21,chem5_f22,chem5_f23)
f21cohort = addBiochemStudents(f21cohort,chem5_f21,chem5_f22,chem5_f23)

f19cohort = addFinalExamBiochemStudents(f19cohort,chem5_f21,chem5_f22,chem5_f23)
f20cohort = addFinalExamBiochemStudents(f20cohort,chem5_f21,chem5_f22,chem5_f23)
f21cohort = addFinalExamBiochemStudents(f21cohort,chem5_f21,chem5_f22,chem5_f23)