cfahlgren1/sf_criminal_court

---
license: cc-by-nc-4.0
task_categories:
- tabular-classification
language:
- en
tags:
- criminal-justice
- san-francisco
- court-records
- district-attorney
- legal
size_categories:
- 1M<n<10M
configs:
- config_name: cases
data_files:
- split: train
path: cases.parquet
- config_name: register_of_actions
data_files:
- split: train
path: register_of_actions.parquet
- config_name: attorneys
data_files:
- split: train
path: attorneys.parquet
- config_name: calendar
data_files:
- split: train
path: calendar.parquet
- config_name: da_arrests
data_files:
- split: train
path: da_arrests.parquet
- config_name: da_prosecuted
data_files:
- split: train
path: da_prosecuted.parquet
- config_name: sfsc_case_matches
data_files:
- split: train
path: sfsc_case_matches.parquet
- config_name: sfsc_charge_dispositions
data_files:
- split: train
path: sfsc_charge_dispositions.parquet
- config_name: judicial_assignment_sources
data_files:
- split: train
path: judicial_assignment_sources.parquet
- config_name: judicial_officers
data_files:
- split: train
path: judicial_officers.parquet
- config_name: judicial_assignments
data_files:
- split: train
path: judicial_assignments.parquet
- config_name: judicial_department_assignments
data_files:
- split: train
path: judicial_department_assignments.parquet
- config_name: calendar_with_judicial_assignments
data_files:
- split: train
path: calendar_with_judicial_assignments.parquet
---

# San Francisco Criminal Court Data

Linked criminal-court records for San Francisco County, combining scraped Superior Court docket data, District Attorney open-data feeds, and a charge-disposition spreadsheet that the Court produced only after sustained pressure under California Rules of Court rule 10.500.

## Tables

| File | Rows | Description |
|------|------|-------------|
| `cases.parquet` | 77,406 | Case-level records: case number, case ID, defendant name, filing date |
| `register_of_actions.parquet` | 776,728 | Docket entries (proceedings, filers, dates) |
| `attorneys.parquet` | 72,289 | Attorneys listed on each case and their role |
| `calendar.parquet` | 318,993 | Hearing dates, types, courtroom/department |
| `da_arrests.parquet` | 176,130 | DA arrest feed: booked charges, agency, crime type, DA action |
| `da_prosecuted.parquet` | 120,827 | DA prosecuted-case feed: filed charges, case status, DA action |
| `sfsc_case_matches.parquet` | 13,790 | Deterministic-inferred matches linking anonymized Court IDs to public case numbers |
| `sfsc_charge_dispositions.parquet` | 44,029 | Charge-level dispositions, sentences, and probation/jail/prison days |
| `judicial_assignment_sources.parquet` | 14 | Source documents/pages used for published judge-to-department assignments |
| `judicial_officers.parquet` | 77 | Canonical judge/officer name dimension and raw-name variant mapping |
| `judicial_assignments.parquet` | 804 | Published judge assignment rows by effective date and department |
| `judicial_department_assignments.parquet` | 777 | One row per source/date/department, ready for date+department joins |
| `calendar_with_judicial_assignments.parquet` | 318,993 | Calendar hearings joined to published department assignments by hearing date and department |

## Data Sources

**`cases`, `calendar`, `attorneys`, `register_of_actions`** — Scraped from San Francisco Superior Court's public criminal case pages. These include public case numbers, court case IDs, defendant names, filing dates, hearing schedules, listed attorneys, and register-of-actions docket entries.

**`da_arrests`** — San Francisco District Attorney arrest/open-data feed. Fields: incident number, court number, arrest date, arresting agency, booked charges, booked case type, crime type, DV flag, DA action taken, and data freshness timestamps.

**`da_prosecuted`** — San Francisco District Attorney prosecuted-cases/open-data feed. Fields: incident number, court number, arrest date, filed charges, filed case type, crime type, DV flag, DA action taken, case status, and data freshness timestamps.

**`sfsc_case_matches` and `sfsc_charge_dispositions`** — Derived from `SFSC_CHARGE_DISPOSITION.xlsx`, a charge/disposition spreadsheet produced by San Francisco Superior Court in response to a California Rules of Court rule 10.500 records request.

