Groundwater Level Trend Analysis and Depletion Assessment for Tamil Nadu Districts (CGWB Open Data, Python)

Groundwater Level Trend Analysis and Depletion Assessment for Tamil Nadu Districts is an M.Sc data-science project that turns the Central Ground Water Board's open well-monitoring records into an early-warning view of aquifer stress.

CGWB and state groundwater agencies measure water levels in thousands of observation wells across India every quarter — January, May, August and November — and publish the records as depth in metres below ground level (mbgl) through the India-WRIS portal. This project ingests those records for Tamil Nadu, cleans and standardises them, and then answers three questions with proper statistics: which wells show a statistically significant long-term decline, how fast is the water table falling, and what does the near future look like?

The analysis engine applies the non-parametric Mann-Kendall test with Sen's slope estimator — the standard tools for skewed environmental series — separately for pre-monsoon and post-monsoon levels, so that seasonal rainfall recharge is not misread as depletion. A forecasting module fits Holt-Winters and SARIMA models to stations with long records and validates them on held-out seasons. A Streamlit dashboard maps every station's trend category (rising, stable, falling), supports drill-down and exports the tables a dissertation chapter needs.

Everything runs in Python on a laptop: pandas for wrangling, SQLite as the analysis store, pymannkendall and statsmodels for statistics, Streamlit for the interface. The data is free, the methods are examinable, and the question matters to every panchayat in the state.

District administrations and panchayats in Tamil Nadu make borewell, tank and cropping decisions without a current, trustworthy picture of groundwater behaviour. CGWB's four-yearly resource assessment gives district averages, but the underlying observation-well measurements — which show where levels are actually falling, how fast, and since when — remain locked in quarterly downloads that almost nobody processes.

Analyses that do exist often compare unlike with unlike: pre-monsoon depths against post-monsoon depths, or raw depths whose seasonal recharge signal swamps the long-term movement. There is a need for a reproducible, statistically sound pipeline that ingests the official open data, tests every station's series for significant trend with methods appropriate for environmental data, separates seasonal behaviour from long-term change, validates any forecast on data the model has not seen, and presents results so that a district officer — not just a statistician — can act on them.

Abstract

Tamil Nadu depends on groundwater for more than half of its water use, yet district planning still quotes the CGWB's four-yearly resource assessment instead of watching the observation-well record continuously. This project builds a reproducible analysis pipeline over the CGWB water-level records published through India-WRIS, quantifies long-term trends for every monitoring well in the state, and presents the results as a dashboard that a non-specialist can read and act on.

Introduction

India's groundwater agencies measure thousands of observation wells four times a year. The measurements are public, but they arrive as downloads that must be joined, unit-checked and de-duplicated before any statistics make sense; few student projects — and few district offices — ever do that work. The result is that a panchayat asking "is our aquifer falling, and how fast?" has no easy, current answer.

Existing system and gap

Published groundwater studies for India typically (a) analyse one district for one season, (b) report a correlation without testing its significance, or (c) mix pre-monsoon and post-monsoon readings in one series, contaminating the long-term trend with seasonal recharge. CGWB's Dynamic Ground Water Resources assessment is authoritative but coarse — district-level, four-yearly, and modelled rather than measured. The gap this project fills: a state-wide, per-station, season-aware trend analysis with documented data cleaning, non-parametric significance testing and an honest holdout evaluation of the forecasts, delivered as reusable code instead of a single static report.

Proposed system

• Data: quarterly water-level records (mbgl) for Tamil Nadu stations downloaded from India-WRIS, with station metadata (district, block, well type, coordinates).
• Cleaning: strict QC — station-code harmonisation, duplicate removal, unit and outlier checks (a depth of 0.2 m or 400 m is a recording error, not an aquifer), and documented gaps.
• Trend analysis: Mann-Kendall tests and Sen's slopes per station and season; district roll-ups ranking blocks by median slope.
• Forecasting: Holt-Winters and SARIMA fits for stations with long records, validated on the last four held-out seasons with MAE in metres against a naive baseline.
• Interface: a Streamlit dashboard with a station map, trend categories, forecast plots and CSV export.

Feasibility study

• Technical: every component is open source and runs on 8 GB of RAM; a state's quarterly records are a few hundred thousand rows, comfortably within SQLite's range.
• Economic: zero licence cost; the data is published by a government agency for public use with attribution.
• Operational: the dashboard needs no server — it runs locally, and the same code can be pointed at any other state, which makes the work genuinely reusable beyond this report.

