JN for APR

A focused set of modular Jupyter notebooks can walk from raw city data all the way to a fully populated APR Excel file (or HCD Connect upload), mirroring the structure of the HCD instructions and tables. [1][2]

## 1. APR schema and validation notebook

Purpose: encode the APR structure and business rules so everything downstream is consistent with HCD’s definitions and table layouts. [2][3]

- Parse the official APR Excel template (or a local schema file) to define columns, data types, required fields, drop‑down domains, and cross‑table relationships for Tables A, A2, B–K. [2][3]

- Implement validators for key concepts such as “application submitted,” “completed entitlement,” “permitted units,” income categories (very low, low, moderate, above moderate), unit categories (SFD, SFA, 2–4, 5+, ADU, MH), and infill status. [4][5][6]

Outputs:

- A reusable Python module with enums, data classes, and validation functions used by all other notebooks. [3][7]

- A simple report listing records that violate APR rules (e.g., missing APNs, inconsistent unit counts, undefined affordability justification). [8][7]

## 2. Data acquisition and integration notebook

Purpose: pull and normalize Berkeley’s source data for permits, applications, sites inventory, business licenses, and related datasets needed to feed APR Tables A, A2, B, C, F, G, H, J, and K. [2][9]

- Connect to City of Berkeley OpenGov / permitting APIs (Planning, Building, Design Review, Certificates of Occupancy) and export all records with activity dates in the reporting year (apps submitted, entitlements, permits, C of O). [1][8]

- Join with assessor parcel data and sites inventory to extract APNs, addresses, unit counts, zoning, and RHNA site flags; compute “development activity spanning multiple years” flags so each year only records the relevant stage. [8][6][9]

Outputs:

- Cleaned fact tables for: applications, entitlements, permits, certificates of occupancy, demolitions/destroyed units, sites inventory changes, and locally owned or surplus lands. [8][7][10]

- Reference tables for income limits (by AMI band), funding sources, deed restriction types, and RHNA targets. [4][11][12]

## 3. Table A and A2 construction notebook

Purpose: transform integrated data into fully formed APR Tables A and A2, including affordability breakdowns, unit categories, and legislative/streamlining flags. [13][8]

- For Table A (applications submitted):  

  - Filter to applications determined complete in the reporting year; attach project identifiers (APN, address, local tracking ID, project name). [13][14]

  - Classify unit category (SFD, SFA, 2–4, 5+, ADU, MH), tenure (R/O), proposed vs approved vs disapproved units, streamlining provisions (SB 9, AB 2011, SB 6, SB 423), density bonus flags, and status (approved, pending, disapproved, withdrawn). [13][14][15][16]

- For Table A2 (entitled, permitted, completed units):  

  - Build one record per project/APN and unit type, with three stages: entitlement, building permit, certificate of occupancy, each with date and affordability-by-income fields. [8][6][15]

  - Compute net new units and track demolished/destroyed units by tenure and cause (demolished vs destroyed). [6][7][10]

Outputs:

- Machine-generated Table A and Table A2 as Pandas DataFrames, aligned to HCD column order and naming. [13][8]

- Export functions to write directly into the APR Excel template or the CSV structure required by HCD Connect. [1][3]

## 4. Affordability, funding, and RHNA summary notebook (Tables B, F, F2, RHNA dashboards)

Purpose: aggregate project-level records into affordability and RHNA progress summaries, plus preservation/rehab conversions where data is available. [2][9]

- Use Table A2 data to construct Table B (permitted units by affordability, by year of the planning cycle) and reconcile against any pre-populated prior-year records. [8][9]

- Build logic to populate fields 16–19 on Table A2 (assistance programs, deed restriction types, affordability without subsidy, term of affordability) based on funding tables and local policies. [12][7][17]

- Design optional modules to support Table F and F2 (rehabilitated, preserved, acquired, and converted units) by linking code enforcement, rehab grant programs, and conversion tracking datasets. [18][19]

Outputs:

- A RHNA progress dashboard (per very low, low, moderate, above moderate) for the reporting year and cumulative cycle. [2][9]

- Draft Table B, F, and F2 DataFrames, with quality checks for double counting and affordability justification completeness. [9][18]

## 5. Sites, surplus lands, and program implementation notebook (Tables C, D, G, H, J, K)

Purpose: support the more policy-focused tables using structured inputs and reproducible logic. [20][21]

- Table C: track sites added or rezoned to accommodate shortfall housing need; join sites inventory and zoning actions, compute realistic capacity, and record unit yields by affordability. [20][17]

- Table D: provide a semi-structured template for program implementation status, tying each program to measurable indicators (e.g., permits issued, rezonings, funding deployed) pulled from other notebooks, to reduce manual narrative work. [10]

- Tables G and H: identify locally owned or controlled lands, and mark those sold, leased, declared surplus, or identified as excess during the year. [21]

