Final grade calculations from a Canvas gradebook that uses an additive grading policy

final-grade-calculations-488.R

## Introduction

# This code will take grades on the Canvas gradebook, and add up the individual
# assignments to get a final grade, according to the policy for the class, in this
# case, here https://docs.google.com/spreadsheets/d/1-6f7mpgStojP4Gb3mk2PSwupGY2MiSrnIDD_KlxxEzk/edit?gid=874302185#gid=874302185

## Get the Canvas gradebook

# Go to the Canvas course page, go to the gradebook for that
# course, click 'Actions' then 'Export'. That will download a CSV file to
# your computer. Move that file into the same folder as this R script file.
# Make sure that the assignment that we are updating is 'published' so
# we get the column in the exported gradebook

library(tidyverse)

# update the Canvas gradebook file name here (assuming we are starting in grading.Rproj)
canvas_gradebook <- "2025-03-24T1409_Grades-ARCHY_488_A.csv"

# check structure of the data
str(read_csv(canvas_gradebook))

# sum up the assignments in each group, and assign to totals
canvas_gradebook_totals_update <-
  read_csv(canvas_gradebook) %>%
  filter(!is.na(`SIS Login ID`)) %>%
  select(-contains(c("Current Points",
                     "Final Points",
                     "Current Score",
                     "Unposted Current Score",
                     "Final Score",
                     "Unposted Final Score"))) %>% 
  mutate(across(c(6:ncol(.) & where(is.character)),
                parse_number)) %>%
  dplyr::rowwise() %>%
  # now do the additive grade calculations, create new columns to hold the 
  # totals, 1:10 for ten weeks
  mutate(`Total Labs`  = sum(c_across(contains(str_c("Week ", 1:10, ": "))),
                                              na.rm = TRUE)) %>% 
  mutate(`Total Final Project`  = sum(c_across(contains(str_c("Step ", 1:10, ": "))),
                                              na.rm = TRUE)) %>%
  # deal with the max allowed from each category, indicated here:
  # https://docs.google.com/spreadsheets/d/1-6f7mpgStojP4Gb3mk2PSwupGY2MiSrnIDD_KlxxEzk/edit?gid=874302185#gid=874302185
  mutate(`Total Labs` = if_else(`Total Labs` > 3,
                                        3, `Total Labs` )) %>%
  mutate(`Total Final Project` = if_else(`Total Final Project` > 1,
                                        1, `Total Final Project` )) %>% 
  
  select(Student,
         ID,
         `SIS User ID`,
         `SIS Login ID`,
          Section,
         `Total Labs`,
         `Total Final Project`
  )

# take a look before we upload to Canvas, does it look ok?
view(canvas_gradebook_totals_update)

# See how their grade out of 4.0 is looking
canvas_gradebook_totals_update_out_of_four <-
  canvas_gradebook_totals_update %>%
  rowwise() %>%
  mutate(total_out_of_4.0 = rowSums(across(starts_with("Total")),
                                    na.rm = TRUE)  ) %>%
  mutate(total_out_of_4.0 = ifelse(total_out_of_4.0 > 4,
                                   4,
                                   total_out_of_4.0)) %>%
  # round up, https://stackoverflow.com/a/12688836/1036500
  mutate(total_out_of_4.0_rounded = janitor::round_half_up(total_out_of_4.0, 1))

# take a look
view(canvas_gradebook_totals_update_out_of_four)

canvas_gradebook_totals_update_out_of_four %>%
  select(Student, total_out_of_4.0_rounded) %>% View

ggplot(canvas_gradebook_totals_update_out_of_four) +
  aes(total_out_of_4.0_rounded) +
  geom_histogram() +
  ggtitle("ARCHY 488 SP23 Distribution of final grades") +
  theme_minimal(base_size = 14) +
  xlab("Final grade submitted to the registrar")+
  xlim(2, 4.1)


# ---- export to CSV, and import this file to Canvas gradebook
write_csv(canvas_gradebook_totals_update,
          "update_totals_upload_to_canvas_gradebook.csv")

# ---- export to CSV, and import this file to UW GradePage
canvas_gradebook_totals_update_out_of_four %>%
  select(`SIS User ID`,
         ImportGrade = total_out_of_4.0_rounded) %>%
  write_csv("update_totals_upload_to_gradepage.csv")

# Go to canvas an import, then come back here and continue...

# delete all the CSV files in this directory so we have a clean slate ready for next week
map(fs::dir_ls( glob = "*.csv"), fs::file_delete)

final-grade-calculations-ARCHY-482-486.R

library(tidyverse)
library(readxl)

# a column for grades to be submitted (ImportGrade)

gb_486 <- read_csv("2025-12-15T1646_Grades-ARCHY_486_A.csv")
gb_482 <- read_csv("2025-12-15T1646_Grades-ARCHY_482_A.csv")


