How to Parse a Moodle Gradebook with Pandas

A Moodle gradebook is not a table — it is a tree. Where Canvas hands you a flat list of submissions, Moodle returns a nested hierarchy of grade categories that contain grade items that finally contain a per-student grade, and each level carries its own aggregation rule, weight, visibility flag, and grade-display type. Flatten that tree naively and you get columns that collapse a course total onto the same row as the quiz that fed it, letter grades silently cast to NaN, and hidden items inflating every denominator. This guide walks a deterministic procedure that turns either the gradereport_user_get_grade_items web-service payload or the raw mdl_grade_grades/mdl_grade_items join into one tidy, analysis-ready pandas DataFrame — one row per student-per-item — while resolving idnumber to a canonical key and tokenizing it before anything is serialized. It builds on the Moodle Course & User Schema entity model and the identifier work in mapping Moodle user profiles to SIS IDs.

Prerequisites

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

• Python 3.10+ (the code uses str | None unions and modern type hints).
• pandas==2.2.* and requests==2.32.* in the virtualenv. The nullable Int64/Float64/boolean/string dtypes used here require pandas 1.0+, but 2.2 is assumed.
• A Moodle web-service token with the gradereport_user_get_grade_items and core_enrol_get_enrolled_users functions enabled on its external service, exported as MOODLE_WS_TOKEN. Provision it under Site administration → Server → Web services.
• The numeric courseid you intend to extract, and the course’s idnumber (the SIS course key) so the composite key stays stable across term rollovers.
• A TOKEN_SALT environment variable holding a per-institution secret — the salt for the student pseudonym must live inside the compliance boundary, never in code.
• Upstream data shape: gradereport_user_get_grade_items returns {"usergrades": [{"userid", "userfullname", "gradeitems": [{"id", "itemtype", "itemname", "graderaw", "gradeformatted", "grademax", "grademin", "gradedatesubmitted", "hidden", "excluded", ...}]}]}. Each user carries the whole item tree, categories included.

Step-by-step implementation

1. Pull the grade report per user, not per course. Unlike Canvas’s bulk submissions endpoint, gradereport_user_get_grade_items is scoped to a single userid (omit it and a non-admin token sees only itself). Enumerate the roster first with core_enrol_get_enrolled_users, which resolves roles through Moodle’s contextlevel=50 graph for you, then call the grade report once per student. If you extract from a read replica instead, the equivalent is mdl_grade_grades joined to mdl_grade_items ON gi.id = gg.itemid filtered by gi.courseid.

2. Walk the gradeitems array and record where each item sits in the tree. Every element carries an itemtype (course, category, mod, manual) and, for module items, an itemmodule (quiz, assign). The itemtype='course' row is the course total and itemtype='category' rows are sub-totals — keep them, but tag them so a later aggregation does not double-count a category against the items that feed it, exactly as the Moodle Course & User Schema describes.

3. Split numeric grades from letter and scale grades. graderaw is the numeric mark (or null); gradeformatted is what the UI shows — which may be a letter (B+), a scale word (Proficient), or a percentage string. Coercing gradeformatted to float silently turns every letter/scale grade into NaN. Keep graderaw as nullable Float64 and gradeformatted as a string column so both survive.

4. Preserve hidden and excluded as boolean columns, do not drop the rows. Moodle omits hidden=1 and excluded=1 items from its own course total, but an analyst may still need to see them. Carry them as rows with explicit flags and let the downstream weighted grade calculation engines decide what enters an aggregate.

5. Coerce to nullable dtypes so ungraded stays <NA>. An ungraded item has graderaw = null; a scored-zero item has graderaw = 0. Use capitalized nullable dtypes (Float64, Int64, boolean, string) so the two never collapse — conflating them fabricates failing grades for unsubmitted work.

6. Resolve idnumber to a canonical key, then tokenize before serialization. The registrar key lives in idnumber (falling back to a custom profile field), never in mdl_user.id. Hash the resolved key with a salted SHA-256 into a stu_… token and drop the raw identifier before the frame leaves memory — the same surrogate-key idea behind mapping Moodle user profiles to SIS IDs.

Complete runnable code block

import os
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("moodle_gradebook_parser")

WS = "https://moodle.example.edu/webservice/rest/server.php"
TOKEN = os.environ["MOODLE_WS_TOKEN"]
SALT = os.environ["TOKEN_SALT"].encode()  # kept inside the compliance boundary

# Strict, nullable schema: <NA> is preserved so "ungraded" never becomes 0.
SCHEMA: dict[str, str] = {
    "item_id": "Int64",
    "item_name": "string",
    "item_type": "string",       # course | category | mod | manual
    "item_module": "string",     # quiz | assign | None
    "category_id": "Int64",
    "grade_raw": "Float64",      # numeric mark, <NA> when ungraded
    "grade_formatted": "string", # letter / scale / percentage as shown
    "grade_max": "Float64",
    "grade_min": "Float64",
    "percent": "Float64",
    "hidden": "boolean",
    "excluded": "boolean",
    "aggregable": "boolean",     # False for hidden/excluded/subtotal rows
}


def tokenize(sis_id: str) -> str:
    """Salted, deterministic pseudonym — never store the raw idnumber downstream."""
    return "stu_" + hashlib.sha256(SALT + sis_id.strip().encode()).hexdigest()[:24]


