River Water Quality Classification & Screening Tool on CPCB Monitoring Data (Python + MySQL)

River Water Quality Classification & Screening Tool is a scoped mini-project in applied machine learning: it takes real water-quality records from the Central Pollution Control Board's National Water Quality Monitoring Programme (NWMP) — hundreds of river, lake and well stations across India measured on parameters such as pH, dissolved oxygen (DO), biochemical oxygen demand (BOD), conductivity and coliform counts — and turns them into two useful things.

First, a compliance audit. Each station-year is checked against CPCB's published water-quality criteria for outdoor bathing (pH 6.5–8.5, DO ≥ 5 mg/L, BOD ≤ 3 mg/L and coliform limits), producing a ranked list of the parameters and stations that fail most often.

Second, a classifier that screens for organic pollution. BOD — the single best indicator of sewage load — needs a five-day laboratory incubation and is the parameter most often missing from a monitoring record. The project trains a logistic-regression baseline and an explainable decision-tree classifier in scikit-learn to predict whether a sample likely exceeds the BOD limit from the parameters that are quickly available (DO, pH, temperature, conductivity), evaluated with stratified k-fold cross-validation, confusion matrices and precision/recall for the exceedance class.

A small Django web app over MySQL stores stations, samples and predictions, accepts a CSV of new readings or a single manual entry, and shows the screened band with the parameters most likely to fail. The scope is deliberately modest: one dataset, two models, one app — completed and defended well beats an ambitious half-built system.

CPCB's NWMP network publishes thousands of river-water measurements every year, but three problems keep the data from being used at the ground level. First, the records are spread across portal pages and reports in formats that need cleaning before any analysis. Second, BOD — the parameter that best indicates sewage contamination — requires a five-day lab test, and it is precisely the one most often missing, so many samples cannot be judged for organic pollution at all. Third, officials and citizens have no simple way to see which stations repeatedly fail the published bathing criteria and why.

There is a need for a beginner-built but methodologically sound tool that consolidates NWMP data into one clean table, audits every sample against CPCB's criteria, and uses a transparent scikit-learn classifier to screen samples with missing BOD from the parameters that are measured on the spot — flagging likely exceedances for follow-up testing instead of leaving them blank.

Abstract

India's rivers are monitored by a large public network, yet the monitoring data rarely reaches a student, NGO or panchayat in a usable form, and one key number — BOD, the main signal of organic (sewage) pollution — is slow and costly to measure, so records frequently ship with it blank. This mini-project builds a pandas pipeline over CPCB's NWMP station data, audits compliance against CPCB's bathing-water criteria, and trains a screening classifier that flags likely BOD exceedance from instant-read parameters, wrapped in a small Django/MySQL web tool.

Introduction

The National Water Quality Monitoring Programme runs a network of hundreds of stations on rivers, lakes, canals and wells, sampled monthly or quarterly; the certified datasets are published through the National Water Data Portal and CPCB's NWMP pages. For a beginner data-science student this is an ideal first real dataset: tabular, small enough for a laptop, publicly sourced, and connected to a topic every examiner in Tamil Nadu understands — the state of the Cauvery, Vaigai and Adyar rivers.

Existing System

• CPCB publishes the raw records and annual reports; analysis is left to the reader.
• State pollution-control board dashboards show current readings but no screening when a parameter is missing.
• Research papers model water quality with remote sensing or deep learning — beyond a beginner's semester and hard to verify.
• Gap: no simple, reproducible tool that (a) audits stations against the published criteria and (b) gives a defensible estimate of organic-pollution risk when BOD is unavailable.

Proposed System

• A cleaned station-sample table (station, date, pH, DO, BOD, conductivity, temperature, coliform) built from the NWMP downloads with a documented missing-value policy.
• A rule-based audit flagging each sample against the bathing criteria and summarising failures by station, river and year.
• A screening classifier — logistic regression as baseline, decision tree for explainability — predicting the BOD-exceedance class from co-measured quick parameters, with stratified 5-fold cross-validation and a precision/recall report for the exceedance class (recall matters most: a missed polluted sample is the costly error).
• A Django + MySQL tool: upload a monitoring CSV or type one reading; the app stores it, returns the screened band, lists the parameters likely to fail and plots the station's trend.

