6_USD_Impact_data_ETL2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                    Tot_Damage  Frequency\n",
      "County                                                   \n",
      "nan                                     3891.0        262\n",
      "E. San Joaquin                           120.0          8\n",
      "E. Trinity                               117.0          8\n",
      "Shasta-Trinity                           113.0          9\n",
      "Sonoma-Lake-Napa                          91.0          8\n",
      "...                                        ...        ...\n",
      "NORTHERN REGION TOTAL                      0.0          1\n",
      "SOUTHERN REGION TOTAL                      0.0          1\n",
      "San Mateo-                                 0.0          1\n",
      "Shaded areas represent unit totals         0.0          1\n",
      "Sonoma-Lake-                               0.0          1\n",
      "\n",
      "[61 rows x 2 columns]\n",
      "Empty DataFrame\n",
      "Columns: [Frequency]\n",
      "Index: []\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\johns\\AppData\\Local\\Temp\\ipykernel_19212\\3900561534.py:111: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\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",
      "  filtered_data['Tot_Damage'] = pd.to_numeric(filtered_data['Tot_Damage'].str.replace('[\\$,]', '', regex=True), errors='coerce')\n",
      "C:\\Users\\johns\\AppData\\Local\\Temp\\ipykernel_19212\\3900561534.py:114: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\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",
      "  unmatched_data['Tot_Damage'] = pd.to_numeric(unmatched_data['Tot_Damage'].str.replace('[\\$,]', '', regex=True), errors='coerce')\n"
     ]
    }
   ],
   "source": [
    "# Directory path where the Excel files are located\n",
    "directory = 'Resources\\CAL_FireStats'\n",
    "pattern = r\"\\b\\d{4}-wildfire-activity-stats\\.xlsx$\"\n",
    "#pattern = r\"2021-wildfire-activity-stats\\.xlsx$\"\n",
    "\n",
    "# Initialize an empty DataFrame to store the extracted data\n",
    "extracted_data = []\n",
    "# Loop through each file in the directory\n",
    "for filename in os.listdir(directory):\n",
    "    if re.match(pattern, filename):\n",
    "        # Extract the year from the filename\n",
    "        year = int(filename[:4])\n",
    "\n",
    "        # Process the file\n",
    "        #print(f\"Processing file: {filename}\")\n",
    "        \n",
    "        # Construct the full file path\n",
    "        file_path = os.path.join(directory, filename)\n",
    "\n",
    "        # Read the Excel file into a DataFrame\n",
    "        xls = pd.ExcelFile(file_path)\n",
    "        \n",
    "        # Loop through each tab in the Excel file\n",
    "        for sheet_name in xls.sheet_names:\n",
    "            tab = pd.read_excel(xls, sheet_name,header=None).astype(str)\n",
    "            #print(f\"Processing tab: {sheet_name}\")\n",
    "                \n",
    "            # Check if the DataFrame is not empty\n",
    "            if not tab.empty:\n",
    "                # Check for the conditions before proceeding with extraction\n",
    "               first_row = tab.iloc[0, 2:].values.tolist()\n",
    "               #print(first_row)\n",
    "\n",
    "            # Check for the presence of the dollar sign ($) character in the tab\n",
    "            if any('$' in item for row in tab.values for item in row):\n",
    "                # Check for the presence of \"Arson\", \"Campfire\", \"Lightning\" in the non-empty values starting from the third one\n",
    "                valid_values = [\"Arson\", \"Campfire\", \"Lightning\", \"Total\",\"DebrisBurning\",\"Equip.Use\",\"Ltng.\",\"Misc.\",\"Power-line\",\"P-W-F\",\"Rail-road\",\"Smoking\",\"Undet.\", \"Vehicle\"]\n",
    "                \n",
    "                # Preprocess the values in first_row by removing whitespace characters\n",
    "                first_row_cleaned = [value.strip() for value in first_row]\n",
    "\n",
    "                # Define the desired column index in data_to_append for the dollar values\n",
    "                dollar_target_column_index = 1  # For example, placing the dollar values in the second column of data_to_append\n",
    "\n",
    "                if any(value in valid_values for value in first_row_cleaned):\n",
    "                     dollar_column = tab.columns[tab.apply(lambda col: col.astype(str).str.contains('\\$', na=False).any())]\n",
    "                     # Convert the NumPy array to a Pandas Series\n",
    "                     dollar_column_series = pd.Series(dollar_column)\n",
    "                     # Ensure the data in dollar_column_series is treated as strings\n",
    "                     dollar_column_series = dollar_column_series.astype(str)\n",
    "                     \n",
    "                     # Repeat the corresponding dollar values for each split value in the county_column\n",
    "                     dollar_values_split = dollar_column_series.str.split('\\n').explode()\n",
    "\n",
    "                     # Reset the index of dollar_values_split and county_column_split\n",
    "                     dollar_values_split = dollar_values_split.reset_index(drop=True)\n",
    "    \n",
    "                     # Extract the county column from the tab DataFrame\n",
    "                     county_column = tab.iloc[:, 0]\n",
    "\n",
    "                     # Split multi-line values in the county_column\n",
    "                     county_column_split = county_column.str.split('\\n').explode()\n",
    "\n",
    "                     # Reset the index of dollar_values_split and county_column_split\n",
    "                     county_column_split = county_column_split.reset_index(drop=True)\n",
    "\n",
    "                     # Create a DataFrame with the split values\n",
    "                     data_to_append = pd.concat([dollar_values_split.to_frame(name='DollarAmount'),\\\n",
    "                                                  county_column_split.to_frame(name='County'), pd.Series(year, name='year')], axis=1)\n",
    "                     # Reassign the dollar values to the desired column index in data_to_append\n",
    "                     data_to_append.insert(dollar_target_column_index, 'DollarAmount', data_to_append.pop('DollarAmount'))\n",
    "\n",
    "                     # Check if the required columns are found\n",
    "                     if dollar_column.size > 0:\n",
    "                        # Append the data to the extracted_data list\n",
    "                        extracted_data.append(data_to_append)\n",
    "                #       print(\"Data extracted and appended successfully.\")\n",
    "                    #else:\n",
    "                #        print(\"Required columns not found in the tab.\")\n",
    "                #else:\n",
    "               #     print(\"Non-empty values starting from the third one do not match the specified list.\")\n",
    "            #else:\n",
    "              #  print(\"Dollar sign ($) character not found in the tab.\")\n",
    "            \n",
    "# Check if data is being appended to the extracted_data list\n",
    "if extracted_data:\n",
    "    # Concatenate the extracted data into a DataFrame\n",
    "    extracted_data_df = pd.concat(extracted_data, ignore_index=True)\n",
    "\n",
    "    # Rename the columns\n",
    "\n",
    "    # check column names\n",
    "    #print(extracted_data_df.columns)\n",
    "    extracted_data_df.rename(columns={0: 'County', 'DollarAmount': 'Tot_Damage', 'year': 'Year'}, inplace=True)\n",
    "\n",
    "    # List of county names to filter\n",
    "    county_names = [\"Alameda\",\"Alpine\", \"Amador\", \"Butte\",\"Calaveras\",\"Colusa\",\"Contra Costa\",\"Del Norte\",\"El Dorado\",\"Fresno\",\\\n",
    "    \"Glenn\",\"Humboldt\",\"Imperial\",\"Inyo\",\"Kern\",\"Kings\",\"Lake\",\"Lassen\",\"Los Angeles\",\"Madera\",\"Marin\",\\\n",
    "        \"Mariposa\",\"Mendocino\",\"Merced\",\"Modoc\",\"Mono\",\"Monterey\",\"Napa\",\"Nevada\",\"Orange\",\"Placer\",\"Plumas\",\\\n",
    "            \"Riverside\",\"Sacramento\",\"San Benito\",\"San Bernardino\",\"San Diego\",\"San Francisco\",\"San Joaquin\",\\\n",
    "            \"San Luis Obispo\",\"San Mateo\",\"Santa Barbara\",\"Santa Clara\",\"Santa Cruz\",\"Shasta\",\"Sierra\",\"Siskiyou\",\\\n",
    "            \"Solano\",\"Sonoma\",\"Stanislaus\",\"Sutter\",\"Tehama\",\"Trinity\",\"Tulare\",\"Tuolumne\",\"Ventura\",\"Yolo\",\"Yuba\"]\n",
    "\n",
    "    # Filter the county column\n",
    "    filtered_data = extracted_data_df[extracted_data_df['County'].isin(county_names)]\n",
    "    #Save the values from the county column that do not match any of the ones in the county names list:\n",
    "    # Save the unmatched county values\n",
    "    unmatched_data = extracted_data_df[~extracted_data_df['County'].isin(county_names)]\n",
    "\n",
    "    # Convert the Tot_Damage column to numeric values, handling errors with 'coerce'\n",
    "    filtered_data['Tot_Damage'] = pd.to_numeric(filtered_data['Tot_Damage'].str.replace('[\\$,]', '', regex=True), errors='coerce')\n",
    "    filtered_data.sort_values(['Year','County','Tot_Damage'])\n",
    "    filtered_data = filtered_data.drop_duplicates()\n",
    "    unmatched_data['Tot_Damage'] = pd.to_numeric(unmatched_data['Tot_Damage'].str.replace('[\\$,]', '', regex=True), errors='coerce')\n",
    "    \n",
    "    summary_unmatched = unmatched_data.groupby('County').agg({'Tot_Damage': 'sum', 'County': 'count'}).\\\n",
    "        rename(columns={'County': 'Frequency'}).sort_values(['Tot_Damage', 'Frequency'], ascending=[False, False])\n",
    "    print(summary_unmatched)\n",
    "    summary_unmatched.to_csv('Outputs\\\\unmatched_DD_rows.csv', index=True)\n",
    "    \n",
    "    # Display the extracted data\n",
    "    #print(\"Extracted Data:\")\n",
    "    #print(extracted_data_df.tail(5))\n",
    "\n",
    "    # Export the extracted data DataFrame to a CSV file\n",
    "    filtered_data.to_csv('Outputs\\extracted_data.csv', index=False)\n",
    "\n",
    "    summary_matched = filtered_data.groupby('Year').agg({'County': 'count'}).\\\n",
    "        rename(columns={'County': 'Frequency'}).sort_values(['Year'])\n",
    "    print(summary_matched)\n",
    "else:\n",
    "    print(\"No data extracted. Check the conditions for data extraction.\")\n",
    "\n",
    "           "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}