#---------------------------------------------------------
# ARCHY 486
gb_486_assignments_long <- 
gb_486 |> 
  drop_na(`SIS User ID`) |> 
  select(Student,
         `SIS User ID`,
         starts_with("Lab module"),
         starts_with("Step"),
         `Field trip (10655111)`) |> 
  mutate(across(starts_with("Lab module"), as.numeric)) |> 
  pivot_longer(-c(Student,
                  `SIS User ID`),
               names_to = "assignment") |> 
  mutate(assignment_type = case_when(
    str_detect(assignment, "Lab module") ~  "lab",
    str_detect(assignment, "Step") ~ "final project",
    str_detect(assignment, "Field") ~ "field trip"))

# # deal with the max allowed from each category, indicated here:
# https://docs.google.com/spreadsheets/u/2/d/1ORPYL7yb_QUL0GNzFa5pYrcwfOaFQ3As-X921dzvekY/edit?usp=sharing Here are the max possible for each assignment type:

# lab note:       2.6 / 4
# final project : 1.4 / 4

gb_486_assignments_long_max <- 
gb_486_assignments_long |> 
  group_by(Student, 
           assignment_type) |> 
  summarise(sum_points = sum(value, na.rm = TRUE)) |> 
  mutate(sum_points_type = case_when(
    assignment_type == "final project" ~ ifelse(sum_points > 1.4, 1.4, sum_points),
    assignment_type == "lab"           ~ ifelse(sum_points > 2.6, 2.6, sum_points),
    assignment_type == "field trip"    ~ sum_points
  )) 

# tally totals out of 4.0 for the gradebook
gb_486_assignments_long_max_for_submitting <- 
gb_486_assignments_long_max |> 
  group_by(Student) |> 
  summarise(sum_points_type_all = sum(sum_points_type, na.rm = TRUE),
            ImportGrade = ifelse(sum_points_type_all > 4, 4, 
                                 janitor::round_half_up(sum_points_type_all, 1)))

write_csv(gb_486_assignments_long_max_for_submitting,
          "gb_486_assignments_long_max_for_submitting.csv")

#---------------------------------------------------------
# ARCHY 482
gb_482_assignments_long <- 
  gb_482 |> 
  drop_na(`SIS User ID`) |> 
  select(Student,
         `SIS User ID`,
         `Module 1: Getting to Know Each Other (10732923)`,
         matches("quiz \\(|Seminar\\:|Final paper step"),
         `Field trip (10657411)`) |> 
  mutate(across(matches("Module|Final|Field"),
                as.numeric)) |> 
  pivot_longer(-c(Student,
                  `SIS User ID`),
               names_to = "assignment") |> 
  mutate(assignment_type = case_when(
    str_detect(assignment, "Seminar|Getting") ~  "seminar",
    str_detect(assignment, "quiz")            ~ "quiz",
    str_detect(assignment, "Final paper")     ~ "paper",
    str_detect(assignment, "Field")           ~ "field trip"
    ))

# # deal with the max allowed from each category, indicated here:
# https://docs.google.com/spreadsheets/u/2/d/1ORPYL7yb_QUL0GNzFa5pYrcwfOaFQ3As-X921dzvekY/edit?usp=sharing Here are the max possible for each assignment type:

quiz_max <-           1.4  # / 4
seminar_max <-        1.6  # / 4
paper_max <-          1.3  # / 4

gb_482_assignments_long_max <- 
  gb_482_assignments_long |> 
  group_by(Student, 
           assignment_type) |> 
  summarise(sum_points = sum(value, na.rm = TRUE)) |> 
  mutate(sum_points_type = case_when(
    assignment_type == "seminar"        ~ ifelse(sum_points > seminar_max, 
                                                 seminar_max, 
                                                 sum_points),
    assignment_type == "quiz"           ~ ifelse(sum_points > quiz_max, 
                                                 quiz_max, 
                                                 sum_points),
    assignment_type == "paper"          ~ ifelse(sum_points > paper_max, 
                                                 paper_max, 
                                                 sum_points),
    assignment_type == "field trip"     ~ sum_points
  )) 

# tally totals out of 4.0 for the gradebook
gb_482_assignments_long_max_for_submitting <- 
  gb_482_assignments_long_max |> 
  group_by(Student) |> 
  summarise(sum_points_type_all = sum(sum_points_type, na.rm = TRUE),
            ImportGrade = ifelse(sum_points_type_all > 4, 4, 
                                 janitor::round_half_up(sum_points_type_all, 1)))

write_csv(gb_482_assignments_long_max_for_submitting,
          "gb_482_assignments_long_max_for_submitting.csv")

final-grade-calculations.R

## Introduction

# This code will take grades on the Canvas gradebook, and add up the individual
# assignments to get a final grade, according to the policy for the class, in this
# case, here https://docs.google.com/spreadsheets/d/1-6f7mpgStojP4Gb3mk2PSwupGY2MiSrnIDD_KlxxEzk/edit?gid=874302185#gid=874302185

