Australian Hospital Separations ETL Pipeline 📊
Automated extraction, transformation & visual analytics for AIHW Admitted-Patient-Care tables.
Project Overview
This pipeline fetches the latest Excel workbooks published by the Australian Institute of Health & Welfare (AIHW), cleans and normalises every sheet to a tidy relational schema in PostgreSQL, then spins up an interactive Streamlit dashboard (7 charts + optional profiling) for rapid exploratory analysis and auto-generated insights.
Key Features
- Dynamic extraction – web-scrapes fresh workbook links, detects headers on the fly.
- Smart transformation – deduplicates columns, harmonises categories, coerces numeric types.
- PostgreSQL load – idempotent upserts into
staging_admissions&clean_admissions. - Streamlit analytics – bar, line, pie, heat-map, treemap, choropleth & box-plot + NLP insight text.
- One-click data-profiling (optional) via
ydata-profiling.
Tech Stack ⚙️
- Python ·
requests,BeautifulSoup,pandas,openpyxl - SQLAlchemy ➜ PostgreSQL
- Streamlit + Plotly Express
- Automated via single
main.py(CI / cron friendly) - Env-configured
DB_URLfor easy cloud deploy
Try It Yourself
Clone the repo, create a PostgreSQL database, set DB_URL in
.env, then run:
pip install -r requirements.txt
python main.py # runs full ETL
streamlit run streamlit_app.py # runs Streamlit server