Live · de25-hemtenta-christofer.onrender.com

MYH Data Pipeline

by Christofer Lindholm · Data Engineering

8 years · 8 Excel files · 9,983 records → PostgreSQL + FastAPI. Swedish vocational education (YH) application rounds, harmonized from inconsistent source data into one clean, queryable dataset.

Python 3.11pandasPostgreSQL 16FastAPIpsycopg2DockerNeon.techRender.com

View dashboard Explore records Source

Years covered: 2018–25
Source files: 8
Records: 9,983
API endpoints: 17

02 / Architecture

Pipeline overview

Raw MYH files

8 yearly Excel files from 2018-2025. Sheet names, header rows, and columns differ across years.

Harmonize

One column map turns the yearly files into one canonical applications schema with traceable source columns.

Clean & derive

Normalize decisions, fix Landsting to Region, coerce numeric values, and derive distance/multi-municipality flags.

Validate

Notebook and API checks verify row counts, year coverage, required values, and normalized decision values.

PostgreSQL

9,983 rows loaded into one typed applications table with indexes for common filters and stats.

FastAPI

17 endpoints expose records, stats, CSV export, refresh, prediction, and SCB municipality enrichment.

ML model

Random Forest model estimates approval probability from historical MYH application patterns.

SCB enrichment

Separate enrichment notebook joins population, employment, and unemployment context from Statistics Sweden.

Dashboard

React, TanStack Query, and Recharts present the API, notebooks, data explorer, ML model, and enrichment layer.

03 / Walkthrough

Project story

Each step below covers the decisions made at that stage, from the source files to the live API.

04 / Notebooks

Jupyter notebooks

Full notebooks including all code and cell outputs. Click any tab to switch notebook.

Excel ingestion, harmonization, cleaning, enrichment, validation, and CSV export.

data_preparation.ipynbopen full screen

05 / Statistics

Live dashboard

All charts hit the FastAPI directly, no mock data, no caching layer. If the API is cold-starting on Render's free tier, the data may take up to 60 seconds to appear. Charts will populate automatically.

Total applications

Curated rows in PostgreSQL

Overall approval

Highest volume

Lowest approval

Applications over time

Volume trend with highlighted peak and latest year

Approval rate trend

Approved applications divided by total

Decision comparison

Approved, rejected, and withdrawn by year

County comparison

Top counties by application volume

Education areas

Volume and approval rate by subject area

Study form comparison

On-site, distance, and other study forms

Top providers

Largest providers by application count

06 / Records

Data explorer

Query GET /applications with combinable filters. 50 rows per page, server-side pagination.

Filters

CSV

All filters except Area query the full dataset server-side. Area filters only the rows on the current page.

Year	Program	Area	Points	Provider	City	Decision	Form	Exam	Details

Page 1 · loaded 0 rows

External enrichment: Statistics Sweden municipality context

MYH application activity joined with SCB population and BAS labour-market statistics. The charts use municipality/kommun, not informal city labels.

MYH2018–2025

SCB—

Time window

MYH 2018–2025→SCB —

All MYH years 2018–2025 are aggregated. Each municipality is joined with its most recent SCB snapshot (population stops at 2024, labour-market data at 2024).

Normalization

per 100k residents

Raw totals favor large municipalities. Per-capita values make smaller municipalities comparable.

Interpretation

context, not cause

Employment and unemployment describe the local labour market. They do not prove why MYH approved an application.

07 / Reference

API endpoints

Method	Path	Description
GET	/pipeline/status	Live data quality checks against the applications table.
GET	/health	Database connectivity and row-count check.
GET	/applications	List applications with year, decision, region, municipality, provider, and study-form filters.
GET	/applications/{diarienummer}	All rows for one MYH case number.
GET	/providers	Distinct education provider names.
GET	/providers/{name}/applications	Provider search using case-insensitive contains matching.
GET	/stats/by-year	Yearly totals and approval rates.
GET	/stats/by-region	County statistics; excludes Flera kommuner by default.
GET	/stats/by-education-area	Volume and approval rate grouped by education area.
GET	/stats/by-study-form	Comparison by Bunden, Distans, and other study forms.
GET	/stats/by-provider	Top providers by application count.
GET	/stats/multi-municipality	Separate summary for rows spanning several municipalities.
GET	/stats/municipality-enrichment	MYH municipality summaries joined with SCB population and labour-market context.
GET	/export/applications	Filtered applications as CSV.
GET	/export/stats/by-year	Yearly stats as CSV.
POST	/predict	ML model: approval probability estimate for a new application.
POST	/refresh	Reload the database table from the curated CSV.

Open interactive Swagger UI

08 / Implementation

Technical details

Tools & stack

