Turn CPCB's daily AQI bulletin PDFs into a clean NCR dataset

What the bulletin contains

Each file on CPCB's AQI Bulletin page is headed "Air Quality Index on <date> @ 4 PM (Average of past 24 hours)". The table has six columns:

The bulletin we inspected for 26 September 2026 ran to 14 pages and 243 cities. After the table it has a list headed "Cities absent in AQI Bulletin due to insufficient data", a notes section, and a table of health statements for each AQI band.

The notes say two things your README must repeat. For cities with several locations the figure is an average, which CPCB itself describes as "not a scientifically sound approach" and is used for simplicity. Any publication using the data "shall duly acknowledge the contribution of respective agencies in generating the data".

Why this is a hiring signal

Plenty of Indian business data still arrives as PDFs: bank statements, government circulars, vendor rate cards, tender results. An analyst who can build an extractor, prove it complete and keep it running earns trust fast. It also shows restraint. You are reproducing the published number, not replacing it with your own.

The traps in the PDF text

When you pull text from these PDFs with a library such as PyMuPDF, every cell comes out on its own line. That is convenient until it isn't:

• Wrapped city names. "Aurangabad" and "(Maharashtra)" arrive on separate lines, so you cannot assume one line per cell.
• Repeated page headers. The title and the column headings recur on each page, in the middle of your data.
• Multi-value pollutant cells. "PM10, CO" is one cell containing two pollutants.
• Trailing sections. The absent-cities list and the health table follow the data and must not be parsed as rows.
• Spelling drift across days. Check city names across bulletins ("Yamuna nagar" and similar) before you join by name.

The robust anchor is the serial number followed later by a category word from a fixed list. Everything between the serial and the category is the city name, however many lines it takes.

A tested parser, run on synthetic text

The text below is synthetic. It imitates the line structure of a bulletin, including a wrapped name and a repeated header. The cities are real NCR names, but the values are invented.

import re
import pandas as pd

CATEGORIES = {"Good", "Satisfactory", "Moderate", "Poor", "Very Poor", "Severe"}
HEADER = {"S.No", "City", "Air Quality", "Index", "Value", "Prominent Pollutant",
          "No. of Stations", "Participated/", "Total Stations"}

sample = """Air Quality Index on Jan 05, 2026 @ 4 PM
(Average of past 24 hours)
S.No
City
Air Quality
1
Delhi
Poor
268
PM2.5
38/40
2
Faridabad
Moderate
181
PM2.5, PM10
3/4
Air Quality Index on Jan 05, 2026 @ 4 PM
S.No
City
3
Greater
Noida
Poor
233
PM2.5
2/2
4
Gurugram
Moderate
176
PM2.5
4/4
* Cities absent in AQI Bulletin due to insufficient data:
1) Example City"""

def parse(text, date):
    lines = [l.strip() for l in text.splitlines() if l.strip()]
    lines = [l for l in lines if l not in HEADER and not l.startswith(("Air Quality Index on", "(Average"))]
    end = next((i for i, l in enumerate(lines) if l.startswith("* Cities absent")), len(lines))
    lines, rows, i = lines[:end], [], 0
    while i < len(lines):
        if lines[i].isdigit():
            j = i + 1
            while j < len(lines) and lines[j] not in CATEGORIES:
                j += 1
            city = " ".join(lines[i + 1:j])
            category, aqi, pollutant, stations = lines[j], int(lines[j + 1]), lines[j + 2], lines[j + 3]
            used, total = map(int, stations.split("/"))
            rows.append({"date": date, "sno": int(lines[i]), "city": city, "category": category,
                         "aqi": aqi, "pollutants": pollutant, "stations_used": used, "stations_total": total})
            i = j + 4
        else:
            i += 1
    return pd.DataFrame(rows)

df = parse(sample, "2026-01-05")
print(df[["sno", "city", "category", "aqi", "pollutants", "stations_used", "stations_total"]])

# Completeness check: serial numbers must run 1..n with no gaps
assert list(df["sno"]) == list(range(1, len(df) + 1)), "a row was dropped"
# Consistency check: AQI must fall in the band named by the category
bands = {"Good": (0, 50), "Satisfactory": (51, 100), "Moderate": (101, 200),
         "Poor": (201, 300), "Very Poor": (301, 400), "Severe": (401, 500)}
bad = df[[not (bands[c][0] <= a <= bands[c][1]) for c, a in zip(df["category"], df["aqi"])]]
print("rows where category and AQI disagree:", len(bad))

The two assertions are the most important part. A serial-number gap means the parser skipped a row. A category that disagrees with its AQI band means a column slipped. The bands in the code follow the health-statement table printed at the end of each bulletin.

Plan for the full dataset

1. 1Download a full year of bulletins. Keep the original filenames and add a manifest CSV of URL, download time and SHA-256 hash.
2. 2Parse each file. Store row counts per day and fail loudly on any assertion.
3. 3Parse the absent-cities list into its own table. On a bad day, "missing" is itself information.
4. 4Normalise city names with a hand-built alias table.
5. 5Filter the NCR five (Delhi, Noida, Ghaziabad, Gurugram, Faridabad) and build a daily panel with station coverage.

The finished deliverable

• A Parquet or CSV file with one row per city per day, plus the absent-cities table.
• A calendar chart for each NCR city, coloured by AQI category, with grey for days the city was absent.
• A small table of days per category per city per month.
• A README covering the source and acknowledgement text, the parser design, the two validation checks, known name aliases, and the caveat that city values are station averages.

Questions you will be asked

• How do you know your parser did not silently drop a city?
• What happens when CPCB changes the layout next year?
• Why did you keep absent-city days instead of leaving them blank?
• How would you schedule this to run daily and alert you on failure?

Once the table exists, the natural follow-up is which pollutant leads NCR's AQI by season. Parsing and validation of this kind is practised throughout the Data Analyst programme, which runs live online, with optional in-person labs at our Noida centre.