Feasibility Study

• Technical: the dataset is a few MB of CSV; pandas, scikit-learn and Django are already taught in CS3361, CS3491 and CCS375; everything runs on an average laptop.
• Economic: zero cost — public data, open-source stack.
• Operational: the tool needs no training; the audit table alone is immediately useful for a college environment audit or an NGO report. The honest limitation — screening estimates risk, it does not replace the lab BOD test — is printed on the results page.

In scope

• CPCB NWMP surface-water records for a bounded period (for example 2017–2022) and a chosen set of rivers/stations; the loader accepts the portal's CSV format.
• Six parameters: pH, DO, BOD, conductivity, temperature and total/faecal coliform.
• Rule-based compliance audit against the Class-B bathing criteria.
• Two classifiers (logistic regression, decision tree) with k-fold cross-validation; optional k-NN comparison.
• Django app: station list, sample entry/CSV upload, screening result page with trend chart; MySQL storage.

Out of scope (v1)

• Groundwater and coastal stations (different criteria tables).
• Real-time sensor feeds from continuous monitoring stations.
• Deep-learning or satellite-based models.
• Prediction of exact BOD values (this is a classification screening, not a lab substitute).
• Multi-user authentication beyond Django's admin — the tool is single-department oriented.

The project follows a linear (waterfall-style) plan with a small evaluation loop at the model stage — appropriate for a 12-week, single-person mini-project where requirements are stable and the risk sits in model quality, not in scope creep.

Evaluation protocol: the classifier is never judged on accuracy alone — the exceedance-class confusion matrix per fold is the reported result, and the decision tree's learned thresholds are printed and compared with the CPCB criteria as a sanity check.

The tool is a simple three-tier application with an offline analysis stage. A Python pipeline stage cleans the CPCB CSVs and computes the audit table; the model stage trains and serialises the classifier; the Django app then serves stations, samples and screening results from MySQL.

flowchart TD
  A["CPCB NWMP CSV downloads"] --> B["pandas cleaning pipeline"]
  B --> C["MySQL: stations & samples"]
  B --> D["Compliance audit vs bathing criteria"]
  C --> E["Model training (scikit-learn)"]
  E --> F["Serialised classifier (joblib)"]
  F --> G["Django app"]
  C --> G
  G --> H["Screening result page + trend chart"]
  G --> I["CSV upload / manual sample entry"]

ER diagram

erDiagram
  STATION ||--o{ SAMPLE : "is sampled at"
  SAMPLE ||--o{ PREDICTION : "is screened as"
  STATION {
    int station_id PK
    string station_name
    string river_name
    string station_type
  }
  SAMPLE {
    int sample_id PK
    int station_id FK
    date sampled_on
    float ph
    float do_mg_l
    float bod_mg_l
    float conductivity
    int coliform_mpn
  }
  PREDICTION {
    int prediction_id PK
    int sample_id FK
    string model_name
    int class_label
    float probability
    string flagged_params
  }

DFD level 0

flowchart LR
  U(["Student / NGO analyst"]) -- "CSV upload or manual reading" --> SYS(("Water Quality<br/>Screening Tool"))
  SYS -- "band, flagged parameters, trend chart" --> U
  C(["CPCB NWMP portal"]) -- "station CSV exports" --> SYS

One request walk-through: the analyst uploads a month of readings; Django parses the CSV with validation, inserts SAMPLE rows, runs each through the serialised classifier when BOD is blank, and renders the screening page with the band, probability and the parameters most likely to fail.

• Add a per-station time-series forecast (next-season band) once the screening classifier is stable.
• Extend to groundwater stations with the separate criteria table.
• Replace fixed thresholds with CPCB's Water Quality Index scoring once the portal exposes it consistently.
• A Tamil-language results page for field volunteers.
• Scheduled refresh: re-download the portal CSVs each quarter and re-run the audit automatically.
• Optional k-NN or random-forest comparison as a fifth-chapter experiment for students who want more.