Grading curve

Grading curve#

This course has a curve due to SIPA policy:

Grades submitted for SIPA core courses must have an average GPA between 3.2 and 3.4, with the goal being 3.3.

How course grades work#

  1. The grades are computed using the weights listed in the syllabus.

  2. The percentage required for all the letter grades is adjusted up or down as necessary to hit the target GPA range across both sections.

  3. The letter grades are uploaded to SSOL.

The min_score column of the new cutoffs shows the current minimum Total percentage required for each letter grade.

Methodology#

The rest of this notebook shows how the grade cutoffs are computed. 

MIN_AVG_GPA = 3.2
MAX_AVG_GPA = 3.4

Load current scores#

import pandas as pd

grades = pd.read_excel("/Users/afeld/Library/CloudStorage/GoogleDrive-alf2215@columbia.edu/My Drive/Computing in Context/INAFU6006 Final grade.xlsx")
grades = grades.sort_values("Final Grade")

Distribution#

import plotly.express as px

fig = px.histogram(
    grades,
    x="Final Grade",
    title="Distribution of the overall grades",
    labels={"Final Grade": "Final Grade (percent)"},
)
fig.update_layout(yaxis_title_text="Number of students")
fig.show()

Match to letter grades / GPAs#

Creating the grading notation table in Pandas:

letter_grade_equivalents = pd.DataFrame(
    index=["A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D", "F"],
    data={"gpa": [4.00, 3.67, 3.33, 3.00, 2.67, 2.33, 2.00, 1.67, 1.00, 0.00]},
)

Assign starting minimum scores, roughly based on the Default Canvas Grading Scheme:

letter_grade_equivalents["min_score"] = [94.0, 90.0, 87.0, 84.0, 80.0, 77.0, 74.0, 70.0, 60.0, 0.0]
letter_grade_equivalents
gpa min_score
A 4.00 94.0
A- 3.67 90.0
B+ 3.33 87.0
B 3.00 84.0
B- 2.67 80.0
C+ 2.33 77.0
C 2.00 74.0
C- 1.67 70.0
D 1.00 60.0
F 0.00 0.0

Adjust cutoffs#

Raise or lower the minimum scores for each grade (not including F) until the average GPA is in the acceptable range.

# merge_asof() needs columns sorted ascending
orig_grade_cutoffs = letter_grade_equivalents.sort_values(by="min_score")
grade_cutoffs = orig_grade_cutoffs.copy()

grades_to_adjust = grade_cutoffs.index != "F"

adjustment = 0
STEP_SIZE = 0.1

while True:
    grade_cutoffs.loc[grades_to_adjust, "min_score"] = (
        orig_grade_cutoffs[grades_to_adjust]["min_score"] + adjustment
    )

    # make the letter grades a column so they show up in the merged DataFrame
    grade_cutoffs_with_letters = grade_cutoffs.reset_index().rename(
        columns={"index": "letter_grade"}
    )

    # find the letter grade / GPA for each student
    adjusted_grades = pd.merge_asof(
        grades,
        grade_cutoffs_with_letters,
        left_on="Final Grade",
        right_on="min_score",
        direction="backward",
    )

    new_mean = adjusted_grades["gpa"].mean()
    print(f"Adjustment: {adjustment:+.1f}, Average: {new_mean:.3f}")

    # check if we've hit the target range
    if MIN_AVG_GPA <= new_mean < MAX_AVG_GPA:
        # success
        break
    elif new_mean >= MAX_AVG_GPA:
        # raise
        adjustment += STEP_SIZE
    else:  # new_mean < MIN_AVG_GPA:
        # lower
        adjustment -= STEP_SIZE

Adjustment: +0.0, Average: 3.493
Adjustment: +0.1, Average: 3.483
Adjustment: +0.2, Average: 3.472
Adjustment: +0.3, Average: 3.462
Adjustment: +0.4, Average: 3.457
Adjustment: +0.5, Average: 3.452
Adjustment: +0.6, Average: 3.452
Adjustment: +0.7, Average: 3.447
Adjustment: +0.8, Average: 3.447
Adjustment: +0.9, Average: 3.442
Adjustment: +1.0, Average: 3.437
Adjustment: +1.1, Average: 3.426
Adjustment: +1.2, Average: 3.411
Adjustment: +1.3, Average: 3.406
Adjustment: +1.4, Average: 3.401
Adjustment: +1.5, Average: 3.396

Confirm the A cutoff is still achievable:

assert grade_cutoffs.at["A", "min_score"] <= 100

New cutoffs#

grade_cutoffs.sort_values("min_score", ascending=False)
gpa min_score
A 4.00 95.5
A- 3.67 91.5
B+ 3.33 88.5
B 3.00 85.5
B- 2.67 81.5
C+ 2.33 78.5
C 2.00 75.5
C- 1.67 71.5
D 1.00 61.5
F 0.00 0.0

Check results#

Double-check the new average is in line with policy:

assert MIN_AVG_GPA <= new_mean < MAX_AVG_GPA, f"{new_mean} not in acceptable range"

new_mean

np.float64(3.395538461538462)

fig = px.histogram(adjusted_grades, x="letter_grade", title="Distribution of letter grades")
fig.update_layout(yaxis_title_text="Number of students")
fig.show()

Export#

Format needed by SSOL.

ssol = adjusted_grades.rename(columns={"SID": "UNI", "letter_grade": "grade"})
ssol = ssol[["UNI", "grade"]].sort_values("UNI")

ssol.to_csv("tmp/grades.csv", index=False)
print("Done")

Done
Contents