uc_salary_aggregator/README.md

# 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](https://github.com/ucinvestments/UC-wages), a
   public dataset (not an API) sourced from
   [UC Annual Wage](https://ucannualwage.ucop.edu/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](#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

```bash
# 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. `gross_pay` is the
  only pay figure available — `regular_pay + overtime_pay + other_pay`
- GCC data does include benefits, and can be used for calculation
- 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

## License
## License

Licensed under the [GNU Affero General Public License v3.0 or later](LICENSE) (AGPL-3.0-or-later).

The license covers the code only, and not the underlying data downloaded from UCOP or the State of California Controller's Office.

Copyright 2026, University of California Faculty Association