How to Parse Blackboard Grade Data with Pandas

Blackboard Learn splits a gradebook across two endpoints that you must join yourself: the columns resource defines what is being graded — its name, points possible, and grading type — while the per-column users resource holds how each student did. Neither is warehouse-ready. Each arrives inside a paging envelope with a results array and an optional cursor, every grade value hides behind a dotted path, and a single column can report a raw score, a percentage, a letter, or free text depending on its grading.type. Flatten that without branching on type and you turn Complete into NaN and misread a 0–100 percentage as raw points. This guide walks a deterministic procedure that turns the column definitions and their user grades into one tidy pandas DataFrame — one row per student-per-column — while substituting the SIS externalId and tokenizing it before serialization. It builds on the Blackboard REST API Architecture resource model and the credential flow in the Blackboard Learn REST API authentication guide.

Blackboard gradebook parsing pipeline A six-stage pipeline from Blackboard's paged gradebook endpoints to a tidy DataFrame. Stage one walks the gradebook columns endpoint following paging.nextPage until it is absent. Stage two, for each column, walks the column's users endpoint the same way to collect per-student grades. Stage three flattens the dotted envelope paths with explicit get chains rather than positional access. Stage four branches on grading.type so score, percentage, letter, and complete-incomplete grades are read into the right column instead of all forced to float. Stages five and six sit below the FERPA minimization boundary: stage five substitutes the SIS externalId and hashes it with a salted SHA-256 into a student token, and stage six emits a tidy DataFrame keyed on the composite grain of column id and student token. nextPage FERPA minimization boundary 1 Walk gradebook columns follow paging.nextPage until absent 2 Walk each column's users columns/{id}/users → per-student grade rows 3 Flatten dotted paths with .get() score.possible, displayGrade.score — absent keys omitted 4 Branch on grading.type Score / Percentage / Text / CompleteIncomplete 5 Substitute externalId & tokenize salted SHA-256 → stu_… · drop primaryId + PII 6 Tidy DataFrame grain = (column_id, student_token) · analysis-ready

Prerequisites

Confirm each item before running the procedure — the script assumes every one of them is in place.

Step-by-step implementation

1. Walk the columns endpoint by following paging.nextPage, not by computing offsets. Blackboard returns a results array and, only when more rows exist, a paging.nextPage relative URL. Follow that URL verbatim — the server may cap limit below what you asked — and treat its absence as the sole stop condition. A short results page is not end-of-data, the same contract described in pagination strategies for bulk exports.

2. For each column, walk its users sub-resource the same way. The columns/{columnId}/users endpoint returns one grade row per enrolled student for that column. Join it back to the column you already hold in memory so each grade carries its score.possible and grading.type — a bare score is meaningless without them, as the Blackboard REST API Architecture explains.

3. Flatten the dotted envelope with explicit .get() chains. A column’s maximum is score.possible; a grade sits at displayGrade.score or displayGrade.text. Blackboard omits absent keys rather than emitting null, so positional access or assuming a key exists throws KeyError mid-batch. Chain .get() with defaults at every level.

4. Branch on grading.type before interpreting the grade. A Score column reports raw points against score.possible; a Percentage column reports 0–100 directly; Text and CompleteIncomplete carry no numeric score at all. Normalizing every column with one formula produces plausible-but-wrong percentages — carry the grading type as context and route text grades to their own column.

5. Coerce to nullable dtypes so ungraded stays <NA>. An ungraded attempt omits score entirely. Use capitalized nullable dtypes (Float64, Int64, boolean, string) so a missing score stays <NA> instead of becoming 0.0 — conflating the two deflates every affected student’s grade.

6. Substitute the SIS externalId, then tokenize before serialization. The primaryId is tenant-internal and meaningless downstream; the externalId is the registrar key that must survive. Hash it with a salted SHA-256 into a stu_… token and drop both the primaryId and the raw externalId before the frame leaves memory — the same surrogate-key idea behind cross-LMS student ID mapping.

Complete runnable code block

python
import os
import time
import hashlib
import logging
import pandas as pd
import requests

logging.basicConfig(level=logging.INFO, format="%(asctime)s | %(levelname)s | %(message)s")
logger = logging.getLogger("blackboard_grade_parser")

BASE = "https://learn.example.edu/learn/api/public"
SALT = os.environ["STUDENT_ID_SALT"]  # from secrets manager, never hardcoded

# Strict, nullable schema: <NA> is preserved so "ungraded" never becomes 0.
SCHEMA: dict[str, str] = {
    "column_id": "string",
    "column_name": "string",
    "grading_type": "string",     # Score | Percentage | Text | CompleteIncomplete | Letter
    "score": "Float64",           # numeric mark, <NA> when ungraded
    "score_possible": "Float64",
    "percent": "Float64",
    "text_grade": "string",       # letter / complete / free text, never coerced
    "status": "string",
}


def tokenize(external_id: str) -> str:
    """Deterministic, salted FERPA-safe surrogate for the SIS externalId."""
    return "stu_" + hashlib.sha256(f"{SALT}:{external_id}".encode()).hexdigest()[:32]


