collins-kimotho/Maji_Ndogo

A data analysis project using SQL to explore water access, quality, and infrastructure challenges in Maji Ndogo, generating insights to guide improvement efforts. # Maji Ndogo: From Analysis to Action ### Clustering Data to Unveil Maji Ndogo's Water Crisis ## Project Background and Overview Maji Ndogo, a region facing a severe water crisis, has compiled a comprehensive database from a nationwide water survey conducted by a dedicated team of engineers, field workers, scientists, and analysts. This database, `md_water_services`, contains over 60,000 records detailing water sources, visits, quality assessments, and pollution levels across provinces and towns. As a data analyst embedded within the Maji Ndogo water services team, my role was to explore this data to identify access inequalities, operational inefficiencies, and contamination risks. The primary goals of this analysis were to: * Assess the distribution and types of water sources to highlight disparities between rural and urban areas. * Analyze visit patterns and queue times to pinpoint bottlenecks in water access. * Evaluate water quality and pollution data to detect health risks. * Develop a prioritized repair and intervention plan to improve commercial and public health outcomes. This project synthesizes insights from sales trends (e.g., access volumes), product performance (e.g., source types), and regional comparisons to drive actionable improvements in water infrastructure. For technical details on data cleaning, queries, and methodologies, see the Jupyter Notebook or SQL scripts. ## Data Structure Overview The `md_water_services` database includes 8 interconnected tables with over 60,000 unique records and 43 columns. Key tables cover employee details, global water access benchmarks, locations, visits, water quality, sources, and well pollution. Below is an Entity Relationship Diagram (ERD)-style overview based on the data dictionary, showing table relationships and key columns. Tables join primarily via IDs like source_id, location_id, and assigned_employee_id, enabling complex queries for insights. This structure is broadly applicable to public heal …