UC Salary Pipeline

This script townloads University of California employee wage data and classifies every employee into an analytical category (Senate Faculty, Athletics, Senior Management, etc.), producing per-employee, aggregate, and top-earner CSVs.

What the script does

1. Downloads raw wage JSON for every requested campus/year from ucinvestments/UC-wages, a public dataset (not an API) sourced from UC Annual Wage, covering 2010 onward for all 10 campuses plus UCOP, ASUCLA, and UC SF Law.
2. Classifies every employee record by job title (and, for non-academic staff, gross pay) into one of 8 categories, and separately flags whether they work at a UC medical center. Categorization is based on job description, and probably needs some work and will never be complete for the medical centers really.
3. Outputs to CSV plus a formatted summary table printed to the terminal.

Architecture

the software has three files, each has one job:

• classifier.py — the classification engine. give it a job title string (and optionally a gross pay figure and campus code), get back a category and/or a medical-center flag. It's kept importable on its own — python -c "import classifier" works. See Classification logic below for how it works.

• uc_wages_downloader.py — fetches raw per-campus/per-year JSON files from the UC-wages GitHub repo into a local cache directory (uc_wages_data/ by default).

• uc_salary_pipeline.py — main shebang. Calls the downloader, reads the cached JSON, maps UC-wages' field names onto what classifier.py expects, calls classifier.classify_title() / classifier.is_medical_center() per record, and writes the output CSVs.

Quick start

# Download every campus, every available year, and classify everything
python uc_salary_pipeline.py

# Get specific slice per year and campus:
python uc_salary_pipeline.py --years 2023 2024 --campuses UCB UCLA

# Skip downloading the data (script also skips existing data if it's there)
python uc_salary_pipeline.py --skip-download

# Full example
python uc_salary_pipeline.py --years 2024 --campuses UCB UCLA UCSF \
    --aggregate-output agg.csv --group-by year campus medical_center \
    --top-earners-output top.csv --top-n 20

Downloaded JSON is cached in uc_wages_data/ (gitignored) and reused on every subsequent run

Runtime flags for uc_salary_pipeline.py

Flag	Default	What it does
--years Y [Y ...]	2010–current year	Years to process.
--campuses C [C ...]	all	Campuses to process. Choices: ASUCLA UCB UCD UCI UCLA UCM UCR UCSD UCSF UCSB UCSC UCOP UC_SF_LAW.
--data-dir DIR	uc_wages_data	Local cache directory for downloaded JSON.
--output PATH	uc_salary_classified.csv	Per-employee output CSV path.
--aggregate-output PATH	(off)	If given, also write a category-level rollup CSV (employee count, total/mean/median gross pay).
--group-by [DIM ...]	year	Extra breakdown dimensions for --aggregate-output and --top-earners-output, alongside category. Choices: year, campus, medical_center. Pass none to collapse to one row per category; pass all three for the finest breakdown.
--top-earners-output PATH	(off)	If given, also write the top --top-n gross-pay earners per category (respects --group-by).
--top-n N	10	How many top earners per group, for --top-earners-output.
--skip-download	off	Classify whatever's already cached in --data-dir; never hits the network.
--quiet	off	Suppress the formatted per-year summary table normally printed to stdout.
--senior-threshold N	100000	Gross pay at/above which non-academic staff are SENIOR_PROF_STAFF instead of PROF_STAFF.
--support-threshold N	40000	Gross pay below which non-academic staff are SUPPORT_STAFF instead of PROF_STAFF.
-h, --help	—	Full usage (argparse-generated).

Category labels in every output (e.g. "Senior Prof/Staff (>=$100k)") automatically reflect whatever --senior-threshold/--support-threshold is passed.

Output files

Per-employee CSV (--output, always written)

One row per employee per year:

year, campus, medical_center, title, category, category_label,
gross_pay, regular_pay, overtime_pay, other_pay, first_name, last_name

Aggregate CSV (--aggregate-output, optional)

One row per category (further broken down by --group-by):

[year] [campus] [medical_center] category, category_label,
employee_count, total_gross_pay, mean_gross_pay, median_gross_pay

Top-earners CSV (--top-earners-output, optional)

The top --top-n gross-pay earners per category (also broken down by --group-by), ranked:

category, category_label, rank, year, campus, medical_center, title,
gross_pay, regular_pay, overtime_pay, other_pay, first_name, last_name

Console summary (default; suppress with --quiet)

A formatted table printed to stdout, one per year processed, showing employee count / total pay / median pay per category — a quick sanity check without opening any CSV.

Classification logic

All in classifier.py. Two independent things are computed per employee:

1. Category (classify_title())

Every employee falls into exactly one of 8 categories, like so:

1. STUDENT_TRAINEE — TAs, GSRs, postdocs, residents, interns, student workers
2. ATHLETICS — coaches and athletics staff
3. SENIOR_MANAGEMENT — Chancellor/Provost/VP/Dean/Executive Director tier
4. SENATE_FACULTY — ladder professors and other Senate-membership titles
5. NON_SENATE_ACADEMIC — lecturers, adjuncts, HS clinical professors, project scientists, librarians, academic coordinators, etc.
6. SENIOR_PROF_STAFF / PROF_STAFF / SUPPORT_STAFF — everyone else (non-academic staff), split purely by gross pay against --senior-threshold / --support-threshold

Titles are matched with regex against a normalized (uppercased, whitespace-collapsed, abbreviation-expanded) form of the raw title string — see normalise(). The category, its human-readable label, and the pay thresholds are all defined at the top of classifier.py.

2. Medical center flag (is_medical_center())

• (any campus): titles containing MED CTR (hospital facilities/exec titles), the HS Clinical Professor/Instructor series, or bare -HCOMP ladder titles.
• Five campuses that operate a UC-owned academic medical center (UCSF, UCLA, UCD, UCI, UCSD): generic clinical-workforce titles — physicians, nurses, dentists, pharmacists, therapists, lab/rad techs, etc. These titles are identical at every campus (Student Health Services exists everywhere), so outside the 5 hospital campuses they can't be reliably told apart from student health staff by title text alone.
• Excluded any title containing SHS (Student Health Services), even at UCSF

This won't catch things like a bare DEAN title that happens to be the Dean of Medicine.

Data notes

• UC-wages has no benefits field (unlike the old California State Controller GCC data this tool previously used), so gross_pay is the only pay figure available — regular_pay + overtime_pay + other_pay... might be useful in the future if we want benefits data to go back to the GCC scraper.
• Employee names are pre-redacted by the upstream source for some records (firstname/lastname show as *****)
• The full dataset is ~1.3GB across ~170 files. uc_wages_data/ is gitignored; run the script to download the data first time