CASE STUDY · DATA ANALYSIS
An automated pipeline that combines four public data sources into a monthly Pennsylvania housing report, with forensic flags for unusual price movement, refreshed and published with no manual work.
Python GitHub Actions Chart.js Power BI Pandas
Problem
Housing data for Pennsylvania is scattered across Zillow, the Census Bureau, the Urban Institute, and OpenStreetMap. Each source updates on its own schedule and format. Answering a simple question like “which counties are heating up?” meant pulling and reconciling four datasets by hand every month.
Data
- Zillow: home values and price trends
- U.S. Census: population and housing characteristics
- Urban Institute: housing finance and affordability context
- OpenStreetMap (Overpass API): location and amenity data
Approach
- Explore, then promote. Analysis starts in notebooks; stable logic moves into a tested Python package.
- Combine and model. Sources are cleaned and merged into monthly and quarterly tables by county.
- Flag anomalies. Forensic checks score each area on severity, and a momentum label (Hot / Steady / Cooling) is assigned from year-over-year change.
- Automate. A GitHub Actions workflow refreshes the data monthly, renders charts, and publishes to GitHub Pages.
- Make it resilient. When the Overpass API throttled the automation servers, I built a fallback chain: fresh fetch → cached data → seed data → controlled failure, so the report never silently breaks.
Result
4
public data sources merged into one report
0
manual steps: refreshes and publishes monthly on its own
3-tier
fallback that keeps the report running when an API fails
Outputs include an interactive Chart.js dashboard, Power BI–ready datasets with flag columns, and charts for top movers, consistency, and flagged trends.
What I learned
Automation needs guardrails. An AI coding agent once committed placeholder data straight to the main branch. I caught it, and it pushed me to require pull requests and passing tests before anything ships.
Full walkthrough
Step-by-step build notes, code, flag logic, and the downloadable Power BI dataset are available to community members.