DAAF-Contribution-Community/daaf / .claude/skills/education-data-query
education-data-query
VerifiedDownloads education datasets from configured mirror sources (parquet/CSV) with local Polars filtering, including versioned mirror vintages (revision-pinned for reproducibility). Use when writing fetch scripts, retrieving CCD, IPEDS, CRDC, SAIPE data, or pinning a fetch to a specific mirror vintage. Load after education-data-explorer — retrieval here, not discovery.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 20.4 KB |
| references/datasets-reference.md | markdown | 23.0 KB |
| references/fetch-patterns.md | markdown | 64.3 KB |
| references/filters-reference.md | markdown | 13.5 KB |
| references/mirrors.yaml | config | 11.2 KB |
| references/query-patterns.md | markdown | 16.4 KB |
| references/vintage-drift.md | markdown | 13.8 KB |
references/fetch-patterns.md
View on GitHub# Fetch Patterns Reference
Mirror-based data fetching patterns for the Education Data Portal.
---
## Overview
All data fetching uses a **mirror-first** approach:
1. Try each mirror in priority order (defined in `mirrors.yaml`)
2. Download the dataset file using the mirror's format and URL template
3. Apply filters locally with Polars
4. Save to `data/raw/` in parquet format
**Mirror configuration is centralized in `mirrors.yaml`.** The patterns below are mirror-agnostic — they work with any mirror defined in that file. To add a new mirror, update `mirrors.yaml` and `datasets-reference.md` only.
---
## Canonical Path Contract (Python Executable Reference)
Python is the executable source of truth for mirror-path canonicalization and discovery. Copy these helpers into a Stage 5 script before the fetch/discovery patterns below. A canonical **data** key is extensionless after normalizing exactly one terminal `.csv` or `.parquet`; a canonical **codebook** key is extensionless after normalizing exactly one terminal `.xls`. Nested relative directories are preserved.
The object kind is required even for extensionless input. This prevents an untyped string from being silently interpreted as either data or a codebook. Empty paths, absolute paths, traversal, backslashes, URL-like strings, unknown extensions, doubled extensions, and kind/extension mismatches fail before URL construction.
```python
import re
from urllib.parse import urlparse
def canonicalize_mirror_path(path: str, object_kind: str) -> str:
"""Return one validated, extensionless canonical mirror key.
Args:
path: Relative mirror path, with one recognized terminal extension or no
extension. Nested directories are retained.
object_kind: Explicitly ``"data"`` or ``"codebook"``.
Raises:
TypeError: ``path`` is not a string.
ValueError: the path or object-kind/extension pairing is invalid.
"""
if object_kind not in {"data", "codebook"}:
raise ValueError("object_kind must be exactly 'data' or 'codebook'")
if not isinstance(path, str):
raise TypeError("mirror path must be a string")
if not path or path != path.strip():
raise ValueError("mirror path must be nonempty and have no outer whitespace")
if path.startswith("/") or "\\" in path or ":" in path:
raise ValueError("mirror path must be a relative POSIX path, not an absolute path or URL")
parts = path.split("/")
if any(part in {"", ".", ".."} for part in parts):
raise ValueError("mirror path cannot contain empty, current-directory, or traversal segments")
allowed_extensions = {
"data": (".csv", ".parquet"),
"codebook": (".xls",),
}
recognized_extensions = (".csv", ".parquet", ".xls")
lower_path = path.lower()
matched_extension = next(
(extension for extension in recognized_extensions if lower_path.endswith(extension)),
None,
)
if matched_extension is not None:
if matched_extension not in allowed_extensions[object_kind]:
raise ValueError(
f"{object_kind} path cannot use terminal extension {matched_extension!r}"
)
canonical_key = path[: -len(matched_extension)]
if canonical_key.lower().endswith(recognized_extensions):
raise ValueError("mirror path has a doubled recognized extension")
else:
final_component = parts[-1]
if "." in final_component:
raise ValueError("mirror path has an unsupported terminal extension")
canonical_key = path
if not canonical_key or canonical_key.endswith("/"):
raise ValueError("canonical mirror key cannot be empty or directory-like")
return canonical_key
def mirror_revision(mirror: dict) -> str:
"""Return the pinned Hugging Face git revision for a mirror.
A "vintage" is a dated Portal snapshot captured into one HF dataset repo; the
"revision" is the git ref (branch, tag, or commit SHA) that pins requests to
one immutable state of that repo. Feeding the revision from mirrors.yaml
(``vintage.hf_revision``) keeps every URL — data, codebook, and discovery — on
the same pinned bytes, so a rerun of a fetch script is byte-reproducible.
Returns ``"main"`` for a mirror with no vintage block (unversioned fallback).
"""
vintage = mirror.get("vintage") or {}
return str(vintage.get("hf_revision", "main"))
def build_mirror_url(
mirror: dict,
path: str,
object_kind: str,
) -> str:
"""Build a URL containing exactly one extension expected for the object kind."""
canonical_key = canonicalize_mirror_path(path, object_kind)
if object_kind == "data":
expected_format = str(mirror.get("format", "")).lower()
template = mirror.get("url_template")
if expected_format not in {"csv", "parquet"}:
raise ValueError("data mirror format must be exactly 'csv' or 'parquet'")
else:
metadata = mirror.get("metadata") or {}
formats = metadata.get("formats") or []
template = metadata.get("url_template")
if formats != ["xls"]:
raise ValueError("codebook metadata formats must be exactly ['xls']")
expected_format = "xls"
if not isinstance(template, str) or not template:
raise ValueError(f"mirror has no URL template for {object_kind} objects")
# revision is unused by templates that omit {revision} (e.g. the Urban CSV
# fallback) — str.format ignores extra kwargs, so this call is uniform.
url = template.format(
root_url=mirror["root_url"],
revision=mirror_revision(mirror),
path=canonical_key,
format=expected_format,
)
url_path = urlparse(url).path.lower()
expected_suffix = f".{expected_format}"
if not url_path.endswith(expected_suffix):
raise ValueError(f"URL template did not append expected {expected_suffix} extension")
without_expected = url_path[: -len(expected_suffix)]
if without_expected.endswith((".csv", ".parquet", ".xls")):
raise ValueError("URL template produced a doubled recognized extension")
return url
```
### R Semantic Translation
The Python helpers above define the tested executable contract. R fetch scripts may use the following semantic translation for URL construction; do not create an independent looser path policy. In particular, R discovery must consume the same explicit object kind and return the same extensionless keys as the Python reference.
```r
canonicalize_mirror_path <- function(path, object_kind) {
if (!is.character(path) || length(path) != 1L || is.na(path)) {
stop("mirror path must be one nonmissing string")
}
if (!(object_kind %in% c("data", "codebook"))) {
stop("object_kind must be exactly 'data' or 'codebook'")
}
if (!nzchar(path) || path != trimws(path)) {
stop("mirror path must be nonempty and have no outer whitespace")
}
if (startsWith(path, "/") || grepl("\\\\", path) || grepl(":", path, fixed = TRUE)) {
stop("mirror path must be a relative POSIX path, not an absolute path or URL")
}
parts <- strsplit(path, "/", fixed = TRUE)[[1]]
if (any(parts %in% c("", ".", ".."))) {
stop("mirror path cannot contain empty, current-directory, or traversal segments")
}
recognized <- c(".parquet", ".csv", ".xls")
allowed <- if (object_kind == "data") c(".parquet", ".csv") else ".xls"
lower_path <- tolower(path)
matched <- recognized[endsWith(lower_path, recognized)]
if (length(matched) > 0L) {
matched <- matched[[1]]
if (!(matched %in% allowed)) stop("object kind and terminal extension do not match")
canonical_key <- substr(path, 1L, nchar(path) - nchar(matched))
if (any(endsWith(tolower(canonical_key), recognized))) {
stop("mirror path has a doubled recognized extension")
}
} else {
if (grepl("\\.", tail(parts, 1L))) stop("mirror path has an unsupported terminal extension")
canonical_key <- path
}
if (!nzchar(canonical_key) || endsWith(canonical_key, "/")) {
stop("canonical mirror key cannot be empty or directory-like")
}
canonical_key
}
# Return the pinned Hugging Face git revision for a mirror. A "vintage" is a dated
# Portal snapshot in one HF repo; the "revision" is the git ref (branch/tag/commit
# SHA) pinning requests to one immutable state. Sourcing it from mirrors.yaml
# (vintage$hf_revision) keeps data, codebook, and discovery URLs on the same
# bytes. Falls back to "main" for a mirror with no vintage block.
mirror_revision <- function(mirror) {
rev <- mirror$vintage$hf_revision
if (is.null(rev)) "main" else as.character(rev)
}
build_mirror_url <- function(mirror, path, object_kind) {
canonical_key <- canonicalize_mirror_path(path, object_kind)
if (object_kind == "data") {
expected_format <- tolower(mirror$format)
template <- mirror$url_template
if (!(expected_format %in% c("csv", "parquet"))) stop("invalid data mirror format")
} else {
if (!identical(unlist(mirror$metadata$formats), "xls")) stop("invalid codebook format")
expected_format <- "xls"
template <- mirror$metadata$url_template
}
# revision is unused by templates without a {revision} placeholder (e.g. the
# Urban CSV fallback) — glue only substitutes referenced names.
url <- glue::glue(template, root_url = mirror$root_url,
revision = mirror_revision(mirror),
path = canonical_key, format = expected_format)
expected_suffix <- paste0(".", expected_format)
without_expected <- substr(url, 1L, nchar(url) - nchar(expected_suffix))
if (!endsWith(tolower(url), expected_suffix) ||
any(endsWith(tolower(without_expected), c(".csv", ".parquet", ".xls")))) {
stop("URL template did not append exactly one expected extension")
}
as.character(url)
}
```
---
## Mirror Resolution Pattern
This pattern is inlined into every Stage 5 fetch script. It tries each mirror in priority order and falls back gracefully.
### Single-File Dataset (all years in one file)
```python
import time
import polars as pl
import requests
import yaml
from pathlib import Path
# --- Rate Limiting ---
# INTENT: Prevent HTTP 429 (Too Many Requests) errors from mirrors.
# REASONING: Mirrors may rate-limit rapid successive requests. A 3-second delay
# between fetch calls avoids triggering limits while keeping pipeline runtime
# reasonable (most fetches are sequential anyway).
FETCH_DELAY_SECONDS = 3
_last_fetch_time = 0.0
def _rate_limit():
"""Sleep if needed to maintain minimum delay between fetch requests."""
global _last_fetch_time
if _last_fetch_time > 0:
elapsed = time.time() - _last_fetch_time
if elapsed < FETCH_DELAY_SECONDS:
wait = FETCH_DELAY_SECONDS - elapsed
print(f" (rate limit: waiting {wait:.1f}s)")
time.sleep(wait)
_last_fetch_time = time.time()
# --- Mirror Configuration ---
# INTENT: Download dataset from the fastest available mirror.
# REASONING: Mirrors are loaded from mirrors.yaml (the single source of truth).
# Each mirror specifies its own url_template, read_strategy, and timeout.
# The first successful response is used; failures fall through to the next mirror.
# REFERENCE: See mirrors.yaml for mirror definitions, datasets-reference.md for paths.
# Load mirror config from mirrors.yaml (adjust path to your project)
SKILL_DIR = Path(__file__).resolve().parent # scripts/ directory
# mirrors.yaml is in the education-data-query skill references directory.
# When used in a research project, copy this path or inline the loaded config.
MIRRORS_YAML = SKILL_DIR / "mirrors.yaml" # Adjust path as needed
def load_mirrors(yaml_path: Path = MIRRORS_YAML) -> list[dict]:
"""Load mirror configuration from mirrors.yaml.
Returns list of mirror dicts with: name, url_template, read_strategy, timeout.
Mirrors are in priority order (first = highest priority).
"""
with open(yaml_path) as f:
config = yaml.safe_load(f)
return config["mirrors"]
# Load mirrors at module level — tried in priority order
MIRRORS = load_mirrors()
# Dataset path: canonical path string from datasets-reference.md.
# All mirrors use the same path — only root_url, format and the pinned revision (HF mirror only) differ.
# Example for SAIPE district poverty:
DATASET_PATH = "saipe/districts_saipe"
def fetch_from_mirrors(
path: str,
filters: dict | None = None,
years: list[int] | None = None,
) -> pl.DataFrame:
"""Try each mirror in order. Return DataFrame on first success.
Args:
path: Canonical dataset path string from datasets-reference.md.
All mirrors use the same path — only root_url, format and the pinned revision (HF mirror only) differ.
Example: "saipe/districts_saipe"
filters: Dict of column->value(s) filters to apply locally
years: List of years to filter to (applied as pl.col("year").is_in(years))
Returns:
Filtered Polars DataFrame
"""
canonical_path = canonicalize_mirror_path(path, "data")
_rate_limit()
last_error = None
for mirror in MIRRORS:
name = mirror["name"]
strategy = mirror["read_strategy"]
timeout = mirror["timeout"]
# Build a URL only after validating the extensionless data contract.
url = build_mirror_url(mirror, canonical_path, "data")
print(f" Trying {name}: {url}")
try:
if strategy == "eager_parquet":
# REASONING: Parquet files have embedded schema, no inference needed.
# Polars reads HTTP URLs natively via pl.read_parquet().
df = pl.read_parquet(url)
elif strategy == "lazy_csv":
# REASONING: CSV files can be 500MB+. Lazy loading streams only
# matching rows into memory rather than loading the full file.
# ASSUMES: CSV has standard column names matching parquet schema.
lazy = pl.scan_csv(url, infer_schema_length=10000)
if years:
lazy = lazy.filter(pl.col("year").is_in(years))
if filters:
for col, val in filters.items():
if isinstance(val, list):
lazy = lazy.filter(pl.col(col).is_in(val))
else:
lazy = lazy.filter(pl.col(col) == val)
df = lazy.collect()
print(f" ✓ {name}: {df.shape[0]:,} rows (after lazy filters)")
return df
else:
print(f" Skipping {name}: unknown read_strategy '{strategy}'")
continue
print(f" ✓ {name}: {df.shape[0]:,} rows")
# Apply filters for eagerly-loaded formats (parquet, etc.)
if years:
df = df.filter(pl.col("year").is_in(years))
if filters:
for col, val in filters.items():
if isinstance(val, list):
df = df.filter(pl.col(col).is_in(val))
else:
df = df.filter(pl.col(col) == val)
print(f" After filters: {df.shape[0]:,} rows")
return df
except Exception as e:
last_error = e
print(f" ✗ {name} failed: {e}")
continue
raise RuntimeError(f"All mirrors failed. Last error: {last_error}")
```
```r
library(arrow)
library(dplyr)
library(readr)
library(yaml)
library(httr2)
# --- Locale Guard (R only) ---
# INTENT: Ensure R runs under a UTF-8 locale before any multibyte read below.
# REASONING: The container image sets LANG/LC_ALL=C.UTF-8 at the image level (the
# root-cause fix), so on a current image this guard is a no-op. It is
# defense-in-depth for runs on a stale or pre-v3.0.0 image where the locale is
# still unset/POSIX: there R's codeset is ANSI_X3.4-1968 (MBCS off) and the
# yaml::read_yaml() call below silently returns NULL on mirrors.yaml's
# multibyte UTF-8 (warning only), while base readLines() byte-mangles strings.
# Unlike Python (PEP 538 startup coercion), R has no self-coercion mechanism,
# so the locale must be set explicitly.
# ASSUMES: C.UTF-8 is available (it ships in the stock ubuntu:24.04 base).
# LIMITATION: this runtime call fixes file *reads* only. It CANNOT repair
# non-ASCII string literals, which the R parser mangles to octal-escape text
# BEFORE this line runs — keep non-ASCII out of script literals, or rely on the
# image-level env (which fixes the parser too).
if (!isTRUE(l10n_info()[["UTF-8"]])) Sys.setlocale("LC_ALL", "C.UTF-8")
# --- Rate Limiting ---
# INTENT: Prevent HTTP 429 (Too Many Requests) errors from mirrors.
# REASONING: Mirrors may rate-limit rapid successive requests. A 3-second delay
# between fetch calls avoids triggering limits while keeping pipeline runtime
# reasonable (most fetches are sequential anyway).
FETCH_DELAY_SECONDS <- 3
last_fetch_time <- 0.0
# --- Download Timeout (R only) ---
# INTENT: Prevent large mirror files from truncating mid-transfer.
# REASONING: Both arrow::read_parquet(url) and readr::read_csv(url) route their
# HTTP transfer through R's download.file(), which enforces getOption("timeout")
# (default 60s) on the ENTIRE transfer — not per-idle-period. Large mirror files
# (e.g., ccd/schools_ccd_directory.parquet, ~269MB at the pinned revision) need
# roughly 190-490s at real CDN throughput (~0.55-1.4 MB/s), so they truncate at ~60s with a
# "downloaded length X != reported length Y" warning and silently fall through
# to the next (CSV) mirror. mirrors.yaml declares timeout: 300 per mirror, but
# the R read functions never consult it — the setting must be applied here.
# Python (Polars/pyarrow) uses its own HTTP client and is unaffected.
options(timeout = max(600, getOption("timeout")))
rate_limit <- function() {
if (last_fetch_time > 0) {
elapsed <- as.numeric(Sys.time()) - last_fetch_time
if (elapsed < FETCH_DELAY_SECONDS) {
wait <- FETCH_DELAY_SECONDS - elapsed
cat(sprintf(" (rate limit: waiting %.1fs)\n", wait))
Sys.sleep(wait)
}
}
last_fetch_time <<- as.numeric(Sys.time())
}
# --- Mirror Configuration ---
# INTENT: Download dataset from the fastest available mirror.
# REASONING: Mirrors are loaded from mirrors.yaml (the single source of truth).
# Each mirror specifies its own url_template, read_strategy, and timeout.
# The first successful response is used; failures fall through to the next mirror.
# REFERENCE: See mirrors.yaml for mirror definitions, datasets-reference.md for paths.
# Load mirror config from mirrors.yaml (adjust path to your project)
mirrors_yaml_path <- "mirrors.yaml" # Adjust path as needed
config <- yaml::read_yaml(mirrors_yaml_path)
mirrors <- config$mirrors
# Dataset path: canonical path string from datasets-reference.md.
# All mirrors use the same path — only root_url, format and the pinned revision (HF mirror only) differ.
# Example for SAIPE district poverty:
DATASET_PATH <- "saipe/districts_saipe"
years <- NULL # integer vector of years to keep, e.g. c(2020, 2021, 2022); NULL keeps all years
# --- Single-File Fetch: Try each mirror in order ---
# INTENT: Try each mirror in priority order. Return data frame on first success.
# Args:
# path: Canonical dataset path string from datasets-reference.md.
# filters: Named list of column->value(s) filters to apply locally.
# years: Integer vector of years to filter to.
# Returns: Filtered tibble.
rate_limit()
last_error <- NULL
# REASONING (variable name): the loop target is mirror_df, deliberately NOT df.
# `<<-` evaluated at the top level of a script searches the package search path
# (it skips the global environment's own frame), so `df <<- ...` finds the
# LOCKED binding stats::df first and fails with
# "cannot change value of locked binding for 'df'". Any loop variable whose
# name masks an attached-package object (df, c, t, T, data, ...) breaks the
# <<- idiom this way — use a distinctive name.
mirror_df <- NULL
for (mirror in mirrors) {
mirror_name <- mirror$name
strategy <- mirror$read_strategy
# Build a URL only after validating the extensionless data contract.
url <- build_mirror_url(mirror, DATASET_PATH, "data")
cat(sprintf(" Trying %s: %s\n", mirror_name, url))
result <- tryCatch({
if (strategy == "eager_parquet") {
# REASONING: Parquet files have embedded schema, no inference needed.
# arrow reads HTTP URLs natively via arrow::read_parquet().
# REASONING (view-safe read): mirror files are Polars-written; some declare
# string columns as `string_view` in the parquet-native schema. The R arrow
# binding reads these at the C++ layer but fails at Table->data.frame with
# "cannot handle Array of type <utf8_view>". So read as an Arrow Table
# (as_data_frame = FALSE tolerates view types), cast any view columns to
# their materialized equivalents, THEN convert. The cast is a no-op on files
# without view types, so this is safe for every mirror read.
# ASSUMES: arrow::open_dataset(url) |> dplyr::collect() is NOT a valid
# alternative — it hits the identical utf8_view conversion error. Non-view
# columns (including integer IDs) pass through untouched: no leading-zero risk.
tbl <- arrow::read_parquet(url, as_data_frame = FALSE)
sch <- tbl$schema
fields <- lapply(seq_len(length(sch$names)), function(i) {
fld <- sch$field(i - 1L) # $field() is 0-indexed (C++ convention)
ts <- fld$type$ToString()
# REASONING: check large_string_view before string_view — the former's
# ToString() contains the substring "string_view", so an unordered check
# would misclassify large_string_view as plain string_view.
new_type <- if (grepl("large_string_view", ts, fixed = TRUE)) arrow::large_utf8()
else if (grepl("string_view", ts, fixed = TRUE)) arrow::utf8()
else if (grepl("binary_view", ts, fixed = TRUE)) arrow::binary()
else fld$type
arrow::field(fld$name, new_type)
})
mirror_df <<- as.data.frame(tbl$cast(arrow::schema(fields)))
} else if (strategy == "lazy_csv") {
# REASONING: CSV files can be large. readr handles efficiently.
# ASSUMES: CSV has standard column names matching parquet schema.
mirror_df <<- readr::read_csv(url, show_col_types = FALSE)
} else {
cat(sprintf(" Skipping %s: unknown read_strategy '%s'\n", mirror_name, strategy))
next
}
cat(sprintf(" Success %s: %s rows\n", mirror_name, format(nrow(mirror_df), big.mark = ",")))
"success"
}, error = function(e) {
last_error <<- e
cat(sprintf(" Failed %s: %s\n", mirror_name, conditionMessage(e)))
"error"
})
if (identical(result, "success")) break
}
if (is.null(mirror_df)) stop(paste("All mirrors failed. Last error:", conditionMessage(last_error)))
# Hand off to the conventional working name (plain <- in the global frame is
# unaffected by the stats::df masking that breaks <<- above)
df <- mirror_df
# Apply filters locally
if (!is.null(years)) {
df <- df |> filter(year %in% years)
}
# For additional filters, apply with dplyr::filter():
# df <- df |> filter(fips == 6, charter == 1)
cat(sprintf(" After filters: %s rows\n", format(nrow(df), big.mark = ",")))
```
### Yearly Dataset (one file per year)
```python
def fetch_yearly_from_mirrors(
path_template: str,
years: list[int],
year_placeholder: str = "{year}",
filters: dict | None = None,
) -> pl.DataFrame:
"""Fetch yearly files and concatenate.
For datasets split into per-year files (e.g., enrollment, assessments),
download each year separately and concatenate.
Args:
path_template: Canonical path string with a year placeholder.
The year_placeholder is substituted with each year before fetching.
Example: "ccd/schools_ccd_enrollment_{year}"
years: List of years to fetch
year_placeholder: String in path_template to replace with year (default: "{year}")
filters: Additional column filters
Returns:
Concatenated, filtered Polars DataFrame
"""
frames = []
for year in years:
# Substitute {year} in the path template for this year
year_path = path_template.replace(year_placeholder, str(year))
print(f"\n Year {year}:")
try:
df = fetch_from_mirrors(
year_path,
filters=filters,
years=[year], # Filter to this specific year
)
frames.append(df)
print(f" → {df.shape[0]:,} rows")
except RuntimeError:
print(f" → SKIP: Year {year} not available from any mirror")
if not frames:
raise RuntimeError(f"No data retrieved for any year in {years}")
# NOTE: diagonal_relaxed upcasts a column whose dtype differs across years for EVERY year
# (e.g. CCD district race/sex are Float64 if 2016 is included); cast code columns back to Int64
# before joining (education-data-source-ccd SKILL.md, Common Pitfalls).
result = pl.concat(frames, how="diagonal_relaxed")
print(f"\n Combined: {result.shape[0]:,} rows x {result.shape[1]} cols")
return result
```
```r
# --- Yearly Dataset Fetch: one file per year, concatenated ---
# INTENT: Fetch yearly files and bind into a single data frame.
# Args:
# path_template: Canonical path with "{year}" placeholder.
# Example: "ccd/schools_ccd_enrollment_{year}"
# years: Integer vector of years to fetch.
# Returns: Combined tibble across all years.
# NOTE (R download timeout): if you run this yearly block WITHOUT the single-file
# Config block above, set the download timeout here too — download.file()'s 60s
# default truncates large yearly files. Any large per-year file needs it:
# options(timeout = max(600, getOption("timeout")))
# (See the single-file Config block for the full REASONING. Python is unaffected.)
path_template <- "ccd/schools_ccd_enrollment_{year}"
years <- c(2020, 2021, 2022)
frames <- list()
for (year in years) {
# Substitute {year} in the path template
year_path <- gsub("\\{year\\}", as.character(year), path_template)
cat(sprintf("\n Year %d:\n", year))
result <- tryCatch({
rate_limit()
last_error_yearly <- NULL
year_df <- NULL
for (mirror in mirrors) {
url <- build_mirror_url(mirror, year_path, "data")
cat(sprintf(" Trying %s: %s\n", mirror$name, url))
year_result <- tryCatch({
if (mirror$read_strategy == "eager_parquet") {
# REASONING (view-safe read): same utf8_view hazard as the single-file
# branch above — Polars-written mirror parquet may declare string_view
# columns the R arrow binding cannot convert directly. Read as an Arrow
# Table, cast view types to materialized, then convert. No-op on files
# without view types. Do NOT substitute open_dataset()|>collect() (same
# failure). See the single-file eager_parquet branch for the annotated form.
tbl <- arrow::read_parquet(url, as_data_frame = FALSE)
sch <- tbl$schema
fields <- lapply(seq_len(length(sch$names)), function(i) {
fld <- sch$field(i - 1L) # 0-indexed (C++ convention)
ts <- fld$type$ToString()
new_type <- if (grepl("large_string_view", ts, fixed = TRUE)) arrow::large_utf8()
else if (grepl("string_view", ts, fixed = TRUE)) arrow::utf8()
else if (grepl("binary_view", ts, fixed = TRUE)) arrow::binary()
else fld$type
arrow::field(fld$name, new_type)
})
year_df <<- as.data.frame(tbl$cast(arrow::schema(fields)))
} else if (mirror$read_strategy == "lazy_csv") {
year_df <<- readr::read_csv(url, show_col_types = FALSE)
}
"success"
}, error = function(e) {
last_error_yearly <<- e
cat(sprintf(" Failed %s: %s\n", mirror$name, conditionMessage(e)))
"error"
})
if (identical(year_result, "success")) break
}
if (is.null(year_df)) stop("All mirrors failed")
year_df <- year_df |> filter(year == !!year)
cat(sprintf(" -> %s rows\n", format(nrow(year_df), big.mark = ",")))
# REASONING: plain <- here. This tryCatch expression is evaluated in the script's
# global frame; a replacement-form <<- (frames[[i]] <<- x) looks `frames` up in
# the parent of the global environment, where it does not exist, so every year
# failed with "object 'frames' not found" and was reported as a SKIP.
frames[[length(frames) + 1]] <- year_df
"success"
}, error = function(e) {
cat(sprintf(" -> SKIP: Year %d: %s\n", year, conditionMessage(e)))
"skip"
})
}
if (length(frames) == 0) stop(paste("No data retrieved for any year in", paste(years, collapse = ", ")))
result <- bind_rows(frames)
cat(sprintf("\n Combined: %s rows x %d cols\n", format(nrow(result), big.mark = ","), ncol(result)))
```
### Large Files (100MB+), R: download-to-disk variant
For large mirror files, raising `options(timeout = ...)` (shown in the patterns
above) is sufficient — `arrow::read_parquet(url)` completes once the timeout
exceeds the transfer time (observed on the v1 copy of `ccd/schools_ccd_directory.parquet`,
~224MB, read directly from URL in ~100-285s across observed runs; the pinned v2 file is
~269MB, so expect proportionally longer — CDN throughput varies several-fold, so treat
any single timing as an observation, not a guarantee).
Prefer that in-memory path when it works, since it leaves no on-disk artifact to
manage.
When you want a **resumable, progress-aware** download instead — for very large
files (>~100MB) or flaky links where a mid-transfer failure is costly — download
to disk with `curl::multi_download()` and then do a view-safe local read. Unlike
`download.file()`, `curl::multi_download()` streams to disk, resumes partial
transfers, and does not depend on `getOption("timeout")` (it uses libcurl's own
connect/low-speed timeouts).
```r
library(arrow)
library(curl)
# --- Config ---
# INTENT: Fetch a large mirror parquet file to disk, resumably, then read it.
# REASONING: curl::multi_download() streams to disk and resumes partial transfers,
# so a dropped connection does not restart from zero. It bypasses R's
# download.file() 60s timeout entirely (uses libcurl connect/low-speed timeouts).
# ASSUMES: DATASET_PATH, root_url, and REVISION resolve to a real parquet URL;
# cache_dir is inside the project (never /tmp — outside the backup/audit boundary).
DATASET_PATH <- "ccd/schools_ccd_directory"
# Mirror root + pinned revision — keep in sync with mirrors.yaml (the HF entry's
# root_url + vintage.hf_revision). "vintage" = a dated Portal snapshot; "revision"
# = the HF git ref (branch/tag/commit SHA) that pins byte-exact bytes. Real fetch
# scripts get both from mirrors.yaml via build_mirror_url(); this standalone
# large-file sample inlines them, so REVISION is pinned here too (keep in sync
# with mirrors.yaml vintage.hf_revision).
root_url <- "https://huggingface.co/datasets/brhkim/education_data_portal_mirror_2026q3/resolve"
REVISION <- "0ad00ce0e232c96b0642459e4e7326607a8d26aa" # immutable commit SHA of the v2 upload
url <- sprintf("%s/%s/%s.parquet", root_url, REVISION, DATASET_PATH)
cache_dir <- file.path(PROJECT_DIR, "scripts", "scratch") # inside project; NOT /tmp
dir.create(cache_dir, recursive = TRUE, showWarnings = FALSE)
dest <- file.path(cache_dir, paste0(gsub("/", "_", DATASET_PATH), ".parquet"))
# --- Download (resumable) ---
dl <- curl::multi_download(url, dest, resume = TRUE)
# REASONING: multi_download() returns a status frame rather than throwing on HTTP
# errors, so validate explicitly. status_code 200 == fresh full transfer;
# 206 == a resume completed. Both are byte-complete successes.
stopifnot(isTRUE(dl$success), dl$status_code %in% c(200L, 206L))
cat(sprintf(" Downloaded %s to %s\n",
format(file.info(dest)$size, big.mark = ","), dest))
# --- View-safe local read ---
# REASONING: identical view-safe cast as the URL read — mirror files are
# Polars-written and may declare string_view columns the R arrow binding cannot
# convert directly. The cast is a no-op on plain-string files, so it is safe here.
tbl <- arrow::read_parquet(dest, as_data_frame = FALSE)
sch <- tbl$schema
fields <- lapply(seq_len(length(sch$names)), function(i) {
fld <- sch$field(i - 1L) # 0-indexed (C++ convention)
ts <- fld$type$ToString()
new_type <- if (grepl("large_string_view", ts, fixed = TRUE)) arrow::large_utf8()
else if (grepl("string_view", ts, fixed = TRUE)) arrow::utf8()
else if (grepl("binary_view", ts, fixed = TRUE)) arrow::binary()
else fld$type
arrow::field(fld$name, new_type)
})
df <- as.data.frame(tbl$cast(arrow::schema(fields)))
# --- Validate ---
# INTENT: confirm the download succeeded and the file reads fully.
# REASONING: dl$success + status 200/206 above guard the transfer, and parquet
# stores its footer at END of file — so a successful arrow read of dest is
# itself strong evidence the file is not truncated. For strict byte-level
# verification, HEAD the URL for content-length and compare to
# file.info(dest)$size before reading.
stopifnot(nrow(df) > 0)
cat(sprintf(" Read %s rows x %d cols\n", format(nrow(df), big.mark = ","), ncol(df)))
# Optional: delete the artifact once loaded to avoid retaining a large scratch file.
# file.remove(dest)
```
---
## Mirror Discovery
Use mirror discovery to observe what is available on one configured surface at request time. Discovery is explicit about object kind: request `"data"` for `.csv`/`.parquet` source objects or `"codebook"` for `.xls` objects. It never claims universal or future completeness.
Urban manifest rows are presentation/endpoint associations, not unique object rows. `discover_mirror_inventory()` therefore retains every raw row for bridge construction while deduplicating object discovery by the exact `file_dir/file_name` path. Hugging Face tree traversal follows RFC `Link` headers; Urban traversal follows the response envelope's `next` URL. Both traversals have a fail-closed page bound and reject malformed envelopes.
### Python Discovery Reference
```python
import re
from urllib.parse import urljoin
import requests
def _rfc_link_next(link_header: str) -> str | None:
"""Return the RFC Link target whose relation list contains ``next``."""
for match in re.finditer(r'<([^>]+)>\s*((?:;\s*[^,]+)*)', link_header or ""):
parameters = match.group(2)
relation = re.search(r';\s*rel\s*=\s*"?([^";,]+)"?', parameters, re.IGNORECASE)
if relation and "next" in relation.group(1).lower().split():
return match.group(1)
return None
def discover_mirror_inventory(
mirror_config: dict,
object_kind: str,
timeout: int = 30,
max_pages: int = 100,
session: requests.Session | None = None,
) -> dict:
"""Return canonical object keys plus raw and exact-path discovery evidence.
``raw_rows`` preserves every manifest/tree row for endpoint-object bridge or
audit use. ``object_rows`` contains one row per exact source path. The
``canonical_paths`` list is sorted, deduplicated, and extensionless.
"""
if object_kind not in {"data", "codebook"}:
raise ValueError("object_kind must be exactly 'data' or 'codebook'")
discovery = mirror_config.get("discovery") or {}
if discovery.get("method") != "http_json":
raise ValueError("mirror discovery method must be exactly 'http_json'")
# A versioned mirror declares discovery.url_template with a {revision} segment
# (the HF tree API) so discovery pins to the same vintage as its data URLs; an
# unversioned mirror (e.g. the Urban CSV fallback) declares a static url.
url_template = discovery.get("url_template")
if isinstance(url_template, str) and url_template:
next_url = url_template.format(revision=mirror_revision(mirror_config))
else:
next_url = discovery.get("url")
if not isinstance(next_url, str) or not next_url:
raise ValueError("mirror discovery requires a nonempty url or url_template")
if not isinstance(max_pages, int) or max_pages < 1:
raise ValueError("max_pages must be a positive integer")
client = session or requests.Session()
raw_rows = []
visited_urls = set()
page_number = 0
while next_url is not None:
page_number += 1
if page_number > max_pages:
raise RuntimeError(f"mirror discovery exceeded {max_pages}-page safety bound")
if next_url in visited_urls:
raise RuntimeError("mirror discovery pagination repeated a URL")
visited_urls.add(next_url)
response = client.get(next_url, timeout=timeout)
response.raise_for_status()
payload = response.json()
if isinstance(payload, dict):
if not isinstance(payload.get("results"), list):
raise ValueError("paginated discovery envelope must contain a results list")
page_rows = payload["results"]
raw_next = payload.get("next")
if raw_next is not None and not isinstance(raw_next, str):
raise ValueError("discovery envelope next value must be a URL string or null")
next_url = urljoin(response.url, raw_next) if raw_next else None
elif isinstance(payload, list):
page_rows = payload
next_target = _rfc_link_next(response.headers.get("Link", ""))
next_url = urljoin(response.url, next_target) if next_target else None
else:
raise ValueError("discovery response must be a results envelope or a list")
if not all(isinstance(row, dict) for row in page_rows):
raise ValueError("every discovery result must be an object")
raw_rows.extend(page_rows)
expected_extensions = {
"data": (".csv", ".parquet"),
"codebook": (".xls",),
}[object_kind]
file_dir_key = discovery.get("file_dir_key")
file_name_key = discovery.get("file_name_key")
path_key = discovery.get("file_path_key", "path")
uses_split_path = bool(file_dir_key or file_name_key)
if uses_split_path and not (file_dir_key and file_name_key):
raise ValueError("split-path discovery requires both file_dir_key and file_name_key")
object_by_exact_path = {}
canonical_to_exact_path = {}
for row in raw_rows:
if uses_split_path:
if file_dir_key not in row or file_name_key not in row:
raise ValueError("Urban discovery row lacks configured file_dir/file_name fields")
hide_value = row.get("hide", 0)
if hide_value not in {0, "0", False, None}:
continue
file_dir = row[file_dir_key]
file_name = row[file_name_key]
if not isinstance(file_dir, str) or not isinstance(file_name, str):
raise ValueError("Urban file_dir and file_name must be strings")
raw_path = f"{file_dir.strip('/')}/{file_name}"
else:
if row.get("type") != "file":
continue
if path_key not in row or not isinstance(row[path_key], str):
raise ValueError("tree file row lacks the configured string path field")
raw_path = row[path_key]
# REASONING: skip repo-root objects (build_manifest.parquet, README.md);
# every canonical data or codebook path is "{source}/{filename}", so a
# root-level parquet would otherwise be counted as a data key.
if "/" not in raw_path:
continue
if not raw_path.lower().endswith(expected_extensions):
continue
canonical_key = canonicalize_mirror_path(raw_path, object_kind)
prior_exact_path = canonical_to_exact_path.get(canonical_key)
if prior_exact_path is not None and prior_exact_path != raw_path:
raise ValueError(
f"distinct source paths collide on canonical key {canonical_key!r}"
)
canonical_to_exact_path[canonical_key] = raw_path
object_by_exact_path.setdefault(
raw_path,
{
"exact_path": raw_path,
"canonical_path": canonical_key,
"object_kind": object_kind,
"first_raw_row": row,
},
)
object_rows = [object_by_exact_path[path] for path in sorted(object_by_exact_path)]
return {
"canonical_paths": sorted(canonical_to_exact_path),
"object_rows": object_rows,
"raw_rows": raw_rows,
"pages_fetched": page_number,
}
def discover_mirror_files(
mirror_config: dict,
object_kind: str,
**kwargs,
) -> list[str]:
"""Return canonical extensionless keys discovered for one explicit object kind."""
return discover_mirror_inventory(mirror_config, object_kind, **kwargs)[
"canonical_paths"
]
# Usage: data and codebook inventories are separate and extensionless.
# data_keys = discover_mirror_files(MIRRORS[0], "data")
# codebook_keys = discover_mirror_files(MIRRORS[0], "codebook")
# target = "saipe/districts_saipe"
# print(target in data_keys)
# bridge_rows = discover_mirror_inventory(MIRRORS[1], "data")["raw_rows"]
```
### R Discovery Semantics
The maintained contract tests execute the Python reference. An R script that needs a mirror inventory must preserve the same semantics rather than copying the former one-page, extension-bearing snippet: resolve the discovery URL from `discovery$url_template` by substituting the mirror's revision (`mirror_revision(mirror)`) so the tree traversal pins to the same vintage as the data URLs, falling back to a static `discovery$url` for an unversioned mirror; follow Urban `next` URLs or Hugging Face RFC `Link` cursors to exhaustion within a page bound; retain raw Urban rows for bridge fidelity; deduplicate exact `file_dir/file_name`; select one explicit object kind; and pass each selected source path through `canonicalize_mirror_path()` before membership checks. Thus the R target is `"saipe/districts_saipe"`, never `"saipe/districts_saipe.parquet"`.
---
## Metadata File References
Codebook and metadata objects use `.xls` on the configured mirrors. The 2026-08-06 v2 build validation confirmed **91 XLS codebooks** present in the Hugging Face mirror (part of the 497-object tree; see `mirrors.yaml` coverage_notes) with zero codebook gaps; this is inventory evidence for that build, not a permanent completeness guarantee. Discover and probe the specific codebook before relying on it. Per the Truth Hierarchy below, codebooks rank **second** — below the actual data but above archived skill docs. They are not ingested into the analysis pipeline as data, but agents can download and read them programmatically to resolve ambiguities.
### Truth Hierarchy
When interpreting data values and resolving discrepancies between sources, apply this priority:
| Priority | Source | Rationale |
|----------|--------|-----------|
| 1 (highest) | **Actual data file** (parquet) | What you observe IS the truth |
| 2 | **Live codebook/metadata** (.xls in mirror) | Official documentation; may lag behind data |
| 3 (lowest) | **Archived skill docs** (variable-definitions.md) | Summarized; convenient but may drift |
- When skill docs contradict observed data → trust the data, flag the discrepancy
- When codebook contradicts observed data → trust the data, but investigate (codebook may describe a different year)
- When skill docs contradict codebook → trust the codebook, update skill docs
### When to Reference Codebooks
| Stage | Use Case |
|-------|----------|
| Stage 3 (Source Deep-Dive) | Verify variable definitions and coded values against authoritative codebook |
| Stage 6 (Context Application) | Resolve coded value ambiguities by consulting codebook |
| Data Onboarding | Reconcile observed data against codebook documentation |
| Discrepancy Investigation | When skill docs and observed data disagree, check codebook as tiebreaker |
### get_codebook_url()
Look up the codebook path from the `codebook` column in `datasets-reference.md`, then construct the full URL using the mirror's metadata configuration.
```python
def get_codebook_url(
codebook_path: str,
mirrors: list[dict] | None = None,
yaml_path: Path | None = None,
) -> str:
"""Construct a codebook URL from a datasets-reference.md codebook path.
Args:
codebook_path: Canonical codebook path from datasets-reference.md codebook column.
Example: "saipe/codebook_districts_saipe"
mirrors: Pre-loaded mirror configs. If None, loads from yaml_path.
yaml_path: Path to mirrors.yaml. If None, uses default.
Returns:
Full URL to the codebook file on the first mirror that has metadata config.
"""
if mirrors is None:
mirrors = load_mirrors(yaml_path or MIRRORS_YAML)
canonical_codebook_path = canonicalize_mirror_path(codebook_path, "codebook")
for mirror in mirrors:
if not mirror.get("metadata"):
continue
return build_mirror_url(mirror, canonical_codebook_path, "codebook")
raise ValueError("No mirror with metadata configuration found")
# Usage:
# url = get_codebook_url("saipe/codebook_districts_saipe")
# → "{root_url}/{revision}/saipe/codebook_districts_saipe.xls" (from first mirror with metadata config)
```
```r
# --- get_codebook_url: construct codebook URL from canonical path ---
# INTENT: Build a codebook download URL from a datasets-reference.md codebook path.
# Args:
# codebook_path: Canonical codebook path (e.g., "saipe/codebook_districts_saipe")
# Returns: Full URL string to the codebook .xls file.
codebook_path <- canonicalize_mirror_path(
"saipe/codebook_districts_saipe", "codebook"
)
codebook_url <- NULL
for (mirror in mirrors) {
if (is.null(mirror$metadata)) next
codebook_url <- build_mirror_url(mirror, codebook_path, "codebook")
break
}
if (is.null(codebook_url)) stop("No mirror with metadata configuration found")
cat(sprintf("Codebook URL: %s\n", codebook_url))
# Usage:
# codebook_url # e.g., "{root_url}/{revision}/saipe/codebook_districts_saipe.xls"
```
### fetch_codebook()
Download a codebook `.xls` file from a mirror to a local cache directory. Returns the local file path. Skips download if the file already exists locally (session-level caching).
```python
import httpx
from pathlib import Path
def fetch_codebook(
codebook_path: str,
cache_dir: Path | str = Path("data/codebooks"),
mirrors: list[dict] | None = None,
yaml_path: Path | None = None,
timeout: int = 60,
) -> Path:
"""Download a codebook .xls file from a mirror to a local cache.
Args:
codebook_path: Canonical codebook path from datasets-reference.md codebook column.
Example: "saipe/codebook_districts_saipe"
cache_dir: Local directory for cached codebook files.
mirrors: Pre-loaded mirror configs. If None, loads from yaml_path.
yaml_path: Path to mirrors.yaml. If None, uses default.
timeout: HTTP request timeout in seconds.
Returns:
Path to the downloaded .xls file.
"""
canonical_codebook_path = canonicalize_mirror_path(codebook_path, "codebook")
cache_dir = Path(cache_dir)
cache_dir.mkdir(parents=True, exist_ok=True)
# Derive local filename from the validated canonical path.
local_name = canonical_codebook_path.replace("/", "_") + ".xls"
local_path = cache_dir / local_name
if local_path.exists():
print(f" Codebook cached: {local_path}")
return local_path
if mirrors is None:
mirrors = load_mirrors(yaml_path or MIRRORS_YAML)
# Try each mirror with metadata config.
last_error = None
for mirror in mirrors:
if not mirror.get("metadata"):
continue
url = build_mirror_url(mirror, canonical_codebook_path, "codebook")
print(f" Fetching codebook from {mirror['name']}: {url}")
try:
_rate_limit()
with httpx.Client(timeout=timeout, follow_redirects=True) as client:
resp = client.get(url)
resp.raise_for_status()
local_path.write_bytes(resp.content)
print(f" ✓ Saved: {local_path} ({len(resp.content):,} bytes)")
return local_path
except Exception as e:
last_error = e
print(f" ✗ {mirror['name']} failed: {e}")
continue
raise RuntimeError(
f"All mirrors failed for codebook '{codebook_path}'. Last error: {last_error}"
)
# Usage:
# path = fetch_codebook("saipe/codebook_districts_saipe")
# → downloads to data/codebooks/saipe_codebook_districts_saipe.xls
```
```r
# --- fetch_codebook: download codebook .xls to local cache ---
# INTENT: Download a codebook .xls file from a mirror to a local cache directory.
# Returns the local file path. Skips download if file already exists (session cache).
# Args:
# codebook_path: Canonical codebook path (e.g., "saipe/codebook_districts_saipe")
# cache_dir: Local directory for cached codebook files.
codebook_path <- canonicalize_mirror_path(
"saipe/codebook_districts_saipe", "codebook"
)
cache_dir <- "data/codebooks"
dir.create(cache_dir, recursive = TRUE, showWarnings = FALSE)
# Derive local filename from canonical path
local_name <- paste0(gsub("/", "_", codebook_path), ".xls")
local_path <- file.path(cache_dir, local_name)
if (file.exists(local_path)) {
cat(sprintf(" Codebook cached: %s\n", local_path))
} else {
last_error_cb <- NULL
downloaded <- FALSE
for (mirror in mirrors) {
if (is.null(mirror$metadata)) next
url <- build_mirror_url(mirror, codebook_path, "codebook")
cat(sprintf(" Fetching codebook from %s: %s\n", mirror$name, url))
result_cb <- tryCatch({
rate_limit()
resp <- httr2::request(url) |>
httr2::req_timeout(60) |>
httr2::req_perform()
writeBin(httr2::resp_body_raw(resp), local_path)
cat(sprintf(" Saved: %s (%s bytes)\n", local_path,
format(file.size(local_path), big.mark = ",")))
downloaded <<- TRUE
"success"
}, error = function(e) {
last_error_cb <<- e
cat(sprintf(" Failed %s: %s\n", mirror$name, conditionMessage(e)))
"error"
})
if (identical(result_cb, "success")) break
}
if (!downloaded) {
stop(paste("All mirrors failed for codebook. Last error:",
conditionMessage(last_error_cb)))
}
}
# Usage:
# local_path # e.g., "data/codebooks/saipe_codebook_districts_saipe.xls"
```
### read_codebook()
Download (if needed) and read a codebook into a dict of DataFrames, one per sheet. This is the primary entry point for agents that need to inspect codebook contents.
```python
import polars as pl
def read_codebook(
codebook_path: str,
cache_dir: Path | str = Path("data/codebooks"),
mirrors: list[dict] | None = None,
yaml_path: Path | None = None,
) -> dict[str, pl.DataFrame]:
"""Download and read a codebook .xls file. Returns {sheet_name: DataFrame}.
Combines fetch_codebook() + sheet reading into a single call.
Uses openpyxl for .xlsx and xlrd for .xls files.
Args:
codebook_path: Canonical codebook path from datasets-reference.md codebook column.
Example: "saipe/codebook_districts_saipe"
cache_dir: Local directory for cached codebook files.
mirrors: Pre-loaded mirror configs. If None, loads from yaml_path.
yaml_path: Path to mirrors.yaml. If None, uses default.
Returns:
Dict mapping sheet names to Polars DataFrames.
"""
local_path = fetch_codebook(
codebook_path, cache_dir=cache_dir, mirrors=mirrors, yaml_path=yaml_path
)
# Read all sheets — try xlrd first (.xls), fall back to openpyxl (.xlsx)
import pandas as pd
try:
sheets = pd.read_excel(local_path, sheet_name=None, engine="xlrd")
except Exception:
sheets = pd.read_excel(local_path, sheet_name=None, engine="openpyxl")
# Convert pandas DataFrames to Polars
result = {}
for name, pdf in sheets.items():
result[name] = pl.from_pandas(pdf)
sheet_summary = ", ".join(
f"{name} ({df.shape[0]}×{df.shape[1]})" for name, df in result.items()
)
print(f" Codebook sheets: {sheet_summary}")
return result
# Usage:
# sheets = read_codebook("saipe/codebook_districts_saipe")
# for name, df in sheets.items():
# print(f"\n--- {name} ---")
# print(df.head())
```
```r
# --- read_codebook: download (if needed) and read codebook into named list of data frames ---
# INTENT: Download and read a codebook .xls file. Returns named list of tibbles, one per sheet.
# Requires the readxl package.
# Assumes fetch_codebook R block above has already run and local_path is set.
library(readxl)
sheet_names <- readxl::excel_sheets(local_path)
sheets <- setNames(
lapply(sheet_names, function(s) readxl::read_excel(local_path, sheet = s)),
sheet_names
)
sheet_summary <- paste(
vapply(names(sheets), function(nm) {
sprintf("%s (%dx%d)", nm, nrow(sheets[[nm]]), ncol(sheets[[nm]]))
}, character(1)),
collapse = ", "
)
cat(sprintf(" Codebook sheets: %s\n", sheet_summary))
# Usage:
# for (nm in names(sheets)) {
# cat(sprintf("\n--- %s ---\n", nm))
# print(head(sheets[[nm]]))
# }
```
---
## Format Handling
Format-specific read behavior is driven by the mirror's `read_strategy` field in `mirrors.yaml`:
### `eager_parquet` (e.g., parquet files)
- Direct read with `pl.read_parquet(url)` — Polars handles HTTP natively
- Schema is embedded in the file (no inference needed)
- Columnar format: efficient for column-subset reads
- Compressed: typically 3-10x smaller than CSV
- Filters applied after loading into memory
- **R only — view-safe parquet read required:** The mirror files are Polars-written,
and some declare string columns as `string_view` in the parquet-native schema
(75 of the 406 data parquets at rev `0ad00ce0`, probed 2026-10-28, all carried forward from v1, e.g. `edfacts/{schools,districts}_edfacts_grad_rates_2010..2019`; `saipe/districts_saipe` has none).
The R `arrow` binding reads these at the C++ layer but fails at the
Table->data.frame step with `cannot handle Array of type <utf8_view>`. Use the
view-safe read pattern shown in the R mirror-loop `eager_parquet` branch above:
read as an Arrow Table (`as_data_frame = FALSE`), cast any view columns to their
materialized types, then convert. The cast is a no-op on plain-string files
(e.g., `meps/schools_meps`), so it is safe for every read. **Do not** substitute
`arrow::open_dataset(url) |> dplyr::collect()` — it hits the same error. Python
(Polars/pyarrow) is unaffected.
- **R only — large-file download timeout:** `arrow::read_parquet(url)` routes its
transfer through R's `download.file()`, which caps the ENTIRE transfer at
`getOption("timeout")` (default 60s). Large mirror files (e.g.,
`ccd/schools_ccd_directory.parquet`, ~269MB) truncate at ~60s with a
`downloaded length X != reported length Y` warning and silently fall through to
the next mirror in priority order (the CSV fallback, in the default
configuration). Raise the timeout before the mirror loop:
`options(timeout = max(600, getOption("timeout")))`. For very large or flaky
transfers, prefer the download-to-disk variant (`curl::multi_download()`) in the
Mirror Resolution section. Python (Polars/pyarrow) is unaffected.
### `lazy_csv` (e.g., CSV files)
- Use `pl.scan_csv(url, infer_schema_length=10000)` for lazy loading
- Set `infer_schema_length=10000` to avoid type inference errors on large files
- Apply filters in the lazy frame before `.collect()` to minimize memory
- Large files (500MB+) — always use lazy loading, never `pl.read_csv()` directly
- **Zero-padded ID columns (CRDC, EDFacts, SAIPE) — general principle:** Portal ID columns (`ncessch`→12 chars, `leaid`→7 chars, and CRDC's `crdc_id`) are zero-padded strings whose leading zeros carry state-FIPS identity (FIPS 01-09 states: AL, AK, AZ, AR, CA, CO, CT — ~19% of school rows). CSV type inference reads them as Int64 and silently drops the leading zeros, splitting the join key for those states. Parquet reads preserve the stored type, and that does not protect the IDs: on the pinned mirror (rev `0ad00ce0`, probed 2026-10-28) `ncessch` is Int64 in `ccd/schools_ccd_enrollment_{year}` (1986-2023; String in 2024), `meps/schools_meps` and `edfacts/schools_edfacts_grad_rates_2019`, and `leaid` is Int64 in `ccd/school-districts_lea_directory`, `ccd/schools_ccd_lea_enrollment_{year}` (1986-2023; String in 2024) and `saipe/districts_saipe`, while `ccd/schools_ccd_directory` and `ccd/districts_ccd_finance` store String. Cast to String, pad and assert on every read, parquet or CSV, before joining. **Two defenses are needed, not one:** (1) force-string on read, AND (2) pad-and-assert *after* read. Force-string alone is insufficient because a source file can ship an ID that is *already* truncated — see the EDFacts 2019 note below — in which case `col_character()`/`schema_overrides` faithfully preserve a value that is already wrong. Only pad-to-width plus a width assertion recovers and verifies these.
- **EDFacts 2019 truncated-ID trap (field-confirmed, HIGH impact):** In a native R-mode pipeline run, 2019 EDFacts grad-rate CSVs delivered 11-character `ncessch` for single-digit-FIPS states (1/2/4/5/6/8/9), versus 12-character in 2015-2018 — the source file itself had already lost the leading zero. Forcing string on read did **not** restore it (there was nothing to restore; the character was gone from the file). The working fix was `str_pad(ncessch, 12)` / `str_pad(leaid, 7)` immediately after each CSV read, before any bind/join, plus `stopifnot(nchar == 12/7)` (R) or an `assert` on `.str.len_chars()` (Python) to catch the failure loudly. In that run this repaired 43,223 short IDs, all localized to 2019. Treat force-string + pad + width-assert as the standard CSV-fallback ID recipe for EDFacts and SAIPE, not just CRDC.
- **Force-string on read (Python, Polars):** `schema_overrides={"ncessch": pl.Utf8, "leaid": pl.Utf8}` — add `"crdc_id": pl.Utf8` for CRDC datasets. This is defense (1).
- **Pad-and-assert after read (Python, Polars):** defense (2) — recovers already-truncated IDs and fails loudly if a width is unexpected:
```python
# INTENT: enforce canonical zero-padded ID widths after CSV fallback read.
# REASONING: force-string preserves what the file holds, but 2019 EDFacts ships
# already-truncated ncessch (11-char) for single-digit-FIPS states; only
# pad-to-width recovers them. ncessch=12, leaid=7.
# ASSUMES: ids are strings (defense 1 applied); a value longer than target width
# is a genuine anomaly, not a pad candidate.
df = df.with_columns(
pl.col("ncessch").cast(pl.Utf8).str.zfill(12),
pl.col("leaid").cast(pl.Utf8).str.zfill(7),
)
assert (df["ncessch"].str.len_chars() == 12).all(), "ncessch width != 12 after pad"
assert (df["leaid"].str.len_chars() == 7).all(), "leaid width != 7 after pad"
```
- **Force-string on read (R, readr):** `col_types = readr::cols(ncessch = readr::col_character(), leaid = readr::col_character())` — add `crdc_id = readr::col_character()` for CRDC. This is defense (1).
- **Pad-and-assert after read (R, readr/stringr):** defense (2):
```r
# INTENT: enforce canonical zero-padded ID widths after CSV fallback read.
# REASONING: col_character() preserves what the file holds, but 2019 EDFacts ships
# already-truncated ncessch (11-char) for single-digit-FIPS states; only
# str_pad recovers them. ncessch=12, leaid=7.
# ASSUMES: ids are character (defense 1 applied); a value wider than the target is
# a genuine anomaly, not a pad candidate.
library(stringr)
df <- df |>
mutate(
ncessch = str_pad(ncessch, 12, pad = "0"),
leaid = str_pad(leaid, 7, pad = "0")
)
stopifnot(all(nchar(df$ncessch) == 12), all(nchar(df$leaid) == 7))
```
- **Per-source specifics:** CRDC also has `crdc_id` (force-string; not an FIPS-padded key). EDFacts is the confirmed truncated-ID case (2019 grad rates). SAIPE's `leaid` is Int64 in the Portal even in parquet — a numeric read lost leading zeros for 14.6% of rows in the field run — so SAIPE needs `leaid`→7 pad-and-assert before any school→district join. See `education-data-source-crdc`, `education-data-source-edfacts`, and `education-data-source-saipe` skills for source-level detail.
- **R only — large-file download timeout:** `readr::read_csv(url)` routes its transfer through `download.file()` exactly like the parquet read, so the same 60s `getOption("timeout")` cap truncates large CSVs (these files reach 500MB+). Apply the same `options(timeout = max(600, getOption("timeout")))` before reading — see the `eager_parquet` bullet above.
---
## Error Handling
| Error | Cause | Resolution |
|-------|-------|------------|
| HTTP 404 | File not in this mirror | Fall through to next mirror |
| Timeout | Large file or slow connection | Increase timeout; fall through. **R only:** both read strategies route through `download.file()`, capped at `getOption("timeout")` (default 60s) — raise it with `options(timeout = max(600, getOption("timeout")))` before the loop, or use `curl::multi_download()` for very large files. A silent truncation shows as `downloaded length X != reported length Y`. |
| Schema mismatch | CSV column types differ from parquet | Use `infer_schema_length=10000` |
| Empty DataFrame | Filters too restrictive | Check filter values; verify year availability |
| All mirrors failed | Dataset not available in any mirror | STOP and escalate to user |
| Codebook read error (xlrd) | `.xls` format not readable by xlrd | Auto-falls back to openpyxl engine |
| Codebook read error (both) | Corrupt or non-Excel file served | Check URL manually; try alternate mirror |
| Codebook empty sheets | Codebook has no data rows | Likely a metadata-only file; inspect raw `.xls` |
---
## Portal Integer Encoding Notes
**CRITICAL:** The Portal uses integer codes, not string labels. When filtering downloaded data:
### Demographic Variables
| Variable | Integer Values | NOT These Strings |
|----------|----------------|-------------------|
| Race | 1-7, 9 (Unknown), 99 (total); IPEDS adds 8; 9 is populated only in CCD (2016+) and IPEDS, and EDFacts has no 6 | WH, BL, HI, AS |
| Sex | 1 (Male), 2 (Female), 9 (Unknown), 99 (Total); every dictionary also declares 3 (Gender Not-Identified). Populated 3/9: CCD 9 from 2016; CRDC 3 in the 2021 enrollment file only; IPEDS 3 and 9 from 2022 (admissions-enrollment, fall-enrollment-race), plus an undeclared 4 in fall-enrollment-race 2022, which has no 9 | M, F |
| Grade | -1 to 15, 99 (total), 999 (not specified) | PK, KG, 01 |
### Grade Encoding (SEMANTIC TRAP!)
```python
# WRONG - filters out Pre-K students!
df = df.filter(pl.col("grade") >= 0)
# RIGHT - grade=-1 is Pre-K, NOT missing data
pre_k = df.filter(pl.col("grade") == -1)
k_12 = df.filter(pl.col("grade").is_between(0, 12))
total = df.filter(pl.col("grade") == 99)
```
```r
# WRONG - filters out Pre-K students!
df <- df |> filter(grade >= 0)
# RIGHT - grade=-1 is Pre-K, NOT missing data
pre_k <- df |> filter(grade == -1)
k_12 <- df |> filter(between(grade, 0, 12))
total <- df |> filter(grade == 99)
```
### Variable Names Are Lowercase
Portal variable names are lowercase:
- `enrollment` not `MEMBER`
- `grade` not `GRADE`
- `fips` not `FIPS`
---
## IAT Documentation for Fetch Scripts
Every fetch script must include these IAT comments:
```python
# --- Mirror Resolution ---
# INTENT: Download {dataset_name} from the fastest available mirror.
# REASONING: Mirrors are tried in priority order per mirrors.yaml config.
# Format-specific read strategy is driven by each mirror's read_strategy field.
# ASSUMES: Mirror URLs are current and accessible; each mirror uses the same canonical
# path with its own root_url and format (plus the pinned revision on the HF mirror).
# Year/filter columns exist in the dataset with expected names.
# Portal uses integer encoding: grade=-1 is Pre-K (NOT missing); race/sex code sets
# vary by source and year (education-data-explorer variable-codes.md).
# REFERENCE: mirrors.yaml for mirror config, datasets-reference.md for canonical paths.
```
```r
# --- Mirror Resolution ---
# INTENT: Download {dataset_name} from the fastest available mirror.
# REASONING: Mirrors are tried in priority order per mirrors.yaml config.
# Format-specific read strategy is driven by each mirror's read_strategy field.
# ASSUMES: Mirror URLs are current and accessible; each mirror uses the same canonical
# path with its own root_url and format (plus the pinned revision on the HF mirror).
# Year/filter columns exist in the dataset with expected names.
# Portal uses integer encoding: grade=-1 is Pre-K (NOT missing); race/sex code sets
# vary by source and year (education-data-explorer variable-codes.md).
# REFERENCE: mirrors.yaml for mirror config, datasets-reference.md for canonical paths.
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |