code/hm_notebooks/2_merging_smoke_fires.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b52e07f7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.005181Z",
     "start_time": "2021-05-21T21:51:47.799449Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import geocoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d482bde0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.821628Z",
     "start_time": "2021-05-21T21:51:48.007648Z"
    }
   },
   "outputs": [],
   "source": [
    "path = '../scratch_data/'\n",
    "smoke = pd.read_csv(path + 'smoke_west_counties_2010_2016.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "95fd7a01",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.830209Z",
     "start_time": "2021-05-21T21:51:48.822749Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3683736, 7)\n"
     ]
    },
    {
     "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</th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>county_name</th>\n",
       "      <th>date</th>\n",
       "      <th>POPULATION</th>\n",
       "      <th>smoke_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>460</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>1301</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>1583</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>969</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STATE  STATEFP  COUNTYFP  county_name      date  POPULATION  smoke_score\n",
       "0    AZ        4         7  Gila County  20100604         460            1\n",
       "1    AZ        4         7  Gila County  20100604         821            1\n",
       "2    AZ        4         7  Gila County  20100604        1301            1\n",
       "3    AZ        4         7  Gila County  20100604        1583            1\n",
       "4    AZ        4         7  Gila County  20100604         969            1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(smoke.shape)\n",
    "smoke.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6a5d4e59",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.902571Z",
     "start_time": "2021-05-21T21:51:48.831233Z"
    }
   },
   "outputs": [],
   "source": [
    "fires = pd.read_csv(path + 'mtbs_FODpoints_DD/MTBS_FODpoints.csv', lineterminator='\\n')\n",
    "wildfires = fires.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "863f478f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.912010Z",
     "start_time": "2021-05-21T21:51:48.903321Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(26557, 24)\n"
     ]
    },
    {
     "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>Unnamed: 0</th>\n",
       "      <th>Event_ID</th>\n",
       "      <th>irwinID</th>\n",
       "      <th>Incid_Name</th>\n",
       "      <th>Incid_Type</th>\n",
       "      <th>Map_ID</th>\n",
       "      <th>Map_Prog</th>\n",
       "      <th>Asmnt_Type</th>\n",
       "      <th>BurnBndAc</th>\n",
       "      <th>BurnBndLat</th>\n",
       "      <th>...</th>\n",
       "      <th>Perim_ID</th>\n",
       "      <th>dNBR_offst</th>\n",
       "      <th>dNBR_stdDv</th>\n",
       "      <th>NoData_T</th>\n",
       "      <th>IncGreen_T</th>\n",
       "      <th>Low_T</th>\n",
       "      <th>Mod_T</th>\n",
       "      <th>High_T</th>\n",
       "      <th>Comment</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>UT4176411148820121004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HELLS HOLLOW</td>\n",
       "      <td>Prescribed Fire</td>\n",
       "      <td>414</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>4758</td>\n",
       "      <td>41.766</td>\n",
       "      <td>...</td>\n",
       "      <td>7.038031e+14</td>\n",
       "      <td>47</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>113</td>\n",
       "      <td>345</td>\n",
       "      <td>616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-111.507983362626 41.75847249141982)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>WA4796812059020120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KLONE PEAK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>441</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1634</td>\n",
       "      <td>47.972</td>\n",
       "      <td>...</td>\n",
       "      <td>7.045027e+14</td>\n",
       "      <td>25</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>250</td>\n",
       "      <td>460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.5815199717518 47.97210047512218)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>WA4797012073420120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BASALT</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>444</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1705</td>\n",
       "      <td>47.967</td>\n",
       "      <td>...</td>\n",
       "      <td>7.045027e+14</td>\n",
       "      <td>-4</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>60</td>\n",
       "      <td>250</td>\n",
       "      <td>500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.7448841870875 47.96732496805843)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>WA4805112058820120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PYRAMID</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>449</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1962</td>\n",
       "      <td>48.044</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>120</td>\n",
       "      <td>220</td>\n",
       "      <td>530</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.5812232399775 48.04473598140331)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>WA4788612026820120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FIRST CREEK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>454</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1434</td>\n",
       "      <td>47.895</td>\n",
       "      <td>...</td>\n",
       "      <td>7.045027e+14</td>\n",
       "      <td>-37</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>200</td>\n",
       "      <td>460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.2604888608036 47.89597596325936)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0               Event_ID irwinID    Incid_Name       Incid_Type  \\\n",
       "0           0  UT4176411148820121004     NaN  HELLS HOLLOW  Prescribed Fire   \n",
       "1           1  WA4796812059020120908     NaN    KLONE PEAK         Wildfire   \n",
       "2           2  WA4797012073420120908     NaN        BASALT         Wildfire   \n",
       "3           3  WA4805112058820120908     NaN       PYRAMID         Wildfire   \n",
       "4           4  WA4788612026820120908     NaN   FIRST CREEK         Wildfire   \n",
       "\n",
       "   Map_ID Map_Prog Asmnt_Type  BurnBndAc  BurnBndLat  ...      Perim_ID  \\\n",
       "0     414     MTBS   Extended       4758      41.766  ...  7.038031e+14   \n",
       "1     441     MTBS   Extended       1634      47.972  ...  7.045027e+14   \n",
       "2     444     MTBS   Extended       1705      47.967  ...  7.045027e+14   \n",
       "3     449     MTBS   Extended       1962      48.044  ...           NaN   \n",
       "4     454     MTBS   Extended       1434      47.895  ...  7.045027e+14   \n",
       "\n",
       "  dNBR_offst dNBR_stdDv NoData_T  IncGreen_T  Low_T  Mod_T  High_T  Comment  \\\n",
       "0         47      -9999     -970        -150    113    345     616      NaN   \n",
       "1         25      -9999     -970        -150     50    250     460      NaN   \n",
       "2         -4      -9999     -970        -150     60    250     500      NaN   \n",
       "3         74      -9999     -970        -150    120    220     530      NaN   \n",
       "4        -37      -9999     -970        -150     50    200     460      NaN   \n",
       "\n",
       "                                       geometry  \n",
       "0   POINT (-111.507983362626 41.75847249141982)  \n",
       "1  POINT (-120.5815199717518 47.97210047512218)  \n",
       "2  POINT (-120.7448841870875 47.96732496805843)  \n",
       "3  POINT (-120.5812232399775 48.04473598140331)  \n",
       "4  POINT (-120.2604888608036 47.89597596325936)  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(fires.shape)\n",
    "fires.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "75c19fb9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.914535Z",
     "start_time": "2021-05-21T21:51:48.912763Z"
    }
   },
   "outputs": [],
   "source": [
    "smoke.columns = smoke.columns.str.lower()\n",
    "wildfires.columns = wildfires.columns.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7f88e008",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.924098Z",
     "start_time": "2021-05-21T21:51:48.915196Z"
    }
   },
   "outputs": [],
   "source": [
    "#drops - only run cell once\n",
    "wildfires.drop('unnamed: 0', axis=1, inplace=True)\n",
    "wildfires['geometry2'] = wildfires['geometry'].map(lambda x: x.replace('POINT (', ''))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc3d284f",
   "metadata": {},
   "source": [
    "### Try merge on lat/long\n",
    "* wildfires: extract lat/lon from 'geometry'\n",
    "    * same as 'brnbndlat' and 'brnbndlon' with more decimals...\n",
    "    * filter df by lon (since no 'state' id) : min_lon_west = -125.5, max_lon_east = -108.5\n",
    "* smoke: create lat/lon from county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "eabade51",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.962842Z",
     "start_time": "2021-05-21T21:51:48.926044Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-8-62f9d2346058>:2: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will*not* be treated as literal strings when regex=True.\n",
      "  wildfires['lat'] = wildfires['lat'].str.replace(')','').astype('float')\n"
     ]
    }
   ],
   "source": [
    "wildfires[['lon', 'lat']] = wildfires['geometry2'].str.split(' ', expand=True)\n",
    "wildfires['lat'] = wildfires['lat'].str.replace(')','').astype('float')\n",
    "wildfires['lon'] = wildfires['lon'].astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5584b801",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.974769Z",
     "start_time": "2021-05-21T21:51:48.965350Z"
    }
   },
   "outputs": [],
   "source": [
    "wildfires['ig_date'] = wildfires['ig_date'].str.replace('-','').astype('int32')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "014a56d5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.986614Z",
     "start_time": "2021-05-21T21:51:48.976458Z"
    }
   },
   "outputs": [],
   "source": [
    "wildfires = wildfires.loc[(wildfires['ig_date']>=20100604) & (wildfires['ig_date']<=20161231)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5137274c",
   "metadata": {},
   "source": [
    "### lat/lon too granular for merge easily with smoke, try reverse geocode county to wildfires"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0ed358bd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.989941Z",
     "start_time": "2021-05-21T21:51:48.987388Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7198"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(wildfires)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ffb72ffa",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.994778Z",
     "start_time": "2021-05-21T21:51:48.990648Z"
    }
   },
   "outputs": [],
   "source": [
    "#bound by lat/lon to get States of interest, no 'state' identifier\n",
    "wildfires = wildfires.loc[wildfires['lon'] <= -108.5]  #eastern bound, AZ-NM border\n",
    "wildfires = wildfires.loc[wildfires['lat'] <= 53.5] #northernmost point in smoke data\n",
    "wildfires = wildfires.loc[wildfires['lon'] >= -130] #west bound since apparently HI is in here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "18027e59",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:48.997306Z",
     "start_time": "2021-05-21T21:51:48.995506Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1588"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(wildfires)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1a410d43",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:49.009608Z",
     "start_time": "2021-05-21T21:51:48.997995Z"
    }
   },
   "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>event_id</th>\n",
       "      <th>irwinid</th>\n",
       "      <th>incid_name</th>\n",
       "      <th>incid_type</th>\n",
       "      <th>map_id</th>\n",
       "      <th>map_prog</th>\n",
       "      <th>asmnt_type</th>\n",
       "      <th>burnbndac</th>\n",
       "      <th>burnbndlat</th>\n",
       "      <th>burnbndlon</th>\n",
       "      <th>...</th>\n",
       "      <th>nodata_t</th>\n",
       "      <th>incgreen_t</th>\n",
       "      <th>low_t</th>\n",
       "      <th>mod_t</th>\n",
       "      <th>high_t</th>\n",
       "      <th>comment</th>\n",
       "      <th>geometry</th>\n",
       "      <th>geometry2</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UT4176411148820121004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HELLS HOLLOW</td>\n",
       "      <td>Prescribed Fire</td>\n",
       "      <td>414</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>4758</td>\n",
       "      <td>41.766</td>\n",
       "      <td>-111.507</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>113</td>\n",
       "      <td>345</td>\n",
       "      <td>616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-111.507983362626 41.75847249141982)</td>\n",
       "      <td>-111.507983362626 41.75847249141982)</td>\n",
       "      <td>-111.507983</td>\n",
       "      <td>41.758472</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                event_id irwinid    incid_name       incid_type  map_id  \\\n",
       "0  UT4176411148820121004     NaN  HELLS HOLLOW  Prescribed Fire     414   \n",
       "\n",
       "  map_prog asmnt_type  burnbndac  burnbndlat  burnbndlon  ...  nodata_t  \\\n",
       "0     MTBS   Extended       4758      41.766    -111.507  ...      -970   \n",
       "\n",
       "  incgreen_t low_t  mod_t  high_t  comment  \\\n",
       "0       -150   113    345     616      NaN   \n",
       "\n",
       "                                      geometry  \\\n",
       "0  POINT (-111.507983362626 41.75847249141982)   \n",
       "\n",
       "                              geometry2         lon        lat  \n",
       "0  -111.507983362626 41.75847249141982) -111.507983  41.758472  \n",
       "\n",
       "[1 rows x 26 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wildfires.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ab37d180",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:49.013522Z",
     "start_time": "2021-05-21T21:51:49.010935Z"
    }
   },
   "outputs": [],
   "source": [
    "reverse_geo_lats = wildfires['lat'].to_list()\n",
    "reverse_geo_lon = wildfires['lon'].to_list()\n",
    "rev_geo_coords = list(zip(reverse_geo_lats, reverse_geo_lon))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d9acbcd5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:49.017214Z",
     "start_time": "2021-05-21T21:51:49.014653Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(31.30242149353263, 48.974338338076)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min(reverse_geo_lats), max(reverse_geo_lats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "9b7a9379",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:49.019900Z",
     "start_time": "2021-05-21T21:51:49.018006Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(-124.0926552889532, -108.5106089326423)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min(reverse_geo_lon), max(reverse_geo_lon)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9dcb218e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.082602Z",
     "start_time": "2021-05-21T21:51:49.020580Z"
    }
   },
   "outputs": [],
   "source": [
    "from geopy.geocoders import Nominatim\n",
    "geolocator = Nominatim(user_agent = 'helen.meigs@gmail.com')\n",
    "location = geolocator.reverse(rev_geo_coords[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "cf8a4c28",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.091939Z",
     "start_time": "2021-05-21T21:51:50.085922Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Cache County, Utah, United States'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "location[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7a73dc1c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.097497Z",
     "start_time": "2021-05-21T21:51:50.094749Z"
    }
   },
   "outputs": [],
   "source": [
    "# %%time\n",
    "# wf_counties = []\n",
    "# wf_states = []\n",
    "# wf_full_loc = []\n",
    "\n",
    "# for i in range(len(rev_geo_coords)):\n",
    "#     location = geolocator.reverse(rev_geo_coords[i])\n",
    "#     county = location[0].split(',')[0]\n",
    "#     state = location[0].split(',')[1]\n",
    "#     wf_counties.append(county)\n",
    "#     wf_states.append(state)\n",
    "#     wf_full_loc.append(location[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a4b28cd",
   "metadata": {},
   "source": [
    "##### Tried working with geocoded 'location' output in list  form (each entry a single str that could be split on ',') but couldn't figure it out.  Proceed to the less elegant answer:\n",
    "* New df with geo_columns for each section of location\n",
    "    * _ONE_ of these geo_columns will contain 'County' for most observations\n",
    "    * geo_column containing 'County' will vary\n",
    "        * curve ball, some things like County Road, and 'Not in a County' exist. great.\n",
    "* Create a column for _each?_ geo_col indicating presence/absence of 'County'?\n",
    "* Populate some other final column with all the counties?  Maybe by cascading fillna()?\n",
    "* REPEAT WITH STATE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "f1faaa42",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.101474Z",
     "start_time": "2021-05-21T21:51:50.099526Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf = pd.DataFrame(wf_full_loc, columns=['reverse_geocode'])\n",
    "# wf_locdf.to_csv('../scratch_data/mtbs_FODpoints_DD/mtbs_reverse_geocode.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7dda8ad9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.110371Z",
     "start_time": "2021-05-21T21:51:50.103258Z"
    }
   },
   "outputs": [],
   "source": [
    "wf_locdf = pd.read_csv('../scratch_data/mtbs_FODpoints_DD/mtbs_reverse_geocode.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "6ada47a8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.121878Z",
     "start_time": "2021-05-21T21:51:50.116345Z"
    }
   },
   "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>reverse_geocode</th>\n",
       "      <th>county_name</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cache County, Utah, United States</td>\n",
       "      <td>Cache County</td>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Minnow Creek Trail, Chelan County, Washington,...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pugh Ridge Trail #1438, Chelan County, Washing...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     reverse_geocode    county_name  \\\n",
       "0                  Cache County, Utah, United States   Cache County   \n",
       "1           Chelan County, Washington, United States  Chelan County   \n",
       "2  Minnow Creek Trail, Chelan County, Washington,...  Chelan County   \n",
       "3  Pugh Ridge Trail #1438, Chelan County, Washing...  Chelan County   \n",
       "4           Chelan County, Washington, United States  Chelan County   \n",
       "\n",
       "        state  \n",
       "0        Utah  \n",
       "1  Washington  \n",
       "2  Washington  \n",
       "3  Washington  \n",
       "4  Washington  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wf_locdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "7d22e7c9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.131790Z",
     "start_time": "2021-05-21T21:51:50.124875Z"
    }
   },
   "outputs": [],
   "source": [
    "wf_locdf[['geo1', 'geo2', 'geo3', 'geo4', 'geo5', 'geo6', 'geo7']] = wf_locdf['reverse_geocode'].str.split(',', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "4c2aca1d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.137308Z",
     "start_time": "2021-05-21T21:51:50.132802Z"
    }
   },
   "outputs": [],
   "source": [
    "wf_locdf.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3c8f1a1e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.146012Z",
     "start_time": "2021-05-21T21:51:50.138243Z"
    }
   },
   "outputs": [],
   "source": [
    "geo_cols = wf_locdf.drop(columns='reverse_geocode').columns.to_list()\n",
    "\n",
    "for i in geo_cols:\n",
    "    wf_locdf[f'{i}_county'] = wf_locdf[i].map(lambda x: x if 'County' in x else np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "33579c1a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.149136Z",
     "start_time": "2021-05-21T21:51:50.146869Z"
    }
   },
   "outputs": [],
   "source": [
    "wf_locdf['county_name'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "25136e96",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.155507Z",
     "start_time": "2021-05-21T21:51:50.151211Z"
    }
   },
   "outputs": [],
   "source": [
    "county_cols = ['geo1_county', 'geo2_county', 'geo3_county', 'geo4_county', 'geo5_county']\n",
    "for i in county_cols:\n",
    "    wf_locdf['county_name'].fillna(wf_locdf[i], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "c7f8e510",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.163407Z",
     "start_time": "2021-05-21T21:51:50.156342Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1588 entries, 0 to 1587\n",
      "Data columns (total 19 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   reverse_geocode     1588 non-null   object \n",
      " 1   county_name         1571 non-null   object \n",
      " 2   state               1588 non-null   object \n",
      " 3   geo1                1588 non-null   object \n",
      " 4   geo2                1588 non-null   object \n",
      " 5   geo3                1588 non-null   object \n",
      " 6   geo4                1588 non-null   object \n",
      " 7   geo5                1588 non-null   object \n",
      " 8   geo6                1588 non-null   object \n",
      " 9   geo7                1588 non-null   object \n",
      " 10  county_name_county  1585 non-null   object \n",
      " 11  state_county        0 non-null      float64\n",
      " 12  geo1_county         1113 non-null   object \n",
      " 13  geo2_county         367 non-null    object \n",
      " 14  geo3_county         82 non-null     object \n",
      " 15  geo4_county         11 non-null     object \n",
      " 16  geo5_county         1 non-null      object \n",
      " 17  geo6_county         0 non-null      float64\n",
      " 18  geo7_county         0 non-null      float64\n",
      "dtypes: float64(3), object(16)\n",
      "memory usage: 235.8+ KB\n"
     ]
    }
   ],
   "source": [
    "# only 15 locations without county ID, not bad\n",
    "wf_locdf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bf1ece4",
   "metadata": {},
   "source": [
    "##### Repeat for State, so can filter by State\n",
    "More annoying because States don't say the word State in them...luckily I only need seven."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1d1540f7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.171173Z",
     "start_time": "2021-05-21T21:51:50.164144Z"
    }
   },
   "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>reverse_geocode</th>\n",
       "      <th>county_name</th>\n",
       "      <th>state</th>\n",
       "      <th>geo1</th>\n",
       "      <th>geo2</th>\n",
       "      <th>geo3</th>\n",
       "      <th>geo4</th>\n",
       "      <th>geo5</th>\n",
       "      <th>geo6</th>\n",
       "      <th>geo7</th>\n",
       "      <th>county_name_county</th>\n",
       "      <th>state_county</th>\n",
       "      <th>geo1_county</th>\n",
       "      <th>geo2_county</th>\n",
       "      <th>geo3_county</th>\n",
       "      <th>geo4_county</th>\n",
       "      <th>geo5_county</th>\n",
       "      <th>geo6_county</th>\n",
       "      <th>geo7_county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cache County, Utah, United States</td>\n",
       "      <td>Cache County</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Cache County</td>\n",
       "      <td>Utah</td>\n",
       "      <td>United States</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Cache County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cache County</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>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>United States</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chelan County</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>Minnow Creek Trail, Chelan County, Washington,...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Minnow Creek Trail</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>United States</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chelan County</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>Pugh Ridge Trail #1438, Chelan County, Washing...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Pugh Ridge Trail #1438</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>United States</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chelan County</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>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "      <td>United States</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chelan County</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",
       "</div>"
      ],
      "text/plain": [
       "                                     reverse_geocode     county_name  \\\n",
       "0                  Cache County, Utah, United States    Cache County   \n",
       "1           Chelan County, Washington, United States   Chelan County   \n",
       "2  Minnow Creek Trail, Chelan County, Washington,...   Chelan County   \n",
       "3  Pugh Ridge Trail #1438, Chelan County, Washing...   Chelan County   \n",
       "4           Chelan County, Washington, United States   Chelan County   \n",
       "\n",
       "        state                    geo1            geo2            geo3  \\\n",
       "0        Utah            Cache County            Utah   United States   \n",
       "1  Washington           Chelan County      Washington   United States   \n",
       "2  Washington      Minnow Creek Trail   Chelan County      Washington   \n",
       "3  Washington  Pugh Ridge Trail #1438   Chelan County      Washington   \n",
       "4  Washington           Chelan County      Washington   United States   \n",
       "\n",
       "             geo4 geo5 geo6 geo7 county_name_county  state_county  \\\n",
       "0                                      Cache County           NaN   \n",
       "1                                     Chelan County           NaN   \n",
       "2   United States                     Chelan County           NaN   \n",
       "3   United States                     Chelan County           NaN   \n",
       "4                                     Chelan County           NaN   \n",
       "\n",
       "     geo1_county     geo2_county geo3_county geo4_county geo5_county  \\\n",
       "0   Cache County             NaN         NaN         NaN         NaN   \n",
       "1  Chelan County             NaN         NaN         NaN         NaN   \n",
       "2            NaN   Chelan County         NaN         NaN         NaN   \n",
       "3            NaN   Chelan County         NaN         NaN         NaN   \n",
       "4  Chelan County             NaN         NaN         NaN         NaN   \n",
       "\n",
       "   geo6_county  geo7_county  \n",
       "0          NaN          NaN  \n",
       "1          NaN          NaN  \n",
       "2          NaN          NaN  \n",
       "3          NaN          NaN  \n",
       "4          NaN          NaN  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wf_locdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c752f582",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.173121Z",
     "start_time": "2021-05-21T21:51:50.171899Z"
    }
   },
   "outputs": [],
   "source": [
    "# westofrockies = ['Montana', 'Wyoming', 'New Mexico', 'Colorado', 'California', 'Arizona', 'Nevada', 'Washington', 'Oregon', 'Utah', 'Idaho']\n",
    "# for i in geo_cols:\n",
    "#     wf_locdf[f'{i}_state'] = wf_locdf[i].str.contains('|'.join(westofrockies)).astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "1747ef47",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.175000Z",
     "start_time": "2021-05-21T21:51:50.173764Z"
    }
   },
   "outputs": [],
   "source": [
    "# for i in geo_cols:\n",
    "#     for j in range(len(wf_locdf)):\n",
    "#                    if wf_locdf[f'{i}_state'][j] == 1:\n",
    "#                        wf_locdf[f'{i}_state'][j]=wf_locdf[i][j]\n",
    "#                    else:\n",
    "#                        wf_locdf[f'{i}_state'][j]=np.nan\n",
    "            \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "5cc6d185",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.176837Z",
     "start_time": "2021-05-21T21:51:50.175706Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf['state'] = np.nan\n",
    "# state_cols = ['geo1_state', 'geo2_state', 'geo3_state', 'geo4_state', 'geo5_state', 'geo6_state']\n",
    "# for i in state_cols:\n",
    "#     wf_locdf['state'].fillna(wf_locdf[i], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "bff6c78c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.178661Z",
     "start_time": "2021-05-21T21:51:50.177484Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf.drop(columns=geo_cols + county_cols + state_cols, inplace=True)\n",
    "# wf_locdf.drop(columns = ['geo6_county', 'geo7_county', 'geo7_state'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5d9c9060",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.180662Z",
     "start_time": "2021-05-21T21:51:50.179332Z"
    }
   },
   "outputs": [],
   "source": [
    "# pd.set_option('display.max_colwidth', 1000)\n",
    "# wf_locdf.loc[wf_locdf['county_name'].isnull()]  #now what"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "78837170",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.183577Z",
     "start_time": "2021-05-21T21:51:50.181584Z"
    }
   },
   "outputs": [],
   "source": [
    "# #manually impute county nulls by google mapping it and seeing what's closest\n",
    "# wf_locdf['county_name'][94] = 'Duchesne County'\n",
    "# wf_locdf['county_name'][126] = 'Santa Cruz County'\n",
    "# wf_locdf['state'][126] = 'Arizona'\n",
    "# # index 135 cannot impute county (Utah, U.S.)\n",
    "# wf_locdf['county_name'][188] = 'San Diego County'\n",
    "# wf_locdf['state'][188] = 'California'\n",
    "# wf_locdf['county_name'][288] = 'Los Angeles County'\n",
    "# wf_locdf['county_name'][323] = 'Duchesne County'\n",
    "# wf_locdf['county_name'][486] = 'Cochise County'\n",
    "# wf_locdf['state'][486] = 'Arizona'\n",
    "# wf_locdf['county_name'][589] = 'La Paz County'\n",
    "# # index 649 cannot impute county (Utah, U.S.)\n",
    "# wf_locdf['county_name'][698] = 'Los Angeles County'\n",
    "# wf_locdf['county_name'][836] = 'Los Angeles County'\n",
    "# wf_locdf['county_name'][1045] = 'Weber County'\n",
    "# wf_locdf['county_name'][1055] = 'Los Angeles County'\n",
    "# wf_locdf['county_name'][1177] = 'Los Angeles County'\n",
    "# wf_locdf['county_name'][1500] = 'Los Angeles County'\n",
    "# wf_locdf['county_name'][1504] = 'Los Angeles County'\n",
    "# # index 1528 cannot impute county (Utah, U.S.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "38a6c8ba",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.188091Z",
     "start_time": "2021-05-21T21:51:50.185920Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf.loc[wf_locdf['county_name'].isnull()] #checks out"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03ca02a8",
   "metadata": {},
   "source": [
    "Got a bunch of not-States, dangit (ie Washington Camp, which is in Arizona).  Few, so fix manually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "41b355b3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.190511Z",
     "start_time": "2021-05-21T21:51:50.188954Z"
    }
   },
   "outputs": [],
   "source": [
    "# #got a bunch of not-states, dangit\n",
    "# wf_locdf.state.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "2b4b27be",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.192798Z",
     "start_time": "2021-05-21T21:51:50.191498Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf.loc[wf_locdf['state'].str.contains('County')] #phew easy fix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "bee9dfae",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.194610Z",
     "start_time": "2021-05-21T21:51:50.193404Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf['state'] = wf_locdf['state'].str.replace('County','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "808f02ad",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.196413Z",
     "start_time": "2021-05-21T21:51:50.195249Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf['state'] = wf_locdf['state'].str.replace('Riding and Hiking ','')\n",
    "# wf_locdf['state'] = wf_locdf['state'].str.replace('Trail','')\n",
    "# wf_locdf['state'] = wf_locdf['state'].str.replace('Washingtonia Drive','California') #San diego county\n",
    "# wf_locdf['state'] = wf_locdf['state'].str.replace('Washington Camp','Arizona') #in AZ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "5212c3f5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.198301Z",
     "start_time": "2021-05-21T21:51:50.197115Z"
    }
   },
   "outputs": [],
   "source": [
    "# #strip leading and trailing whitespace for state and county_name\n",
    "# wf_locdf['state'] = wf_locdf['state'].str.strip()\n",
    "# wf_locdf['county_name'] = wf_locdf['county_name'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "e1549ce5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.200077Z",
     "start_time": "2021-05-21T21:51:50.198867Z"
    }
   },
   "outputs": [],
   "source": [
    "# wf_locdf.to_csv('../scratch_data/mtbs_FODpoints_DD/mtbs_reverse_geocode.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db4d1ad8",
   "metadata": {},
   "source": [
    "## Merge reverse geocodes back to wildfires\n",
    "Now wildfire df has state and county name\n",
    "* Few NANs\n",
    "* few obs from States not of interest (Wyoming, Colorado, New Mexico, Real Mexico).  However, these made the cut based on the lat/lon boundaries established earlier so these fire ignition points are very close to our established bounds (close to state border).\n",
    "    * keep in air quality analysis, because they will impact pollution sensors even though fires not originating from 'State of interest'?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "8725c196",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.204229Z",
     "start_time": "2021-05-21T21:51:50.200794Z"
    }
   },
   "outputs": [],
   "source": [
    "wf_locdf = pd.read_csv('../scratch_data/mtbs_FODpoints_DD/mtbs_reverse_geocode.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "35ce7595",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.206421Z",
     "start_time": "2021-05-21T21:51:50.204961Z"
    }
   },
   "outputs": [],
   "source": [
    "wildfires.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "d013bd85",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.209727Z",
     "start_time": "2021-05-21T21:51:50.207125Z"
    }
   },
   "outputs": [],
   "source": [
    "wildfires_with_counties = pd.concat([wildfires, wf_locdf], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "958599c7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.219937Z",
     "start_time": "2021-05-21T21:51:50.210480Z"
    }
   },
   "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>event_id</th>\n",
       "      <th>irwinid</th>\n",
       "      <th>incid_name</th>\n",
       "      <th>incid_type</th>\n",
       "      <th>map_id</th>\n",
       "      <th>map_prog</th>\n",
       "      <th>asmnt_type</th>\n",
       "      <th>burnbndac</th>\n",
       "      <th>burnbndlat</th>\n",
       "      <th>burnbndlon</th>\n",
       "      <th>...</th>\n",
       "      <th>mod_t</th>\n",
       "      <th>high_t</th>\n",
       "      <th>comment</th>\n",
       "      <th>geometry</th>\n",
       "      <th>geometry2</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "      <th>reverse_geocode</th>\n",
       "      <th>county_name</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UT4176411148820121004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HELLS HOLLOW</td>\n",
       "      <td>Prescribed Fire</td>\n",
       "      <td>414</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>4758</td>\n",
       "      <td>41.766</td>\n",
       "      <td>-111.507</td>\n",
       "      <td>...</td>\n",
       "      <td>345</td>\n",
       "      <td>616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-111.507983362626 41.75847249141982)</td>\n",
       "      <td>-111.507983362626 41.75847249141982)</td>\n",
       "      <td>-111.507983</td>\n",
       "      <td>41.758472</td>\n",
       "      <td>Cache County, Utah, United States</td>\n",
       "      <td>Cache County</td>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA4796812059020120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KLONE PEAK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>441</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1634</td>\n",
       "      <td>47.972</td>\n",
       "      <td>-120.582</td>\n",
       "      <td>...</td>\n",
       "      <td>250</td>\n",
       "      <td>460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.5815199717518 47.97210047512218)</td>\n",
       "      <td>-120.5815199717518 47.97210047512218)</td>\n",
       "      <td>-120.581520</td>\n",
       "      <td>47.972100</td>\n",
       "      <td>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WA4797012073420120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BASALT</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>444</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1705</td>\n",
       "      <td>47.967</td>\n",
       "      <td>-120.746</td>\n",
       "      <td>...</td>\n",
       "      <td>250</td>\n",
       "      <td>500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.7448841870875 47.96732496805843)</td>\n",
       "      <td>-120.7448841870875 47.96732496805843)</td>\n",
       "      <td>-120.744884</td>\n",
       "      <td>47.967325</td>\n",
       "      <td>Minnow Creek Trail, Chelan County, Washington,...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WA4805112058820120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PYRAMID</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>449</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1962</td>\n",
       "      <td>48.044</td>\n",
       "      <td>-120.581</td>\n",
       "      <td>...</td>\n",
       "      <td>220</td>\n",
       "      <td>530</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.5812232399775 48.04473598140331)</td>\n",
       "      <td>-120.5812232399775 48.04473598140331)</td>\n",
       "      <td>-120.581223</td>\n",
       "      <td>48.044736</td>\n",
       "      <td>Pugh Ridge Trail #1438, Chelan County, Washing...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WA4788612026820120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FIRST CREEK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>454</td>\n",
       "      <td>MTBS</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1434</td>\n",
       "      <td>47.895</td>\n",
       "      <td>-120.259</td>\n",
       "      <td>...</td>\n",
       "      <td>200</td>\n",
       "      <td>460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-120.2604888608036 47.89597596325936)</td>\n",
       "      <td>-120.2604888608036 47.89597596325936)</td>\n",
       "      <td>-120.260489</td>\n",
       "      <td>47.895976</td>\n",
       "      <td>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                event_id irwinid    incid_name       incid_type  map_id  \\\n",
       "0  UT4176411148820121004     NaN  HELLS HOLLOW  Prescribed Fire     414   \n",
       "1  WA4796812059020120908     NaN    KLONE PEAK         Wildfire     441   \n",
       "2  WA4797012073420120908     NaN        BASALT         Wildfire     444   \n",
       "3  WA4805112058820120908     NaN       PYRAMID         Wildfire     449   \n",
       "4  WA4788612026820120908     NaN   FIRST CREEK         Wildfire     454   \n",
       "\n",
       "  map_prog asmnt_type  burnbndac  burnbndlat  burnbndlon  ...  mod_t high_t  \\\n",
       "0     MTBS   Extended       4758      41.766    -111.507  ...    345    616   \n",
       "1     MTBS   Extended       1634      47.972    -120.582  ...    250    460   \n",
       "2     MTBS   Extended       1705      47.967    -120.746  ...    250    500   \n",
       "3     MTBS   Extended       1962      48.044    -120.581  ...    220    530   \n",
       "4     MTBS   Extended       1434      47.895    -120.259  ...    200    460   \n",
       "\n",
       "  comment                                      geometry  \\\n",
       "0     NaN   POINT (-111.507983362626 41.75847249141982)   \n",
       "1     NaN  POINT (-120.5815199717518 47.97210047512218)   \n",
       "2     NaN  POINT (-120.7448841870875 47.96732496805843)   \n",
       "3     NaN  POINT (-120.5812232399775 48.04473598140331)   \n",
       "4     NaN  POINT (-120.2604888608036 47.89597596325936)   \n",
       "\n",
       "                               geometry2         lon        lat  \\\n",
       "0   -111.507983362626 41.75847249141982) -111.507983  41.758472   \n",
       "1  -120.5815199717518 47.97210047512218) -120.581520  47.972100   \n",
       "2  -120.7448841870875 47.96732496805843) -120.744884  47.967325   \n",
       "3  -120.5812232399775 48.04473598140331) -120.581223  48.044736   \n",
       "4  -120.2604888608036 47.89597596325936) -120.260489  47.895976   \n",
       "\n",
       "                                     reverse_geocode    county_name  \\\n",
       "0                  Cache County, Utah, United States   Cache County   \n",
       "1           Chelan County, Washington, United States  Chelan County   \n",
       "2  Minnow Creek Trail, Chelan County, Washington,...  Chelan County   \n",
       "3  Pugh Ridge Trail #1438, Chelan County, Washing...  Chelan County   \n",
       "4           Chelan County, Washington, United States  Chelan County   \n",
       "\n",
       "        state  \n",
       "0        Utah  \n",
       "1  Washington  \n",
       "2  Washington  \n",
       "3  Washington  \n",
       "4  Washington  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wildfires_with_counties.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "1f30b443",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.224515Z",
     "start_time": "2021-05-21T21:51:50.220800Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['event_id', 'irwinid', 'incid_name', 'incid_type', 'map_id', 'map_prog',\n",
       "       'asmnt_type', 'burnbndac', 'burnbndlat', 'burnbndlon', 'ig_date',\n",
       "       'pre_id', 'post_id', 'perim_id', 'dnbr_offst', 'dnbr_stddv', 'nodata_t',\n",
       "       'incgreen_t', 'low_t', 'mod_t', 'high_t', 'comment', 'geometry',\n",
       "       'geometry2', 'lon', 'lat', 'reverse_geocode', 'county_name', 'state'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wildfires_with_counties.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "a413c260",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.228795Z",
     "start_time": "2021-05-21T21:51:50.225303Z"
    }
   },
   "outputs": [],
   "source": [
    "wildfires_with_counties.drop(['irwinid', 'dnbr_offst', 'dnbr_stddv', 'pre_id', 'post_id',\n",
    "                'map_prog', 'map_id', 'comment', 'geometry2'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d03e2865",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.231154Z",
     "start_time": "2021-05-21T21:51:50.229496Z"
    }
   },
   "outputs": [],
   "source": [
    "wildfires_with_counties.rename(columns = {'ig_date':'date'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "cc592ea0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.238148Z",
     "start_time": "2021-05-21T21:51:50.231895Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1588 entries, 0 to 1587\n",
      "Data columns (total 20 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   event_id         1588 non-null   object \n",
      " 1   incid_name       1588 non-null   object \n",
      " 2   incid_type       1588 non-null   object \n",
      " 3   asmnt_type       1588 non-null   object \n",
      " 4   burnbndac        1588 non-null   int64  \n",
      " 5   burnbndlat       1588 non-null   float64\n",
      " 6   burnbndlon       1588 non-null   float64\n",
      " 7   date             1588 non-null   int32  \n",
      " 8   perim_id         238 non-null    float64\n",
      " 9   nodata_t         1588 non-null   int64  \n",
      " 10  incgreen_t       1588 non-null   int64  \n",
      " 11  low_t            1588 non-null   int64  \n",
      " 12  mod_t            1588 non-null   int64  \n",
      " 13  high_t           1588 non-null   int64  \n",
      " 14  geometry         1588 non-null   object \n",
      " 15  lon              1588 non-null   float64\n",
      " 16  lat              1588 non-null   float64\n",
      " 17  reverse_geocode  1588 non-null   object \n",
      " 18  county_name      1585 non-null   object \n",
      " 19  state            1588 non-null   object \n",
      "dtypes: float64(5), int32(1), int64(6), object(8)\n",
      "memory usage: 242.0+ KB\n"
     ]
    }
   ],
   "source": [
    "wildfires_with_counties.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e0e99790",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.241359Z",
     "start_time": "2021-05-21T21:51:50.238825Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-52-558f65d1d115>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  wildfires_with_counties['county_name'][135] = 'Uintah County'\n",
      "<ipython-input-52-558f65d1d115>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  wildfires_with_counties['county_name'][649] = 'Uintah County'\n",
      "<ipython-input-52-558f65d1d115>:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  wildfires_with_counties['county_name'][1528] = 'Uintah County'\n"
     ]
    }
   ],
   "source": [
    "#manually impute missing counties based on incident name & lat/lon score\n",
    "wildfires_with_counties['county_name'][135] = 'Uintah County'\n",
    "wildfires_with_counties['county_name'][649] = 'Uintah County'\n",
    "wildfires_with_counties['county_name'][1528] = 'Uintah County'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc8f5874",
   "metadata": {},
   "source": [
    "## Merge wildfires df with smoke df\n",
    "## EDIT: first add lat/lon to smoke before merging\n",
    "Analyzing at county level or higher, but want lat/lon for every observation for mapping or potential clustering (clusters may be better than counties to group nearby observations)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5d417b2",
   "metadata": {},
   "source": [
    "### Adding lat/lon to smoke - geocodes from county_name\n",
    "Code to generate county_key is at end of notebook (had sidelined because thought no longer useful).  Starting with completed county_key here:\n",
    "\n",
    "Geocode based on county name AND STATE NAME.  For example, there is a Kings County CA and Kings County PA, so your first go at this did not pan out and you never even knew it until it was mapped out in Tableau and you had smoke measurements coming from the East coast."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8cd49ca7",
   "metadata": {},
   "source": [
    "## Smoke df: Need to make column with 'county_name, State' for geocoding\n",
    "Otherwise you get counties from East Coast with shared names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "440703fd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.404338Z",
     "start_time": "2021-05-21T21:51:50.242132Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CA    1829232\n",
       "WA     669898\n",
       "OR     469052\n",
       "ID     224217\n",
       "UT     177761\n",
       "AZ     177436\n",
       "NV     136140\n",
       "Name: state, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "smoke.state.value_counts() #yes filtered to what we want"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "e23002a8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.738272Z",
     "start_time": "2021-05-21T21:51:50.404978Z"
    }
   },
   "outputs": [],
   "source": [
    "smoke['county_state'] = smoke['county_name'] + ', ' + smoke['state']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "0b4cd934",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.743636Z",
     "start_time": "2021-05-21T21:51:50.738996Z"
    }
   },
   "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</th>\n",
       "      <th>statefp</th>\n",
       "      <th>countyfp</th>\n",
       "      <th>county_name</th>\n",
       "      <th>date</th>\n",
       "      <th>population</th>\n",
       "      <th>smoke_score</th>\n",
       "      <th>county_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>460</td>\n",
       "      <td>1</td>\n",
       "      <td>Gila County, AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "      <td>Gila County, AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>1301</td>\n",
       "      <td>1</td>\n",
       "      <td>Gila County, AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>1583</td>\n",
       "      <td>1</td>\n",
       "      <td>Gila County, AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>969</td>\n",
       "      <td>1</td>\n",
       "      <td>Gila County, AZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state  statefp  countyfp  county_name      date  population  smoke_score  \\\n",
       "0    AZ        4         7  Gila County  20100604         460            1   \n",
       "1    AZ        4         7  Gila County  20100604         821            1   \n",
       "2    AZ        4         7  Gila County  20100604        1301            1   \n",
       "3    AZ        4         7  Gila County  20100604        1583            1   \n",
       "4    AZ        4         7  Gila County  20100604         969            1   \n",
       "\n",
       "      county_state  \n",
       "0  Gila County, AZ  \n",
       "1  Gila County, AZ  \n",
       "2  Gila County, AZ  \n",
       "3  Gila County, AZ  \n",
       "4  Gila County, AZ  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "smoke.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "32c0b5c5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.953606Z",
     "start_time": "2021-05-21T21:51:50.744409Z"
    }
   },
   "outputs": [],
   "source": [
    "unique_locs = set(smoke.county_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "116f52cf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.956159Z",
     "start_time": "2021-05-21T21:51:50.954357Z"
    }
   },
   "outputs": [],
   "source": [
    "county_key = pd.DataFrame(unique_locs, columns=['county_state'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "38545dbb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.960590Z",
     "start_time": "2021-05-21T21:51:50.956807Z"
    }
   },
   "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>county_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>King County, WA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Kings County, CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         county_state\n",
       "26    King County, WA\n",
       "146  Kings County, CA"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_key.loc[county_key['county_state'].str.contains('King')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "3e1b8028",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.962681Z",
     "start_time": "2021-05-21T21:51:50.961334Z"
    }
   },
   "outputs": [],
   "source": [
    "# from geopy.geocoders import Nominatim\n",
    "# geolocator = Nominatim(user_agent = 'helen.meigs@gmail.com')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d9ec54a7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.964911Z",
     "start_time": "2021-05-21T21:51:50.963490Z"
    }
   },
   "outputs": [],
   "source": [
    "# %%time\n",
    "# county_key['lat']= county_key['county_state'].map(lambda x: geocoder.arcgis(x).latlng[0])\n",
    "# county_key['lon'] = county_key['county_state'].map(lambda x: geocoder.arcgis(x).latlng[1])\n",
    "\n",
    "# # wall time 6min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "5dd39717",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.967111Z",
     "start_time": "2021-05-21T21:51:50.965737Z"
    }
   },
   "outputs": [],
   "source": [
    "# county_key['county_name'] = county_key['county_state'].map(lambda x: x[:-4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "ad9f6e17",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.968959Z",
     "start_time": "2021-05-21T21:51:50.967865Z"
    }
   },
   "outputs": [],
   "source": [
    "# county_key.to_csv('../scratch_data/smoke_2000_16_county_key.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "6cc8d48e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.972531Z",
     "start_time": "2021-05-21T21:51:50.969641Z"
    }
   },
   "outputs": [],
   "source": [
    "county_key = pd.read_csv('../scratch_data/smoke_2000_16_county_key.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "63355c85",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:50.976656Z",
     "start_time": "2021-05-21T21:51:50.973191Z"
    }
   },
   "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>county_state</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>county_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Walla Walla County, WA</td>\n",
       "      <td>46.231254</td>\n",
       "      <td>-118.472687</td>\n",
       "      <td>Walla Walla County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>San Mateo County, CA</td>\n",
       "      <td>37.422133</td>\n",
       "      <td>-122.328630</td>\n",
       "      <td>San Mateo County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Placer County, CA</td>\n",
       "      <td>39.059137</td>\n",
       "      <td>-120.753630</td>\n",
       "      <td>Placer County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Modoc County, CA</td>\n",
       "      <td>41.576517</td>\n",
       "      <td>-120.736847</td>\n",
       "      <td>Modoc County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bear Lake County, ID</td>\n",
       "      <td>42.297151</td>\n",
       "      <td>-111.330000</td>\n",
       "      <td>Bear Lake County</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             county_state        lat         lon         county_name\n",
       "0  Walla Walla County, WA  46.231254 -118.472687  Walla Walla County\n",
       "1    San Mateo County, CA  37.422133 -122.328630    San Mateo County\n",
       "2       Placer County, CA  39.059137 -120.753630       Placer County\n",
       "3        Modoc County, CA  41.576517 -120.736847        Modoc County\n",
       "4    Bear Lake County, ID  42.297151 -111.330000    Bear Lake County"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_key.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "17d63d18",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:51.580947Z",
     "start_time": "2021-05-21T21:51:50.977328Z"
    }
   },
   "outputs": [],
   "source": [
    "smoke = pd.merge(smoke, county_key, on=['county_state', 'county_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "202efcd9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:14.409124Z",
     "start_time": "2021-05-21T21:52:14.403191Z"
    }
   },
   "outputs": [],
   "source": [
    "smoke.rename(columns={'lat':'lat_smo', 'lon':'lon_smo'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "1fe4e4ef",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:19.127731Z",
     "start_time": "2021-05-21T21:52:18.485159Z"
    }
   },
   "outputs": [],
   "source": [
    "smoke.drop(columns = ['statefp', 'countyfp', 'population','county_state'], axis=1, inplace=True)\n",
    "smoke.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "2a5166e6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:20.110765Z",
     "start_time": "2021-05-21T21:52:20.106254Z"
    }
   },
   "outputs": [],
   "source": [
    "smoke.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "85779e5c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:20.860905Z",
     "start_time": "2021-05-21T21:52:20.848173Z"
    }
   },
   "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</th>\n",
       "      <th>county_name</th>\n",
       "      <th>date</th>\n",
       "      <th>smoke_score</th>\n",
       "      <th>lat_smo</th>\n",
       "      <th>lon_smo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100604</td>\n",
       "      <td>1</td>\n",
       "      <td>33.799665</td>\n",
       "      <td>-110.811659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100606</td>\n",
       "      <td>1</td>\n",
       "      <td>33.799665</td>\n",
       "      <td>-110.811659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100624</td>\n",
       "      <td>1</td>\n",
       "      <td>33.799665</td>\n",
       "      <td>-110.811659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100627</td>\n",
       "      <td>1</td>\n",
       "      <td>33.799665</td>\n",
       "      <td>-110.811659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Gila County</td>\n",
       "      <td>20100713</td>\n",
       "      <td>1</td>\n",
       "      <td>33.799665</td>\n",
       "      <td>-110.811659</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state  county_name      date  smoke_score    lat_smo     lon_smo\n",
       "0    AZ  Gila County  20100604            1  33.799665 -110.811659\n",
       "1    AZ  Gila County  20100606            1  33.799665 -110.811659\n",
       "2    AZ  Gila County  20100624            1  33.799665 -110.811659\n",
       "3    AZ  Gila County  20100627            1  33.799665 -110.811659\n",
       "4    AZ  Gila County  20100713            1  33.799665 -110.811659"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "smoke.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1c7739f",
   "metadata": {},
   "source": [
    "## Back to merging wildfires with smoke df:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "e255c85c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:30.328848Z",
     "start_time": "2021-05-21T21:52:30.306917Z"
    }
   },
   "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>event_id</th>\n",
       "      <th>incid_name</th>\n",
       "      <th>incid_type</th>\n",
       "      <th>asmnt_type</th>\n",
       "      <th>burnbndac</th>\n",
       "      <th>burnbndlat</th>\n",
       "      <th>burnbndlon</th>\n",
       "      <th>date</th>\n",
       "      <th>perim_id</th>\n",
       "      <th>nodata_t</th>\n",
       "      <th>incgreen_t</th>\n",
       "      <th>low_t</th>\n",
       "      <th>mod_t</th>\n",
       "      <th>high_t</th>\n",
       "      <th>geometry</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "      <th>reverse_geocode</th>\n",
       "      <th>county_name</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UT4176411148820121004</td>\n",
       "      <td>HELLS HOLLOW</td>\n",
       "      <td>Prescribed Fire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>4758</td>\n",
       "      <td>41.766</td>\n",
       "      <td>-111.507</td>\n",
       "      <td>20121004</td>\n",
       "      <td>7.038031e+14</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>113</td>\n",
       "      <td>345</td>\n",
       "      <td>616</td>\n",
       "      <td>POINT (-111.507983362626 41.75847249141982)</td>\n",
       "      <td>-111.507983</td>\n",
       "      <td>41.758472</td>\n",
       "      <td>Cache County, Utah, United States</td>\n",
       "      <td>Cache County</td>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA4796812059020120908</td>\n",
       "      <td>KLONE PEAK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1634</td>\n",
       "      <td>47.972</td>\n",
       "      <td>-120.582</td>\n",
       "      <td>20120908</td>\n",
       "      <td>7.045027e+14</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>250</td>\n",
       "      <td>460</td>\n",
       "      <td>POINT (-120.5815199717518 47.97210047512218)</td>\n",
       "      <td>-120.581520</td>\n",
       "      <td>47.972100</td>\n",
       "      <td>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WA4797012073420120908</td>\n",
       "      <td>BASALT</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1705</td>\n",
       "      <td>47.967</td>\n",
       "      <td>-120.746</td>\n",
       "      <td>20120908</td>\n",
       "      <td>7.045027e+14</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>60</td>\n",
       "      <td>250</td>\n",
       "      <td>500</td>\n",
       "      <td>POINT (-120.7448841870875 47.96732496805843)</td>\n",
       "      <td>-120.744884</td>\n",
       "      <td>47.967325</td>\n",
       "      <td>Minnow Creek Trail, Chelan County, Washington,...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WA4805112058820120908</td>\n",
       "      <td>PYRAMID</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1962</td>\n",
       "      <td>48.044</td>\n",
       "      <td>-120.581</td>\n",
       "      <td>20120908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>120</td>\n",
       "      <td>220</td>\n",
       "      <td>530</td>\n",
       "      <td>POINT (-120.5812232399775 48.04473598140331)</td>\n",
       "      <td>-120.581223</td>\n",
       "      <td>48.044736</td>\n",
       "      <td>Pugh Ridge Trail #1438, Chelan County, Washing...</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WA4788612026820120908</td>\n",
       "      <td>FIRST CREEK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>1434</td>\n",
       "      <td>47.895</td>\n",
       "      <td>-120.259</td>\n",
       "      <td>20120908</td>\n",
       "      <td>7.045027e+14</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>200</td>\n",
       "      <td>460</td>\n",
       "      <td>POINT (-120.2604888608036 47.89597596325936)</td>\n",
       "      <td>-120.260489</td>\n",
       "      <td>47.895976</td>\n",
       "      <td>Chelan County, Washington, United States</td>\n",
       "      <td>Chelan County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                event_id    incid_name       incid_type asmnt_type  burnbndac  \\\n",
       "0  UT4176411148820121004  HELLS HOLLOW  Prescribed Fire   Extended       4758   \n",
       "1  WA4796812059020120908    KLONE PEAK         Wildfire   Extended       1634   \n",
       "2  WA4797012073420120908        BASALT         Wildfire   Extended       1705   \n",
       "3  WA4805112058820120908       PYRAMID         Wildfire   Extended       1962   \n",
       "4  WA4788612026820120908   FIRST CREEK         Wildfire   Extended       1434   \n",
       "\n",
       "   burnbndlat  burnbndlon      date      perim_id  nodata_t  incgreen_t  \\\n",
       "0      41.766    -111.507  20121004  7.038031e+14      -970        -150   \n",
       "1      47.972    -120.582  20120908  7.045027e+14      -970        -150   \n",
       "2      47.967    -120.746  20120908  7.045027e+14      -970        -150   \n",
       "3      48.044    -120.581  20120908           NaN      -970        -150   \n",
       "4      47.895    -120.259  20120908  7.045027e+14      -970        -150   \n",
       "\n",
       "   low_t  mod_t  high_t                                      geometry  \\\n",
       "0    113    345     616   POINT (-111.507983362626 41.75847249141982)   \n",
       "1     50    250     460  POINT (-120.5815199717518 47.97210047512218)   \n",
       "2     60    250     500  POINT (-120.7448841870875 47.96732496805843)   \n",
       "3    120    220     530  POINT (-120.5812232399775 48.04473598140331)   \n",
       "4     50    200     460  POINT (-120.2604888608036 47.89597596325936)   \n",
       "\n",
       "          lon        lat                                    reverse_geocode  \\\n",
       "0 -111.507983  41.758472                  Cache County, Utah, United States   \n",
       "1 -120.581520  47.972100           Chelan County, Washington, United States   \n",
       "2 -120.744884  47.967325  Minnow Creek Trail, Chelan County, Washington,...   \n",
       "3 -120.581223  48.044736  Pugh Ridge Trail #1438, Chelan County, Washing...   \n",
       "4 -120.260489  47.895976           Chelan County, Washington, United States   \n",
       "\n",
       "     county_name       state  \n",
       "0   Cache County        Utah  \n",
       "1  Chelan County  Washington  \n",
       "2  Chelan County  Washington  \n",
       "3  Chelan County  Washington  \n",
       "4  Chelan County  Washington  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wildfires_with_counties.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "343f8fbe",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:43.910936Z",
     "start_time": "2021-05-21T21:52:43.906172Z"
    }
   },
   "outputs": [],
   "source": [
    "def rename_latlon(df, name):\n",
    "    df.rename(columns = {'lat':f'lat_{name}', 'lon':f'lon_{name}'}, inplace=True)\n",
    "    \n",
    "    \n",
    "rename_latlon(smoke, 'smo')\n",
    "rename_latlon(wildfires_with_counties, 'wf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "3687e8fa",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:45.977855Z",
     "start_time": "2021-05-21T21:52:45.971588Z"
    }
   },
   "outputs": [],
   "source": [
    "state_dict = {'Idaho':'ID', 'California': 'CA', 'Arizona':'AZ', 'Oregon':'OR', \n",
    "              'Washington':'WA', 'Nevada':'NV', 'Montana':'MT', 'Utah':'UT',\n",
    "              'Wyoming':'WY', 'New Mexico':'NM', 'Colorado':'CO'}\n",
    "\n",
    "wildfires_with_counties['state_abbrev'] = wildfires_with_counties['state'].map(state_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "0f7ff186",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:46.437199Z",
     "start_time": "2021-05-21T21:52:46.435549Z"
    }
   },
   "outputs": [],
   "source": [
    "wildfires_with_counties.rename(columns = {'state':'state_full', 'state_abbrev':'state',\n",
    "                                         'reverse_geocode':'reverse_geocode_fire_ig'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "8ae0bfa2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:46.981771Z",
     "start_time": "2021-05-21T21:52:46.968711Z"
    }
   },
   "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>event_id</th>\n",
       "      <th>incid_name</th>\n",
       "      <th>incid_type</th>\n",
       "      <th>asmnt_type</th>\n",
       "      <th>burnbndac</th>\n",
       "      <th>burnbndlat</th>\n",
       "      <th>burnbndlon</th>\n",
       "      <th>date</th>\n",
       "      <th>perim_id</th>\n",
       "      <th>nodata_t</th>\n",
       "      <th>...</th>\n",
       "      <th>low_t</th>\n",
       "      <th>mod_t</th>\n",
       "      <th>high_t</th>\n",
       "      <th>geometry</th>\n",
       "      <th>lon_wf</th>\n",
       "      <th>lat_wf</th>\n",
       "      <th>reverse_geocode_fire_ig</th>\n",
       "      <th>county_name</th>\n",
       "      <th>state_full</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1587</th>\n",
       "      <td>OR4309711823520110825</td>\n",
       "      <td>DSL COMPLEX</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Initial</td>\n",
       "      <td>5976</td>\n",
       "      <td>43.068</td>\n",
       "      <td>-118.234</td>\n",
       "      <td>20110825</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-970</td>\n",
       "      <td>...</td>\n",
       "      <td>40</td>\n",
       "      <td>9999</td>\n",
       "      <td>9999</td>\n",
       "      <td>POINT (-118.2343864935406 43.06794965014317)</td>\n",
       "      <td>-118.234386</td>\n",
       "      <td>43.06795</td>\n",
       "      <td>Harney County, Oregon, United States</td>\n",
       "      <td>Harney County</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>OR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   event_id   incid_name incid_type asmnt_type  burnbndac  \\\n",
       "1587  OR4309711823520110825  DSL COMPLEX   Wildfire    Initial       5976   \n",
       "\n",
       "      burnbndlat  burnbndlon      date  perim_id  nodata_t  ...  low_t  mod_t  \\\n",
       "1587      43.068    -118.234  20110825       NaN      -970  ...     40   9999   \n",
       "\n",
       "      high_t                                      geometry      lon_wf  \\\n",
       "1587    9999  POINT (-118.2343864935406 43.06794965014317) -118.234386   \n",
       "\n",
       "        lat_wf               reverse_geocode_fire_ig    county_name  \\\n",
       "1587  43.06795  Harney County, Oregon, United States  Harney County   \n",
       "\n",
       "     state_full state  \n",
       "1587     Oregon    OR  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wildfires_with_counties.tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "9ae19db9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:47.550097Z",
     "start_time": "2021-05-21T21:52:47.543919Z"
    }
   },
   "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</th>\n",
       "      <th>county_name</th>\n",
       "      <th>date</th>\n",
       "      <th>smoke_score</th>\n",
       "      <th>lat_smo</th>\n",
       "      <th>lon_smo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>66592</th>\n",
       "      <td>NV</td>\n",
       "      <td>White Pine County</td>\n",
       "      <td>20160919</td>\n",
       "      <td>1</td>\n",
       "      <td>39.442161</td>\n",
       "      <td>-114.901646</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      state        county_name      date  smoke_score    lat_smo     lon_smo\n",
       "66592    NV  White Pine County  20160919            1  39.442161 -114.901646"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "smoke.tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "61ee4c28",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:49.583938Z",
     "start_time": "2021-05-21T21:52:49.498663Z"
    }
   },
   "outputs": [],
   "source": [
    "inner_fires_smoke = pd.merge(smoke, wildfires_with_counties, on = ['county_name','state', 'date'], how='inner')\n",
    "keepfires_smoke = pd.merge(smoke, wildfires_with_counties, on = ['county_name', 'state', 'date'], how='right')\n",
    "outer_fires_smoke = pd.merge(smoke, wildfires_with_counties, on=['county_name', 'state', 'date'], how = 'outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90df9673",
   "metadata": {},
   "source": [
    "See len of each join: inner join drops 100 fires (of 1588 - no good).  Maintaining all fires gives 2250 datapoints, but we do want pollution data for days in between fires, so the outer join of 83,000 may be what we have to work with. Especially because this is only fire IGNITION DATES, and we absolutely want to look at days immediately post-fire, and how they compare to baseline (long duration away from fire).  Exporting outer join for EDA, the limited versions are option for modeling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "6a91ed6b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:52.422832Z",
     "start_time": "2021-05-21T21:52:52.418455Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "66593\n",
      "1588\n",
      "68181\n",
      "67561 2006 1197\n"
     ]
    }
   ],
   "source": [
    "print(len(smoke))\n",
    "print(len(wildfires_with_counties))\n",
    "print(len(smoke) + len(wildfires_with_counties))\n",
    "print(len(outer_fires_smoke), len(keepfires_smoke), len(inner_fires_smoke))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "721895b5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:54.349771Z",
     "start_time": "2021-05-21T21:52:54.316641Z"
    }
   },
   "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</th>\n",
       "      <th>county_name</th>\n",
       "      <th>date</th>\n",
       "      <th>smoke_score</th>\n",
       "      <th>lat_smo</th>\n",
       "      <th>lon_smo</th>\n",
       "      <th>event_id</th>\n",
       "      <th>incid_name</th>\n",
       "      <th>incid_type</th>\n",
       "      <th>asmnt_type</th>\n",
       "      <th>...</th>\n",
       "      <th>nodata_t</th>\n",
       "      <th>incgreen_t</th>\n",
       "      <th>low_t</th>\n",
       "      <th>mod_t</th>\n",
       "      <th>high_t</th>\n",
       "      <th>geometry</th>\n",
       "      <th>lon_wf</th>\n",
       "      <th>lat_wf</th>\n",
       "      <th>reverse_geocode_fire_ig</th>\n",
       "      <th>state_full</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Graham County</td>\n",
       "      <td>20110626</td>\n",
       "      <td>1</td>\n",
       "      <td>32.932678</td>\n",
       "      <td>-109.887499</td>\n",
       "      <td>AZ3307311037720110626</td>\n",
       "      <td>STANLEY</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>-10</td>\n",
       "      <td>102</td>\n",
       "      <td>250</td>\n",
       "      <td>POINT (-110.3297109946449 33.05281063846503)</td>\n",
       "      <td>-110.329711</td>\n",
       "      <td>33.052811</td>\n",
       "      <td>Graham County, Arizona, United States</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Graham County</td>\n",
       "      <td>20120316</td>\n",
       "      <td>1</td>\n",
       "      <td>32.932678</td>\n",
       "      <td>-109.887499</td>\n",
       "      <td>AZ3328910997820120316</td>\n",
       "      <td>HOOKER</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Initial (SS)</td>\n",
       "      <td>...</td>\n",
       "      <td>9999</td>\n",
       "      <td>9999</td>\n",
       "      <td>5</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>POINT (-109.9572221649203 33.2835725224978)</td>\n",
       "      <td>-109.957222</td>\n",
       "      <td>33.283573</td>\n",
       "      <td>Graham County, Arizona, United States</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Graham County</td>\n",
       "      <td>20130513</td>\n",
       "      <td>1</td>\n",
       "      <td>32.932678</td>\n",
       "      <td>-109.887499</td>\n",
       "      <td>AZ3343510971420130513</td>\n",
       "      <td>PINE SALT</td>\n",
       "      <td>Prescribed Fire</td>\n",
       "      <td>Initial</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>330</td>\n",
       "      <td>9999</td>\n",
       "      <td>POINT (-109.7197042422813 33.33716745372745)</td>\n",
       "      <td>-109.719704</td>\n",
       "      <td>33.337167</td>\n",
       "      <td>Black River Road, Graham County, Arizona, Unit...</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Graham County</td>\n",
       "      <td>20130513</td>\n",
       "      <td>3</td>\n",
       "      <td>32.932678</td>\n",
       "      <td>-109.887499</td>\n",
       "      <td>AZ3343510971420130513</td>\n",
       "      <td>PINE SALT</td>\n",
       "      <td>Prescribed Fire</td>\n",
       "      <td>Initial</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>330</td>\n",
       "      <td>9999</td>\n",
       "      <td>POINT (-109.7197042422813 33.33716745372745)</td>\n",
       "      <td>-109.719704</td>\n",
       "      <td>33.337167</td>\n",
       "      <td>Black River Road, Graham County, Arizona, Unit...</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Graham County</td>\n",
       "      <td>20130616</td>\n",
       "      <td>1</td>\n",
       "      <td>32.932678</td>\n",
       "      <td>-109.887499</td>\n",
       "      <td>AZ3339910989520130616</td>\n",
       "      <td>CREEK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Initial</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>330</td>\n",
       "      <td>9999</td>\n",
       "      <td>POINT (-109.841033210156 33.39446296079526)</td>\n",
       "      <td>-109.841033</td>\n",
       "      <td>33.394463</td>\n",
       "      <td>Graham County, Arizona, United States</td>\n",
       "      <td>Arizona</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1192</th>\n",
       "      <td>NV</td>\n",
       "      <td>White Pine County</td>\n",
       "      <td>20130701</td>\n",
       "      <td>1</td>\n",
       "      <td>39.442161</td>\n",
       "      <td>-114.901646</td>\n",
       "      <td>NV4003011445220130701</td>\n",
       "      <td>NORTH CREEK</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>70</td>\n",
       "      <td>218</td>\n",
       "      <td>410</td>\n",
       "      <td>POINT (-114.4792105431762 40.01817586298156)</td>\n",
       "      <td>-114.479211</td>\n",
       "      <td>40.018176</td>\n",
       "      <td>White Pine County, Nevada, United States</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1193</th>\n",
       "      <td>NV</td>\n",
       "      <td>White Pine County</td>\n",
       "      <td>20140716</td>\n",
       "      <td>1</td>\n",
       "      <td>39.442161</td>\n",
       "      <td>-114.901646</td>\n",
       "      <td>NV3924811413320140716</td>\n",
       "      <td>HAMPTON</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>319</td>\n",
       "      <td>610</td>\n",
       "      <td>POINT (-114.1265650310761 39.27945844585704)</td>\n",
       "      <td>-114.126565</td>\n",
       "      <td>39.279458</td>\n",
       "      <td>White Pine County, Nevada, United States</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1194</th>\n",
       "      <td>NV</td>\n",
       "      <td>White Pine County</td>\n",
       "      <td>20140724</td>\n",
       "      <td>2</td>\n",
       "      <td>39.442161</td>\n",
       "      <td>-114.901646</td>\n",
       "      <td>NV4000011456620140724</td>\n",
       "      <td>SAMPSON</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>324</td>\n",
       "      <td>620</td>\n",
       "      <td>POINT (-114.5689564363166 40.00122744315588)</td>\n",
       "      <td>-114.568956</td>\n",
       "      <td>40.001227</td>\n",
       "      <td>White Pine County, Nevada, United States</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1195</th>\n",
       "      <td>NV</td>\n",
       "      <td>White Pine County</td>\n",
       "      <td>20160808</td>\n",
       "      <td>1</td>\n",
       "      <td>39.442161</td>\n",
       "      <td>-114.901646</td>\n",
       "      <td>NV3904811430620160808</td>\n",
       "      <td>STRAWBERRY</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>15</td>\n",
       "      <td>290</td>\n",
       "      <td>520</td>\n",
       "      <td>POINT (-114.276930874846 39.05990748074837)</td>\n",
       "      <td>-114.276931</td>\n",
       "      <td>39.059907</td>\n",
       "      <td>National Forest Development Road 456, White Pi...</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1196</th>\n",
       "      <td>NV</td>\n",
       "      <td>White Pine County</td>\n",
       "      <td>20160808</td>\n",
       "      <td>1</td>\n",
       "      <td>39.442161</td>\n",
       "      <td>-114.901646</td>\n",
       "      <td>NV3996611562620160808</td>\n",
       "      <td>OVERLAND</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>Extended</td>\n",
       "      <td>...</td>\n",
       "      <td>-970</td>\n",
       "      <td>-150</td>\n",
       "      <td>50</td>\n",
       "      <td>108</td>\n",
       "      <td>220</td>\n",
       "      <td>POINT (-115.5633851016411 39.99251018200475)</td>\n",
       "      <td>-115.563385</td>\n",
       "      <td>39.992510</td>\n",
       "      <td>White Pine County, Nevada, United States</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1197 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     state        county_name      date  smoke_score    lat_smo     lon_smo  \\\n",
       "0       AZ      Graham County  20110626            1  32.932678 -109.887499   \n",
       "1       AZ      Graham County  20120316            1  32.932678 -109.887499   \n",
       "2       AZ      Graham County  20130513            1  32.932678 -109.887499   \n",
       "3       AZ      Graham County  20130513            3  32.932678 -109.887499   \n",
       "4       AZ      Graham County  20130616            1  32.932678 -109.887499   \n",
       "...    ...                ...       ...          ...        ...         ...   \n",
       "1192    NV  White Pine County  20130701            1  39.442161 -114.901646   \n",
       "1193    NV  White Pine County  20140716            1  39.442161 -114.901646   \n",
       "1194    NV  White Pine County  20140724            2  39.442161 -114.901646   \n",
       "1195    NV  White Pine County  20160808            1  39.442161 -114.901646   \n",
       "1196    NV  White Pine County  20160808            1  39.442161 -114.901646   \n",
       "\n",
       "                   event_id   incid_name       incid_type    asmnt_type  ...  \\\n",
       "0     AZ3307311037720110626      STANLEY         Wildfire      Extended  ...   \n",
       "1     AZ3328910997820120316       HOOKER         Wildfire  Initial (SS)  ...   \n",
       "2     AZ3343510971420130513    PINE SALT  Prescribed Fire       Initial  ...   \n",
       "3     AZ3343510971420130513    PINE SALT  Prescribed Fire       Initial  ...   \n",
       "4     AZ3339910989520130616        CREEK         Wildfire       Initial  ...   \n",
       "...                     ...          ...              ...           ...  ...   \n",
       "1192  NV4003011445220130701  NORTH CREEK         Wildfire      Extended  ...   \n",
       "1193  NV3924811413320140716      HAMPTON         Wildfire      Extended  ...   \n",
       "1194  NV4000011456620140724      SAMPSON         Wildfire      Extended  ...   \n",
       "1195  NV3904811430620160808   STRAWBERRY         Wildfire      Extended  ...   \n",
       "1196  NV3996611562620160808     OVERLAND         Wildfire      Extended  ...   \n",
       "\n",
       "      nodata_t  incgreen_t  low_t  mod_t  high_t  \\\n",
       "0         -970        -150    -10    102     250   \n",
       "1         9999        9999      5  -9999   -9999   \n",
       "2         -970        -150     50    330    9999   \n",
       "3         -970        -150     50    330    9999   \n",
       "4         -970        -150     50    330    9999   \n",
       "...        ...         ...    ...    ...     ...   \n",
       "1192      -970        -150     70    218     410   \n",
       "1193      -970        -150     50    319     610   \n",
       "1194      -970        -150     50    324     620   \n",
       "1195      -970        -150     15    290     520   \n",
       "1196      -970        -150     50    108     220   \n",
       "\n",
       "                                          geometry      lon_wf     lat_wf  \\\n",
       "0     POINT (-110.3297109946449 33.05281063846503) -110.329711  33.052811   \n",
       "1      POINT (-109.9572221649203 33.2835725224978) -109.957222  33.283573   \n",
       "2     POINT (-109.7197042422813 33.33716745372745) -109.719704  33.337167   \n",
       "3     POINT (-109.7197042422813 33.33716745372745) -109.719704  33.337167   \n",
       "4      POINT (-109.841033210156 33.39446296079526) -109.841033  33.394463   \n",
       "...                                            ...         ...        ...   \n",
       "1192  POINT (-114.4792105431762 40.01817586298156) -114.479211  40.018176   \n",
       "1193  POINT (-114.1265650310761 39.27945844585704) -114.126565  39.279458   \n",
       "1194  POINT (-114.5689564363166 40.00122744315588) -114.568956  40.001227   \n",
       "1195   POINT (-114.276930874846 39.05990748074837) -114.276931  39.059907   \n",
       "1196  POINT (-115.5633851016411 39.99251018200475) -115.563385  39.992510   \n",
       "\n",
       "                                reverse_geocode_fire_ig state_full  \n",
       "0                 Graham County, Arizona, United States    Arizona  \n",
       "1                 Graham County, Arizona, United States    Arizona  \n",
       "2     Black River Road, Graham County, Arizona, Unit...    Arizona  \n",
       "3     Black River Road, Graham County, Arizona, Unit...    Arizona  \n",
       "4                 Graham County, Arizona, United States    Arizona  \n",
       "...                                                 ...        ...  \n",
       "1192           White Pine County, Nevada, United States     Nevada  \n",
       "1193           White Pine County, Nevada, United States     Nevada  \n",
       "1194           White Pine County, Nevada, United States     Nevada  \n",
       "1195  National Forest Development Road 456, White Pi...     Nevada  \n",
       "1196           White Pine County, Nevada, United States     Nevada  \n",
       "\n",
       "[1197 rows x 24 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inner_fires_smoke"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "ef02778a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:52:55.245317Z",
     "start_time": "2021-05-21T21:52:55.205966Z"
    }
   },
   "outputs": [],
   "source": [
    "outer_fires_smoke.drop_duplicates(inplace=True)\n",
    "outer_fires_smoke.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0261e5ec",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-21T21:51:51.694445Z",
     "start_time": "2021-05-21T21:51:47.893Z"
    }
   },
   "outputs": [],
   "source": [
    "#outer_fires_smoke.to_csv('../scratch_data/MTBS_merge_smoke_inProgress.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.2"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}