02disasters.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Worldwide Natural Disasters from 1900 - 2021\n",
    "---\n",
    "Initial exploration and cleaning to get tropical storms in the US."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Year Disaster Group Disaster Subgroup      Disaster Type  \\\n",
      "0      1900        Natural    Climatological            Drought   \n",
      "1      1900        Natural    Climatological            Drought   \n",
      "2      1902        Natural       Geophysical         Earthquake   \n",
      "3      1902        Natural       Geophysical  Volcanic activity   \n",
      "4      1902        Natural       Geophysical  Volcanic activity   \n",
      "...     ...            ...               ...                ...   \n",
      "16121  2021        Natural      Hydrological              Flood   \n",
      "16122  2021        Natural      Hydrological              Flood   \n",
      "16123  2021        Natural        Biological           Epidemic   \n",
      "16124  2021        Natural      Hydrological              Flood   \n",
      "16125  2021        Natural      Hydrological              Flood   \n",
      "\n",
      "      Disaster Subtype Disaster Subsubtype   Event Name  \\\n",
      "0              Drought                 NaN          NaN   \n",
      "1              Drought                 NaN          NaN   \n",
      "2      Ground movement                 NaN          NaN   \n",
      "3             Ash fall                 NaN  Santa Maria   \n",
      "4             Ash fall                 NaN  Santa Maria   \n",
      "...                ...                 ...          ...   \n",
      "16121              NaN                 NaN          NaN   \n",
      "16122              NaN                 NaN          NaN   \n",
      "16123    Viral disease                 NaN   Meningitis   \n",
      "16124              NaN                 NaN          NaN   \n",
      "16125              NaN                 NaN          NaN   \n",
      "\n",
      "                                      Country  ISO           Region  ...  \\\n",
      "0                                  Cabo Verde  CPV   Western Africa  ...   \n",
      "1                                       India  IND    Southern Asia  ...   \n",
      "2                                   Guatemala  GTM  Central America  ...   \n",
      "3                                   Guatemala  GTM  Central America  ...   \n",
      "4                                   Guatemala  GTM  Central America  ...   \n",
      "...                                       ...  ...              ...  ...   \n",
      "16121                                   Yemen  YEM     Western Asia  ...   \n",
      "16122                            South Africa  ZAF  Southern Africa  ...   \n",
      "16123  Congo (the Democratic Republic of the)  COD    Middle Africa  ...   \n",
      "16124                                  Serbia  SRB  Southern Europe  ...   \n",
      "16125                             South Sudan  SSD  Northern Africa  ...   \n",
      "\n",
      "      Dis Mag Scale Start Year  Start Month Start Day  End Year  End Month  \\\n",
      "0               Km2       1900          NaN       NaN      1900        NaN   \n",
      "1               Km2       1900          NaN       NaN      1900        NaN   \n",
      "2           Richter       1902          4.0      18.0      1902        4.0   \n",
      "3               NaN       1902          4.0       8.0      1902        4.0   \n",
      "4               NaN       1902         10.0      24.0      1902       10.0   \n",
      "...             ...        ...          ...       ...       ...        ...   \n",
      "16121           Km2       2021          7.0      16.0      2021        8.0   \n",
      "16122           Km2       2021          2.0       1.0      2021        2.0   \n",
      "16123    Vaccinated       2021          9.0       7.0      2021        9.0   \n",
      "16124           Km2       2021          1.0      11.0      2021        1.0   \n",
      "16125           Km2       2021          5.0       NaN      2021       10.0   \n",
      "\n",
      "       End Day  Total Deaths  Total Damages ('000 US$)       CPI  \n",
      "0          NaN       11000.0                       NaN  3.221647  \n",
      "1          NaN     1250000.0                       NaN  3.221647  \n",
      "2         18.0        2000.0                   25000.0  3.350513  \n",
      "3          8.0        1000.0                       NaN  3.350513  \n",
      "4         24.0        6000.0                       NaN  3.350513  \n",
      "...        ...           ...                       ...       ...  \n",
      "16121      7.0          11.0                       NaN       NaN  \n",
      "16122     15.0          31.0                   75000.0       NaN  \n",
      "16123     13.0         131.0                       NaN       NaN  \n",
      "16124     12.0           NaN                       NaN       NaN  \n",
      "16125      7.0           7.0                       NaN       NaN  \n",
      "\n",
      "[16126 rows x 23 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd \n",
    "import re\n",
    "import numpy as np \n",
    "import matplotlib.pyplot as plt  \n",
    "\n",
    "import plotly.express as px\n",
    "import os\n",
    "\n",
    "df = pd.read_csv('data/disasters_1900_2021.csv')\n",
    "\n",
    "#drop columns\n",
    "df.drop(['Seq', 'Glide', 'Origin', 'Appeal', 'Declaration', 'Aid Contribution', 'Adm Level', 'Admin1 Code', 'Admin2 Code', 'Geo Locations', 'Associated Dis', 'Associated Dis2', 'OFDA Response', 'Latitude', 'Longitude', 'Local Time', 'River Basin', 'No Injured', 'No Affected', 'No Homeless', 'Total Affected', \"Insured Damages ('000 US$)\"], axis=1, inplace=True)\n",
    "\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 16126 entries, 0 to 16125\n",
      "Data columns (total 23 columns):\n",
      " #   Column                    Non-Null Count  Dtype  \n",
      "---  ------                    --------------  -----  \n",
      " 0   Year                      16126 non-null  int64  \n",
      " 1   Disaster Group            16126 non-null  object \n",
      " 2   Disaster Subgroup         16126 non-null  object \n",
      " 3   Disaster Type             16126 non-null  object \n",
      " 4   Disaster Subtype          13016 non-null  object \n",
      " 5   Disaster Subsubtype       1077 non-null   object \n",
      " 6   Event Name                3861 non-null   object \n",
      " 7   Country                   16126 non-null  object \n",
      " 8   ISO                       16126 non-null  object \n",
      " 9   Region                    16126 non-null  object \n",
      " 10  Continent                 16126 non-null  object \n",
      " 11  Location                  14334 non-null  object \n",
      " 12  Dis Mag Value             4946 non-null   float64\n",
      " 13  Dis Mag Scale             14936 non-null  object \n",
      " 14  Start Year                16126 non-null  int64  \n",
      " 15  Start Month               15739 non-null  float64\n",
      " 16  Start Day                 12498 non-null  float64\n",
      " 17  End Year                  16126 non-null  int64  \n",
      " 18  End Month                 15418 non-null  float64\n",
      " 19  End Day                   12570 non-null  float64\n",
      " 20  Total Deaths              11413 non-null  float64\n",
      " 21  Total Damages ('000 US$)  5245 non-null   float64\n",
      " 22  CPI                       15811 non-null  float64\n",
      "dtypes: float64(8), int64(3), object(12)\n",
      "memory usage: 2.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Year</th>\n",
       "      <th>Disaster Group</th>\n",
       "      <th>Disaster Subgroup</th>\n",
       "      <th>Disaster Type</th>\n",
       "      <th>Disaster Subtype</th>\n",
       "      <th>Disaster Subsubtype</th>\n",
       "      <th>Event Name</th>\n",
       "      <th>Country</th>\n",
       "      <th>ISO</th>\n",
       "      <th>Region</th>\n",
       "      <th>...</th>\n",
       "      <th>Dis Mag Scale</th>\n",
       "      <th>Start Year</th>\n",
       "      <th>Start Month</th>\n",
       "      <th>Start Day</th>\n",
       "      <th>End Year</th>\n",
       "      <th>End Month</th>\n",
       "      <th>End Day</th>\n",
       "      <th>Total Deaths</th>\n",
       "      <th>Total Damages ('000 US$)</th>\n",
       "      <th>CPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1900</td>\n",
       "      <td>Natural</td>\n",
       "      <td>Climatological</td>\n",
       "      <td>Drought</td>\n",
       "      <td>Drought</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cabo Verde</td>\n",
       "      <td>CPV</td>\n",
       "      <td>Western Africa</td>\n",
       "      <td>...</td>\n",
       "      <td>Km2</td>\n",
       "      <td>1900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.221647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1900</td>\n",
       "      <td>Natural</td>\n",
       "      <td>Climatological</td>\n",
       "      <td>Drought</td>\n",
       "      <td>Drought</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>India</td>\n",
       "      <td>IND</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>...</td>\n",
       "      <td>Km2</td>\n",
       "      <td>1900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1250000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.221647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1902</td>\n",
       "      <td>Natural</td>\n",
       "      <td>Geophysical</td>\n",
       "      <td>Earthquake</td>\n",
       "      <td>Ground movement</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Guatemala</td>\n",
       "      <td>GTM</td>\n",
       "      <td>Central America</td>\n",
       "      <td>...</td>\n",
       "      <td>Richter</td>\n",
       "      <td>1902</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1902</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>3.350513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1902</td>\n",
       "      <td>Natural</td>\n",
       "      <td>Geophysical</td>\n",
       "      <td>Volcanic activity</td>\n",
       "      <td>Ash fall</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Guatemala</td>\n",
       "      <td>GTM</td>\n",
       "      <td>Central America</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1902</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1902</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.350513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1902</td>\n",
       "      <td>Natural</td>\n",
       "      <td>Geophysical</td>\n",
       "      <td>Volcanic activity</td>\n",
       "      <td>Ash fall</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Guatemala</td>\n",
       "      <td>GTM</td>\n",
       "      <td>Central America</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1902</td>\n",
       "      <td>10.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1902</td>\n",
       "      <td>10.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.350513</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year Disaster Group Disaster Subgroup      Disaster Type Disaster Subtype  \\\n",
       "0  1900        Natural    Climatological            Drought          Drought   \n",
       "1  1900        Natural    Climatological            Drought          Drought   \n",
       "2  1902        Natural       Geophysical         Earthquake  Ground movement   \n",
       "3  1902        Natural       Geophysical  Volcanic activity         Ash fall   \n",
       "4  1902        Natural       Geophysical  Volcanic activity         Ash fall   \n",
       "\n",
       "  Disaster Subsubtype   Event Name     Country  ISO           Region  ...  \\\n",
       "0                 NaN          NaN  Cabo Verde  CPV   Western Africa  ...   \n",
       "1                 NaN          NaN       India  IND    Southern Asia  ...   \n",
       "2                 NaN          NaN   Guatemala  GTM  Central America  ...   \n",
       "3                 NaN  Santa Maria   Guatemala  GTM  Central America  ...   \n",
       "4                 NaN  Santa Maria   Guatemala  GTM  Central America  ...   \n",
       "\n",
       "  Dis Mag Scale Start Year  Start Month Start Day  End Year  End Month  \\\n",
       "0           Km2       1900          NaN       NaN      1900        NaN   \n",
       "1           Km2       1900          NaN       NaN      1900        NaN   \n",
       "2       Richter       1902          4.0      18.0      1902        4.0   \n",
       "3           NaN       1902          4.0       8.0      1902        4.0   \n",
       "4           NaN       1902         10.0      24.0      1902       10.0   \n",
       "\n",
       "   End Day  Total Deaths  Total Damages ('000 US$)       CPI  \n",
       "0      NaN       11000.0                       NaN  3.221647  \n",
       "1      NaN     1250000.0                       NaN  3.221647  \n",
       "2     18.0        2000.0                   25000.0  3.350513  \n",
       "3      8.0        1000.0                       NaN  3.350513  \n",
       "4     24.0        6000.0                       NaN  3.350513  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Climatological' 'Geophysical' 'Meteorological' 'Hydrological'\n",
      " 'Biological' 'Extra-terrestrial']\n"
     ]
    }
   ],
   "source": [
    "unique_subgroup = df['Disaster Subgroup'].unique()\n",
    "print(unique_subgroup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Year Disaster Group Disaster Subgroup Disaster Type  Disaster Subtype  \\\n",
      "7      1904        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "14     1906        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "18     1909        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "19     1909        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "22     1909        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "...     ...            ...               ...           ...               ...   \n",
      "16094  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16095  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16096  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16115  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16119  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "\n",
      "      Disaster Subsubtype                          Event Name  \\\n",
      "7                     NaN                                 NaN   \n",
      "14                    NaN                                 NaN   \n",
      "18                    NaN                                 NaN   \n",
      "19                    NaN                                 NaN   \n",
      "22                    NaN                                 NaN   \n",
      "...                   ...                                 ...   \n",
      "16094                 NaN             Tropical storm 'Koguma'   \n",
      "16095                 NaN           Tropical cyclone 'Eloise'   \n",
      "16096                 NaN           Tropical cyclone 'Eloise'   \n",
      "16115                 NaN                    Hurricane 'Elsa'   \n",
      "16119                 NaN  Tropical cyclone 'Conson' (Jolina)   \n",
      "\n",
      "                                Country  ISO              Region  ...  \\\n",
      "7                            Bangladesh  BGD       Southern Asia  ...   \n",
      "14                            Hong Kong  HKG        Eastern Asia  ...   \n",
      "18                           Bangladesh  BGD       Southern Asia  ...   \n",
      "19                           Bangladesh  BGD       Southern Asia  ...   \n",
      "22                                Haiti  HTI           Caribbean  ...   \n",
      "...                                 ...  ...                 ...  ...   \n",
      "16094                          Viet Nam  VNM  South-Eastern Asia  ...   \n",
      "16095                      South Africa  ZAF     Southern Africa  ...   \n",
      "16096                          Zimbabwe  ZWE      Eastern Africa  ...   \n",
      "16115  Saint Vincent and the Grenadines  VCT           Caribbean  ...   \n",
      "16119                          Viet Nam  VNM  South-Eastern Asia  ...   \n",
      "\n",
      "      Dis Mag Scale Start Year  Start Month Start Day  End Year  End Month  \\\n",
      "7               Kph       1904         11.0       NaN      1904       11.0   \n",
      "14              Kph       1906          9.0       8.0      1906        9.0   \n",
      "18              Kph       1909         10.0      15.0      1909       10.0   \n",
      "19              Kph       1909         12.0       NaN      1909       12.0   \n",
      "22              Kph       1909         11.0      12.0      1909       11.0   \n",
      "...             ...        ...          ...       ...       ...        ...   \n",
      "16094           Kph       2021          6.0      12.0      2021        6.0   \n",
      "16095           Kph       2021          1.0      24.0      2021        1.0   \n",
      "16096           Kph       2021          1.0      23.0      2021        1.0   \n",
      "16115           Kph       2021          7.0       2.0      2021        7.0   \n",
      "16119           Kph       2021          9.0      12.0      2021        9.0   \n",
      "\n",
      "       End Day  Total Deaths  Total Damages ('000 US$)       CPI  \n",
      "7          NaN           NaN                       NaN  3.479379  \n",
      "14         8.0       10000.0                   20000.0  3.479379  \n",
      "18        15.0         172.0                       NaN  3.479379  \n",
      "19         NaN           NaN                       NaN  3.479379  \n",
      "22        12.0         150.0                       NaN  3.479379  \n",
      "...        ...           ...                       ...       ...  \n",
      "16094     13.0           1.0                   10000.0       NaN  \n",
      "16095     24.0           4.0                       NaN       NaN  \n",
      "16096     23.0           3.0                       NaN       NaN  \n",
      "16115      3.0           NaN                       NaN       NaN  \n",
      "16119     12.0           2.0                       NaN       NaN  \n",
      "\n",
      "[2420 rows x 23 columns]\n"
     ]
    }
   ],
   "source": [
    "storm_df = df[(df['Disaster Subtype'] == 'Tropical cyclone')].copy()\n",
    "\n",
    "print(storm_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Year Disaster Group Disaster Subgroup Disaster Type  Disaster Subtype  \\\n",
      "845    1900        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "866    1906        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "867    1906        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "875    1909        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "876    1909        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "...     ...            ...               ...           ...               ...   \n",
      "16048  2020        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16049  2020        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16091  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16092  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16093  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "\n",
      "      Disaster Subsubtype                   Event Name  \\\n",
      "845                   NaN                          NaN   \n",
      "866                   NaN                          NaN   \n",
      "867                   NaN                          NaN   \n",
      "875                   NaN                          NaN   \n",
      "876                   NaN                          NaN   \n",
      "...                   ...                          ...   \n",
      "16048                 NaN   Tropical storm 'Cristobal'   \n",
      "16049                 NaN           Hurricane 'Isaias'   \n",
      "16091                 NaN             Hurricane 'Elsa'   \n",
      "16092                 NaN  Tropical depression 'Henri'   \n",
      "16093                 NaN         Tropical storm 'Ida'   \n",
      "\n",
      "                              Country  ISO Continent  ... Dis Mag Scale  \\\n",
      "845    United States of America (the)  USA  Americas  ...           Kph   \n",
      "866    United States of America (the)  USA  Americas  ...           Kph   \n",
      "867    United States of America (the)  USA  Americas  ...           Kph   \n",
      "875    United States of America (the)  USA  Americas  ...           Kph   \n",
      "876    United States of America (the)  USA  Americas  ...           Kph   \n",
      "...                               ...  ...       ...  ...           ...   \n",
      "16048  United States of America (the)  USA  Americas  ...           Kph   \n",
      "16049  United States of America (the)  USA  Americas  ...           Kph   \n",
      "16091  United States of America (the)  USA  Americas  ...           Kph   \n",
      "16092  United States of America (the)  USA  Americas  ...           Kph   \n",
      "16093  United States of America (the)  USA  Americas  ...           Kph   \n",
      "\n",
      "       Start Year Start Month  Start Day  End Year  End Month  End Day  \\\n",
      "845          1900         9.0        8.0      1900        9.0      8.0   \n",
      "866          1906         NaN        NaN      1906        NaN      NaN   \n",
      "867          1906         NaN        NaN      1906        NaN      NaN   \n",
      "875          1909         NaN        NaN      1909        NaN      NaN   \n",
      "876          1909         NaN        NaN      1909        NaN      NaN   \n",
      "...           ...         ...        ...       ...        ...      ...   \n",
      "16048        2020         6.0        7.0      2020        6.0      7.0   \n",
      "16049        2020         7.0       31.0      2020        8.0      2.0   \n",
      "16091        2021         7.0        2.0      2021        7.0      9.0   \n",
      "16092        2021         8.0       20.0      2021        8.0     25.0   \n",
      "16093        2021         8.0       28.0      2021        9.0      2.0   \n",
      "\n",
      "       Total Deaths  Total Damages ('000 US$)         CPI  \n",
      "845          6000.0                   30000.0    3.221647  \n",
      "866           164.0                       NaN    3.479379  \n",
      "867           134.0                       NaN    3.479379  \n",
      "875           350.0                       NaN    3.479379  \n",
      "876            41.0                       NaN    3.479379  \n",
      "...             ...                       ...         ...  \n",
      "16048           1.0                  325000.0  100.000000  \n",
      "16049          16.0                 4800000.0  100.000000  \n",
      "16091           1.0                  775000.0         NaN  \n",
      "16092           NaN                  550000.0         NaN  \n",
      "16093          77.0                10000000.0         NaN  \n",
      "\n",
      "[132 rows x 22 columns]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Drop the 'Region' column from the DataFrame to prevent conflict with merges to other data\n",
    "storm_df_dropped = storm_df.drop('Region', axis=1)\n",
    "\n",
    "# Filter the DataFrame for rows where 'ISO' equals 'USA' and make a copy\n",
    "us_storms = storm_df_dropped[storm_df_dropped['ISO'] == 'USA'].copy()\n",
    "\n",
    "print(us_storms)\n",
    "\n",
    "# Save the DataFrame to a CSV file\n",
    "us_storms.to_csv('data/us_storms.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_storms = storm_df[storm_df['ISO'] == 'USA'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Galveston (Texas)', 'Florida', 'Mississippi, Alabama, Pensacola',\n",
       "       'Grand Isle (Louisiana)', 'Velasco (Texas)',\n",
       "       'Cavelston (Texas), New Orleans (Louisiana)',\n",
       "       'Louisiana (Southwest)', 'Florida Keys, South Texas', 'Louisiana',\n",
       "       'Florida, Alabama, Pensacola', 'Lake Okeechobee (Florida)',\n",
       "       'Freeport (Texas)', 'South Texas', 'New York',\n",
       "       'Georgia, Carolinas', 'East coast', 'Florida, Louisiana',\n",
       "       'East Coast', 'South Carolina, North Carolina', nan,\n",
       "       'Connie (North Carolina)', 'Gulf Coast', 'Texas',\n",
       "       'Florida, Louisiana, Alabama, Mississippi',\n",
       "       'Mississippi, Louisiana, Virginia', 'East', 'Gulf coast',\n",
       "       'Texas, Louisiana', 'Kalamazoo', 'Nebraska', 'Hawaii',\n",
       "       'Florida, Panhandle, Georgia',\n",
       "       'Florida, Arkansas, Kentucky, South Dakota, Iowa, Michgigan, Indianapolis, Missouri',\n",
       "       'Louisiana, Mississippi, Florida Panhandle', 'Colorado, Texas',\n",
       "       'Floride', 'South Caroline', 'Texas, Louisiane',\n",
       "       'North Carolina, Delaware, New York, Richmond, Maine, Rhode Island, Connecticut, Massachusetts, Indiana, Michigan, Ohio, Kentucky',\n",
       "       'New England, New York, New Jersey',\n",
       "       'Massachusetts, New York, Rhode Island, New Jersey, Connecticut',\n",
       "       'Kauai, Nihau, Oahu, Hawai', 'Florida, Louisiana, Bahamas',\n",
       "       'Majuro ( Marshall Isl.)', 'North Carolina',\n",
       "       'Minnesota, Kansas, Oklahoma, South Dakota, Texas, Arkansas, Nebraska, Louisiana',\n",
       "       'Georgia, Michigan, Florida, Alabama',\n",
       "       'Florida, Georgia, Alabama, North Carolina',\n",
       "       'Florida, Alabama, Mississipi',\n",
       "       'North Carolina, South Carolina, Virginia, Maryland, West Virginia, Pennsylvania, Ohio, Washington DC',\n",
       "       'Louisiana, Alabama, South Carolina, North Carolina',\n",
       "       'Florida, Mississippi, Alabama, Lousiana',\n",
       "       'North Carolina, Virginia, South Carolina',\n",
       "       'North Carolina, Florida, South Carolina, Virginia, Maryland, Pennsylvania, New Jersey, New York, Delaware, Rhode Island, Connecticut, Massachusetts, Vermont',\n",
       "       'Kennedy county (Texas)', 'Bahamas, Floride',\n",
       "       'Bahamas, North Carolina, Virginia',\n",
       "       'Miami-Dade, Monroe, Broward, Collier districts (Florida province)',\n",
       "       'Texas, Mississippi, Louisiana, Florida, Pennsylvania, South Carolina, North Carolina, Georgia, Pensylvania, New Jersey, Virginia, Iowa provinces',\n",
       "       'St. Tammany, Terrebonne, Lafayette, Acadia, Evangeline, Rapides districts (Louisiana province)',\n",
       "       'Louisiane, Mississippi, Alabama, Tennessee provinces',\n",
       "       'North Carolina, Maryland, Virginia, West Virginia, Delaware, Pennsylvania, New Jersey, District of Columbia provinces',\n",
       "       'Orleans, St. John the Baptist, Terrebonne, St. Tammany, Tangipahoa districts (Louisiana province), Hancock, Harrison, Jackson, Pike, Walthall districts (Mississippi province), Bay district (Florida province), Mobile, Baldwin, Lee districts (Alabama province), Sumter, Bulloch, Fulton, DeKalb, Cobb, Clayton, Gwinnett districts (Georgia province), Wake district (North Carolina province)',\n",
       "       'Alabama, Louisiana, Mississippi, Florida, Pennsylvania, Maryland, New Jersey, Ohio, North Carolina, West Virginia, Georgia, Tennessee provinces',\n",
       "       'Martin, Palm Beach districts (Florida province), North Carolina, South Carolina, Ohio provinces',\n",
       "       'Richmond, Lynchburg, Chesterfield districts (Virginia province), South Carolina, North Carolina provinces',\n",
       "       'Bay, Calhoun, Escambia, Franklin, Gadsden, Gulf, Holmes, Jackson, Jefferson, Leon, Liberty, Madison, Okaloosa, Santa Rosa, Taylor, Wakulla, Walton, Washington districts (Florida province), South Carolina, North Carolina provinces',\n",
       "       'Florida province',\n",
       "       'Mobile, Bayou La Batre, Dauphin Island, Coden areas (Mobile district, Alabama province), New Orleans city (Orleans district, Louisiana province), Slidell area (St. Tammany district, Louisiana province), St. Bernard district (Louisiana province), Biloxi, Gulfport cities (Harrison district, Mississippi province), Pascagoula city (Jackson district, Mississippi province), Waveland, Bay St. Louis cities (Hancock district, Mississippi province), Georgia, Florida provinces',\n",
       "       'Florida Keys, Naples areas (Collier district, Florida province)',\n",
       "       'Louisiana, Texas, Mississippi provinces',\n",
       "       'Florida, Georgia provinces',\n",
       "       'South Carolina, North Carolina, Virginia provinces',\n",
       "       'Texas, Oklahoma, Missouri provinces',\n",
       "       'Panama city beach (Bay district, Florida province), Aransas, Bexar, Brooks, Calhoun, Cameron, Hidalgo, Jim Wells, Kenedy, Kleberg, Nueces, Refugio, San Patricio, Starr, Victoria, Willacy districts (Texas province)',\n",
       "       'Florida, Georgia, Alabama, Mississippi provinces',\n",
       "       'Galveston, Brazoria, Harris, Chambers, Jefferson districts (Texas province), Louisiana, Tennessee, Arkansas, Ohio, Indiana, Illinois, Missouri, Kentucky, Pennsylvania, Michigan provinces',\n",
       "       'Louisiana, Mississippi, Texas, Alabama provinces',\n",
       "       'Florida, North Carolina, Maryland provinces',\n",
       "       'Kent, Sussex districts (Delaware province), Atlantic, Burlington, Cape May, Cumberland, Monmouth, Ocean districts (New Jersey province), Virginia, New York, North Carolina, Pennsylvannia provinces',\n",
       "       'Maine province',\n",
       "       'North Carolina, Virginia, Maryland, Massachusetts, Rhode Island provinces',\n",
       "       'Texas province',\n",
       "       'New York, New Jersey, Pennsylvania, North Carolina, Virginia, Maryland, District of Columbia, Connecticut, Florida provinces',\n",
       "       'Louisiana, Mississippi, Alabama, Texas, New York, Pennsylvania, District of Columbia, Georgia, Maryland, Tennessee, Virginia provinces',\n",
       "       'New York, New Jersey, Pennsylvania, Connecticut, Ohio, Delaware, Rhode Island, Maryland, Massachusetts, Maine, New Hampshire, North Carolina, Vermont, Virginia, District of Columbia, West Virginia provinces',\n",
       "       'Mississippi, Louisiana provinces',\n",
       "       'Bay, Calhoun, Escambia, Franklin, Gadsden, Gulf, Holmes, Jackson, Jefferson, Leon, Liberty, Madison, Okaloosa, Santa Rosa, Taylor, Wakulla, Walton, Washington, Brevard, Citrus, Hardee, Hernando, Hillsborough, Indian River, Lake, Manatee, Marion, Orange, Osceola, Pasco, Pinellas, Polk, Seminole, Sumter, Volusia districts',\n",
       "       'Hawaii province',\n",
       "       'Georgia, Florida, South Carolina, New York, New Jersey; Cedar Key, Florida, Aurora (North Carolina); Virginia Tidewater region',\n",
       "       'Florida, Georgia, South Carolina, North Carolina, Virginia provinces',\n",
       "       'Eastern Texas (Rockport, Corpus Chrsti, Port Lavaca, Cypress area of Houston, Houston metro area, Beaumont, Port Arthur, Angelina, Aransas, Atascosa, Austin, Bastrop, Bee, Bexar, Brazoria, Brazos, Burleson, Caldwell, Calhoun, Cameron, Chambers, Colorado, Comal, DeWitt, Fayette, Fort Bend, Galveston, Goliad, Gonzales, Grimes, Guadalupe, Hardin, Harris, Jackson, Jasper, Jefferson, Jim Wells, Karnes, Kerr, Kleberg, Lavaca, Lee, Leon, Liberty, Live Oak, Madison, Matagorda, Montgomery, Newton, Nueces, Orange, Polk, Refugio, Sabine, San Jacinto, San Patricio, Trinity, Tyler, Victoria, Walker, Waller, Washington, Wharton, Willacy, Wilson, San Augustine), Southwestern Louisiana (Acadia, Forrest, Iberia, Lafayette, Vernon, Beauregard, Calcasieu, Cameron (Hackberry), Jefferson Davis, Vermillion, Allen, Natchitoches, Rapides, Sabine)',\n",
       "       'Keys islands, Monroe, South Florida, Jacksonville (Duval), Marco Island, Naples (Collier), Fort Lauderdale (Broward), Lakeland (Polk), Orlando (Orange), Clay (Florida), Savannah, Tybee Island (Chatham), Brunswick, St. Simons Island (Glynn), McIntosh, Camden (Georgia), Charleston, Folly Beach, the Isle of Palms, Sullivan’s Island (Charleston) Hilton Head, Beaufort, Edisto Beach (Colleton) (South Carolina)',\n",
       "       'Jersey shore, Seaside Park, Point Pleasant Beach (Ocean), Long Branch (Monmouth) (New Jersey), Fernandina beach (Nassau-Florida)',\n",
       "       'Mexico Gulf, Louisiania, Mississippi (coastal areas, Biloxi), Alabama (Dauphin Island, Autauga, Chilton, Lowndes Counties), Florida, South Carolina (Pickens, Laurens, Spartanburg, Newberry, Greenville, Union), North Carolina (Polk, Wilkes, Ashe )',\n",
       "       'South and North Carolina, Virginia',\n",
       "       'Florida, Georgia, Alabama, North Carolina, Virginia, Maryland',\n",
       "       'Southeast',\n",
       "       'Louisiana, Mississippi, Arkansas, Oklahoma, Great Lakes region',\n",
       "       'Florida, Georgia, South Carolina, North Carolina, Virginia',\n",
       "       'Florida, North Carolina, Virginia',\n",
       "       'Louisiana, Texas, Mississippi, Georgia, North and South Carolina',\n",
       "       'Alabama, Florida',\n",
       "       'Louisiana, Mississippi, Alabama, northern Georgia, south and North Carolinas',\n",
       "       'Louisiana, Texas, Arkansas, Mississippi ',\n",
       "       'errebonne, Plaquemines, Lafourche Parishes (Louisiana)',\n",
       "       'North Carolina, South Carolina, New York, Maryland, Delaware, New Jersey, Connecticut, Florida, Virginia, Pennsylvania, New Hampshire',\n",
       "       'California, Georgia, Florida',\n",
       "       'Rhode Isl., Connecticut, Vermont, New York, New Jersey states',\n",
       "       'Louisiana, New York, New Jersey, Maryland, Connecticut, Virginia, Pennsylvania'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_storms['Location'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter us_storms for regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Galveston (Texas) -> TX\n",
      "Gainesville (Georgia) -> GA\n",
      "Florida -> FL\n",
      "Mississippi, Alabama, Pensacola -> AL, FL, MS\n",
      "Grand Isle (Louisiana) -> LA\n",
      "Velasco (Texas) -> TX\n",
      "Ohio, Indiana, Texas -> IN, OH, TX\n",
      "East Coast -> East Coast\n",
      "Some city (West Coast) -> West Coast\n",
      "A town in the Gulf Coast region -> Gulf Coast\n"
     ]
    }
   ],
   "source": [
    "from categorizeregion import state_map\n",
    "\n",
    "from categorizeregion import state_map\n",
    "\n",
    "def extract_states(location):\n",
    "    region_abbreviations = {\n",
    "        'East Coast': 'East Coast',\n",
    "        'West Coast': 'West Coast',\n",
    "        'Gulf Coast': 'Gulf Coast',\n",
    "    }\n",
    "    if not isinstance(location, str):\n",
    "        return ''\n",
    "    \n",
    "    # Regular expression for recognized regions\n",
    "    region_pattern = r'\\b(?:' + '|'.join(re.escape(region) for region in region_abbreviations) + r')\\b'\n",
    "    \n",
    "    # First, check if the location matches any of the recognized regions\n",
    "    found_regions = re.findall(region_pattern, location, re.IGNORECASE)\n",
    "    if found_regions:\n",
    "        # Assuming only one region can be mentioned in the location, return the first match\n",
    "        return region_abbreviations[found_regions[0].title()]\n",
    "    \n",
    "    # Regular expression to match state names or abbreviations in various contexts\n",
    "    pattern = r'\\b(?:' + '|'.join(re.escape(s) for s in state_map.keys()) + r')\\b'\n",
    "    \n",
    "    # Find all matches in the location string\n",
    "    found_states = re.findall(pattern, location, re.IGNORECASE)\n",
    "    \n",
    "    # Convert state names to their abbreviations\n",
    "    state_abbrs = set()  # Use a set to avoid duplicates\n",
    "    for state in found_states:\n",
    "        state_title = state.title()  # Ensure the first letter of each word is uppercase\n",
    "        if state_title in state_map:\n",
    "            state_abbrs.add(state_map[state_title])\n",
    "    \n",
    "    return ', '.join(sorted(state_abbrs))  # Return a string of unique, sorted abbreviations\n",
    "\n",
    "# Test the function with examples\n",
    "location_examples = ['Galveston (Texas)', 'Gainesville (Georgia)', 'Florida',\n",
    "                     'Mississippi, Alabama, Pensacola', 'Grand Isle (Louisiana)',\n",
    "                     'Velasco (Texas)', 'Ohio, Indiana, Texas', 'East Coast',\n",
    "                     'Some city (West Coast)', 'A town in the Gulf Coast region']\n",
    "\n",
    "for loc in location_examples:\n",
    "    print(f\"{loc} -> {extract_states(loc)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Year Disaster Group Disaster Subgroup Disaster Type  Disaster Subtype  \\\n",
      "845    1900        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "866    1906        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "867    1906        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "875    1909        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "876    1909        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "...     ...            ...               ...           ...               ...   \n",
      "16048  2020        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16049  2020        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16091  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16092  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "16093  2021        Natural    Meteorological         Storm  Tropical cyclone   \n",
      "\n",
      "      Disaster Subsubtype                   Event Name  \\\n",
      "845                   NaN                          NaN   \n",
      "866                   NaN                          NaN   \n",
      "867                   NaN                          NaN   \n",
      "875                   NaN                          NaN   \n",
      "876                   NaN                          NaN   \n",
      "...                   ...                          ...   \n",
      "16048                 NaN   Tropical storm 'Cristobal'   \n",
      "16049                 NaN           Hurricane 'Isaias'   \n",
      "16091                 NaN             Hurricane 'Elsa'   \n",
      "16092                 NaN  Tropical depression 'Henri'   \n",
      "16093                 NaN         Tropical storm 'Ida'   \n",
      "\n",
      "                              Country  ISO            Region  ... Start Year  \\\n",
      "845    United States of America (the)  USA  Northern America  ...       1900   \n",
      "866    United States of America (the)  USA  Northern America  ...       1906   \n",
      "867    United States of America (the)  USA  Northern America  ...       1906   \n",
      "875    United States of America (the)  USA  Northern America  ...       1909   \n",
      "876    United States of America (the)  USA  Northern America  ...       1909   \n",
      "...                               ...  ...               ...  ...        ...   \n",
      "16048  United States of America (the)  USA  Northern America  ...       2020   \n",
      "16049  United States of America (the)  USA  Northern America  ...       2020   \n",
      "16091  United States of America (the)  USA  Northern America  ...       2021   \n",
      "16092  United States of America (the)  USA  Northern America  ...       2021   \n",
      "16093  United States of America (the)  USA  Northern America  ...       2021   \n",
      "\n",
      "      Start Month  Start Day End Year  End Month  End Day  Total Deaths  \\\n",
      "845           9.0        8.0     1900        9.0      8.0        6000.0   \n",
      "866           NaN        NaN     1906        NaN      NaN         164.0   \n",
      "867           NaN        NaN     1906        NaN      NaN         134.0   \n",
      "875           NaN        NaN     1909        NaN      NaN         350.0   \n",
      "876           NaN        NaN     1909        NaN      NaN          41.0   \n",
      "...           ...        ...      ...        ...      ...           ...   \n",
      "16048         6.0        7.0     2020        6.0      7.0           1.0   \n",
      "16049         7.0       31.0     2020        8.0      2.0          16.0   \n",
      "16091         7.0        2.0     2021        7.0      9.0           1.0   \n",
      "16092         8.0       20.0     2021        8.0     25.0           NaN   \n",
      "16093         8.0       28.0     2021        9.0      2.0          77.0   \n",
      "\n",
      "       Total Damages ('000 US$)         CPI  \\\n",
      "845                     30000.0    3.221647   \n",
      "866                         NaN    3.479379   \n",
      "867                         NaN    3.479379   \n",
      "875                         NaN    3.479379   \n",
      "876                         NaN    3.479379   \n",
      "...                         ...         ...   \n",
      "16048                  325000.0  100.000000   \n",
      "16049                 4800000.0  100.000000   \n",
      "16091                  775000.0         NaN   \n",
      "16092                  550000.0         NaN   \n",
      "16093                10000000.0         NaN   \n",
      "\n",
      "                                            State  \n",
      "845                                            TX  \n",
      "866                                            FL  \n",
      "867                                    AL, FL, MS  \n",
      "875                                            LA  \n",
      "876                                            TX  \n",
      "...                                           ...  \n",
      "16048                                          LA  \n",
      "16049  CT, DE, FL, MD, NC, NH, NJ, NY, PA, SC, VA  \n",
      "16091                                  CA, FL, GA  \n",
      "16092                              CT, NJ, NY, VT  \n",
      "16093                  CT, LA, MD, NJ, NY, PA, VA  \n",
      "\n",
      "[132 rows x 24 columns]\n"
     ]
    }
   ],
   "source": [
    "us_storms['State'] = us_storms['Location'].apply(extract_states)\n",
    "print(us_storms)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                       Location       State\n",
      "845                           Galveston (Texas)          TX\n",
      "866                                     Florida          FL\n",
      "867             Mississippi, Alabama, Pensacola  AL, FL, MS\n",
      "875                      Grand Isle (Louisiana)          LA\n",
      "876                             Velasco (Texas)          TX\n",
      "883                                     Florida          FL\n",
      "899  Cavelston (Texas), New Orleans (Louisiana)      LA, TX\n",
      "906                       Louisiana (Southwest)          LA\n",
      "908                   Florida Keys, South Texas      FL, TX\n",
      "930                                   Louisiana          LA\n"
     ]
    }
   ],
   "source": [
    "print(us_storms[['Location', 'State']].head(10))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "usstorms = us_storms.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Region\n",
      "Gulf Coast    90\n",
      "East Coast    26\n",
      "Other         13\n",
      "Island         2\n",
      "West Coast     1\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "from categorizeregion import add_region_column\n",
    "\n",
    "us_storms = add_region_column(us_storms)\n",
    "\n",
    "us_storms.to_excel('data/cleanusstorms.xlsx', index=False)\n",
    "print(us_storms['Region'].value_counts())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Follow up later\n",
    "---\n",
    "###### Look at regions and consider how to handle storms that impact multiple areas\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}