Publish AQI bulletins data on Datasette

This notebook publishes the AQI bulletins data we collected on Datasette.

Steps:

1. Import data into a SQLite Database
2. Explore the SQLite db created
3. Opening the SQLite db in Datasette
4. Deploy Datasette using Vercel

Examples
References

STEP 1: Import data into a SQLite Database

import sqlite_utils
import pandas as pd

# Time series data -- AQI Bulletins data of all Indian Cities
time_series_df = pd.read_csv("C:/Users/dskcy/UEInfo/AQI_bulletins/data/Processed/AllIndiaBulletins_Master_2024.csv")

#clean
time_series_df = time_series_df[['date', 'City', 'No. Stations', 'Air Quality', 'Index Value', 'Prominent Pollutant']]
time_series_df.columns=['date', 'city', 'no_stations', 'aqi_category','aqi','prominent_pollutant']
time_series_df = time_series_df.replace(to_replace='Very poor', value='Very Poor')
# Replace "OZONE" with "O3" in the 'prominent_pollutant' column
time_series_df['prominent_pollutant'] = time_series_df['prominent_pollutant'].astype(str).str.replace("OZONE", "O3", regex=False)
time_series_df['date'] = pd.to_datetime(time_series_df['date'])

time_series_df['year'] = time_series_df['date'].dt.year
time_series_df['month'] = time_series_df['date'].dt.strftime('%b')

duplicates = time_series_df[time_series_df.duplicated(subset=['date', 'city'], keep=False)]
duplicates

	date	city	no_stations	aqi_category	aqi	prominent_pollutant	year	month
179252	2022-03-31	Aurangabad	1	Moderate	104	PM10	2022	Mar
179253	2022-03-31	Aurangabad	1	Poor	268	PM10	2022	Mar
179301	2022-04-01	Aurangabad	1	Very Poor	312	PM10	2022	Apr
179302	2022-04-01	Aurangabad	1	Moderate	108	PM10	2022	Apr
179670	2022-04-03	Aurangabad	1	Very Poor	317	PM10	2022	Apr
...	...	...	...	...	...	...	...	...
212132	2022-11-01	Aurangabad	1	Moderate	117	PM2.5	2022	Nov
212193	2022-11-02	Aurangabad	1	Moderate	107	PM10	2022	Nov
212194	2022-11-02	Aurangabad	1	Moderate	144	PM10	2022	Nov
212418	2022-11-03	Aurangabad	1	Moderate	169	PM10	2022	Nov
212419	2022-11-03	Aurangabad	1	Moderate	128	PM2.5	2022	Nov

68 rows × 8 columns

On these dates, the bulletins did not differentiate the two Aurangabads of Maharashtra and Bihar. Example

Removing these duplicates.

db = sqlite_utils.Database("C:/Users/dskcy/UEInfo/AQI_bulletins/data/Processed/aqi-bulletins.db")
# this creates or connects to an SQLite file

# Add a table in the db and insert all rows from csv in it
table_name = "aqi_bulletins"
db[table_name].insert_all(
                            time_series_df.drop_duplicates(subset=["date", "city"]).to_dict(orient="records"),
                            alter=True,
                            pk=("date", "city") # Primary Key \\ Don't auto-create row_id...
                         )

<Table aqi_bulletins (date, city, no_stations, aqi_category, aqi, prominent_pollutant, year, month)>

#Date in YYYY-MM-DD format
db.execute("""
    UPDATE aqi_bulletins
    SET date = substr(date, 1, 10)
""")
db.conn.commit()

# To avoid facet timeout errors, create index on columns on which we want facets
db.execute("CREATE INDEX IF NOT EXISTS idx_city ON aqi_bulletins(city)")
db.execute("CREATE INDEX IF NOT EXISTS idx_aqicategory ON aqi_bulletins(aqi_category)")
db.conn.commit()

STEP 2: Explore the SQLite db created

#See all tables in db
db.tables

[<Table aqi_bulletins (date, city, no_stations, aqi_category, aqi, prominent_pollutant, year, month)>]

for table_name in db.table_names():
    row_count = db[table_name].count
    print(f"{table_name}: {row_count} rows")

aqi_bulletins: 387686 rows

# Inspect schema
print(db["aqi_bulletins"].schema)

CREATE TABLE [aqi_bulletins] (
   [date] TEXT,
   [city] TEXT,
   [no_stations] INTEGER,
   [aqi_category] TEXT,
   [aqi] INTEGER,
   [prominent_pollutant] TEXT,
   [year] INTEGER,
   [month] TEXT,
   PRIMARY KEY ([date], [city])
)

# Show first 5 rows in the table
list(db[table_name].rows)[:5]  

