Chennai Drinking-Water Reservoir Storage Analytics and Dry-Season Forecasting (Python)

NeerWatch Chennai is a data-science project that turns Chennai's publicly published drinking-water reservoir data into short-horizon storage forecasts and a drought early-warning dashboard. Chennai's city supply depends on a chain of reservoirs — chiefly Poondi, Cholavaram, Red Hills (Puzhal) and Chembarambakkam — whose storage the Chennai Metropolitan Water Supply and Sewerage Board (CMWSSB) publishes in million cubic feet (mcft) every day. In June 2019 all four principal reservoirs effectively ran dry, the episode popularly called Chennai's "Day Zero", and residents learnt about the crisis only when tanker queues appeared.

The project builds a reproducible pipeline: reservoir storage and rainfall CSVs are ingested into a MySQL database, cleaned and aggregated to daily series, and enriched with features such as storage lags, rolling rainfall totals, north-east-monsoon flags and an estimated daily supply draw. Models — a seasonal-naive baseline, linear regression, random forest, gradient boosting and a SARIMA reference — are compared with time-series cross-validation to forecast storage one, two and four weeks ahead for each reservoir and for the system total.

A Streamlit dashboard shows each reservoir's history, the forecast band, the percentage of full capacity and a risk classification (Normal, Watch, Alert, Critical). The work applies Python Programming (MC4103), Data Mining (MC4020), Data Visualization (MC4019) and Advanced Database Technology (MC4202) from the Anna University MCA R2021 curriculum.

Chennai's drinking-water security depends on a handful of reservoirs whose storage is published daily, yet nobody outside the water board turns that feed into a forward-looking picture. The published tables live in different places and formats, contain gaps, and are rarely joined with rainfall, so a consistent analytical history has to be assembled by hand every time someone wants to study it. The public — residents, schools, hospitals and ward offices — sees only today's number, not whether storage is falling faster than usual or when a reservoir is likely to approach empty. In 2019 the decline to near-empty storage was reported as news rather than predicted as a trend, and tanker demand then exploded. There is a need for a reproducible, offline-runnable analytics system that maintains a clean daily history of reservoir storage and rainfall, forecasts storage a week to a month ahead with honestly validated models, and translates the result into risk categories and weeks-of-water-remaining estimates that a non-specialist can act on.

Abstract

Chennai stores its drinking water in a chain of reservoirs whose combined capacity is about 11,057 mcft for the four principal lakes, supplemented by Veeranam and the newer Kannankottai Thervoy Kandigai reservoir. Storage rises during the October–December north-east monsoon and falls steadily through the dry months, and in 2019 the city came close to a full supply failure. This project builds an end-to-end analytics system that ingests daily reservoir storage and rainfall data published by CMWSSB, models the storage dynamics, forecasts one to four weeks ahead and converts the forecast into a plain-language risk category and a "weeks of water remaining" estimate.

Introduction

A metropolitan water board makes operational decisions — canal releases, tanker deployment, supply-hour reductions — weeks before the public feels them. Those decisions depend on how fast storage is falling, which depends on rainfall, evaporation and the daily draw. All of these are visible in the daily published data, but the data is spread across web tables and CSV files in different formats, so a consistent, machine-readable history must first be assembled. The north-east monsoon gives Chennai a strongly seasonal storage cycle, which makes it a good candidate for time-series modelling with lagged features.

Existing System

• CMWSSB publishes a daily lake-level table on its website (level, storage, inflow, outflow, rainfall, and the same day last year); historical compilations exist on OpenCity (monthly, since 2003) and Kaggle (daily, 2004–2019).
• The formats differ between sources, values are occasionally missing, and nothing joins the reservoir series with rainfall.
• News coverage reports crisis only when it is already visible: no published weekly forecast or risk category exists for a lay reader or a ward official.

Proposed System

• A validated ETL that loads daily storage and rainfall for each reservoir into MySQL, with unit checks (mcft), duplicate removal and gap rules.
• Feature engineering: storage lags (1, 7, 14, 30 days), 7- and 30-day rolling rainfall, month and week-of-year, NE-monsoon flag, and an estimated net draw computed from day-over-day storage change on rain-free days.
• Models compared with TimeSeriesSplit cross-validation: seasonal-naive (storage of the same week last year), linear regression, random forest, gradient boosting and SARIMA.
• Forecasts mapped to risk bands by percentage of full capacity — Normal above 50%, Watch 25–50%, Alert 10–25%, Critical below 10% — plus a draw-rate-based weeks-of-water-remaining estimate.
• A Streamlit dashboard with history, forecast, band, per-reservoir and system views, and a downloadable CSV report.

Feasibility Study

• Technical: every library is open-source; the daily datasets are small (a few thousand rows per reservoir) and train in seconds on a laptop. MySQL, covered in Advanced Database Technology (MC4202), stores the series; Streamlit covers the visualization requirement of MC4019.
• Economic: zero licence cost; the data is public.
• Operational: the dashboard runs offline on a campus laptop; a one-command refresh re-runs ingestion, training and forecasting when new CMWSSB numbers are pasted in.

