Quick tip: Analysing Air Quality Data with SingleStoreDB
Tags: singlestoredb, ibis, pandas, aqi
Published: 2023-03-29 14:42:07

## Abstract
Air quality has become a significant concern in many cities globally due to increased pollution levels. In this short article, we'll see how to calculate the Air Quality Index (AQI), store air quality data in SingleStoreDB and quickly visualise the AQI.

## Introduction
In this article, we'll obtain air quality data from the Department for Environment, Food and Rural Affairs (Defra) in the UK and store it in SingleStoreDB. SingleStoreDB integrates with many external tools and libraries. We'll utilise one of these integrations in this example.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **AQI Demo Group** as our Workspace Group Name and **aqi-demo** as our Workspace Name. We'll make a note of our **password** and **host** name.

We'll use the **SQL Editor** to create a new database, as follows:

```sql
CREATE DATABASE IF NOT EXISTS aqi_db;
```

## Create a Deepnote account
We'll create a free [account](https://deepnote.com/sign-up) on the Deepnote website. Once logged in, we'll create a new Deepnote project to give us a new notebook. We'll also create a new directory called `data`.

## Obtain Air Quality Data
Courtesy of [Tony Hirst](https://www.linkedin.com/in/psychemedia), we'll use the [Python downloader for UK Air Quality data](https://gist.github.com/psychemedia/ad30077359ab1f19fbfcace5236c55c2) notebook to obtain sample data. We'll use the same site code of KC1 (London N. Kensington) and select 2022 for the year. In the final step of the notebook, we'll assign the results to a Pandas Dataframe and write it to a CSV file, as follows:

```python
import pandas as pd

...

aqi_df = pyreadr.read_r(fn)[fn.split(".")[0]]

aqi_df.to_csv("data/KC1_2022.csv", index = False)
```

At this point, we need to acknowledge Defra and [uk-air.defra.gov.uk](https://uk-air.defra.gov.uk/) as the source of information as well as the licence:

> © Crown 2023 copyright Defra via uk-air.defra.gov.uk, licenced under the [Open Government Licence](http://www.nationalarchives.gov.uk/doc/open-government-licence/version/2/) (OGL).

Let's take a quick look at the data:

```python
aqi_df.head()
```

The output should be similar to the following:

```
                  date        O3       NO      NO2  NOXasNO2      SO2        CO  PM10   PM2.5     wd   ws  temp                  site code
0  2022-01-01 00:00:00  39.11572  0.24946  9.94500  10.51875  0.26609  0.209556  14.9  12.076  212.0  3.0  10.4  London N. Kensington  KC1
1  2022-01-01 01:00:00  38.71658  0.12473  7.84125   8.22375 -0.53218  0.197914  16.3  12.642  214.8  3.7  10.5  London N. Kensington  KC1
2  2022-01-01 02:00:00  41.51056  0.12473  6.88500   7.07625  0.26609  0.221198  15.4  11.604  212.9  3.6  10.2  London N. Kensington  KC1
3  2022-01-01 03:00:00  45.70153  0.12473  4.97250   5.16375  0.26609  0.186272  13.6  10.189  208.8  3.9  10.6  London N. Kensington  KC1
4  2022-01-01 04:00:00  48.49551  0.12473  4.59000   4.59000  0.26609  0.186272  10.9   8.302  206.7  4.0  11.3  London N. Kensington  KC1
```

We can see the range of values:

```python
aqi_df.describe()
```

The output should be similar to the following:

```
                O3           NO          NO2     NOXasNO2          SO2           CO         PM10        PM2.5           wd           ws         temp
count  8617.000000  8593.000000  8593.000000  8593.000000  7923.000000  7737.000000  8592.000000  8622.000000  8664.000000  8664.000000  8664.000000
mean     49.920524     4.817513    18.449004    25.724694     0.907117     0.165013    14.606646     8.869804   193.805794     3.286034    10.889578
std      26.435005    18.278836    16.204639    39.501914     1.111520     0.139987    10.512747     8.222229    98.460865     1.567511     6.410335
min      -0.199570    -0.124730     0.000000     0.000000    -1.330450     0.000000     0.700000     0.377000     0.100000     0.000000    -5.900000
25%      32.929050     0.374190     7.458750     8.415000     0.266090     0.093136     8.200000     4.245000   107.475000     2.200000     6.500000
50%      51.089920     0.748380    12.813750    14.152500     0.532180     0.128062    11.900000     6.226000   219.100000     2.900000    10.700000
75%      66.456810     1.995680    23.906250    26.392500     1.064360     0.186272    17.500000    10.283000   265.000000     4.100000    15.300000
max     166.441380   368.701880   108.438750   652.927500    10.377510     1.641522   110.400000    84.623000   360.000000    12.900000    31.900000
```

We can see data for the polluting gases:

- Ozone (O3)
- Nitrogen monoxide (NO)
- Nitrogen dioxide (NO2)
- Sulphur dioxide (SO2)
- Carbon monoxide (CO)
- Particulates (PM10 and PM2.5)

We'll check for missing data:

```python
aqi_df.isna().sum()
```

The output should be similar to the following:

```
date           0
O3           143
NO           167
NO2          167
NOXasNO2     167
SO2          837
CO          1023
PM10         168
PM2.5        138
wd            96
ws            96
temp          96
site           0
code           0
```

For our initial analysis, we'll remove the rows with missing data:

```python
aqi_df.dropna(inplace = True)
```

## Calculate Air Quality Index (AQI)
Next, we'll use the `python-aqi` library to calculate the AQI. This library uses algorithms from:

- The United States Environmental Protection Agency (EPA)
- The China Ministry of Environmental Protection (MEP)

First, we need to install it, as follows:

```shell
!pip install python-aqi
```

We'll initially use the particulates (PM10 and PM2.5) to calculate the AQI using the EPA algorithm and create a new column in the Pandas Dataframe to store the result:

```python
import aqi

aqi_df["aqi"] = aqi_df.apply(
    lambda row: aqi.to_aqi([
        (aqi.POLLUTANT_PM25, row["PM2.5"]),
        (aqi.POLLUTANT_PM10, row["PM10"])
    ]),
    axis = 1
)
```

Now we'll store the Pandas Dataframe in SingleStoreDB.

## Store data in SingleStoreDB
We'll use Ibis to create the table and store the data in SingleStoreDB.

First, we'll install the package:

```shell
!pip install ibis-singlestoredb
```

Next, we'll import:

```python
import ibis

ibis.options.interactive = True
```

We'll now create a connection to SingleStoreDB:

```python
conn = ibis.singlestoredb.connect(
    "admin:<password>@<host>:3306/aqi_db"
)
```

We'll replace the `<password>` and `<host>` with the values from our SingleStoreDB Cloud account.

Now we'll create a new table in SingleStoreDB using the Pandas Dataframe:

```python
aqi_tbl = conn.create_table("aqi", aqi_df, force = True)
```

We can check the table using several methods. Let's use the **SQL Editor** in SingleStoreDB:

```sql
USE aqi_db;

SELECT * FROM aqi LIMIT 5;
```

The output should be similar to the following:

```
+---------------------+---------+---------+---------+----------+---------+----------+------+--------+-------+------+------+----------------------+------+------+
| date                | O3      | NO      | NO2     | NOXasNO2 | SO2     | CO       | PM10 | PM2.5  | wd    | ws   | temp | site                 | code | aqi  |
+---------------------+---------+---------+---------+----------+---------+----------+------+--------+-------+------+------+----------------------+------+------+
| 2022-01-01 03:00:00 | 45.7015 | 0.12473 |  4.9725 |  5.16375 | 0.26609 | 0.186272 | 13.6 | 10.189 | 208.8 |  3.9 | 10.6 | London N. Kensington | KC1  |   42 |
| 2022-01-01 04:00:00 | 48.4955 | 0.12473 |    4.59 |     4.59 | 0.26609 | 0.186272 | 10.9 |  8.302 | 206.7 |    4 | 11.3 | London N. Kensington | KC1  |   35 |
| 2022-01-01 17:00:00 | 46.3002 | 0.12473 | 8.22375 |    8.415 | 0.26609 | 0.197914 |   23 | 12.925 | 229.7 |  3.8 | 10.2 | London N. Kensington | KC1  |   53 |
| 2022-01-01 19:00:00 | 41.7101 | 0.12473 | 12.0487 |    12.24 | 0.26609 | 0.221198 | 23.4 | 14.057 | 231.8 |    5 |   11 | London N. Kensington | KC1  |   55 |
| 2022-01-01 21:00:00 | 44.7037 | 0.12473 | 9.75375 |  9.75375 | 0.26609 | 0.221198 | 24.3 | 13.679 | 231.8 |    5 | 11.3 | London N. Kensington | KC1  |   54 |
+---------------------+---------+---------+---------+----------+---------+----------+------+--------+-------+------+------+----------------------+------+------+
```

A variety of tools could now be used with SingleStoreDB to perform analytics on the data. For example, we have previously discussed the ease with which we can use analytics and visualisation tools with SingleStoreDB, such as [Apache Superset](https://dev.to/singlestore/quick-tip-connecting-apache-superset-to-singlestoredb-35a5), [Metabase](https://dev.to/singlestore/adtech-using-singlestoredb-kafka-and-metabase-2480) and [Looker](https://dev.to/singlestore/adtech-using-singlestoredb-and-looker-gh6).

## Bonus: Create visualisation
From our Python environment, we can also create a quick visualisation using Plotly:

```python
import pandas as pd
import plotly.graph_objs as go

# Define the ranges for each AQI category
aqi_bounds = [0, 51, 101, 151, 201, 301, 500]

# Define the names of each AQI category
aqi_categories = [
    "Good",
    "Moderate",
    "Unhealthy for Sensitive Groups",
    "Unhealthy",
    "Very Unhealthy",
    "Hazardous",
    "Very Hazardous"
]

# Define the colours for each AQI category
aqi_colors = [
    [0.0,   "green" ],
    [0.1,   "green" ],
    [0.102, "yellow"],
    [0.2,   "yellow"],
    [0.202, "orange"],
    [0.3,   "orange"],
    [0.302, "red"   ],
    [0.4,   "red"   ],
    [0.402, "purple"],
    [0.6,   "purple"],
    [0.602, "maroon"],
    [1.0,   "maroon"]
]

# Define the tick values and labels for the colorbar
tickvals = [(aqi_bounds[i] + aqi_bounds[i + 1]) / 2 for i in range(len(aqi_bounds)-1)]
ticktext = aqi_categories[:-1]

# Create a new figure object with a heatmap trace
fig = go.Figure(data = go.Heatmap(
    x = aqi_df["date"], y = aqi_df["site"], z = aqi_df["aqi"],
    colorscale = aqi_colors, zmin = aqi_bounds[0], zmax = aqi_bounds[-1],
    colorbar = dict(title = "Air Quality Index",
                    tickmode = "array",
                    ticktext = ticktext,
                    tickvals = tickvals
                )
    )
)

# Customise the layout of the figure
fig.update_layout(
    xaxis_title = "Date",
    yaxis_title = "Location",
    title = "Air Quality Index by Location and Date",
    width = 1000,
    height = 600
)

# Show the figure
fig.show()
```

This produces the heatmap shown in Figure 1.

![Figure 1. AQI for KC1.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/347b8w1atsehwh69q6ra.png)

<center>Figure 1. AQI for KC1.</center>

The overall quality of air throughout 2022 was **Good**. The next steps would be to drill down and further analyse the data, check the data for the other polluting gases, and so on. We could also make the heatmap more interesting by adding additional weather data.

## Summary
In this short article, we have quickly analysed some air quality data from Defra in the UK, using the US EPA algorithm with data for particulates (PM10 and PM2.5). However, we note that many countries use different algorithms to calculate their AQI, although the approach described in this article could be easily adapted for other countries.