kahoot-name-matching

name

kahoot-name-matching

description

Build students/students.csv for the kahoot-merger tool by matching a student roster (e.g. Hebrew first/last name + ID) against the login names students actually typed into Kahoot reports, then produce an HTML sanity-check table for the instructor to review before grading. Trigger when the user has a roster in one script/language and Kahoot report logins in another (or just messy/typo'd logins) and asks to build or update students.csv, match student names to Kahoot logins, or reconcile a class list against Kahoot data.

Kahoot Name Matching

Builds students/students.csv (the file merge_kahoots.py / merge_kahoot_functions.py read via get_id_table) by matching every student in a roster to the login name(s) they actually used in the Kahoot reports — even when the roster is in a different script (e.g. Hebrew) than the Kahoot logins (usually transliterated English), and even though real students never follow the "first.last" naming convention perfectly.

Do this work directly (read the roster, read the actual report files, reason about each match) rather than relying purely on a fuzzy-matching heuristic — name transliteration has too many genuinely ambiguous cases (e.g. two different students both plausibly "Amit", a "Roni" that could be two different people) for a script to resolve alone. A heuristic clustering step narrows the problem; your own judgment (and the user's) resolves the rest.

Step 0 — Locate the inputs

• The roster: a spreadsheet or CSV with (at minimum) first name, last name, and student ID. It may be in a different alphabet/script than the Kahoot logins (this project's roster is Hebrew).
• The Kahoot report files: reports/*.xlsx, each with a Final Scores sheet, header on row 3 (0-indexed row 2), with a Player column.
• The target output: students/students.csv with header Name,ID — Name is space-separated tokens matched against the Kahoot login split on [._\s-]+ (case-insensitively).

If the roster also contains an attendance sheet or other per-student data, that's a separate concern — this skill only covers name matching.

Step 1 — Extract every unique login name from the reports

import pandas as pd
from os import listdir
players = set()
for report in listdir('reports'):
    if not report.endswith('.xlsx'):
        continue
    df = pd.read_excel('reports/'+report, sheet_name='Final Scores', header=2, usecols=['Player'])
    for p in df['Player'].dropna():
        players.add(str(p).strip())

Check the actual column header names first (df.columns.tolist() on one report with header=2, nrows=1). Kahoot has changed its export format between years (e.g. "Total Score (points)" vs "Total score (points)"); a hardcoded constant in merge_kahoot_functions.py that doesn't match the real header will fail with a cryptic pandas usecols ValueError. Fix the constant in the merger code if it's stale — don't just work around it here.

Step 2 — Cluster logins by normalized token-set

Mimic exactly what get_players_and_scores/get_id_table do when matching, so a cluster found here is guaranteed to match once written to the CSV:

import re
clusters = {}
for p in players:
    tokens = re.split(r'[._\s-]+', p.lower().strip('.# \t\n\r'))
    tokens = [t for t in tokens if t]
    key = ' '.join(sorted(frozenset(tokens)))   # order-independent key
    clusters.setdefault(key, []).append(p)

This collapses capitalization and ordering variants (Adi.Green/ADI.GREEN/adi green) into one cluster automatically. What's left is typos across clusters (Adi.Green vs Adi.Gree vs Adi.Greem — three separate clusters, same person) and genuine ambiguity — that's where your judgment comes in next.

Step 3 — Match each roster entry to cluster(s)

For each roster row, work out the transliteration/spelling a student would plausibly have used, then find the cluster(s) that match. Go through the entire roster, not just the easy cases — a systematic pass (row by row) catches far more than pattern-matching only the obvious hits, and prevents silently losing students who genuinely have zero matches (they still need a CSV row so the "missing/absent" logic in the merger picks them up).

Track three buckets as you go:

1. Matched — roster ID ↔ one or more login clusters.
2. Never appeared — roster ID with no plausible match in any cluster (fine — they'll show as 0/absent in the final grade — but still needs a fallback CSV row with your best-guess name and their ID).
3. Unmatched/junk clusters — logins that don't confidently belong to anyone (stray numbers, bare nicknames like "avi", single ambiguous first names that could be 2+ students). Do not force a guess here — report these to the user rather than silently assigning them; a wrong assignment gives one student credit that isn't theirs and steals it from another.

Watch for genuinely ambiguous cases explicitly (e.g. a bare first name with no surname when the roster has that surname policy) — that's a policy decision for the instructor ("should an unlabeled login count at all?"), not something to resolve unilaterally. Ask.

Step 4 — Build students.csv with the canonical name FIRST per student

get_id_table keeps only the first CSV row it sees for a given ID as that student's displayed First/Last name (ID_HASH[this_id]=this_name, guarded by if not this_id in ID_HASH). All other rows for the same ID are alias rows used only for matching, not display.

Common bug: if you generate cluster-derived rows by capitalizing the alphabetically-sorted cluster key (e.g. cluster key "barak rom" → "Barak Rom"), the displayed First/Last name can come out reversed from the student's actual name order (real name: Rom Barak; cluster key sorted the tokens alphabetically and lost the original order). Avoid this by building an explicit ID -> "Correct First Last" canonical mapping from the roster (using your knowledge of the roster's real name order) and writing that row before any alias rows for that ID:

rows = []
for sid in all_roster_ids:
    rows.append((canonical_name[sid], sid))          # primary row: correct order
    for alias in aliases_by_id.get(sid, []):
        if alias.lower() != canonical_name[sid].lower():
            rows.append((alias, sid))                 # secondary: matching only

Also restore this guard in get_id_table if it's missing — a single-word alias (e.g. bare "Rom") will crash write_out_excel (IndexError: list index out of range on ID_HASH[id][1]) without it:

if len(this_name) == 1:
    this_name.append('')

And the frozenset built from the CSV name must be lowercased to match the (already-lowercased) Kahoot-side frozenset — frozenset([str(item).lower() for item in this_name if item]). Without .lower() here, every single student fails to match, silently, because CSV names are typically capitalized ("Rom Barak") while Kahoot logins are lowercased before comparison.

Step 5 — Verify full coverage

roster_ids = {...}   # all IDs from the roster
csv_ids = {row['ID'] for row in students_csv_rows}
assert roster_ids == csv_ids   # missing = students.csv rows accidentally dropped

Any missing ID means a roster row got lost somewhere in Step 3/4 — go back and add it (even as a "never appeared" fallback row), don't leave it out.

Step 6 — Produce a sanity-check artifact

Render an HTML table (use the Artifact tool) with columns: student ID, original-script name (set direction: rtl for Hebrew/Arabic), and the matched Kahoot login variant(s) — plus a visible list of unmatched/junk clusters. This lets the instructor actually eyeball the mapping before it's used for real grades; do not skip this step or treat automatic matching as final. Wait for the user to flag any corrections (they know their students; you're pattern-matching transliterations) before the resulting students.csv is used to generate real grades.

Step 7 — Apply corrections and never commit real data

When the user corrects a mapping, update it and regenerate — don't just patch the CSV by hand, regenerate from the mapping table so re-running later is trivial when the roster changes.

students/students.csv contains real student names and IDs — verify it is covered by .gitignore (along with reports/*.xlsx, any attendance file, and generated merged_kahoots*.xlsx output) before anything gets committed. If the repo ships an example file for new users, keep it as a separate tracked file (e.g. students/students_example.csv) with placeholder data, distinct from the real gitignored students.csv.