Join to federal data with NCES LEAID
Join two labelled sample federal rows on LEAID, while preserving a separate branch for records whose LEAID is null.
Join two labelled sample federal rows on LEAID, while preserving a separate branch for records whose LEAID is null.
What you will produce: A joined table that never invents a federal identifier and keeps DistrictFacts-only identities reviewable.
1. Read both identities
LEAID is the seven-digit NCES identifier. district_key is optional and is the stable DistrictFacts identity when supplied.
curl
curl --fail --silent --show-error \
https://www.districtfacts.com/data/v1/de/districts.jsoncurl
const response = await fetch("https://www.districtfacts.com/data/v1/de/districts.json");
if (!response.ok) throw new Error(`HTTP ${response.status}`);
interface DistrictDirectoryEntry {
slug: string;
name: string;
leaid: string | null;
api_url: string;
}
const districts = (await response.json()) as DistrictDirectoryEntry[];
console.log(districts.length, "districts");JavaScript
const response = await fetch("https://www.districtfacts.com/data/v1/de/districts.json");
if (!response.ok) throw new Error(`HTTP ${response.status}`);
const data = await response.json();
console.log(data);Python
import json
from urllib.request import Request, urlopen
request = Request("https://www.districtfacts.com/data/v1/de/districts.json")
with urlopen(request, timeout=30) as response:
data = json.load(response)
print(json.dumps(data, indent=2))Sample output
[{ "name": "Capital School District", "leaid": "1000190", "slug": "capital-school-district" }]2. Define two sample federal rows
These two rows are sample data for join mechanics, not reported federal figures.
curl
printf '%s
' '[{"LEAID":"1000190","sample_metric":12},{"LEAID":"1001240","sample_metric":34}]' | jq .JavaScript
// Sample data only; sample_metric is invented for this join.
const sampleFederalData = [{ LEAID: "1000190", sample_metric: 12 }, { LEAID: "1001240", sample_metric: 34 }];
console.log(sampleFederalData);Python
# Sample data only; sample_metric is invented for this join.
sample_data = [{"LEAID": "1000190", "sample_metric": 12}, {"LEAID": "1001240", "sample_metric": 34}]
print(sample_data)Sample output
Sample data
[{ "LEAID": "1000190", "sample_metric": 12 }, { "LEAID": "1001240", "sample_metric": 34 }]3. Join only non-null LEAIDs
Use LEAID as the federal key. A missing federal row remains null instead of becoming a zero-valued match.
curl
curl -sS https://www.districtfacts.com/data/v1/de/districts.json | jq --argjson sample '[{"LEAID":"1000190","sample_metric":12},{"LEAID":"1001240","sample_metric":34}]' '[.[] | . as $d | {district: $d.name, leaid: $d.leaid, federal: ([$sample[] | select(.LEAID == $d.leaid)][0] // null)}]'JavaScript
const directory = await fetch("https://www.districtfacts.com/data/v1/de/districts.json").then(response => response.json());
const byLeaid = new Map(sampleFederalData.map(row => [row.LEAID, row]));
const joined = directory.map(district => ({ district: district.name, leaid: district.leaid, federal: district.leaid === null ? null : byLeaid.get(district.leaid) ?? null }));
console.log(joined);Python
import json
from urllib.request import urlopen
with urlopen("https://www.districtfacts.com/data/v1/de/districts.json") as response: directory = json.load(response)
by_leaid = {row["LEAID"]: row for row in sample_data}
joined = [{"district": district["name"], "leaid": district["leaid"], "federal": None if district["leaid"] is None else by_leaid.get(district["leaid"])} for district in directory]
print(json.dumps(joined, indent=2))Sample output
{ "district": "Capital School District", "leaid": "1000190", "federal": { "LEAID": "1000190", "sample_metric": 12 } }4. Branch explicitly when LEAID is null
A record can have a null leaid: the schema allows it for a district the state reports that has no federal identifier. Do not put district_key in a federal LEAID column. Keep such rows apart, keyed by district_key, for review. This step reuses the Delaware directory from the previous step; it prints an empty list when every district there has a LEAID, and the branch is still the code to keep.
curl
curl -sS https://www.districtfacts.com/data/v1/de/districts.json | jq '[.[] | select(.leaid == null) | {name, leaid, district_key, federal_join: null, action: "review by district_key"}]'JavaScript
const unjoined = directory.filter(district => district.leaid === null).map(district => ({ name: district.name, leaid: null, district_key: district.district_key ?? null, federal_join: null, action: "review by district_key" }));
console.log(unjoined);Python
unjoined = [{"name": district["name"], "leaid": None, "district_key": district.get("district_key"), "federal_join": None, "action": "review by district_key"} for district in directory if district["leaid"] is None]
print(json.dumps(unjoined, indent=2))Sample output
[] (or, for a record without a federal identifier: { "leaid": null, "district_key": "<stable key>", "federal_join": null, "action": "review by district_key" })Never substitute a slug, state ID, or district_key into an NCES column.