LMjuju10/Restoring-water-access-Part-1
Domain:
environment and energyRecord type:
dataset
Creator:
LMj
Host:
Uncovering the patterns and nuances in the Maji Ndogo database.
## Maji Ndogo Project – Part 1: Data Exploration
### Table of Contents
- Project Overview
- Data Understanding
- Exploratory Data Analysis (EDA)
- Data Cleaning Steps
- Insights and Recommendations
---
### Project Overview
Part 1 of the **Maji Ndogo** project focuses on exploring and interpreting a large dataset to uncover valuable insights. This process unfolds across five key stages:
1. **Get to know our data**
Load and preview tables to understand the dataset structure.
2. **Dive into the water sources**
Explore the `water_source` table to identify unique source types.
3. **Unpack the visits to water sources**
Analyze patterns of high-visit locations and usage trends.
4. **Assess the quality of water sources**
Use the `water_quality` table to filter for frequently visited sources with high quality scores.
5. **Investigate any pollution issues**
Flag water sources marked as "dirty" or "biologically contaminated" to find areas needing urgent action.
---
### Data Understanding
**Data Source**: `md_water_services.sql`
#### Database Schema / Table Relationships
- The `visits` table is linked to the `employee` table via the `employee_id` column.
- The `well_pollution` and `water_source` tables relate to the `visits` table through the `source_id` column.
- The `visits` table is connected to the `location` table through the `location_id` column.
---
### Exploratory Data Analysis (EDA)
Key questions guiding the analysis:
1. Which tables and columns hold the most relevant information for trends in water access and quality?
2. How does water quality vary by source type or location?
3. Which sources experience the longest queues?
4. What is the distribution of visits per water source?
5. Do frequently visited sources have better, worse, or similar quality and pollution scores?
---
### Data Cleaning Steps
#### 1. Get to Know Our Data
```sql
-- Number of tables in the database
SELECT DISTINCT table_name
FROM md_water_services.data_dictionary;
-- List all …