{ "cells": [ { "cell_type": "markdown", "id": "2ecba7ed", "metadata": {}, "source": [ "# Link reported USAspending subawards to prime awards\n", "\n", "Executed against the September 25, 2026 observation. This is the first 25 rows of a July action-date query, not a complete month. Replay is offline. Reporting timestamps are unavailable in the pinned projection; retrieval times remain separate. Run from the extracted bundle directory." ] }, { "cell_type": "code", "execution_count": 1, "id": "68be3dfc", "metadata": { "execution": { "iopub.execute_input": "2026-09-25T05:35:11.606650Z", "iopub.status.busy": "2026-09-25T05:35:11.606268Z", "iopub.status.idle": "2026-09-25T05:35:11.628875Z", "shell.execute_reply": "2026-09-25T05:35:11.626935Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{\n", " \"source_rows\": 25,\n", " \"linked_rows\": 25,\n", " \"unmatched_rows\": 0,\n", " \"flagged_rows\": 1,\n", " \"prime_awards\": 5,\n", " \"distinct_nonempty_subrecipient_ueis\": 21,\n", " \"has_next_page\": true,\n", " \"retrieved_at\": \"2026-09-25T05:26:23.148440+00:00\"\n", "}\n" ] } ], "source": [ "import sys\n", "sys.dont_write_bytecode = True\n", "import csv, json\n", "from pathlib import Path\n", "from html import escape\n", "from IPython.display import HTML, display\n", "from build_edges import build, read_verified\n", "summary = build()\n", "print(json.dumps(summary, indent=2))" ] }, { "cell_type": "markdown", "id": "9f2eb15a", "metadata": {}, "source": [ "## Preserve observation grain and inspect source-backed edges\n", "Each row retains its subaward ID. Repeated UEIs are not automatically duplicates." ] }, { "cell_type": "code", "execution_count": 2, "id": "81b756fd", "metadata": { "execution": { "iopub.execute_input": "2026-09-25T05:35:11.631882Z", "iopub.status.busy": "2026-09-25T05:35:11.631551Z", "iopub.status.idle": "2026-09-25T05:35:11.716144Z", "shell.execute_reply": "2026-09-25T05:35:11.713882Z" } }, "outputs": [ { "data": { "text/html": [ "
subaward_idsubrecipient_namesubrecipient_ueiprime_piidlink_status
0000000588MAX-CEVA SOLUTIONSSFZ9FA92HU8370CMSD26FR0000060linked
0000000731G2S CORPR875BEBFLMU475D30122F15135linked
0000000732LOYAL SOURCE GOVERNMENT SERVICES LLCY54FQAPG4TN575D30122F15135linked
0000000871G2S CORPR875BEBFLMU475D30122F15135linked
0000000875LOYAL SOURCE GOVERNMENT SERVICES LLCY54FQAPG4TN575D30122F15135linked
0000001584-0ADVANCED COOLING TECHNOLOGIES INCZ8KVZV3DR7J4N0003024C6001linked
0000011731 MOD 3RAZORLEAF GOVERNMENT SOLUTIONS LLCHR54HX6DF1L5N6833525C0136linked
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "def load(name):\n", " with (Path(\"example-output\") / name).open(newline=\"\", encoding=\"utf-8\") as handle:\n", " return list(csv.DictReader(handle))\n", "def show(rows, fields):\n", " header = \"\".join(\"\" + escape(f) + \"\" for f in fields)\n", " body = \"\".join(\"\" + \"\".join(\"\" + escape(str(row.get(f, \"\"))) + \"\" for f in fields) + \"\" for row in rows)\n", " display(HTML(\"\" + header + \"\" + body + \"
\"))\n", "edges = load(\"edges.csv\")\n", "show(edges[:7], [\"subaward_id\", \"subrecipient_name\", \"subrecipient_uei\", \"prime_piid\", \"link_status\"])" ] }, { "cell_type": "markdown", "id": "7ad05428", "metadata": {}, "source": [ "## Review flags are separate from successful links\n", "Do not infer the cause of the date/amount discrepancy, or silently repair it." ] }, { "cell_type": "code", "execution_count": 3, "id": "da5b2d90", "metadata": { "execution": { "iopub.execute_input": "2026-09-25T05:35:11.720468Z", "iopub.status.busy": "2026-09-25T05:35:11.719900Z", "iopub.status.idle": "2026-09-25T05:35:11.731387Z", "shell.execute_reply": "2026-09-25T05:35:11.729537Z" } }, "outputs": [ { "data": { "text/html": [ "
subaward_idsubaward_action_datesubaward_amount_usdreview_flags
00000005882026-07-01174441.6subaward_before_prime_date_signed;prime_zero_obligation
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
prime_piiddate_signedtotal_obligation_usdretrieved_at
70CMSD26FR00000602026-08-260.02026-09-25T05:26:46.882258+00:00
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "flagged = load(\"review-flags.csv\")\n", "show(flagged, [\"subaward_id\", \"subaward_action_date\", \"subaward_amount_usd\", \"review_flags\"])\n", "primes = load(\"primes.csv\")\n", "show([p for p in primes if p[\"prime_award_key\"] == flagged[0][\"prime_award_key\"]], [\"prime_piid\", \"date_signed\", \"total_obligation_usd\", \"retrieved_at\"])" ] }, { "cell_type": "markdown", "id": "c73e956a", "metadata": {}, "source": [ "## Keep financial grains and dates separate\n", "Prime obligations appear once per prime table row. The 18 edges for one prime must not multiply its obligations. No combined financial total is produced." ] }, { "cell_type": "code", "execution_count": 4, "id": "977e815d", "metadata": { "execution": { "iopub.execute_input": "2026-09-25T05:35:11.735694Z", "iopub.status.busy": "2026-09-25T05:35:11.734799Z", "iopub.status.idle": "2026-09-25T05:35:11.745248Z", "shell.execute_reply": "2026-09-25T05:35:11.742880Z" } }, "outputs": [ { "data": { "text/html": [ "
prime_piidedge_rowstotal_obligation_usd
70CMSD26FR000006010.0
75D30122F15135440426653.26
89233224CNA000004183572209101.57
N0003024C600111219922110.28
N6833525C013617347313.59
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Reporting timestamps available: 0\n" ] } ], "source": [ "from collections import Counter\n", "counts = Counter(row[\"prime_piid\"] for row in edges)\n", "show([{\"prime_piid\": p[\"prime_piid\"], \"edge_rows\": counts[p[\"prime_piid\"]], \"total_obligation_usd\": p[\"total_obligation_usd\"]} for p in primes], [\"prime_piid\", \"edge_rows\", \"total_obligation_usd\"])\n", "print(\"Reporting timestamps available:\", sum(bool(r[\"reported_at\"]) for r in edges))" ] }, { "cell_type": "markdown", "id": "f2246179", "metadata": {}, "source": [ "## Validate the observed result\n", "An empty unmatched report is valid. Synthetic failure cases live in test_edges.py, not the observed dataset." ] }, { "cell_type": "code", "execution_count": 5, "id": "a3ac5ead", "metadata": { "execution": { "iopub.execute_input": "2026-09-25T05:35:11.748815Z", "iopub.status.busy": "2026-09-25T05:35:11.748481Z", "iopub.status.idle": "2026-09-25T05:35:11.757988Z", "shell.execute_reply": "2026-09-25T05:35:11.756316Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Verified: 25 rows preserved, 25 links, 0 unmatched, 1 flagged; more source pages exist.\n" ] } ], "source": [ "source, receipt = read_verified(\"search\")\n", "unmatched = load(\"unmatched.csv\")\n", "assert len(edges) == len(source[\"results\"]) == 25\n", "assert [r[\"subaward_id\"] for r in edges] == [r[\"Sub-Award ID\"] for r in source[\"results\"]]\n", "assert len({r[\"observation_id\"] for r in edges}) == 25\n", "assert len(primes) == 5 and len(flagged) == 1 and len(unmatched) == 0\n", "assert all(r[\"link_status\"] == \"linked\" for r in edges)\n", "assert all(not r[\"reported_at\"] and not r[\"last_modified_at\"] for r in edges)\n", "assert source[\"page_metadata\"][\"hasNext\"] is True\n", "print(\"Verified: 25 rows preserved, 25 links, 0 unmatched, 1 flagged; more source pages exist.\")" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.3" } }, "nbformat": 4, "nbformat_minor": 5 }