284 lines
9 KiB
Python
284 lines
9 KiB
Python
"""
|
|
gcc_downloader.py
|
|
=================
|
|
Download UC salary data from the California State Controller's Office
|
|
Government Compensation in California (GCC) website.
|
|
|
|
URL pattern (confirmed from browser):
|
|
https://gcc.sco.ca.gov/Reports/GetReport.aspx
|
|
?reportName=ExpEmployees
|
|
&fileType=csv
|
|
¶meterList=Year:YYYY;EntityID:NNNN;
|
|
|
|
No login, no session management, no scraping — plain GET requests.
|
|
|
|
USAGE
|
|
-----
|
|
# Download 2015 and 2024 for all campuses:
|
|
python gcc_downloader.py --years 2015 2024 --out-dir gcc_data
|
|
|
|
# One campus only:
|
|
python gcc_downloader.py --years 2024 --campuses UCLA UCSF
|
|
|
|
# Dry run (print URLs, download nothing):
|
|
python gcc_downloader.py --years 2015 2024 --dry-run
|
|
|
|
OUTPUT
|
|
------
|
|
One CSV per campus per year: gcc_data/GCC_UCLA_2024.csv
|
|
One combined CSV per year: gcc_data/GCC_ALL_2024.csv (adds 'campus' column)
|
|
|
|
The combined file is what uc_salary_pipeline.py expects when called with
|
|
--source gcc (to be wired up next).
|
|
"""
|
|
|
|
import argparse
|
|
import csv
|
|
import io
|
|
import logging
|
|
import sys
|
|
import time
|
|
from pathlib import Path
|
|
|
|
import requests
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# ENTITY MAP (entity IDs confirmed by hand from the GCC website)
|
|
# ---------------------------------------------------------------------------
|
|
ENTITIES = {
|
|
"UCLA": 8241,
|
|
"UCSF": 8246,
|
|
"UCSD": 8245,
|
|
"UCD": 8249,
|
|
"UCI": 8243,
|
|
"UCB": 8239,
|
|
"UCSB": 8247,
|
|
"UCR": 8244,
|
|
"UCSC": 8248,
|
|
"UCM": 8242,
|
|
"UCOP": 8240,
|
|
}
|
|
|
|
GCC_URL = (
|
|
"https://gcc.sco.ca.gov/Reports/GetReport.aspx"
|
|
"?reportName=ExpEmployees"
|
|
"&fileType=csv"
|
|
"¶meterList=Year:{year};EntityID:{entity_id};"
|
|
)
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# LOGGING
|
|
# ---------------------------------------------------------------------------
|
|
logging.basicConfig(
|
|
format="%(asctime)s [%(levelname)s] %(message)s",
|
|
level=logging.INFO,
|
|
handlers=[
|
|
logging.FileHandler("gcc_downloader.log", encoding="utf-8"),
|
|
logging.StreamHandler(sys.stdout),
|
|
],
|
|
)
|
|
log = logging.getLogger(__name__)
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# DOWNLOAD
|
|
# ---------------------------------------------------------------------------
|
|
|
|
def download_csv(campus: str, entity_id: int, year: int,
|
|
out_dir: Path, session: requests.Session,
|
|
retries: int = 3, timeout: int = 120) -> Path | None:
|
|
"""
|
|
Download one campus/year CSV from GCC. Returns the local path on
|
|
success, None if all retries failed.
|
|
|
|
Skips download if the file already exists (idempotent).
|
|
"""
|
|
out_path = out_dir / f"GCC_{campus}_{year}.csv"
|
|
if out_path.exists():
|
|
log.info("Already exists, skipping: %s", out_path)
|
|
return out_path
|
|
|
|
url = GCC_URL.format(year=year, entity_id=entity_id)
|
|
log.info("Downloading %s %s ...", campus, year)
|
|
log.debug(" URL: %s", url)
|
|
|
|
for attempt in range(1, retries + 1):
|
|
try:
|
|
resp = session.get(url, timeout=timeout)
|
|
resp.raise_for_status()
|
|
|
|
# GCC returns an error page (HTML) instead of CSV when data is
|
|
# unavailable for a year. Detect by checking the content type
|
|
# or whether the first bytes look like HTML.
|
|
content = resp.content
|
|
if content.lstrip()[:5].lower() in (b"<html", b"<!doc"):
|
|
log.warning(
|
|
"%s %s: server returned HTML, not CSV "
|
|
"(data may not exist for this year). Skipping.",
|
|
campus, year,
|
|
)
|
|
return None
|
|
|
|
out_path.write_bytes(content)
|
|
# Quick sanity-check: count rows
|
|
rows = content.decode("utf-8-sig", errors="replace").count("\n")
|
|
log.info(" -> %s (%d rows)", out_path.name, rows)
|
|
return out_path
|
|
|
|
except requests.RequestException as exc:
|
|
if attempt == retries:
|
|
log.error(
|
|
"%s %s: download failed after %d attempts: %s",
|
|
campus, year, retries, exc,
|
|
)
|
|
return None
|
|
wait = 10 * attempt
|
|
log.warning(
|
|
"%s %s: attempt %d failed (%s), retrying in %ds ...",
|
|
campus, year, attempt, exc, wait,
|
|
)
|
|
time.sleep(wait)
|
|
|
|
return None
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# COMBINE
|
|
# ---------------------------------------------------------------------------
|
|
|
|
def combine_csvs(paths: list[Path], year: int,
|
|
out_dir: Path) -> Path | None:
|
|
"""
|
|
Concatenate per-campus CSVs into one combined file, adding a 'campus'
|
|
column as the first field. Returns the combined file path.
|
|
"""
|
|
if not paths:
|
|
return None
|
|
|
|
out_path = out_dir / f"GCC_ALL_{year}.csv"
|
|
total_rows = 0
|
|
header_written = False
|
|
|
|
with open(out_path, "w", newline="", encoding="utf-8") as out_fh:
|
|
writer = None
|
|
|
|
for path in paths:
|
|
campus = path.stem.split("_")[1] # GCC_UCLA_2024 -> UCLA
|
|
with open(path, newline="", encoding="utf-8-sig") as in_fh:
|
|
reader = csv.DictReader(in_fh)
|
|
if reader.fieldnames is None:
|
|
log.warning("Empty or unreadable file: %s", path)
|
|
continue
|
|
|
|
if not header_written:
|
|
fieldnames = ["campus"] + list(reader.fieldnames)
|
|
writer = csv.DictWriter(out_fh, fieldnames=fieldnames,
|
|
extrasaction="ignore")
|
|
writer.writeheader()
|
|
header_written = True
|
|
|
|
for row in reader:
|
|
row["campus"] = campus
|
|
writer.writerow(row)
|
|
total_rows += 1
|
|
|
|
log.info("Combined %d rows -> %s", total_rows, out_path)
|
|
return out_path
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CLI
|
|
# ---------------------------------------------------------------------------
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(
|
|
description="Download UC salary CSVs from the GCC (State Controller).",
|
|
formatter_class=argparse.RawDescriptionHelpFormatter,
|
|
epilog=__doc__,
|
|
)
|
|
parser.add_argument(
|
|
"--years", nargs="+", type=int, required=True,
|
|
help="Calendar year(s) to download, e.g. --years 2015 2024",
|
|
)
|
|
parser.add_argument(
|
|
"--campuses", nargs="+",
|
|
choices=list(ENTITIES.keys()), default=list(ENTITIES.keys()),
|
|
metavar="CAMPUS",
|
|
help=(
|
|
"Campuses to download (default: all). "
|
|
"Choices: " + " ".join(ENTITIES.keys())
|
|
),
|
|
)
|
|
parser.add_argument(
|
|
"--out-dir", default="gcc_data",
|
|
help="Directory to write CSV files into (default: gcc_data/)",
|
|
)
|
|
parser.add_argument(
|
|
"--no-combine", action="store_true",
|
|
help="Skip writing the combined GCC_ALL_<year>.csv file.",
|
|
)
|
|
parser.add_argument(
|
|
"--dry-run", action="store_true",
|
|
help="Print download URLs without downloading anything.",
|
|
)
|
|
parser.add_argument(
|
|
"--timeout", type=int, default=120,
|
|
help="HTTP timeout in seconds per request (default: 120)",
|
|
)
|
|
parser.add_argument(
|
|
"--retries", type=int, default=3,
|
|
help="Retry attempts per file on failure (default: 3)",
|
|
)
|
|
args = parser.parse_args()
|
|
|
|
out_dir = Path(args.out_dir)
|
|
|
|
if args.dry_run:
|
|
print("Dry run - URLs that would be fetched:")
|
|
for year in sorted(args.years):
|
|
for campus in args.campuses:
|
|
entity_id = ENTITIES[campus]
|
|
url = GCC_URL.format(year=year, entity_id=entity_id)
|
|
out = out_dir / f"GCC_{campus}_{year}.csv"
|
|
print(f" {out} <- {url}")
|
|
return
|
|
|
|
out_dir.mkdir(parents=True, exist_ok=True)
|
|
session = requests.Session()
|
|
session.headers.update({
|
|
"User-Agent": (
|
|
"Mozilla/5.0 (X11; Linux x86_64; rv:120.0) "
|
|
"Gecko/20100101 Firefox/120.0"
|
|
),
|
|
"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
|
|
"Referer": "https://gcc.sco.ca.gov/",
|
|
})
|
|
|
|
failed = []
|
|
|
|
for year in sorted(args.years):
|
|
downloaded = []
|
|
for campus in args.campuses:
|
|
entity_id = ENTITIES[campus]
|
|
path = download_csv(
|
|
campus, entity_id, year, out_dir, session,
|
|
retries=args.retries, timeout=args.timeout,
|
|
)
|
|
if path:
|
|
downloaded.append(path)
|
|
else:
|
|
failed.append(f"{campus} {year}")
|
|
# Small polite pause between requests
|
|
time.sleep(1.5)
|
|
|
|
if not args.no_combine and downloaded:
|
|
combine_csvs(downloaded, year, out_dir)
|
|
|
|
if failed:
|
|
log.warning("Failed downloads (%d): %s", len(failed), ", ".join(failed))
|
|
else:
|
|
log.info("All downloads complete.")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|