This was not a public bulk download or an ordinary open-data release. The Court declined to provide public case numbers — the same case numbers it publishes on its own website, on individual case pages, accessible to anyone with a browser. Instead, after extended back-and-forth in which the Court appeared to treat a routine records-access rule as an unreasonable imposition, it supplied anonymized case identifiers. The result: public-interest researchers receive a spreadsheet that is artificially harder to join to other public records, for no apparent reason beyond institutional reluctance to make bulk access straightforward.

`sfsc_case_matches` was generated locally by matching the anonymized Court-provided spreadsheet back to public court and DA data. The match key is `filed_date + normalized charge multiset`, accepted only when that composite key is unique on both sides. This is labeled "deterministic-inferred" rather than simply "deterministic" because the Court chose not to provide the obvious join key (the public case number), requiring inference of a link that should have been given outright.

`sfsc_charge_dispositions` contains the matched charge-level fields from the rule 10.500 spreadsheet, enriched with the public `case_number` where a strict match succeeded. Fields include statute descriptions, initial and disposition charge types, disposition date/type, diversion type, sentence type/date, probation days, jail days, and prison days.

**`judicial_assignment_sources`, `judicial_officers`, `judicial_assignments`, `judicial_department_assignments`, and `calendar_with_judicial_assignments`** — Built from public San Francisco Superior Court judicial assignment materials: published assignment memos, department-assignment PDFs, and the current judicial assignment web page.

These files identify the judge publicly assigned to a court department over an effective-date range. They do **not** prove the historical actual daily sitting judge for a particular hearing; substitute judges, visiting judges, illness, vacations, and calendar coverage changes may not appear in these public assignment documents. A rule 10.500 request for actual presiding-judge-per-hearing data is outstanding with the Court, but given their track record of treating public records access as an adversarial proceeding, we are not holding our breath. Published department assignments are used in the meantime.

Source types used:

- Public SF Superior Court assignment memos (mostly PDF), with explicit effective dates from 2022–2025.
- Public SF Superior Court department assignment listing PDFs from 2024–2025.
- Current SF Superior Court Judicial Assignments page, effective April 27, 2026: https://sf.courts.ca.gov/general-information/judicial-assignments-2026

Some PDFs were scanned/image-only. Those were OCR'd locally with `glm-ocr` via Ollama; cached OCR output is stored in `judicial_assignment_sources/ocr/`. Text-based PDFs were parsed with `pdftotext`.

Judge names are preserved as extracted and also mapped to canonical keys in `judicial_officers` so analytics can merge harmless variants such as missing middle initials, OCR misspellings, and first-name/last-name ordering while still auditing the source strings.

Generated tables:

- `judicial_assignment_sources.parquet`: one row per source document/page set, including URL, local file path, effective start/end dates, extraction method, and date-basis notes.
- `judicial_officers.parquet`: canonical judge/officer dimension table with raw extracted variants, cleaned variants, placeholder flags, and normalization method.
- `judicial_assignments.parquet`: raw extracted assignment rows from each source, usually one row per department/judge/function.
- `judicial_department_assignments.parquet`: normalized one-row-per-source/date/department table, intended for joining by `department + hearing date`.
- `calendar_with_judicial_assignments.parquet`: `calendar.parquet` left-joined to `judicial_department_assignments` using calendar department and hearing date falling within the assignment effective range.

Join confidence fields:

- `deterministic_to_published_department_assignment`: exact department/date match to a single published assigned judge.
- `published_assignment_month_only_date`: joined to a source whose public header only gave month/year, so the script used the first day of that month and flags the date basis.
- Null confidence: no matching public assignment source was available for that calendar row.

The integration script is `integrate_judicial_assignments.py`. Re-running it downloads/caches source documents, OCRs scanned PDFs when needed, rebuilds the SQLite tables/views in `cases.db`, and regenerates the parquet outputs.

## Usage

```python
import duckdb

con = duckdb.connect()

# Query a single table
con.sql("SELECT * FROM 'cases.parquet' LIMIT 10").show()

# Join cases to charge dispositions
con.sql("""
SELECT c.case_number, c.defendant_name, d.statute_description,
d.dispo_type, d.jail_days, d.prison_days
FROM 'cases.parquet' c
JOIN 'sfsc_charge_dispositions.parquet' d USING (case_number)
WHERE d.prison_days > 0
ORDER BY d.prison_days DESC
LIMIT 20
""").show()
```

## License

CC-BY-NC-4.0. Non-commercial use only.