DennisMochoge/Kenya-Public-School-Analysis-MySql
Domain:
educationRecord type:
project
Creator:
Den
Host:
This project began as a personal initiative to help my daughter with her Grade 10 Senior School selection in Kenya. The official Ministry of Education guidelines require learners to select schools across clusters (C1–C4), genders, and regional distributions — a process that is complex and traditionally paper-based.
# Kenya's Public School Analysis – Data Cleaning & Interactive Dashboard
This project began as a personal initiative to help my daughter with her Grade 10 Senior School selection in Kenya. The official Ministry of Education guidelines require learners to select schools across clusters (C1–C4), genders, and regional distributions — a process that is complex and traditionally paper-based. The workflow spans data cleaning with MySQL, crafting calculated DAX measures, and designing a rich, interactive dashboard with advanced drill-down capabilities.
---
## 📌 Objective
To clean, structure, and visualize school-level education data across Kenya to support decision-making by analyzing:
- Gender distribution (Boys, Girls, Mixed)
- Regional disparities (Region → County → Sub-county)
- School cluster segmentation (C1, C2, C3, C4)
- Overall school availability by location and type
---
## 🧰 Tools & Technologies
| Tool | Purpose |
|--------------|----------------------------------------|
| MySQL | Data cleaning and transformation |
| MySQL Workbench | Writing and testing SQL scripts |
| Power BI | Data modeling and dashboarding |
| Power Query (M) | Import transformations & shaping |
| DAX | Calculated fields and custom measures |
---
## 🗂️ Repository Structure
```
Kenya-Public-School-Analysis/
│
├── Combined_School_Data.sql # All data transformation queries
│
├── Kenya_School_Analysis.pbix
│
├── Dashboard.png # Dashboard preview image
│
└── README.md
```
---
## 🛠️ Process Overview
### 1. 🔄 Data Cleaning in MySQL
- **Trim & Standardize Sub-county Names**
```sql
UPDATE school_data s
JOIN sub_county_mapping m
ON TRIM(UPPER(s.`SUB COUNTY`)) = TRIM(UPPER(m.error_sub_county))
SET s.`SUB COUNTY` = m.correct_sub_county;
```
- **Fix Gender Values**
Ensured that gender data matched one of: `'BOYS'`, `'GIRLS'`, `'MIXED'`.
- **Create Cluster Grouping**
Used mappings or class …