Outputs:

- Structured tables for C, G, H, J, K that can be copy–pasted into the APR Excel or uploaded, with links back to the underlying parcels and actions. [20][21][22]

- A partially automated program-status matrix for Table D, prefilled with quantitative indicators and space for policy staff to add narrative. [10]

## 6. Export, Datasette, and report-notebook

Purpose: finalize APR outputs, publish data for exploration, and generate derived visuals and narrative for local reporting.

- Write all APR tables (A, A2, B–K) into a single Excel workbook exactly matching HCD’s template, including correct sheet names, formats, and date fields (mm/dd/yyyy). [3][8][6]

- Build and/or update a SQLite database keyed by project/APN, with views or tables that mirror APR tables plus extra local indicators; ship that directly into Datasette for interactive querying and mapping. [6][9]

- Generate local “Berkeley APR supplement” visualizations (e.g., time series of permits by income band, maps of infill vs non‑infill, density bonus usage, streamlining projects) for the city’s annual public reporting, drawing only from validated APR-ready tables. [8][11][23]

Outputs:

- A scripted “end-to-end” run that takes a reporting year as a parameter and produces: a validated APR Excel/CSV package, a refreshed Datasette database, and a small static site or PDF with charts and maps summarizing the year. [1][2][24]

If you like, the next step can be to outline concrete notebook filenames and minimal column schemas for each APR table so they slot directly into your existing Berkeley pipeline/Datasette repo.

Sources

[1] Jupyter Notebooks in VS Code https://code.visualstudio.com/docs/datascience/jupyter-notebooks

[2] Jupyter notebook not trusted - python - Stack Overflow https://stackoverflow.com/questions/44943646/jupyter-notebook-not-trusted

[3] User input prompts are not visible when running .ipynb in VS Code. https://github.com/microsoft/vscode-jupyter/issues/8846

[4] Workspace Trust - Visual Studio Code https://code.visualstudio.com/docs/editing/workspaces/workspace-trust

[5] Make this notebook trusted to load map : File - folium - Stack Overflow https://stackoverflow.com/questions/63245563/make-this-notebook-trusted-to-load-map-file-trust-notebook

[6] Data Science in VS Code tutorial https://code.visualstudio.com/docs/datascience/data-science-tutorial

[7] Jupyterlab not remembering trust for notebooks https://discourse.jupyter.org/t/jupyterlab-not-remembering-trust-for-notebooks/7869

[8] Notebook trust prompt shown for files in trusted workspace #5938 https://github.com/microsoft/vscode-jupyter/issues/5938

[9] Plotting with Folium - GeoPandas https://geopandas.org/en/stable/gallery/plotting_with_folium.html

[10] Folium — Panel v1.8.5 - HoloViz https://panel.holoviz.org/reference/panes/Folium.html

[11] Prompts not appearing in VS code : r/learnpython - Reddit https://www.reddit.com/r/learnpython/comments/1j71c1u/prompts_not_appearing_in_vs_code/

[12] Getting started — Folium 0.20.0 documentation - GitHub Pages https://python-visualization.github.io/folium/latest/getting_started.html

[13] Are VSCode and Jupyter Notebook Private? : r/privacy - Reddit https://www.reddit.com/r/privacy/comments/1ih02qn/are_vscode_and_jupyter_notebook_private/

[14] Using Folium in Deepnote: A guide to displaying maps https://deepnote.com/guides/platform-solutions/folium-in-deepnote

[15] Getting `Make this Notebook Trusted to load map: File - GitHub https://github.com/python-visualization/branca/issues/81

[16] Python Interactive window - Visual Studio Code https://code.visualstudio.com/docs/python/jupyter-support-py

[17] Spatial joins in Python | Kasey Zapatka, PhD https://www.kaseyzapatka.com/blog/2025/spatial-joins/

[18] Workspace Trust - Visual Studio Code https://code.visualstudio.com/blogs/2021/07/06/workspace-trust

[19] How can I disable workspace trust in VS Code 1.57? - Stack Overflow https://stackoverflow.com/questions/67914899/how-can-i-disable-workspace-trust-in-vs-code-1-57

[20] Jupyter notebooks - Sphinx Book Theme https://sphinx-book-theme.readthedocs.io/en/stable/content/notebooks.html

[21] Notebooks — JupyterLab 4.6.0a0 documentation https://jupyterlab.readthedocs.io/en/latest/user/notebook.html

[22] Issue with Untrusted to Trusted Workspaces in Jupyter Notebooks https://github.com/microsoft/vscode-python/issues/20367

[23] VS Code: Do you trust the authors of the files in this folder? https://stackoverflow.com/questions/67914668/vs-code-do-you-trust-the-authors-of-the-files-in-this-folder

[24] Security in IPython notebooks https://ipython.org/ipython-doc/3/notebook/security.html