Air Quality in Indian Metro Cities: An Analytics Study of CPCB Open Data (Python and Power BI)

Air Quality in Indian Metro Cities is a beginner-friendly analytics study for the B.Sc. IT Phase I project dissertation (Semester 5) at the University of Mumbai. It turns the Central Pollution Control Board's open data — published on the Government of India's data.gov.in portal as the Real Time Air Quality Index from Various Locations resource — into a clean, reproducible analysis of how air quality actually behaves in Delhi, Mumbai, Kolkata and Chennai, and then presents the findings in an interactive Power BI dashboard.

The study answers questions news headlines usually blur: How much worse is Delhi's winter air than its monsoon air, numerically? Which of the four metro cities has the highest share of days in the Poor or worse AQI categories? Do two stations in the same city disagree, and what does that say about siting? The work stays deliberately beginner-level: one scripted pipeline (fetch → clean → aggregate), plain pandas operations, matplotlib charts for the report, and Power BI Desktop's drag-and-drop interface for the dashboard — no forecasting models, no cloud deployment.

Everything is designed to be re-run by anyone: the pipeline is a handful of short scripts, the data is free government open data accessed with a personal API key from data.gov.in, the historical baseline comes from a CPCB-derived city-level dataset (2015–2020), and every chart regenerates from the cleaned CSV files. The deliverable is a Phase I dissertation that reads like an honest policy brief — plus a dashboard the viva panel can click through.

Indian metro cities debate air quality every winter, but the public conversation runs on single-day numbers: today's AQI, this week's emergency order. The Central Pollution Control Board actually publishes the underlying measurements as open government data on data.gov.in, yet the resource arrives as long-format station rows with missing readings, inconsistent station names and no aggregation — unusable for citizens, journalists or students without a cleaning pipeline. As a result, comparisons that matter for policy — how deep the winter spike is relative to the monsoon trough in each city, which stations are chronically the worst, whether the share of days in Poor-or-worse categories is changing year over year — get asserted without being shown.

This project fills that gap at beginner scale: a documented, reproducible Python pipeline plus a Power BI dashboard that turns CPCB's open data for Delhi, Mumbai, Kolkata and Chennai into clear, honest, sourceable findings that a non-technical reader can verify.

Abstract

This study analyses Central Pollution Control Board (CPCB) air-quality data for four Indian metro cities using Python and Power BI. A scripted pipeline acquires data from the data.gov.in open-government portal and a CPCB-derived 2015–2020 city-level dataset, cleans it into a tidy table, and produces exploratory, trend and cross-city analyses: monthly and seasonal patterns, pollutant profiles, and city-to-city and station-to-station comparisons. Findings are presented as an interactive Power BI dashboard and a policy-relevant dissertation chapter.

Introduction

Air quality is the most visible environmental issue in Indian cities, and Delhi-NCR's winter smog makes national news every November. Yet the underlying data is public: CPCB publishes station-level readings through data.gov.in under the government's open-data policy. The gap is not data availability but usability — the raw resource is a long list of station-pollutant-value rows that a citizen cannot directly interpret. This project converts that raw feed into context: trends, seasons and honest comparisons, built with beginner-level tools the B.Sc. IT syllabus already teaches.

Existing systems and the gap

• News reports and apps quote today's AQI but rarely show multi-year context, seasonal structure or the station-to-station spread within one city.
• Research dashboards exist but are complex; a first-year-style analysis with documented steps is missing for a beginner to learn from.
• The raw portal itself gives long-format rows with gaps and duplicate station spellings, which is exactly the data-wrangling practice a first analytics project needs.
• The gap: a reproducible, documented beginner pipeline that connects the official open-data resource to defensible, policy-relevant charts.

Proposed system

A three-layer study. The pipeline layer is two Python scripts: fetch (download the data.gov.in resource via its API using a personal key, or load the bundled historical CSV) and prepare (parse dates, map AQI categories, resolve station-name variants, handle missing readings, aggregate to city-month level). The analysis layer is Jupyter notebooks producing the EDA, seasonal decomposition by month, year-over-year comparisons and the three headline findings. The presentation layer is a Power BI Desktop dashboard with city, year and pollutant slicers and a map visual of station locations.

Feasibility

• Technical: the portal's API and bulk CSV need only a registered key and pandas' read_csv/JSON handling; Power BI Desktop is free. A modest laptop handles the file sizes easily.
• Economic: zero cost — government open data plus free software.
• Operational: the 12-week Phase I slot fits acquisition, cleaning, EDA, dashboard and writing, and leaves the deeper modelling (forecasting, GRAP-day analysis) as the natural Phase II continuation in Semester 6.

