Academic Record Normalizer

A small, audit-first pipeline for converting heterogeneous undergraduate GPA records into a consistent 0-4 band. It selects the first explicitly conferred undergraduate degree, applies exact school-level conversion rules when available, falls back to validated country medians, and routes uncertain records to human review.

No real applicant data is included. The examples use fictional identifiers and institutions.

Why audit-first

Academic records contain incompatible scales, incomplete locations, duplicated school names, and ambiguous conversion tables. Silently forcing every value onto a 4-point scale creates false precision. This tool therefore returns both a normalized table and review queues for:

• missing, non-numeric, negative, or out-of-range GPAs;
• missing countries or conversion thresholds;
• likely scale mismatches;
• duplicate conversion-master candidates;
• records without an explicitly conferred undergraduate degree.

School matching is exact after case, punctuation, ampersand, Unicode, and whitespace normalization. It deliberately avoids fuzzy matching because a plausible but wrong institution match is worse than a review flag.

Quick start

Requires Python 3.10 or newer.

python -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.txt

python academic_record_normalizer.py \
  --applications examples/applications.csv \
  --conversion-master examples/conversion_master.csv \
  --slots 2 \
  --output-dir results

Outputs:

• normalized_records.csv: one row per applicant ID with source and status;
• records_needing_review.csv: all unresolved conversions;
• duplicate_conversion_candidates.csv: conversion-master collisions.

Input schemas

Applications require a unique applicant_id plus repeated slot columns:

ug_conferred_1, ug_school_1, ug_gpa_1, ug_location_1
ug_conferred_2, ug_school_2, ug_gpa_2, ug_location_2
...

Set --slots to the number of complete slot groups. Conferred values accept explicit forms such as yes/no, true/false, and 1/0; unknown values are not guessed.

The conversion master requires:

Column	Meaning
university	Institution name
country	Country used for fallback grouping
us_undergraduate	Whether the source is already a U.S.-style GPA
a, b, c, d	Strictly descending lower bounds for letter-grade bands

For non-U.S. records, the output maps A/B/C/D/below-D bands to 4/3/2/1/0. This is a categorical normalization, not a claim of transcript equivalence.

Safeguards and limitations

• U.S.-style values above 4.33 are flagged instead of clipped.
• Conversion rows with incomplete or non-descending thresholds cannot drive normalization.
• Country inference recognizes only explicit comma-delimited countries or known U.S. postal codes.
• Country fallbacks are medians of valid school rules and remain a coarse last resort.
• The pipeline does not rank applicants, assign school tiers, infer merit, or make admissions decisions.

GPA conversion policies differ by institution and jurisdiction. Every conversion master needs domain-owner approval, versioning, and periodic review. Sensitive source files and outputs should be encrypted, access-controlled, retained only as necessary, and never committed to Git.

Tests

python -m unittest discover -s tests -v

The suite covers boolean parsing, Unicode institution normalization, state/country inference, duplicate rule selection, primary-degree selection, country fallback, scale mismatches, identifier integrity, and CSV round-tripping. GitHub Actions runs it on Python 3.10 and 3.13.

Publication boundary

This repository is a privacy-safe reconstruction of an internal data-cleaning workflow. It excludes real names, emails, application files, institution-specific ranking logic, source spreadsheets, notebooks, and generated admissions outputs. See NOTICE.

License

Released under the MIT License.