public-assistance.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f64e6921",
   "metadata": {},
   "source": [
    "# Public Assistance Funded Projects Details - v1\n",
    "Author: Mark Bauer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "47349bc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import duckdb\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9453672b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Python implementation: CPython\n",
      "Python version       : 3.11.0\n",
      "IPython version      : 8.6.0\n",
      "\n",
      "duckdb: 1.0.0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# reproducibility\n",
    "%reload_ext watermark\n",
    "%watermark -v -p duckdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1b566886",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This notebook was executed on 2024-10-20.\n"
     ]
    }
   ],
   "source": [
    "# data retrieved\n",
    "current_date = datetime.now()\n",
    "print(f\"This notebook was executed on {current_date.strftime('%Y-%m-%d')}.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "549fbc10",
   "metadata": {},
   "source": [
    "Note: This analysis uses the Federal Emergency Management Agency’s OpenFEMA API, but is not endorsed by FEMA. The Federal Government or FEMA cannot vouch for the data or analyses derived from these data after the data have been retrieved from the Agency's website(s).\n",
    "\n",
    "Read more about OpenFEMA's [Terms and Conditions](https://www.fema.gov/about/openfema/terms-conditions)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5bbce5a",
   "metadata": {},
   "source": [
    "# OpenFEMA Dataset: Public Assistance Funded Projects Details - v1\n",
    "\n",
    "## Dataset\n",
    "Federal Emergency Management Agency (FEMA), OpenFEMA Dataset: Public Assistance Funded Projects Details - v1. Retrieved from https://www.fema.gov/openfema-data-page/public-assistance-funded-projects-details-v1. This product uses the FEMA OpenFEMA API, but is not endorsed by FEMA. The Federal Government or FEMA cannot vouch for the data or analyses derived from these data after the data have been retrieved from the Agency's website(s).\n",
    "\n",
    "Read more about [OpenFEMA Terms and Conditions](https://www.fema.gov/about/openfema/terms-conditions).\n",
    "\n",
    "## Dataset Description\n",
    ">The Public Assistance Funded Projects Details dataset contains obligated (financial obligation to grantee) Public Assistance projects, lists public assistance recipients designated as applicants in the data, and a list of every funded, individual project, called project worksheets. Open projects still under pre-obligation processing are not represented. Applicant details can be obtained by relating this dataset with the OpenFEMA Public Assistance Applicants dataset using the Applicant ID field.\n",
    "\n",
    "Source: [Public Assistance Funded Projects Details - v1](https://www.fema.gov/openfema-data-page/public-assistance-funded-projects-details-v1)\n",
    "\n",
    "## Disaster Delcarations\n",
    "For more information on the disaster declaration process:\n",
    "- Information about disasters: https://www.fema.gov/disasters\n",
    "- How a disaster is declared: https://www.fema.gov/disasters/how-declared"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68389128",
   "metadata": {},
   "source": [
    "# Additional Resources\n",
    "To examine other cool data visualizations about this data, visit FEMA's [Public Assistance Program Summary of Obligations](https://www.fema.gov/data-visualization/public-assistance-program-summary-obligations) page."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72299437",
   "metadata": {},
   "source": [
    "# Read In Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f49e63f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────────┬─────────────────────┬──────────────┬───┬──────────────────────┬──────────────────────┐\n",
       "│ disasterNumber │   declarationDate   │ incidentType │ … │         hash         │          id          │\n",
       "│     int64      │      timestamp      │   varchar    │   │       varchar        │       varchar        │\n",
       "├────────────────┼─────────────────────┼──────────────┼───┼──────────────────────┼──────────────────────┤\n",
       "│           4023 │ 2011-09-02 00:00:00 │ Hurricane    │ … │ 5ff26ea21f9af4de5d…  │ d247f735-5e5d-4d47…  │\n",
       "│           4155 │ 2013-11-08 00:00:00 │ Severe Storm │ … │ 39a1559d2dd1a04afa…  │ 39552c91-2dc1-4519…  │\n",
       "│           4226 │ 2015-06-26 00:00:00 │ Severe Storm │ … │ aef455b4030e92d3c7…  │ d02b7bb6-fb6c-4fb4…  │\n",
       "│           4314 │ 2017-05-22 00:00:00 │ Severe Storm │ … │ 727e5a2e3628d483b2…  │ 044e88f1-b38e-483c…  │\n",
       "│           4337 │ 2017-09-10 00:00:00 │ Hurricane    │ … │ ab453e975b05cc1163…  │ 98731455-3b88-4fc0…  │\n",
       "├────────────────┴─────────────────────┴──────────────┴───┴──────────────────────┴──────────────────────┤\n",
       "│ 5 rows                                                                           22 columns (5 shown) │\n",
       "└───────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a duckdb database connection\n",
    "con = duckdb.connect()\n",
    "\n",
    "# create public_assistance table from OpenFEMA parquet file\n",
    "con.execute(\"\"\"\n",
    "    CREATE TABLE public_assistance AS\n",
    "    FROM read_csv('https://www.fema.gov/api/open/v1/PublicAssistanceFundedProjectsDetails.csv')\n",
    "\"\"\"\n",
    ")\n",
    "\n",
    "# sanity check\n",
    "sql = \"\"\"\n",
    "    SELECT *\n",
    "    FROM public_assistance\n",
    "    LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bce76b86",
   "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>disasterNumber</th>\n",
       "      <th>declarationDate</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>pwNumber</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>applicantId</th>\n",
       "      <th>damageCategoryCode</th>\n",
       "      <th>projectSize</th>\n",
       "      <th>county</th>\n",
       "      <th>countyCode</th>\n",
       "      <th>...</th>\n",
       "      <th>stateNumberCode</th>\n",
       "      <th>projectAmount</th>\n",
       "      <th>federalShareObligated</th>\n",
       "      <th>totalObligated</th>\n",
       "      <th>obligatedDate</th>\n",
       "      <th>dcc</th>\n",
       "      <th>damageCategory</th>\n",
       "      <th>lastRefresh</th>\n",
       "      <th>hash</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4023</td>\n",
       "      <td>2011-09-02</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>247</td>\n",
       "      <td>MCMDG00 Trumbull Ave Revetment</td>\n",
       "      <td>009-47500-00</td>\n",
       "      <td>G - Recreational or Other</td>\n",
       "      <td>Large</td>\n",
       "      <td>New Haven</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>2012-04-05</td>\n",
       "      <td>G</td>\n",
       "      <td>Recreational or Other</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>5ff26ea21f9af4de5dce40c1f4ab2324b11b5fcb</td>\n",
       "      <td>d247f735-5e5d-4d47-9b4b-58a2fa6396b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4155</td>\n",
       "      <td>2013-11-08</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>173</td>\n",
       "      <td>SG107  - Donated Resources</td>\n",
       "      <td>093-62100-00</td>\n",
       "      <td>B - Protective Measures</td>\n",
       "      <td>Small</td>\n",
       "      <td>Meade</td>\n",
       "      <td>93</td>\n",
       "      <td>...</td>\n",
       "      <td>46</td>\n",
       "      <td>20739.92</td>\n",
       "      <td>16506.90</td>\n",
       "      <td>16506.90</td>\n",
       "      <td>2024-03-22</td>\n",
       "      <td>B</td>\n",
       "      <td>Protective Measures</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>39a1559d2dd1a04afafdcde601838082c65b4729</td>\n",
       "      <td>39552c91-2dc1-4519-87fb-b6beb815a0ad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4226</td>\n",
       "      <td>2015-06-26</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>62</td>\n",
       "      <td>SCT002C - Roads and Culverts</td>\n",
       "      <td>127-99127-00</td>\n",
       "      <td>C - Roads and Bridges</td>\n",
       "      <td>Large</td>\n",
       "      <td>Scott</td>\n",
       "      <td>127</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>205754.79</td>\n",
       "      <td>154316.09</td>\n",
       "      <td>154316.09</td>\n",
       "      <td>2015-12-08</td>\n",
       "      <td>C</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>aef455b4030e92d3c7219138d3456fcebdd789bf</td>\n",
       "      <td>d02b7bb6-fb6c-4fb4-a029-58fdfc9848f9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4314</td>\n",
       "      <td>2017-05-22</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>14</td>\n",
       "      <td>DUARW01 - PAAP Debris Removal 1-30 Days</td>\n",
       "      <td>051-20500-00</td>\n",
       "      <td>A - Debris Removal</td>\n",
       "      <td>Large</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>51</td>\n",
       "      <td>...</td>\n",
       "      <td>28</td>\n",
       "      <td>2115399.61</td>\n",
       "      <td>1687334.81</td>\n",
       "      <td>1687334.81</td>\n",
       "      <td>2017-07-21</td>\n",
       "      <td>A</td>\n",
       "      <td>Debris Removal</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>727e5a2e3628d483b2ac6407f48663a3a69a144e</td>\n",
       "      <td>044e88f1-b38e-483c-b579-80a60c6a10fa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4337</td>\n",
       "      <td>2017-09-10</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>6679</td>\n",
       "      <td>Zoo Exhibit and Trail Repairs</td>\n",
       "      <td>099-UXTQE-00</td>\n",
       "      <td>G - Parks, Recreational Facilities, and Other ...</td>\n",
       "      <td>Large</td>\n",
       "      <td>Palm Beach County</td>\n",
       "      <td>99</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>137858.93</td>\n",
       "      <td>124077.54</td>\n",
       "      <td>124077.54</td>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>G</td>\n",
       "      <td>Parks, Recreational Facilities, and Other Items</td>\n",
       "      <td>2024-09-06 15:04:53.183</td>\n",
       "      <td>ab453e975b05cc11633455ccde7607fdd24ff1ca</td>\n",
       "      <td>98731455-3b88-4fc0-af7f-dae089378537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   disasterNumber declarationDate  incidentType  pwNumber  \\\n",
       "0            4023      2011-09-02     Hurricane       247   \n",
       "1            4155      2013-11-08  Severe Storm       173   \n",
       "2            4226      2015-06-26  Severe Storm        62   \n",
       "3            4314      2017-05-22  Severe Storm        14   \n",
       "4            4337      2017-09-10     Hurricane      6679   \n",
       "\n",
       "                          applicationTitle   applicantId  \\\n",
       "0           MCMDG00 Trumbull Ave Revetment  009-47500-00   \n",
       "1               SG107  - Donated Resources  093-62100-00   \n",
       "2             SCT002C - Roads and Culverts  127-99127-00   \n",
       "3  DUARW01 - PAAP Debris Removal 1-30 Days  051-20500-00   \n",
       "4            Zoo Exhibit and Trail Repairs  099-UXTQE-00   \n",
       "\n",
       "                                  damageCategoryCode projectSize  \\\n",
       "0                          G - Recreational or Other       Large   \n",
       "1                            B - Protective Measures       Small   \n",
       "2                              C - Roads and Bridges       Large   \n",
       "3                                 A - Debris Removal       Large   \n",
       "4  G - Parks, Recreational Facilities, and Other ...       Large   \n",
       "\n",
       "              county  countyCode  ... stateNumberCode projectAmount  \\\n",
       "0          New Haven           9  ...               9     614291.00   \n",
       "1              Meade          93  ...              46      20739.92   \n",
       "2              Scott         127  ...               5     205754.79   \n",
       "3             Holmes          51  ...              28    2115399.61   \n",
       "4  Palm Beach County          99  ...              12     137858.93   \n",
       "\n",
       "   federalShareObligated  totalObligated  obligatedDate  dcc  \\\n",
       "0              614291.00       614291.00     2012-04-05    G   \n",
       "1               16506.90        16506.90     2024-03-22    B   \n",
       "2              154316.09       154316.09     2015-12-08    C   \n",
       "3             1687334.81      1687334.81     2017-07-21    A   \n",
       "4              124077.54       124077.54     2020-01-24    G   \n",
       "\n",
       "                                    damageCategory             lastRefresh  \\\n",
       "0                            Recreational or Other 2024-03-23 17:02:09.387   \n",
       "1                              Protective Measures 2024-03-23 17:02:09.387   \n",
       "2                                Roads and Bridges 2024-03-23 17:02:09.387   \n",
       "3                                   Debris Removal 2024-03-23 17:02:09.387   \n",
       "4  Parks, Recreational Facilities, and Other Items 2024-09-06 15:04:53.183   \n",
       "\n",
       "                                       hash  \\\n",
       "0  5ff26ea21f9af4de5dce40c1f4ab2324b11b5fcb   \n",
       "1  39a1559d2dd1a04afafdcde601838082c65b4729   \n",
       "2  aef455b4030e92d3c7219138d3456fcebdd789bf   \n",
       "3  727e5a2e3628d483b2ac6407f48663a3a69a144e   \n",
       "4  ab453e975b05cc11633455ccde7607fdd24ff1ca   \n",
       "\n",
       "                                     id  \n",
       "0  d247f735-5e5d-4d47-9b4b-58a2fa6396b4  \n",
       "1  39552c91-2dc1-4519-87fb-b6beb815a0ad  \n",
       "2  d02b7bb6-fb6c-4fb4-a029-58fdfc9848f9  \n",
       "3  044e88f1-b38e-483c-b579-80a60c6a10fa  \n",
       "4  98731455-3b88-4fc0-af7f-dae089378537  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pandas dataframe view\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "19f2e5ea",
   "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>database</th>\n",
       "      <th>schema</th>\n",
       "      <th>name</th>\n",
       "      <th>column_names</th>\n",
       "      <th>column_types</th>\n",
       "      <th>temporary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>memory</td>\n",
       "      <td>main</td>\n",
       "      <td>public_assistance</td>\n",
       "      <td>[disasterNumber, declarationDate, incidentType...</td>\n",
       "      <td>[BIGINT, TIMESTAMP, VARCHAR, BIGINT, VARCHAR, ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  database schema               name  \\\n",
       "0   memory   main  public_assistance   \n",
       "\n",
       "                                        column_names  \\\n",
       "0  [disasterNumber, declarationDate, incidentType...   \n",
       "\n",
       "                                        column_types  temporary  \n",
       "0  [BIGINT, TIMESTAMP, VARCHAR, BIGINT, VARCHAR, ...      False  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# list tables and schemas\n",
    "con.sql(\"SHOW ALL TABLES\").df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a85e1b7e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────┐\n",
       "│ count_rows │\n",
       "│   int64    │\n",
       "├────────────┤\n",
       "│     800128 │\n",
       "└────────────┘"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count of rows\n",
    "con.sql(\"\"\"\n",
    "    SELECT\n",
    "        COUNT(*) AS count_rows\n",
    "    FROM\n",
    "        public_assistance\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "72bfd79e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────┐\n",
       "│ count_columns │\n",
       "│     int64     │\n",
       "├───────────────┤\n",
       "│            22 │\n",
       "└───────────────┘"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count of columns\n",
    "con.sql(\"\"\"\n",
    "    SELECT\n",
    "        COUNT(column_name) AS count_columns\n",
    "    FROM\n",
    "        (DESCRIBE FROM public_assistance)\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75c9dab3",
   "metadata": {},
   "source": [
    "# Examine Dataset\n",
    "## Column Info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b129a251",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌───────────────────────┬─────────────┐\n",
      "│      column_name      │ column_type │\n",
      "│        varchar        │   varchar   │\n",
      "├───────────────────────┼─────────────┤\n",
      "│ disasterNumber        │ BIGINT      │\n",
      "│ declarationDate       │ TIMESTAMP   │\n",
      "│ incidentType          │ VARCHAR     │\n",
      "│ pwNumber              │ BIGINT      │\n",
      "│ applicationTitle      │ VARCHAR     │\n",
      "│ applicantId           │ VARCHAR     │\n",
      "│ damageCategoryCode    │ VARCHAR     │\n",
      "│ projectSize           │ VARCHAR     │\n",
      "│ county                │ VARCHAR     │\n",
      "│ countyCode            │ BIGINT      │\n",
      "│ state                 │ VARCHAR     │\n",
      "│ stateCode             │ VARCHAR     │\n",
      "│ stateNumberCode       │ BIGINT      │\n",
      "│ projectAmount         │ DOUBLE      │\n",
      "│ federalShareObligated │ DOUBLE      │\n",
      "│ totalObligated        │ DOUBLE      │\n",
      "│ obligatedDate         │ TIMESTAMP   │\n",
      "│ dcc                   │ VARCHAR     │\n",
      "│ damageCategory        │ VARCHAR     │\n",
      "│ lastRefresh           │ TIMESTAMP   │\n",
      "│ hash                  │ VARCHAR     │\n",
      "│ id                    │ VARCHAR     │\n",
      "├───────────────────────┴─────────────┤\n",
      "│ 22 rows                   2 columns │\n",
      "└─────────────────────────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# examine column datatypes\n",
    "con.sql(\"\"\"\n",
    "    SELECT\n",
    "        column_name,\n",
    "        column_type\n",
    "    FROM\n",
    "        (DESCRIBE public_assistance)\n",
    "\"\"\").show(max_rows=30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5a39da54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌──────────────────┬─────────────────┐\n",
      "│   column_name    │ null_percentage │\n",
      "│     varchar      │  decimal(9,2)   │\n",
      "├──────────────────┼─────────────────┤\n",
      "│ county           │            1.69 │\n",
      "│ projectSize      │            1.63 │\n",
      "│ countyCode       │            1.63 │\n",
      "│ applicationTitle │            0.78 │\n",
      "└──────────────────┴─────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# examine column null percentage\n",
    "con.sql(\"\"\"\n",
    "    SELECT\n",
    "        column_name,\n",
    "        null_percentage\n",
    "    FROM\n",
    "        (SUMMARIZE FROM public_assistance)\n",
    "    WHERE\n",
    "        null_percentage > 0\n",
    "    ORDER BY\n",
    "        null_percentage DESC\n",
    "\"\"\").show(max_rows=80)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c033733",
   "metadata": {},
   "source": [
    "## Preview Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b76733e7",
   "metadata": {
    "scrolled": false
   },
   "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>disasterNumber</th>\n",
       "      <th>declarationDate</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>pwNumber</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>applicantId</th>\n",
       "      <th>damageCategoryCode</th>\n",
       "      <th>projectSize</th>\n",
       "      <th>county</th>\n",
       "      <th>countyCode</th>\n",
       "      <th>state</th>\n",
       "      <th>stateCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4023</td>\n",
       "      <td>2011-09-02</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>247</td>\n",
       "      <td>MCMDG00 Trumbull Ave Revetment</td>\n",
       "      <td>009-47500-00</td>\n",
       "      <td>G - Recreational or Other</td>\n",
       "      <td>Large</td>\n",
       "      <td>New Haven</td>\n",
       "      <td>9</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>CT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4155</td>\n",
       "      <td>2013-11-08</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>173</td>\n",
       "      <td>SG107  - Donated Resources</td>\n",
       "      <td>093-62100-00</td>\n",
       "      <td>B - Protective Measures</td>\n",
       "      <td>Small</td>\n",
       "      <td>Meade</td>\n",
       "      <td>93</td>\n",
       "      <td>South Dakota</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4226</td>\n",
       "      <td>2015-06-26</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>62</td>\n",
       "      <td>SCT002C - Roads and Culverts</td>\n",
       "      <td>127-99127-00</td>\n",
       "      <td>C - Roads and Bridges</td>\n",
       "      <td>Large</td>\n",
       "      <td>Scott</td>\n",
       "      <td>127</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4314</td>\n",
       "      <td>2017-05-22</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>14</td>\n",
       "      <td>DUARW01 - PAAP Debris Removal 1-30 Days</td>\n",
       "      <td>051-20500-00</td>\n",
       "      <td>A - Debris Removal</td>\n",
       "      <td>Large</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>51</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>MS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4337</td>\n",
       "      <td>2017-09-10</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>6679</td>\n",
       "      <td>Zoo Exhibit and Trail Repairs</td>\n",
       "      <td>099-UXTQE-00</td>\n",
       "      <td>G - Parks, Recreational Facilities, and Other ...</td>\n",
       "      <td>Large</td>\n",
       "      <td>Palm Beach County</td>\n",
       "      <td>99</td>\n",
       "      <td>Florida</td>\n",
       "      <td>FL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   disasterNumber declarationDate  incidentType  pwNumber  \\\n",
       "0            4023      2011-09-02     Hurricane       247   \n",
       "1            4155      2013-11-08  Severe Storm       173   \n",
       "2            4226      2015-06-26  Severe Storm        62   \n",
       "3            4314      2017-05-22  Severe Storm        14   \n",
       "4            4337      2017-09-10     Hurricane      6679   \n",
       "\n",
       "                          applicationTitle   applicantId  \\\n",
       "0           MCMDG00 Trumbull Ave Revetment  009-47500-00   \n",
       "1               SG107  - Donated Resources  093-62100-00   \n",
       "2             SCT002C - Roads and Culverts  127-99127-00   \n",
       "3  DUARW01 - PAAP Debris Removal 1-30 Days  051-20500-00   \n",
       "4            Zoo Exhibit and Trail Repairs  099-UXTQE-00   \n",
       "\n",
       "                                  damageCategoryCode projectSize  \\\n",
       "0                          G - Recreational or Other       Large   \n",
       "1                            B - Protective Measures       Small   \n",
       "2                              C - Roads and Bridges       Large   \n",
       "3                                 A - Debris Removal       Large   \n",
       "4  G - Parks, Recreational Facilities, and Other ...       Large   \n",
       "\n",
       "              county  countyCode         state stateCode  \n",
       "0          New Haven           9   Connecticut        CT  \n",
       "1              Meade          93  South Dakota        SD  \n",
       "2              Scott         127      Arkansas        AR  \n",
       "3             Holmes          51   Mississippi        MS  \n",
       "4  Palm Beach County          99       Florida        FL  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview data\n",
    "sql = \"\"\"\n",
    "    SELECT *\n",
    "    FROM public_assistance\n",
    "    LIMIT 5   \n",
    "\"\"\"\n",
    "\n",
    "# preview first 12 columns\n",
    "con.sql(sql).df().iloc[:, :12]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cff61655",
   "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>stateNumberCode</th>\n",
       "      <th>projectAmount</th>\n",
       "      <th>federalShareObligated</th>\n",
       "      <th>totalObligated</th>\n",
       "      <th>obligatedDate</th>\n",
       "      <th>dcc</th>\n",
       "      <th>damageCategory</th>\n",
       "      <th>lastRefresh</th>\n",
       "      <th>hash</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>2012-04-05</td>\n",
       "      <td>G</td>\n",
       "      <td>Recreational or Other</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>5ff26ea21f9af4de5dce40c1f4ab2324b11b5fcb</td>\n",
       "      <td>d247f735-5e5d-4d47-9b4b-58a2fa6396b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46</td>\n",
       "      <td>20739.92</td>\n",
       "      <td>16506.90</td>\n",
       "      <td>16506.90</td>\n",
       "      <td>2024-03-22</td>\n",
       "      <td>B</td>\n",
       "      <td>Protective Measures</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>39a1559d2dd1a04afafdcde601838082c65b4729</td>\n",
       "      <td>39552c91-2dc1-4519-87fb-b6beb815a0ad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>205754.79</td>\n",
       "      <td>154316.09</td>\n",
       "      <td>154316.09</td>\n",
       "      <td>2015-12-08</td>\n",
       "      <td>C</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>aef455b4030e92d3c7219138d3456fcebdd789bf</td>\n",
       "      <td>d02b7bb6-fb6c-4fb4-a029-58fdfc9848f9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28</td>\n",
       "      <td>2115399.61</td>\n",
       "      <td>1687334.81</td>\n",
       "      <td>1687334.81</td>\n",
       "      <td>2017-07-21</td>\n",
       "      <td>A</td>\n",
       "      <td>Debris Removal</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>727e5a2e3628d483b2ac6407f48663a3a69a144e</td>\n",
       "      <td>044e88f1-b38e-483c-b579-80a60c6a10fa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>137858.93</td>\n",
       "      <td>124077.54</td>\n",
       "      <td>124077.54</td>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>G</td>\n",
       "      <td>Parks, Recreational Facilities, and Other Items</td>\n",
       "      <td>2024-09-06 15:04:53.183</td>\n",
       "      <td>ab453e975b05cc11633455ccde7607fdd24ff1ca</td>\n",
       "      <td>98731455-3b88-4fc0-af7f-dae089378537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   stateNumberCode  projectAmount  federalShareObligated  totalObligated  \\\n",
       "0                9      614291.00              614291.00       614291.00   \n",
       "1               46       20739.92               16506.90        16506.90   \n",
       "2                5      205754.79              154316.09       154316.09   \n",
       "3               28     2115399.61             1687334.81      1687334.81   \n",
       "4               12      137858.93              124077.54       124077.54   \n",
       "\n",
       "  obligatedDate dcc                                   damageCategory  \\\n",
       "0    2012-04-05   G                            Recreational or Other   \n",
       "1    2024-03-22   B                              Protective Measures   \n",
       "2    2015-12-08   C                                Roads and Bridges   \n",
       "3    2017-07-21   A                                   Debris Removal   \n",
       "4    2020-01-24   G  Parks, Recreational Facilities, and Other Items   \n",
       "\n",
       "              lastRefresh                                      hash  \\\n",
       "0 2024-03-23 17:02:09.387  5ff26ea21f9af4de5dce40c1f4ab2324b11b5fcb   \n",
       "1 2024-03-23 17:02:09.387  39a1559d2dd1a04afafdcde601838082c65b4729   \n",
       "2 2024-03-23 17:02:09.387  aef455b4030e92d3c7219138d3456fcebdd789bf   \n",
       "3 2024-03-23 17:02:09.387  727e5a2e3628d483b2ac6407f48663a3a69a144e   \n",
       "4 2024-09-06 15:04:53.183  ab453e975b05cc11633455ccde7607fdd24ff1ca   \n",
       "\n",
       "                                     id  \n",
       "0  d247f735-5e5d-4d47-9b4b-58a2fa6396b4  \n",
       "1  39552c91-2dc1-4519-87fb-b6beb815a0ad  \n",
       "2  d02b7bb6-fb6c-4fb4-a029-58fdfc9848f9  \n",
       "3  044e88f1-b38e-483c-b579-80a60c6a10fa  \n",
       "4  98731455-3b88-4fc0-af7f-dae089378537  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview last 12 columns\n",
    "con.sql(sql).df().iloc[:, 12:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e5df914e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬───────┐\n",
       "│   id    │ count │\n",
       "│ varchar │ int64 │\n",
       "├─────────┴───────┤\n",
       "│     0 rows      │\n",
       "└─────────────────┘"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count duplicate IDs\n",
    "con.sql(\"\"\"\n",
    "    SELECT\n",
    "        id,\n",
    "        COUNT(id) AS count\n",
    "    FROM\n",
    "        public_assistance\n",
    "    GROUP BY\n",
    "        id\n",
    "    HAVING\n",
    "        count > 1\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ce899af1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record last updated at: 2024-10-20T15:05:23.895000000\n"
     ]
    }
   ],
   "source": [
    "# latest record refreshed date\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        lastRefresh\n",
    "    FROM\n",
    "        public_assistance\n",
    "    ORDER BY\n",
    "        lastRefresh DESC\n",
    "    LIMIT 1\n",
    "\"\"\"\n",
    "\n",
    "record_last_updated = (\n",
    "    con\n",
    "    .sql(sql)\n",
    "    .df()\n",
    "    .loc[:, ['lastRefresh']]\n",
    "    .values[0][0]\n",
    ")\n",
    "\n",
    "print(f\"Record last updated at: {record_last_updated}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "77537389",
   "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>lastRefresh</th>\n",
       "      <th>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>year</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-03-22 11:47:01.426</td>\n",
       "      <td>1751</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>DONATED RESOURCES</td>\n",
       "      <td>2008</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>1590.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-03-22 11:47:01.426</td>\n",
       "      <td>1751</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>DONATED RESOURCES</td>\n",
       "      <td>2008</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>2820.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-03-22 11:47:01.426</td>\n",
       "      <td>1751</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>ROADS AND BRIDGES</td>\n",
       "      <td>2008</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>5869.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-03-22 11:47:01.426</td>\n",
       "      <td>1751</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>DONATED RESOURCES</td>\n",
       "      <td>2008</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>11506.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-03-22 11:47:01.426</td>\n",
       "      <td>1751</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>ROADS AND BRIDGES</td>\n",
       "      <td>2008</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>27406.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              lastRefresh  disasterNumber     state   applicationTitle  year  \\\n",
       "0 2024-03-22 11:47:01.426            1751  Arkansas  DONATED RESOURCES  2008   \n",
       "1 2024-03-22 11:47:01.426            1751  Arkansas  DONATED RESOURCES  2008   \n",
       "2 2024-03-22 11:47:01.426            1751  Arkansas  ROADS AND BRIDGES  2008   \n",
       "3 2024-03-22 11:47:01.426            1751  Arkansas  DONATED RESOURCES  2008   \n",
       "4 2024-03-22 11:47:01.426            1751  Arkansas  ROADS AND BRIDGES  2008   \n",
       "\n",
       "   incidentType  totalObligated  \n",
       "0  Severe Storm         1590.00  \n",
       "1  Severe Storm         2820.00  \n",
       "2  Severe Storm         5869.13  \n",
       "3  Severe Storm        11506.96  \n",
       "4  Severe Storm        27406.33  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 5 earliest last refreshed\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        lastRefresh,\n",
    "        disasterNumber,\n",
    "        state,\n",
    "        applicationTitle,\n",
    "        substring(declarationDate::VARCHAR, 1, 4) as year,\n",
    "        incidentType,\n",
    "        totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    ORDER BY\n",
    "        lastRefresh ASC\n",
    "    LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9d80baf7",
   "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>lastRefresh</th>\n",
       "      <th>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>year</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-10-20 15:05:23.895</td>\n",
       "      <td>4562</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>181792 - OSP &amp; FM_CAT B_EPM_HOLIDAY FIRE_CONFL...</td>\n",
       "      <td>2020</td>\n",
       "      <td>Fire</td>\n",
       "      <td>782688.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-10-20 15:05:23.895</td>\n",
       "      <td>4285</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>4-C0297 Shawcroft Road Permanent Repair</td>\n",
       "      <td>2016</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>716223.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-10-20 15:05:23.895</td>\n",
       "      <td>4339</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>CLOSEOUT MCHS003 - Building Damages and Equipm...</td>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>20333.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-10-20 15:05:23.895</td>\n",
       "      <td>4339</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>CLOSEOUT MMID002 Asociacion Comunidad Monteria...</td>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>44288.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-10-20 15:05:23.895</td>\n",
       "      <td>4339</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>CLOSEOUT Equipment Damages with Content</td>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>10481.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              lastRefresh  disasterNumber           state  \\\n",
       "0 2024-10-20 15:05:23.895            4562          Oregon   \n",
       "1 2024-10-20 15:05:23.895            4285  North Carolina   \n",
       "2 2024-10-20 15:05:23.895            4339     Puerto Rico   \n",
       "3 2024-10-20 15:05:23.895            4339     Puerto Rico   \n",
       "4 2024-10-20 15:05:23.895            4339     Puerto Rico   \n",
       "\n",
       "                                    applicationTitle  year incidentType  \\\n",
       "0  181792 - OSP & FM_CAT B_EPM_HOLIDAY FIRE_CONFL...  2020         Fire   \n",
       "1           4-C0297 Shawcroft Road Permanent Repair   2016    Hurricane   \n",
       "2  CLOSEOUT MCHS003 - Building Damages and Equipm...  2017    Hurricane   \n",
       "3  CLOSEOUT MMID002 Asociacion Comunidad Monteria...  2017    Hurricane   \n",
       "4            CLOSEOUT Equipment Damages with Content  2017    Hurricane   \n",
       "\n",
       "   totalObligated  \n",
       "0       782688.66  \n",
       "1       716223.09  \n",
       "2        20333.75  \n",
       "3        44288.42  \n",
       "4        10481.75  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 5 latest last refreshed\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        lastRefresh,\n",
    "        disasterNumber,\n",
    "        state,\n",
    "        applicationTitle,\n",
    "        substring(declarationDate::VARCHAR, 1, 4) as year,\n",
    "        incidentType,\n",
    "        totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    ORDER BY\n",
    "        lastRefresh DESC\n",
    "    LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e696ef4f",
   "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>declarationDate</th>\n",
       "      <th>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>year</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1998-08-26</td>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>IMMEDIATE NEEDS FUNDING</td>\n",
       "      <td>1998</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>80340.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1998-08-26</td>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>(L)</td>\n",
       "      <td>1998</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>333171.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1998-08-26</td>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>L</td>\n",
       "      <td>1998</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>53975.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1998-08-26</td>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>1998</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>17745.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1998-08-26</td>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>1998</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>15461.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  declarationDate  disasterNumber  state         applicationTitle  year  \\\n",
       "0      1998-08-26            1239  Texas  IMMEDIATE NEEDS FUNDING  1998   \n",
       "1      1998-08-26            1239  Texas                      (L)  1998   \n",
       "2      1998-08-26            1239  Texas                        L  1998   \n",
       "3      1998-08-26            1239  Texas             Not Provided  1998   \n",
       "4      1998-08-26            1239  Texas             Not Provided  1998   \n",
       "\n",
       "   incidentType  totalObligated  \n",
       "0  Severe Storm        80340.00  \n",
       "1  Severe Storm       333171.72  \n",
       "2  Severe Storm        53975.01  \n",
       "3  Severe Storm        17745.86  \n",
       "4  Severe Storm        15461.00  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 5 earliest declared disaster event\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        declarationDate,\n",
    "        disasterNumber,\n",
    "        state,\n",
    "        applicationTitle,\n",
    "        substring(declarationDate::VARCHAR, 1, 4) as year,\n",
    "        incidentType,\n",
    "        totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    ORDER BY\n",
    "        declarationDate ASC\n",
    "    LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e9f546f5",
   "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>declarationDate</th>\n",
       "      <th>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>year</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-10-11</td>\n",
       "      <td>4834</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Expedited Contractual Services Project</td>\n",
       "      <td>2024</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>4.407218e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-10-11</td>\n",
       "      <td>4834</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Expedited Mutual Aid</td>\n",
       "      <td>2024</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>1.635179e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-10-11</td>\n",
       "      <td>4834</td>\n",
       "      <td>Florida</td>\n",
       "      <td>EXP - Plant City - CAT B Emergency Protective ...</td>\n",
       "      <td>2024</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>6.101983e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-10-11</td>\n",
       "      <td>4834</td>\n",
       "      <td>Florida</td>\n",
       "      <td>EXP - Plant City - CAT A Debris Removal - POP 1</td>\n",
       "      <td>2024</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>1.885000e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-09-28</td>\n",
       "      <td>4828</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Expedited Contractual Services Project</td>\n",
       "      <td>2024</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>3.041577e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  declarationDate  disasterNumber    state  \\\n",
       "0      2024-10-11            4834  Florida   \n",
       "1      2024-10-11            4834  Florida   \n",
       "2      2024-10-11            4834  Florida   \n",
       "3      2024-10-11            4834  Florida   \n",
       "4      2024-09-28            4828  Florida   \n",
       "\n",
       "                                    applicationTitle  year incidentType  \\\n",
       "0             Expedited Contractual Services Project  2024    Hurricane   \n",
       "1                               Expedited Mutual Aid  2024    Hurricane   \n",
       "2  EXP - Plant City - CAT B Emergency Protective ...  2024    Hurricane   \n",
       "3    EXP - Plant City - CAT A Debris Removal - POP 1  2024    Hurricane   \n",
       "4             Expedited Contractual Services Project  2024    Hurricane   \n",
       "\n",
       "   totalObligated  \n",
       "0    4.407218e+08  \n",
       "1    1.635179e+08  \n",
       "2    6.101983e+05  \n",
       "3    1.885000e+06  \n",
       "4    3.041577e+08  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 5 latest disaster declared event\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        declarationDate,\n",
    "        disasterNumber,\n",
    "        state,\n",
    "        applicationTitle,\n",
    "        substring(declarationDate::VARCHAR, 1, 4) as year,\n",
    "        incidentType,\n",
    "        totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    ORDER BY\n",
    "        declarationDate DESC\n",
    "    LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e528e810",
   "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>disasterNumber</th>\n",
       "      <th>declarationDate</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>pwNumber</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>applicantId</th>\n",
       "      <th>damageCategoryCode</th>\n",
       "      <th>projectSize</th>\n",
       "      <th>county</th>\n",
       "      <th>countyCode</th>\n",
       "      <th>state</th>\n",
       "      <th>stateCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4485</td>\n",
       "      <td>2020-03-25</td>\n",
       "      <td>Biological</td>\n",
       "      <td>35</td>\n",
       "      <td>COVID-19 Medical Surge Staffing</td>\n",
       "      <td>000-U0109-00</td>\n",
       "      <td>B - Emergency Protective Measures</td>\n",
       "      <td>Large</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>453</td>\n",
       "      <td>Texas</td>\n",
       "      <td>TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4339</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>6099</td>\n",
       "      <td>MEPA078 Puerto Rico Electrical Power Authority...</td>\n",
       "      <td>000-UA2QU-00</td>\n",
       "      <td>F - Utilities</td>\n",
       "      <td>Large</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>127</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4339</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>7382</td>\n",
       "      <td>MAAA200 Puerto Rico Aqueduct and Sewer Authori...</td>\n",
       "      <td>000-UK5O0-00</td>\n",
       "      <td>F - Utilities</td>\n",
       "      <td>Large</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>127</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4340</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>1425</td>\n",
       "      <td>XMUET95 - GM708286 -St. Croix Island-Wide Unif...</td>\n",
       "      <td>000-UET63-01</td>\n",
       "      <td>C - Roads and Bridges</td>\n",
       "      <td>Large</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>10</td>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>VI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4480</td>\n",
       "      <td>2020-03-20</td>\n",
       "      <td>Biological</td>\n",
       "      <td>150</td>\n",
       "      <td>Vaccination Center(s)</td>\n",
       "      <td>061-51000-41</td>\n",
       "      <td>B - Emergency Protective Measures</td>\n",
       "      <td>Large</td>\n",
       "      <td>New York County</td>\n",
       "      <td>61</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   disasterNumber declarationDate incidentType  pwNumber  \\\n",
       "0            4485      2020-03-25   Biological        35   \n",
       "1            4339      2017-09-20    Hurricane      6099   \n",
       "2            4339      2017-09-20    Hurricane      7382   \n",
       "3            4340      2017-09-20    Hurricane      1425   \n",
       "4            4480      2020-03-20   Biological       150   \n",
       "\n",
       "                                    applicationTitle   applicantId  \\\n",
       "0                    COVID-19 Medical Surge Staffing  000-U0109-00   \n",
       "1  MEPA078 Puerto Rico Electrical Power Authority...  000-UA2QU-00   \n",
       "2  MAAA200 Puerto Rico Aqueduct and Sewer Authori...  000-UK5O0-00   \n",
       "3  XMUET95 - GM708286 -St. Croix Island-Wide Unif...  000-UET63-01   \n",
       "4                              Vaccination Center(s)  061-51000-41   \n",
       "\n",
       "                  damageCategoryCode projectSize           county  countyCode  \\\n",
       "0  B - Emergency Protective Measures       Large        Statewide         453   \n",
       "1                      F - Utilities       Large        Statewide         127   \n",
       "2                      F - Utilities       Large        Statewide         127   \n",
       "3              C - Roads and Bridges       Large        Statewide          10   \n",
       "4  B - Emergency Protective Measures       Large  New York County          61   \n",
       "\n",
       "            state stateCode  \n",
       "0           Texas        TX  \n",
       "1     Puerto Rico        PR  \n",
       "2     Puerto Rico        PR  \n",
       "3  Virgin Islands        VI  \n",
       "4        New York        NY  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine top 5 largest totalObligated record\n",
    "sql = \"\"\"\n",
    "    SELECT *\n",
    "    FROM\n",
    "        public_assistance\n",
    "    ORDER BY\n",
    "        totalObligated DESC\n",
    "    LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "# slice through columns\n",
    "con.sql(sql).df().iloc[:, :12]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5940ea7d",
   "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>stateNumberCode</th>\n",
       "      <th>projectAmount</th>\n",
       "      <th>federalShareObligated</th>\n",
       "      <th>totalObligated</th>\n",
       "      <th>obligatedDate</th>\n",
       "      <th>dcc</th>\n",
       "      <th>damageCategory</th>\n",
       "      <th>lastRefresh</th>\n",
       "      <th>hash</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48</td>\n",
       "      <td>9.053782e+09</td>\n",
       "      <td>9.053782e+09</td>\n",
       "      <td>9.053782e+09</td>\n",
       "      <td>2020-09-29</td>\n",
       "      <td>B</td>\n",
       "      <td>Emergency Protective Measures</td>\n",
       "      <td>2024-07-31 19:58:22.029</td>\n",
       "      <td>03e62eb8dc779c1224ee63ce9e1386dda1bea50e</td>\n",
       "      <td>617d34c9-e66a-4ca1-bf0f-557a752693e8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>72</td>\n",
       "      <td>4.132895e+09</td>\n",
       "      <td>5.119352e+09</td>\n",
       "      <td>5.119352e+09</td>\n",
       "      <td>2020-09-23</td>\n",
       "      <td>F</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>2024-10-09 15:06:10.686</td>\n",
       "      <td>673cc2dc6e10c39c5517042512473a2cda2c8c3c</td>\n",
       "      <td>26482a89-6541-41ec-b124-e37c642b2abc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>72</td>\n",
       "      <td>2.304165e+09</td>\n",
       "      <td>2.114759e+09</td>\n",
       "      <td>2.114759e+09</td>\n",
       "      <td>2021-01-08</td>\n",
       "      <td>F</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>2024-10-03 15:20:18.531</td>\n",
       "      <td>f4266f1e75b97a076d5107c04bba634b583cd1bc</td>\n",
       "      <td>8f59d572-3459-418a-8f6f-eefa0a2e019f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>78</td>\n",
       "      <td>1.791529e+09</td>\n",
       "      <td>1.755698e+09</td>\n",
       "      <td>1.755698e+09</td>\n",
       "      <td>2024-10-03</td>\n",
       "      <td>C</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>2024-10-04 15:34:11.733</td>\n",
       "      <td>7a46c3c4e44dab5b1039b4fe2b1572e8b0d6f98d</td>\n",
       "      <td>46a06619-08d3-45b3-843e-326f6f4b4680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>1.691034e+09</td>\n",
       "      <td>1.691034e+09</td>\n",
       "      <td>1.691034e+09</td>\n",
       "      <td>2021-01-24</td>\n",
       "      <td>B</td>\n",
       "      <td>Emergency Protective Measures</td>\n",
       "      <td>2024-07-31 19:58:22.029</td>\n",
       "      <td>5640a648f122b43717a70f7a528a951617a283dc</td>\n",
       "      <td>6ddd40d7-54e2-4d12-a236-b3697b14290d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   stateNumberCode  projectAmount  federalShareObligated  totalObligated  \\\n",
       "0               48   9.053782e+09           9.053782e+09    9.053782e+09   \n",
       "1               72   4.132895e+09           5.119352e+09    5.119352e+09   \n",
       "2               72   2.304165e+09           2.114759e+09    2.114759e+09   \n",
       "3               78   1.791529e+09           1.755698e+09    1.755698e+09   \n",
       "4               36   1.691034e+09           1.691034e+09    1.691034e+09   \n",
       "\n",
       "  obligatedDate dcc                 damageCategory             lastRefresh  \\\n",
       "0    2020-09-29   B  Emergency Protective Measures 2024-07-31 19:58:22.029   \n",
       "1    2020-09-23   F                      Utilities 2024-10-09 15:06:10.686   \n",
       "2    2021-01-08   F                      Utilities 2024-10-03 15:20:18.531   \n",
       "3    2024-10-03   C              Roads and Bridges 2024-10-04 15:34:11.733   \n",
       "4    2021-01-24   B  Emergency Protective Measures 2024-07-31 19:58:22.029   \n",
       "\n",
       "                                       hash  \\\n",
       "0  03e62eb8dc779c1224ee63ce9e1386dda1bea50e   \n",
       "1  673cc2dc6e10c39c5517042512473a2cda2c8c3c   \n",
       "2  f4266f1e75b97a076d5107c04bba634b583cd1bc   \n",
       "3  7a46c3c4e44dab5b1039b4fe2b1572e8b0d6f98d   \n",
       "4  5640a648f122b43717a70f7a528a951617a283dc   \n",
       "\n",
       "                                     id  \n",
       "0  617d34c9-e66a-4ca1-bf0f-557a752693e8  \n",
       "1  26482a89-6541-41ec-b124-e37c642b2abc  \n",
       "2  8f59d572-3459-418a-8f6f-eefa0a2e019f  \n",
       "3  46a06619-08d3-45b3-843e-326f6f4b4680  \n",
       "4  6ddd40d7-54e2-4d12-a236-b3697b14290d  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# slice through columns\n",
    "con.sql(sql).df().iloc[:, 12:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fbb97438",
   "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>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>applicantId</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>pwNumber</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1294</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>045-76792-00</td>\n",
       "      <td>PUMPING STATION</td>\n",
       "      <td>304</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1334</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>079-80020-00</td>\n",
       "      <td>EMERGENCY WORK FOR EAGLES VIEW &amp; TURTLEVILLE H...</td>\n",
       "      <td>1295</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1446</td>\n",
       "      <td>Guam</td>\n",
       "      <td>000-UU5GZ-00</td>\n",
       "      <td>EMERGENCY WORK</td>\n",
       "      <td>1220</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1539</td>\n",
       "      <td>Florida</td>\n",
       "      <td>015-0332A-00</td>\n",
       "      <td>PUBLIC BUILDINGS AND FACILITIES</td>\n",
       "      <td>4268</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1551</td>\n",
       "      <td>Florida</td>\n",
       "      <td>033-0517F-00</td>\n",
       "      <td>Net Small Project Cost Overrun (NSPO)</td>\n",
       "      <td>3855</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1571</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>050-78245-00</td>\n",
       "      <td>SHED REPAIR</td>\n",
       "      <td>99</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1577</td>\n",
       "      <td>California</td>\n",
       "      <td>000-U8RA6-00</td>\n",
       "      <td>PARK REPAIR- PT. MUGU STATE PARK</td>\n",
       "      <td>1069</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1577</td>\n",
       "      <td>California</td>\n",
       "      <td>037-30000-00</td>\n",
       "      <td>EMERGENCY PROTECTIVE MEASURES - CAMINO SAN RAF...</td>\n",
       "      <td>2859</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1577</td>\n",
       "      <td>California</td>\n",
       "      <td>059-1C6FF-00</td>\n",
       "      <td>RESERVOIR REPAIR- TALEGA RESERVOIR</td>\n",
       "      <td>1538</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1577</td>\n",
       "      <td>California</td>\n",
       "      <td>111-UL4GF-00</td>\n",
       "      <td>DAM REPAIR- ARUNDELL BARRANCA</td>\n",
       "      <td>1989</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   disasterNumber         state   applicantId  \\\n",
       "0            1294  Pennsylvania  045-76792-00   \n",
       "1            1334  North Dakota  079-80020-00   \n",
       "2            1446          Guam  000-UU5GZ-00   \n",
       "3            1539       Florida  015-0332A-00   \n",
       "4            1551       Florida  033-0517F-00   \n",
       "5            1571        Alaska  050-78245-00   \n",
       "6            1577    California  000-U8RA6-00   \n",
       "7            1577    California  037-30000-00   \n",
       "8            1577    California  059-1C6FF-00   \n",
       "9            1577    California  111-UL4GF-00   \n",
       "\n",
       "                                    applicationTitle  pwNumber  count  \n",
       "0                                    PUMPING STATION       304      2  \n",
       "1  EMERGENCY WORK FOR EAGLES VIEW & TURTLEVILLE H...      1295      2  \n",
       "2                                     EMERGENCY WORK      1220      2  \n",
       "3                    PUBLIC BUILDINGS AND FACILITIES      4268      2  \n",
       "4              Net Small Project Cost Overrun (NSPO)      3855      2  \n",
       "5                                        SHED REPAIR        99      2  \n",
       "6                   PARK REPAIR- PT. MUGU STATE PARK      1069      2  \n",
       "7  EMERGENCY PROTECTIVE MEASURES - CAMINO SAN RAF...      2859      2  \n",
       "8                 RESERVOIR REPAIR- TALEGA RESERVOIR      1538      2  \n",
       "9                      DAM REPAIR- ARUNDELL BARRANCA      1989      2  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# identify combination of unique records\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "       disasterNumber,\n",
    "       state,\n",
    "       applicantId,\n",
    "       applicationTitle,\n",
    "       pwNumber,\n",
    "       count(id) AS count\n",
    "    FROM\n",
    "        public_assistance\n",
    "    GROUP BY ALL\n",
    "    ORDER BY\n",
    "        count DESC, disasterNumber ASC  \n",
    "    LIMIT 10\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3039efe",
   "metadata": {},
   "source": [
    "The duplicates suggests that one disaster number, state, applicant, and project worksheet (PW) can have at least two records in this dataset. Let's examine further."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4c2db7ba",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "incidentType          1\n",
       "applicationTitle      1\n",
       "applicantId           1\n",
       "damageCategoryCode    2\n",
       "projectSize           1\n",
       "county                1\n",
       "state                 1\n",
       "stateCode             1\n",
       "dcc                   2\n",
       "damageCategory        2\n",
       "hash                  2\n",
       "id                    2\n",
       "Name: unique, dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine count unique for applicantTitle = 'PUMPING STATION' for disaster 1294\n",
    "(con\n",
    " .sql(\"\"\"\n",
    "    SELECT *\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        applicationTitle = 'PUMPING STATION'\n",
    "        AND disasterNumber = 1294\n",
    "\"\"\")\n",
    " .df()\n",
    " .describe(include='object')\n",
    " .loc['unique']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "e2ca4ac6",
   "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>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>applicantId</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>pwNumber</th>\n",
       "      <th>damageCategory</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1539</td>\n",
       "      <td>Florida</td>\n",
       "      <td>015-0332A-00</td>\n",
       "      <td>PUBLIC BUILDINGS AND FACILITIES</td>\n",
       "      <td>4268</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1551</td>\n",
       "      <td>Florida</td>\n",
       "      <td>033-0517F-00</td>\n",
       "      <td>Net Small Project Cost Overrun (NSPO)</td>\n",
       "      <td>3855</td>\n",
       "      <td>Recreational or Other</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>071-06A69-00</td>\n",
       "      <td>UT/ WATER TREATMENT PLANT (WEST BANK)</td>\n",
       "      <td>1936</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>071-55000-00</td>\n",
       "      <td>CARVER PENN PLAYGROUND</td>\n",
       "      <td>3301</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>051-UANIZ-00</td>\n",
       "      <td>EJE0058 REPLACEMENT OF SPECIAL FUNCTIONS &amp; STO...</td>\n",
       "      <td>4006</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>071-UHDXF-00</td>\n",
       "      <td>LIGHT CITY CHURCH AND CHRISTIAN ACADEMY</td>\n",
       "      <td>20109</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>071-UENQ9-00</td>\n",
       "      <td>DAC/COI COSTS</td>\n",
       "      <td>21021</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>117-04F21-00</td>\n",
       "      <td>EWA0023 BCS'SUPERIOR ELEMENTARY SCHOOL BLDG. A</td>\n",
       "      <td>2308</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>117-08150-00</td>\n",
       "      <td>CITY HALL BUILDING</td>\n",
       "      <td>4367</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>071-02E99-00</td>\n",
       "      <td>ST MARY'S ACADEMY  -  SWIMMING POOL</td>\n",
       "      <td>16239</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   disasterNumber      state   applicantId  \\\n",
       "0            1539    Florida  015-0332A-00   \n",
       "1            1551    Florida  033-0517F-00   \n",
       "2            1603  Louisiana  071-06A69-00   \n",
       "3            1603  Louisiana  071-55000-00   \n",
       "4            1603  Louisiana  051-UANIZ-00   \n",
       "5            1603  Louisiana  071-UHDXF-00   \n",
       "6            1603  Louisiana  071-UENQ9-00   \n",
       "7            1603  Louisiana  117-04F21-00   \n",
       "8            1603  Louisiana  117-08150-00   \n",
       "9            1603  Louisiana  071-02E99-00   \n",
       "\n",
       "                                    applicationTitle  pwNumber  \\\n",
       "0                    PUBLIC BUILDINGS AND FACILITIES      4268   \n",
       "1              Net Small Project Cost Overrun (NSPO)      3855   \n",
       "2              UT/ WATER TREATMENT PLANT (WEST BANK)      1936   \n",
       "3                             CARVER PENN PLAYGROUND      3301   \n",
       "4  EJE0058 REPLACEMENT OF SPECIAL FUNCTIONS & STO...      4006   \n",
       "5            LIGHT CITY CHURCH AND CHRISTIAN ACADEMY     20109   \n",
       "6                                      DAC/COI COSTS     21021   \n",
       "7     EWA0023 BCS'SUPERIOR ELEMENTARY SCHOOL BLDG. A      2308   \n",
       "8                                 CITY HALL BUILDING      4367   \n",
       "9                ST MARY'S ACADEMY  -  SWIMMING POOL     16239   \n",
       "\n",
       "          damageCategory  count  \n",
       "0       Public Buildings      2  \n",
       "1  Recreational or Other      2  \n",
       "2       Public Buildings      2  \n",
       "3       Public Buildings      2  \n",
       "4       Public Buildings      2  \n",
       "5       Public Buildings      2  \n",
       "6       Public Buildings      2  \n",
       "7       Public Buildings      2  \n",
       "8       Public Buildings      2  \n",
       "9       Public Buildings      2  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add damage category to identify unique records\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "       disasterNumber,\n",
    "       state,\n",
    "       applicantId,\n",
    "       applicationTitle,\n",
    "       pwNumber,\n",
    "       damageCategory,\n",
    "       count(id) AS count\n",
    "    FROM\n",
    "        public_assistance\n",
    "    GROUP BY ALL\n",
    "    ORDER BY\n",
    "        count DESC, disasterNumber ASC  \n",
    "    LIMIT 10\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "b1d22dc2",
   "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>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>applicantId</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>pwNumber</th>\n",
       "      <th>projectSize</th>\n",
       "      <th>damageCategory</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>465-19792-00</td>\n",
       "      <td>IMMEDIATE NEEDS FUNDING</td>\n",
       "      <td>1</td>\n",
       "      <td>Large</td>\n",
       "      <td>Protective Measures</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>465-99465-00</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>23</td>\n",
       "      <td>Small</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>465-062A0-00</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>27</td>\n",
       "      <td>Small</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>465-062A0-00</td>\n",
       "      <td>L</td>\n",
       "      <td>28</td>\n",
       "      <td>Large</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>463-99463-00</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>54</td>\n",
       "      <td>Small</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>463-99463-00</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>55</td>\n",
       "      <td>Small</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>463-99463-00</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>57</td>\n",
       "      <td>Small</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>465-062A0-00</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>61</td>\n",
       "      <td>Small</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>465-000A1-00</td>\n",
       "      <td>Not Provided</td>\n",
       "      <td>67</td>\n",
       "      <td>Small</td>\n",
       "      <td>Public Buildings</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1239</td>\n",
       "      <td>Texas</td>\n",
       "      <td>465-99465-00</td>\n",
       "      <td>L</td>\n",
       "      <td>70</td>\n",
       "      <td>Large</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   disasterNumber  state   applicantId         applicationTitle  pwNumber  \\\n",
       "0            1239  Texas  465-19792-00  IMMEDIATE NEEDS FUNDING         1   \n",
       "1            1239  Texas  465-99465-00             Not Provided        23   \n",
       "2            1239  Texas  465-062A0-00             Not Provided        27   \n",
       "3            1239  Texas  465-062A0-00                        L        28   \n",
       "4            1239  Texas  463-99463-00             Not Provided        54   \n",
       "5            1239  Texas  463-99463-00             Not Provided        55   \n",
       "6            1239  Texas  463-99463-00             Not Provided        57   \n",
       "7            1239  Texas  465-062A0-00             Not Provided        61   \n",
       "8            1239  Texas  465-000A1-00             Not Provided        67   \n",
       "9            1239  Texas  465-99465-00                        L        70   \n",
       "\n",
       "  projectSize       damageCategory  count  \n",
       "0       Large  Protective Measures      1  \n",
       "1       Small    Roads and Bridges      1  \n",
       "2       Small     Public Buildings      1  \n",
       "3       Large     Public Buildings      1  \n",
       "4       Small    Roads and Bridges      1  \n",
       "5       Small    Roads and Bridges      1  \n",
       "6       Small    Roads and Bridges      1  \n",
       "7       Small     Public Buildings      1  \n",
       "8       Small     Public Buildings      1  \n",
       "9       Large    Roads and Bridges      1  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add project size\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "       disasterNumber,\n",
    "       state,\n",
    "       applicantId,\n",
    "       applicationTitle,\n",
    "       pwNumber,\n",
    "       projectSize,\n",
    "       damageCategory,\n",
    "       count(id) AS count\n",
    "    FROM\n",
    "        public_assistance\n",
    "    GROUP BY ALL\n",
    "    ORDER BY\n",
    "        count DESC, disasterNumber ASC  \n",
    "    LIMIT 10\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0340b204",
   "metadata": {},
   "source": [
    "From the analysis above, unique record consist of a disaster, a state, an applicant, PW number, project size, and damage category."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9cf814e",
   "metadata": {},
   "source": [
    "# Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "61c36baa",
   "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>column_name</th>\n",
       "      <th>column_type</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>approx_unique</th>\n",
       "      <th>avg</th>\n",
       "      <th>std</th>\n",
       "      <th>q25</th>\n",
       "      <th>q50</th>\n",
       "      <th>q75</th>\n",
       "      <th>count</th>\n",
       "      <th>null_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>disasterNumber</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>1239</td>\n",
       "      <td>4834</td>\n",
       "      <td>1658</td>\n",
       "      <td>2644.8032189849623</td>\n",
       "      <td>1282.3435294583908</td>\n",
       "      <td>1603</td>\n",
       "      <td>1859</td>\n",
       "      <td>4143</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>declarationDate</td>\n",
       "      <td>TIMESTAMP</td>\n",
       "      <td>1998-08-26 00:00:00</td>\n",
       "      <td>2024-10-11 00:00:00</td>\n",
       "      <td>1272</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>incidentType</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Biological</td>\n",
       "      <td>Winter Storm</td>\n",
       "      <td>28</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>pwNumber</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>1</td>\n",
       "      <td>108017</td>\n",
       "      <td>20071</td>\n",
       "      <td>1652.7270811670132</td>\n",
       "      <td>2812.4249005260904</td>\n",
       "      <td>192</td>\n",
       "      <td>598</td>\n",
       "      <td>1716</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>applicationTitle</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>\\t1-30 day debris removal</td>\n",
       "      <td>¿Public Health Division 14, July 6, 2021 thru ...</td>\n",
       "      <td>531301</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>applicantId</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>000-00000-01</td>\n",
       "      <td>999-99999-99</td>\n",
       "      <td>67748</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>damageCategoryCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>A - Debris Removal</td>\n",
       "      <td>Z - State Management</td>\n",
       "      <td>16</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>projectSize</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Large</td>\n",
       "      <td>Small</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>1.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>county</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Abbeville</td>\n",
       "      <td>Ziebach</td>\n",
       "      <td>3561</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>1.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>countyCode</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>840</td>\n",
       "      <td>333</td>\n",
       "      <td>71.84950198190873</td>\n",
       "      <td>81.78430406469897</td>\n",
       "      <td>14</td>\n",
       "      <td>54</td>\n",
       "      <td>105</td>\n",
       "      <td>800128</td>\n",
       "      <td>1.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>state</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>63</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>stateCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>AK</td>\n",
       "      <td>WY</td>\n",
       "      <td>61</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>stateNumberCode</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>1003</td>\n",
       "      <td>60</td>\n",
       "      <td>31.938379859222525</td>\n",
       "      <td>28.462248884046275</td>\n",
       "      <td>19</td>\n",
       "      <td>32</td>\n",
       "      <td>42</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>projectAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-372687108.0</td>\n",
       "      <td>9053782360.01</td>\n",
       "      <td>605340</td>\n",
       "      <td>338555.86592993897</td>\n",
       "      <td>14736543.031356422</td>\n",
       "      <td>3964.7216442921435</td>\n",
       "      <td>12020.788937021878</td>\n",
       "      <td>43157.75290009091</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>federalShareObligated</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-372687108.0</td>\n",
       "      <td>9053782360.01</td>\n",
       "      <td>627550</td>\n",
       "      <td>309817.51612332376</td>\n",
       "      <td>14495910.563616406</td>\n",
       "      <td>3139.4236266517223</td>\n",
       "      <td>9680.814315312911</td>\n",
       "      <td>35187.367669150175</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>totalObligated</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-376423296.25</td>\n",
       "      <td>9053782360.01</td>\n",
       "      <td>661149</td>\n",
       "      <td>310584.1216519752</td>\n",
       "      <td>14500259.377777914</td>\n",
       "      <td>3235.270212422886</td>\n",
       "      <td>9925.176934867435</td>\n",
       "      <td>35913.31078759798</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>obligatedDate</td>\n",
       "      <td>TIMESTAMP</td>\n",
       "      <td>1998-09-15 00:00:00</td>\n",
       "      <td>2024-10-19 00:00:00</td>\n",
       "      <td>7501</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>dcc</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>A</td>\n",
       "      <td>Z</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>damageCategory</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Buildings and Equipment</td>\n",
       "      <td>Water Control Facilities</td>\n",
       "      <td>16</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>lastRefresh</td>\n",
       "      <td>TIMESTAMP</td>\n",
       "      <td>2024-03-22 11:47:01.426</td>\n",
       "      <td>2024-10-20 15:05:23.895</td>\n",
       "      <td>149</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>hash</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>0000290a56046086abb4a55c5a9fb1d88c01b3bb</td>\n",
       "      <td>ffffb6b610ad0d6fbffa634e902f53c1fb398484</td>\n",
       "      <td>806735</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>id</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>000002c6-2553-42e7-b260-631126e47c47</td>\n",
       "      <td>ffffff4b-7443-4161-97fe-19b06696bc45</td>\n",
       "      <td>813695</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              column_name column_type  \\\n",
       "0          disasterNumber      BIGINT   \n",
       "1         declarationDate   TIMESTAMP   \n",
       "2            incidentType     VARCHAR   \n",
       "3                pwNumber      BIGINT   \n",
       "4        applicationTitle     VARCHAR   \n",
       "5             applicantId     VARCHAR   \n",
       "6      damageCategoryCode     VARCHAR   \n",
       "7             projectSize     VARCHAR   \n",
       "8                  county     VARCHAR   \n",
       "9              countyCode      BIGINT   \n",
       "10                  state     VARCHAR   \n",
       "11              stateCode     VARCHAR   \n",
       "12        stateNumberCode      BIGINT   \n",
       "13          projectAmount      DOUBLE   \n",
       "14  federalShareObligated      DOUBLE   \n",
       "15         totalObligated      DOUBLE   \n",
       "16          obligatedDate   TIMESTAMP   \n",
       "17                    dcc     VARCHAR   \n",
       "18         damageCategory     VARCHAR   \n",
       "19            lastRefresh   TIMESTAMP   \n",
       "20                   hash     VARCHAR   \n",
       "21                     id     VARCHAR   \n",
       "\n",
       "                                         min  \\\n",
       "0                                       1239   \n",
       "1                        1998-08-26 00:00:00   \n",
       "2                                 Biological   \n",
       "3                                          1   \n",
       "4                  \\t1-30 day debris removal   \n",
       "5                               000-00000-01   \n",
       "6                         A - Debris Removal   \n",
       "7                                      Large   \n",
       "8                                  Abbeville   \n",
       "9                                          0   \n",
       "10                                   Alabama   \n",
       "11                                        AK   \n",
       "12                                         0   \n",
       "13                              -372687108.0   \n",
       "14                              -372687108.0   \n",
       "15                             -376423296.25   \n",
       "16                       1998-09-15 00:00:00   \n",
       "17                                         A   \n",
       "18                   Buildings and Equipment   \n",
       "19                   2024-03-22 11:47:01.426   \n",
       "20  0000290a56046086abb4a55c5a9fb1d88c01b3bb   \n",
       "21      000002c6-2553-42e7-b260-631126e47c47   \n",
       "\n",
       "                                                  max  approx_unique  \\\n",
       "0                                                4834           1658   \n",
       "1                                 2024-10-11 00:00:00           1272   \n",
       "2                                        Winter Storm             28   \n",
       "3                                              108017          20071   \n",
       "4   ¿Public Health Division 14, July 6, 2021 thru ...         531301   \n",
       "5                                        999-99999-99          67748   \n",
       "6                                Z - State Management             16   \n",
       "7                                               Small              2   \n",
       "8                                             Ziebach           3561   \n",
       "9                                                 840            333   \n",
       "10                                            Wyoming             63   \n",
       "11                                                 WY             61   \n",
       "12                                               1003             60   \n",
       "13                                      9053782360.01         605340   \n",
       "14                                      9053782360.01         627550   \n",
       "15                                      9053782360.01         661149   \n",
       "16                                2024-10-19 00:00:00           7501   \n",
       "17                                                  Z              8   \n",
       "18                           Water Control Facilities             16   \n",
       "19                            2024-10-20 15:05:23.895            149   \n",
       "20           ffffb6b610ad0d6fbffa634e902f53c1fb398484         806735   \n",
       "21               ffffff4b-7443-4161-97fe-19b06696bc45         813695   \n",
       "\n",
       "                   avg                 std                 q25  \\\n",
       "0   2644.8032189849623  1282.3435294583908                1603   \n",
       "1                 None                None                None   \n",
       "2                 None                None                None   \n",
       "3   1652.7270811670132  2812.4249005260904                 192   \n",
       "4                 None                None                None   \n",
       "5                 None                None                None   \n",
       "6                 None                None                None   \n",
       "7                 None                None                None   \n",
       "8                 None                None                None   \n",
       "9    71.84950198190873   81.78430406469897                  14   \n",
       "10                None                None                None   \n",
       "11                None                None                None   \n",
       "12  31.938379859222525  28.462248884046275                  19   \n",
       "13  338555.86592993897  14736543.031356422  3964.7216442921435   \n",
       "14  309817.51612332376  14495910.563616406  3139.4236266517223   \n",
       "15   310584.1216519752  14500259.377777914   3235.270212422886   \n",
       "16                None                None                None   \n",
       "17                None                None                None   \n",
       "18                None                None                None   \n",
       "19                None                None                None   \n",
       "20                None                None                None   \n",
       "21                None                None                None   \n",
       "\n",
       "                   q50                 q75   count  null_percentage  \n",
       "0                 1859                4143  800128             0.00  \n",
       "1                 None                None  800128             0.00  \n",
       "2                 None                None  800128             0.00  \n",
       "3                  598                1716  800128             0.00  \n",
       "4                 None                None  800128             0.78  \n",
       "5                 None                None  800128             0.00  \n",
       "6                 None                None  800128             0.00  \n",
       "7                 None                None  800128             1.63  \n",
       "8                 None                None  800128             1.69  \n",
       "9                   54                 105  800128             1.63  \n",
       "10                None                None  800128             0.00  \n",
       "11                None                None  800128             0.00  \n",
       "12                  32                  42  800128             0.00  \n",
       "13  12020.788937021878   43157.75290009091  800128             0.00  \n",
       "14   9680.814315312911  35187.367669150175  800128             0.00  \n",
       "15   9925.176934867435   35913.31078759798  800128             0.00  \n",
       "16                None                None  800128             0.00  \n",
       "17                None                None  800128             0.00  \n",
       "18                None                None  800128             0.00  \n",
       "19                None                None  800128             0.00  \n",
       "20                None                None  800128             0.00  \n",
       "21                None                None  800128             0.00  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# summary statistics\n",
    "sql = \"\"\"\n",
    "    SUMMARIZE\n",
    "        SELECT *\n",
    "        FROM public_assistance\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4c742b00",
   "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>column_name</th>\n",
       "      <th>column_type</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>approx_unique</th>\n",
       "      <th>avg</th>\n",
       "      <th>std</th>\n",
       "      <th>q25</th>\n",
       "      <th>q50</th>\n",
       "      <th>q75</th>\n",
       "      <th>count</th>\n",
       "      <th>null_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>projectAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-372687108.0</td>\n",
       "      <td>9053782360.01</td>\n",
       "      <td>605340</td>\n",
       "      <td>338555.86592993897</td>\n",
       "      <td>14736543.031356422</td>\n",
       "      <td>3964.7216442921435</td>\n",
       "      <td>12020.788937021878</td>\n",
       "      <td>43157.75290009091</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>federalShareObligated</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-372687108.0</td>\n",
       "      <td>9053782360.01</td>\n",
       "      <td>627550</td>\n",
       "      <td>309817.51612332376</td>\n",
       "      <td>14495910.563616406</td>\n",
       "      <td>3139.4236266517223</td>\n",
       "      <td>9680.814315312911</td>\n",
       "      <td>35187.367669150175</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>totalObligated</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-376423296.25</td>\n",
       "      <td>9053782360.01</td>\n",
       "      <td>661149</td>\n",
       "      <td>310584.1216519752</td>\n",
       "      <td>14500259.377777914</td>\n",
       "      <td>3235.270212422886</td>\n",
       "      <td>9925.176934867435</td>\n",
       "      <td>35913.31078759798</td>\n",
       "      <td>800128</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             column_name column_type            min            max  \\\n",
       "0          projectAmount      DOUBLE   -372687108.0  9053782360.01   \n",
       "1  federalShareObligated      DOUBLE   -372687108.0  9053782360.01   \n",
       "2         totalObligated      DOUBLE  -376423296.25  9053782360.01   \n",
       "\n",
       "   approx_unique                 avg                 std                 q25  \\\n",
       "0         605340  338555.86592993897  14736543.031356422  3964.7216442921435   \n",
       "1         627550  309817.51612332376  14495910.563616406  3139.4236266517223   \n",
       "2         661149   310584.1216519752  14500259.377777914   3235.270212422886   \n",
       "\n",
       "                  q50                 q75   count  null_percentage  \n",
       "0  12020.788937021878   43157.75290009091  800128              0.0  \n",
       "1   9680.814315312911  35187.367669150175  800128              0.0  \n",
       "2   9925.176934867435   35913.31078759798  800128              0.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine 'projectAmount', 'federalShareObligated', 'totalObligated' summary stats\n",
    "sql = \"\"\"\n",
    "    SELECT *\n",
    "    FROM (SUMMARIZE public_assistance)\n",
    "    WHERE column_name IN (\n",
    "        'projectAmount',\n",
    "        'federalShareObligated',\n",
    "        'totalObligated'\n",
    "    )\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c83e5cde",
   "metadata": {},
   "source": [
    "# Analysis\n",
    "Add constraint `WHERE declarationDate >= '2003-01-01'` to be consistent with FEMA's online [data visualization](https://www.fema.gov/data-visualization/public-assistance-program-summary-obligations)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3966d04d",
   "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>disasterNumber</th>\n",
       "      <th>declarationDate</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>pwNumber</th>\n",
       "      <th>applicationTitle</th>\n",
       "      <th>applicantId</th>\n",
       "      <th>damageCategoryCode</th>\n",
       "      <th>projectSize</th>\n",
       "      <th>county</th>\n",
       "      <th>countyCode</th>\n",
       "      <th>...</th>\n",
       "      <th>stateNumberCode</th>\n",
       "      <th>projectAmount</th>\n",
       "      <th>federalShareObligated</th>\n",
       "      <th>totalObligated</th>\n",
       "      <th>obligatedDate</th>\n",
       "      <th>dcc</th>\n",
       "      <th>damageCategory</th>\n",
       "      <th>lastRefresh</th>\n",
       "      <th>hash</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4023</td>\n",
       "      <td>2011-09-02</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>247</td>\n",
       "      <td>MCMDG00 Trumbull Ave Revetment</td>\n",
       "      <td>009-47500-00</td>\n",
       "      <td>G - Recreational or Other</td>\n",
       "      <td>Large</td>\n",
       "      <td>New Haven</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>614291.00</td>\n",
       "      <td>2012-04-05</td>\n",
       "      <td>G</td>\n",
       "      <td>Recreational or Other</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>5ff26ea21f9af4de5dce40c1f4ab2324b11b5fcb</td>\n",
       "      <td>d247f735-5e5d-4d47-9b4b-58a2fa6396b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4155</td>\n",
       "      <td>2013-11-08</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>173</td>\n",
       "      <td>SG107  - Donated Resources</td>\n",
       "      <td>093-62100-00</td>\n",
       "      <td>B - Protective Measures</td>\n",
       "      <td>Small</td>\n",
       "      <td>Meade</td>\n",
       "      <td>93</td>\n",
       "      <td>...</td>\n",
       "      <td>46</td>\n",
       "      <td>20739.92</td>\n",
       "      <td>16506.90</td>\n",
       "      <td>16506.90</td>\n",
       "      <td>2024-03-22</td>\n",
       "      <td>B</td>\n",
       "      <td>Protective Measures</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>39a1559d2dd1a04afafdcde601838082c65b4729</td>\n",
       "      <td>39552c91-2dc1-4519-87fb-b6beb815a0ad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4226</td>\n",
       "      <td>2015-06-26</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>62</td>\n",
       "      <td>SCT002C - Roads and Culverts</td>\n",
       "      <td>127-99127-00</td>\n",
       "      <td>C - Roads and Bridges</td>\n",
       "      <td>Large</td>\n",
       "      <td>Scott</td>\n",
       "      <td>127</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>205754.79</td>\n",
       "      <td>154316.09</td>\n",
       "      <td>154316.09</td>\n",
       "      <td>2015-12-08</td>\n",
       "      <td>C</td>\n",
       "      <td>Roads and Bridges</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>aef455b4030e92d3c7219138d3456fcebdd789bf</td>\n",
       "      <td>d02b7bb6-fb6c-4fb4-a029-58fdfc9848f9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4314</td>\n",
       "      <td>2017-05-22</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>14</td>\n",
       "      <td>DUARW01 - PAAP Debris Removal 1-30 Days</td>\n",
       "      <td>051-20500-00</td>\n",
       "      <td>A - Debris Removal</td>\n",
       "      <td>Large</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>51</td>\n",
       "      <td>...</td>\n",
       "      <td>28</td>\n",
       "      <td>2115399.61</td>\n",
       "      <td>1687334.81</td>\n",
       "      <td>1687334.81</td>\n",
       "      <td>2017-07-21</td>\n",
       "      <td>A</td>\n",
       "      <td>Debris Removal</td>\n",
       "      <td>2024-03-23 17:02:09.387</td>\n",
       "      <td>727e5a2e3628d483b2ac6407f48663a3a69a144e</td>\n",
       "      <td>044e88f1-b38e-483c-b579-80a60c6a10fa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4337</td>\n",
       "      <td>2017-09-10</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>6679</td>\n",
       "      <td>Zoo Exhibit and Trail Repairs</td>\n",
       "      <td>099-UXTQE-00</td>\n",
       "      <td>G - Parks, Recreational Facilities, and Other ...</td>\n",
       "      <td>Large</td>\n",
       "      <td>Palm Beach County</td>\n",
       "      <td>99</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>137858.93</td>\n",
       "      <td>124077.54</td>\n",
       "      <td>124077.54</td>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>G</td>\n",
       "      <td>Parks, Recreational Facilities, and Other Items</td>\n",
       "      <td>2024-09-06 15:04:53.183</td>\n",
       "      <td>ab453e975b05cc11633455ccde7607fdd24ff1ca</td>\n",
       "      <td>98731455-3b88-4fc0-af7f-dae089378537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   disasterNumber declarationDate  incidentType  pwNumber  \\\n",
       "0            4023      2011-09-02     Hurricane       247   \n",
       "1            4155      2013-11-08  Severe Storm       173   \n",
       "2            4226      2015-06-26  Severe Storm        62   \n",
       "3            4314      2017-05-22  Severe Storm        14   \n",
       "4            4337      2017-09-10     Hurricane      6679   \n",
       "\n",
       "                          applicationTitle   applicantId  \\\n",
       "0           MCMDG00 Trumbull Ave Revetment  009-47500-00   \n",
       "1               SG107  - Donated Resources  093-62100-00   \n",
       "2             SCT002C - Roads and Culverts  127-99127-00   \n",
       "3  DUARW01 - PAAP Debris Removal 1-30 Days  051-20500-00   \n",
       "4            Zoo Exhibit and Trail Repairs  099-UXTQE-00   \n",
       "\n",
       "                                  damageCategoryCode projectSize  \\\n",
       "0                          G - Recreational or Other       Large   \n",
       "1                            B - Protective Measures       Small   \n",
       "2                              C - Roads and Bridges       Large   \n",
       "3                                 A - Debris Removal       Large   \n",
       "4  G - Parks, Recreational Facilities, and Other ...       Large   \n",
       "\n",
       "              county  countyCode  ... stateNumberCode projectAmount  \\\n",
       "0          New Haven           9  ...               9     614291.00   \n",
       "1              Meade          93  ...              46      20739.92   \n",
       "2              Scott         127  ...               5     205754.79   \n",
       "3             Holmes          51  ...              28    2115399.61   \n",
       "4  Palm Beach County          99  ...              12     137858.93   \n",
       "\n",
       "   federalShareObligated  totalObligated  obligatedDate  dcc  \\\n",
       "0              614291.00       614291.00     2012-04-05    G   \n",
       "1               16506.90        16506.90     2024-03-22    B   \n",
       "2              154316.09       154316.09     2015-12-08    C   \n",
       "3             1687334.81      1687334.81     2017-07-21    A   \n",
       "4              124077.54       124077.54     2020-01-24    G   \n",
       "\n",
       "                                    damageCategory             lastRefresh  \\\n",
       "0                            Recreational or Other 2024-03-23 17:02:09.387   \n",
       "1                              Protective Measures 2024-03-23 17:02:09.387   \n",
       "2                                Roads and Bridges 2024-03-23 17:02:09.387   \n",
       "3                                   Debris Removal 2024-03-23 17:02:09.387   \n",
       "4  Parks, Recreational Facilities, and Other Items 2024-09-06 15:04:53.183   \n",
       "\n",
       "                                       hash  \\\n",
       "0  5ff26ea21f9af4de5dce40c1f4ab2324b11b5fcb   \n",
       "1  39a1559d2dd1a04afafdcde601838082c65b4729   \n",
       "2  aef455b4030e92d3c7219138d3456fcebdd789bf   \n",
       "3  727e5a2e3628d483b2ac6407f48663a3a69a144e   \n",
       "4  ab453e975b05cc11633455ccde7607fdd24ff1ca   \n",
       "\n",
       "                                     id  \n",
       "0  d247f735-5e5d-4d47-9b4b-58a2fa6396b4  \n",
       "1  39552c91-2dc1-4519-87fb-b6beb815a0ad  \n",
       "2  d02b7bb6-fb6c-4fb4-a029-58fdfc9848f9  \n",
       "3  044e88f1-b38e-483c-b579-80a60c6a10fa  \n",
       "4  98731455-3b88-4fc0-af7f-dae089378537  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview data\n",
    "con.sql(\"\"\"\n",
    "    SELECT *\n",
    "    FROM public_assistance\n",
    "    LIMIT 5\n",
    "\"\"\").df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3aa90adc",
   "metadata": {},
   "source": [
    "## Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "d75b4746",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────────┐\n",
       "│ totalObligated │\n",
       "│     int64      │\n",
       "├────────────────┤\n",
       "│   239639591774 │\n",
       "└────────────────┘"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "399256f9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total public assistance program total obligated amount: $239,639,591,774\n"
     ]
    }
   ],
   "source": [
    "# format dollar value\n",
    "totalObligated = con.sql(sql).df().values[0][0]\n",
    "\n",
    "print(f\"Total public assistance program total obligated amount: ${totalObligated:,}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "08ef2713",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total public assistance program declarations: 1,462\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        COUNT(DISTINCT disasterNumber)\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'\n",
    "\"\"\"\n",
    "\n",
    "disasterNumber = con.sql(sql).df().values[0][0]\n",
    "\n",
    "print(f\"Total public assistance program declarations: {disasterNumber:,}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bb3a7be3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of applicants with funded projects: 60,790\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        COUNT(DISTINCT applicantId)\n",
    "    FROM\n",
    "        public_assistance  \n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'\n",
    "\"\"\"\n",
    "\n",
    "applicantName = con.sql(sql).df().values[0][0]\n",
    "\n",
    "print(f\"Number of applicants with funded projects: {applicantName:,}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23a36d26",
   "metadata": {},
   "source": [
    "## Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90c99642",
   "metadata": {},
   "source": [
    "**Table xx.** Top 20 Disaster Declarations, States, Years, and Incident Types with the Highest Total Obligated Amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "9d9e6a66",
   "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>disasterNumber</th>\n",
       "      <th>state</th>\n",
       "      <th>year</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4339</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>34219012591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4480</td>\n",
       "      <td>New York</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>15621948518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4340</td>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>15613097183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4485</td>\n",
       "      <td>Texas</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>14931375168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4085</td>\n",
       "      <td>New York</td>\n",
       "      <td>2012</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>14734769570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1603</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>2005</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>13666166213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4482</td>\n",
       "      <td>California</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>11859337690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4486</td>\n",
       "      <td>Florida</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>3704570313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1604</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>2005</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>3218622940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4488</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>3065122302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4496</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>3000648669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4332</td>\n",
       "      <td>Texas</td>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2926236242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>4559</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>2020</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2878431248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4611</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>2021</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2708671159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4481</td>\n",
       "      <td>Washington</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>2575697107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4484</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>2496593976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4337</td>\n",
       "      <td>Florida</td>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2463907651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4673</td>\n",
       "      <td>Florida</td>\n",
       "      <td>2022</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2371842629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4086</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>2012</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2243002711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1791</td>\n",
       "      <td>Texas</td>\n",
       "      <td>2008</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2223441188</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    disasterNumber           state  year incidentType  totalObligated\n",
       "0             4339     Puerto Rico  2017    Hurricane     34219012591\n",
       "1             4480        New York  2020   Biological     15621948518\n",
       "2             4340  Virgin Islands  2017    Hurricane     15613097183\n",
       "3             4485           Texas  2020   Biological     14931375168\n",
       "4             4085        New York  2012    Hurricane     14734769570\n",
       "5             1603       Louisiana  2005    Hurricane     13666166213\n",
       "6             4482      California  2020   Biological     11859337690\n",
       "7             4486         Florida  2020   Biological      3704570313\n",
       "8             1604     Mississippi  2005    Hurricane      3218622940\n",
       "9             4488      New Jersey  2020   Biological      3065122302\n",
       "10            4496   Massachusetts  2020   Biological      3000648669\n",
       "11            4332           Texas  2017    Hurricane      2926236242\n",
       "12            4559       Louisiana  2020    Hurricane      2878431248\n",
       "13            4611       Louisiana  2021    Hurricane      2708671159\n",
       "14            4481      Washington  2020   Biological      2575697107\n",
       "15            4484       Louisiana  2020   Biological      2496593976\n",
       "16            4337         Florida  2017    Hurricane      2463907651\n",
       "17            4673         Florida  2022    Hurricane      2371842629\n",
       "18            4086      New Jersey  2012    Hurricane      2243002711\n",
       "19            1791           Texas  2008    Hurricane      2223441188"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        disasterNumber,\n",
    "        state,\n",
    "        substring(declarationDate::VARCHAR, 1, 4) as year,\n",
    "        incidentType,\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'    \n",
    "    GROUP BY ALL\n",
    "    ORDER BY\n",
    "        totalObligated DESC   \n",
    "    LIMIT 20\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51593714",
   "metadata": {},
   "source": [
    "**Table xx.** Number and Total Amount Obligations by Damage Category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "435164e1",
   "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>damageCategoryCode</th>\n",
       "      <th>countObligated</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A - Debris Removal</td>\n",
       "      <td>74495</td>\n",
       "      <td>19731447297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B - Emergency Protective Measures</td>\n",
       "      <td>56003</td>\n",
       "      <td>101109718074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B - Emergency Work Donated Resources</td>\n",
       "      <td>1709</td>\n",
       "      <td>46054652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B - Protective Measures</td>\n",
       "      <td>144887</td>\n",
       "      <td>13410518780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C - Roads and Bridges</td>\n",
       "      <td>217011</td>\n",
       "      <td>15970269942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>D - Water Control Facilities</td>\n",
       "      <td>9668</td>\n",
       "      <td>2347825641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>E - Buildings and Equipment</td>\n",
       "      <td>18620</td>\n",
       "      <td>18131279521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>E - Public Buildings</td>\n",
       "      <td>74326</td>\n",
       "      <td>18481469278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>F - Public Utilities</td>\n",
       "      <td>31184</td>\n",
       "      <td>11227176882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>F - Utilities</td>\n",
       "      <td>7457</td>\n",
       "      <td>22342104876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>G - Parks, Recreational Facilities, and Other ...</td>\n",
       "      <td>9347</td>\n",
       "      <td>2830257212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>G - Recreational or Other</td>\n",
       "      <td>31158</td>\n",
       "      <td>3967811751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Z - Direct Administrative Costs</td>\n",
       "      <td>1805</td>\n",
       "      <td>1461592079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Z - Management Costs</td>\n",
       "      <td>13611</td>\n",
       "      <td>6482305390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Z - Section 324 Management Costs</td>\n",
       "      <td>46</td>\n",
       "      <td>233409189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Z - State Management</td>\n",
       "      <td>7984</td>\n",
       "      <td>1866351210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   damageCategoryCode  countObligated  \\\n",
       "0                                  A - Debris Removal           74495   \n",
       "1                   B - Emergency Protective Measures           56003   \n",
       "2                B - Emergency Work Donated Resources            1709   \n",
       "3                             B - Protective Measures          144887   \n",
       "4                               C - Roads and Bridges          217011   \n",
       "5                        D - Water Control Facilities            9668   \n",
       "6                         E - Buildings and Equipment           18620   \n",
       "7                                E - Public Buildings           74326   \n",
       "8                                F - Public Utilities           31184   \n",
       "9                                       F - Utilities            7457   \n",
       "10  G - Parks, Recreational Facilities, and Other ...            9347   \n",
       "11                          G - Recreational or Other           31158   \n",
       "12                    Z - Direct Administrative Costs            1805   \n",
       "13                               Z - Management Costs           13611   \n",
       "14                   Z - Section 324 Management Costs              46   \n",
       "15                               Z - State Management            7984   \n",
       "\n",
       "    totalObligated  \n",
       "0      19731447297  \n",
       "1     101109718074  \n",
       "2         46054652  \n",
       "3      13410518780  \n",
       "4      15970269942  \n",
       "5       2347825641  \n",
       "6      18131279521  \n",
       "7      18481469278  \n",
       "8      11227176882  \n",
       "9      22342104876  \n",
       "10      2830257212  \n",
       "11      3967811751  \n",
       "12      1461592079  \n",
       "13      6482305390  \n",
       "14       233409189  \n",
       "15      1866351210  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        damageCategoryCode,\n",
    "        COUNT(totalObligated) AS countObligated,\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'\n",
    "    GROUP BY\n",
    "        damageCategoryCode \n",
    "    ORDER BY\n",
    "        damageCategoryCode ASC\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d393181",
   "metadata": {},
   "source": [
    "**Table xx.** Incident Types with the Highest Total Obligated Amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "a74adf8a",
   "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>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hurricane</td>\n",
       "      <td>117704128429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Biological</td>\n",
       "      <td>85751511508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>12211035769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Flood</td>\n",
       "      <td>5617721440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wildfire</td>\n",
       "      <td>3972705596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Severe Storm(s)</td>\n",
       "      <td>3741265684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Fire</td>\n",
       "      <td>2105294513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tropical Storm</td>\n",
       "      <td>2032557897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Earthquake</td>\n",
       "      <td>1424343349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Severe Ice Storm</td>\n",
       "      <td>1238587018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Snowstorm</td>\n",
       "      <td>1062232431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Winter Storm</td>\n",
       "      <td>827478713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Other</td>\n",
       "      <td>623227413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Tornado</td>\n",
       "      <td>568142179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Typhoon</td>\n",
       "      <td>399218674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Volcano</td>\n",
       "      <td>124893424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Mud/Landslide</td>\n",
       "      <td>73041864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Tsunami</td>\n",
       "      <td>50365943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Snow</td>\n",
       "      <td>30753523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Coastal Storm</td>\n",
       "      <td>29997143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Dam/Levee Break</td>\n",
       "      <td>21025562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Volcanic Eruption</td>\n",
       "      <td>11132782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>National Special Security Event</td>\n",
       "      <td>6215069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Terrorist</td>\n",
       "      <td>6195206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Freezing</td>\n",
       "      <td>3343649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Chemical</td>\n",
       "      <td>1639099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Toxic Substances</td>\n",
       "      <td>1458636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Drought</td>\n",
       "      <td>79260</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       incidentType  totalObligated\n",
       "0                         Hurricane    117704128429\n",
       "1                        Biological     85751511508\n",
       "2                      Severe Storm     12211035769\n",
       "3                             Flood      5617721440\n",
       "4                          Wildfire      3972705596\n",
       "5                   Severe Storm(s)      3741265684\n",
       "6                              Fire      2105294513\n",
       "7                    Tropical Storm      2032557897\n",
       "8                        Earthquake      1424343349\n",
       "9                  Severe Ice Storm      1238587018\n",
       "10                        Snowstorm      1062232431\n",
       "11                     Winter Storm       827478713\n",
       "12                            Other       623227413\n",
       "13                          Tornado       568142179\n",
       "14                          Typhoon       399218674\n",
       "15                          Volcano       124893424\n",
       "16                    Mud/Landslide        73041864\n",
       "17                          Tsunami        50365943\n",
       "18                             Snow        30753523\n",
       "19                    Coastal Storm        29997143\n",
       "20                  Dam/Levee Break        21025562\n",
       "21                Volcanic Eruption        11132782\n",
       "22  National Special Security Event         6215069\n",
       "23                        Terrorist         6195206\n",
       "24                         Freezing         3343649\n",
       "25                         Chemical         1639099\n",
       "26                 Toxic Substances         1458636\n",
       "27                          Drought           79260"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        incidentType,\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'    \n",
    "    GROUP BY\n",
    "        incidentType\n",
    "    ORDER BY\n",
    "        totalObligated DESC   \n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f065ca00",
   "metadata": {},
   "source": [
    "**Table xx.** Top 20 Years with the Highest Total Obligated Amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "83fe7d52",
   "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>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>93457114313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>57815006617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>21509286775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>17883384428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>6345451122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2008</td>\n",
       "      <td>5947021905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021</td>\n",
       "      <td>5234464414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022</td>\n",
       "      <td>4809628729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011</td>\n",
       "      <td>3819139907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2004</td>\n",
       "      <td>3465609239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016</td>\n",
       "      <td>3254503265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2019</td>\n",
       "      <td>2287881529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2023</td>\n",
       "      <td>1892070356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2007</td>\n",
       "      <td>1746398987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2009</td>\n",
       "      <td>1605743093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2010</td>\n",
       "      <td>1533523763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2024</td>\n",
       "      <td>1383502956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013</td>\n",
       "      <td>1243401596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2015</td>\n",
       "      <td>1167481060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2006</td>\n",
       "      <td>1115908796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2003</td>\n",
       "      <td>1092053513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2014</td>\n",
       "      <td>1031015410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    year  totalObligated\n",
       "0   2020     93457114313\n",
       "1   2017     57815006617\n",
       "2   2005     21509286775\n",
       "3   2012     17883384428\n",
       "4   2018      6345451122\n",
       "5   2008      5947021905\n",
       "6   2021      5234464414\n",
       "7   2022      4809628729\n",
       "8   2011      3819139907\n",
       "9   2004      3465609239\n",
       "10  2016      3254503265\n",
       "11  2019      2287881529\n",
       "12  2023      1892070356\n",
       "13  2007      1746398987\n",
       "14  2009      1605743093\n",
       "15  2010      1533523763\n",
       "16  2024      1383502956\n",
       "17  2013      1243401596\n",
       "18  2015      1167481060\n",
       "19  2006      1115908796\n",
       "20  2003      1092053513\n",
       "21  2014      1031015410"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        substring(declarationDate::VARCHAR, 1, 4) as year,\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'    \n",
    "    GROUP BY\n",
    "        year\n",
    "    ORDER BY\n",
    "        totalObligated DESC   \n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fef1bd96",
   "metadata": {},
   "source": [
    "**Table xx.** Top 20 Years and Incident Types with the Highest Total Obligated Amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "462e52e1",
   "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>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>Biological</td>\n",
       "      <td>85748618068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>55339902502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>20667100370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>17484753483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>3717818500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>3571175987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2008</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>3295165910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>3283002843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2004</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2932842864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2021</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2914438091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2008</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>2248926413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>1987915990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2016</td>\n",
       "      <td>Flood</td>\n",
       "      <td>1599967274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019</td>\n",
       "      <td>Severe Storm(s)</td>\n",
       "      <td>1415065020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2011</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>1339441656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2007</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>1307453241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2011</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>1269171770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2016</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>1117341162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2017</td>\n",
       "      <td>Flood</td>\n",
       "      <td>1096353982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2009</td>\n",
       "      <td>Severe Storm</td>\n",
       "      <td>1089064265</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    year     incidentType  totalObligated\n",
       "0   2020       Biological     85748618068\n",
       "1   2017        Hurricane     55339902502\n",
       "2   2005        Hurricane     20667100370\n",
       "3   2012        Hurricane     17484753483\n",
       "4   2022        Hurricane      3717818500\n",
       "5   2020        Hurricane      3571175987\n",
       "6   2008        Hurricane      3295165910\n",
       "7   2018        Hurricane      3283002843\n",
       "8   2004        Hurricane      2932842864\n",
       "9   2021        Hurricane      2914438091\n",
       "10  2008     Severe Storm      2248926413\n",
       "11  2018         Wildfire      1987915990\n",
       "12  2016            Flood      1599967274\n",
       "13  2019  Severe Storm(s)      1415065020\n",
       "14  2011     Severe Storm      1339441656\n",
       "15  2007     Severe Storm      1307453241\n",
       "16  2011        Hurricane      1269171770\n",
       "17  2016        Hurricane      1117341162\n",
       "18  2017            Flood      1096353982\n",
       "19  2009     Severe Storm      1089064265"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        substring(declarationDate::VARCHAR, 1, 4) as year,\n",
    "        incidentType,\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'    \n",
    "    GROUP BY\n",
    "        1, 2\n",
    "    ORDER BY\n",
    "        totalObligated DESC  \n",
    "    LIMIT 20\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff7f478d",
   "metadata": {},
   "source": [
    "**Table xx.** Top 20 States with the Highest Total Obligated Amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "5a9230a4",
   "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>state</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>36793614855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New York</td>\n",
       "      <td>33617112453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>24970218064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Texas</td>\n",
       "      <td>22169182338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>18286152637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Florida</td>\n",
       "      <td>17785922221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>15839264991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>6145263406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>4231493306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>3803345210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>3709027968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Washington</td>\n",
       "      <td>3188604527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>2750804861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>2527666297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>2514327747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>2500375646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>2392526525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>2354944500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>1805076874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>1720353300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             state  totalObligated\n",
       "0      Puerto Rico     36793614855\n",
       "1         New York     33617112453\n",
       "2        Louisiana     24970218064\n",
       "3            Texas     22169182338\n",
       "4       California     18286152637\n",
       "5          Florida     17785922221\n",
       "6   Virgin Islands     15839264991\n",
       "7       New Jersey      6145263406\n",
       "8      Mississippi      4231493306\n",
       "9   North Carolina      3803345210\n",
       "10   Massachusetts      3709027968\n",
       "11      Washington      3188604527\n",
       "12        Colorado      2750804861\n",
       "13    Pennsylvania      2527666297\n",
       "14        Maryland      2514327747\n",
       "15          Oregon      2500375646\n",
       "16        Illinois      2392526525\n",
       "17            Iowa      2354944500\n",
       "18        Virginia      1805076874\n",
       "19        Missouri      1720353300"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        state,\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'    \n",
    "    GROUP BY\n",
    "        state\n",
    "    ORDER BY\n",
    "        totalObligated DESC   \n",
    "    LIMIT 20\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b7e18c6",
   "metadata": {},
   "source": [
    "**Table xx.** Top 20 States and Incident Types with the Highest Total Obligated Amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "4b212a44",
   "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>state</th>\n",
       "      <th>incidentType</th>\n",
       "      <th>totalObligated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>35542209396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>21396779409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>15680481937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>New York</td>\n",
       "      <td>Biological</td>\n",
       "      <td>15625510704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>15301525435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Texas</td>\n",
       "      <td>Biological</td>\n",
       "      <td>14931375168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Florida</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>13601983195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>California</td>\n",
       "      <td>Biological</td>\n",
       "      <td>11868705995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Texas</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>6191403421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Florida</td>\n",
       "      <td>Biological</td>\n",
       "      <td>3706379958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>3325696442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>California</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>3097063529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>Biological</td>\n",
       "      <td>3065122302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>Biological</td>\n",
       "      <td>3002943753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Washington</td>\n",
       "      <td>Biological</td>\n",
       "      <td>2576696096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>Biological</td>\n",
       "      <td>2496593976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>Hurricane</td>\n",
       "      <td>2366015094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>Biological</td>\n",
       "      <td>2205820484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>Biological</td>\n",
       "      <td>2157395017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>Biological</td>\n",
       "      <td>2026375366</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             state incidentType  totalObligated\n",
       "0      Puerto Rico    Hurricane     35542209396\n",
       "1        Louisiana    Hurricane     21396779409\n",
       "2   Virgin Islands    Hurricane     15680481937\n",
       "3         New York   Biological     15625510704\n",
       "4         New York    Hurricane     15301525435\n",
       "5            Texas   Biological     14931375168\n",
       "6          Florida    Hurricane     13601983195\n",
       "7       California   Biological     11868705995\n",
       "8            Texas    Hurricane      6191403421\n",
       "9          Florida   Biological      3706379958\n",
       "10     Mississippi    Hurricane      3325696442\n",
       "11      California     Wildfire      3097063529\n",
       "12      New Jersey   Biological      3065122302\n",
       "13   Massachusetts   Biological      3002943753\n",
       "14      Washington   Biological      2576696096\n",
       "15       Louisiana   Biological      2496593976\n",
       "16      New Jersey    Hurricane      2366015094\n",
       "17        Colorado   Biological      2205820484\n",
       "18        Maryland   Biological      2157395017\n",
       "19        Illinois   Biological      2026375366"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        state,\n",
    "        incidentType,\n",
    "        ROUND(SUM(totalObligated), 0)::BIGINT AS totalObligated\n",
    "    FROM\n",
    "        public_assistance\n",
    "    WHERE\n",
    "        declarationDate >= '2003-01-01'    \n",
    "    GROUP BY\n",
    "        1, 2\n",
    "    ORDER BY\n",
    "        totalObligated DESC   \n",
    "    LIMIT 20\n",
    "\"\"\"\n",
    "\n",
    "con.sql(sql).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38779e11",
   "metadata": {},
   "source": [
    "# Export Data\n",
    "Export data to reproduce."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "6c3535d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export to parquet\n",
    "con.sql(\"COPY public_assistance TO 'data/public-assistance.parquet' (FORMAT PARQUET);\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "c60a03a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "disaster-declarations.parquet  public-assistance.parquet\r\n"
     ]
    }
   ],
   "source": [
    "# sanity check\n",
    "%ls data/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "6a9115c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────────┬─────────────────────┬──────────────┬───┬──────────────────────┬──────────────────────┐\n",
       "│ disasterNumber │   declarationDate   │ incidentType │ … │         hash         │          id          │\n",
       "│     int64      │      timestamp      │   varchar    │   │       varchar        │       varchar        │\n",
       "├────────────────┼─────────────────────┼──────────────┼───┼──────────────────────┼──────────────────────┤\n",
       "│           4023 │ 2011-09-02 00:00:00 │ Hurricane    │ … │ 5ff26ea21f9af4de5d…  │ d247f735-5e5d-4d47…  │\n",
       "│           4155 │ 2013-11-08 00:00:00 │ Severe Storm │ … │ 39a1559d2dd1a04afa…  │ 39552c91-2dc1-4519…  │\n",
       "│           4226 │ 2015-06-26 00:00:00 │ Severe Storm │ … │ aef455b4030e92d3c7…  │ d02b7bb6-fb6c-4fb4…  │\n",
       "│           4314 │ 2017-05-22 00:00:00 │ Severe Storm │ … │ 727e5a2e3628d483b2…  │ 044e88f1-b38e-483c…  │\n",
       "│           4337 │ 2017-09-10 00:00:00 │ Hurricane    │ … │ ab453e975b05cc1163…  │ 98731455-3b88-4fc0…  │\n",
       "│           4480 │ 2020-03-20 00:00:00 │ Biological   │ … │ ed6a778c448feb9e7e…  │ d23ffa21-c7cb-4acb…  │\n",
       "│           4488 │ 2020-03-25 00:00:00 │ Biological   │ … │ 4bc9461a8667de9891…  │ 484a376d-fd16-4cab…  │\n",
       "│           4480 │ 2020-03-20 00:00:00 │ Biological   │ … │ 015cbd964515723ebe…  │ b3ac45a0-a025-4188…  │\n",
       "│           4480 │ 2020-03-20 00:00:00 │ Biological   │ … │ b229b92cf1dcd5a412…  │ f56222a9-011a-43ed…  │\n",
       "│           4488 │ 2020-03-25 00:00:00 │ Biological   │ … │ b237312d93db152d87…  │ b9e7c7ce-a52a-4491…  │\n",
       "├────────────────┴─────────────────────┴──────────────┴───┴──────────────────────┴──────────────────────┤\n",
       "│ 10 rows                                                                          22 columns (5 shown) │\n",
       "└───────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check on exported parquet file\n",
    "con.sql(\"SELECT * FROM read_parquet('data/public-assistance.parquet') LIMIT 10;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91901463",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}