In scope

• Four metro cities (Delhi, Mumbai, Kolkata, Chennai) and all their CPCB stations present in the chosen datasets.
• Criteria pollutants and AQI as published: PM2.5, PM10, NO2, SO2, CO, O3, NH3 where available, plus the AQI value and its category.
• Time spans: the 2015–2020 historical city-level dataset plus the most recent months available from the data.gov.in feed at the time of the study.
• Monthly and seasonal aggregation, year-over-year comparison, city and station comparisons, and one winter-season deep dive for Delhi-NCR.
• Descriptive analytics only: aggregation, trends, ratios — with sources cited and data gaps stated.

Out of scope (and why)

• Forecasting and machine-learning models (SARIMA, regression prediction): a deliberate Phase II continuation, not a Phase I beginner deliverable.
• Satellite-derived or modelled data (CPCB ground stations only), and health-impact or source-apportionment claims, which need domain expertise beyond the syllabus.
• Real-time alerting or a hosted web dashboard; Power BI Desktop plus exported report figures meet the Phase I requirement.
• Cities beyond the four metros, so depth beats breadth in a 12-week slot.

The study follows a descriptive analytics research design: define questions → acquire → clean → aggregate → visualise → interpret, executed as a 12-week pipeline mapped to the Phase I (Semester 5) project-dissertation slot. Each phase ends with a versioned artefact (raw CSV, clean CSV, notebook, dashboard), so the guide can review concrete outputs at every meeting.

Quality checks built in: after cleaning, the script prints row counts before and after each rule so nothing is dropped silently; three known readings are spot-checked against the live portal table; and every figure caption in the report names the generating notebook cell, so the external can trace any chart back to code.

The architecture is a simple one-way pipeline with a CSV hand-off — deliberately beginner-shaped, with no database or cloud dependency in Phase I (MySQL remains available if the guide prefers a database deliverable, since the syllabus includes it). Everything on the left of the hand-off is scripted Python; everything on the right is notebooks and Power BI, both reading the same clean files so the dashboard and the dissertation can never disagree.

flowchart TD
  DG["data.gov.in resource: Real Time AQI from Various Locations"] --> F["fetch.py with personal API key"]
  KG["CPCB-derived 2015-2020 city-level CSV"] --> F
  F --> RAW["raw/ folder with fetch log"]
  RAW --> P["prepare.py: clean, normalise, map AQI categories"]
  P --> QC["Quality gate: row counts, spot-checks vs portal"]
  QC --> CL["data/clean CSVs + data dictionary"]
  CL --> N1["Notebook 01: EDA and distributions"]
  CL --> N2["Notebook 02: trends and comparisons"]
  N1 --> FIG["Figures for the dissertation"]
  N2 --> FIG
  CL --> PBI["Power BI Desktop: City, Station, Date, Reading tables"]
  PBI --> DASH["Dashboard with slicers and station map"]
  FIG --> RPT["Phase I dissertation"]
  DASH --> RPT

The dashboard reads a small star-shaped layout — one fact table of readings plus three lookup tables — which keeps DAX measures simple and is the gentlest possible introduction to dimensional thinking:

erDiagram
  CITY ||--o{ STATION : "has"
  STATION ||--o{ READING : "records"
  DATE ||--o{ READING : "dates"
  CITY {
    string city_id PK
    string city_name
    string state
  }
  STATION {
    string station_id PK
    string city_id FK
    string station_name
    real latitude
    real longitude
  }
  DATE {
    date date_id PK
    int month
    string season
    int year
  }
  READING {
    string reading_id PK
    string station_id FK
    date date_id FK
    string pollutant
    real value
    int aqi
    string aqi_category
  }

Design choices worth defending: a long-format fact table (one row per station-date-pollutant) matches how the portal publishes data and makes slicers trivial; a Season column derived from the month carries the study's central comparison; and keeping raw data immutable under raw/ means any cleaning mistake can be fixed by re-running, not by patching.

• Phase II forecasting: extend with a seasonal-naive or SARIMA baseline for monthly PM2.5, so the study graduates from description to prediction.
• Alert-day analysis: join CPCB AQI categories with GRAP-stage announcements in Delhi to show how policy days align with measured air.
• Automated refresh: schedule fetch.py so the dashboard stays current, or publish it through Power BI Service.
• More cities: tier-2 cities with newer CAAQMS networks drop into the same pipeline unchanged.
• Health context overlay: attach population density or hospital-admission statistics at the city level for richer, still-honest comparisons.