02-preprocessing-FINAL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "eaf2ed64-1698-43f6-a27c-4abc13240982",
   "metadata": {},
   "source": [
    "## Processing and Cleaning Fire Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2001e355-34bb-4b82-b4c9-beaf179a6fcc",
   "metadata": {},
   "source": [
    "The fire data includes a feature called ```UNIT_ID```, which is very similar to county, but not exactly the same because two counties are occasionally grouped together in the same unit.  Additionally, the abbreviations used in ```UNIT_ID``` are not the same as the standard three letter abbreviations used elsewhere to identify California counties.  So, a dictionary mapping these abbreviations to county names had to be manually constructed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "b3a71581-3b54-486f-856f-1c7a8a1a012c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import ast\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "7e919380-6931-4438-8e2b-32ba37bdd3fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "fire = pd.read_csv('./data/raw_fires.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "47ec2ae6-6f12-4868-9d02-1179b4783b92",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_dict = {'NEU' : 'Yuba', 'BTU': 'Butte', 'CZU': 'San Mateo - Santa Cruz', 'MEU': 'Mendocino', 'HUU': 'Humboldt', 'TGU': 'Tehama - Glenn', 'ORC': 'Orange County', 'SKU': 'Siskiyou', 'TCU': 'Tuolumne - Calaveras',\n",
    "       'KRN': 'Kern', 'CND': 'Kern', 'MMU': 'Madera - Mariposa', 'SHF': 'Trinity', 'SHU': 'Shasta - Trinity', 'SBC': 'Santa Barbara', 'BDU': 'San Bernardino', 'SLU': 'San Luis Obispo', 'TUU': 'Tulare', \n",
    "       'AEU': 'Amador - El Dorado', 'FKU': 'Fresno - Kings', 'BEU': 'Monterey - San Benito', 'VNC': 'Ventura', 'SCU': 'Santa Clara', 'MVU': 'San Diego', 'RRU': 'Riverside', 'LAC': 'Los Angeles', 'ANF': 'Los Angeles',\n",
    "       'LNU': 'Sonoma Lake - Napa', 'MRN': 'Marin', 'MCP': 'San Diego', 'INF': 'Inyo', 'MDF': 'Modoc', 'TNF': 'Yuba', 'PNF': 'Plumas', 'LPF': 'Santa Barbara', 'KNF': 'Siksiyou',\n",
    "       'SQF': 'Fresno', 'ENF': 'El Dorado', 'SNF': 'Mariposa', 'HIA': 'Humboldt', 'MNF': 'Lake', 'STF': 'Tuolumne', 'BDF': 'San Bernadino', 'CNF': 'San Diego', 'MNP': 'San Bernardino',\n",
    "       'LNF': 'Lassen', 'CDD': 'Riverside', 'LMU': 'Lassen - Modoc', 'SRF': 'Del Norte', 'HTF': 'Humboldt', 'YNP': 'Mariposa', 'BNP': 'Siskiyou', 'KNP': 'Tulare', 'CNP': 'Ventura',\n",
    "       'RNP': 'Marin', 'CCD': 'Los Angeles', 'NOD': 'Shasta', 'BBD': 'Kern', 'TMU': 'Placer', 'TOI': 'Mono', 'SNU': 'Sonoma', 'DVP': 'Inyo',\n",
    "       'AFV': 'Santa Barbara', 'CRB': 'San Luis Obispo', 'SMP': 'Los Angeles', 'LDF': 'Los Angeles', 'FNF': 'Klamath', 'WED': 'Siskiyou', 'KRR': 'Kern',\n",
    "       'SWR': 'Sacramento', 'LUR': 'Merced', 'BRR': 'Kern', 'HPR': 'Ventura', 'PLR': 'Tulare', 'SOR': 'Imperial', 'TNR': 'San Diego', 'SJR': 'San Joaquin', 'CLR': 'Modoc',\n",
    "       'LKR': 'Klamath', 'RWP': 'Humboldt', 'LNP': 'Lassen', 'JTP': 'Riverside', 'GNP': 'Marin', 'PIP': 'San Benito', 'VLJ': 'Solano', 'RRS': 'Siskiyou'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "58a721c1-c6c4-475a-a55a-dfb16b157281",
   "metadata": {},
   "outputs": [],
   "source": [
    "fire['UNIT_ID'] = fire['UNIT_ID'].map(county_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8caea08-7996-4394-b75b-4015d720b523",
   "metadata": {},
   "source": [
    "Since the weather data we obtained only contains information back to July 1, 2008, we drop the fire data before this date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "933aed82-cb85-4118-be12-f426c0b59978",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4304 entries, 0 to 21317\n",
      "Data columns (total 18 columns):\n",
      " #   Column        Non-Null Count  Dtype              \n",
      "---  ------        --------------  -----              \n",
      " 0   OBJECTID      4304 non-null   int64              \n",
      " 1   YEAR_         4304 non-null   float64            \n",
      " 2   STATE         4301 non-null   object             \n",
      " 3   AGENCY        4299 non-null   object             \n",
      " 4   UNIT_ID       4279 non-null   object             \n",
      " 5   FIRE_NAME     4287 non-null   object             \n",
      " 6   INC_NUM       4078 non-null   object             \n",
      " 7   ALARM_DATE    4304 non-null   datetime64[ns, UTC]\n",
      " 8   CONT_DATE     4249 non-null   object             \n",
      " 9   CAUSE         4273 non-null   float64            \n",
      " 10  COMMENTS      2052 non-null   object             \n",
      " 11  REPORT_AC     3659 non-null   float64            \n",
      " 12  GIS_ACRES     4297 non-null   float64            \n",
      " 13  C_METHOD      4293 non-null   float64            \n",
      " 14  OBJECTIVE     4203 non-null   float64            \n",
      " 15  FIRE_NUM      635 non-null    object             \n",
      " 16  SHAPE_Length  4304 non-null   float64            \n",
      " 17  SHAPE_Area    4304 non-null   float64            \n",
      "dtypes: datetime64[ns, UTC](1), float64(8), int64(1), object(8)\n",
      "memory usage: 638.9+ KB\n"
     ]
    }
   ],
   "source": [
    "fire['ALARM_DATE'] = pd.to_datetime(fire.ALARM_DATE, errors = 'coerce')\n",
    "fire_recent = fire.loc[fire.ALARM_DATE > '2008-07-01', :]\n",
    "fire_recent.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c6170bd-794a-4200-a707-2c50f3eafe7d",
   "metadata": {},
   "source": [
    "From this data we only need the size of the fire, when it occurred, it's cause, and where it is located, so everything else is dropped."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5684c0b9-9438-4bbe-9ae9-9c10289fea1e",
   "metadata": {},
   "source": [
    "Where the ```UNIT_ID``` contained two counties we arbitrarily select the second so that we will not have merge issues when we join this data to the weather data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "c82c9105-7195-4b61-897e-3be2e1030118",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIT_ID</th>\n",
       "      <th>FIRE_NAME</th>\n",
       "      <th>ALARM_DATE</th>\n",
       "      <th>CAUSE</th>\n",
       "      <th>GIS_ACRES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>NELSON</td>\n",
       "      <td>2020-06-18 00:00:00+00:00</td>\n",
       "      <td>11.0</td>\n",
       "      <td>109.60250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>AMORUSO</td>\n",
       "      <td>2020-06-01 00:00:00+00:00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>685.58502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>ATHENS</td>\n",
       "      <td>2020-08-10 00:00:00+00:00</td>\n",
       "      <td>14.0</td>\n",
       "      <td>27.30048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>FLEMING</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.93155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>MELANESE</td>\n",
       "      <td>2020-04-14 00:00:00+00:00</td>\n",
       "      <td>18.0</td>\n",
       "      <td>10.31596</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  UNIT_ID FIRE_NAME                ALARM_DATE  CAUSE  GIS_ACRES\n",
       "0    Yuba    NELSON 2020-06-18 00:00:00+00:00   11.0  109.60250\n",
       "1    Yuba   AMORUSO 2020-06-01 00:00:00+00:00    2.0  685.58502\n",
       "2    Yuba    ATHENS 2020-08-10 00:00:00+00:00   14.0   27.30048\n",
       "3    Yuba   FLEMING 2020-03-31 00:00:00+00:00    9.0   12.93155\n",
       "4    Yuba  MELANESE 2020-04-14 00:00:00+00:00   18.0   10.31596"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fire_recent.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac479297-4315-449b-9f3b-2b9f53fefe37",
   "metadata": {},
   "outputs": [],
   "source": [
    "fire_recent.to_csv('./data/fire_data.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdf52e0a-7ff3-46e4-baa1-132ce4782dbf",
   "metadata": {},
   "source": [
    "## Processing and Cleaning Weather Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bac989e7-f842-44a6-bbc6-46c5d5b9ec81",
   "metadata": {},
   "source": [
    "Import the California county data in order add county information to the weather data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "8549cadb-dc80-4c1e-9a4f-e0c95e7f6d56",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jeffw\\anaconda3\\envs\\dsi\\lib\\site-packages\\pandas\\core\\frame.py:5034: 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",
      "  return super().rename(\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>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sierra County</td>\n",
       "      <td>39.576925</td>\n",
       "      <td>-120.521993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sacramento County</td>\n",
       "      <td>38.450011</td>\n",
       "      <td>-121.340441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Santa Barbara County</td>\n",
       "      <td>34.537057</td>\n",
       "      <td>-120.039973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Calaveras County</td>\n",
       "      <td>38.183900</td>\n",
       "      <td>-120.561442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ventura County</td>\n",
       "      <td>34.358742</td>\n",
       "      <td>-119.133143</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name        lat         lon\n",
       "0         Sierra County  39.576925 -120.521993\n",
       "1     Sacramento County  38.450011 -121.340441\n",
       "2  Santa Barbara County  34.537057 -120.039973\n",
       "3      Calaveras County  38.183900 -120.561442\n",
       "4        Ventura County  34.358742 -119.133143"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather = pd.read_csv('./data/clean_daily_weather.csv')\n",
    "counties_long = pd.read_csv('./data/CA_Counties_Location.csv')\n",
    "counties = counties_long[['NAMELSAD', 'INTPTLAT', 'INTPTLON']]\n",
    "counties.rename(columns = {'NAMELSAD': 'name', 'INTPTLAT': 'lat', 'INTPTLON': 'lon'}, inplace = True)\n",
    "counties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a967ebf9-4daa-4df8-b71b-7df083a23a2c",
   "metadata": {},
   "source": [
    "Merge the county data to the weather data using latitude and longitude."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cbc290c-b5c4-42f1-8556-c0cec44b6554",
   "metadata": {},
   "outputs": [],
   "source": [
    "counties = counties.round(2)\n",
    "w_c = pd.merge(weather, counties, left_on = ['lat', 'long'], right_on = ['lat', 'lon'])\n",
    "w_c.drop(columns = ['lon'], inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67aae19e-e10f-413e-8ecc-b9c56a326a61",
   "metadata": {},
   "source": [
    "At this point our weather data has information for every day of every month of every year since July 1, 2008 in every county in California.  This data is too granular for our model, so we apply a county and date mask to the dataframe for every combination of day and county in order to find monthly average for all of our weather statistics.  Then this data is appended to a new data frame named ```out```.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f1bd49a-220f-45ac-91bf-dac46f2bba80",
   "metadata": {},
   "outputs": [],
   "source": [
    "## THIS CELL TAKES 22 HOURS TO RUN ##\n",
    "\n",
    "feature_list = ['maxtempF', 'mintempF', 'avgtempF', 'totalSnow_cm', 'sunHour', 'precip', 'humidity', 'windspeed', 'lat', 'long']\n",
    "county_list = list(w_c['name'].unique())\n",
    "years = list(range(2008, 2021))\n",
    "months = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']\n",
    "days = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]\n",
    "out = w_c.loc[0:1].copy()\n",
    "out['name'] = 'drop'\n",
    "\n",
    "new_row = []\n",
    "\n",
    "\n",
    "for year in years:\n",
    "    for i, month in enumerate(months):\n",
    "        if month > '06' or year > 2008:\n",
    "            for county in county_list:\n",
    "                new_row = [str(year) + '-' + month]\n",
    "                for feature in feature_list:\n",
    "                    counter = 0\n",
    "                    feature_collector = 0\n",
    "                    for day in range(1, days[i]+1):\n",
    "                        if len(str(day)) == 1:\n",
    "                            day = '0' + str(day)\n",
    "                        else:\n",
    "                            day = str(day)\n",
    "                        date_mask = str(year) + '-' + month + '-' + str(day)\n",
    "                        counter += 1\n",
    "                        feature_collector += float(w_c[(w_c['name'] == county) & (w_c['date'] == date_mask)][feature])\n",
    "                    new_row.append(feature_collector / counter)\n",
    "                \n",
    "                new_row.append(county)    \n",
    "\n",
    "                columns = ['date', 'maxtempF', 'mintempF', 'avgtempF', 'totalSnow_cm', 'sunHour', 'precip', 'humidity', 'windspeed', 'lat', 'long', 'name']\n",
    "\n",
    "                out = out.append(pd.DataFrame([new_row], columns= columns), ignore_index = True)\n",
    "\n",
    "                clear_output()\n",
    "                \n",
    "                print(f'{date_mask} of {county} complete')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13c463c9-be78-4ec7-bc01-76727dfa5e81",
   "metadata": {},
   "source": [
    "The precipitation, humidity, and wind speed were stored differently in the WWO data than the temperature data.  The next cell collects this daily data into a new data frame and then uses the ```.resample``` function to convert this to monthly data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "051a8950-04ff-4d2c-98b2-efc1aee1a2a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "precip = []\n",
    "humid = []\n",
    "wind = []\n",
    "for item in df_out['hourly']:\n",
    "    precip.append(ast.literal_eval(item)[0]['precipInches'])\n",
    "    humid.append(ast.literal_eval(item)[0]['humidity'])\n",
    "    wind.append(ast.literal_eval(item)[0]['windspeedMiles'])\n",
    "\n",
    "lat = out['Lat and Long'].map(lambda x: float(x[4:9]))\n",
    "long = out['Lat and Long'].map(lambda x: float(x[18:26]))\n",
    "date = out['date']\n",
    "\n",
    "fixer = pd.DataFrame(list(zip(date, precip, humid, wind, lat, long)), columns = ['date', 'precip', 'humid', 'wind', 'lat', 'long'])\n",
    "\n",
    "fixer['date'] = pd.to_datetime(fixer.date)\n",
    "\n",
    "fixer['humid'] = fixer['humid'].astype(float)\n",
    "fixer['wind'] = fixer['wind'].astype(float)\n",
    "fixer['precip'] = fixer['precip'].astype(float)\n",
    "fixer = pd.merge(fixer, counties, left_on = ['lat', 'long'], right_on = ['lat', 'lon'])\n",
    "\n",
    "iters = 0\n",
    "for df in county_dfs:\n",
    "    name = df['name'][0]\n",
    "    temp_df = df[['humid', 'wind', 'precip']].resample('M').mean()\n",
    "    temp_df['name'] = name\n",
    "    temp_df.reset_index(inplace = True)\n",
    "    if iters == 0:\n",
    "        out = temp_df.copy()\n",
    "    else:\n",
    "        out = pd.concat([out, temp_df])\n",
    "    iters += 1\n",
    "    clear_output()\n",
    "    print(iters)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87d002da-3423-4a61-b122-abf7f6861ae8",
   "metadata": {},
   "source": [
    "Finally, the wind, humidity, and precipitation data is merged to the rest of the weather data and saved as a .csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ec37cea-e72f-4da2-a2aa-00bd7eaf2345",
   "metadata": {},
   "outputs": [],
   "source": [
    "out['date'] = out['date'].astype(str)\n",
    "out['date'] = out['date'].map(lambda x: x[0:7])\n",
    "\n",
    "weather = pd.merge(weather, out, left_on = ['date', 'name'], right_on = ['date', 'name'])\n",
    "\n",
    "weather.rename(columns = {'name': 'county'}, inplace = True)\n",
    "\n",
    "weather = weather[['date', 'county', 'maxtempF', 'mintempF', 'avgtempF', 'totalSnow_cm', 'humid', 'wind', 'precip', 'sunHour', 'lat', 'long']]\n",
    "\n",
    "weather.to_csv('./data/weather_data.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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}