3_CA_wildfire_data_ETL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\", message=\"Could not infer format, so each element will be parsed individually\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     County  Fire Name      Start  Contained  Acres  Strux_Destr  Strux_Dmgd  \\\n",
      "0  MONTEREY   COLORADO 2022-01-21 2022-02-02    687            1           1   \n",
      "1      INYO    AIRPORT 2022-02-16 2022-02-26  4,136            0           0   \n",
      "2    MADERA     VALLEY 2022-04-13 2022-04-13    680            0           0   \n",
      "3    MERCED      SANDY 2022-05-15 2022-05-15    457            0           1   \n",
      "4      KERN  EDMONSTON 2022-05-19 2022-05-24    682            0           0   \n",
      "\n",
      "   Deaths_FF  Deaths_Civil  Duration  \n",
      "0          0             0        13  \n",
      "1          0             0        11  \n",
      "2          0             0         1  \n",
      "3          0             0         1  \n",
      "4          0             0         6  \n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 206 entries, 0 to 205\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype         \n",
      "---  ------        --------------  -----         \n",
      " 0   County        206 non-null    object        \n",
      " 1   Fire Name     206 non-null    object        \n",
      " 2   Start         206 non-null    datetime64[ns]\n",
      " 3   Contained     206 non-null    datetime64[ns]\n",
      " 4   Acres         206 non-null    object        \n",
      " 5   Strux_Destr   206 non-null    int32         \n",
      " 6   Strux_Dmgd    206 non-null    int32         \n",
      " 7   Deaths_FF     206 non-null    int32         \n",
      " 8   Deaths_Civil  206 non-null    int32         \n",
      " 9   Duration      206 non-null    int64         \n",
      "dtypes: datetime64[ns](2), int32(4), int64(1), object(3)\n",
      "memory usage: 13.0+ KB\n"
     ]
    }
   ],
   "source": [
    "# Load the Excel file\n",
    "file_path = 'Resources/Consolidated_Incidents_by_Year.xlsx'\n",
    "xl = pd.ExcelFile(file_path)\n",
    "\n",
    "# Initialize an empty DataFrame to store the extracted data\n",
    "final_data = pd.DataFrame()\n",
    "\n",
    "# Loop through each sheet in the Excel file\n",
    "for sheet_name in xl.sheet_names:\n",
    "    # Skip the first two rows and import columns 2, 3, 4, and 7\n",
    "    data = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=1, \n",
    "                         usecols=[1, 2, 3, 4, 6, 9, 10, 11 , 12])\n",
    "    \n",
    "    # Append the data to the final DataFrame\n",
    "    final_data = pd.concat([final_data, data], ignore_index=True)\n",
    "\n",
    "\n",
    "# 'TRANSFORM': Convert 'Start' and 'Contained' columns to datetime format\n",
    "final_data['Start'] = pd.to_datetime(final_data['Start'])\n",
    "final_data['Contained'] = pd.to_datetime(final_data['Contained'])\n",
    "\n",
    "# 'TRANSFORM': Convert 'Dest.', 'Dam.', 'Fire', and 'Civil' columns to integers and replace NaN values with zero\n",
    "final_data['Dest.'] = pd.to_numeric(final_data['Dest.'], errors='coerce').fillna(0).astype(int)\n",
    "final_data['Dam.'] = pd.to_numeric(final_data['Dam.'], errors='coerce').fillna(0).astype(int)\n",
    "final_data['Fire'] = pd.to_numeric(final_data['Fire'], errors='coerce').fillna(0).astype(int)\n",
    "final_data['Civil'] = pd.to_numeric(final_data['Civil'], errors='coerce').fillna(0).astype(int)\n",
    "\n",
    "# Rename columns\n",
    "final_data = final_data.rename(columns={'Total': 'Acres', 'Dest.': 'Strux_Destr', 'Dam.': 'Strux_Dmgd', 'Fire': 'Deaths_FF', 'Civil': 'Deaths_Civil'})\n",
    "\n",
    "# Calculate the duration in days\n",
    "final_data['Duration'] = (final_data['Contained'] - final_data['Start']).dt.days+1\n",
    "\n",
    "# Get the last column name\n",
    "last_column = final_data.columns[-1]\n",
    "\n",
    "# Move the 'Duration' column to after Start and Contained?\n",
    "\n",
    "\n",
    "# Display the extracted data\n",
    "print(final_data.head(5))\n",
    "final_data.info()\n",
    "\n",
    "final_data.to_csv('Outputs/fires_2020_2022.csv', index=False)\n",
    "\n",
    "xl.close()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "dev",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}