Final_Project_EDA_Data_generation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "47367708",
   "metadata": {},
   "source": [
    "# Topic 2A: Climate and the Environment, General Measurements and Statistics\n",
    "\n",
    "# Project Purpose: We want to understand if we can predict air quality at a particular location based off of weather and greenhouse gas emissions in a specified radius of that location\n",
    "\n",
    "## EDA\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61d20f55",
   "metadata": {},
   "source": [
    "## Important preliminary information:\n",
    "### AQI is measured per specific pollutants, PM2.5, PM10, Ozone, CO, SO2, NO2. With the overall Daily AQI being the maximum value of any specific pollutant AQI."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "badd2d0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "43609b01",
   "metadata": {},
   "outputs": [],
   "source": [
    "#aqi_by_county = pd.read_csv(\"county_aqi_2020_daily.csv\")\n",
    "daily_global_weather_2020 = pd.read_csv(\"datasets/2020_daily_global_weather.csv\").drop(columns=\"Unnamed: 0\")\n",
    "us_greenhouse_gas_emission_direct_emitter_gas_type = pd.read_csv(\"datasets/greenhouse_gas_emmiter_gas_types.csv\", low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "13014717",
   "metadata": {},
   "outputs": [],
   "source": [
    "aqi_sites = pd.read_csv(\"datasets/aqs_sites.csv\")\n",
    "\n",
    "daily_county_aqi = pd.read_csv(\"datasets/county_aqi_2020_daily.csv\") \n",
    "\n",
    "daily_temp = pd.read_csv(\"datasets/daily_temperature_2020.csv\")\n",
    "\n",
    "daily_wind = pd.read_csv(\"datasets/daily_wind_2020.csv\")\n",
    "\n",
    "traffic_volumes = pd.read_csv(\"datasets/traffic_volumes.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e2e73d3",
   "metadata": {},
   "source": [
    "## \"AQI by county\" shows the annual distribution of AQI and ratings by State and County. Each row represents the AQI distribution of a State and County pair.\n",
    "\n",
    "## \"daily global weather 2020\" shows the daily average temperature and precipitation on a global scale. Each row represents a specific station somewhere in the world per day.\n",
    "\n",
    "## \"US greenhouse gas emission\" shows CO2 equivalent green house gas emissions for various greehhouse gasses by state, county, city and year. Each row represents a CO2 equivalent green house gas emission for a speific gas in a city by year.\n",
    "\n",
    "## \"aqi sites\" has meta deta about the AQI sites where AQI values are measured and determined. Each row is a site.\n",
    "\n",
    "## \"daily_county_aqi\" shows the daily AQI and its defining parameter for a State and County. Each row represents a day for each state and county pair.\n",
    "\n",
    "## \"daily_temp\" shows the average daily temperature for a state and county. Each row represents a day for each state and county pair. (City column exists but does not apply to all rows)\n",
    "\n",
    "## \"daily_wind\" shows the average wind speed and wind direction for a state and county. Each row represents a day for each state and county pair. (City column exists but does not apply to all rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c85f7292",
   "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 Name</th>\n",
       "      <th>county Name</th>\n",
       "      <th>State Code</th>\n",
       "      <th>County Code</th>\n",
       "      <th>Date</th>\n",
       "      <th>AQI</th>\n",
       "      <th>Category</th>\n",
       "      <th>Defining Parameter</th>\n",
       "      <th>Defining Site</th>\n",
       "      <th>Number of Sites Reporting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>48</td>\n",
       "      <td>Good</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-003-0010</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>13</td>\n",
       "      <td>Good</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-003-0010</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>14</td>\n",
       "      <td>Good</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-003-0010</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>39</td>\n",
       "      <td>Good</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-003-0010</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>29</td>\n",
       "      <td>Good</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-003-0010</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336696</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>2020-12-27</td>\n",
       "      <td>32</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>56-045-0003</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336697</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>30</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>56-045-0003</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336698</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>33</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>56-045-0003</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336699</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>33</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>56-045-0003</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336700</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>34</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>56-045-0003</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>336701 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       State Name county Name  State Code  County Code        Date  AQI  \\\n",
       "0         Alabama     Baldwin           1            3  2020-01-01   48   \n",
       "1         Alabama     Baldwin           1            3  2020-01-04   13   \n",
       "2         Alabama     Baldwin           1            3  2020-01-07   14   \n",
       "3         Alabama     Baldwin           1            3  2020-01-10   39   \n",
       "4         Alabama     Baldwin           1            3  2020-01-13   29   \n",
       "...           ...         ...         ...          ...         ...  ...   \n",
       "336696    Wyoming      Weston          56           45  2020-12-27   32   \n",
       "336697    Wyoming      Weston          56           45  2020-12-28   30   \n",
       "336698    Wyoming      Weston          56           45  2020-12-29   33   \n",
       "336699    Wyoming      Weston          56           45  2020-12-30   33   \n",
       "336700    Wyoming      Weston          56           45  2020-12-31   34   \n",
       "\n",
       "       Category Defining Parameter Defining Site  Number of Sites Reporting  \n",
       "0          Good              PM2.5   01-003-0010                          1  \n",
       "1          Good              PM2.5   01-003-0010                          1  \n",
       "2          Good              PM2.5   01-003-0010                          1  \n",
       "3          Good              PM2.5   01-003-0010                          1  \n",
       "4          Good              PM2.5   01-003-0010                          1  \n",
       "...         ...                ...           ...                        ...  \n",
       "336696     Good              Ozone   56-045-0003                          2  \n",
       "336697     Good              Ozone   56-045-0003                          2  \n",
       "336698     Good              Ozone   56-045-0003                          2  \n",
       "336699     Good              Ozone   56-045-0003                          2  \n",
       "336700     Good              Ozone   56-045-0003                          2  \n",
       "\n",
       "[336701 rows x 10 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We want to predict the AQI column\n",
    "daily_county_aqi"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f93925b8",
   "metadata": {},
   "source": [
    "## Looking at the data sets, we have different granularities. For example, us_greenhouse_gas_emissions shows the ANNUAL CO2 equivalent emissions for a particular GH gas by State, County and City over multiple years. While the daily_no2, daily_so2, daily_co, daily_ozone, daily_temp, daily_wind, and daily_global_weather_2020 feature DAILY measurements over 1 year.\n",
    "\n",
    "\n",
    "## Since we are trying to predict AQI, we should not include the values used to calculate the AQI found within daily_co, daily_ozone, daily_no2, daily_so2.\n",
    "\n",
    "## Tentative Features to include in the model: daily_temp, daily_wind_speed, daily_wind_direction, daily_precipitation,  elevation, gh_gas_emissions by gas (co2 equivalents). Each row will be the daily value by each state and county."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e95c3505",
   "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>Station</th>\n",
       "      <th>Date</th>\n",
       "      <th>TAVG</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>PRCP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AE000041196</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>211.0</td>\n",
       "      <td>25.3330</td>\n",
       "      <td>55.5170</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AEM00041194</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>217.0</td>\n",
       "      <td>25.2550</td>\n",
       "      <td>55.3640</td>\n",
       "      <td>10.4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AFM00040938</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>54.0</td>\n",
       "      <td>34.2100</td>\n",
       "      <td>62.2280</td>\n",
       "      <td>977.2</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AG000060611</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>71.0</td>\n",
       "      <td>28.0500</td>\n",
       "      <td>9.6331</td>\n",
       "      <td>561.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AGE00147708</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>99.0</td>\n",
       "      <td>36.7200</td>\n",
       "      <td>4.0500</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1064278</th>\n",
       "      <td>USW00094847</td>\n",
       "      <td>2020-10-22</td>\n",
       "      <td>96.0</td>\n",
       "      <td>42.2314</td>\n",
       "      <td>-83.3308</td>\n",
       "      <td>192.3</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1064279</th>\n",
       "      <td>USW00094849</td>\n",
       "      <td>2020-10-22</td>\n",
       "      <td>67.0</td>\n",
       "      <td>45.0717</td>\n",
       "      <td>-83.5644</td>\n",
       "      <td>208.5</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1064280</th>\n",
       "      <td>USW00094860</td>\n",
       "      <td>2020-10-22</td>\n",
       "      <td>77.0</td>\n",
       "      <td>42.8939</td>\n",
       "      <td>-85.5447</td>\n",
       "      <td>237.1</td>\n",
       "      <td>165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1064281</th>\n",
       "      <td>USW00094908</td>\n",
       "      <td>2020-10-22</td>\n",
       "      <td>73.0</td>\n",
       "      <td>42.3978</td>\n",
       "      <td>-90.7036</td>\n",
       "      <td>321.9</td>\n",
       "      <td>361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1064282</th>\n",
       "      <td>USW00094910</td>\n",
       "      <td>2020-10-22</td>\n",
       "      <td>70.0</td>\n",
       "      <td>42.5544</td>\n",
       "      <td>-92.4011</td>\n",
       "      <td>264.6</td>\n",
       "      <td>747.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1064283 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Station        Date   TAVG  Latitude  Longitude  Elevation   PRCP\n",
       "0        AE000041196  2020-01-01  211.0   25.3330    55.5170       34.0    0.0\n",
       "1        AEM00041194  2020-01-01  217.0   25.2550    55.3640       10.4    0.0\n",
       "2        AFM00040938  2020-01-01   54.0   34.2100    62.2280      977.2   23.0\n",
       "3        AG000060611  2020-01-01   71.0   28.0500     9.6331      561.0   10.0\n",
       "4        AGE00147708  2020-01-01   99.0   36.7200     4.0500      222.0    0.0\n",
       "...              ...         ...    ...       ...        ...        ...    ...\n",
       "1064278  USW00094847  2020-10-22   96.0   42.2314   -83.3308      192.3  157.0\n",
       "1064279  USW00094849  2020-10-22   67.0   45.0717   -83.5644      208.5   99.0\n",
       "1064280  USW00094860  2020-10-22   77.0   42.8939   -85.5447      237.1  165.0\n",
       "1064281  USW00094908  2020-10-22   73.0   42.3978   -90.7036      321.9  361.0\n",
       "1064282  USW00094910  2020-10-22   70.0   42.5544   -92.4011      264.6  747.0\n",
       "\n",
       "[1064283 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#PRCP is average percipitation in tenths of a mm, TAVG is tempearture average at each station for each day\n",
    "daily_global_weather_2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dc825eac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['V_GHG_EMITTER_GAS.ADDRESS1', 'V_GHG_EMITTER_GAS.ADDRESS2',\n",
       "       'V_GHG_EMITTER_GAS.CITY', 'V_GHG_EMITTER_GAS.CO2E_EMISSION',\n",
       "       'V_GHG_EMITTER_GAS.COUNTY', 'V_GHG_EMITTER_GAS.FACILITY_ID',\n",
       "       'V_GHG_EMITTER_GAS.GAS_CODE', 'V_GHG_EMITTER_GAS.GAS_NAME',\n",
       "       'V_GHG_EMITTER_GAS.LATITUDE', 'V_GHG_EMITTER_GAS.LONGITUDE',\n",
       "       'V_GHG_EMITTER_GAS.STATE', 'V_GHG_EMITTER_GAS.STATE_NAME',\n",
       "       'V_GHG_EMITTER_GAS.YEAR', 'V_GHG_EMITTER_GAS.ZIP',\n",
       "       'V_GHG_EMITTER_GAS.FACILITY_NAME', 'V_GHG_EMITTER_GAS.COUNTY_FIPS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_greenhouse_gas_emission_direct_emitter_gas_type.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3d16b7ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['State Name', 'county Name', 'State Code', 'County Code', 'Date', 'AQI',\n",
       "       'Category', 'Defining Parameter', 'Defining Site',\n",
       "       'Number of Sites Reporting'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_county_aqi.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "90a80411",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['State Code', 'County Code', 'Site Number', 'Latitude', 'Longitude',\n",
       "       'Datum', 'Elevation', 'Land Use', 'Location Setting',\n",
       "       'Site Established Date', 'Site Closed Date', 'Met Site State Code',\n",
       "       'Met Site County Code', 'Met Site Site Number', 'Met Site Type',\n",
       "       'Met Site Distance', 'Met Site Direction', 'GMT Offset',\n",
       "       'Owning Agency', 'Local Site Name', 'Address', 'Zip Code', 'State Name',\n",
       "       'County Name', 'City Name', 'CBSA Name', 'Tribe Name',\n",
       "       'Extraction Date'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aqi_sites.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa48fad9",
   "metadata": {},
   "source": [
    "## The defining site column in daily_county_aqi is defined by \"State Code\"-\"County Code\"-\"Site Number\" for a given State and County in aqi_sites. Thus, the City name can be determined by the defining site using the aqi_sites table, if increased granularity is required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9668d902",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['State Code', 'County Code', 'Site Num', 'Parameter Code', 'POC',\n",
       "       'Latitude', 'Longitude', 'Datum', 'Parameter Name', 'Sample Duration',\n",
       "       'Pollutant Standard', 'Date Local', 'Units of Measure', 'Event Type',\n",
       "       'Observation Count', 'Observation Percent', 'Arithmetic Mean',\n",
       "       '1st Max Value', '1st Max Hour', 'AQI', 'Method Code', 'Method Name',\n",
       "       'Local Site Name', 'Address', 'State Name', 'County Name', 'City Name',\n",
       "       'CBSA Name', 'Date of Last Change'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_wind.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "521af833",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['State Code', 'County Code', 'Site Num', 'Parameter Code', 'POC',\n",
       "       'Latitude', 'Longitude', 'Datum', 'Parameter Name', 'Sample Duration',\n",
       "       'Pollutant Standard', 'Date Local', 'Units of Measure', 'Event Type',\n",
       "       'Observation Count', 'Observation Percent', 'Arithmetic Mean',\n",
       "       '1st Max Value', '1st Max Hour', 'AQI', 'Method Code', 'Method Name',\n",
       "       'Local Site Name', 'Address', 'State Name', 'County Name', 'City Name',\n",
       "       'CBSA Name', 'Date of Last Change'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_temp.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc559969",
   "metadata": {},
   "source": [
    "# Aggregating Daily temp, wind speed and wind direction by state and county"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3d65e85",
   "metadata": {},
   "source": [
    "## EDA on Daily Temperature Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bba9a7bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# EDA on daily temperature, wind speed and wind direction\n",
    "\n",
    "# Extracting relevant columns\n",
    "truncated_daily_temp = daily_temp[[\"State Code\", \"County Code\", \"Site Num\", \"State Name\", \"County Name\", \"City Name\", \"Latitude\", \"Longitude\", \"Date Local\", \"Arithmetic Mean\"]]\n",
    "\n",
    "# Array of US States and territories\n",
    "US_States_territories = us_greenhouse_gas_emission_direct_emitter_gas_type[\"V_GHG_EMITTER_GAS.STATE_NAME\"].unique()\n",
    "\n",
    "# Only US States and territories should be in our data\n",
    "truncated_daily_temp = truncated_daily_temp[truncated_daily_temp[\"State Name\"].str.upper().isin(US_States_territories)]\n",
    "\n",
    "# Impute missing 0s, with mean of the arithmetic mean, there are no null values\n",
    "truncated_daily_temp[\"Arithmetic Mean\"] = truncated_daily_temp[\"Arithmetic Mean\"].replace(0, truncated_daily_temp[\"Arithmetic Mean\"].mean())\n",
    "\n",
    "# Rename Arithmetic Mean column to include temperature and units (Temp - F)\n",
    "truncated_daily_temp = truncated_daily_temp.rename(columns={\"Arithmetic Mean\": \"Mean Temperature (F)\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "564498a7",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date Local</th>\n",
       "      <th>Mean Temperature (F)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.09210</td>\n",
       "      <td>-87.54350</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>53.540000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.09210</td>\n",
       "      <td>-87.54350</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>67.342857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.09210</td>\n",
       "      <td>-87.54350</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>26.580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.09210</td>\n",
       "      <td>-87.54350</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>44.830000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.09210</td>\n",
       "      <td>-87.54350</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>51.550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290326</th>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>800</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>43.84539</td>\n",
       "      <td>-104.20512</td>\n",
       "      <td>2020-12-27</td>\n",
       "      <td>31.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290327</th>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>800</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>43.84539</td>\n",
       "      <td>-104.20512</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>26.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290328</th>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>800</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>43.84539</td>\n",
       "      <td>-104.20512</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>22.958333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290329</th>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>800</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>43.84539</td>\n",
       "      <td>-104.20512</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>14.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290330</th>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>800</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>43.84539</td>\n",
       "      <td>-104.20512</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>24.833333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>290331 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        State Code  County Code  Site Num State Name County Name  \\\n",
       "0                1           53      1000    Alabama    Escambia   \n",
       "1                1           53      1000    Alabama    Escambia   \n",
       "2                1           53      1000    Alabama    Escambia   \n",
       "3                1           53      1000    Alabama    Escambia   \n",
       "4                1           53      1000    Alabama    Escambia   \n",
       "...            ...          ...       ...        ...         ...   \n",
       "290326          56           45       800    Wyoming      Weston   \n",
       "290327          56           45       800    Wyoming      Weston   \n",
       "290328          56           45       800    Wyoming      Weston   \n",
       "290329          56           45       800    Wyoming      Weston   \n",
       "290330          56           45       800    Wyoming      Weston   \n",
       "\n",
       "            City Name  Latitude  Longitude  Date Local  Mean Temperature (F)  \n",
       "0       Not in a city  31.09210  -87.54350  2020-01-01             53.540000  \n",
       "1       Not in a city  31.09210  -87.54350  2020-01-02             67.342857  \n",
       "2       Not in a city  31.09210  -87.54350  2020-01-03             26.580000  \n",
       "3       Not in a city  31.09210  -87.54350  2020-01-04             44.830000  \n",
       "4       Not in a city  31.09210  -87.54350  2020-01-05             51.550000  \n",
       "...               ...       ...        ...         ...                   ...  \n",
       "290326      Newcastle  43.84539 -104.20512  2020-12-27             31.000000  \n",
       "290327      Newcastle  43.84539 -104.20512  2020-12-28             26.000000  \n",
       "290328      Newcastle  43.84539 -104.20512  2020-12-29             22.958333  \n",
       "290329      Newcastle  43.84539 -104.20512  2020-12-30             14.583333  \n",
       "290330      Newcastle  43.84539 -104.20512  2020-12-31             24.833333  \n",
       "\n",
       "[290331 rows x 10 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truncated_daily_temp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b37ef451",
   "metadata": {},
   "source": [
    "## EDA on Daily Wind Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a4ca41a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract relevant Columns\n",
    "truncated_daily_wind = daily_wind[[\"State Code\", \"County Code\", \"Site Num\", \"State Name\", \"County Name\", \"City Name\", \"Latitude\", \"Longitude\", \"Parameter Name\", \"Date Local\", \"Arithmetic Mean\"]]\n",
    "\n",
    "# Only US States and territories should be in our data\n",
    "truncated_daily_wind = truncated_daily_wind[truncated_daily_wind[\"State Name\"].str.upper().isin(US_States_territories)]\n",
    "\n",
    "# Seperate wind direction and wind speed before imputing missing values\n",
    "daily_wind_speed = truncated_daily_wind[truncated_daily_wind[\"Parameter Name\"] == \"Wind Speed - Resultant\"].reset_index(drop=True)\n",
    "\n",
    "daily_wind_direction = truncated_daily_wind[truncated_daily_wind[\"Parameter Name\"] == \"Wind Direction - Resultant\"].reset_index(drop=True)\n",
    "\n",
    "# Impute missing values or 0 values with mean of the column\n",
    "daily_wind_speed[\"Arithmetic Mean\"] = daily_wind_speed[\"Arithmetic Mean\"].replace(0, daily_wind_speed[\"Arithmetic Mean\"].mean())\n",
    "\n",
    "daily_wind_direction[\"Arithmetic Mean\"] = daily_wind_direction[\"Arithmetic Mean\"].replace(0, daily_wind_direction[\"Arithmetic Mean\"].mean())\n",
    "\n",
    "# Rename Arithmetic Mean columns to include the measurement and units\n",
    "daily_wind_speed = daily_wind_speed.rename(columns={\"Arithmetic Mean\": \"Mean Wind Speed (knots)\"}).drop(columns=[\"Parameter Name\"])\n",
    "daily_wind_direction = daily_wind_direction.rename(columns={\"Arithmetic Mean\": \"Mean Wind Direction (compass degrees)\"}).drop(columns=[\"Parameter Name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8a80644b",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date Local</th>\n",
       "      <th>Mean Wind Speed (knots)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239426</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-27</td>\n",
       "      <td>1.795833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239427</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>1.212500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239428</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>1.441667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239429</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>1.466667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239430</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>1.687500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>239431 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        State Code  County Code  Site Num State Name County Name  \\\n",
       "0                1           53      1000    Alabama    Escambia   \n",
       "1                1           53      1000    Alabama    Escambia   \n",
       "2                1           53      1000    Alabama    Escambia   \n",
       "3                1           53      1000    Alabama    Escambia   \n",
       "4                1           53      1000    Alabama    Escambia   \n",
       "...            ...          ...       ...        ...         ...   \n",
       "239426          56           39      1013    Wyoming       Teton   \n",
       "239427          56           39      1013    Wyoming       Teton   \n",
       "239428          56           39      1013    Wyoming       Teton   \n",
       "239429          56           39      1013    Wyoming       Teton   \n",
       "239430          56           39      1013    Wyoming       Teton   \n",
       "\n",
       "            City Name   Latitude   Longitude  Date Local  \\\n",
       "0       Not in a city  31.092100  -87.543500  2020-01-01   \n",
       "1       Not in a city  31.092100  -87.543500  2020-01-02   \n",
       "2       Not in a city  31.092100  -87.543500  2020-01-03   \n",
       "3       Not in a city  31.092100  -87.543500  2020-01-04   \n",
       "4       Not in a city  31.092100  -87.543500  2020-01-05   \n",
       "...               ...        ...         ...         ...   \n",
       "239426  Not in a city  44.373056 -110.830833  2020-12-27   \n",
       "239427  Not in a city  44.373056 -110.830833  2020-12-28   \n",
       "239428  Not in a city  44.373056 -110.830833  2020-12-29   \n",
       "239429  Not in a city  44.373056 -110.830833  2020-12-30   \n",
       "239430  Not in a city  44.373056 -110.830833  2020-12-31   \n",
       "\n",
       "        Mean Wind Speed (knots)  \n",
       "0                      4.349492  \n",
       "1                      4.349492  \n",
       "2                      4.349492  \n",
       "3                      4.349492  \n",
       "4                      4.349492  \n",
       "...                         ...  \n",
       "239426                 1.795833  \n",
       "239427                 1.212500  \n",
       "239428                 1.441667  \n",
       "239429                 1.466667  \n",
       "239430                 1.687500  \n",
       "\n",
       "[239431 rows x 10 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_wind_speed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a5adcf38",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date Local</th>\n",
       "      <th>Mean Wind Direction (compass degrees)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>179.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>108.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>271.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>230.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>251.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242333</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-27</td>\n",
       "      <td>190.375000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242334</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>171.375000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242335</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>181.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242336</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>173.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242337</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>157.958333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>242338 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        State Code  County Code  Site Num State Name County Name  \\\n",
       "0                1           53      1000    Alabama    Escambia   \n",
       "1                1           53      1000    Alabama    Escambia   \n",
       "2                1           53      1000    Alabama    Escambia   \n",
       "3                1           53      1000    Alabama    Escambia   \n",
       "4                1           53      1000    Alabama    Escambia   \n",
       "...            ...          ...       ...        ...         ...   \n",
       "242333          56           39      1013    Wyoming       Teton   \n",
       "242334          56           39      1013    Wyoming       Teton   \n",
       "242335          56           39      1013    Wyoming       Teton   \n",
       "242336          56           39      1013    Wyoming       Teton   \n",
       "242337          56           39      1013    Wyoming       Teton   \n",
       "\n",
       "            City Name   Latitude   Longitude  Date Local  \\\n",
       "0       Not in a city  31.092100  -87.543500  2020-01-01   \n",
       "1       Not in a city  31.092100  -87.543500  2020-01-02   \n",
       "2       Not in a city  31.092100  -87.543500  2020-01-03   \n",
       "3       Not in a city  31.092100  -87.543500  2020-01-04   \n",
       "4       Not in a city  31.092100  -87.543500  2020-01-05   \n",
       "...               ...        ...         ...         ...   \n",
       "242333  Not in a city  44.373056 -110.830833  2020-12-27   \n",
       "242334  Not in a city  44.373056 -110.830833  2020-12-28   \n",
       "242335  Not in a city  44.373056 -110.830833  2020-12-29   \n",
       "242336  Not in a city  44.373056 -110.830833  2020-12-30   \n",
       "242337  Not in a city  44.373056 -110.830833  2020-12-31   \n",
       "\n",
       "        Mean Wind Direction (compass degrees)  \n",
       "0                                  179.900000  \n",
       "1                                  108.857143  \n",
       "2                                  271.000000  \n",
       "3                                  230.100000  \n",
       "4                                  251.100000  \n",
       "...                                       ...  \n",
       "242333                             190.375000  \n",
       "242334                             171.375000  \n",
       "242335                             181.166667  \n",
       "242336                             173.666667  \n",
       "242337                             157.958333  \n",
       "\n",
       "[242338 rows x 10 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_wind_direction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "519acd42",
   "metadata": {},
   "source": [
    "## Merging the wind, and temperature data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4b58e0e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "wind_data_merge = pd.merge(daily_wind_direction, daily_wind_speed, how='inner', on=[\"State Code\", \"County Code\", \"Site Num\", \"State Name\", \"County Name\", \"City Name\", \"Latitude\", \"Longitude\", \"Date Local\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0b8bc65d",
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_data = truncated_daily_temp.merge(wind_data_merge, how='inner', on=[\"State Code\", \"County Code\", \"Site Num\", \"State Name\", \"County Name\", \"City Name\", \"Latitude\", \"Longitude\", \"Date Local\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef88dd74",
   "metadata": {},
   "source": [
    "## Final dataset which includes weather data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "375822c4",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date Local</th>\n",
       "      <th>Mean Temperature (F)</th>\n",
       "      <th>Mean Wind Direction (compass degrees)</th>\n",
       "      <th>Mean Wind Speed (knots)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>53.540000</td>\n",
       "      <td>179.900000</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>67.342857</td>\n",
       "      <td>108.857143</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>26.580000</td>\n",
       "      <td>271.000000</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>44.830000</td>\n",
       "      <td>230.100000</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>51.550000</td>\n",
       "      <td>251.100000</td>\n",
       "      <td>4.349492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228161</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-27</td>\n",
       "      <td>15.250000</td>\n",
       "      <td>190.375000</td>\n",
       "      <td>1.795833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228162</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>-4.833333</td>\n",
       "      <td>171.375000</td>\n",
       "      <td>1.212500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228163</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>-1.625000</td>\n",
       "      <td>181.166667</td>\n",
       "      <td>1.441667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228164</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>8.291667</td>\n",
       "      <td>173.666667</td>\n",
       "      <td>1.466667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228165</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>18.875000</td>\n",
       "      <td>157.958333</td>\n",
       "      <td>1.687500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228166 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        State Code  County Code  Site Num State Name County Name  \\\n",
       "0                1           53      1000    Alabama    Escambia   \n",
       "1                1           53      1000    Alabama    Escambia   \n",
       "2                1           53      1000    Alabama    Escambia   \n",
       "3                1           53      1000    Alabama    Escambia   \n",
       "4                1           53      1000    Alabama    Escambia   \n",
       "...            ...          ...       ...        ...         ...   \n",
       "228161          56           39      1013    Wyoming       Teton   \n",
       "228162          56           39      1013    Wyoming       Teton   \n",
       "228163          56           39      1013    Wyoming       Teton   \n",
       "228164          56           39      1013    Wyoming       Teton   \n",
       "228165          56           39      1013    Wyoming       Teton   \n",
       "\n",
       "            City Name   Latitude   Longitude  Date Local  \\\n",
       "0       Not in a city  31.092100  -87.543500  2020-01-01   \n",
       "1       Not in a city  31.092100  -87.543500  2020-01-02   \n",
       "2       Not in a city  31.092100  -87.543500  2020-01-03   \n",
       "3       Not in a city  31.092100  -87.543500  2020-01-04   \n",
       "4       Not in a city  31.092100  -87.543500  2020-01-05   \n",
       "...               ...        ...         ...         ...   \n",
       "228161  Not in a city  44.373056 -110.830833  2020-12-27   \n",
       "228162  Not in a city  44.373056 -110.830833  2020-12-28   \n",
       "228163  Not in a city  44.373056 -110.830833  2020-12-29   \n",
       "228164  Not in a city  44.373056 -110.830833  2020-12-30   \n",
       "228165  Not in a city  44.373056 -110.830833  2020-12-31   \n",
       "\n",
       "        Mean Temperature (F)  Mean Wind Direction (compass degrees)  \\\n",
       "0                  53.540000                             179.900000   \n",
       "1                  67.342857                             108.857143   \n",
       "2                  26.580000                             271.000000   \n",
       "3                  44.830000                             230.100000   \n",
       "4                  51.550000                             251.100000   \n",
       "...                      ...                                    ...   \n",
       "228161             15.250000                             190.375000   \n",
       "228162             -4.833333                             171.375000   \n",
       "228163             -1.625000                             181.166667   \n",
       "228164              8.291667                             173.666667   \n",
       "228165             18.875000                             157.958333   \n",
       "\n",
       "        Mean Wind Speed (knots)  \n",
       "0                      4.349492  \n",
       "1                      4.349492  \n",
       "2                      4.349492  \n",
       "3                      4.349492  \n",
       "4                      4.349492  \n",
       "...                         ...  \n",
       "228161                 1.795833  \n",
       "228162                 1.212500  \n",
       "228163                 1.441667  \n",
       "228164                 1.466667  \n",
       "228165                 1.687500  \n",
       "\n",
       "[228166 rows x 12 columns]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "c52605bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "368"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(weather_data[\"City Name\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "447f7d8d",
   "metadata": {},
   "source": [
    "# Incorporating land use, elevation and location setting for the AQI sites into weather data set as features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "71c5c164",
   "metadata": {},
   "outputs": [],
   "source": [
    "truncated_aqi_sites = aqi_sites[[\"State Code\", \"County Code\", \"Site Number\", \"State Name\", \"County Name\", \"City Name\", \"Elevation\", \"Land Use\", \"Location Setting\"]].rename(columns={\"Site Number\": \"Site Num\"})\n",
    "\n",
    "# Only showing US and territories aqi sites\n",
    "truncated_aqi_sites = truncated_aqi_sites[truncated_aqi_sites[\"State Name\"].str.upper().isin(US_States_territories)]\n",
    "\n",
    "truncated_aqi_sites[\"State Code\"] = truncated_aqi_sites[\"State Code\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "68dbb65d",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>Land Use</th>\n",
       "      <th>Location Setting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Brewton</td>\n",
       "      <td>37.00</td>\n",
       "      <td>RESIDENTIAL</td>\n",
       "      <td>SUBURBAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>2</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Brewton</td>\n",
       "      <td>50.00</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a City</td>\n",
       "      <td>89.61</td>\n",
       "      <td>AGRICULTURAL</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    State Code  County Code  Site Num State Name County Name      City Name  \\\n",
       "80           1           53         1    Alabama    Escambia        Brewton   \n",
       "81           1           53         2    Alabama    Escambia        Brewton   \n",
       "82           1           53      1000    Alabama    Escambia  Not in a City   \n",
       "\n",
       "    Elevation      Land Use       Location Setting  \n",
       "80      37.00   RESIDENTIAL               SUBURBAN  \n",
       "81      50.00    COMMERCIAL  URBAN AND CENTER CITY  \n",
       "82      89.61  AGRICULTURAL                  RURAL  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truncated_aqi_sites[(truncated_aqi_sites[\"State Name\"] == \"Alabama\") & (truncated_aqi_sites[\"County Name\"] == \"Escambia\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "29772a4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_set = weather_data.merge(truncated_aqi_sites, on=[\"State Code\", \"County Code\", \"Site Num\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "e81fdf0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_set = feature_set.drop(columns=[\"State Name_y\", \"County Name_y\", \"City Name_y\"]).rename(columns={\"State Name_x\": \"State Name\", \"County Name_x\": \"County Name\", \"City Name_x\": \"City Name\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "1859972e",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date Local</th>\n",
       "      <th>Mean Temperature (F)</th>\n",
       "      <th>Mean Wind Direction (compass degrees)</th>\n",
       "      <th>Mean Wind Speed (knots)</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>Land Use</th>\n",
       "      <th>Location Setting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>53.540000</td>\n",
       "      <td>179.900000</td>\n",
       "      <td>4.349492</td>\n",
       "      <td>89.61</td>\n",
       "      <td>AGRICULTURAL</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>67.342857</td>\n",
       "      <td>108.857143</td>\n",
       "      <td>4.349492</td>\n",
       "      <td>89.61</td>\n",
       "      <td>AGRICULTURAL</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>26.580000</td>\n",
       "      <td>271.000000</td>\n",
       "      <td>4.349492</td>\n",
       "      <td>89.61</td>\n",
       "      <td>AGRICULTURAL</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>44.830000</td>\n",
       "      <td>230.100000</td>\n",
       "      <td>4.349492</td>\n",
       "      <td>89.61</td>\n",
       "      <td>AGRICULTURAL</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Escambia</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>31.092100</td>\n",
       "      <td>-87.543500</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>51.550000</td>\n",
       "      <td>251.100000</td>\n",
       "      <td>4.349492</td>\n",
       "      <td>89.61</td>\n",
       "      <td>AGRICULTURAL</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228161</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-27</td>\n",
       "      <td>15.250000</td>\n",
       "      <td>190.375000</td>\n",
       "      <td>1.795833</td>\n",
       "      <td>2230.00</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228162</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>-4.833333</td>\n",
       "      <td>171.375000</td>\n",
       "      <td>1.212500</td>\n",
       "      <td>2230.00</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228163</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>-1.625000</td>\n",
       "      <td>181.166667</td>\n",
       "      <td>1.441667</td>\n",
       "      <td>2230.00</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228164</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>8.291667</td>\n",
       "      <td>173.666667</td>\n",
       "      <td>1.466667</td>\n",
       "      <td>2230.00</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228165</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>18.875000</td>\n",
       "      <td>157.958333</td>\n",
       "      <td>1.687500</td>\n",
       "      <td>2230.00</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228166 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        State Code  County Code  Site Num State Name County Name  \\\n",
       "0                1           53      1000    Alabama    Escambia   \n",
       "1                1           53      1000    Alabama    Escambia   \n",
       "2                1           53      1000    Alabama    Escambia   \n",
       "3                1           53      1000    Alabama    Escambia   \n",
       "4                1           53      1000    Alabama    Escambia   \n",
       "...            ...          ...       ...        ...         ...   \n",
       "228161          56           39      1013    Wyoming       Teton   \n",
       "228162          56           39      1013    Wyoming       Teton   \n",
       "228163          56           39      1013    Wyoming       Teton   \n",
       "228164          56           39      1013    Wyoming       Teton   \n",
       "228165          56           39      1013    Wyoming       Teton   \n",
       "\n",
       "            City Name   Latitude   Longitude  Date Local  \\\n",
       "0       Not in a city  31.092100  -87.543500  2020-01-01   \n",
       "1       Not in a city  31.092100  -87.543500  2020-01-02   \n",
       "2       Not in a city  31.092100  -87.543500  2020-01-03   \n",
       "3       Not in a city  31.092100  -87.543500  2020-01-04   \n",
       "4       Not in a city  31.092100  -87.543500  2020-01-05   \n",
       "...               ...        ...         ...         ...   \n",
       "228161  Not in a city  44.373056 -110.830833  2020-12-27   \n",
       "228162  Not in a city  44.373056 -110.830833  2020-12-28   \n",
       "228163  Not in a city  44.373056 -110.830833  2020-12-29   \n",
       "228164  Not in a city  44.373056 -110.830833  2020-12-30   \n",
       "228165  Not in a city  44.373056 -110.830833  2020-12-31   \n",
       "\n",
       "        Mean Temperature (F)  Mean Wind Direction (compass degrees)  \\\n",
       "0                  53.540000                             179.900000   \n",
       "1                  67.342857                             108.857143   \n",
       "2                  26.580000                             271.000000   \n",
       "3                  44.830000                             230.100000   \n",
       "4                  51.550000                             251.100000   \n",
       "...                      ...                                    ...   \n",
       "228161             15.250000                             190.375000   \n",
       "228162             -4.833333                             171.375000   \n",
       "228163             -1.625000                             181.166667   \n",
       "228164              8.291667                             173.666667   \n",
       "228165             18.875000                             157.958333   \n",
       "\n",
       "        Mean Wind Speed (knots)  Elevation      Land Use Location Setting  \n",
       "0                      4.349492      89.61  AGRICULTURAL            RURAL  \n",
       "1                      4.349492      89.61  AGRICULTURAL            RURAL  \n",
       "2                      4.349492      89.61  AGRICULTURAL            RURAL  \n",
       "3                      4.349492      89.61  AGRICULTURAL            RURAL  \n",
       "4                      4.349492      89.61  AGRICULTURAL            RURAL  \n",
       "...                         ...        ...           ...              ...  \n",
       "228161                 1.795833    2230.00        FOREST            RURAL  \n",
       "228162                 1.212500    2230.00        FOREST            RURAL  \n",
       "228163                 1.441667    2230.00        FOREST            RURAL  \n",
       "228164                 1.466667    2230.00        FOREST            RURAL  \n",
       "228165                 1.687500    2230.00        FOREST            RURAL  \n",
       "\n",
       "[228166 rows x 15 columns]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_set"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84b982df",
   "metadata": {},
   "source": [
    "## Adding the observed values for AQI to the features dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "6d43263e",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Extracting the Site Num from Defining Site\n",
    "daily_county_aqi[\"Site Num\"] = daily_county_aqi[\"Defining Site\"].str.extract(pat=f'\\d+-\\d+-(\\d+)').astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "7aad3f50",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Renaming columns to allow for merge\n",
    "daily_county_aqi = daily_county_aqi.rename(columns={\"county Name\": \"County Name\", \"Date\": \"Date Local\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "d7ba134c",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Merging with the feature set\n",
    "final_data_set = feature_set.merge(daily_county_aqi, on=[\"State Code\", \"County Code\", \"Site Num\", \"Date Local\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "8378d4aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Dropping redundant columns and renaming \n",
    "final_data_set = final_data_set.drop(columns=[\"State Name_y\", \"County Name_y\"]).rename(columns={\"State Name_x\": \"State Name\", \"County Name_x\":\"County Name\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "c0e55aed",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date Local</th>\n",
       "      <th>Mean Temperature (F)</th>\n",
       "      <th>Mean Wind Direction (compass degrees)</th>\n",
       "      <th>Mean Wind Speed (knots)</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>Land Use</th>\n",
       "      <th>Location Setting</th>\n",
       "      <th>AQI</th>\n",
       "      <th>Category</th>\n",
       "      <th>Defining Parameter</th>\n",
       "      <th>Defining Site</th>\n",
       "      <th>Number of Sites Reporting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.815000</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>52.125000</td>\n",
       "      <td>137.125000</td>\n",
       "      <td>4.904167</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>56</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.815000</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>65.750000</td>\n",
       "      <td>163.708333</td>\n",
       "      <td>7.583333</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>44</td>\n",
       "      <td>Good</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.815000</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>63.937500</td>\n",
       "      <td>208.916667</td>\n",
       "      <td>3.166667</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>30</td>\n",
       "      <td>Good</td>\n",
       "      <td>NO2</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.815000</td>\n",
       "      <td>2020-01-16</td>\n",
       "      <td>58.012500</td>\n",
       "      <td>75.458333</td>\n",
       "      <td>5.945833</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>31</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.815000</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>54.945833</td>\n",
       "      <td>105.375000</td>\n",
       "      <td>5.491667</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>34</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94814</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-10-05</td>\n",
       "      <td>45.708333</td>\n",
       "      <td>162.666667</td>\n",
       "      <td>2.287500</td>\n",
       "      <td>2230.0</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "      <td>54</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-039-1013</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94815</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>44.083333</td>\n",
       "      <td>174.791667</td>\n",
       "      <td>2.087500</td>\n",
       "      <td>2230.0</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "      <td>85</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-039-1013</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94816</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-10-08</td>\n",
       "      <td>46.375000</td>\n",
       "      <td>169.375000</td>\n",
       "      <td>3.016667</td>\n",
       "      <td>2230.0</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "      <td>69</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-039-1013</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94817</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-10-09</td>\n",
       "      <td>45.041667</td>\n",
       "      <td>168.666667</td>\n",
       "      <td>2.612500</td>\n",
       "      <td>2230.0</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "      <td>55</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-039-1013</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94818</th>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>1013</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Not in a city</td>\n",
       "      <td>44.373056</td>\n",
       "      <td>-110.830833</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>43.875000</td>\n",
       "      <td>191.791667</td>\n",
       "      <td>4.179167</td>\n",
       "      <td>2230.0</td>\n",
       "      <td>FOREST</td>\n",
       "      <td>RURAL</td>\n",
       "      <td>70</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-039-1013</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>94819 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       State Code  County Code  Site Num State Name County Name  \\\n",
       "0               1           73        23    Alabama   Jefferson   \n",
       "1               1           73        23    Alabama   Jefferson   \n",
       "2               1           73        23    Alabama   Jefferson   \n",
       "3               1           73        23    Alabama   Jefferson   \n",
       "4               1           73        23    Alabama   Jefferson   \n",
       "...           ...          ...       ...        ...         ...   \n",
       "94814          56           39      1013    Wyoming       Teton   \n",
       "94815          56           39      1013    Wyoming       Teton   \n",
       "94816          56           39      1013    Wyoming       Teton   \n",
       "94817          56           39      1013    Wyoming       Teton   \n",
       "94818          56           39      1013    Wyoming       Teton   \n",
       "\n",
       "           City Name   Latitude   Longitude  Date Local  Mean Temperature (F)  \\\n",
       "0         Birmingham  33.553056  -86.815000  2020-01-09             52.125000   \n",
       "1         Birmingham  33.553056  -86.815000  2020-01-10             65.750000   \n",
       "2         Birmingham  33.553056  -86.815000  2020-01-14             63.937500   \n",
       "3         Birmingham  33.553056  -86.815000  2020-01-16             58.012500   \n",
       "4         Birmingham  33.553056  -86.815000  2020-01-17             54.945833   \n",
       "...              ...        ...         ...         ...                   ...   \n",
       "94814  Not in a city  44.373056 -110.830833  2020-10-05             45.708333   \n",
       "94815  Not in a city  44.373056 -110.830833  2020-10-07             44.083333   \n",
       "94816  Not in a city  44.373056 -110.830833  2020-10-08             46.375000   \n",
       "94817  Not in a city  44.373056 -110.830833  2020-10-09             45.041667   \n",
       "94818  Not in a city  44.373056 -110.830833  2020-10-10             43.875000   \n",
       "\n",
       "       Mean Wind Direction (compass degrees)  Mean Wind Speed (knots)  \\\n",
       "0                                 137.125000                 4.904167   \n",
       "1                                 163.708333                 7.583333   \n",
       "2                                 208.916667                 3.166667   \n",
       "3                                  75.458333                 5.945833   \n",
       "4                                 105.375000                 5.491667   \n",
       "...                                      ...                      ...   \n",
       "94814                             162.666667                 2.287500   \n",
       "94815                             174.791667                 2.087500   \n",
       "94816                             169.375000                 3.016667   \n",
       "94817                             168.666667                 2.612500   \n",
       "94818                             191.791667                 4.179167   \n",
       "\n",
       "       Elevation    Land Use       Location Setting  AQI  Category  \\\n",
       "0          177.0  COMMERCIAL  URBAN AND CENTER CITY   56  Moderate   \n",
       "1          177.0  COMMERCIAL  URBAN AND CENTER CITY   44      Good   \n",
       "2          177.0  COMMERCIAL  URBAN AND CENTER CITY   30      Good   \n",
       "3          177.0  COMMERCIAL  URBAN AND CENTER CITY   31      Good   \n",
       "4          177.0  COMMERCIAL  URBAN AND CENTER CITY   34      Good   \n",
       "...          ...         ...                    ...  ...       ...   \n",
       "94814     2230.0      FOREST                  RURAL   54  Moderate   \n",
       "94815     2230.0      FOREST                  RURAL   85  Moderate   \n",
       "94816     2230.0      FOREST                  RURAL   69  Moderate   \n",
       "94817     2230.0      FOREST                  RURAL   55  Moderate   \n",
       "94818     2230.0      FOREST                  RURAL   70  Moderate   \n",
       "\n",
       "      Defining Parameter Defining Site  Number of Sites Reporting  \n",
       "0                  PM2.5   01-073-0023                          9  \n",
       "1                  PM2.5   01-073-0023                          9  \n",
       "2                    NO2   01-073-0023                          9  \n",
       "3                  Ozone   01-073-0023                          9  \n",
       "4                  Ozone   01-073-0023                          8  \n",
       "...                  ...           ...                        ...  \n",
       "94814              PM2.5   56-039-1013                          3  \n",
       "94815              PM2.5   56-039-1013                          3  \n",
       "94816              PM2.5   56-039-1013                          3  \n",
       "94817              PM2.5   56-039-1013                          4  \n",
       "94818              PM2.5   56-039-1013                          3  \n",
       "\n",
       "[94819 rows x 20 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data_set"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b422101",
   "metadata": {},
   "source": [
    "# US Emissions data set\n",
    "## Incorporating US GH emissions by state/county as features "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "26dd8c06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['V_GHG_EMITTER_GAS.ADDRESS1', 'V_GHG_EMITTER_GAS.ADDRESS2',\n",
       "       'V_GHG_EMITTER_GAS.CITY', 'V_GHG_EMITTER_GAS.CO2E_EMISSION',\n",
       "       'V_GHG_EMITTER_GAS.COUNTY', 'V_GHG_EMITTER_GAS.FACILITY_ID',\n",
       "       'V_GHG_EMITTER_GAS.GAS_CODE', 'V_GHG_EMITTER_GAS.GAS_NAME',\n",
       "       'V_GHG_EMITTER_GAS.LATITUDE', 'V_GHG_EMITTER_GAS.LONGITUDE',\n",
       "       'V_GHG_EMITTER_GAS.STATE', 'V_GHG_EMITTER_GAS.STATE_NAME',\n",
       "       'V_GHG_EMITTER_GAS.YEAR', 'V_GHG_EMITTER_GAS.ZIP',\n",
       "       'V_GHG_EMITTER_GAS.FACILITY_NAME', 'V_GHG_EMITTER_GAS.COUNTY_FIPS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_greenhouse_gas_emission_direct_emitter_gas_type.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "51a3104b",
   "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>V_GHG_EMITTER_GAS.ADDRESS1</th>\n",
       "      <th>V_GHG_EMITTER_GAS.ADDRESS2</th>\n",
       "      <th>V_GHG_EMITTER_GAS.CITY</th>\n",
       "      <th>V_GHG_EMITTER_GAS.CO2E_EMISSION</th>\n",
       "      <th>V_GHG_EMITTER_GAS.COUNTY</th>\n",
       "      <th>V_GHG_EMITTER_GAS.FACILITY_ID</th>\n",
       "      <th>V_GHG_EMITTER_GAS.GAS_CODE</th>\n",
       "      <th>V_GHG_EMITTER_GAS.GAS_NAME</th>\n",
       "      <th>V_GHG_EMITTER_GAS.LATITUDE</th>\n",
       "      <th>V_GHG_EMITTER_GAS.LONGITUDE</th>\n",
       "      <th>V_GHG_EMITTER_GAS.STATE</th>\n",
       "      <th>V_GHG_EMITTER_GAS.STATE_NAME</th>\n",
       "      <th>V_GHG_EMITTER_GAS.YEAR</th>\n",
       "      <th>V_GHG_EMITTER_GAS.ZIP</th>\n",
       "      <th>V_GHG_EMITTER_GAS.FACILITY_NAME</th>\n",
       "      <th>V_GHG_EMITTER_GAS.COUNTY_FIPS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1919 S. BROADWAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GREEN BAY</td>\n",
       "      <td>58024.000</td>\n",
       "      <td>BROWN COUNTY</td>\n",
       "      <td>1000589</td>\n",
       "      <td>BIOCO2</td>\n",
       "      <td>Biogenic CO2</td>\n",
       "      <td>44.492500</td>\n",
       "      <td>-88.032300</td>\n",
       "      <td>WI</td>\n",
       "      <td>WISCONSIN</td>\n",
       "      <td>2017</td>\n",
       "      <td>54304</td>\n",
       "      <td>GEORGIA-PACIFIC CONSUMER OPERATIONS LLC</td>\n",
       "      <td>55009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>850 12TH AVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>134.500</td>\n",
       "      <td>New York</td>\n",
       "      <td>1000766</td>\n",
       "      <td>CH4</td>\n",
       "      <td>Methane</td>\n",
       "      <td>40.771100</td>\n",
       "      <td>-73.991100</td>\n",
       "      <td>NY</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>2018</td>\n",
       "      <td>10019</td>\n",
       "      <td>59th Street</td>\n",
       "      <td>36061.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3379 HWY 482</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Noble</td>\n",
       "      <td>6.854</td>\n",
       "      <td>SABINE</td>\n",
       "      <td>1009343</td>\n",
       "      <td>N2O</td>\n",
       "      <td>Nitrous Oxide</td>\n",
       "      <td>31.621528</td>\n",
       "      <td>-93.724774</td>\n",
       "      <td>LA</td>\n",
       "      <td>LOUISIANA</td>\n",
       "      <td>2012</td>\n",
       "      <td>71462</td>\n",
       "      <td>Baker Road Treater</td>\n",
       "      <td>22085.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1012 BEAUCHAMP ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GREENVILLE</td>\n",
       "      <td>53562.000</td>\n",
       "      <td>WASHINGTON COUNTY</td>\n",
       "      <td>1003557</td>\n",
       "      <td>CH4</td>\n",
       "      <td>Methane</td>\n",
       "      <td>33.392476</td>\n",
       "      <td>-91.017584</td>\n",
       "      <td>MS</td>\n",
       "      <td>MISSISSIPPI</td>\n",
       "      <td>2017</td>\n",
       "      <td>38701</td>\n",
       "      <td>TEXAS GAS TRANSMISSION - GREENVILLE STATION</td>\n",
       "      <td>28151.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>487 CORN CREEK ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BEDFORD</td>\n",
       "      <td>7635064.700</td>\n",
       "      <td>Trimble</td>\n",
       "      <td>1006542</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>38.584700</td>\n",
       "      <td>-85.411700</td>\n",
       "      <td>KY</td>\n",
       "      <td>KENTUCKY</td>\n",
       "      <td>2018</td>\n",
       "      <td>40006</td>\n",
       "      <td>Trimble County</td>\n",
       "      <td>21223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219923</th>\n",
       "      <td>14353 McFarland Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burlington</td>\n",
       "      <td>1929.800</td>\n",
       "      <td>SKAGIT COUNTY</td>\n",
       "      <td>1010898</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>48.448041</td>\n",
       "      <td>-122.433615</td>\n",
       "      <td>WA</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>2016</td>\n",
       "      <td>98273</td>\n",
       "      <td>Sierra Pacific Industries - Burlington</td>\n",
       "      <td>53057.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219924</th>\n",
       "      <td>7628 Thorndike Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>2776.500</td>\n",
       "      <td>GUILFORD COUNTY</td>\n",
       "      <td>1010033</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>36.075650</td>\n",
       "      <td>-79.965030</td>\n",
       "      <td>NC</td>\n",
       "      <td>NORTH CAROLINA</td>\n",
       "      <td>2018</td>\n",
       "      <td>27409</td>\n",
       "      <td>QORVO</td>\n",
       "      <td>37081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219925</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Calumet</td>\n",
       "      <td>28352.800</td>\n",
       "      <td>CANADIAN COUNTY</td>\n",
       "      <td>1011778</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>35.516370</td>\n",
       "      <td>-98.182280</td>\n",
       "      <td>OK</td>\n",
       "      <td>OKLAHOMA</td>\n",
       "      <td>2014</td>\n",
       "      <td>73014</td>\n",
       "      <td>OFS CS Cedar Lake</td>\n",
       "      <td>40017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219926</th>\n",
       "      <td>1186 Calkins Rd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Troy</td>\n",
       "      <td>23.840</td>\n",
       "      <td>BRADFORD</td>\n",
       "      <td>1010571</td>\n",
       "      <td>N2O</td>\n",
       "      <td>Nitrous Oxide</td>\n",
       "      <td>41.730488</td>\n",
       "      <td>-76.707873</td>\n",
       "      <td>PA</td>\n",
       "      <td>PENNSYLVANIA</td>\n",
       "      <td>2012</td>\n",
       "      <td>16947</td>\n",
       "      <td>Granville Compressor Station</td>\n",
       "      <td>42015.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219927</th>\n",
       "      <td>1186 Calkins Rd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Troy</td>\n",
       "      <td>23.000</td>\n",
       "      <td>BRADFORD COUNTY</td>\n",
       "      <td>1010571</td>\n",
       "      <td>CH4</td>\n",
       "      <td>Methane</td>\n",
       "      <td>41.730488</td>\n",
       "      <td>-76.707873</td>\n",
       "      <td>PA</td>\n",
       "      <td>PENNSYLVANIA</td>\n",
       "      <td>2013</td>\n",
       "      <td>16947</td>\n",
       "      <td>Granville Compressor Station</td>\n",
       "      <td>42015.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>219928 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       V_GHG_EMITTER_GAS.ADDRESS1  V_GHG_EMITTER_GAS.ADDRESS2  \\\n",
       "0                1919 S. BROADWAY                         NaN   \n",
       "1                    850 12TH AVE                         NaN   \n",
       "2                    3379 HWY 482                         NaN   \n",
       "3               1012 BEAUCHAMP ST                         NaN   \n",
       "4             487 CORN CREEK ROAD                         NaN   \n",
       "...                           ...                         ...   \n",
       "219923       14353 McFarland Road                         NaN   \n",
       "219924        7628 Thorndike Road                         NaN   \n",
       "219925                        NaN                         NaN   \n",
       "219926            1186 Calkins Rd                         NaN   \n",
       "219927            1186 Calkins Rd                         NaN   \n",
       "\n",
       "       V_GHG_EMITTER_GAS.CITY  V_GHG_EMITTER_GAS.CO2E_EMISSION  \\\n",
       "0                   GREEN BAY                        58024.000   \n",
       "1                    NEW YORK                          134.500   \n",
       "2                       Noble                            6.854   \n",
       "3                  GREENVILLE                        53562.000   \n",
       "4                     BEDFORD                      7635064.700   \n",
       "...                       ...                              ...   \n",
       "219923             Burlington                         1929.800   \n",
       "219924             Greensboro                         2776.500   \n",
       "219925                Calumet                        28352.800   \n",
       "219926                   Troy                           23.840   \n",
       "219927                   Troy                           23.000   \n",
       "\n",
       "       V_GHG_EMITTER_GAS.COUNTY  V_GHG_EMITTER_GAS.FACILITY_ID  \\\n",
       "0                  BROWN COUNTY                        1000589   \n",
       "1                      New York                        1000766   \n",
       "2                        SABINE                        1009343   \n",
       "3             WASHINGTON COUNTY                        1003557   \n",
       "4                       Trimble                        1006542   \n",
       "...                         ...                            ...   \n",
       "219923            SKAGIT COUNTY                        1010898   \n",
       "219924          GUILFORD COUNTY                        1010033   \n",
       "219925          CANADIAN COUNTY                        1011778   \n",
       "219926                 BRADFORD                        1010571   \n",
       "219927          BRADFORD COUNTY                        1010571   \n",
       "\n",
       "       V_GHG_EMITTER_GAS.GAS_CODE V_GHG_EMITTER_GAS.GAS_NAME  \\\n",
       "0                          BIOCO2               Biogenic CO2   \n",
       "1                             CH4                    Methane   \n",
       "2                             N2O              Nitrous Oxide   \n",
       "3                             CH4                    Methane   \n",
       "4                             CO2             Carbon Dioxide   \n",
       "...                           ...                        ...   \n",
       "219923                        CO2             Carbon Dioxide   \n",
       "219924                        CO2             Carbon Dioxide   \n",
       "219925                        CO2             Carbon Dioxide   \n",
       "219926                        N2O              Nitrous Oxide   \n",
       "219927                        CH4                    Methane   \n",
       "\n",
       "        V_GHG_EMITTER_GAS.LATITUDE  V_GHG_EMITTER_GAS.LONGITUDE  \\\n",
       "0                        44.492500                   -88.032300   \n",
       "1                        40.771100                   -73.991100   \n",
       "2                        31.621528                   -93.724774   \n",
       "3                        33.392476                   -91.017584   \n",
       "4                        38.584700                   -85.411700   \n",
       "...                            ...                          ...   \n",
       "219923                   48.448041                  -122.433615   \n",
       "219924                   36.075650                   -79.965030   \n",
       "219925                   35.516370                   -98.182280   \n",
       "219926                   41.730488                   -76.707873   \n",
       "219927                   41.730488                   -76.707873   \n",
       "\n",
       "       V_GHG_EMITTER_GAS.STATE V_GHG_EMITTER_GAS.STATE_NAME  \\\n",
       "0                           WI                    WISCONSIN   \n",
       "1                           NY                     NEW YORK   \n",
       "2                           LA                    LOUISIANA   \n",
       "3                           MS                  MISSISSIPPI   \n",
       "4                           KY                     KENTUCKY   \n",
       "...                        ...                          ...   \n",
       "219923                      WA                   WASHINGTON   \n",
       "219924                      NC               NORTH CAROLINA   \n",
       "219925                      OK                     OKLAHOMA   \n",
       "219926                      PA                 PENNSYLVANIA   \n",
       "219927                      PA                 PENNSYLVANIA   \n",
       "\n",
       "        V_GHG_EMITTER_GAS.YEAR  V_GHG_EMITTER_GAS.ZIP  \\\n",
       "0                         2017                  54304   \n",
       "1                         2018                  10019   \n",
       "2                         2012                  71462   \n",
       "3                         2017                  38701   \n",
       "4                         2018                  40006   \n",
       "...                        ...                    ...   \n",
       "219923                    2016                  98273   \n",
       "219924                    2018                  27409   \n",
       "219925                    2014                  73014   \n",
       "219926                    2012                  16947   \n",
       "219927                    2013                  16947   \n",
       "\n",
       "                    V_GHG_EMITTER_GAS.FACILITY_NAME  \\\n",
       "0           GEORGIA-PACIFIC CONSUMER OPERATIONS LLC   \n",
       "1                                       59th Street   \n",
       "2                                Baker Road Treater   \n",
       "3       TEXAS GAS TRANSMISSION - GREENVILLE STATION   \n",
       "4                                    Trimble County   \n",
       "...                                             ...   \n",
       "219923       Sierra Pacific Industries - Burlington   \n",
       "219924                                        QORVO   \n",
       "219925                            OFS CS Cedar Lake   \n",
       "219926                 Granville Compressor Station   \n",
       "219927                 Granville Compressor Station   \n",
       "\n",
       "        V_GHG_EMITTER_GAS.COUNTY_FIPS  \n",
       "0                             55009.0  \n",
       "1                             36061.0  \n",
       "2                             22085.0  \n",
       "3                             28151.0  \n",
       "4                             21223.0  \n",
       "...                               ...  \n",
       "219923                        53057.0  \n",
       "219924                        37081.0  \n",
       "219925                        40017.0  \n",
       "219926                        42015.0  \n",
       "219927                        42015.0  \n",
       "\n",
       "[219928 rows x 16 columns]"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_greenhouse_gas_emission_direct_emitter_gas_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "b79b15fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "US_GH_gas_emissions = us_greenhouse_gas_emission_direct_emitter_gas_type[[\"V_GHG_EMITTER_GAS.STATE_NAME\", \"V_GHG_EMITTER_GAS.COUNTY\", \"V_GHG_EMITTER_GAS.CITY\", \"V_GHG_EMITTER_GAS.CO2E_EMISSION\", \"V_GHG_EMITTER_GAS.GAS_CODE\", \"V_GHG_EMITTER_GAS.GAS_NAME\", \"V_GHG_EMITTER_GAS.YEAR\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "e2971fae",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.mode.chained_assignment = None\n",
    "\n",
    "# Renaming columns and making sure the names match with the final_data set we will eventually merge with.\n",
    "US_GH_gas_emissions.columns = [\"State Name\", \"County Name\", \"City Name\", \"CO2 equiv emission\", \"Gas Code\", \"Gas Name\", \"Year\"]\n",
    "US_GH_gas_emissions[\"State Name\"] = US_GH_gas_emissions[\"State Name\"].str.title()\n",
    "US_GH_gas_emissions[\"County Name\"] = US_GH_gas_emissions[\"County Name\"].str.title()\n",
    "US_GH_gas_emissions[\"City Name\"] = US_GH_gas_emissions[\"City Name\"].str.title()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "2d436bde",
   "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 Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>CO2 equiv emission</th>\n",
       "      <th>Gas Code</th>\n",
       "      <th>Gas Name</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>Brown County</td>\n",
       "      <td>Green Bay</td>\n",
       "      <td>58024.000</td>\n",
       "      <td>BIOCO2</td>\n",
       "      <td>Biogenic CO2</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>134.500</td>\n",
       "      <td>CH4</td>\n",
       "      <td>Methane</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>Sabine</td>\n",
       "      <td>Noble</td>\n",
       "      <td>6.854</td>\n",
       "      <td>N2O</td>\n",
       "      <td>Nitrous Oxide</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>Washington County</td>\n",
       "      <td>Greenville</td>\n",
       "      <td>53562.000</td>\n",
       "      <td>CH4</td>\n",
       "      <td>Methane</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Trimble</td>\n",
       "      <td>Bedford</td>\n",
       "      <td>7635064.700</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219923</th>\n",
       "      <td>Washington</td>\n",
       "      <td>Skagit County</td>\n",
       "      <td>Burlington</td>\n",
       "      <td>1929.800</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219924</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Guilford County</td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>2776.500</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219925</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>Canadian County</td>\n",
       "      <td>Calumet</td>\n",
       "      <td>28352.800</td>\n",
       "      <td>CO2</td>\n",
       "      <td>Carbon Dioxide</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219926</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Bradford</td>\n",
       "      <td>Troy</td>\n",
       "      <td>23.840</td>\n",
       "      <td>N2O</td>\n",
       "      <td>Nitrous Oxide</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219927</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Bradford County</td>\n",
       "      <td>Troy</td>\n",
       "      <td>23.000</td>\n",
       "      <td>CH4</td>\n",
       "      <td>Methane</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>219928 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            State Name        County Name   City Name  CO2 equiv emission  \\\n",
       "0            Wisconsin       Brown County   Green Bay           58024.000   \n",
       "1             New York           New York    New York             134.500   \n",
       "2            Louisiana             Sabine       Noble               6.854   \n",
       "3          Mississippi  Washington County  Greenville           53562.000   \n",
       "4             Kentucky            Trimble     Bedford         7635064.700   \n",
       "...                ...                ...         ...                 ...   \n",
       "219923      Washington      Skagit County  Burlington            1929.800   \n",
       "219924  North Carolina    Guilford County  Greensboro            2776.500   \n",
       "219925        Oklahoma    Canadian County     Calumet           28352.800   \n",
       "219926    Pennsylvania           Bradford        Troy              23.840   \n",
       "219927    Pennsylvania    Bradford County        Troy              23.000   \n",
       "\n",
       "       Gas Code        Gas Name  Year  \n",
       "0        BIOCO2    Biogenic CO2  2017  \n",
       "1           CH4         Methane  2018  \n",
       "2           N2O   Nitrous Oxide  2012  \n",
       "3           CH4         Methane  2017  \n",
       "4           CO2  Carbon Dioxide  2018  \n",
       "...         ...             ...   ...  \n",
       "219923      CO2  Carbon Dioxide  2016  \n",
       "219924      CO2  Carbon Dioxide  2018  \n",
       "219925      CO2  Carbon Dioxide  2014  \n",
       "219926      N2O   Nitrous Oxide  2012  \n",
       "219927      CH4         Methane  2013  \n",
       "\n",
       "[219928 rows x 7 columns]"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "US_GH_gas_emissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "93ba72ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "Grouped_US_GH_gas_emissions = US_GH_gas_emissions.groupby([\"State Name\", \"County Name\", \"City Name\", \"Gas Code\"])[\"CO2 equiv emission\"].agg(\"mean\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "2adcf292",
   "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 Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Gas Code</th>\n",
       "      <th>CO2 equiv emission</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>CH4</td>\n",
       "      <td>9.502500e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>CO2</td>\n",
       "      <td>2.049189e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>N2O</td>\n",
       "      <td>1.132698e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Billingsley</td>\n",
       "      <td>CH4</td>\n",
       "      <td>6.356875e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Billingsley</td>\n",
       "      <td>CO2</td>\n",
       "      <td>1.375766e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23017</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Osage</td>\n",
       "      <td>CO2</td>\n",
       "      <td>7.340513e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23018</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Osage</td>\n",
       "      <td>N2O</td>\n",
       "      <td>1.081740e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23019</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>CH4</td>\n",
       "      <td>1.263103e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23020</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>CO2</td>\n",
       "      <td>9.864694e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23021</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>N2O</td>\n",
       "      <td>2.266904e+02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23022 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      State Name    County Name     City Name Gas Code  CO2 equiv emission\n",
       "0        Alabama        Autauga  Autaugaville      CH4        9.502500e+02\n",
       "1        Alabama        Autauga  Autaugaville      CO2        2.049189e+06\n",
       "2        Alabama        Autauga  Autaugaville      N2O        1.132698e+03\n",
       "3        Alabama        Autauga   Billingsley      CH4        6.356875e+02\n",
       "4        Alabama        Autauga   Billingsley      CO2        1.375766e+06\n",
       "...          ...            ...           ...      ...                 ...\n",
       "23017    Wyoming         Weston         Osage      CO2        7.340513e+04\n",
       "23018    Wyoming         Weston         Osage      N2O        1.081740e+03\n",
       "23019    Wyoming  Weston County     Newcastle      CH4        1.263103e+03\n",
       "23020    Wyoming  Weston County     Newcastle      CO2        9.864694e+04\n",
       "23021    Wyoming  Weston County     Newcastle      N2O        2.266904e+02\n",
       "\n",
       "[23022 rows x 5 columns]"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CO2 equiv emissions are the average annual CO2 equiv emissions for the gas.\n",
    "Grouped_US_GH_gas_emissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "0dd2c9d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert to daily GH gas emissions\n",
    "Grouped_US_GH_gas_emissions[\"Average Daily CO2 equiv emission\"] = Grouped_US_GH_gas_emissions[\"CO2 equiv emission\"] / 366"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "fb71b63a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Grouped_US_GH_gas_emissions = Grouped_US_GH_gas_emissions.drop(columns=\"CO2 equiv emission\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "c80a9017",
   "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 Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Gas Code</th>\n",
       "      <th>Average Daily CO2 equiv emission</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>CH4</td>\n",
       "      <td>2.596311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>CO2</td>\n",
       "      <td>5598.875820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>N2O</td>\n",
       "      <td>3.094803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Billingsley</td>\n",
       "      <td>CH4</td>\n",
       "      <td>1.736851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Billingsley</td>\n",
       "      <td>CO2</td>\n",
       "      <td>3758.924495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23017</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Osage</td>\n",
       "      <td>CO2</td>\n",
       "      <td>200.560474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23018</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Osage</td>\n",
       "      <td>N2O</td>\n",
       "      <td>2.955574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23019</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>CH4</td>\n",
       "      <td>3.451101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23020</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>CO2</td>\n",
       "      <td>269.527162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23021</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>N2O</td>\n",
       "      <td>0.619373</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23022 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      State Name    County Name     City Name Gas Code  \\\n",
       "0        Alabama        Autauga  Autaugaville      CH4   \n",
       "1        Alabama        Autauga  Autaugaville      CO2   \n",
       "2        Alabama        Autauga  Autaugaville      N2O   \n",
       "3        Alabama        Autauga   Billingsley      CH4   \n",
       "4        Alabama        Autauga   Billingsley      CO2   \n",
       "...          ...            ...           ...      ...   \n",
       "23017    Wyoming         Weston         Osage      CO2   \n",
       "23018    Wyoming         Weston         Osage      N2O   \n",
       "23019    Wyoming  Weston County     Newcastle      CH4   \n",
       "23020    Wyoming  Weston County     Newcastle      CO2   \n",
       "23021    Wyoming  Weston County     Newcastle      N2O   \n",
       "\n",
       "       Average Daily CO2 equiv emission  \n",
       "0                              2.596311  \n",
       "1                           5598.875820  \n",
       "2                              3.094803  \n",
       "3                              1.736851  \n",
       "4                           3758.924495  \n",
       "...                                 ...  \n",
       "23017                        200.560474  \n",
       "23018                          2.955574  \n",
       "23019                          3.451101  \n",
       "23020                        269.527162  \n",
       "23021                          0.619373  \n",
       "\n",
       "[23022 rows x 5 columns]"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Grouped_US_GH_gas_emissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "id": "d144c664",
   "metadata": {},
   "outputs": [],
   "source": [
    "Pivot_Grouped_US_GH_gas_emissions = Grouped_US_GH_gas_emissions.pivot_table('Average Daily CO2 equiv emission', [\"State Name\", \"County Name\", \"City Name\"], \"Gas Code\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "id": "f5526b23",
   "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>Gas Code</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>BIOCO2</th>\n",
       "      <th>CH4</th>\n",
       "      <th>CO2</th>\n",
       "      <th>HFC</th>\n",
       "      <th>HFE</th>\n",
       "      <th>N2O</th>\n",
       "      <th>NF3</th>\n",
       "      <th>Other</th>\n",
       "      <th>Other_Full</th>\n",
       "      <th>PFC</th>\n",
       "      <th>SF6</th>\n",
       "      <th>Very_Short</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.596311</td>\n",
       "      <td>5598.875820</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.094803</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Billingsley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.736851</td>\n",
       "      <td>3758.924495</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.072728</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Prattville</td>\n",
       "      <td>4362.405137</td>\n",
       "      <td>72.520516</td>\n",
       "      <td>860.282705</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.186514</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Summerdale</td>\n",
       "      <td>NaN</td>\n",
       "      <td>499.213388</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>Eufaula</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.055024</td>\n",
       "      <td>105.047787</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.065589</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7672</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Uinta County</td>\n",
       "      <td>Mountain View</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.354679</td>\n",
       "      <td>36.397404</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.019948</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7673</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>Worland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.038934</td>\n",
       "      <td>66.135656</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.054552</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7674</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie County</td>\n",
       "      <td>Worland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.047558</td>\n",
       "      <td>79.295663</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.067885</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Osage</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.155055</td>\n",
       "      <td>200.560474</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.955574</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7676</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.451101</td>\n",
       "      <td>269.527162</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.619373</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7677 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Gas Code State Name      County Name      City Name       BIOCO2         CH4  \\\n",
       "0           Alabama          Autauga   Autaugaville          NaN    2.596311   \n",
       "1           Alabama          Autauga    Billingsley          NaN    1.736851   \n",
       "2           Alabama          Autauga     Prattville  4362.405137   72.520516   \n",
       "3           Alabama   Baldwin County     Summerdale          NaN  499.213388   \n",
       "4           Alabama   Barbour County        Eufaula          NaN    0.055024   \n",
       "...             ...              ...            ...          ...         ...   \n",
       "7672        Wyoming     Uinta County  Mountain View          NaN    5.354679   \n",
       "7673        Wyoming         Washakie        Worland          NaN    0.038934   \n",
       "7674        Wyoming  Washakie County        Worland          NaN    0.047558   \n",
       "7675        Wyoming           Weston          Osage          NaN    0.155055   \n",
       "7676        Wyoming    Weston County      Newcastle          NaN    3.451101   \n",
       "\n",
       "Gas Code          CO2  HFC  HFE        N2O  NF3  Other  Other_Full  PFC  SF6  \\\n",
       "0         5598.875820  NaN  NaN   3.094803  NaN    NaN         NaN  NaN  NaN   \n",
       "1         3758.924495  NaN  NaN   2.072728  NaN    NaN         NaN  NaN  NaN   \n",
       "2          860.282705  NaN  NaN  31.186514  NaN    NaN         NaN  NaN  NaN   \n",
       "3                 NaN  NaN  NaN        NaN  NaN    NaN         NaN  NaN  NaN   \n",
       "4          105.047787  NaN  NaN   0.065589  NaN    NaN         NaN  NaN  NaN   \n",
       "...               ...  ...  ...        ...  ...    ...         ...  ...  ...   \n",
       "7672        36.397404  NaN  NaN   0.019948  NaN    NaN         NaN  NaN  NaN   \n",
       "7673        66.135656  NaN  NaN   0.054552  NaN    NaN         NaN  NaN  NaN   \n",
       "7674        79.295663  NaN  NaN   0.067885  NaN    NaN         NaN  NaN  NaN   \n",
       "7675       200.560474  NaN  NaN   2.955574  NaN    NaN         NaN  NaN  NaN   \n",
       "7676       269.527162  NaN  NaN   0.619373  NaN    NaN         NaN  NaN  NaN   \n",
       "\n",
       "Gas Code  Very_Short  \n",
       "0                NaN  \n",
       "1                NaN  \n",
       "2                NaN  \n",
       "3                NaN  \n",
       "4                NaN  \n",
       "...              ...  \n",
       "7672             NaN  \n",
       "7673             NaN  \n",
       "7674             NaN  \n",
       "7675             NaN  \n",
       "7676             NaN  \n",
       "\n",
       "[7677 rows x 15 columns]"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Pivot_Grouped_US_GH_gas_emissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "id": "4032c49e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resetting index\n",
    "Pivot_Grouped_US_GH_gas_emissions = Pivot_Grouped_US_GH_gas_emissions.rename_axis(None, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "id": "d4e3f592",
   "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 Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>BIOCO2</th>\n",
       "      <th>CH4</th>\n",
       "      <th>CO2</th>\n",
       "      <th>HFC</th>\n",
       "      <th>HFE</th>\n",
       "      <th>N2O</th>\n",
       "      <th>NF3</th>\n",
       "      <th>Other</th>\n",
       "      <th>Other_Full</th>\n",
       "      <th>PFC</th>\n",
       "      <th>SF6</th>\n",
       "      <th>Very_Short</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.596311</td>\n",
       "      <td>5598.875820</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.094803</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Billingsley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.736851</td>\n",
       "      <td>3758.924495</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.072728</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Prattville</td>\n",
       "      <td>4362.405137</td>\n",
       "      <td>72.520516</td>\n",
       "      <td>860.282705</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.186514</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Summerdale</td>\n",
       "      <td>NaN</td>\n",
       "      <td>499.213388</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>Eufaula</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.055024</td>\n",
       "      <td>105.047787</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.065589</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7672</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Uinta County</td>\n",
       "      <td>Mountain View</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.354679</td>\n",
       "      <td>36.397404</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.019948</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7673</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>Worland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.038934</td>\n",
       "      <td>66.135656</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.054552</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7674</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie County</td>\n",
       "      <td>Worland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.047558</td>\n",
       "      <td>79.295663</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.067885</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Osage</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.155055</td>\n",
       "      <td>200.560474</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.955574</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7676</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.451101</td>\n",
       "      <td>269.527162</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.619373</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7677 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     State Name      County Name      City Name       BIOCO2         CH4  \\\n",
       "0       Alabama          Autauga   Autaugaville          NaN    2.596311   \n",
       "1       Alabama          Autauga    Billingsley          NaN    1.736851   \n",
       "2       Alabama          Autauga     Prattville  4362.405137   72.520516   \n",
       "3       Alabama   Baldwin County     Summerdale          NaN  499.213388   \n",
       "4       Alabama   Barbour County        Eufaula          NaN    0.055024   \n",
       "...         ...              ...            ...          ...         ...   \n",
       "7672    Wyoming     Uinta County  Mountain View          NaN    5.354679   \n",
       "7673    Wyoming         Washakie        Worland          NaN    0.038934   \n",
       "7674    Wyoming  Washakie County        Worland          NaN    0.047558   \n",
       "7675    Wyoming           Weston          Osage          NaN    0.155055   \n",
       "7676    Wyoming    Weston County      Newcastle          NaN    3.451101   \n",
       "\n",
       "              CO2  HFC  HFE        N2O  NF3  Other  Other_Full  PFC  SF6  \\\n",
       "0     5598.875820  NaN  NaN   3.094803  NaN    NaN         NaN  NaN  NaN   \n",
       "1     3758.924495  NaN  NaN   2.072728  NaN    NaN         NaN  NaN  NaN   \n",
       "2      860.282705  NaN  NaN  31.186514  NaN    NaN         NaN  NaN  NaN   \n",
       "3             NaN  NaN  NaN        NaN  NaN    NaN         NaN  NaN  NaN   \n",
       "4      105.047787  NaN  NaN   0.065589  NaN    NaN         NaN  NaN  NaN   \n",
       "...           ...  ...  ...        ...  ...    ...         ...  ...  ...   \n",
       "7672    36.397404  NaN  NaN   0.019948  NaN    NaN         NaN  NaN  NaN   \n",
       "7673    66.135656  NaN  NaN   0.054552  NaN    NaN         NaN  NaN  NaN   \n",
       "7674    79.295663  NaN  NaN   0.067885  NaN    NaN         NaN  NaN  NaN   \n",
       "7675   200.560474  NaN  NaN   2.955574  NaN    NaN         NaN  NaN  NaN   \n",
       "7676   269.527162  NaN  NaN   0.619373  NaN    NaN         NaN  NaN  NaN   \n",
       "\n",
       "      Very_Short  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "...          ...  \n",
       "7672         NaN  \n",
       "7673         NaN  \n",
       "7674         NaN  \n",
       "7675         NaN  \n",
       "7676         NaN  \n",
       "\n",
       "[7677 rows x 15 columns]"
      ]
     },
     "execution_count": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Pivot_Grouped_US_GH_gas_emissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "id": "a14afb48",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State Name     7677\n",
       "County Name    7677\n",
       "City Name      7677\n",
       "BIOCO2         1360\n",
       "CH4            7599\n",
       "CO2            6908\n",
       "HFC              91\n",
       "HFE              36\n",
       "N2O            6507\n",
       "NF3              63\n",
       "Other            17\n",
       "Other_Full       69\n",
       "PFC              96\n",
       "SF6             226\n",
       "Very_Short       50\n",
       "dtype: int64"
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of non null entires (to see which gasses to add to the data set)\n",
    "Pivot_Grouped_US_GH_gas_emissions.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "726bee3b",
   "metadata": {},
   "source": [
    "## Based on the counts of non null entries, we have a reasonable amount of data for BIOCO2, CH4, CO2, and N2O. So we will try to use these as features. We can revist this if need be."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "id": "8b24a366",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Truncating data to the gasses we want and filling the NaN with 0s\n",
    "Truncated_US_GH_gas_emissions = Pivot_Grouped_US_GH_gas_emissions[[\"State Name\", \"County Name\", \"City Name\", \"BIOCO2\", \"CH4\", \"CO2\", \"N2O\"]].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "2af0294c",
   "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 Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>BIOCO2</th>\n",
       "      <th>CH4</th>\n",
       "      <th>CO2</th>\n",
       "      <th>N2O</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autaugaville</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.596311</td>\n",
       "      <td>5598.875820</td>\n",
       "      <td>3.094803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Billingsley</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.736851</td>\n",
       "      <td>3758.924495</td>\n",
       "      <td>2.072728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Prattville</td>\n",
       "      <td>4362.405137</td>\n",
       "      <td>72.520516</td>\n",
       "      <td>860.282705</td>\n",
       "      <td>31.186514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Summerdale</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>499.213388</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>Eufaula</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.055024</td>\n",
       "      <td>105.047787</td>\n",
       "      <td>0.065589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7672</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Uinta County</td>\n",
       "      <td>Mountain View</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.354679</td>\n",
       "      <td>36.397404</td>\n",
       "      <td>0.019948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7673</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>Worland</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.038934</td>\n",
       "      <td>66.135656</td>\n",
       "      <td>0.054552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7674</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie County</td>\n",
       "      <td>Worland</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.047558</td>\n",
       "      <td>79.295663</td>\n",
       "      <td>0.067885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Osage</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.155055</td>\n",
       "      <td>200.560474</td>\n",
       "      <td>2.955574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7676</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.451101</td>\n",
       "      <td>269.527162</td>\n",
       "      <td>0.619373</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7677 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     State Name      County Name      City Name       BIOCO2         CH4  \\\n",
       "0       Alabama          Autauga   Autaugaville     0.000000    2.596311   \n",
       "1       Alabama          Autauga    Billingsley     0.000000    1.736851   \n",
       "2       Alabama          Autauga     Prattville  4362.405137   72.520516   \n",
       "3       Alabama   Baldwin County     Summerdale     0.000000  499.213388   \n",
       "4       Alabama   Barbour County        Eufaula     0.000000    0.055024   \n",
       "...         ...              ...            ...          ...         ...   \n",
       "7672    Wyoming     Uinta County  Mountain View     0.000000    5.354679   \n",
       "7673    Wyoming         Washakie        Worland     0.000000    0.038934   \n",
       "7674    Wyoming  Washakie County        Worland     0.000000    0.047558   \n",
       "7675    Wyoming           Weston          Osage     0.000000    0.155055   \n",
       "7676    Wyoming    Weston County      Newcastle     0.000000    3.451101   \n",
       "\n",
       "              CO2        N2O  \n",
       "0     5598.875820   3.094803  \n",
       "1     3758.924495   2.072728  \n",
       "2      860.282705  31.186514  \n",
       "3        0.000000   0.000000  \n",
       "4      105.047787   0.065589  \n",
       "...           ...        ...  \n",
       "7672    36.397404   0.019948  \n",
       "7673    66.135656   0.054552  \n",
       "7674    79.295663   0.067885  \n",
       "7675   200.560474   2.955574  \n",
       "7676   269.527162   0.619373  \n",
       "\n",
       "[7677 rows x 7 columns]"
      ]
     },
     "execution_count": 273,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Truncated_US_GH_gas_emissions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91b1aa71",
   "metadata": {},
   "source": [
    "# Merging Gas data with our Final Features Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "id": "ccb8f013",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Merging with the final data set\n",
    "complete_data_set = final_data_set.merge(Truncated_US_GH_gas_emissions, on=[\"State Name\", \"County Name\", \"City Name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "id": "8a51f359",
   "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 Num</th>\n",
       "      <th>State Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>City Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date Local</th>\n",
       "      <th>Mean Temperature (F)</th>\n",
       "      <th>Mean Wind Direction (compass degrees)</th>\n",
       "      <th>Mean Wind Speed (knots)</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>Land Use</th>\n",
       "      <th>Location Setting</th>\n",
       "      <th>AQI</th>\n",
       "      <th>Category</th>\n",
       "      <th>Defining Parameter</th>\n",
       "      <th>Defining Site</th>\n",
       "      <th>Number of Sites Reporting</th>\n",
       "      <th>BIOCO2</th>\n",
       "      <th>CH4</th>\n",
       "      <th>CO2</th>\n",
       "      <th>N2O</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.8150</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>52.125000</td>\n",
       "      <td>137.125000</td>\n",
       "      <td>4.904167</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>56</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>194.591845</td>\n",
       "      <td>145.217054</td>\n",
       "      <td>0.555493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.8150</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>65.750000</td>\n",
       "      <td>163.708333</td>\n",
       "      <td>7.583333</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>44</td>\n",
       "      <td>Good</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>194.591845</td>\n",
       "      <td>145.217054</td>\n",
       "      <td>0.555493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.8150</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>63.937500</td>\n",
       "      <td>208.916667</td>\n",
       "      <td>3.166667</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>30</td>\n",
       "      <td>Good</td>\n",
       "      <td>NO2</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>194.591845</td>\n",
       "      <td>145.217054</td>\n",
       "      <td>0.555493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.8150</td>\n",
       "      <td>2020-01-16</td>\n",
       "      <td>58.012500</td>\n",
       "      <td>75.458333</td>\n",
       "      <td>5.945833</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>31</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>194.591845</td>\n",
       "      <td>145.217054</td>\n",
       "      <td>0.555493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>23</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>33.553056</td>\n",
       "      <td>-86.8150</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>54.945833</td>\n",
       "      <td>105.375000</td>\n",
       "      <td>5.491667</td>\n",
       "      <td>177.0</td>\n",
       "      <td>COMMERCIAL</td>\n",
       "      <td>URBAN AND CENTER CITY</td>\n",
       "      <td>34</td>\n",
       "      <td>Good</td>\n",
       "      <td>Ozone</td>\n",
       "      <td>01-073-0023</td>\n",
       "      <td>8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>194.591845</td>\n",
       "      <td>145.217054</td>\n",
       "      <td>0.555493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30140</th>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Fremont</td>\n",
       "      <td>Riverton</td>\n",
       "      <td>43.024210</td>\n",
       "      <td>-108.3637</td>\n",
       "      <td>2020-09-18</td>\n",
       "      <td>59.291667</td>\n",
       "      <td>195.166667</td>\n",
       "      <td>2.687500</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>RESIDENTIAL</td>\n",
       "      <td>SUBURBAN</td>\n",
       "      <td>106</td>\n",
       "      <td>Unhealthy for Sensitive Groups</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-013-0004</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.998634</td>\n",
       "      <td>167.363388</td>\n",
       "      <td>0.091191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30141</th>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Fremont</td>\n",
       "      <td>Riverton</td>\n",
       "      <td>43.024210</td>\n",
       "      <td>-108.3637</td>\n",
       "      <td>2020-09-19</td>\n",
       "      <td>61.708333</td>\n",
       "      <td>215.166667</td>\n",
       "      <td>4.508333</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>RESIDENTIAL</td>\n",
       "      <td>SUBURBAN</td>\n",
       "      <td>60</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-013-0004</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.998634</td>\n",
       "      <td>167.363388</td>\n",
       "      <td>0.091191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30142</th>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Fremont</td>\n",
       "      <td>Riverton</td>\n",
       "      <td>43.024210</td>\n",
       "      <td>-108.3637</td>\n",
       "      <td>2020-10-05</td>\n",
       "      <td>56.416667</td>\n",
       "      <td>224.500000</td>\n",
       "      <td>3.720833</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>RESIDENTIAL</td>\n",
       "      <td>SUBURBAN</td>\n",
       "      <td>54</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM2.5</td>\n",
       "      <td>56-013-0004</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.998634</td>\n",
       "      <td>167.363388</td>\n",
       "      <td>0.091191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30143</th>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Fremont</td>\n",
       "      <td>Riverton</td>\n",
       "      <td>43.024210</td>\n",
       "      <td>-108.3637</td>\n",
       "      <td>2020-10-11</td>\n",
       "      <td>50.333333</td>\n",
       "      <td>258.958333</td>\n",
       "      <td>10.012500</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>RESIDENTIAL</td>\n",
       "      <td>SUBURBAN</td>\n",
       "      <td>52</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM10</td>\n",
       "      <td>56-013-0004</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.998634</td>\n",
       "      <td>167.363388</td>\n",
       "      <td>0.091191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30144</th>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Fremont</td>\n",
       "      <td>Riverton</td>\n",
       "      <td>43.024210</td>\n",
       "      <td>-108.3637</td>\n",
       "      <td>2020-11-07</td>\n",
       "      <td>54.500000</td>\n",
       "      <td>184.708333</td>\n",
       "      <td>11.091667</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>RESIDENTIAL</td>\n",
       "      <td>SUBURBAN</td>\n",
       "      <td>52</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>PM10</td>\n",
       "      <td>56-013-0004</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.998634</td>\n",
       "      <td>167.363388</td>\n",
       "      <td>0.091191</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30145 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       State Code  County Code  Site Num State Name County Name   City Name  \\\n",
       "0               1           73        23    Alabama   Jefferson  Birmingham   \n",
       "1               1           73        23    Alabama   Jefferson  Birmingham   \n",
       "2               1           73        23    Alabama   Jefferson  Birmingham   \n",
       "3               1           73        23    Alabama   Jefferson  Birmingham   \n",
       "4               1           73        23    Alabama   Jefferson  Birmingham   \n",
       "...           ...          ...       ...        ...         ...         ...   \n",
       "30140          56           13         4    Wyoming     Fremont    Riverton   \n",
       "30141          56           13         4    Wyoming     Fremont    Riverton   \n",
       "30142          56           13         4    Wyoming     Fremont    Riverton   \n",
       "30143          56           13         4    Wyoming     Fremont    Riverton   \n",
       "30144          56           13         4    Wyoming     Fremont    Riverton   \n",
       "\n",
       "        Latitude  Longitude  Date Local  Mean Temperature (F)  \\\n",
       "0      33.553056   -86.8150  2020-01-09             52.125000   \n",
       "1      33.553056   -86.8150  2020-01-10             65.750000   \n",
       "2      33.553056   -86.8150  2020-01-14             63.937500   \n",
       "3      33.553056   -86.8150  2020-01-16             58.012500   \n",
       "4      33.553056   -86.8150  2020-01-17             54.945833   \n",
       "...          ...        ...         ...                   ...   \n",
       "30140  43.024210  -108.3637  2020-09-18             59.291667   \n",
       "30141  43.024210  -108.3637  2020-09-19             61.708333   \n",
       "30142  43.024210  -108.3637  2020-10-05             56.416667   \n",
       "30143  43.024210  -108.3637  2020-10-11             50.333333   \n",
       "30144  43.024210  -108.3637  2020-11-07             54.500000   \n",
       "\n",
       "       Mean Wind Direction (compass degrees)  Mean Wind Speed (knots)  \\\n",
       "0                                 137.125000                 4.904167   \n",
       "1                                 163.708333                 7.583333   \n",
       "2                                 208.916667                 3.166667   \n",
       "3                                  75.458333                 5.945833   \n",
       "4                                 105.375000                 5.491667   \n",
       "...                                      ...                      ...   \n",
       "30140                             195.166667                 2.687500   \n",
       "30141                             215.166667                 4.508333   \n",
       "30142                             224.500000                 3.720833   \n",
       "30143                             258.958333                10.012500   \n",
       "30144                             184.708333                11.091667   \n",
       "\n",
       "       Elevation     Land Use       Location Setting  AQI  \\\n",
       "0          177.0   COMMERCIAL  URBAN AND CENTER CITY   56   \n",
       "1          177.0   COMMERCIAL  URBAN AND CENTER CITY   44   \n",
       "2          177.0   COMMERCIAL  URBAN AND CENTER CITY   30   \n",
       "3          177.0   COMMERCIAL  URBAN AND CENTER CITY   31   \n",
       "4          177.0   COMMERCIAL  URBAN AND CENTER CITY   34   \n",
       "...          ...          ...                    ...  ...   \n",
       "30140     1500.0  RESIDENTIAL               SUBURBAN  106   \n",
       "30141     1500.0  RESIDENTIAL               SUBURBAN   60   \n",
       "30142     1500.0  RESIDENTIAL               SUBURBAN   54   \n",
       "30143     1500.0  RESIDENTIAL               SUBURBAN   52   \n",
       "30144     1500.0  RESIDENTIAL               SUBURBAN   52   \n",
       "\n",
       "                             Category Defining Parameter Defining Site  \\\n",
       "0                            Moderate              PM2.5   01-073-0023   \n",
       "1                                Good              PM2.5   01-073-0023   \n",
       "2                                Good                NO2   01-073-0023   \n",
       "3                                Good              Ozone   01-073-0023   \n",
       "4                                Good              Ozone   01-073-0023   \n",
       "...                               ...                ...           ...   \n",
       "30140  Unhealthy for Sensitive Groups              PM2.5   56-013-0004   \n",
       "30141                        Moderate              PM2.5   56-013-0004   \n",
       "30142                        Moderate              PM2.5   56-013-0004   \n",
       "30143                        Moderate               PM10   56-013-0004   \n",
       "30144                        Moderate               PM10   56-013-0004   \n",
       "\n",
       "       Number of Sites Reporting  BIOCO2         CH4         CO2       N2O  \n",
       "0                              9     0.0  194.591845  145.217054  0.555493  \n",
       "1                              9     0.0  194.591845  145.217054  0.555493  \n",
       "2                              9     0.0  194.591845  145.217054  0.555493  \n",
       "3                              9     0.0  194.591845  145.217054  0.555493  \n",
       "4                              8     0.0  194.591845  145.217054  0.555493  \n",
       "...                          ...     ...         ...         ...       ...  \n",
       "30140                          5     0.0    9.998634  167.363388  0.091191  \n",
       "30141                          4     0.0    9.998634  167.363388  0.091191  \n",
       "30142                          4     0.0    9.998634  167.363388  0.091191  \n",
       "30143                          4     0.0    9.998634  167.363388  0.091191  \n",
       "30144                          4     0.0    9.998634  167.363388  0.091191  \n",
       "\n",
       "[30145 rows x 24 columns]"
      ]
     },
     "execution_count": 284,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_data_set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "id": "32dd376c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([30145, 30145, 30145, 30145, 30145, 30145, 30145, 30145, 30145,\n",
       "       30145, 30145, 30145, 28016, 30145, 30145, 28558, 30145, 30145,\n",
       "       30145, 30145,  7076, 29787, 28599, 28421])"
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Non-zero count for each column\n",
    "np.count_nonzero(complete_data_set, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7947b935",
   "metadata": {},
   "source": [
    "## For this final aggregated dataset the BIOCO2, CH4, CO2, and N2O average daily emissions non-zero counts are 7076, 29787, 28599, and 28421, respectively. These values seem reasonable but if the lower values for BIOCO2 cause issues then we can remove that feature. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dff0e6b7",
   "metadata": {},
   "source": [
    "## From this EDA our final dataset is complete_data_set, and has the following features: State Name, County Name, City Name, Mean Temperature (F), Mean Wind Direction (compass degrees), Mean Wind Speed (knots), Elevation, Land Use, Location Setting, BIOCO2, CH4, CO2, and N2O. The dataset also contains the AQI which is what we will be trying to predict, along with some other columns which can be considered meta data.\n",
    "\n",
    "## If time permits precipitation from the daily_global_weather_2020 should be added."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84f7a2c5",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}