Forecasting Children in 24 hour Foster Care Placements across NYC Community Districts

Methods

Data Parsing & Cleaning

Each Excel file contained non-standard formatting: borough names were interspersed as unlabeled rows, district names included alphanumeric CD codes (e.g. BX01, QN12), totals and footnotes were embedded inline, and column headers varied slightly across years. A Python parser using pandas and regex was written to:

• Detect borough-level section headers and carry them forward as a grouping key
• Extract CD codes and neighborhood names using a consistent regex pattern
• Skip subtotal rows, unknown-CD rows, and footnote lines
• Concatenate all five years into a single tidy long-format DataFrame (year, borough, cd_code, cd_name, count)

Exploratory Analysis

Before forecasting, the data was examined at multiple levels of aggregation: citywide totals, borough totals, and individual community district trends. A consistent downward trend was observed citywide from 2020–2023, with a near-plateau between 2023 and 2024 (4,994 to 5,005 — a marginal increase of 11 children). This leveling off informs model selection, as pure linear extrapolation may overstate future declines.

Model Selection Rationale

With only five annual observations per district, model selection prioritized parsimony and interpretability. Four models were implemented:

Model	MAE	MAPE	Notes
Exp Smoothing	8.4	—	Adapts to recent levels; robust with flat/leveling trends
ARIMA (1,1,0)	9.4	—	Differencing handles non-stationarity; limited by 5 obs
Ensemble (avg)	9.6	—	Average of all three; most conservative projection
Linear Trend	13.7	—	OLS extrapolation; overprojects decline after 2023 plateau

MAPE is listed as undefined because some community districts approach zero placements, making percentage-based error metrics unreliable. MAE is used as the primary evaluation metric.

Validation — Leave-One-Out

Given the small sample size, a leave-one-out approach was used: models were trained on 2020–2023 data for each of the 59 community districts and then evaluated against the known 2024 values. This produced 59 independent test cases per model. Exponential Smoothing achieved the lowest mean absolute error of 8.4 children per district — meaning its 2024 predictions were off by an average of roughly 8 children, against a citywide mean of ~89 children per district.

Forecasting Horizon

All four models were then retrained on the full 2020–2024 dataset and used to generate projections for 2025, 2026, and 2027. The Ensemble forecast (average of all three models) is treated as the primary projection. A ±12% uncertainty band is applied to the Ensemble line in the community district spotlight charts, reflecting the typical spread observed during validation.

Methodology

Technology Stack

• Python 3 — core scripting language
• pandas — data loading, parsing, and long-format reshaping
• NumPy — array operations and manual feature construction
• statsmodels — ExponentialSmoothing and ARIMA model fitting
• scikit-learn — LinearRegression, StandardScaler, MAE metric
• matplotlib — all visualizations (dark-theme dashboard + individual PNGs)

Forecasting Pipeline

• Load & parse: regex-based extraction from raw Excel into tidy DataFrame
• Validate: leave-one-out test across all 56 CDs, evaluate MAE per model
• Retrain: fit all models on full 2020–2024 history
• Forecast: project 2025–2027 per district and citywide
• Export: per-district CSV and PNG dashboard with 5 individual charts

Limitations

• Five annual data points per district is a small sample — forecasts should be treated as directional projections, not precise predictions
• No external covariates (poverty rates, housing instability, policy changes) are incorporated
• The 2023–2024 plateau is not explained by the models — it may reflect policy interventions, demographic shifts, or reporting changes
• Placement location ≠ child's home district; the data does not track individual children across years
• Future policy changes — such as expanded preventive services or immigration enforcement effects — could significantly alter trends