tayooyebamiji0137-cloud/Water-Accessibility-and-Infrastructure-Maji-Ndogo-

Investigate access to water sources, queue times, contamination risks and where infrastructure improvements would benefit the most people # Water-Accessibility-and-Infrastructure-Maji-Ndogo- ## Table of Content * Project Overveiw * Data Source * Tools * Data Overview * Data Cleaning * Exploratory Data Analysis * Data Analysis * Insight * Recommendation ### Project Overveiw Investigate access to water sources, queue times, contamination risks and where infrastructure improvements would benefit the most people ### Data Source md_water_services data. The primary dataset use for this analysis is the md_water_services.sql file, containing detailed information about water service in maji-ndogo. > Download here ### Tools * SQL Server - Data analysis * Excel - Pivot table and visualization ### Data Overview Data dictionary (docs/data_dictionary.md) --- Dictionary listing the main tables and important fields, e.g.: * location — location_id, province_name, town_name, location_type (rural/urban/tap/well/shared) * water_source — source_id, type_of_water_source, number_of_people_served, town_name, location_id * visits — record_id, time_of_record, time_in_queue, assigned_employee_id, source_id * well_pollution — record_id, biological, chemical, results, description * water_quality — source_id, subjective_quality_score, visit_count * employee — assigned_employee_id, employee_name, phone_number, email, town_name ### Data Cleaning In the initial data preparation phase, i performed the following tasks; 1. Data loading and inspection. 2. Data cleaning and formatting. 3. Fix well_pollution description: replace Clean Bacteria: E. coli with Bacteria: E. coli and similar for Giardia Lamblia. 4. Update results from Clean to Contaminated: Biological for rows where biological > 0.01. 5. Trim whitespace and standardize phone_number values (SQL TRIM() and formatting regexes). 6. Export a well_pollution_copy as an immutable snapshot before making destructive updates. ### Exploratory Data Analysis * I check for populaton survey * How I rank sources: aggregate sum(number_of_people_served) by type_of_water_source a …