shalotech/Maji_Ndogo_Water_Crisis_Investigation

# Maji Ndogo Water Crisis Investigation - Analysis Report **Project:** Maji Ndogo Water Services - Water Crisis Intervention **Analyst:** Shalon Ngobeni **Database:** md_water_services **Analysis Period:** 2021-2023 --- ## Executive Summary This comprehensive analysis investigates water infrastructure and data integrity across Maji Ndogo, examining 39,650 records across multiple provinces. The investigation uncovered critical issues with water access, identified data quality problems, and revealed potential corruption among field employees. Key findings include severe queue times at shared taps (average 137 minutes), significant rural water access challenges (60% of all sources), and systematic data tampering by certain field employees. --- ## 1. Project Objectives ### Primary Goals - **Data Integrity Assessment**: Evaluate accuracy and reliability of water source survey data - **Infrastructure Analysis**: Identify water access patterns and infrastructure gaps - **Quality Assurance**: Cross-validate field surveyor data against independent auditor reports - **Corruption Investigation**: Identify potential data manipulation by field employees --- ## 2. Database Structure & Methodology ### Core Tables - **employee** (56 employees): Field surveyor information - **location** (39,650 records): Geographic distribution across 5 provinces, 25 towns - **water_source** (39,650 sources): Five source types tracked - **visits** (60,097 records): Survey visit logs with queue times - **water_quality** (39,650 records): Quality assessments - **well_pollution** (17,383 records): Contamination test results - **auditor_report** (1,620 records): Independent quality verification ### Data Cleaning Steps 1. Generated employee email addresses: `firstname.lastname@ndogowater.gov` 2. Trimmed phone numbers (resolved 13→12 character issue) 3. Corrected well pollution descriptions (removed erroneous "Clean" prefixes) 4. Fixed biological contamination classifications (>0.01 CFU/mL thre …