7_calculating_key_metrics_for_visualization_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Improt dependencies\n",
    "import pandas as pd\n",
    "import ast # process trees of the Python abstract syntax grammar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the cleaned data from csv\n",
    "file_path = 'Outputs/fires_2008_2022_cleaned.csv'\n",
    "data = pd.read_csv(file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove commas and convert to numeric\n",
    "data['Acres'] = data['Acres'].str.replace(',', '').astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  Fire Name  Total Deaths\n",
      "0     CONTROL BURN, GEYSERS             0\n",
      "1                     Bluff             0\n",
      "2                 WAWONA NW             0\n",
      "3               SANTA ANITA             0\n",
      "4                    APACHE             0\n",
      "...                     ...           ...\n",
      "1092               MOUNTAIN             0\n",
      "1093               FAIRVIEW             2\n",
      "1094               MOSQUITO             0\n",
      "1095                   FORK             0\n",
      "1096                 BARNES             0\n",
      "\n",
      "[1097 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Ensure 'Deaths_FF' and 'Deaths_Civil' are numeric to calculate total deaths\n",
    "data['Deaths_FF'] = pd.to_numeric(data['Deaths_FF'], errors='coerce')\n",
    "data['Deaths_Civil'] = pd.to_numeric(data['Deaths_Civil'], errors='coerce')\n",
    "\n",
    "# Calculate total deaths\n",
    "data['Total Deaths'] = data['Deaths_FF'] + data['Deaths_Civil']\n",
    "\n",
    "# Display the results\n",
    "print(data[['Fire Name', 'Total Deaths']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Convert 'Start' and 'Contained\" to datetime format to avoid errors\n",
    "data['Start'] = pd.to_datetime(data['Start'], errors='coerce')\n",
    "data['Contained'] = pd.to_datetime(data['Contained'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Start  Contained  Year  Month\n",
      "0 2008-02-13 2008-02-13  2008      2\n",
      "1 2008-03-16 2008-03-20  2008      3\n",
      "2 2008-04-09 2008-04-19  2008      4\n"
     ]
    }
   ],
   "source": [
    "# Create a new column for the 'Year' and 'Month'\n",
    "data['Year'] = data['Start'].dt.year\n",
    "data['Month'] = data['Start'].dt.month\n",
    "# Print data 3 first lines to verify correctness\n",
    "print(data[['Start', 'Contained', 'Year', 'Month']].head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a copy to prevent altering the original data in case of any errors\n",
    "Corrected_Duration_Long_Fires = data.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          Start  Contained              Duration_Per_Month\n",
      "0    2008-02-13 2008-02-13                  {'2008-02': 1}\n",
      "1    2008-03-16 2008-03-20                  {'2008-03': 5}\n",
      "2    2008-04-09 2008-04-19                 {'2008-04': 11}\n",
      "3    2008-04-26 2008-05-02    {'2008-04': 5, '2008-05': 2}\n",
      "4    2008-04-29 2008-05-04    {'2008-04': 2, '2008-05': 4}\n",
      "...         ...        ...                             ...\n",
      "1092 2022-09-02 2022-10-30  {'2022-09': 29, '2022-10': 30}\n",
      "1093 2022-09-05 2022-09-22                 {'2022-09': 18}\n",
      "1094 2022-09-06 2022-10-22  {'2022-09': 25, '2022-10': 22}\n",
      "1095 2022-09-07 2022-09-14                  {'2022-09': 8}\n",
      "1096 2022-09-07 2022-09-28                 {'2022-09': 22}\n",
      "\n",
      "[1097 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Invoke a custom function:\n",
    "def calculate_duration(row):\n",
    "    # Initialize an empty dictionary to hold the duration for each month\n",
    "    duration_per_month = {}\n",
    "\n",
    "    start_date = row['Start'] # set start dat\n",
    "    end_date = row['Contained'] # set end date\n",
    "    \n",
    "    # Calculate duration for the first month\n",
    "    # Offsets here helps to end the count at the end of the month\n",
    "    end_of_start_month = start_date + pd.offsets.MonthEnd(0)\n",
    "    if end_date <= end_of_start_month: #set conditions\n",
    "        # If the end date is in the same month\n",
    "        first_month_duration = (end_date - start_date).days + 1\n",
    "        duration_per_month[f'{start_date.year}-{start_date.month:02d}'] = first_month_duration\n",
    "    else: #set contiotion if the 1st is not met\n",
    "        first_month_duration = (end_of_start_month - start_date).days + 1\n",
    "        duration_per_month[f'{start_date.year}-{start_date.month:02d}'] = first_month_duration\n",
    "\n",
    "        # Handle months between 'Start' and 'Contained'\n",
    "        # Offsets here helps to start the count at the begining of the month\n",
    "        current_month = start_date + pd.offsets.MonthBegin(1)\n",
    "        while current_month <= end_date:\n",
    "            end_of_current_month = current_month + pd.offsets.MonthEnd(0)\n",
    "            if end_date <= end_of_current_month:\n",
    "                duration_in_current_month = (end_date - current_month).days + 1\n",
    "                duration_per_month[f'{current_month.year}-{current_month.month:02d}'] = duration_in_current_month\n",
    "                break\n",
    "            else:\n",
    "                duration_in_current_month = (end_of_current_month - current_month).days + 1\n",
    "                duration_per_month[f'{current_month.year}-{current_month.month:02d}'] = duration_in_current_month\n",
    "                current_month = current_month + pd.offsets.MonthBegin(1)\n",
    "\n",
    "    return duration_per_month # used return function for correct count in the loop\n",
    "\n",
    "# Apply the function to calculate the duration per month for each row\n",
    "Corrected_Duration_Long_Fires['Duration_Per_Month'] = data.apply(calculate_duration, axis=1)\n",
    "\n",
    "# Display the result to check\n",
    "print(Corrected_Duration_Long_Fires[['Start', 'Contained', 'Duration_Per_Month']])\n",
    "Corrected_Duration_Long_Fires.to_csv('Outputs/Corrected_Duration_Long_Fires.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['County', 'Fire Name', 'Start', 'Contained', 'Acres', 'Deaths_FF',\n",
       "       'Deaths_Civil', 'Duration', 'Strux_Destr', 'Strux_Dmgd', 'Total Deaths',\n",
       "       'Year', 'Month', 'Duration_Per_Month'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display columns in our data to use correct names of indexes\n",
    "Corrected_Duration_Long_Fires.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        County    Fire Name      Start  Contained   Acres  Deaths_FF  \\\n",
      "0        MODOC       BARNES 2022-09-07 2022-09-28  5843.0          0   \n",
      "1  LOS ANGELES  SANTA ANITA 2008-04-26 2008-05-02     0.0          0   \n",
      "2  LOS ANGELES  SANTA ANITA 2008-04-26 2008-05-02   584.0          0   \n",
      "\n",
      "   Deaths_Civil  Duration  Strux_Destr  Strux_Dmgd  Total Deaths  Year  Month  \\\n",
      "0             0        22            2           0             0  2022      9   \n",
      "1             0         5            0           0             0  2008      4   \n",
      "2             0         2            0           0             0  2008      5   \n",
      "\n",
      "  Duration_Per_Month  \n",
      "0    {'2022-09': 22}  \n",
      "1     {'2008-04': 5}  \n",
      "2     {'2008-05': 2}  \n",
      "object\n"
     ]
    }
   ],
   "source": [
    "# Function to split rows where 'Duration_Per_Month' has multiple entries\n",
    "# This is needed for fires that started in one month and continued into the following month(s)\n",
    "def split_rows(row, group_counts):\n",
    "    durations = row['Duration_Per_Month']\n",
    "    if len(durations) > 1:\n",
    "        rows = []\n",
    "        for month, duration in durations.items():\n",
    "            new_row = row.copy()\n",
    "            new_row['Month'] = int(month.split('-')[1])  # Set the correct month\n",
    "            new_row['Duration'] = duration  # Set the correct duration\n",
    "            new_row['Year'] = int(month.split('-')[0])  # Set the correct year\n",
    "            new_row['Duration_Per_Month'] = {month: duration}  # Keep only the current month in the dictionary\n",
    "            \n",
    "            # Create a unique key for checking how many times this Fire Name, County, and Year combo appears\n",
    "            key = (new_row['Fire Name'], new_row['County'], new_row['Year'])\n",
    "            \n",
    "            # Check if the key exists in group_counts dictionary\n",
    "            if key in group_counts and group_counts[key] > 0:\n",
    "                # Set values to 0 for subsequent occurrences\n",
    "                new_row['Acres'] = 0\n",
    "                new_row['Deaths_FF'] = 0\n",
    "                new_row['Deaths_Civil'] = 0\n",
    "                new_row['Strux_Destr'] = 0\n",
    "                new_row['Strux_Dmgd'] = 0\n",
    "                new_row['Total Deaths'] = 0\n",
    "                \n",
    "                # Decrement the count for this key\n",
    "                group_counts[key] -= 1\n",
    "            \n",
    "            rows.append(new_row)\n",
    "        return rows\n",
    "    else:\n",
    "        # Only return the row itself if it doesn't need splitting\n",
    "        return [row]\n",
    "\n",
    "# Create a dictionary to track the count of each 'Fire Name', 'County', and 'Year' combination\n",
    "group_counts = Corrected_Duration_Long_Fires.groupby(['Fire Name', 'County', 'Year']).size().to_dict()\n",
    "\n",
    "# Apply the split_rows function to each row and pass in the group_counts dictionary\n",
    "expanded_rows = Corrected_Duration_Long_Fires.apply(lambda row: split_rows(row, group_counts), axis=1).tolist()\n",
    "expanded_rows = [item for sublist in expanded_rows for item in sublist]  # Flatten the list of lists\n",
    "\n",
    "# Convert the list of rows into a DataFrame\n",
    "updated_corrected_duration = pd.DataFrame(expanded_rows)\n",
    "\n",
    "# Convert the 'Duration_Per_Month' dictionary to a string to make it hashable\n",
    "updated_corrected_duration['Duration_Per_Month'] = updated_corrected_duration['Duration_Per_Month'].apply(lambda x: str(x))\n",
    "\n",
    "# Remove any duplicate rows to prevent data alteration\n",
    "updated_corrected_duration = updated_corrected_duration.drop_duplicates()\n",
    "\n",
    "# Reset the index to make sure the DataFrame is well-formed\n",
    "updated_corrected_duration = updated_corrected_duration.reset_index(drop=True)\n",
    "\n",
    "# Display the resulting DataFrame\n",
    "print(updated_corrected_duration.head(3))\n",
    "\n",
    "# Save the result to a CSV file\n",
    "updated_corrected_duration.to_csv('Outputs/updated_corrected_duration.csv', index=False)\n",
    "print(updated_corrected_duration['Duration_Per_Month'].dtype)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Fire Name      Start  Contained  Month Duration_Per_Month  Duration  \\\n",
      "0         BARNES 2022-09-07 2022-09-28      9    {'2022-09': 22}        22   \n",
      "1    SANTA ANITA 2008-04-26 2008-05-02      4     {'2008-04': 5}         5   \n",
      "2    SANTA ANITA 2008-04-26 2008-05-02      5     {'2008-05': 2}         2   \n",
      "3         APACHE 2008-04-29 2008-05-04      4     {'2008-04': 2}         2   \n",
      "4         APACHE 2008-04-29 2008-05-04      5     {'2008-05': 4}         4   \n",
      "..           ...        ...        ...    ...                ...       ...   \n",
      "946        ROUTE 2022-08-31 2022-09-06      9     {'2022-09': 6}         6   \n",
      "947     MOUNTAIN 2022-09-02 2022-10-30      9    {'2022-09': 29}        29   \n",
      "948     MOUNTAIN 2022-09-02 2022-10-30     10    {'2022-10': 30}        30   \n",
      "949     MOSQUITO 2022-09-06 2022-10-22      9    {'2022-09': 25}        25   \n",
      "950     MOSQUITO 2022-09-06 2022-10-22     10    {'2022-10': 22}        22   \n",
      "\n",
      "     Deaths_FF  Deaths_Civil  \n",
      "0            0             0  \n",
      "1            0             0  \n",
      "2            0             0  \n",
      "3            0             0  \n",
      "4            0             0  \n",
      "..         ...           ...  \n",
      "946          0             0  \n",
      "947          0             0  \n",
      "948          0             0  \n",
      "949          0             0  \n",
      "950          0             0  \n",
      "\n",
      "[951 rows x 8 columns]\n"
     ]
    }
   ],
   "source": [
    "# Create a copy of the DataFrame to prevent modifying the original data in case of any errors\n",
    "df_duration = updated_corrected_duration.copy()\n",
    "\n",
    "# Convert strings of dictionaries to actual dictionaries\n",
    "# As a first step to extract data to get the duration of each fire in each month\n",
    "def convert_to_dict(duration_str): # defining function to convert\n",
    "    try:\n",
    "        return ast.literal_eval(duration_str) # convert to strings\n",
    "    except (ValueError, SyntaxError): # set how to handle errors\n",
    "        return {} # if conversion fails, then return empty value\n",
    "    \n",
    "# Apply this function to each element\n",
    "df_duration['Duration_Per_Month'] = df_duration['Duration_Per_Month'].apply(convert_to_dict)\n",
    "\n",
    "# Function to extract the numeric value from the dictionary in Duration_Per_Month\n",
    "def extract_duration(duration_dict):\n",
    "    return list(duration_dict.values())[0] if duration_dict else 0 #if distionary is empty ruturn '0' value\n",
    "\n",
    "# Apply the extraction function to each dictionary\n",
    "df_duration['Duration'] = df_duration['Duration_Per_Month'].apply(extract_duration)\n",
    "\n",
    "# Display the updated DataFrame\n",
    "print(df_duration[['Fire Name', 'Start', 'Contained', 'Month', 'Duration_Per_Month', 'Duration', 'Deaths_FF', 'Deaths_Civil']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Duration  Total Deaths\n",
      "0          22             0\n",
      "1           5             0\n",
      "2           2             0\n",
      "3           2             0\n",
      "4           4             0\n",
      "..        ...           ...\n",
      "946         6             0\n",
      "947        29             0\n",
      "948        30             0\n",
      "949        25             0\n",
      "950        22             0\n",
      "\n",
      "[951 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Ensure 'Total Deaths'are numeric to avoid errors\n",
    "df_duration['Total Deaths'] = pd.to_numeric(df_duration['Total Deaths'], errors='coerce')\n",
    "\n",
    "# Convert 'Duration' to integer\n",
    "df_duration['Duration'] = df_duration['Duration'].astype(int)\n",
    "\n",
    "# Display the result\n",
    "print(df_duration[['Duration', 'Total Deaths']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Combined DataFrame:\n",
      "          County    Fire Name      Start  Contained  Acres  Duration  \\\n",
      "739  LOS ANGELES  SANTA ANITA 2008-04-26 2008-05-02    0.0         2   \n",
      "738  LOS ANGELES  SANTA ANITA 2008-04-26 2008-05-02  584.0         5   \n",
      "17     RIVERSIDE       APACHE 2008-04-29 2008-05-04  769.0         2   \n",
      "18     RIVERSIDE       APACHE 2008-04-29 2008-05-04    0.0         4   \n",
      "840  SANTA CLARA       Summit 2008-05-22 2008-06-15    0.0        15   \n",
      "\n",
      "     Total Deaths  Year  Month  \n",
      "739             0  2008      5  \n",
      "738             0  2008      4  \n",
      "17              0  2008      4  \n",
      "18              0  2008      5  \n",
      "840             0  2008      6  \n"
     ]
    }
   ],
   "source": [
    "# Our data only specify the dates of each fire, total acres and deaths.\n",
    "# This code ensures that we don't get our values duplicated on addition rows we previously created\n",
    " \n",
    "# Select only the columns needed\n",
    "df_duration = df_duration[['County', 'Fire Name', 'Start', 'Contained', 'Acres', 'Duration', 'Total Deaths', 'Year', 'Month']]\n",
    "\n",
    "# Identify rows with duplicate 'Fire Name', 'Start' combinations\n",
    "duplicate_groups = df_duration[df_duration.duplicated(subset=['Fire Name', 'Start'], keep=False)]\n",
    "\n",
    "# DataFrame with unique rows (no duplicates)\n",
    "unique_rows = df_duration.drop_duplicates(subset=['Fire Name', 'Start'], keep=False)\n",
    "\n",
    "# Function to sum the values and assign to the first row, then set the rest to 0\n",
    "def assign_to_first_row(group):\n",
    "    # Sort the group by 'Month' or any other column if needed\n",
    "    group = group.sort_values(by='Month')\n",
    "    \n",
    "    # Sum the values across the group\n",
    "    total_deaths = group['Total Deaths'].sum()\n",
    "    total_acres = group['Acres'].sum()\n",
    "    \n",
    "    # Assign the summed values to the first row\n",
    "    group.iloc[0, group.columns.get_loc('Total Deaths')] = total_deaths\n",
    "    group.iloc[0, group.columns.get_loc('Acres')] = total_acres\n",
    "    \n",
    "    # Set the rest of the rows to 0\n",
    "    if len(group) > 1:\n",
    "        group.iloc[1:, group.columns.get_loc('Total Deaths')] = 0\n",
    "        group.iloc[1:, group.columns.get_loc('Acres')] = 0\n",
    "    \n",
    "    return group\n",
    "\n",
    "# Apply the function to each group\n",
    "duplicate_rows_with_corrections = duplicate_groups.groupby(['Fire Name', 'Start'], group_keys=False).apply(assign_to_first_row)\n",
    "\n",
    "# Combine unique rows and corrected duplicate rows\n",
    "combined_df = pd.concat([unique_rows, duplicate_rows_with_corrections])\n",
    "\n",
    "# Reset the index to make sure the DataFrame is well-formed\n",
    "combined_df = combined_df.reset_index(drop=True)\n",
    "\n",
    "# Sort the combined DataFrame by the 'Start' date\n",
    "combined_df = combined_df.sort_values(by='Start')\n",
    "\n",
    "# Save the final DataFrame to a CSV file named 'combined_monthly_stats.csv' in the 'Outputs' folder\n",
    "combined_df.to_csv('Outputs/combined_monthly_stats.csv', index=False)\n",
    "\n",
    "# Display the final DataFrame\n",
    "print(\"Combined DataFrame:\")\n",
    "print(combined_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Year  total_fires  total_acres  median_yearly_duration  \\\n",
      "0   2008          154    1360379.0                       8   \n",
      "1   2009           67     422400.0                       7   \n",
      "2   2010           43      96745.0                       4   \n",
      "3   2011           62     174361.0                       4   \n",
      "4   2012           72     730733.0                       5   \n",
      "5   2013           54     546298.0                       6   \n",
      "6   2014           52     535318.0                       9   \n",
      "7   2015           55     773415.0                       8   \n",
      "8   2016           69     542952.0                      11   \n",
      "9   2017          125    1418515.0                       9   \n",
      "10  2018           77    1532601.0                       8   \n",
      "11  2019           61     267306.0                       6   \n",
      "12  2020          113    4178768.0                       9   \n",
      "13  2021           65    2502181.0                      11   \n",
      "14  2022           28     177266.0                       6   \n",
      "\n",
      "    mode_yearly_duration  total_deaths  \n",
      "0                      1            11  \n",
      "1                      3             3  \n",
      "2                      2             0  \n",
      "3                      2             0  \n",
      "4                      5             0  \n",
      "5                      5             1  \n",
      "6                      9             2  \n",
      "7                      5             7  \n",
      "8                      7             8  \n",
      "9                      4            47  \n",
      "10                     2            99  \n",
      "11                     2             3  \n",
      "12                     1            33  \n",
      "13                     2             0  \n",
      "14                     6             4  \n"
     ]
    }
   ],
   "source": [
    "# Calculate the statistics for each year\n",
    "# for yearly stats the original 'data' file was used as we do not need to devide duration by the month end\n",
    "\n",
    "# Exclude the first row from the DataFrame as this is header\n",
    "data_without_first_row = data.iloc[1:]\n",
    "\n",
    "# Calculate the statistics grouped by year\n",
    "yearly_stats_data = data.groupby('Year').agg(\n",
    "    total_fires=('Fire Name', 'size'),  # 'size' counts the number of rows\n",
    "    total_acres=('Acres', 'sum'),\n",
    "    median_yearly_duration=('Duration', 'median'),  # Median fire duration\n",
    "    mode_yearly_duration=('Duration', lambda x: x.mode()[0] if not x.mode().empty else None),  # Mode fire duration\n",
    "    total_deaths=('Total Deaths', 'sum')\n",
    ").reset_index()\n",
    "\n",
    "# Convert 'median_yearly_duration' to integers for visualization simplicity\n",
    "yearly_stats_data['median_yearly_duration'] = yearly_stats_data['median_yearly_duration'].astype(int)\n",
    "\n",
    "# Display the yearly statistics DataFrame\n",
    "print(yearly_stats_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['County', 'Fire Name', 'Start', 'Contained', 'Acres', 'Duration',\n",
      "       'Total Deaths', 'Year', 'Month'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# Display columns in my df (duration is taken into account month-end)\n",
    "print(combined_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Year  Month  total_fires  total_acres  median_monthly_duration  \\\n",
      "0  2008      4            2       1353.0                        3   \n",
      "1  2008      5            3      34870.0                        4   \n",
      "2  2008      6           57    1879198.0                       10   \n",
      "\n",
      "   total_deaths  \n",
      "0             0  \n",
      "1             0  \n",
      "2            33  \n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Exclude the first row from the DataFrame as this is header not to count the header\n",
    "# Fires names are not unque and were reused more than once, that is why size option was more suitable\n",
    "df_without_first_row = combined_df.iloc[1:]\n",
    "\n",
    "# Calculate statistics by Month of each Year\n",
    "monthly_stats = df_without_first_row.groupby(['Year', 'Month']).agg(\n",
    "    total_fires=('Fire Name', 'size'),\n",
    "    total_acres=('Acres', 'sum'),\n",
    "    median_monthly_duration=('Duration', 'median'),  # median was used due to a very wide range in fire durations\n",
    "    total_deaths=('Total Deaths', 'sum')\n",
    ").reset_index()\n",
    "\n",
    "# Convert 'avg_monthly_duration' to integers for viewers simplicity during visulization\n",
    "monthly_stats['median_monthly_duration'] = monthly_stats['median_monthly_duration'].astype(int)\n",
    "\n",
    "# Display the first few rows of the monthly_stats DataFrame\n",
    "print(monthly_stats.head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Year  total_fires  total_acres  median_yearly_duration  \\\n",
      "12  2020          113    4178768.0                       9   \n",
      "13  2021           65    2502181.0                      11   \n",
      "14  2022           28     177266.0                       6   \n",
      "\n",
      "    mode_yearly_duration  total_deaths    Tot_Damage  \n",
      "12                     1            33  4.725059e+09  \n",
      "13                     2             0  5.141048e+08  \n",
      "14                     6             4  3.133646e+08  \n"
     ]
    }
   ],
   "source": [
    "# Merge yearly statistical data with information about financial losses: \n",
    "\n",
    "# Read the Excel file wiht monetary losses information from \"Outputs\" folder\n",
    "file_path = 'Outputs\\merged_summary_dollar_data_8.25.24.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "# Calculate totals for each year\n",
    "totals_by_year = df.groupby('Year')['Tot_Damage'].sum().reset_index()\n",
    "# Merge both datas on year\n",
    "yearly_stats = pd.merge(yearly_stats_data, totals_by_year, on='Year', how='left')\n",
    "# Display the merged DataFrame to verify results\n",
    "print(yearly_stats.tail(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace NaN values with 0 in the merged DataFrame\n",
    "yearly_stats = yearly_stats.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Yearly stats saved to Outputs/yearly_stats.csv\n",
      "Monthly stats saved to Outputs/monthly_stats.csv\n"
     ]
    }
   ],
   "source": [
    "# Save the processed data to be used in HTML\n",
    "yearly_stats.to_csv('Outputs/yearly_stats.csv', index=False)\n",
    "monthly_stats.to_csv('Outputs/monthly_stats.csv', index=False)\n",
    "\n",
    "#Print resullts\n",
    "print(f\"Yearly stats saved to Outputs/yearly_stats.csv\") # Display the location and the file name where the data was saved\n",
    "print(f\"Monthly stats saved to Outputs/monthly_stats.csv\")  \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
}