brycewang-stanford/Auto-Empirical-Research-Skills / skills/17-DAAF-Contribution-Community-daaf/dot-claude/skills/education-data-query
education-data-query
VerifiedDownloads education datasets from configured mirror sources (parquet/CSV) with local Polars filtering. Use when writing fetch scripts or retrieving CCD, IPEDS, CRDC, SAIPE data. Load after education-data-explorer — retrieval here, not discovery.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 11.0 KB |
| references/datasets-reference.md | markdown | 19.2 KB |
| references/fetch-patterns.md | markdown | 21.8 KB |
| references/filters-reference.md | markdown | 10.2 KB |
| references/mirrors.yaml | config | 5.0 KB |
| references/query-patterns.md | markdown | 15.9 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.
---
## 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 and format 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 and format 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
"""
_rate_limit()
last_error = None
for mirror in MIRRORS:
name = mirror["name"]
strategy = mirror["read_strategy"]
timeout = mirror["timeout"]
# Build URL from template + canonical path
url = mirror["url_template"].format(root_url=mirror["root_url"], path=path, format=mirror["format"])
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}")
```
### 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}")
result = pl.concat(frames, how="diagonal_relaxed")
print(f"\n Combined: {result.shape[0]:,} rows x {result.shape[1]} cols")
return result
```
---
## Mirror Discovery
Use mirror discovery to check what files are currently available before attempting a fetch. The discovery method for each mirror is defined in `mirrors.yaml`.
### Generic Discovery Function
```python
import requests
def discover_mirror_files(mirror_config: dict) -> list[str] | None:
"""Query a mirror's discovery endpoint to list available files.
Args:
mirror_config: A single mirror entry from mirrors.yaml, including
its 'discovery' section.
Returns:
List of file paths available in the mirror, or None if discovery
is not supported (e.g., method is 'known_complete').
"""
discovery = mirror_config.get("discovery", {})
method = discovery.get("method")
if method == "http_json":
url = discovery["url"]
file_filter = discovery.get("file_filter", "*")
response = requests.get(url, timeout=30)
response.raise_for_status()
raw = response.json()
# Handle paginated response envelopes (e.g., Urban CSV returns
# {"count": N, "results": [...]} instead of a flat list).
if isinstance(raw, dict) and "results" in raw:
entries = raw["results"]
elif isinstance(raw, list):
entries = raw
else:
print(f" Unexpected discovery response type: {type(raw)}")
return None
# Handle response format differences between mirrors.
# Some mirrors return separate dir + name fields; others use a single path field.
# The keys are configured in mirrors.yaml's discovery section.
file_dir_key = discovery.get("file_dir_key")
file_name_key = discovery.get("file_name_key")
path_key = discovery.get("file_path_key", "path")
if file_dir_key and file_name_key:
# Construct paths from separate dir + name fields
paths = [
f"{e[file_dir_key]}/{e[file_name_key]}"
for e in entries
if isinstance(e, dict) and e.get("hide", 0) == 0
]
else:
# Single path field
paths = [e[path_key] for e in entries if isinstance(e, dict) and e.get("type") == "file"]
# Apply file_filter if specified (simple suffix matching)
if file_filter != "*":
suffix = file_filter.lstrip("*")
paths = [p for p in paths if p.endswith(suffix)]
return paths
elif method == "known_complete":
# This mirror has complete coverage — all datasets in
# datasets-reference.md are available. No query needed.
return None
else:
print(f" Unknown discovery method: {method}")
return None
# Usage example:
# Check if a specific dataset is available in the primary mirror
# mirror = MIRRORS[0] # highest priority
# files = discover_mirror_files(mirror)
# if files is not None:
# target = "saipe/districts_saipe.parquet"
# if target in files:
# print("Available in primary mirror")
# else:
# print("Not in primary mirror — will fall through to next")
```
---
## Metadata File References
Codebook and metadata files are available alongside data files in both mirrors. These are `.xls` files that document variable definitions, coded values, and data structure. 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)
for mirror in mirrors:
meta = mirror.get("metadata")
if not meta:
continue
fmt = meta["formats"][0] # e.g., "xls"
template = meta["url_template"]
root_url = mirror["root_url"]
# All mirrors resolve codebook the same way using the canonical path
url = template.format(root_url=root_url, path=codebook_path, format=fmt)
return url
raise ValueError("No mirror with metadata configuration found")
# Usage:
# url = get_codebook_url("saipe/codebook_districts_saipe")
# → "{root_url}/saipe/codebook_districts_saipe.xls" (from first mirror with metadata config)
```
### 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.
"""
cache_dir = Path(cache_dir)
cache_dir.mkdir(parents=True, exist_ok=True)
# Derive local filename from the canonical path (flatten source/filename → filename.xls)
local_name = 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:
meta = mirror.get("metadata")
if not meta:
continue
fmt = meta["formats"][0] # e.g., "xls"
template = meta["url_template"]
root_url = mirror["root_url"]
url = template.format(root_url=root_url, path=codebook_path, format=fmt)
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
```
### 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())
```
---
## 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
### `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
- **CRDC ID columns:** When reading any CRDC dataset from CSV, pass `schema_overrides={"ncessch": pl.Utf8, "leaid": pl.Utf8, "crdc_id": pl.Utf8}` to preserve zero-padded identifiers. Without this override, Polars infers Int64, destroying leading zeros for FIPS 01-09 states (~19% of rows). See `education-data-source-crdc` skill.
---
## 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 |
| 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, 99 (total) | WH, BL, HI, AS |
| Sex | 1 (Male), 2 (Female), 99 | M, F |
| Grade | -1 to 13, 99 (total) | 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)
```
### 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.
# Year/filter columns exist in the dataset with expected names.
# Portal uses integer encoding: grade=-1 is Pre-K (NOT missing), race=1-7, sex=1-2.
# 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 |