def get_paginated(session: requests.Session, path: str) -> list[dict]:
    """Follow Blackboard's paging.nextPage cursor until it is absent."""
    rows: list[dict] = []
    url = f"{BASE}{path}"
    root = BASE.rsplit("/public", 1)[0]  # nextPage is /learn/api/public/... relative
    while url:
        resp = session.get(url, timeout=30)
        if resp.status_code == 429:
            wait = int(resp.headers.get("Retry-After", "5"))
            logger.warning("throttled; sleeping %ss", wait)
            time.sleep(wait)
            continue
        resp.raise_for_status()
        body = resp.json()
        rows.extend(body.get("results", []))
        nxt = body.get("paging", {}).get("nextPage")
        url = f"{root}{nxt}" if nxt else None
    return rows


def parse_gradebook(token: str, course_id: str) -> pd.DataFrame:
    """Join gradebook columns to per-user grades into one tidy DataFrame."""
    session = requests.Session()
    session.headers.update({"Authorization": f"Bearer {token}"})

    # Resolve user primaryId -> SIS externalId once, up front.
    ext: dict[str, str] = {}
    for m in get_paginated(session, f"/v1/courses/{course_id}/users"):
        if m.get("courseRoleId") != "Student":
            continue
        u = m.get("user", {})
        ext[m["userId"]] = (u.get("externalId") or u.get("studentId") or "").strip()

    rows: list[dict] = []
    for col in get_paginated(session, f"/v2/courses/{course_id}/gradebook/columns"):
        possible = col.get("score", {}).get("possible")
        gtype = col.get("grading", {}).get("type", "Score")
        col_id = col["id"]
        for g in get_paginated(session, f"/v2/courses/{course_id}/gradebook/columns/{col_id}/users"):
            sis = ext.get(g.get("userId"))
            if not sis:                         # skip instructors and unmapped rows
                continue
            display = g.get("displayGrade", {})
            score = display.get("score")
            text = display.get("text")
            # Branch on grading type: only numeric columns get a percentage.
            percent = None
            if gtype in ("Score", "Percentage") and score is not None:
                percent = round(score if gtype == "Percentage"
                                else (100 * score / possible if possible else None), 2) \
                          if (gtype == "Percentage" or possible) else None
            rows.append({
                "student_token": tokenize(sis),  # never the raw id
                "column_id": col_id,
                "column_name": col.get("name", ""),
                "grading_type": gtype,
                "score": score,
                "score_possible": possible,
                "percent": percent,
                "text_grade": text if gtype in ("Text", "CompleteIncomplete", "Letter") else None,
                "status": g.get("status"),
            })

    if not rows:
        return pd.DataFrame(columns=["student_token", *SCHEMA])
    df = pd.DataFrame(rows)
    df = df.astype({c: dt for c, dt in SCHEMA.items() if c in df.columns})
    return df[["student_token", *[c for c in SCHEMA if c in df.columns]]]


if __name__ == "__main__":
    frame = parse_gradebook(token=os.environ["BB_TOKEN"], course_id=os.environ["COURSE_ID"])
    logger.info("DataFrame shape: %s", frame.shape)
    print(frame.dtypes)
    print(frame.head())

Verification and output validation

Confirm the parser produced a clean, FERPA-safe frame before handing it downstream:

  • Grain is unique. assert len(frame) == frame[["column_id", "student_token"]].drop_duplicates().shape[0] — one row per (column_id, student_token).
  • No raw identifiers leaked. assert "externalId" not in frame.columns and assert frame["student_token"].str.startswith("stu_").all(); SHA-256 hex truncated to 32 chars keeps the token opaque.
  • Text grades survived. For a CompleteIncomplete column, assert frame.loc[mask, "text_grade"].notna().any() while frame.loc[mask, "score"] is <NA> — proof the string grade was not forced to float.
  • Percentage vs Score not conflated. For a Percentage column, assert (frame.loc[mask, "percent"] == frame.loc[mask, "score"]).all(); for a Score column the percent is derived from score_possible, not copied.
  • Null preserved, not zeroed. For an ungraded row, assert pd.isna(frame.loc[mask, "score"]).all(); a 0.0 here is the classic ungraded-as-zero bug.
  • Dtypes are nullable. assert frame["score"].dtype.name == "Float64" and assert frame["text_grade"].dtype.name == "string".

Troubleshooting

  • 401 Unauthorized partway through pagination. A semester-wide pull outlived the one-hour token TTL. Refresh proactively at ~80% of lifespan as the authentication guide details, rather than reacting to the 401.
  • 404 Not Found on /gradebook/columns. Columns and attempts live at v2 while courses and users live at v1. Hardcoding one version against the wrong resource returns 404. Pin versions per-resource exactly as shown.
  • KeyError inside the flatten step. Blackboard omits absent keys rather than emitting null, so col["score"]["possible"] throws when a column has no points. Use .get("score", {}).get("possible") chains with defaults at every level.
  • A Percentage column reads as an impossible 300%. You applied the Score formula (100 * score / possible) to a column whose score is already 0–100. Branch on grading.type first and copy a Percentage score straight through.
  • All students collapse onto one stu_ token. Manually created accounts often have a null externalId. The script skips empty keys; route those rows to a quarantine table rather than tokenizing "" into a shared bucket.
  • Throttled runs abort during end-of-term reconciliation. Ignoring Retry-After re-trips the tenant limit. The loop honours the header exactly; for wide course fans layer the pacing from handling LMS API rate limits on top.

Part of: Blackboard REST API Architecture