Primary School Compass 🧒📚

A modern web application for comparing primary school (KS2) performance data in Wandsworth and Merton over the last 5 years. Built with FastAPI and vanilla JavaScript with Chart.js visualizations.

Features

• 📊 Interactive Charts - Visualize KS2 performance trends over time
• 🔍 Smart Search - Find primary schools by name in Wandsworth & Merton
• ⚖️ Side-by-Side Comparison - Compare up to 5 schools simultaneously
• 🏆 Rankings - View top-performing primary schools by various KS2 metrics
• 📱 Responsive Design - Works beautifully on desktop and mobile

Key Metrics (KS2)

The application tracks these Key Stage 2 performance indicators:

Metric	Description
Reading Progress	Progress in reading from KS1 to KS2
Writing Progress	Progress in writing from KS1 to KS2
Maths Progress	Progress in maths from KS1 to KS2
Reading Expected %	Percentage meeting expected standard in reading
Writing Expected %	Percentage meeting expected standard in writing
Maths Expected %	Percentage meeting expected standard in maths
Reading, Writing & Maths Combined %	Percentage meeting expected standard in all three subjects

Quick Start

1. Clone and Setup

cd school_results

# Create virtual environment
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

2. Run the Application

# Start the server
python -m uvicorn backend.app:app --reload --port 8000

Then open http://localhost:8000 in your browser.

The app will run with sample data by default, showing 110 primary schools (66 in Wandsworth, 44 in Merton) with 5 years of KS2 performance data.

3. (Optional) Use Real Data

To use real UK school performance data:

1. Visit Compare School Performance - Download Data

2. Download Key Stage 2 data for the years you want (2019-2024)

   • Select "Key Stage 2" as the data type

3. Place the CSV files in the data/ folder

4. Restart the server - it will automatically load and filter to Wandsworth & Merton schools

Note: The app only displays schools in Wandsworth and Merton. Data from other areas will be filtered out.

See the helper script for more details:

python scripts/download_data.py

Project Structure

school_results/
├── backend/
│   └── app.py           # FastAPI application with all API endpoints
├── frontend/
│   ├── index.html       # Main HTML page
│   ├── styles.css       # Styling (warm, editorial design)
│   └── app.js           # Frontend JavaScript
├── data/
│   └── .gitkeep         # Place CSV data files here
├── scripts/
│   └── download_data.py # Helper for downloading/processing data
├── requirements.txt     # Python dependencies
└── README.md

API Endpoints

Endpoint	Description
GET /api/schools	List schools with optional search/filter
GET /api/schools/{urn}	Get detailed data for a specific school
GET /api/compare?urns=...	Compare multiple schools
GET /api/rankings	Get school rankings by metric
GET /api/filters	Get available filter options
GET /api/metrics	Get available performance metrics

Example API Usage

# Search for schools
curl "http://localhost:8000/api/schools?search=academy"

# Get school details
curl "http://localhost:8000/api/schools/100001"

# Compare schools
curl "http://localhost:8000/api/compare?urns=100001,100002,100003"

# Get rankings
curl "http://localhost:8000/api/rankings?metric=rwm_expected_pct&year=2024"

Data Format

If using your own CSV data, ensure it includes these columns (or similar):

Column	Type	Description
URN	Integer	Unique Reference Number
SCHNAME	String	School name
LA	String	Local Authority (must be Wandsworth or Merton)
READPROG	Float	Reading progress score
WRITPROG	Float	Writing progress score
MATPROG	Float	Maths progress score
PTRWM_EXP	Float	% meeting expected standard in reading, writing & maths
PTREAD_EXP	Float	% meeting expected standard in reading
PTWRIT_EXP	Float	% meeting expected standard in writing
PTMAT_EXP	Float	% meeting expected standard in maths

The application normalizes column names automatically and filters to only show Wandsworth and Merton schools.

Technology Stack

• Backend: FastAPI (Python) - High-performance async API framework
• Frontend: Vanilla JavaScript with Chart.js
• Styling: Custom CSS with CSS variables for theming
• Data: Pandas for CSV processing

Design Philosophy

The UI features a warm, editorial design inspired by quality publications:

• Typography: DM Sans for body text, Playfair Display for headings
• Color Palette: Warm cream background with coral and teal accents
• Interactions: Smooth animations and hover effects
• Charts: Clean, readable data visualizations

Development

# Run with auto-reload
python -m uvicorn backend.app:app --reload --port 8000

# Or run directly
python backend/app.py

Coverage

This application is specifically designed for:

• School Phase: Primary schools only (Key Stage 2)
• Geographic Area: Wandsworth and Merton (London boroughs)
• Time Period: Last 5 years of data (2020-2024)

Note: 2021 data shows as unavailable because SATs were cancelled due to COVID-19.

Data Source

Data is sourced from the UK Government's Compare School Performance service, which provides official school performance data for England.

Important: When using real data, please comply with the terms of use and data protection regulations.

Scheduled Jobs

Geocoding Schools (Cron Job)

School postcodes are geocoded by a scheduled job, not on-demand. This improves performance and reduces API calls.

Setup the cron job (runs weekly on Sunday at 2am):

# Edit crontab
crontab -e

# Add this line (adjust paths as needed):
0 2 * * 0 cd /path/to/school_compare && /path/to/venv/bin/python scripts/geocode_schools.py >> /var/log/geocode_schools.log 2>&1

Manual run:

# Geocode only schools missing coordinates
python scripts/geocode_schools.py

# Force re-geocode all schools
python scripts/geocode_schools.py --force

License

MIT License - feel free to use this project for educational purposes.

---

Built with ❤️ for Wandsworth & Merton families