retrieve_daily_aqi_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b319cefc-e171-4fd2-9654-244dc6ba5a0c",
   "metadata": {},
   "source": [
    "This was originally developed by Dr. David W. McDonald for use in DATA 512, a course in the UW MS Data Science degree program. This code is provided under the Creative Commons CC-BY license. Revision 1.2 - August 16, 2024\n",
    "\n",
    "The code is adapted to the specific needs of this project to capture data related to Corpus Christi at a daily level through fire season.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3bc1c895-ff68-432e-b70a-7cea4a11c22d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json, time\n",
    "import requests\n",
    "import pandas as pd\n",
    "from geopy.distance import geodesic\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "606f2650-6d2e-4bf7-96e4-a8a0392141a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d17621ac-8da9-454d-b487-26b72d31583e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#########\n",
    "#\n",
    "#    CONSTANTS\n",
    "#\n",
    "\n",
    "#\n",
    "#    This is the root of all AQS API URLs\n",
    "#\n",
    "API_REQUEST_URL = 'https://aqs.epa.gov/data/api'\n",
    "\n",
    "#\n",
    "#    These are some of the 'actions' we can ask the API to take or requests that we can make of the API\n",
    "#\n",
    "#    Sign-up request - generally only performed once - unless you lose your key\n",
    "API_ACTION_SIGNUP = '/signup?email={email}'\n",
    "#\n",
    "#    List actions provide information on API parameter values that are required by some other actions/requests\n",
    "API_ACTION_LIST_CLASSES = '/list/classes?email={email}&key={key}'\n",
    "API_ACTION_LIST_PARAMS = '/list/parametersByClass?email={email}&key={key}&pc={pclass}'\n",
    "API_ACTION_LIST_SITES = '/list/sitesByCounty?email={email}&key={key}&state={state}&county={county}'\n",
    "#\n",
    "#    Monitor actions are requests for monitoring stations that meet specific criteria\n",
    "API_ACTION_MONITORS_COUNTY = '/monitors/byCounty?email={email}&key={key}&param={param}&bdate={begin_date}&edate={end_date}&state={state}&county={county}'\n",
    "API_ACTION_MONITORS_BOX = '/monitors/byBox?email={email}&key={key}&param={param}&bdate={begin_date}&edate={end_date}&minlat={minlat}&maxlat={maxlat}&minlon={minlon}&maxlon={maxlon}'\n",
    "#\n",
    "#    Summary actions are requests for summary data. These are for daily summaries\n",
    "API_ACTION_DAILY_SUMMARY_COUNTY = '/dailyData/byCounty?email={email}&key={key}&param={param}&bdate={begin_date}&edate={end_date}&state={state}&county={county}'\n",
    "API_ACTION_DAILY_SUMMARY_BOX = '/dailyData/byBox?email={email}&key={key}&param={param}&bdate={begin_date}&edate={end_date}&minlat={minlat}&maxlat={maxlat}&minlon={minlon}&maxlon={maxlon}'\n",
    "API_ACTION_DAILY_SUMMARY_SITE = '/dailyData/bySite?email={email}&key={key}&param={param}&bdate={begin_date}&edate={end_date}&state={state}&county={county}&site={site}'\n",
    "\n",
    "# Example; returns annual summary ozone data for the Millbrook School site (#0014) in Wake County, NC for 2017 \n",
    "# (Note, for annual data, only the year portion of the bdate and edate are used and only whole years of data are returned. \n",
    "# For example, bdate = 20171231 and edate = 20180101 will return full data for 2017 and 2018):\n",
    "# https://aqs.epa.gov/data/api/annualData/bySite?email=test@aqs.api&key=test&param=44201&bdate=20170618&edate=20170618&state=37&county=183&site=0014\n",
    "API_ACTION_ANNUAL_SUMMARY_SITE = '/annualData/bySite?email={email}&key={key}&param={param}&bdate={begin_date}&edate={end_date}&state={state}&county={county}&site={site}'\n",
    "\n",
    "#\n",
    "#    It is always nice to be respectful of a free data resource.\n",
    "#    We're going to observe a 100 requests per minute limit - which is fairly nice\n",
    "API_LATENCY_ASSUMED = 0.002       # Assuming roughly 2ms latency on the API and network\n",
    "API_THROTTLE_WAIT = (1.0/100.0)-API_LATENCY_ASSUMED\n",
    "#\n",
    "#\n",
    "#    This is a template that covers most of the parameters for the actions we might take, from the set of actions\n",
    "#    above. In the examples below, most of the time parameters can either be supplied as individual values to a\n",
    "#    function - or they can be set in a copy of the template and passed in with the template.\n",
    "# \n",
    "AQS_REQUEST_TEMPLATE = {\n",
    "    \"email\":      \"\",     \n",
    "    \"key\":        \"\",      \n",
    "    \"state\":      \"\",     # the two digit state FIPS # as a string\n",
    "    \"county\":     \"\",     # the three digit county FIPS # as a string\n",
    "    \"begin_date\": \"\",     # the start of a time window in YYYYMMDD format\n",
    "    \"end_date\":   \"\",     # the end of a time window in YYYYMMDD format, begin_date and end_date must be in the same year\n",
    "    \"minlat\":    0.0,\n",
    "    \"maxlat\":    0.0,\n",
    "    \"minlon\":    0.0,\n",
    "    \"maxlon\":    0.0,\n",
    "    \"param\":     \"\",     # a list of comma separated 5 digit codes, max 5 codes requested\n",
    "    \"pclass\":    \"\",      # parameter class is only used by the List calls\n",
    "    \"site\": \"\",\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0daca88-cf54-4ea1-a3ab-4bc2bc004828",
   "metadata": {},
   "source": [
    "### Function to request signup info for api "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9cf0b4af-5d1f-4ecd-9f3b-79844b352ec9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "#    This implements the sign-up request. The parameters are standardized so that this function definition matches\n",
    "#    all of the others. However, the easiest way to call this is to simply call this function with your preferred\n",
    "#    email address.\n",
    "#\n",
    "def request_signup(email_address = None,\n",
    "                   endpoint_url = API_REQUEST_URL, \n",
    "                   endpoint_action = API_ACTION_SIGNUP, \n",
    "                   request_template = AQS_REQUEST_TEMPLATE,\n",
    "                   headers = None):\n",
    "    \n",
    "    # Make sure we have a string - if you don't have access to this email addres, things might go badly for you\n",
    "    if email_address:\n",
    "        request_template['email'] = email_address        \n",
    "    \n",
    "    if not request_template['email']: \n",
    "        raise Exception(\"Must supply an email address to call 'request_signup()'\")\n",
    "\n",
    "    if '@' not in request_template['email']: \n",
    "        raise Exception(f\"Must supply an email address to call 'request_signup()'. The string '{request_template['email']}' does not look like an email address.\")\n",
    "\n",
    "    # Compose the signup url - create a request URL by combining the endpoint_url with the parameters for the request\n",
    "    request_url = endpoint_url+endpoint_action.format(**request_template)\n",
    "        \n",
    "    # make the request\n",
    "    try:\n",
    "        # Wait first, to make sure we don't exceed a rate limit in the situation where an exception occurs\n",
    "        # during the request processing - throttling is always a good practice with a free data source\n",
    "        if API_THROTTLE_WAIT > 0.0:\n",
    "            time.sleep(API_THROTTLE_WAIT)\n",
    "        response = requests.get(request_url, headers=headers)\n",
    "        json_response = response.json()\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        json_response = None\n",
    "    return json_response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "142a047c-6dc6-4179-afde-e888c8596ec3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(\"Requesting SIGNUP ...\")\n",
    "# USERNAME = \"<your_email_here>\"\n",
    "# response = request_signup(USERNAME)\n",
    "# print(json.dumps(response,indent=4))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f7b38063-12b6-474b-9786-c7d316ee24fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "USERNAME = ''\n",
    "APIKEY = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60a1e15a-e80a-494c-b59e-f905e7871aff",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "234d206b-832a-4785-9c2e-63436b8e147f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "#   Once we have a list of the classes or groups of possible sensors, we can find the sensor IDs that make up that class (group)\n",
    "#   The one that looks to be associated with the Air Quality Index is \"AQI POLLUTANTS\"\n",
    "#   We'll use that to make another list request.\n",
    "#\n",
    "AQI_PARAM_CLASS = \"AQI POLLUTANTS\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc2db32e-b084-4b01-8203-7dd2f2d4d0bc",
   "metadata": {},
   "source": [
    "PM2.5 is the primary particulate pollution created by wildfires so we will only collect that air quality data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7073c43a-c620-42f6-832f-64e217af8340",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "#   Given the set of sensor codes, now we can create a parameter list or 'param' value as defined by the AQS API spec.\n",
    "#   It turns out that we want all of these measures for AQI, but we need to have two different param constants to get\n",
    "#   all seven of the code types. We can only have a max of 5 sensors/values request per param.\n",
    "#\n",
    "#   Gaseous AQI pollutants CO, SO2, NO2, and O2\n",
    "AQI_PARAMS_GASEOUS = \"42101,42401,42602,44201\"\n",
    "#\n",
    "#   Particulate AQI pollutants PM10, PM2.5, and Acceptable PM2.5\n",
    "AQI_PARAMS_PARTICULATES =  \"88101\"# \"88101,81102,88502\"  #\"88101\" #Removed 81102,88502 from original code\n",
    "#   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52574fcd-2d32-40ec-b94c-e6067b058cb9",
   "metadata": {},
   "source": [
    "## Prepare to collect Corpus Christi Data\n",
    "In tests not shown here we determined there are no monitoring stations in Corpus Christi, so we will use the box method to collect data from stations in a box centered around Corpus Christi with sides of length 50 miles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bbfc3dff-9f87-4e30-bf06-8b836023de44",
   "metadata": {},
   "outputs": [],
   "source": [
    "CITY_LOCATIONS = {\n",
    "    'corpus_christi' :       {'city'   : 'Corpus Christi',\n",
    "                       'county' : 'Nueces County',\n",
    "                       'state'  : 'Texas',\n",
    "                       'fips'   : '48273',\n",
    "                       'latlon' : [27.75, -97.17] }\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a45d888b-c512-466c-8775-55bc44b4d642",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "#   These are rough estimates for creating bounding boxes based on a city location\n",
    "#   You can find these rough estimates on the USGS website:\n",
    "#   https://www.usgs.gov/faqs/how-much-distance-does-a-degree-minute-and-second-cover-your-maps\n",
    "#\n",
    "LAT_25MILES = 25.0 * (1.0/27.75)    # This is about 25 miles of latitude in decimal degrees\n",
    "LON_25MILES = 25.0 * (1.0/97.17)    # This is about 25 miles of longitude in decimal degrees\n",
    "#\n",
    "#   Compute rough estimates for a bounding box around a given place\n",
    "#   The bounding box is scaled in 50 mile increments. That is, the bounding box will have sides that\n",
    "#   are rough multiples of 50 miles, with the center of the box around the indicated place.\n",
    "#   The scale parameter determines the scale (size) of the bounding box\n",
    "#\n",
    "def bounding_latlon(place=None,scale=1.0):\n",
    "    minlat = place['latlon'][0] - float(scale) * LAT_25MILES\n",
    "    maxlat = place['latlon'][0] + float(scale) * LAT_25MILES\n",
    "    minlon = place['latlon'][1] - float(scale) * LON_25MILES\n",
    "    maxlon = place['latlon'][1] + float(scale) * LON_25MILES\n",
    "    return [minlat,maxlat,minlon,maxlon]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ef698b3-6227-4441-a46f-d326305ba275",
   "metadata": {},
   "source": [
    "### Create bounding box"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "960b61c5-1e75-4ff2-b932-cd162185001f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "#    Create a copy of the AQS_REQUEST_TEMPLATE\n",
    "#\n",
    "request_data = AQS_REQUEST_TEMPLATE.copy()\n",
    "request_data['email'] = USERNAME\n",
    "request_data['key'] = APIKEY\n",
    "request_data['param'] = AQI_PARAMS_PARTICULATES     # same particulate request as the one abover\n",
    "# \n",
    "\n",
    "#   Now, we need bounding box parameters\n",
    "\n",
    "#   50 mile box\n",
    "#bbox = bounding_latlon(CITY_LOCATIONS['corpus_christi'],scale=1.0)\n",
    "#   100 mile box\n",
    "bbox = bounding_latlon(CITY_LOCATIONS['corpus_christi'],scale=2.0)\n",
    "#   150 mile box\n",
    "#bbox = bounding_latlon(CITY_LOCATIONS['corpus_christi'],scale=3.0)\n",
    "#   200 mile box\n",
    "#bbox = bounding_latlon(CITY_LOCATIONS['corpus_christi'],scale=4.0)\n",
    "\n",
    "# the bbox response comes back as a list - [minlat,maxlat,minlon,maxlon]\n",
    "\n",
    "#   put our bounding box into the request_data\n",
    "request_data['minlat'] = bbox[0]\n",
    "request_data['maxlat'] = bbox[1]\n",
    "request_data['minlon'] = bbox[2]\n",
    "request_data['maxlon'] = bbox[3]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7a6f77c-2776-440c-9819-0297608e526f",
   "metadata": {},
   "source": [
    "### Function to retieve box daily summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "03b71553-4e3e-47ba-8f7f-5fd1de08f959",
   "metadata": {},
   "outputs": [],
   "source": [
    "def request_daily_box_summary(email_address = None, key = None, param=None,\n",
    "                          begin_date = None, end_date = None, fips = None, site = None, minlat=None, maxlat=None, minlon=None, maxlon=None,\n",
    "                          endpoint_url = API_REQUEST_URL, \n",
    "                          endpoint_action = API_ACTION_DAILY_SUMMARY_BOX, \n",
    "                          request_template = AQS_REQUEST_TEMPLATE,\n",
    "                          headers = None):\n",
    "    if begin_date:\n",
    "        request_template['begin_date'] = begin_date\n",
    "    if end_date:\n",
    "        request_template['end_date'] = end_date\n",
    "        \n",
    "    # Make sure there are values that allow us to make a call - these are always required\n",
    "    if not request_template['email']:\n",
    "        raise Exception(\"Must supply an email address to call 'request_daily_box_summary()'\")\n",
    "    if not request_template['key']: \n",
    "        raise Exception(\"Must supply a key to call 'request_daily_box_summary()'\")\n",
    "    if not request_template['param']: \n",
    "        raise Exception(\"Must supply param values to call 'request_daily_box_summary()'\")\n",
    "    if not request_template['begin_date']: \n",
    "        raise Exception(\"Must supply a begin_date to call 'request_daily_box_summary()'\")\n",
    "    if not request_template['end_date']: \n",
    "        raise Exception(\"Must supply an end_date to call 'request_daily_box_summary()'\")\n",
    "    if not request_template['minlon']:\n",
    "        raise Exception(\"Must supply a box to call 'request_daily_box_summary()'\")\n",
    "    # Note we're not validating FIPS fields because not all of the daily summary actions require the FIPS numbers\n",
    "        \n",
    "    # compose the request\n",
    "    request_url = endpoint_url+endpoint_action.format(**request_template)\n",
    "        \n",
    "    # make the request\n",
    "    try:\n",
    "        # Wait first, to make sure we don't exceed a rate limit in the situation where an exception occurs\n",
    "        # during the request processing - throttling is always a good practice with a free data source\n",
    "        if API_THROTTLE_WAIT > 0.0:\n",
    "            time.sleep(API_THROTTLE_WAIT)\n",
    "        response = requests.get(request_url, headers=headers)\n",
    "        json_response = response.json()\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        json_response = None\n",
    "    return json_response"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eab1a7d4-9570-4d4a-9246-27a7660ec1ad",
   "metadata": {},
   "source": [
    "### Box daily summary parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c81c3ea3-c72e-4787-8698-e607a096024f",
   "metadata": {},
   "outputs": [],
   "source": [
    "box_request_data = AQS_REQUEST_TEMPLATE.copy()\n",
    "box_request_data['email'] = USERNAME\n",
    "box_request_data['key'] = APIKEY\n",
    "box_request_data['state'] = CITY_LOCATIONS['corpus_christi']['fips'][:2]\n",
    "box_request_data['county'] = CITY_LOCATIONS['corpus_christi']['fips'][2:]\n",
    "box_request_data['site'] =  \"0032\" #\"0314\" #\"1001\"\n",
    "box_request_data['param'] = AQI_PARAMS_PARTICULATES\n",
    "# box_request_data['begin_date'] = '20000501'\n",
    "# box_request_data['end_date'] = '20000831'\n",
    "box_request_data['minlat'] = bbox[0]\n",
    "box_request_data['maxlat'] = bbox[1]\n",
    "box_request_data['minlon'] = bbox[2]\n",
    "box_request_data['maxlon'] = bbox[3]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a331ed8-aae9-4d10-8c52-a3aefb5ae8f9",
   "metadata": {},
   "source": [
    "# Retrieve data and store to dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1c168467-073a-4111-9892-e02581de6f13",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Processing years: 100%|█| 22/22 [00:31<\n"
     ]
    }
   ],
   "source": [
    "all_years_data = []\n",
    "\n",
    "\n",
    "for year in tqdm(range(2000, 2022), desc=\"Processing years\"):\n",
    "    box_daily_summary_result = request_daily_box_summary(request_template=box_request_data, begin_date=str(year)+\"0501\", end_date=str(year)+\"0831\")\n",
    "    year_daily_box_data_df = pd.DataFrame(box_daily_summary_result['Data'])\n",
    "    all_years_data.append(year_daily_box_data_df)\n",
    "\n",
    "all_years_combined_df = pd.concat(all_years_data, ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d49b1364-18d9-43cf-bc3b-1a08d54356b5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32374 entries, 0 to 32373\n",
      "Data columns (total 32 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   state_code            32374 non-null  object \n",
      " 1   county_code           32374 non-null  object \n",
      " 2   site_number           32374 non-null  object \n",
      " 3   parameter_code        32374 non-null  object \n",
      " 4   poc                   32374 non-null  int64  \n",
      " 5   latitude              32374 non-null  float64\n",
      " 6   longitude             32374 non-null  float64\n",
      " 7   datum                 32374 non-null  object \n",
      " 8   parameter             32374 non-null  object \n",
      " 9   sample_duration_code  32374 non-null  object \n",
      " 10  sample_duration       32374 non-null  object \n",
      " 11  pollutant_standard    30746 non-null  object \n",
      " 12  date_local            32374 non-null  object \n",
      " 13  units_of_measure      32374 non-null  object \n",
      " 14  event_type            32374 non-null  object \n",
      " 15  observation_count     32374 non-null  int64  \n",
      " 16  observation_percent   32374 non-null  float64\n",
      " 17  validity_indicator    32374 non-null  object \n",
      " 18  arithmetic_mean       32374 non-null  float64\n",
      " 19  first_max_value       32374 non-null  float64\n",
      " 20  first_max_hour        32374 non-null  int64  \n",
      " 21  aqi                   30746 non-null  float64\n",
      " 22  method_code           32374 non-null  object \n",
      " 23  method                32374 non-null  object \n",
      " 24  local_site_name       32374 non-null  object \n",
      " 25  site_address          32374 non-null  object \n",
      " 26  state                 32374 non-null  object \n",
      " 27  county                32374 non-null  object \n",
      " 28  city                  32374 non-null  object \n",
      " 29  cbsa_code             32374 non-null  object \n",
      " 30  cbsa                  32374 non-null  object \n",
      " 31  date_of_last_change   32374 non-null  object \n",
      "dtypes: float64(6), int64(3), object(23)\n",
      "memory usage: 7.9+ MB\n"
     ]
    }
   ],
   "source": [
    "all_years_combined_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "6a19390d-87b2-4eae-b786-c18390ee746c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state_code</th>\n",
       "      <th>county_code</th>\n",
       "      <th>site_number</th>\n",
       "      <th>parameter_code</th>\n",
       "      <th>poc</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>datum</th>\n",
       "      <th>parameter</th>\n",
       "      <th>sample_duration_code</th>\n",
       "      <th>sample_duration</th>\n",
       "      <th>pollutant_standard</th>\n",
       "      <th>date_local</th>\n",
       "      <th>units_of_measure</th>\n",
       "      <th>event_type</th>\n",
       "      <th>observation_count</th>\n",
       "      <th>observation_percent</th>\n",
       "      <th>validity_indicator</th>\n",
       "      <th>arithmetic_mean</th>\n",
       "      <th>first_max_value</th>\n",
       "      <th>first_max_hour</th>\n",
       "      <th>aqi</th>\n",
       "      <th>method_code</th>\n",
       "      <th>method</th>\n",
       "      <th>local_site_name</th>\n",
       "      <th>site_address</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>city</th>\n",
       "      <th>cbsa_code</th>\n",
       "      <th>cbsa</th>\n",
       "      <th>date_of_last_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48</td>\n",
       "      <td>355</td>\n",
       "      <td>0032</td>\n",
       "      <td>88101</td>\n",
       "      <td>2</td>\n",
       "      <td>27.804489</td>\n",
       "      <td>-97.431553</td>\n",
       "      <td>NAD83</td>\n",
       "      <td>PM2.5 - Local Conditions</td>\n",
       "      <td>7</td>\n",
       "      <td>24 HOUR</td>\n",
       "      <td>PM25 24-hour 2006</td>\n",
       "      <td>2000-05-12</td>\n",
       "      <td>Micrograms/cubic meter (LC)</td>\n",
       "      <td>No Events</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>20.6</td>\n",
       "      <td>20.6</td>\n",
       "      <td>0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>118</td>\n",
       "      <td>R &amp; P Model 2025 PM2.5 Sequential w/WINS - GRA...</td>\n",
       "      <td>Corpus Christi Huisache</td>\n",
       "      <td>3810 Huisache Street</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Nueces</td>\n",
       "      <td>Corpus Christi</td>\n",
       "      <td>18580</td>\n",
       "      <td>Corpus Christi, TX</td>\n",
       "      <td>2024-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48</td>\n",
       "      <td>355</td>\n",
       "      <td>0032</td>\n",
       "      <td>88101</td>\n",
       "      <td>2</td>\n",
       "      <td>27.804489</td>\n",
       "      <td>-97.431553</td>\n",
       "      <td>NAD83</td>\n",
       "      <td>PM2.5 - Local Conditions</td>\n",
       "      <td>7</td>\n",
       "      <td>24 HOUR</td>\n",
       "      <td>PM25 24-hour 2006</td>\n",
       "      <td>2000-05-18</td>\n",
       "      <td>Micrograms/cubic meter (LC)</td>\n",
       "      <td>No Events</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>13.2</td>\n",
       "      <td>13.2</td>\n",
       "      <td>0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>118</td>\n",
       "      <td>R &amp; P Model 2025 PM2.5 Sequential w/WINS - GRA...</td>\n",
       "      <td>Corpus Christi Huisache</td>\n",
       "      <td>3810 Huisache Street</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Nueces</td>\n",
       "      <td>Corpus Christi</td>\n",
       "      <td>18580</td>\n",
       "      <td>Corpus Christi, TX</td>\n",
       "      <td>2024-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48</td>\n",
       "      <td>355</td>\n",
       "      <td>0032</td>\n",
       "      <td>88101</td>\n",
       "      <td>2</td>\n",
       "      <td>27.804489</td>\n",
       "      <td>-97.431553</td>\n",
       "      <td>NAD83</td>\n",
       "      <td>PM2.5 - Local Conditions</td>\n",
       "      <td>7</td>\n",
       "      <td>24 HOUR</td>\n",
       "      <td>PM25 24-hour 2006</td>\n",
       "      <td>2000-05-24</td>\n",
       "      <td>Micrograms/cubic meter (LC)</td>\n",
       "      <td>No Events</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>118</td>\n",
       "      <td>R &amp; P Model 2025 PM2.5 Sequential w/WINS - GRA...</td>\n",
       "      <td>Corpus Christi Huisache</td>\n",
       "      <td>3810 Huisache Street</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Nueces</td>\n",
       "      <td>Corpus Christi</td>\n",
       "      <td>18580</td>\n",
       "      <td>Corpus Christi, TX</td>\n",
       "      <td>2024-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>355</td>\n",
       "      <td>0032</td>\n",
       "      <td>88101</td>\n",
       "      <td>2</td>\n",
       "      <td>27.804489</td>\n",
       "      <td>-97.431553</td>\n",
       "      <td>NAD83</td>\n",
       "      <td>PM2.5 - Local Conditions</td>\n",
       "      <td>7</td>\n",
       "      <td>24 HOUR</td>\n",
       "      <td>PM25 24-hour 2006</td>\n",
       "      <td>2000-05-30</td>\n",
       "      <td>Micrograms/cubic meter (LC)</td>\n",
       "      <td>No Events</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>6.7</td>\n",
       "      <td>6.7</td>\n",
       "      <td>0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>118</td>\n",
       "      <td>R &amp; P Model 2025 PM2.5 Sequential w/WINS - GRA...</td>\n",
       "      <td>Corpus Christi Huisache</td>\n",
       "      <td>3810 Huisache Street</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Nueces</td>\n",
       "      <td>Corpus Christi</td>\n",
       "      <td>18580</td>\n",
       "      <td>Corpus Christi, TX</td>\n",
       "      <td>2024-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48</td>\n",
       "      <td>355</td>\n",
       "      <td>0032</td>\n",
       "      <td>88101</td>\n",
       "      <td>2</td>\n",
       "      <td>27.804489</td>\n",
       "      <td>-97.431553</td>\n",
       "      <td>NAD83</td>\n",
       "      <td>PM2.5 - Local Conditions</td>\n",
       "      <td>7</td>\n",
       "      <td>24 HOUR</td>\n",
       "      <td>PM25 24-hour 2006</td>\n",
       "      <td>2000-06-03</td>\n",
       "      <td>Micrograms/cubic meter (LC)</td>\n",
       "      <td>No Events</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>8.5</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>118</td>\n",
       "      <td>R &amp; P Model 2025 PM2.5 Sequential w/WINS - GRA...</td>\n",
       "      <td>Corpus Christi Huisache</td>\n",
       "      <td>3810 Huisache Street</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Nueces</td>\n",
       "      <td>Corpus Christi</td>\n",
       "      <td>18580</td>\n",
       "      <td>Corpus Christi, TX</td>\n",
       "      <td>2024-05-19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_code county_code site_number parameter_code  poc   latitude  \\\n",
       "0         48         355        0032          88101    2  27.804489   \n",
       "1         48         355        0032          88101    2  27.804489   \n",
       "2         48         355        0032          88101    2  27.804489   \n",
       "3         48         355        0032          88101    2  27.804489   \n",
       "4         48         355        0032          88101    2  27.804489   \n",
       "\n",
       "   longitude  datum                 parameter sample_duration_code  \\\n",
       "0 -97.431553  NAD83  PM2.5 - Local Conditions                    7   \n",
       "1 -97.431553  NAD83  PM2.5 - Local Conditions                    7   \n",
       "2 -97.431553  NAD83  PM2.5 - Local Conditions                    7   \n",
       "3 -97.431553  NAD83  PM2.5 - Local Conditions                    7   \n",
       "4 -97.431553  NAD83  PM2.5 - Local Conditions                    7   \n",
       "\n",
       "  sample_duration pollutant_standard  date_local             units_of_measure  \\\n",
       "0         24 HOUR  PM25 24-hour 2006  2000-05-12  Micrograms/cubic meter (LC)   \n",
       "1         24 HOUR  PM25 24-hour 2006  2000-05-18  Micrograms/cubic meter (LC)   \n",
       "2         24 HOUR  PM25 24-hour 2006  2000-05-24  Micrograms/cubic meter (LC)   \n",
       "3         24 HOUR  PM25 24-hour 2006  2000-05-30  Micrograms/cubic meter (LC)   \n",
       "4         24 HOUR  PM25 24-hour 2006  2000-06-03  Micrograms/cubic meter (LC)   \n",
       "\n",
       "  event_type  observation_count  observation_percent validity_indicator  \\\n",
       "0  No Events                  1                100.0                  Y   \n",
       "1  No Events                  1                100.0                  Y   \n",
       "2  No Events                  1                100.0                  Y   \n",
       "3  No Events                  1                100.0                  Y   \n",
       "4  No Events                  1                100.0                  Y   \n",
       "\n",
       "   arithmetic_mean  first_max_value  first_max_hour   aqi method_code  \\\n",
       "0             20.6             20.6               0  72.0         118   \n",
       "1             13.2             13.2               0  59.0         118   \n",
       "2             14.0             14.0               0  60.0         118   \n",
       "3              6.7              6.7               0  37.0         118   \n",
       "4              8.5              8.5               0  47.0         118   \n",
       "\n",
       "                                              method          local_site_name  \\\n",
       "0  R & P Model 2025 PM2.5 Sequential w/WINS - GRA...  Corpus Christi Huisache   \n",
       "1  R & P Model 2025 PM2.5 Sequential w/WINS - GRA...  Corpus Christi Huisache   \n",
       "2  R & P Model 2025 PM2.5 Sequential w/WINS - GRA...  Corpus Christi Huisache   \n",
       "3  R & P Model 2025 PM2.5 Sequential w/WINS - GRA...  Corpus Christi Huisache   \n",
       "4  R & P Model 2025 PM2.5 Sequential w/WINS - GRA...  Corpus Christi Huisache   \n",
       "\n",
       "           site_address  state  county            city cbsa_code  \\\n",
       "0  3810 Huisache Street  Texas  Nueces  Corpus Christi     18580   \n",
       "1  3810 Huisache Street  Texas  Nueces  Corpus Christi     18580   \n",
       "2  3810 Huisache Street  Texas  Nueces  Corpus Christi     18580   \n",
       "3  3810 Huisache Street  Texas  Nueces  Corpus Christi     18580   \n",
       "4  3810 Huisache Street  Texas  Nueces  Corpus Christi     18580   \n",
       "\n",
       "                 cbsa date_of_last_change  \n",
       "0  Corpus Christi, TX          2024-05-19  \n",
       "1  Corpus Christi, TX          2024-05-19  \n",
       "2  Corpus Christi, TX          2024-05-19  \n",
       "3  Corpus Christi, TX          2024-05-19  \n",
       "4  Corpus Christi, TX          2024-05-19  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_years_combined_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce5d2817-3f14-4e22-8271-f1147af90199",
   "metadata": {},
   "source": [
    "# Save data to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ff6b44d1-0b3c-4bde-928b-1e25327994d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_years_combined_df.to_csv('int_data/aqi_data_daily_raw.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d41600f-a0fc-470f-826c-9784c0336372",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (venv)",
   "language": "python",
   "name": "myenv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}