DimphoData/Maji-Ndogo-Water-Analysis-Pt.2-Clustering-Data-To-Unveil-Maji-Ndogo-Water-Crisis

National water infrastructure analysis using SQL and Python to optimize resource allocation. # Maji-Ndogo-Water-Analysis-Pt.2-Clustering-Data-To-Unveil-Maji-Ndogo-Water-Crisis National water infrastructure analysis using SQL and Python to optimize resource allocation. # Maji Ndogo Water Crisis: Data Clustering & Strategic Action (Part 2) ## 📖 Overview This project focuses on identifying geographical trends, infrastructure bottlenecks, and daily queue patterns for the Maji Ndogo water crisis. By transitioning from raw data cleaning to clustering, I developed a data-driven "Action Plan" to improve water access for over 60,000 citizens. ## 🚀 Key Insights * **The Saturday Crisis:** Using SQL pivot tables, I identified that Saturday wait times peak at **double** the weekly average, specifically between 6:00 AM and 10:00 AM. * **Infrastructure Bottlenecks:** Analysis revealed that **60% of water sources are rural**, and shared taps serve an average of **2,000 people per source**. * **Data Standardization:** Corrected 100% of employee contact records and generated professional internal emails using SQL string functions. ## 🛠️ Tech Stack - **SQL (MySQL):** Aggregations, CASE Statements (Pivoting), CTEs, and String Manipulation. - **Python:** SQLAlchemy for database connection, Pandas for data handling. - **Data Visualization:** Matplotlib for trend analysis. ## 📋 Final Action Plan 1. **Logistics:** Deploy water tankers during Saturday morning peaks. 2. **Quality Control:** Install UV filters for biological issues and RO filters for chemical pollution. 3. **Infrastructure:** Prioritize repairs for taps serving the largest population clusters. 4. **Target:** Reduce all queue times to the UN standard of <30 minutes.