[{'date': '2015-05-01',
  'city': 'Varanasi',
  'no_stations': 0,
  'aqi_category': 'Moderate',
  'aqi': 157,
  'prominent_pollutant': 'PM10',
  'year': 2015,
  'month': 'May'},
 {'date': '2015-05-01',
  'city': 'Kanpur',
  'no_stations': 0,
  'aqi_category': 'Moderate',
  'aqi': 175,
  'prominent_pollutant': 'PM10',
  'year': 2015,
  'month': 'May'},
 {'date': '2015-05-01',
  'city': 'Faridabad',
  'no_stations': 0,
  'aqi_category': 'Moderate',
  'aqi': 173,
  'prominent_pollutant': 'PM10',
  'year': 2015,
  'month': 'May'},
 {'date': '2015-05-01',
  'city': 'Ahmedabad',
  'no_stations': 0,
  'aqi_category': 'Moderate',
  'aqi': 168,
  'prominent_pollutant': 'PM2.5',
  'year': 2015,
  'month': 'May'},
 {'date': '2015-05-01',
  'city': 'Hyderabad',
  'no_stations': 0,
  'aqi_category': 'Moderate',
  'aqi': 189,
  'prominent_pollutant': 'PM2.5',
  'year': 2015,
  'month': 'May'}]

# Run SQL queries
## Max AQI in each month in Delhi
results = db.execute("""
    SELECT
        substr(date, 1, 7) AS month,
        MAX(aqi) AS max_aqi
    FROM
        aqi_bulletins
    WHERE
        city = 'Delhi'
    GROUP BY
        month
    ORDER BY
        month
""")
list(results)

[('2015-08', 246),
 ('2015-09', 302),
 ('2016-03', 352),
 ('2016-10', 445),
 ('2016-11', 497),
 ('2017-04', 383),
 ('2017-05', 368),
 ('2017-12', 469),
 ('2018-06', 447),
 ('2019-02', 382),
 ('2019-07', 317),
 ('2021-01', 460)]

db.conn.close()

STEP 3: Opening the SQLite db in Datasette

import json

metadata = {
    "title": "Air Quality Index (AQI) Bulletins",
  "description": "This dataset contains daily Air quality bulletins released by CPCB from 2015-24",
  "license": "GODL",
  "license_url": "https://www.data.gov.in/Godl",
  "source": "Central Pollution Control Board (CPCB)",
  "source_url": "https://cpcb.nic.in/AQI_Bulletin.php",
    "about": "UrbanEmissions.info",
    "about_url": "https://github.com/urbanemissionsinfo/AQI_bulletins",
    "databases": {
        "aqi-bulletins": {
            "tables": {
                "aqi_bulletins": {
                    "facets": ["city","aqi_category","year","month"]  # specify what you want
                }
            }
        }
    }
}

with open("metadata.json", "w") as f:
    json.dump(metadata, f, indent=4)

#!pip install datasette
#!pip install datasette-vega #For charting options
#!datasette "C:/Users/dskcy/UEInfo/AQI_bulletins/data/Processed/aqi_bulletins.db" --port 8001

import subprocess
proc = subprocess.Popen(["datasette",
                         "C:/Users/dskcy/UEInfo/AQI_bulletins/data/Processed/aqi-bulletins.db",
                         "-m", "metadata.json",
                         "--config", "suggest_facets:off", #Don't show suggested facets
                         "--config", "default_facet_size:10", #Show only top 10 in facet
                         "--config", "facet_time_limit_ms:1000", #Increased facet query timeout limit to 1000ms from default 200ms.
                         "--port", "8001",
                        ])

You can now see the db on Datasette on your local at http://localhost:8001/

# Kill process
proc.terminate()
proc.wait()  # wait for the process to cleanly exit

1

STEP 4: Deploy

You can use ngrok for pilot usecases.

You can also use Datasette Lite to deploy the db on user's browser itself.

The following procedure is to deploy Datasette on Vercel.

1. Keep the aqi-bulletins.db, metadata.json in a single directory (just to make it easy)
2. Create a new vercel.json file in the same folder.
3. Create a new requirements.txt file in the same folder.
4. Create a new api.py file in the same folder.
5. In your terminal, go to the directory containing the above files.
6. pip install uvicorn
7. uvicorn api:app --reload --host 0.0.0.0 --port 8000 -- this will start the Datasette on local. You can check if it is running fine on http://localhost:8000/
8. Install Vercel CLI tool
9. Run vercel login and complete the login/signup.
10. Run vercel --prod and complete all the steps. Your Datasette app will be published.

You can find the published AQI-Bulletins Datasette app here: AQI Bulletins Datasette

versel.json:

{
  "version": 2,
  "builds": [
    {
      "src": "api.py",
      "use": "@vercel/python"
    }
  ],
  "routes": [
    { "src": "/(.*)", "dest": "api.py" }
  ]
}

requirements.txt

datasette
datasette-vega
<anyother datasette plugins>

api.py

from datasette.app import Datasette
import os
import json

db_path = os.path.join(os.getcwd(), "aqi-bulletins.db")
metadata_path = os.path.join(os.getcwd(), "metadata.json")


with open(metadata_path) as f:
    metadata = json.load(f)


settings =  {
        "suggest_facets": False,
        "default_facet_size": 10,
        "facet_time_limit_ms": 1000,
    }
ds = Datasette([db_path], metadata=metadata, settings=settings)

# This is what Vercel uses as the ASGI entrypoint
app = ds.app()

Examples

Select all rows that have PM2.5

SELECT * FROM aqi_bulletins WHERE prominent_pollutant LIKE '%PM2.5%';

References

1. 10 min video tutorial on using sqlite-utils
2. Datasette config settings
3. Datasette plugins: Cluster map, full text search
4. Simon Willison on Datasette