In scope

• Four principal reservoirs (Poondi, Cholavaram, Red Hills, Chembarambakkam); Veeranam and Kannankottai Thervoy Kandigai added optionally where the student has data.
• Daily storage (mcft) and rainfall (mm) from public sources: the Kaggle daily compilation (2004–2019), OpenCity monthly levels (since 2003) and CMWSSB's current daily table pasted into a watched file.
• Batch pipeline, weekly-to-daily refresh by re-running the ingest command; horizons of 7, 14 and 28 days.
• Risk bands, weeks-of-water-remaining estimate, Streamlit dashboard, CSV export, pytest test suite.

Out of scope

• Real-time scraping of the CMWSSB site (the daily table is pasted into a file by hand; automated scraping is a future enhancement).
• Hydrological simulation (inflow–outflow mass balance, evaporation pan models) and canal or Krishna-water supply modelling.
• Groundwater and city demand modelling; consumption metering.
• Operational use by the water board — this is an academic early-warning prototype.

The project follows the KDD process (selection, pre-processing, transformation, data mining, interpretation/evaluation) taught in Data Mining (MC4020), run in iterations over 14 weeks. A team of three splits the work: Member A owns ingestion and cleaning, Member B owns features and models, Member C owns the alert logic, dashboard and tests; every phase is reviewed by the guide.

Validation protocol: TimeSeriesSplit(n_splits=5) inside 2014–2017 for tuning; 2018–2019 held out untouched, which deliberately includes the 2019 dry-season decline. Metrics: MAE and RMSE (mcft) per horizon, percentage of days whose predicted risk band matches the observed band, and a retrospective check — how many weeks before June 2019 each model first flagged Critical. No result is claimed before it is produced by the team's own run.

NeerWatch Chennai is a batch pipeline with a presentation layer, built so each stage can be re-run independently and writes its output to MySQL.

flowchart TD
  A["Kaggle daily CSVs (2004-2019)"] --> I["Ingest and validate"]
  B["OpenCity monthly CSV"] --> I
  C["CMWSSB daily table (pasted file)"] --> I
  I --> Q["Quarantine bad rows"]
  I --> M["Clean: units, duplicates, gaps"]
  M --> DB[("MySQL: daily storage and rainfall")]
  DB --> F["Feature engineering"]
  F --> T["Train models with TimeSeriesSplit"]
  T --> R["Model registry (joblib + metrics)"]
  R --> P["Forecast job: 7 / 14 / 28 days"]
  P --> AL["Risk bands + weeks-of-water"]
  AL --> DB
  DB --> S["Streamlit dashboard"]

Data model

erDiagram
  RESERVOIR ||--o{ STORAGE_DAILY : "has"
  RESERVOIR ||--o{ RAINFALL_DAILY : "receives"
  RESERVOIR ||--o{ FORECAST : "is forecast"
  MODEL_RUN ||--o{ FORECAST : "produces"
  RESERVOIR {
    int id PK
    string name
    float fullCapacityMcft
    float fullTankLevelFt
  }
  STORAGE_DAILY {
    int id PK
    int reservoirId FK
    date day
    float storageMcft
    float pctCapacity
    int sourceId FK
  }
  RAINFALL_DAILY {
    int id PK
    int reservoirId FK
    date day
    float rainMm
  }
  MODEL_RUN {
    int id PK
    string algorithm
    datetime trainedAt
    float cvMae
    float cvRmse
  }
  FORECAST {
    int id PK
    int reservoirId FK
    int modelRunId FK
    date targetDay
    float storagePredMcft
    string riskBand
    float weeksRemaining
  }
  SOURCE_FILE {
    int id PK
    string filename
    datetime ingestedAt
    string contentHash
  }

A QUARANTINE table records every rejected row (file, line number, reason) so cleaning decisions are auditable. Risk bands compare storage with each reservoir's full capacity: Normal above 50%, Watch 25–50%, Alert 10–25%, Critical below 10%. The weeks-of-water-remaining estimate divides current storage by the median net daily draw observed in dry-season months (storage drop on rain-free days), and is displayed with its assumption, not as an exact date.

• Scheduled scraping of the CMWSSB lake-level page with a change detector, replacing the pasted-file workflow.
• Inflow modelling using catchment rainfall and a simple mass-balance term to separate supply draw from evaporation.
• Scenario planning: slider-driven what-if analysis (rainfall percentile, draw growth) for ward-level planning discussions.
• Sequence models (LSTM) as a comparison, only if the tree-model error analysis motivates it.
• Alert delivery by SMS or e-mail to registered schools and hospitals when Alert or Critical is forecast.
• Generalisation to other Tamil Nadu reservoir systems (Cauvery basin) using the same pipeline configuration.