## Get the Canvas gradebook

# Go to the Canvas course page, go to the gradebook for that
# course, click 'Actions' then 'Export'. That will download a CSV file to
# your computer. Move that file into the same folder as this R script file.
# Make sure that the assignment that we are updating is 'published' so
# we get the column in the exported gradebook

library(tidyverse)

# update the Canvas gradebook file name here (assuming we are starting in grading.Rproj)
canvas_gradebook <- "2025-03-24T0948_Grades-ARCHY_483_A.csv"

# check structure of the data
str(read_csv(canvas_gradebook))

# sum up the assignments in each group, and assign to totals
canvas_gradebook_totals_update <-
  read_csv(canvas_gradebook) %>%
  filter(!is.na(`SIS Login ID`)) %>%
  select(-contains(c("Current Points",
                     "Final Points",
                     "Current Score",
                     "Unposted Current Score",
                     "Final Score",
                     "Unposted Final Score"))) %>% 
  mutate(across(c(6:ncol(.) & where(is.character)),
                parse_number)) %>%
  dplyr::rowwise() %>%
  # now do the additive grade calculations, create new columns to hold the 
  # totals, 1:10 for ten weeks
  mutate(`Total Seminar notes`  = sum(c_across(contains(str_c("Seminar ", 1:10, ": "))),
                                              na.rm = TRUE)) %>%
  mutate(`Total Participation`  = sum(c_across(contains(str_c("Lecture ", 1:10, " participation "))),
                                              na.rm = TRUE)) %>%
  mutate(`Total Quizzes`        = sum(c_across(contains(c(str_c("Lecture ", 1:10, " quiz "),
                                                  "Syllabus Quiz"))),
                                              na.rm = TRUE))%>%
  mutate(`Total Final paper`   = round(sum(c_across(starts_with("Final paper step")),
                                                    na.rm = TRUE), 1) ) %>%
  # deal with the max allowed from each category, indicated here:
  # https://docs.google.com/spreadsheets/d/1-6f7mpgStojP4Gb3mk2PSwupGY2MiSrnIDD_KlxxEzk/edit?gid=874302185#gid=874302185
  # max is 1 for all assignment categories, so we count 1 as the max towards the final grade
  mutate(`Total Seminar notes` = if_else(`Total Seminar notes` > 1,
                                        1, `Total Seminar notes` )) %>%
  mutate(`Total Participation` = if_else(`Total Participation` > 1,
                                        1, `Total Participation` )) %>%
  mutate(`Total Quizzes` =       if_else(`Total Quizzes`  > 1,
                                        1, `Total Quizzes`  )) %>%
  mutate(`Total Final paper` = if_else(`Total Final paper` > 1,
                                        1, `Total Final paper`  )) %>%
  
  select(Student,
         ID,
         `SIS User ID`,
         `SIS Login ID`,
          Section,
         `Total Seminar notes`,
         `Total Participation`,
         `Total Quizzes`,
         `Total Final paper`
  )

# take a look before we upload to Canvas, does it look ok?
view(canvas_gradebook_totals_update)

# See how their grade out of 4.0 is looking
canvas_gradebook_totals_update_out_of_four <-
  canvas_gradebook_totals_update %>%
  rowwise() %>%
  mutate(total_out_of_4.0 = rowSums(across(starts_with("Total")),
                                    na.rm = TRUE)  ) %>%
  mutate(total_out_of_4.0 = ifelse(total_out_of_4.0 > 4,
                                   4,
                                   total_out_of_4.0)) %>%
  # round up, https://stackoverflow.com/a/12688836/1036500
  mutate(total_out_of_4.0_rounded = janitor::round_half_up(total_out_of_4.0, 1))

# take a look
view(canvas_gradebook_totals_update_out_of_four)

canvas_gradebook_totals_update_out_of_four %>%
  select(Student, total_out_of_4.0_rounded) %>% View

ggplot(canvas_gradebook_totals_update_out_of_four) +
  aes(total_out_of_4.0_rounded) +
  geom_histogram() +
  ggtitle("ARCHY 483 SP23 Distribution of final grades") +
  theme_minimal(base_size = 14) +
  xlab("Final grade submitted to the registrar")+
  xlim(2, 4.1)


# ---- export to CSV, and import this file to Canvas gradebook
write_csv(canvas_gradebook_totals_update,
          "update_totals_upload_to_canvas_gradebook.csv")

# ---- export to CSV, and import this file to UW GradePage
canvas_gradebook_totals_update_out_of_four %>%
  select(`SIS User ID`,
         ImportGrade = total_out_of_4.0_rounded) %>%
  write_csv("update_totals_upload_to_gradepage.csv")

# Go to canvas an import, then come back here and continue...

# delete all the CSV files in this directory so we have a clean slate ready for next week
map(fs::dir_ls( glob = "*.csv"), fs::file_delete)