In scope

• One state (Tamil Nadu); CGWB and state observation wells; quarterly measurements published through India-WRIS.
• Depth-to-water (mbgl) as the single analysed variable; pre-monsoon and post-monsoon series kept separate throughout.
• Trend testing (Mann-Kendall family, Sen's slope), district roll-ups, short-horizon forecasting for long-record stations, and a local dashboard.
• Deliverables: ingestion/QC code, SQLite analysis store, dashboard, dissertation, viva deck.

Out of scope

• Rainfall-recharge modelling or coupling with CGWB's extraction estimates — noted as future work.
• Water quality analysis (the same portal hosts it; it would double the cleaning effort).
• Real-time telemetry ingestion; the project works from periodic manual downloads.
• Automated alert delivery (SMS/e-mail); alerts appear in the dashboard only.
• Other states — the code is state-agnostic by design, but the report analyses Tamil Nadu only.

Research design: quantitative, secondary-data study with an engineering deliverable (pipeline plus dashboard). All statistics are computed on public aggregate environmental data, so no ethics clearance is required; sources are used with attribution as published.

Tools and analysis plan: Python 3 with pandas/NumPy for wrangling, pymannkendall for trend tests, statsmodels for forecasting, SQLite as the store, pytest for the statistics. Every transformation is scripted — no manual spreadsheet edits — so the chain reruns from the raw zone in one command. The significance level (α = 0.05) and the slope-category boundaries (m/year) are fixed in the proposal before any results are computed.

The system is a three-layer pipeline — acquire, analyse, present — with SQLite as the single source of truth between them.

flowchart TD
  A["India-WRIS / CGWB well-level exports (CSV)"] --> B["Raw zone: immutable archived downloads"]
  B --> C["QC: station codes, units (mbgl), duplicates, impossible depths, gaps"]
  C --> D[("SQLite analysis store")]
  D --> E["Trend engine: Mann-Kendall + Sen's slope per season"]
  D --> F["Forecasting: Holt-Winters / SARIMA per long-record station"]
  D --> G["District roll-up: median slopes, category counts"]
  E --> H["Trend results: z, p, slope, category"]
  F --> I["Holdout validation: MAE vs naive baseline"]
  H --> J["Streamlit dashboard: map, drill-down, forecasts"]
  I --> J
  G --> J
  J --> K["CSV exports for the dissertation"]

erDiagram
  DISTRICT ||--o{ STATION : contains
  STATION ||--o{ READING : "is measured in"
  STATION ||--o| TREND_RESULT : "has one per season"
  STATION ||--o{ FORECAST : "has"
  TREND_RESULT ||--o{ ALERT : "triggers"

  DISTRICT {
    string district_id PK
    string name
  }
  STATION {
    string station_id PK
    string district_id FK
    string well_type
    float latitude
    float longitude
  }
  READING {
    string reading_id PK
    string station_id FK
    string season
    date measured_on
    real depth_mbgl
  }
  TREND_RESULT {
    string result_id PK
    string station_id FK
    string season
    real sens_slope_m_per_year
    real z_statistic
    real p_value
    string trend_label
  }
  FORECAST {
    string forecast_id PK
    string station_id FK
    string horizon
    real predicted_mbgl
    real mae_holdout
  }
  ALERT {
    string alert_id PK
    string station_id FK
    string rule
    string severity
  }

Three design decisions are visible. First, the raw zone is immutable — every portal download is archived unmodified so a changed export format can never silently rewrite history. Second, season is part of the schema, not a filter bolted on later: readings carry a season attribute and trends are computed per season, which is what stops recharge from masquerading as depletion. Third, alert rules (for example, significant falling trend plus forecast exceeding the well's historical maximum depth) live in the database as rules, so the dashboard only renders what the statistics support.

• Add gridded rainfall as a covariate so falling trends can be discussed against recharge, not just time.
• Ingest CGWB's Dynamic Ground Water Resources stage-of-extraction tables and test whether measured well trends agree with the four-yearly assessments.
• Extend the same pipeline to water-quality parameters hosted on the same portal.
• Convert the dashboard's alert rules into a scheduled e-mail digest for a district office as a deployment exercise.
• Port the trend engine to a state-agnostic package and benchmark runtime on all-India data.