5_USD_Impact_data_ETL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 23, 24, 26, 27, 29,\n",
      "       30, 32, 33, 35, 36, 38, 39],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 23 24 26 27 29 30 32 33 35 36 38\n",
      " 39]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([ 3,  4,  6,  7,  9, 10, 12, 13, 15, 16, 18, 19, 21, 22, 24, 25, 27, 28,\n",
      "       30, 31, 33, 34, 36, 37, 39, 40],\n",
      "      dtype='int64')\n",
      "$ columns np:  [ 3  4  6  7  9 10 12 13 15 16 18 19 21 22 24 25 27 28 30 31 33 34 36 37\n",
      " 39 40]\n",
      "Dollar column: Index([3, 6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39], dtype='int64')\n",
      "$ columns np:  [ 3  6  9 12 15 18 21 24 27 30 33 36 39]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Dollar column: Index([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], dtype='int64')\n",
      "$ columns np:  [ 1  2  3  4  5  6  7  8  9 10 11 12 13]\n",
      "Index([0, 'DollarAmount', 'year'], dtype='object')\n",
      "                                                      Tot_Damage  Frequency\n",
      "County                                                                     \n",
      "STATE TOTAL                                         4.718965e+10         23\n",
      "TOTAL                                               2.219958e+10         18\n",
      "Sonoma-Lake-Napa                                    6.581923e+09          9\n",
      "nan                                                 6.208045e+09        263\n",
      "Nevada-Yuba-Placer                                  6.105954e+09          8\n",
      "Butte**                                             4.001416e+09          2\n",
      "Nevada-Yuba-\\nPlacer                                2.748064e+09          1\n",
      "Shasta-Trinity                                      2.607834e+09         10\n",
      "Amador-El Dorado                                    4.125561e+08          9\n",
      "Tuolumne-\\nCalaveras                                2.823089e+08          2\n",
      "Madera-Mariposa-Merced                              2.007749e+08          2\n",
      "Fresno-Kings                                        9.240333e+07         10\n",
      "San\\nBernardino                                     9.013191e+07          1\n",
      "San Mateo-Santa Cruz                                5.694123e+07          7\n",
      "Madera-Mariposa                                     5.002692e+07          8\n",
      "San Benito-\\nMonterey                               3.590223e+07          2\n",
      "NORTHERN REGION TOTAL                               3.250893e+07          1\n",
      "Tehama-Glenn                                        2.194825e+07         10\n",
      "San Benito-Monterey                                 1.487639e+07          8\n",
      "Lassen-Modoc                                        1.351044e+07         11\n",
      "Tuolumne-Calaveras                                  7.122035e+06          8\n",
      "Humboldt-Del\\nNorte                                 5.000200e+06          1\n",
      "Nevada-Yuba-Placer\\nTotal                           4.910633e+06          1\n",
      "Humboldt-Del Norte                                  3.767855e+06          9\n",
      "Amador-El\\nDorado                                   3.644362e+06          1\n",
      "San Mateo-Santa\\nCruz                               2.015438e+06          3\n",
      "SOUTHERN REGION TOTAL                               1.447969e+06          1\n",
      "Contra\\nCosta                                       8.588500e+05          3\n",
      "Sonoma-Lake-\\nNapa                                  6.866500e+05          1\n",
      "W. San Joaquin                                      6.509900e+05          5\n",
      "E. Stanislaus                                       6.288890e+05          8\n",
      "San Luis\\nObispo                                    5.770220e+05          1\n",
      "E. Trinity                                          4.912010e+05          8\n",
      "W. Stanislaus                                       4.002000e+05          5\n",
      "W.\\nStanislaus                                      1.524000e+05          2\n",
      "E. San Joaquin                                      1.290900e+05          8\n",
      "TSoatanl Mateo-Santa Cruz                           1.164060e+05          1\n",
      "W. San\\nJoaquin                                     9.055000e+04          2\n",
      "San Mateo-\\nSanta Cruz                              1.342600e+04          1\n",
      "COUNTY                                              0.000000e+00         14\n",
      "NORTHERN\\nREGION                                    0.000000e+00         10\n",
      "NORTHERN REGION                                     0.000000e+00         10\n",
      "SOUTHERN REGION                                     0.000000e+00          7\n",
      "SOUTHERN\\nREGION                                    0.000000e+00          6\n",
      "W. Trinity                                          0.000000e+00          4\n",
      "Northern Region                                     0.000000e+00          3\n",
      "Southern Region                                     0.000000e+00          2\n",
      "*Shaded areas repres                                0.000000e+00          1\n",
      "*Shaded areas represe                               0.000000e+00          1\n",
      "*Shaded areas represen                              0.000000e+00          1\n",
      "*Shaded areas represent unit to                     0.000000e+00          1\n",
      "*Shaded areas represent unit totals                 0.000000e+00          1\n",
      "Alameda\\nContra Costa\\nSanta Clara\\nW. San Joaq...  0.000000e+00          1\n",
      "Amador\\nEl Dorado\\nSacramento\\nSan Joaquin          0.000000e+00          1\n",
      "Shaded areas represent unit totals                  0.000000e+00          1\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\johns\\AppData\\Local\\Temp\\ipykernel_31808\\4004627573.py:89: 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",
      "C:\\Users\\johns\\AppData\\Local\\Temp\\ipykernel_31808\\4004627573.py:99: 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'] = filtered_data['Tot_Damage'].str.replace('[\\$,]', '', regex=True).astype(float)\n"
     ]
    }
   ],
   "source": [
    "# Directory path where the Excel files are located, and filename pattern\n",
    "directory = 'Resources\\CAL_FireStats'\n",
    "pattern = r\"\\b\\d{4}-wildfire-activity-stats\\.xlsx$\"\n",
    "\n",
    "# Empty DataFrame to store the extracted data\n",
    "extracted_data = []\n",
    "\n",
    "# Process each year's data 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",
    "        # Construct the full file path and read file into a DataFrame\n",
    "        file_path = os.path.join(directory, filename)\n",
    "        xls = pd.ExcelFile(file_path)\n",
    "        \n",
    "        # Process tabs in the Excel file, looking for the 1st sheet with \"$\" character, which is where the Damages data are located\n",
    "        for sheet_name in xls.sheet_names:\n",
    "            tab = pd.read_excel(xls, sheet_name,header=None).astype(str)\n",
    "                            \n",
    "            # Check if the DataFrame is not empty\n",
    "            if not tab.empty:\n",
    "                first_row = tab.iloc[0, 2:].values.tolist()\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",
    "                # Look for the indicators of the tabs containing by-county damages data\n",
    "                valid_values = [\"Arson\", \"Campfire\", \"Lightning\", \"Total\",\"DebrisBurning\",\"Equip.Use\",\"Ltng.\",\"Misc.\",\"Power-line\",\"P-W-F\",\"Rail-road\",\"Smoking\",\"Undet.\", \"Vehicle\"]\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  # ()\n",
    "\n",
    "                if any(value in valid_values for value in first_row_cleaned):\n",
    "                    # Find the column with the dollar sign ($)\n",
    "                    dollar_column = tab.columns[tab.apply(lambda col: col.astype(str).str.contains('\\$', na=False).any())]\n",
    "                    print(\"Dollar column:\", dollar_column)\n",
    "                \n",
    "                    # Extract data from the tab\n",
    "                    # Convert the Index objects to numpy arrays before indexing\n",
    "                    dollar_column = tab.columns.to_numpy()[tab.apply(lambda col: col.astype(str).str.contains('\\$', na=False).any())]\n",
    "                    print(\"$ columns np: \", dollar_column)\n",
    "\n",
    "                    # Extract the county column from the tab DataFrame\n",
    "                    county_column = tab.iloc[:, 0]\n",
    "\n",
    "                    # Create a DataFrame with the extracted columns and the assigned year\n",
    "                    data_to_append = pd.concat([tab.loc[:, dollar_column[0]].to_frame(), county_column.to_frame(), pd.Series(year, index=tab.index, name='year')], axis=1)\n",
    "                    \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(dollar_column[0]))\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",
    "# DEBUGGING CODE PRESERVED FOR FUTURE CONVENIENCE\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",
    "# Ensure extracted data is 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",
    "    # 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 to remove 'Units' (i.e. aggregrate regions comprised of multiple counties)\n",
    "    filtered_data = extracted_data_df[extracted_data_df['County'].isin(county_names)]\n",
    "\n",
    "    # Process the unmatched values from the county column\n",
    "    # (for analysis to ensure none are unintentionally excluded)\n",
    "    unmatched_data = extracted_data_df[~extracted_data_df['County'].isin(county_names)]\n",
    "    unmatched_data['Tot_Damage'] = pd.to_numeric(unmatched_data['Tot_Damage'].str.replace('[\\$,]', '', regex=True), errors='coerce')\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",
    "    # Final data transformation and export steps \n",
    "    # Convert the Tot_Damage column to numeric values to facilitate calculations\n",
    "    filtered_data['Tot_Damage'] = filtered_data['Tot_Damage'].str.replace('[\\$,]', '', regex=True).astype(float)\n",
    "    filtered_data.sort_values(['Year','County','Tot_Damage'])\n",
    "    filtered_data = filtered_data.drop_duplicates()    \n",
    "    filtered_data.to_csv('Outputs\\extracted_data.csv', index=False)\n",
    "else:\n",
    "    print(\"No data extracted. Check the conditions for data extraction.\")"
   ]
  },
  {
   "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
}