This project is deprecated.

Automated Air Quality Index and Allergen Database

By Damian M. Garcia, PharmD

https://damianmgarcia.com

Last Updated: 2020-09-13

This database may be viewed by anyone with the link, but it will function in a read-only mode for those users,

meaning changes cannot be made and many functions cannot be tested.

Takes a date (required) and dyspnea level (required if manual entry) and attempts to extract air quality

index (AQI) and allergen level data for that date for the San Antonio, Texas area from two data sources. The

air quality data source is a "clean" JSON file from airnow.gov. The allergen data source (the only one I

could find with numerical values) is a "dirty" HTML file from news4sanantonio.com. A dyspnea scale (0-4),

which is not a validated scale, is included to study statistical relationships between dyspnea and

environmental factors such as air pollution and airborne allergens.

1) Automated Database Updates

The program automatically extracts yesterday's data on a daily basis. The program can also take dates

entered by the user. Simply enter a date and dyspnea rating, then click the (+) button.

2) Automated Quality Control

The program attempts to validate data before adding it to the database. If the program is only able to get

partial data (i.e. only AQI or only allergens) due to validity issues, it will change the format of the

corresponding date to yellow and bold to notify database users that data extraction for that date may be

incomplete. If the program is not able to get any AQI or allergen data, it will change the format of the

corresponding date to red and bold to notify database users that data extraction for that date failed. For

either case, whether partial or complete data extraction failure, the program will automatically note these

cases and attempt to extract the data on a once-daily basis for up to 3 more attempts.

3) Automated Data Auditing

If a user attempts to change a date in the database, the program will notify the user that if they continue,

this action will result in the deletion of the date and its associated data from the database. If a user

attempts to change a value in the database, the program will request a reason for the change, which will then

be included in a data change log for that value along with a timestamp, the user's ID, the old value, and the

new value. If additional changes are made to the same value in the future, those changes will be appended to

the data change log.

4) Smart Data Filter

This feature allows the user to intelligently filter and show only data within a certain number of days of

interest. More than simply hiding the date rows past the cutoff, the program also scans through all data

columns to find columns without any data points. Columns that lack any data points for the date range of

interest will automatically be hidden, thus only showing relevant data.

5) Automated Creation of New Data Columns

The program will automatically create new data columns when necessary, such as when the program encounters a

new allergen not already in the database.

6) Data Categories at a Glance

The data values for AQI and allergens are colored to indicate severity categories according to their category

ranges. The category ranges that are used to determine colors are based on the category ranges created by the

United States Environmental Protection Agency for the AQI and by the American Academy of Allergy, Asthma &

Immunology National Allergen Bureau for allergen levels.

1) Parsing "Dirty" Data

Parsing data from the news4sanantonio.com HTML file is challenging. HTML as a source is not usually ideal: it

is full of various HTML tags between data points (e.g. <p>, <br>, etc.). Additionally, the dataset, although

extensive, is inconsistently formatted: allergen names for the same allergen sometimes vary (e.g. Mt Cedar vs

Mt. Cedar), the date formats in the dataset are inconsistent (e.g. mm-dd-yyyy vs m-dd-yyyy vs m-d-yy, etc.),

and there is also inconsistent use of delimiters (e.g. "," vs ";" vs ":").