ncedox/maji_ndogo_water_project_part-2

# Maji Ndogo Water Project – Part 2 ### Data Integrity, Pattern Discovery, and Analysis. This project focuses on cleaning and analyzing employee and water source data for Maji Ndogo. The goal is to understand water access, identify patterns, and prioritize interventions ## Project Activities ### 1. Cleaning and Standardizing Employee Data Formatted employee emails by replacing spaces with dots (First Last → first.last@ndogowater.gov). Converted all emails to lowercase to avoid case issues. Updated phone numbers to include country code (+) for numbers with 11 characters ### 2. Employee Performance Analysis Counted the number of location visits per employee. Identified the top 3 field surveyors by visit count for recognition ### 3. Water Source Distribution Analysis Counted water source records by town and province. Calculated the percentage of water sources in rural areas. Summed the total number of people served by all water sources. Counted number of wells, taps, and rivers. ### 4. Water Source Usage Analysis Calculated the average number of people per source type. Calculated the total number of people using each source type. Calculated the percentage of the population using each source type. ### 5. Prioritizing Water Source Repairs Ranked water sources by the number of people served to prioritize repairs. Used RANK() to assign priority levels within each type of source (shared taps, wells, rivers). ### 6. Survey Duration Identified the first and last visit dates to determine how long the field survey took. ### 7. Queue Time Analysis Calculated average queue time across all locations with waiting lines. Analyzed average queue time by day of the week. Analyzed average queue time by hour and day of the week to identify peak demand times. ## Findings ### Employee Visits Top 3 surveyors handled the most visits: Employee 1 – 3,708 visits Employee 30 – 3,676 visits Employee 34 – 3,539 visits ### Water Source Records Most water sources by town: Rural (23,740), Harare (1, …