Case Study: Water Supply Distribution Analysis

Water Supply Distribution Analysis for Rural Infrastructure Efficiency

This project simulates a real-world water distribution system serving over 1100 villages. It uses synthetic yet realistic data to identify leakage patterns, assess distribution performance, and provide insights through interactive dashboards. The solution combines Python and Power BI to bridge domain knowledge with data analytics.

Business Problem / Objective

Optimize water supply and reduce losses across 1120+ villages
Monitor daily consumption and identify high-leakage zones
Deliver actionable insights to planners and engineers
Simulate real infrastructure challenges using synthetic data

Project Scope

Villages covered: 1120
Zones: 113 (approx. 10 villages per zone)
Data generated: Daily supply and usage logs, leakage calculations
Target audience: Decision-makers, engineers, planners in public infrastructure

Tools & Technologies Used

Tool	Purpose
Python	Data generation, merging, cleaning
Pandas, NumPy	Data manipulation and random logic generation
Power BI	Dashboard creation and KPI visualization
Excel	Early-stage data previewing
GitHub	Version control and project hosting
VS Code	Code development environment

Data Sources

Village Master Data: Region, population, pipe length, OHT capacity
Daily Logs: Synthetic records of water supply and usage (30 days)
Data simulated using logical patterns and random seeds to mimic real conditions

Data Cleaning & Preparation

Generated village_master_1120.csv containing demographics and infrastructure metrics
Created daily_supply_logs.csv and daily_consumption_logs.csv
Derived columns: leakage (supply - usage), leakage percent
Merged all datasets in Python using merge() for Power BI modeling

Exploratory Data Analysis (Python)

Zone-wise population analysis
Pipe length vs. population correlation
Time-series of supply and usage
Detection of zones with >25% average leakage

Power BI Dashboard Design

Daily supply vs. usage trendline

Page 1: Executive Overview

Section	Visual Type	Description
Top KPIs	KPI Cards	Total Supply, Total Usage, Total Leakage, Avg Leakage %
Trend Analysis	Line Chart	Daily supply vs. usage over 30 days
Leakage Ranking	Bar Chart	Top 10 zones by leakage %
Filters	Slicers	Date, Region, Zone ID

DAX measures used:

Total_Leakage = SUM('merged_water_analysis'[Leakage_Liters])

Avg_Leakage_Percent = AVERAGE('merged_water_analysis'[Leakage_Percent])

📌 Page 2: Zone-Level Analysis

Leakage trend analysis by zone

Table & Card visuals: Daily zone metrics
Slicers: Region, Zone, Date
Color formatting for leakage thresholds

Key Insights & Outcomes

~12 zones consistently showed leakage above 25%
Northern region had higher OHT capacity per capita
Zone-level dashboards help prioritize pipeline maintenance
Leakage correlated with older pipelines and low-capacity tanks

Challenges & Solutions

Challenge	Solution
Creating large-scale synthetic data	Used NumPy randomization with domain constraints
Matching consumption/supply logs	Linked by date and village using keys
Dashboard performance	Filtered visuals and optimized Power BI model

Business Value Simulation

This case study demonstrates how structured data and BI tools can simulate and solve infrastructure problems. If implemented, such analytics could:

Cut water leakage by ~15% across target zones
Improve planning of pipeline upgrades
Enable real-time monitoring for civic bodies

Next Steps / Future Enhancements

Integrate anomaly detection using Python (e.g., spikes in leakage)
Add GIS-based mapping of supply pipelines and zones
Expand dashboard to allow predictive modeling (ARIMA, LSTM)
Published as an open-source framework for municipal water boards

Project Repository & Demo

GitHub: github.com/rkthedataanalyst/water-supply-analysis
Power BI Live Report: [Insert public dashboard link here]

Conclusion

By combining domain expertise in public infrastructure with analytical tools like Python and Power BI, this project shows the value of transforming raw data into operational decisions. It's an ideal example of how data analytics can create scalable impact in the real world.