Tool	Layer	Role
Python 3.11	Runtime	Core language for the data pipeline and API
pandas	Data	Excel ingestion, harmonization, cleaning, and enrichment
openpyxl	Data	Engine for reading MYH .xlsx files
SCB PxWeb API	External data	Population and BAS labour-market enrichment snapshot
Jupyter	Notebook	Documents the raw-data journey and transformation decisions
PostgreSQL 16	Storage	Stores the curated applications table
psycopg2-binary	Database	PostgreSQL adapter — direct connection pooling and parameterized SQL queries
FastAPI	API	Read API with filters, statistics, CSV export, and Swagger docs
Pydantic	API	Response models for application rows and yearly stats
Uvicorn	API	ASGI server for running the FastAPI app
python-dotenv	Config	Loads local PG_* settings from .env
Docker	Local/Deploy	Containerized API image based on python:3.11-slim
Docker Compose	Local	Starts PostgreSQL and API together with seeded data
Neon.tech	Cloud DB	Hosted PostgreSQL for the deployed API
Render.com	Hosting	Hosts the deployed FastAPI service
Ruff	Quality	Python formatter and linter configured in the backend repo
Lovable	Frontend	Created and deploys this React dashboard project
React 19	Frontend	UI framework for the dashboard and explorer
TanStack Query	Frontend	Live API fetching, retry, and loading state
Recharts	Frontend	Gradient trend charts and comparison charts
Tailwind CSS v4	Frontend	Visual styling and responsive layout
Claude Code	Assistant	Code assistant and project assistant during development
Codex	Assistant	Reviewer of the project plus API/frontend polish and release prep

Harmonization decisions

beslut
Mapped to approved / rejected / withdrawn. Handles 'Beviljad', 'Avslag', 'Ej beviljad', 'Återkallad' and per-year variants.
Header rows
2018–2022 header on row 0; 2023–2024 on row 5; 2025 on row 6. Detected per-file before parsing.
Column names
Unified across all 8 files via a single COLUMN_MAP. Diacritics preserved (YH-poäng, Utbildningsområde, Sökta omgångar).
Landsting → Region
2018 file uses old administrative term. Corrected in harmonization so huvudmannatyp is consistent 2018–2025.
SUN5 / SeQF
Columns introduced in 2023. Kept as null for 2018-2022, not imputed or dropped.
typ_av_examen
Missing in 2018 and 2020, expected. Documented in the schema.
yh_poang
Coerced to int via pd.to_numeric(errors='coerce'). Non-numeric or empty → null.
is_distance
Derived: True when studieform contains 'Distans'. Added as a boolean column for easier filtering.

applications schema

27 columns (26 data + id). ? = nullable.

Column	Type	Notes
id	int	Surrogate primary key (SERIAL)
source_year	int	MYH application round year
source_file	text	Original .xlsx filename
source_sheet	text	Sheet within the workbook
diarienummer	text	MYH case number (not unique pre-2019)
utbildningsnamn	text	Program name
utbildningsomrade	text?	Education area
beslut	text?	Raw Swedish decision value
beslut_normalized	text?	approved / rejected / withdrawn
lan	text?	County (län)
kommun	text?	Municipality
yh_poang	int?	YH credits
studieform	text?	Form of study
studietakt_procent	int?	Pace of study (%)
typ_av_examen	text?	Exam type, null for 2018 and 2020
utbildningsanordnare	text?	Education provider
huvudmannatyp	text?	Provider type (Privat/Kommun/Region/Statlig)
flera_kommuner	text?	Multi-municipality raw value, null for 2018
antal_kommuner	int?	Municipality count, null for 2018
sokta_omgangar	int?	Rounds applied for, null for 2018-2019
beviljade_omgangar	int?	Rounds granted, null for 2018-2019
sun5_inriktning	text?	SUN5 subject code, 2023+ only
sun5_inriktning_namn	text?	SUN5 subject name, 2023+ only
seqf_niva	text?	SeQF qualification level, 2023+ only
smalt_yrkesomrade	text?	Narrow occupational area, 2023+ only
is_distance	bool?	Derived: studieform contains 'Distans'
has_multiple_municipalities	bool?	Derived: more than one kommun

09 / ML Model

Approval Predictor

Random Forest · 9 983 training records

Fill in the details of a YH application. The model estimates the probability it would be approved based on 8 years of MYH decisions (2018–2025).

Application details

Values must match the training data exactly.

Län (county)

Utbildningsområde

Studieform

Huvudmannatyp

Application year

YH-poäng (program size)

Studietakt (%)

Multiple municipalities

Result

Fill in the details and click Predict.

How it works: A Random Forest model trained on 9,983 MYH applications (2018–2025) predicts based on region, education area, study form, operator type, program size, and year.

Limitation: The model reflects historical patterns only. MYH's actual decisions also weigh program quality, employer demand, and regional need, none of which are in the dataset. Historical average approval rate: ~36%.

10 / Verification

Data quality report

Live checks against the applications table, checking row count, year coverage, null constraints, and value integrity. Run against the production database on every page load. The four notebooks also include their own end logs or output summaries for the recorded walkthrough.