def call(wsfunction: str, **params) -> dict | list:
    payload = {"wstoken": TOKEN, "wsfunction": wsfunction,
               "moodlewsrestformat": "json", **params}
    resp = requests.post(WS, data=payload, timeout=30)
    resp.raise_for_status()
    data = resp.json()
    if isinstance(data, dict) and data.get("exception"):
        raise RuntimeError(f"{wsfunction}: {data.get('errorcode')} {data.get('message')}")
    return data


def _to_float(value) -> float | None:
    if value in (None, "", "-"):
        return None
    try:
        return float(value)
    except (TypeError, ValueError):
        return None


def parse_gradebook(course_id: int) -> pd.DataFrame:
    """Flatten Moodle's grade tree into one tidy row per student-per-item."""
    students = [
        u for u in call("core_enrol_get_enrolled_users", courseid=course_id)
        if any(r.get("shortname") == "student" for r in u.get("roles", []))
    ]
    rows: list[dict] = []
    for u in students:
        sis_id = (u.get("idnumber") or "").strip()
        if not sis_id:                      # quarantine rows with no SIS key
            logger.warning("user %s has no idnumber; skipped", u.get("id"))
            continue
        student_token = tokenize(sis_id)
        report = call("gradereport_user_get_grade_items",
                      courseid=course_id, userid=u["id"])
        for grades in report.get("usergrades", []):
            for gi in grades.get("gradeitems", []):
                gmax, gmin = _to_float(gi.get("grademax")), _to_float(gi.get("grademin")) or 0.0
                raw = _to_float(gi.get("graderaw"))
                pct = None
                if raw is not None and gmax and (gmax - gmin):
                    pct = round((raw - gmin) / (gmax - gmin) * 100, 2)
                hidden = bool(gi.get("hidden"))
                excluded = bool(gi.get("excluded"))
                subtotal = gi.get("itemtype") in ("course", "category")
                rows.append({
                    "student_token": student_token,
                    "item_id": gi.get("id"),
                    "item_name": gi.get("itemname") or gi.get("itemtype"),
                    "item_type": gi.get("itemtype"),
                    "item_module": gi.get("itemmodule"),
                    "category_id": gi.get("categoryid"),
                    "grade_raw": raw,
                    "grade_formatted": gi.get("gradeformatted"),
                    "grade_max": gmax,
                    "grade_min": gmin,
                    "percent": pct,
                    "hidden": hidden,
                    "excluded": excluded,
                    "aggregable": not (hidden or excluded or subtotal),
                })
    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})
    ordered = ["student_token", *[c for c in SCHEMA if c in df.columns]]
    return df[ordered]


if __name__ == "__main__":
    frame = parse_gradebook(course_id=int(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[["item_id", "student_token"]].drop_duplicates().shape[0] — one row per (item_id, student_token).
• No raw identifiers leaked. assert "idnumber" not in frame.columns and assert frame["student_token"].str.startswith("stu_").all().
• Letter/scale grades survived. Pick a scale item and assert frame.loc[mask, "grade_formatted"].notna().all() while frame.loc[mask, "grade_raw"] may be <NA> — proof the string column was not force-coerced.
• Null preserved, not zeroed. For an ungraded item, assert pd.isna(frame.loc[mask, "grade_raw"]).all(); a 0.0 here is the classic ungraded-as-zero bug.
• Aggregable set excludes noise. assert not frame.loc[frame["hidden"] | frame["excluded"], "aggregable"].any() and assert not frame.loc[frame["item_type"].isin(["course", "category"]), "aggregable"].any().
• Dtypes are nullable. assert frame["grade_raw"].dtype.name == "Float64" and assert frame["hidden"].dtype.name == "boolean"; lowercase float64/bool means a null slipped through coerced.

Troubleshooting

• invalidtoken / accessexception from call(). The web-service token lacks the function on its external service, or the token’s user has no grading rights on the course. Add gradereport_user_get_grade_items to the service and confirm the user holds a teacher/manager role, then retry.
• Every grade_formatted is NaN after astype. You mapped grade_formatted to Float64. Letter and scale grades are strings — keep the column as string and only coerce grade_raw to Float64.
• Course totals double-count the course grade. A .groupby("student_token")["percent"].mean() that includes itemtype in ('course','category') rows folds subtotals back into the average. Filter on frame["aggregable"] before any aggregation.
• All students collapse onto one stu_ token. Blank idnumber values hash to the same bucket. The script skips empty keys; if you extract via SQL, add WHERE u.idnumber <> '' and quarantine the rest rather than tokenizing "".
• percent is <NA> for scale items that clearly have a grade. Scale grades carry a graderaw index into mdl_scale, not a points value, so grademax/grademin do not describe a percentage. Read the scale label from grade_formatted and leave percent null for itemmodule-less scale items.
• hidden items still appear in the instructor-facing total but not yours (or vice versa). Moodle hides an item from students yet may still count it for staff depending on hidden timing. Treat hidden and excluded as data, not as a delete — carry both flags and let the aggregation layer decide, matching Moodle’s own course-total behaviour.

Related

• Moodle Course & User Schema — the parent entity model, tables, and web-service endpoints this parser consumes.
• Mapping Moodle User Profiles to SIS IDs — resolving idnumber and custom profile fields into the canonical key the token derives from.
• Weighted Grade Calculation Engines — replaying Moodle’s aggregation over the aggregable rows this frame marks.

Part of: Moodle Course & User Schema