01_data_merge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import requests\n",
    "import time\n",
    "from time import sleep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>id</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>name</th>\n",
       "      <th>location_type</th>\n",
       "      <th>pm_2.5</th>\n",
       "      <th>temp_f</th>\n",
       "      <th>temp_c</th>\n",
       "      <th>humidity</th>\n",
       "      <th>pressure</th>\n",
       "      <th>...</th>\n",
       "      <th>flagged</th>\n",
       "      <th>downgraded</th>\n",
       "      <th>age</th>\n",
       "      <th>is_owner</th>\n",
       "      <th>10min_avg</th>\n",
       "      <th>30min_avg</th>\n",
       "      <th>1hour_avg</th>\n",
       "      <th>6hour_avg</th>\n",
       "      <th>1day_avg</th>\n",
       "      <th>1week_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25999</td>\n",
       "      <td>30.053808</td>\n",
       "      <td>-95.494643</td>\n",
       "      <td>Villages of Bridgestone AQI</td>\n",
       "      <td>outside</td>\n",
       "      <td>1.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>1022.37</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>2.47</td>\n",
       "      <td>4.19</td>\n",
       "      <td>8.1</td>\n",
       "      <td>19.66</td>\n",
       "      <td>17.15</td>\n",
       "      <td>15.22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id        lat        lon                          name location_type  \\\n",
       "0  25999  30.053808 -95.494643   Villages of Bridgestone AQI       outside   \n",
       "\n",
       "   pm_2.5  temp_f  temp_c  humidity  pressure  ... flagged downgraded  age  \\\n",
       "0     1.0    50.0    10.0      62.0   1022.37  ...   False      False    0   \n",
       "\n",
       "   is_owner  10min_avg  30min_avg  1hour_avg  6hour_avg  1day_avg  1week_avg  \n",
       "0     False       2.47       4.19        8.1      19.66     17.15      15.22  \n",
       "\n",
       "[1 rows x 23 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "air_df = pd.read_csv('./data/original_data/purpleair_original.csv')\n",
    "air_df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to pull 2010 Census FIPs code from latitude & longitude \n",
    "\n",
    "def lat_lon_query(lat, lon, cen_yr):\n",
    "\n",
    "    url_fips = f'https://geo.fcc.gov/api/census/block/find?latitude={lat}&longitude={lon}&censusYear={cen_yr}&showall=true&format=json'\n",
    "    response = requests.get(url_fips)\n",
    "\n",
    "    if response.status_code == 200:\n",
    "        aq_fips = response.json()\n",
    "        aq_fips_code = aq_fips['Block']['FIPS']\n",
    "        # print(aq_fips_code)\n",
    "    else:\n",
    "        print('Error!')\n",
    "\n",
    "    return aq_fips_code\n",
    "\n",
    "# API: https://geo.fcc.gov/api/census/#!/block/get_block_find"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Warning:** Long run time (1 hour +) for cell below  \n",
    "\n",
    "Output saved as CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pulling FIPs codes for the observations in our purpleair data set, using the above function\n",
    "\n",
    "fips = air_df.apply(lambda row: lat_lon_query(row['lat'], row['lon'], 2010), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving FIPs codes to new column in a dataframe\n",
    "air_df['fips_id'] = fips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving the updated dataset with the fips_id column to a new CSV\n",
    "air_df.to_csv('./data/original_data/purpleair_fips.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 14163 entries, 0 to 14162\n",
      "Data columns (total 24 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   id             14163 non-null  int64  \n",
      " 1   lat            14163 non-null  float64\n",
      " 2   lon            14163 non-null  float64\n",
      " 3   name           14163 non-null  object \n",
      " 4   location_type  14135 non-null  object \n",
      " 5   pm_2.5         14163 non-null  float64\n",
      " 6   temp_f         14163 non-null  float64\n",
      " 7   temp_c         14163 non-null  float64\n",
      " 8   humidity       14163 non-null  float64\n",
      " 9   pressure       14163 non-null  float64\n",
      " 10  last_seen      14163 non-null  object \n",
      " 11  model          14163 non-null  object \n",
      " 12  hidden         14163 non-null  bool   \n",
      " 13  flagged        14163 non-null  bool   \n",
      " 14  downgraded     14163 non-null  bool   \n",
      " 15  age            14163 non-null  int64  \n",
      " 16  is_owner       14163 non-null  bool   \n",
      " 17  10min_avg      14163 non-null  float64\n",
      " 18  30min_avg      14163 non-null  float64\n",
      " 19  1hour_avg      14163 non-null  float64\n",
      " 20  6hour_avg      14163 non-null  float64\n",
      " 21  1day_avg       14163 non-null  float64\n",
      " 22  1week_avg      14163 non-null  float64\n",
      " 23  fips_id        13791 non-null  float64\n",
      "dtypes: bool(4), float64(14), int64(2), object(4)\n",
      "memory usage: 2.3+ MB\n"
     ]
    }
   ],
   "source": [
    "# Pulling the new CSV into a new dataframe\n",
    "pa_fips_df = pd.read_csv('./data/original_data/purpleair_fips.csv', index_col = 0)\n",
    "pa_fips_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop null fips_ids\n",
    "pa_fips_df.dropna(subset=['fips_id'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    482015549031070\n",
       "1    530530703082014\n",
       "2    530530703082005\n",
       "3    421298086003016\n",
       "4     60190050003006\n",
       "Name: fips_id, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pa_fips_df['fips_id'].astype(np.int64)[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    482015549031\n",
       "1    530530703082\n",
       "2    530530703082\n",
       "3    421298086003\n",
       "4    060190050003\n",
       "Name: fips_id, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FIPs ID from float to int to string to fill to 15 numbers to first 12 numbers\n",
    "pa_fips_df['fips_id'] = pa_fips_df['fips_id'].astype(np.int64).astype(str).str.zfill(15).apply(lambda num: num[:12])\n",
    "pa_fips_df['fips_id'][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>id</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>name</th>\n",
       "      <th>location_type</th>\n",
       "      <th>pm_2.5</th>\n",
       "      <th>temp_f</th>\n",
       "      <th>temp_c</th>\n",
       "      <th>humidity</th>\n",
       "      <th>pressure</th>\n",
       "      <th>...</th>\n",
       "      <th>downgraded</th>\n",
       "      <th>age</th>\n",
       "      <th>is_owner</th>\n",
       "      <th>10min_avg</th>\n",
       "      <th>30min_avg</th>\n",
       "      <th>1hour_avg</th>\n",
       "      <th>6hour_avg</th>\n",
       "      <th>1day_avg</th>\n",
       "      <th>1week_avg</th>\n",
       "      <th>fips_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25999</td>\n",
       "      <td>30.053808</td>\n",
       "      <td>-95.494643</td>\n",
       "      <td>Villages of Bridgestone AQI</td>\n",
       "      <td>outside</td>\n",
       "      <td>1.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>1022.37</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>2.47</td>\n",
       "      <td>4.19</td>\n",
       "      <td>8.1</td>\n",
       "      <td>19.66</td>\n",
       "      <td>17.15</td>\n",
       "      <td>15.22</td>\n",
       "      <td>482015549031</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id        lat        lon                          name location_type  \\\n",
       "0  25999  30.053808 -95.494643   Villages of Bridgestone AQI       outside   \n",
       "\n",
       "   pm_2.5  temp_f  temp_c  humidity  pressure  ... downgraded age  is_owner  \\\n",
       "0     1.0    50.0    10.0      62.0   1022.37  ...      False   0     False   \n",
       "\n",
       "   10min_avg  30min_avg  1hour_avg  6hour_avg  1day_avg  1week_avg  \\\n",
       "0       2.47       4.19        8.1      19.66     17.15      15.22   \n",
       "\n",
       "        fips_id  \n",
       "0  482015549031  \n",
       "\n",
       "[1 rows x 24 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pa_fips_df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All locations  \n",
    "Warning - will take ~1 minute to load the dataset below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>OBJECTID</th>\n",
       "      <th>GEOID10</th>\n",
       "      <th>GEOID20</th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>TRACTCE</th>\n",
       "      <th>BLKGRPCE</th>\n",
       "      <th>CSA</th>\n",
       "      <th>CSA_Name</th>\n",
       "      <th>CBSA</th>\n",
       "      <th>...</th>\n",
       "      <th>D5DRI</th>\n",
       "      <th>D5DE</th>\n",
       "      <th>D5DEI</th>\n",
       "      <th>D2A_Ranked</th>\n",
       "      <th>D2B_Ranked</th>\n",
       "      <th>D3B_Ranked</th>\n",
       "      <th>D4A_Ranked</th>\n",
       "      <th>NatWalkInd</th>\n",
       "      <th>Shape_Length</th>\n",
       "      <th>Shape_Area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>48</td>\n",
       "      <td>113</td>\n",
       "      <td>7825</td>\n",
       "      <td>4</td>\n",
       "      <td>206.0</td>\n",
       "      <td>Dallas-Fort Worth, TX-OK</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.184697</td>\n",
       "      <td>0.000476</td>\n",
       "      <td>0.137707</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>3110.360820</td>\n",
       "      <td>297836.0831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>48</td>\n",
       "      <td>113</td>\n",
       "      <td>7825</td>\n",
       "      <td>2</td>\n",
       "      <td>206.0</td>\n",
       "      <td>Dallas-Fort Worth, TX-OK</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.323221</td>\n",
       "      <td>0.000801</td>\n",
       "      <td>0.231868</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>10.833333</td>\n",
       "      <td>3519.469110</td>\n",
       "      <td>484945.1466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>48</td>\n",
       "      <td>113</td>\n",
       "      <td>7825</td>\n",
       "      <td>3</td>\n",
       "      <td>206.0</td>\n",
       "      <td>Dallas-Fort Worth, TX-OK</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.314628</td>\n",
       "      <td>0.000736</td>\n",
       "      <td>0.213146</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>8.333333</td>\n",
       "      <td>1697.091802</td>\n",
       "      <td>106705.9281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>48</td>\n",
       "      <td>113</td>\n",
       "      <td>7824</td>\n",
       "      <td>1</td>\n",
       "      <td>206.0</td>\n",
       "      <td>Dallas-Fort Worth, TX-OK</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.229821</td>\n",
       "      <td>0.000708</td>\n",
       "      <td>0.205018</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>15.666667</td>\n",
       "      <td>2922.609204</td>\n",
       "      <td>481828.4303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>48</td>\n",
       "      <td>113</td>\n",
       "      <td>7824</td>\n",
       "      <td>2</td>\n",
       "      <td>206.0</td>\n",
       "      <td>Dallas-Fort Worth, TX-OK</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.164863</td>\n",
       "      <td>0.000433</td>\n",
       "      <td>0.125296</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>10.166667</td>\n",
       "      <td>3731.971773</td>\n",
       "      <td>687684.7752</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 117 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID       GEOID10       GEOID20  STATEFP  COUNTYFP  TRACTCE  BLKGRPCE  \\\n",
       "0         1  4.811300e+11  4.811300e+11       48       113     7825         4   \n",
       "1         2  4.811300e+11  4.811300e+11       48       113     7825         2   \n",
       "2         3  4.811300e+11  4.811300e+11       48       113     7825         3   \n",
       "3         4  4.811300e+11  4.811300e+11       48       113     7824         1   \n",
       "4         5  4.811300e+11  4.811300e+11       48       113     7824         2   \n",
       "\n",
       "     CSA                  CSA_Name     CBSA  ...     D5DRI      D5DE  \\\n",
       "0  206.0  Dallas-Fort Worth, TX-OK  19100.0  ...  0.184697  0.000476   \n",
       "1  206.0  Dallas-Fort Worth, TX-OK  19100.0  ...  0.323221  0.000801   \n",
       "2  206.0  Dallas-Fort Worth, TX-OK  19100.0  ...  0.314628  0.000736   \n",
       "3  206.0  Dallas-Fort Worth, TX-OK  19100.0  ...  0.229821  0.000708   \n",
       "4  206.0  Dallas-Fort Worth, TX-OK  19100.0  ...  0.164863  0.000433   \n",
       "\n",
       "      D5DEI  D2A_Ranked  D2B_Ranked  D3B_Ranked  D4A_Ranked  NatWalkInd  \\\n",
       "0  0.137707           6          14          15          17   14.000000   \n",
       "1  0.231868           3          10          12          14   10.833333   \n",
       "2  0.213146           1           1           7          17    8.333333   \n",
       "3  0.205018          16          10          17          17   15.666667   \n",
       "4  0.125296           4           7          11          14   10.166667   \n",
       "\n",
       "   Shape_Length   Shape_Area  \n",
       "0   3110.360820  297836.0831  \n",
       "1   3519.469110  484945.1466  \n",
       "2   1697.091802  106705.9281  \n",
       "3   2922.609204  481828.4303  \n",
       "4   3731.971773  687684.7752  \n",
       "\n",
       "[5 rows x 117 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looking at the Walkability dataset for USA...\n",
    "url = 'https://edg.epa.gov/EPADataCommons/public/OA/EPA_SmartLocationDatabase_V3_Jan_2021_Final.csv'\n",
    "walk_df = pd.read_csv(url)\n",
    "walk_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "walk_df.dropna(subset=['GEOID10'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# FIPs ID from float to int to string to fill to 12 numbers\n",
    "walk_df['GEOID10'] = walk_df['GEOID10'].astype(np.int64).astype(str).str.zfill(12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming column so we can merge on\n",
    "walk_df.rename(columns={'GEOID10': 'fips_id'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>OBJECTID</th>\n",
       "      <th>fips_id</th>\n",
       "      <th>GEOID20</th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>TRACTCE</th>\n",
       "      <th>BLKGRPCE</th>\n",
       "      <th>CSA</th>\n",
       "      <th>CSA_Name</th>\n",
       "      <th>CBSA</th>\n",
       "      <th>...</th>\n",
       "      <th>D5DRI</th>\n",
       "      <th>D5DE</th>\n",
       "      <th>D5DEI</th>\n",
       "      <th>D2A_Ranked</th>\n",
       "      <th>D2B_Ranked</th>\n",
       "      <th>D3B_Ranked</th>\n",
       "      <th>D4A_Ranked</th>\n",
       "      <th>NatWalkInd</th>\n",
       "      <th>Shape_Length</th>\n",
       "      <th>Shape_Area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>481130000000</td>\n",
       "      <td>4.811300e+11</td>\n",
       "      <td>48</td>\n",
       "      <td>113</td>\n",
       "      <td>7825</td>\n",
       "      <td>4</td>\n",
       "      <td>206.0</td>\n",
       "      <td>Dallas-Fort Worth, TX-OK</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.184697</td>\n",
       "      <td>0.000476</td>\n",
       "      <td>0.137707</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3110.36082</td>\n",
       "      <td>297836.0831</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 117 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID       fips_id       GEOID20  STATEFP  COUNTYFP  TRACTCE  BLKGRPCE  \\\n",
       "0         1  481130000000  4.811300e+11       48       113     7825         4   \n",
       "\n",
       "     CSA                  CSA_Name     CBSA  ...     D5DRI      D5DE  \\\n",
       "0  206.0  Dallas-Fort Worth, TX-OK  19100.0  ...  0.184697  0.000476   \n",
       "\n",
       "      D5DEI  D2A_Ranked  D2B_Ranked  D3B_Ranked  D4A_Ranked  NatWalkInd  \\\n",
       "0  0.137707           6          14          15          17        14.0   \n",
       "\n",
       "   Shape_Length   Shape_Area  \n",
       "0    3110.36082  297836.0831  \n",
       "\n",
       "[1 rows x 117 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "walk_df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9538, 140)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merging Purple Air AQ data with Walkability for USA\n",
    "air_walk = pd.merge(pa_fips_df, walk_df, on='fips_id')\n",
    "air_walk.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CSA_Name\n",
       "San Jose-San Francisco-Oakland, CA    5720\n",
       "Los Angeles-Long Beach, CA            1033\n",
       "Sacramento-Roseville, CA               865\n",
       "Denver-Aurora, CO                      272\n",
       "Fresno-Madera-Hanford, CA              203\n",
       "Redding-Red Bluff, CA                   74\n",
       "Tucson-Nogales, AZ                      44\n",
       "Edwards-Glenwood Springs, CO            37\n",
       "Phoenix-Mesa, AZ                        36\n",
       "New York-Newark, NY-NJ-CT-PA            26\n",
       "Steamboat Springs-Craig, CO             12\n",
       "Hartford-East Hartford, CT              11\n",
       "Birmingham-Hoover-Talladega, AL          6\n",
       "Huntsville-Decatur, AL                   5\n",
       "Pueblo-Cañon City, CO                    4\n",
       "Little Rock-North Little Rock, AR        4\n",
       "Mobile-Daphne-Fairhope, AL               1\n",
       "Name: CSA, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "air_walk.groupby('CSA_Name').count()['CSA'].sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "air_walk.to_csv('original_data/air_walk_merged.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Los Angeles Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6425 entries, 31470 to 54651\n",
      "Columns: 117 entries, OBJECTID to Shape_Area\n",
      "dtypes: float64(72), int64(42), object(3)\n",
      "memory usage: 5.8+ MB\n"
     ]
    }
   ],
   "source": [
    "walk_la_df = walk_df.query('(STATEFP == 6) and (COUNTYFP == 37)')\n",
    "walk_la_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "walk_la_df.to_csv('./data/LA_data/walkability_LA.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>OBJECTID</th>\n",
       "      <th>fips_id</th>\n",
       "      <th>GEOID20</th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>TRACTCE</th>\n",
       "      <th>BLKGRPCE</th>\n",
       "      <th>CSA</th>\n",
       "      <th>CSA_Name</th>\n",
       "      <th>CBSA</th>\n",
       "      <th>...</th>\n",
       "      <th>D5DRI</th>\n",
       "      <th>D5DE</th>\n",
       "      <th>D5DEI</th>\n",
       "      <th>D2A_Ranked</th>\n",
       "      <th>D2B_Ranked</th>\n",
       "      <th>D3B_Ranked</th>\n",
       "      <th>D4A_Ranked</th>\n",
       "      <th>NatWalkInd</th>\n",
       "      <th>Shape_Length</th>\n",
       "      <th>Shape_Area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31470</th>\n",
       "      <td>31471</td>\n",
       "      <td>060371872002</td>\n",
       "      <td>6.037187e+10</td>\n",
       "      <td>6</td>\n",
       "      <td>37</td>\n",
       "      <td>187200</td>\n",
       "      <td>2</td>\n",
       "      <td>348.0</td>\n",
       "      <td>Los Angeles-Long Beach, CA</td>\n",
       "      <td>31080.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.309765</td>\n",
       "      <td>0.000133</td>\n",
       "      <td>0.17882</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>13.333333</td>\n",
       "      <td>3796.740538</td>\n",
       "      <td>552006.8969</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 117 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       OBJECTID       fips_id       GEOID20  STATEFP  COUNTYFP  TRACTCE  \\\n",
       "31470     31471  060371872002  6.037187e+10        6        37   187200   \n",
       "\n",
       "       BLKGRPCE    CSA                    CSA_Name     CBSA  ...     D5DRI  \\\n",
       "31470         2  348.0  Los Angeles-Long Beach, CA  31080.0  ...  0.309765   \n",
       "\n",
       "           D5DE    D5DEI  D2A_Ranked  D2B_Ranked  D3B_Ranked  D4A_Ranked  \\\n",
       "31470  0.000133  0.17882          15           7          16          13   \n",
       "\n",
       "       NatWalkInd  Shape_Length   Shape_Area  \n",
       "31470   13.333333   3796.740538  552006.8969  \n",
       "\n",
       "[1 rows x 117 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looking at the Walkability dataset for LA...\n",
    "walk_la_df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31470    060371872002\n",
       "31471    060371873001\n",
       "31472    060371873002\n",
       "31476    060375437024\n",
       "31477    060375437025\n",
       "Name: fips_id, dtype: object"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "walk_la_df['fips_id'][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    482015549031\n",
       "1    530530703082\n",
       "2    530530703082\n",
       "3    421298086003\n",
       "4    060190050003\n",
       "Name: fips_id, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pa_fips_df['fips_id'][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(612, 140)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "air_walk_la = pd.merge(pa_fips_df, walk_la_df, on='fips_id')\n",
    "air_walk_la.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "496"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "air_walk_la['fips_id'].value_counts().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>id</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>name</th>\n",
       "      <th>location_type</th>\n",
       "      <th>pm_2.5</th>\n",
       "      <th>temp_f</th>\n",
       "      <th>temp_c</th>\n",
       "      <th>humidity</th>\n",
       "      <th>pressure</th>\n",
       "      <th>...</th>\n",
       "      <th>D5DRI</th>\n",
       "      <th>D5DE</th>\n",
       "      <th>D5DEI</th>\n",
       "      <th>D2A_Ranked</th>\n",
       "      <th>D2B_Ranked</th>\n",
       "      <th>D3B_Ranked</th>\n",
       "      <th>D4A_Ranked</th>\n",
       "      <th>NatWalkInd</th>\n",
       "      <th>Shape_Length</th>\n",
       "      <th>Shape_Area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>47959</td>\n",
       "      <td>33.767104</td>\n",
       "      <td>-118.198957</td>\n",
       "      <td>100 Oceangate</td>\n",
       "      <td>inside</td>\n",
       "      <td>0.82</td>\n",
       "      <td>86.0</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1012.79</td>\n",
       "      <td>...</td>\n",
       "      <td>0.104685</td>\n",
       "      <td>0.000111</td>\n",
       "      <td>0.149839</td>\n",
       "      <td>18</td>\n",
       "      <td>17</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>19.166667</td>\n",
       "      <td>6972.639103</td>\n",
       "      <td>1.801666e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>94345</td>\n",
       "      <td>34.138132</td>\n",
       "      <td>-118.009183</td>\n",
       "      <td>1230 S Mayflower (Inside)</td>\n",
       "      <td>inside</td>\n",
       "      <td>0.00</td>\n",
       "      <td>94.0</td>\n",
       "      <td>34.444444</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1002.99</td>\n",
       "      <td>...</td>\n",
       "      <td>0.099344</td>\n",
       "      <td>0.000064</td>\n",
       "      <td>0.085859</td>\n",
       "      <td>19</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>20</td>\n",
       "      <td>19.166667</td>\n",
       "      <td>3024.620797</td>\n",
       "      <td>4.501286e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>95367</td>\n",
       "      <td>34.138150</td>\n",
       "      <td>-118.009264</td>\n",
       "      <td>1230 S Mayflower (Outside)</td>\n",
       "      <td>outside</td>\n",
       "      <td>0.30</td>\n",
       "      <td>103.0</td>\n",
       "      <td>39.444444</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1003.45</td>\n",
       "      <td>...</td>\n",
       "      <td>0.099344</td>\n",
       "      <td>0.000064</td>\n",
       "      <td>0.085859</td>\n",
       "      <td>19</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>20</td>\n",
       "      <td>19.166667</td>\n",
       "      <td>3024.620797</td>\n",
       "      <td>4.501286e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>129297</td>\n",
       "      <td>34.019778</td>\n",
       "      <td>-118.295592</td>\n",
       "      <td>1234W 37th PL</td>\n",
       "      <td>inside</td>\n",
       "      <td>19.43</td>\n",
       "      <td>94.0</td>\n",
       "      <td>34.444444</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1012.53</td>\n",
       "      <td>...</td>\n",
       "      <td>0.763976</td>\n",
       "      <td>0.000355</td>\n",
       "      <td>0.477984</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>20</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>2453.349539</td>\n",
       "      <td>2.802423e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3479</td>\n",
       "      <td>34.046755</td>\n",
       "      <td>-118.155307</td>\n",
       "      <td>1381 Rock Haven St</td>\n",
       "      <td>outside</td>\n",
       "      <td>0.04</td>\n",
       "      <td>124.0</td>\n",
       "      <td>51.111111</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1001.91</td>\n",
       "      <td>...</td>\n",
       "      <td>0.123065</td>\n",
       "      <td>0.000066</td>\n",
       "      <td>0.088451</td>\n",
       "      <td>19</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>20</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>3474.082108</td>\n",
       "      <td>5.913926e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 140 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       id        lat         lon                        name location_type  \\\n",
       "0   47959  33.767104 -118.198957               100 Oceangate        inside   \n",
       "1   94345  34.138132 -118.009183   1230 S Mayflower (Inside)        inside   \n",
       "2   95367  34.138150 -118.009264  1230 S Mayflower (Outside)       outside   \n",
       "3  129297  34.019778 -118.295592               1234W 37th PL        inside   \n",
       "4    3479  34.046755 -118.155307          1381 Rock Haven St       outside   \n",
       "\n",
       "   pm_2.5  temp_f     temp_c  humidity  pressure  ...     D5DRI      D5DE  \\\n",
       "0    0.82    86.0  30.000000      17.0   1012.79  ...  0.104685  0.000111   \n",
       "1    0.00    94.0  34.444444      11.0   1002.99  ...  0.099344  0.000064   \n",
       "2    0.30   103.0  39.444444       3.0   1003.45  ...  0.099344  0.000064   \n",
       "3   19.43    94.0  34.444444      18.0   1012.53  ...  0.763976  0.000355   \n",
       "4    0.04   124.0  51.111111       2.0   1001.91  ...  0.123065  0.000066   \n",
       "\n",
       "      D5DEI  D2A_Ranked  D2B_Ranked  D3B_Ranked  D4A_Ranked  NatWalkInd  \\\n",
       "0  0.149839          18          17          20          20   19.166667   \n",
       "1  0.085859          19          18          19          20   19.166667   \n",
       "2  0.085859          19          18          19          20   19.166667   \n",
       "3  0.477984           6           2          18          20   14.000000   \n",
       "4  0.088451          19          17          13          20   17.000000   \n",
       "\n",
       "   Shape_Length    Shape_Area  \n",
       "0   6972.639103  1.801666e+06  \n",
       "1   3024.620797  4.501286e+05  \n",
       "2   3024.620797  4.501286e+05  \n",
       "3   2453.349539  2.802423e+05  \n",
       "4   3474.082108  5.913926e+05  \n",
       "\n",
       "[5 rows x 140 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "air_walk_la.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "air_walk_la.to_csv('./data/LA_data/air_walk_merged_LA.csv', index = False)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "98a50cb2aca4e80adc6e29c12ccfb4672626a7c0d857767537bdcd8f6e0a96bd"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "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"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}