analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fc43a51c",
   "metadata": {},
   "source": [
    "# Analyzing FEMA's National Flood Insurance Program (NFIP) Data With DuckDB\n",
    "Author: Mark Bauer"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61d43be8",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "The FEMA National Flood Insurance Program (NFIP) website offers a trove of valuable information. Among its highlights is the [Flood Insurance Data and Analytics](https://nfipservices.floodsmart.gov/reports-flood-insurance-data) section, featuring data visualizations, tables, and reports. This project was inspired by these resources, particularly the Financial Losses by State and Policy, and Loss Statistics by Flood Zone Excel files.\n",
    "\n",
    "Additionally, this analysis focuses solely on the [NFIP Redated Claims](https://www.fema.gov/openfema-data-page/fima-nfip-redacted-claims-v2) dataset, and fortunately, it is available as a Parquet file. Given the size of this dataset, it was a good opportunity to utilize and learn more about [DuckDB](https://duckdb.org/).\n",
    "\n",
    "# Data\n",
    "Title: **OpenFEMA Dataset: FIMA NFIP Redacted Claims - v2**  \n",
    "\n",
    "You can find information about the data, as well as the data dictionary, here: https://www.fema.gov/openfema-data-page/fima-nfip-redacted-claims-v2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "92bb3fc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import duckdb\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2abfd2f0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb version: 0.10.0\n"
     ]
    }
   ],
   "source": [
    "print(\"duckdb version: {}\".format(duckdb.__version__))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b4eb2beb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a DuckDB database instance\n",
    "con = duckdb.connect(config={\"allow_unsigned_extensions\": \"true\"})\n",
    "\n",
    "# assign the path of the httpfs extension. Note: I built the extension from source but you\n",
    "# should review the extensions guide on DuckDB's official documentation.\n",
    "path = \"../duckdb/build/release/repository/v0.10.0/osx_amd64/httpfs.duckdb_extension\"\n",
    "\n",
    "# install and load extension\n",
    "con.install_extension(path)\n",
    "con.load_extension(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a60a95b9",
   "metadata": {},
   "source": [
    "# Create Table Using DuckDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "683565a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌────────────┐\n",
      "│ count_rows │\n",
      "│   int64    │\n",
      "├────────────┤\n",
      "│    2609973 │\n",
      "└────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# create table of the claims dataset\n",
    "con.sql(\n",
    "    \"\"\"\n",
    "    CREATE TABLE claims AS\n",
    "    FROM read_parquet('data/FimaNfipClaims.parquet')\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "# examine count of rows\n",
    "con.sql(\"SELECT COUNT(*) AS count_rows FROM claims\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59aadc50",
   "metadata": {},
   "source": [
    "# Examine and Describe Data Set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f4b54f07",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌──────────────────────┬──────────────────────┬──────────────────────┬───┬──────────────────────┬──────────┬───────────┐\n",
      "│          id          │ agricultureStructu…  │       asOfDate       │ … │ censusBlockGroupFips │ latitude │ longitude │\n",
      "│       varchar        │       boolean        │      timestamp       │   │       varchar        │  double  │  double   │\n",
      "├──────────────────────┼──────────────────────┼──────────────────────┼───┼──────────────────────┼──────────┼───────────┤\n",
      "│ 61a2811b-a92b-486d…  │ false                │ 2020-12-11 16:25:4…  │ … │ 010030114072         │     30.3 │     -87.7 │\n",
      "│ b9123f27-fa0e-4097…  │ false                │ 2020-11-13 14:50:3…  │ … │ 010030114073         │     30.3 │     -87.7 │\n",
      "│ 9de3700b-bbbd-408c…  │ false                │ 2022-04-21 19:56:4…  │ … │ 010010204001         │     32.5 │     -86.4 │\n",
      "│ c2450563-0de0-40df…  │ false                │ 2020-03-09 19:28:2…  │ … │ 010010204001         │     32.5 │     -86.4 │\n",
      "│ 6b13ea94-ef8d-428a…  │ false                │ 2020-01-22 16:55:5…  │ … │ 010010206001         │     32.5 │     -86.5 │\n",
      "│ d8e27485-f342-4874…  │ false                │ 2020-01-22 16:55:5…  │ … │ 010010205002         │     32.5 │     -86.4 │\n",
      "│ 44f223b4-2387-4c36…  │ false                │ 2020-01-22 16:55:5…  │ … │ 010010204001         │     32.5 │     -86.4 │\n",
      "│ 13b0a78d-5a30-4c7a…  │ false                │ 2024-03-08 14:44:1…  │ … │ 010510313001         │     32.4 │     -86.4 │\n",
      "│ 317cab0d-a88c-4e08…  │ false                │ 2020-01-22 16:55:5…  │ … │ 010010207001         │     32.4 │     -86.4 │\n",
      "│ 571188c3-533c-49c4…  │ false                │ 2023-08-09 12:56:4…  │ … │ 010010207001         │     32.4 │     -86.4 │\n",
      "├──────────────────────┴──────────────────────┴──────────────────────┴───┴──────────────────────┴──────────┴───────────┤\n",
      "│ 10 rows                                                                                         73 columns (6 shown) │\n",
      "└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# preview data\n",
    "con.sql(\"SELECT * FROM claims LIMIT 10\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "946b22cb",
   "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>null</th>\n",
       "      <th>key</th>\n",
       "      <th>default</th>\n",
       "      <th>extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>id</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>agricultureStructureIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>asOfDate</td>\n",
       "      <td>TIMESTAMP</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>basementEnclosureCrawlspaceType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>policyCount</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>countyCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>censusTract</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>censusBlockGroupFips</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>latitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>longitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        column_name column_type null   key default extra\n",
       "0                                id     VARCHAR  YES  None    None  None\n",
       "1     agricultureStructureIndicator     BOOLEAN  YES  None    None  None\n",
       "2                          asOfDate   TIMESTAMP  YES  None    None  None\n",
       "3   basementEnclosureCrawlspaceType    SMALLINT  YES  None    None  None\n",
       "4                       policyCount    SMALLINT  YES  None    None  None\n",
       "..                              ...         ...  ...   ...     ...   ...\n",
       "68                       countyCode     VARCHAR  YES  None    None  None\n",
       "69                      censusTract     VARCHAR  YES  None    None  None\n",
       "70             censusBlockGroupFips     VARCHAR  YES  None    None  None\n",
       "71                         latitude      DOUBLE  YES  None    None  None\n",
       "72                        longitude      DOUBLE  YES  None    None  None\n",
       "\n",
       "[73 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine column metadata and assign as a pandas df\n",
    "describe_df = con.sql(\"DESCRIBE claims\").df()\n",
    "\n",
    "describe_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4d511980",
   "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>null</th>\n",
       "      <th>key</th>\n",
       "      <th>default</th>\n",
       "      <th>extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>id</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>agricultureStructureIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>asOfDate</td>\n",
       "      <td>TIMESTAMP</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>basementEnclosureCrawlspaceType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>policyCount</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>crsClassificationCode</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>dateOfLoss</td>\n",
       "      <td>DATE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>elevatedBuildingIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>elevationCertificateIndicator</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>elevationDifference</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>baseFloodElevation</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ratedFloodZone</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>houseWorship</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>locationOfContents</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>lowestAdjacentGrade</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>lowestFloorElevation</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>numberOfFloorsInTheInsuredBuilding</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>nonProfitIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>obstructionType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>occupancyType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>originalConstructionDate</td>\n",
       "      <td>DATE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>originalNBDate</td>\n",
       "      <td>DATE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>amountPaidOnBuildingClaim</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>amountPaidOnContentsClaim</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>amountPaidOnIncreasedCostOfComplianceClaim</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   column_name column_type null   key default  \\\n",
       "0                                           id     VARCHAR  YES  None    None   \n",
       "1                agricultureStructureIndicator     BOOLEAN  YES  None    None   \n",
       "2                                     asOfDate   TIMESTAMP  YES  None    None   \n",
       "3              basementEnclosureCrawlspaceType    SMALLINT  YES  None    None   \n",
       "4                                  policyCount    SMALLINT  YES  None    None   \n",
       "5                        crsClassificationCode    SMALLINT  YES  None    None   \n",
       "6                                   dateOfLoss        DATE  YES  None    None   \n",
       "7                    elevatedBuildingIndicator     BOOLEAN  YES  None    None   \n",
       "8                elevationCertificateIndicator     VARCHAR  YES  None    None   \n",
       "9                          elevationDifference      DOUBLE  YES  None    None   \n",
       "10                          baseFloodElevation      DOUBLE  YES  None    None   \n",
       "11                              ratedFloodZone     VARCHAR  YES  None    None   \n",
       "12                                houseWorship     BOOLEAN  YES  None    None   \n",
       "13                          locationOfContents    SMALLINT  YES  None    None   \n",
       "14                         lowestAdjacentGrade      DOUBLE  YES  None    None   \n",
       "15                        lowestFloorElevation      DOUBLE  YES  None    None   \n",
       "16          numberOfFloorsInTheInsuredBuilding    SMALLINT  YES  None    None   \n",
       "17                          nonProfitIndicator     BOOLEAN  YES  None    None   \n",
       "18                             obstructionType    SMALLINT  YES  None    None   \n",
       "19                               occupancyType    SMALLINT  YES  None    None   \n",
       "20                    originalConstructionDate        DATE  YES  None    None   \n",
       "21                              originalNBDate        DATE  YES  None    None   \n",
       "22                   amountPaidOnBuildingClaim      DOUBLE  YES  None    None   \n",
       "23                   amountPaidOnContentsClaim      DOUBLE  YES  None    None   \n",
       "24  amountPaidOnIncreasedCostOfComplianceClaim      DOUBLE  YES  None    None   \n",
       "\n",
       "   extra  \n",
       "0   None  \n",
       "1   None  \n",
       "2   None  \n",
       "3   None  \n",
       "4   None  \n",
       "5   None  \n",
       "6   None  \n",
       "7   None  \n",
       "8   None  \n",
       "9   None  \n",
       "10  None  \n",
       "11  None  \n",
       "12  None  \n",
       "13  None  \n",
       "14  None  \n",
       "15  None  \n",
       "16  None  \n",
       "17  None  \n",
       "18  None  \n",
       "19  None  \n",
       "20  None  \n",
       "21  None  \n",
       "22  None  \n",
       "23  None  \n",
       "24  None  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine each column in sections because of large number of columns\n",
    "describe_df.iloc[:25, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b83c237b",
   "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>null</th>\n",
       "      <th>key</th>\n",
       "      <th>default</th>\n",
       "      <th>extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>postFIRMConstructionIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>rateMethod</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>smallBusinessIndicatorBuilding</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>totalBuildingInsuranceCoverage</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>totalContentsInsuranceCoverage</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>yearOfLoss</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>primaryResidenceIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>buildingDamageAmount</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>buildingDeductibleCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>netBuildingPaymentAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>buildingPropertyValue</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>causeOfDamage</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>condominiumCoverageTypeCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>contentsDamageAmount</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>contentsDeductibleCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>netContentsPaymentAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>contentsPropertyValue</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>disasterAssistanceCoverageRequired</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>eventDesignationNumber</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>ficoNumber</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>floodCharacteristicsIndicator</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>floodWaterDuration</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>floodproofedIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>floodEvent</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>iccCoverage</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           column_name column_type null   key default extra\n",
       "25       postFIRMConstructionIndicator     BOOLEAN  YES  None    None  None\n",
       "26                          rateMethod     VARCHAR  YES  None    None  None\n",
       "27      smallBusinessIndicatorBuilding     BOOLEAN  YES  None    None  None\n",
       "28      totalBuildingInsuranceCoverage      BIGINT  YES  None    None  None\n",
       "29      totalContentsInsuranceCoverage      BIGINT  YES  None    None  None\n",
       "30                          yearOfLoss    SMALLINT  YES  None    None  None\n",
       "31           primaryResidenceIndicator     BOOLEAN  YES  None    None  None\n",
       "32                buildingDamageAmount      BIGINT  YES  None    None  None\n",
       "33              buildingDeductibleCode     VARCHAR  YES  None    None  None\n",
       "34            netBuildingPaymentAmount      DOUBLE  YES  None    None  None\n",
       "35               buildingPropertyValue      BIGINT  YES  None    None  None\n",
       "36                       causeOfDamage     VARCHAR  YES  None    None  None\n",
       "37         condominiumCoverageTypeCode     VARCHAR  YES  None    None  None\n",
       "38                contentsDamageAmount      BIGINT  YES  None    None  None\n",
       "39              contentsDeductibleCode     VARCHAR  YES  None    None  None\n",
       "40            netContentsPaymentAmount      DOUBLE  YES  None    None  None\n",
       "41               contentsPropertyValue      BIGINT  YES  None    None  None\n",
       "42  disasterAssistanceCoverageRequired    SMALLINT  YES  None    None  None\n",
       "43              eventDesignationNumber     VARCHAR  YES  None    None  None\n",
       "44                          ficoNumber    SMALLINT  YES  None    None  None\n",
       "45       floodCharacteristicsIndicator    SMALLINT  YES  None    None  None\n",
       "46                  floodWaterDuration    SMALLINT  YES  None    None  None\n",
       "47               floodproofedIndicator     BOOLEAN  YES  None    None  None\n",
       "48                          floodEvent     VARCHAR  YES  None    None  None\n",
       "49                         iccCoverage     INTEGER  YES  None    None  None"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "describe_df.iloc[25:50, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2928e90b",
   "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>null</th>\n",
       "      <th>key</th>\n",
       "      <th>default</th>\n",
       "      <th>extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>netIccPaymentAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>nfipRatedCommunityNumber</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>nfipCommunityNumberCurrent</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>nfipCommunityName</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>nonPaymentReasonContents</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>nonPaymentReasonBuilding</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>numberOfUnits</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>buildingReplacementCost</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>contentsReplacementCost</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>replacementCostBasis</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>stateOwnedIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>waterDepth</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>floodZoneCurrent</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>buildingDescriptionCode</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>rentalPropertyIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>state</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>reportedCity</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>reportedZipCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>countyCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>censusTract</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>censusBlockGroupFips</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>latitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>longitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>YES</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   column_name column_type null   key default extra\n",
       "50         netIccPaymentAmount      DOUBLE  YES  None    None  None\n",
       "51    nfipRatedCommunityNumber     VARCHAR  YES  None    None  None\n",
       "52  nfipCommunityNumberCurrent     VARCHAR  YES  None    None  None\n",
       "53           nfipCommunityName     VARCHAR  YES  None    None  None\n",
       "54    nonPaymentReasonContents     VARCHAR  YES  None    None  None\n",
       "55    nonPaymentReasonBuilding     VARCHAR  YES  None    None  None\n",
       "56               numberOfUnits     INTEGER  YES  None    None  None\n",
       "57     buildingReplacementCost      BIGINT  YES  None    None  None\n",
       "58     contentsReplacementCost      BIGINT  YES  None    None  None\n",
       "59        replacementCostBasis     VARCHAR  YES  None    None  None\n",
       "60         stateOwnedIndicator     BOOLEAN  YES  None    None  None\n",
       "61                  waterDepth    SMALLINT  YES  None    None  None\n",
       "62            floodZoneCurrent     VARCHAR  YES  None    None  None\n",
       "63     buildingDescriptionCode    SMALLINT  YES  None    None  None\n",
       "64     rentalPropertyIndicator     BOOLEAN  YES  None    None  None\n",
       "65                       state     VARCHAR  YES  None    None  None\n",
       "66                reportedCity     VARCHAR  YES  None    None  None\n",
       "67             reportedZipCode     VARCHAR  YES  None    None  None\n",
       "68                  countyCode     VARCHAR  YES  None    None  None\n",
       "69                 censusTract     VARCHAR  YES  None    None  None\n",
       "70        censusBlockGroupFips     VARCHAR  YES  None    None  None\n",
       "71                    latitude      DOUBLE  YES  None    None  None\n",
       "72                   longitude      DOUBLE  YES  None    None  None"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "describe_df.iloc[50:, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac593dd8",
   "metadata": {},
   "source": [
    "# Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "39581469",
   "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>id</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>000006af-51d1-453e-8c06-5791060175ab</td>\n",
       "      <td>fffffeef-b65f-4405-ab4e-60257523aa4f</td>\n",
       "      <td>2662059</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>agricultureStructureIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>asOfDate</td>\n",
       "      <td>TIMESTAMP</td>\n",
       "      <td>2019-09-19 06:12:43.388</td>\n",
       "      <td>2024-05-01 20:18:36.77</td>\n",
       "      <td>425022</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>basementEnclosureCrawlspaceType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.228374267022299</td>\n",
       "      <td>1.0566934718476721</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2609973</td>\n",
       "      <td>70.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>policyCount</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>1090</td>\n",
       "      <td>398</td>\n",
       "      <td>1.2797094069555508</td>\n",
       "      <td>6.67351428857187</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>countyCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>01001</td>\n",
       "      <td>78030</td>\n",
       "      <td>2942</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>2.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>censusTract</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>01001020100</td>\n",
       "      <td>78030961200</td>\n",
       "      <td>54666</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>5.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>censusBlockGroupFips</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>010010201001</td>\n",
       "      <td>780309612002</td>\n",
       "      <td>110152</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>5.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>latitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-36.0</td>\n",
       "      <td>69.9</td>\n",
       "      <td>335</td>\n",
       "      <td>33.86958959935434</td>\n",
       "      <td>5.826147648469343</td>\n",
       "      <td>29.885555420103564</td>\n",
       "      <td>31.034309863474476</td>\n",
       "      <td>39.741329849029</td>\n",
       "      <td>2609973</td>\n",
       "      <td>1.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>longitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-170.7</td>\n",
       "      <td>174.0</td>\n",
       "      <td>717</td>\n",
       "      <td>-86.07527425472838</td>\n",
       "      <td>11.03787203900624</td>\n",
       "      <td>-91.22763060784378</td>\n",
       "      <td>-86.95045579385058</td>\n",
       "      <td>-77.21615237901081</td>\n",
       "      <td>2609973</td>\n",
       "      <td>1.53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        column_name column_type  \\\n",
       "0                                id     VARCHAR   \n",
       "1     agricultureStructureIndicator     BOOLEAN   \n",
       "2                          asOfDate   TIMESTAMP   \n",
       "3   basementEnclosureCrawlspaceType    SMALLINT   \n",
       "4                       policyCount    SMALLINT   \n",
       "..                              ...         ...   \n",
       "68                       countyCode     VARCHAR   \n",
       "69                      censusTract     VARCHAR   \n",
       "70             censusBlockGroupFips     VARCHAR   \n",
       "71                         latitude      DOUBLE   \n",
       "72                        longitude      DOUBLE   \n",
       "\n",
       "                                     min  \\\n",
       "0   000006af-51d1-453e-8c06-5791060175ab   \n",
       "1                                  false   \n",
       "2                2019-09-19 06:12:43.388   \n",
       "3                                      0   \n",
       "4                                      1   \n",
       "..                                   ...   \n",
       "68                                 01001   \n",
       "69                           01001020100   \n",
       "70                          010010201001   \n",
       "71                                 -36.0   \n",
       "72                                -170.7   \n",
       "\n",
       "                                     max  approx_unique                 avg  \\\n",
       "0   fffffeef-b65f-4405-ab4e-60257523aa4f        2662059                None   \n",
       "1                                   true              2                None   \n",
       "2                 2024-05-01 20:18:36.77         425022                None   \n",
       "3                                      4              4   1.228374267022299   \n",
       "4                                   1090            398  1.2797094069555508   \n",
       "..                                   ...            ...                 ...   \n",
       "68                                 78030           2942                None   \n",
       "69                           78030961200          54666                None   \n",
       "70                          780309612002         110152                None   \n",
       "71                                  69.9            335   33.86958959935434   \n",
       "72                                 174.0            717  -86.07527425472838   \n",
       "\n",
       "                   std                 q25                 q50  \\\n",
       "0                 None                None                None   \n",
       "1                 None                None                None   \n",
       "2                 None                None                None   \n",
       "3   1.0566934718476721                   0                   1   \n",
       "4     6.67351428857187                   1                   1   \n",
       "..                 ...                 ...                 ...   \n",
       "68                None                None                None   \n",
       "69                None                None                None   \n",
       "70                None                None                None   \n",
       "71   5.826147648469343  29.885555420103564  31.034309863474476   \n",
       "72   11.03787203900624  -91.22763060784378  -86.95045579385058   \n",
       "\n",
       "                   q75    count  null_percentage  \n",
       "0                 None  2609973             0.00  \n",
       "1                 None  2609973             0.00  \n",
       "2                 None  2609973             0.00  \n",
       "3                    2  2609973            70.18  \n",
       "4                    1  2609973             0.00  \n",
       "..                 ...      ...              ...  \n",
       "68                None  2609973             2.38  \n",
       "69                None  2609973             5.27  \n",
       "70                None  2609973             5.27  \n",
       "71     39.741329849029  2609973             1.53  \n",
       "72  -77.21615237901081  2609973             1.53  \n",
       "\n",
       "[73 rows x 12 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate summary statistics of each column\n",
    "summarize_df = con.sql(\"SUMMARIZE claims\").df()\n",
    "\n",
    "summarize_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "acb4ed9b",
   "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>id</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>000006af-51d1-453e-8c06-5791060175ab</td>\n",
       "      <td>fffffeef-b65f-4405-ab4e-60257523aa4f</td>\n",
       "      <td>2662059</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>agricultureStructureIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>asOfDate</td>\n",
       "      <td>TIMESTAMP</td>\n",
       "      <td>2019-09-19 06:12:43.388</td>\n",
       "      <td>2024-05-01 20:18:36.77</td>\n",
       "      <td>425022</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>basementEnclosureCrawlspaceType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.228374267022299</td>\n",
       "      <td>1.0566934718476721</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2609973</td>\n",
       "      <td>70.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>policyCount</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>1090</td>\n",
       "      <td>398</td>\n",
       "      <td>1.2797094069555508</td>\n",
       "      <td>6.67351428857187</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>crsClassificationCode</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>6.848428755453079</td>\n",
       "      <td>1.5049489596950096</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>2609973</td>\n",
       "      <td>82.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>dateOfLoss</td>\n",
       "      <td>DATE</td>\n",
       "      <td>1978-01-01</td>\n",
       "      <td>2024-05-01</td>\n",
       "      <td>16687</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>elevatedBuildingIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>elevationCertificateIndicator</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>1</td>\n",
       "      <td>E</td>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>77.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>elevationDifference</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-9989.0</td>\n",
       "      <td>998.0</td>\n",
       "      <td>370</td>\n",
       "      <td>1.2817296900477881</td>\n",
       "      <td>29.362656199635108</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.7745562409888866</td>\n",
       "      <td>2609973</td>\n",
       "      <td>72.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>baseFloodElevation</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>9998.0</td>\n",
       "      <td>10079</td>\n",
       "      <td>123.80080997365465</td>\n",
       "      <td>751.3180224953265</td>\n",
       "      <td>6.618645230395739</td>\n",
       "      <td>9.00000411190943</td>\n",
       "      <td>14.032950504289476</td>\n",
       "      <td>2609973</td>\n",
       "      <td>75.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ratedFloodZone</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>A</td>\n",
       "      <td>X</td>\n",
       "      <td>73</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>5.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>houseWorship</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>locationOfContents</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>3.2033096049448826</td>\n",
       "      <td>0.9722124966477267</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2609973</td>\n",
       "      <td>32.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>lowestAdjacentGrade</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-99999.9</td>\n",
       "      <td>9998.9</td>\n",
       "      <td>12377</td>\n",
       "      <td>46.202361415737954</td>\n",
       "      <td>1570.0474871017345</td>\n",
       "      <td>2.9718786479959243</td>\n",
       "      <td>6.093426200820634</td>\n",
       "      <td>10.92802779441887</td>\n",
       "      <td>2609973</td>\n",
       "      <td>81.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>lowestFloorElevation</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-6002.0</td>\n",
       "      <td>9998.0</td>\n",
       "      <td>12941</td>\n",
       "      <td>117.6063703913928</td>\n",
       "      <td>715.7659733011736</td>\n",
       "      <td>6.411312837157788</td>\n",
       "      <td>10.186463310521475</td>\n",
       "      <td>17.36810311888465</td>\n",
       "      <td>2609973</td>\n",
       "      <td>76.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>numberOfFloorsInTheInsuredBuilding</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>1.7014519472755498</td>\n",
       "      <td>0.875643030173172</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>nonProfitIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>obstructionType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>10</td>\n",
       "      <td>98</td>\n",
       "      <td>20</td>\n",
       "      <td>16.43027047007325</td>\n",
       "      <td>15.962975248145261</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>2609973</td>\n",
       "      <td>42.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>occupancyType</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>14</td>\n",
       "      <td>1.685089796470531</td>\n",
       "      <td>1.9761187054510256</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>originalConstructionDate</td>\n",
       "      <td>DATE</td>\n",
       "      <td>1492-10-12</td>\n",
       "      <td>2024-01-16</td>\n",
       "      <td>31657</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>originalNBDate</td>\n",
       "      <td>DATE</td>\n",
       "      <td>0998-09-21</td>\n",
       "      <td>2024-06-02</td>\n",
       "      <td>17485</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>amountPaidOnBuildingClaim</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-162432.16</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>1280737</td>\n",
       "      <td>31499.400109979073</td>\n",
       "      <td>65354.07711381901</td>\n",
       "      <td>2325.4413897869463</td>\n",
       "      <td>9640.335422962684</td>\n",
       "      <td>36545.16068308723</td>\n",
       "      <td>2609973</td>\n",
       "      <td>21.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>amountPaidOnContentsClaim</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-41276.32</td>\n",
       "      <td>757048.95</td>\n",
       "      <td>471239</td>\n",
       "      <td>7114.89939527643</td>\n",
       "      <td>22202.62370893087</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00022173083740329136</td>\n",
       "      <td>4838.808609433782</td>\n",
       "      <td>2609973</td>\n",
       "      <td>21.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>amountPaidOnIncreasedCostOfComplianceClaim</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-6450.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>8751</td>\n",
       "      <td>461.9391984542799</td>\n",
       "      <td>3497.7930841076013</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2609973</td>\n",
       "      <td>21.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   column_name column_type  \\\n",
       "0                                           id     VARCHAR   \n",
       "1                agricultureStructureIndicator     BOOLEAN   \n",
       "2                                     asOfDate   TIMESTAMP   \n",
       "3              basementEnclosureCrawlspaceType    SMALLINT   \n",
       "4                                  policyCount    SMALLINT   \n",
       "5                        crsClassificationCode    SMALLINT   \n",
       "6                                   dateOfLoss        DATE   \n",
       "7                    elevatedBuildingIndicator     BOOLEAN   \n",
       "8                elevationCertificateIndicator     VARCHAR   \n",
       "9                          elevationDifference      DOUBLE   \n",
       "10                          baseFloodElevation      DOUBLE   \n",
       "11                              ratedFloodZone     VARCHAR   \n",
       "12                                houseWorship     BOOLEAN   \n",
       "13                          locationOfContents    SMALLINT   \n",
       "14                         lowestAdjacentGrade      DOUBLE   \n",
       "15                        lowestFloorElevation      DOUBLE   \n",
       "16          numberOfFloorsInTheInsuredBuilding    SMALLINT   \n",
       "17                          nonProfitIndicator     BOOLEAN   \n",
       "18                             obstructionType    SMALLINT   \n",
       "19                               occupancyType    SMALLINT   \n",
       "20                    originalConstructionDate        DATE   \n",
       "21                              originalNBDate        DATE   \n",
       "22                   amountPaidOnBuildingClaim      DOUBLE   \n",
       "23                   amountPaidOnContentsClaim      DOUBLE   \n",
       "24  amountPaidOnIncreasedCostOfComplianceClaim      DOUBLE   \n",
       "\n",
       "                                     min  \\\n",
       "0   000006af-51d1-453e-8c06-5791060175ab   \n",
       "1                                  false   \n",
       "2                2019-09-19 06:12:43.388   \n",
       "3                                      0   \n",
       "4                                      1   \n",
       "5                                      1   \n",
       "6                             1978-01-01   \n",
       "7                                  false   \n",
       "8                                      1   \n",
       "9                                -9989.0   \n",
       "10                               -9999.0   \n",
       "11                                     A   \n",
       "12                                 false   \n",
       "13                                     1   \n",
       "14                              -99999.9   \n",
       "15                               -6002.0   \n",
       "16                                     1   \n",
       "17                                 false   \n",
       "18                                    10   \n",
       "19                                     1   \n",
       "20                            1492-10-12   \n",
       "21                            0998-09-21   \n",
       "22                            -162432.16   \n",
       "23                             -41276.32   \n",
       "24                               -6450.0   \n",
       "\n",
       "                                     max  approx_unique                 avg  \\\n",
       "0   fffffeef-b65f-4405-ab4e-60257523aa4f        2662059                None   \n",
       "1                                   true              2                None   \n",
       "2                 2024-05-01 20:18:36.77         425022                None   \n",
       "3                                      4              4   1.228374267022299   \n",
       "4                                   1090            398  1.2797094069555508   \n",
       "5                                     10             10   6.848428755453079   \n",
       "6                             2024-05-01          16687                None   \n",
       "7                                   true              2                None   \n",
       "8                                      E              9                None   \n",
       "9                                  998.0            370  1.2817296900477881   \n",
       "10                                9998.0          10079  123.80080997365465   \n",
       "11                                     X             73                None   \n",
       "12                                  true              2                None   \n",
       "13                                     7              7  3.2033096049448826   \n",
       "14                                9998.9          12377  46.202361415737954   \n",
       "15                                9998.0          12941   117.6063703913928   \n",
       "16                                     6              6  1.7014519472755498   \n",
       "17                                  true              2                None   \n",
       "18                                    98             20   16.43027047007325   \n",
       "19                                    19             14   1.685089796470531   \n",
       "20                            2024-01-16          31657                None   \n",
       "21                            2024-06-02          17485                None   \n",
       "22                            10000000.0        1280737  31499.400109979073   \n",
       "23                             757048.95         471239    7114.89939527643   \n",
       "24                               60000.0           8751   461.9391984542799   \n",
       "\n",
       "                   std                 q25                     q50  \\\n",
       "0                 None                None                    None   \n",
       "1                 None                None                    None   \n",
       "2                 None                None                    None   \n",
       "3   1.0566934718476721                   0                       1   \n",
       "4     6.67351428857187                   1                       1   \n",
       "5   1.5049489596950096                   5                       7   \n",
       "6                 None                None                    None   \n",
       "7                 None                None                    None   \n",
       "8                 None                None                    None   \n",
       "9   29.362656199635108                 0.0                     1.0   \n",
       "10   751.3180224953265   6.618645230395739        9.00000411190943   \n",
       "11                None                None                    None   \n",
       "12                None                None                    None   \n",
       "13  0.9722124966477267                   3                       3   \n",
       "14  1570.0474871017345  2.9718786479959243       6.093426200820634   \n",
       "15   715.7659733011736   6.411312837157788      10.186463310521475   \n",
       "16   0.875643030173172                   1                       1   \n",
       "17                None                None                    None   \n",
       "18  15.962975248145261                  10                      10   \n",
       "19  1.9761187054510256                   1                       1   \n",
       "20                None                None                    None   \n",
       "21                None                None                    None   \n",
       "22   65354.07711381901  2325.4413897869463       9640.335422962684   \n",
       "23   22202.62370893087                 0.0  0.00022173083740329136   \n",
       "24  3497.7930841076013                 0.0                     0.0   \n",
       "\n",
       "                   q75    count  null_percentage  \n",
       "0                 None  2609973             0.00  \n",
       "1                 None  2609973             0.00  \n",
       "2                 None  2609973             0.00  \n",
       "3                    2  2609973            70.18  \n",
       "4                    1  2609973             0.00  \n",
       "5                    8  2609973            82.30  \n",
       "6                 None  2609973             0.00  \n",
       "7                 None  2609973             0.00  \n",
       "8                 None  2609973            77.41  \n",
       "9   2.7745562409888866  2609973            72.86  \n",
       "10  14.032950504289476  2609973            75.68  \n",
       "11                None  2609973             5.33  \n",
       "12                None  2609973             0.00  \n",
       "13                   4  2609973            32.89  \n",
       "14   10.92802779441887  2609973            81.38  \n",
       "15   17.36810311888465  2609973            76.24  \n",
       "16                   2  2609973             0.66  \n",
       "17                None  2609973             0.00  \n",
       "18                  10  2609973            42.78  \n",
       "19                   1  2609973             0.02  \n",
       "20                None  2609973             0.01  \n",
       "21                None  2609973             0.00  \n",
       "22   36545.16068308723  2609973            21.50  \n",
       "23   4838.808609433782  2609973            21.50  \n",
       "24                 0.0  2609973            21.50  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine each column in sections because of large number of columns\n",
    "summarize_df.iloc[:25, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "9784194b",
   "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>25</th>\n",
       "      <td>postFIRMConstructionIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>rateMethod</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>1</td>\n",
       "      <td>W</td>\n",
       "      <td>22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>1.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>smallBusinessIndicatorBuilding</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>totalBuildingInsuranceCoverage</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>243903000</td>\n",
       "      <td>11951</td>\n",
       "      <td>166889.41585940993</td>\n",
       "      <td>1228597.5907276564</td>\n",
       "      <td>38979</td>\n",
       "      <td>99733</td>\n",
       "      <td>209278</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>totalContentsInsuranceCoverage</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>6000000</td>\n",
       "      <td>3048</td>\n",
       "      <td>30733.689877477143</td>\n",
       "      <td>50621.54742463355</td>\n",
       "      <td>0</td>\n",
       "      <td>11481</td>\n",
       "      <td>44532</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>yearOfLoss</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1978</td>\n",
       "      <td>2024</td>\n",
       "      <td>47</td>\n",
       "      <td>2002.1435083811211</td>\n",
       "      <td>12.501478526204501</td>\n",
       "      <td>1993</td>\n",
       "      <td>2005</td>\n",
       "      <td>2012</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>primaryResidenceIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>buildingDamageAmount</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>927700000</td>\n",
       "      <td>201765</td>\n",
       "      <td>36311.10027419284</td>\n",
       "      <td>814576.3551107972</td>\n",
       "      <td>3442</td>\n",
       "      <td>11247</td>\n",
       "      <td>40081</td>\n",
       "      <td>2609973</td>\n",
       "      <td>22.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>buildingDeductibleCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>0</td>\n",
       "      <td>H</td>\n",
       "      <td>15</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>netBuildingPaymentAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-162432.16</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>1280807</td>\n",
       "      <td>24695.035330748662</td>\n",
       "      <td>59271.29895574326</td>\n",
       "      <td>0.15364584889707653</td>\n",
       "      <td>4723.460869598978</td>\n",
       "      <td>24764.392949312303</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>buildingPropertyValue</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>9950000000</td>\n",
       "      <td>417344</td>\n",
       "      <td>7185179.093795381</td>\n",
       "      <td>200484954.57392937</td>\n",
       "      <td>60707</td>\n",
       "      <td>109667</td>\n",
       "      <td>189103</td>\n",
       "      <td>2609973</td>\n",
       "      <td>22.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>causeOfDamage</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>0</td>\n",
       "      <td>Z</td>\n",
       "      <td>17</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>1.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>condominiumCoverageTypeCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>A</td>\n",
       "      <td>U</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>1.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>contentsDamageAmount</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>19230507</td>\n",
       "      <td>102032</td>\n",
       "      <td>18359.902942019133</td>\n",
       "      <td>85635.12448811416</td>\n",
       "      <td>1634</td>\n",
       "      <td>5724</td>\n",
       "      <td>18585</td>\n",
       "      <td>2609973</td>\n",
       "      <td>59.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>contentsDeductibleCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>0</td>\n",
       "      <td>H</td>\n",
       "      <td>15</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>20.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>netContentsPaymentAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-41276.32</td>\n",
       "      <td>757048.95</td>\n",
       "      <td>471642</td>\n",
       "      <td>5563.005184249095</td>\n",
       "      <td>19864.170528635594</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2228.7933559822723</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>contentsPropertyValue</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>-1</td>\n",
       "      <td>281895323</td>\n",
       "      <td>58742</td>\n",
       "      <td>31187.93643040255</td>\n",
       "      <td>488975.74472234386</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>19516</td>\n",
       "      <td>2609973</td>\n",
       "      <td>59.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>disasterAssistanceCoverageRequired</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>0.05622307145791252</td>\n",
       "      <td>0.47619641062381596</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2609973</td>\n",
       "      <td>12.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>eventDesignationNumber</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>AL0221</td>\n",
       "      <td>WV0421</td>\n",
       "      <td>57</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>96.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>ficoNumber</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>998</td>\n",
       "      <td>733</td>\n",
       "      <td>500.7083830181456</td>\n",
       "      <td>237.2537407649024</td>\n",
       "      <td>305</td>\n",
       "      <td>593</td>\n",
       "      <td>655</td>\n",
       "      <td>2609973</td>\n",
       "      <td>33.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>floodCharacteristicsIndicator</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.8946377938925552</td>\n",
       "      <td>0.5954632949188174</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2609973</td>\n",
       "      <td>98.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>floodWaterDuration</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>0</td>\n",
       "      <td>999</td>\n",
       "      <td>261</td>\n",
       "      <td>0.8072950774434239</td>\n",
       "      <td>14.732918977650533</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2609973</td>\n",
       "      <td>6.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>floodproofedIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>floodEvent</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>2021 Mid-Spring Severe Storms</td>\n",
       "      <td>Yellowstone Flooding</td>\n",
       "      <td>166</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>28.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>iccCoverage</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>0</td>\n",
       "      <td>30000</td>\n",
       "      <td>4</td>\n",
       "      <td>27920.613055832175</td>\n",
       "      <td>4822.960980725853</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>2609973</td>\n",
       "      <td>37.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           column_name column_type  \\\n",
       "25       postFIRMConstructionIndicator     BOOLEAN   \n",
       "26                          rateMethod     VARCHAR   \n",
       "27      smallBusinessIndicatorBuilding     BOOLEAN   \n",
       "28      totalBuildingInsuranceCoverage      BIGINT   \n",
       "29      totalContentsInsuranceCoverage      BIGINT   \n",
       "30                          yearOfLoss    SMALLINT   \n",
       "31           primaryResidenceIndicator     BOOLEAN   \n",
       "32                buildingDamageAmount      BIGINT   \n",
       "33              buildingDeductibleCode     VARCHAR   \n",
       "34            netBuildingPaymentAmount      DOUBLE   \n",
       "35               buildingPropertyValue      BIGINT   \n",
       "36                       causeOfDamage     VARCHAR   \n",
       "37         condominiumCoverageTypeCode     VARCHAR   \n",
       "38                contentsDamageAmount      BIGINT   \n",
       "39              contentsDeductibleCode     VARCHAR   \n",
       "40            netContentsPaymentAmount      DOUBLE   \n",
       "41               contentsPropertyValue      BIGINT   \n",
       "42  disasterAssistanceCoverageRequired    SMALLINT   \n",
       "43              eventDesignationNumber     VARCHAR   \n",
       "44                          ficoNumber    SMALLINT   \n",
       "45       floodCharacteristicsIndicator    SMALLINT   \n",
       "46                  floodWaterDuration    SMALLINT   \n",
       "47               floodproofedIndicator     BOOLEAN   \n",
       "48                          floodEvent     VARCHAR   \n",
       "49                         iccCoverage     INTEGER   \n",
       "\n",
       "                              min                   max  approx_unique  \\\n",
       "25                          false                  true              2   \n",
       "26                              1                     W             22   \n",
       "27                          false                  true              2   \n",
       "28                              0             243903000          11951   \n",
       "29                              0               6000000           3048   \n",
       "30                           1978                  2024             47   \n",
       "31                          false                  true              2   \n",
       "32                              0             927700000         201765   \n",
       "33                              0                     H             15   \n",
       "34                     -162432.16            10000000.0        1280807   \n",
       "35                              0            9950000000         417344   \n",
       "36                              0                     Z             17   \n",
       "37                              A                     U              5   \n",
       "38                              0              19230507         102032   \n",
       "39                              0                     H             15   \n",
       "40                      -41276.32             757048.95         471642   \n",
       "41                             -1             281895323          58742   \n",
       "42                              0                     5              6   \n",
       "43                         AL0221                WV0421             57   \n",
       "44                              1                   998            733   \n",
       "45                              1                     5              5   \n",
       "46                              0                   999            261   \n",
       "47                          false                  true              2   \n",
       "48  2021 Mid-Spring Severe Storms  Yellowstone Flooding            166   \n",
       "49                              0                 30000              4   \n",
       "\n",
       "                    avg                  std                  q25  \\\n",
       "25                 None                 None                 None   \n",
       "26                 None                 None                 None   \n",
       "27                 None                 None                 None   \n",
       "28   166889.41585940993   1228597.5907276564                38979   \n",
       "29   30733.689877477143    50621.54742463355                    0   \n",
       "30   2002.1435083811211   12.501478526204501                 1993   \n",
       "31                 None                 None                 None   \n",
       "32    36311.10027419284    814576.3551107972                 3442   \n",
       "33                 None                 None                 None   \n",
       "34   24695.035330748662    59271.29895574326  0.15364584889707653   \n",
       "35    7185179.093795381   200484954.57392937                60707   \n",
       "36                 None                 None                 None   \n",
       "37                 None                 None                 None   \n",
       "38   18359.902942019133    85635.12448811416                 1634   \n",
       "39                 None                 None                 None   \n",
       "40    5563.005184249095   19864.170528635594                  0.0   \n",
       "41    31187.93643040255   488975.74472234386                    0   \n",
       "42  0.05622307145791252  0.47619641062381596                    0   \n",
       "43                 None                 None                 None   \n",
       "44    500.7083830181456    237.2537407649024                  305   \n",
       "45   1.8946377938925552   0.5954632949188174                    2   \n",
       "46   0.8072950774434239   14.732918977650533                    0   \n",
       "47                 None                 None                 None   \n",
       "48                 None                 None                 None   \n",
       "49   27920.613055832175    4822.960980725853                30000   \n",
       "\n",
       "                  q50                 q75    count  null_percentage  \n",
       "25               None                None  2609973             0.00  \n",
       "26               None                None  2609973             1.90  \n",
       "27               None                None  2609973             0.00  \n",
       "28              99733              209278  2609973             0.00  \n",
       "29              11481               44532  2609973             0.00  \n",
       "30               2005                2012  2609973             0.00  \n",
       "31               None                None  2609973             0.00  \n",
       "32              11247               40081  2609973            22.40  \n",
       "33               None                None  2609973            11.99  \n",
       "34  4723.460869598978  24764.392949312303  2609973             0.00  \n",
       "35             109667              189103  2609973            22.40  \n",
       "36               None                None  2609973             1.40  \n",
       "37               None                None  2609973             1.43  \n",
       "38               5724               18585  2609973            59.08  \n",
       "39               None                None  2609973            20.33  \n",
       "40                0.0  2228.7933559822723  2609973             0.00  \n",
       "41                  0               19516  2609973            59.08  \n",
       "42                  0                   0  2609973            12.04  \n",
       "43               None                None  2609973            96.01  \n",
       "44                593                 655  2609973            33.75  \n",
       "45                  2                   2  2609973            98.47  \n",
       "46                  0                   0  2609973             6.97  \n",
       "47               None                None  2609973             0.00  \n",
       "48               None                None  2609973            28.99  \n",
       "49              30000               30000  2609973            37.05  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summarize_df.iloc[25:50, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "df631c4b",
   "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>50</th>\n",
       "      <td>netIccPaymentAmount</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-6450.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>8708</td>\n",
       "      <td>361.25060566526923</td>\n",
       "      <td>3098.8927849140314</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>nfipRatedCommunityNumber</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>000000</td>\n",
       "      <td>999999</td>\n",
       "      <td>16288</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>nfipCommunityNumberCurrent</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>0000</td>\n",
       "      <td>815000</td>\n",
       "      <td>11987</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>72.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>nfipCommunityName</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>ABBEVILLE COUNTY *</td>\n",
       "      <td>ZUMBRO FALLS, CITY OF</td>\n",
       "      <td>9794</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>72.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>nonPaymentReasonContents</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>01</td>\n",
       "      <td>99</td>\n",
       "      <td>23</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>68.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>nonPaymentReasonBuilding</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>01</td>\n",
       "      <td>99</td>\n",
       "      <td>23</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>77.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>numberOfUnits</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>0</td>\n",
       "      <td>99999</td>\n",
       "      <td>445</td>\n",
       "      <td>1.3626484479940102</td>\n",
       "      <td>66.17856487595769</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>buildingReplacementCost</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>9999000000</td>\n",
       "      <td>443562</td>\n",
       "      <td>8613520.156327939</td>\n",
       "      <td>235672086.90846258</td>\n",
       "      <td>0</td>\n",
       "      <td>120306</td>\n",
       "      <td>223120</td>\n",
       "      <td>2609973</td>\n",
       "      <td>22.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>contentsReplacementCost</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>0</td>\n",
       "      <td>20000000</td>\n",
       "      <td>8867</td>\n",
       "      <td>2802.1066546353063</td>\n",
       "      <td>46646.036696565956</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2609973</td>\n",
       "      <td>59.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>replacementCostBasis</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>A</td>\n",
       "      <td>R</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>2609973</td>\n",
       "      <td>7.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>stateOwnedIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>waterDepth</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>-999</td>\n",
       "      <td>999</td>\n",
       "      <td>474</td>\n",
       "      <td>4.302038514059302</td>\n",
       "      <td>16.433883166615583</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2609973</td>\n",
       "      <td>8.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>floodZoneCurrent</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>A</td>\n",
       "      <td>X</td>\n",
       "      <td>62</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>73.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>buildingDescriptionCode</td>\n",
       "      <td>SMALLINT</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>18</td>\n",
       "      <td>1.335554376519114</td>\n",
       "      <td>1.4922894731712084</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2609973</td>\n",
       "      <td>64.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>rentalPropertyIndicator</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>false</td>\n",
       "      <td>true</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>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>state</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>AK</td>\n",
       "      <td>WY</td>\n",
       "      <td>57</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>reportedCity</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Currently Unavailable</td>\n",
       "      <td>Currently Unavailable</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>reportedZipCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td></td>\n",
       "      <td>99999</td>\n",
       "      <td>26228</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>countyCode</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>01001</td>\n",
       "      <td>78030</td>\n",
       "      <td>2942</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>2.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>censusTract</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>01001020100</td>\n",
       "      <td>78030961200</td>\n",
       "      <td>54666</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>5.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>censusBlockGroupFips</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>010010201001</td>\n",
       "      <td>780309612002</td>\n",
       "      <td>110152</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2609973</td>\n",
       "      <td>5.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>latitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-36.0</td>\n",
       "      <td>69.9</td>\n",
       "      <td>335</td>\n",
       "      <td>33.86958959935434</td>\n",
       "      <td>5.826147648469343</td>\n",
       "      <td>29.885555420103564</td>\n",
       "      <td>31.034309863474476</td>\n",
       "      <td>39.741329849029</td>\n",
       "      <td>2609973</td>\n",
       "      <td>1.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>longitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>-170.7</td>\n",
       "      <td>174.0</td>\n",
       "      <td>717</td>\n",
       "      <td>-86.07527425472838</td>\n",
       "      <td>11.03787203900624</td>\n",
       "      <td>-91.22763060784378</td>\n",
       "      <td>-86.95045579385058</td>\n",
       "      <td>-77.21615237901081</td>\n",
       "      <td>2609973</td>\n",
       "      <td>1.53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   column_name column_type                    min  \\\n",
       "50         netIccPaymentAmount      DOUBLE                -6450.0   \n",
       "51    nfipRatedCommunityNumber     VARCHAR                 000000   \n",
       "52  nfipCommunityNumberCurrent     VARCHAR                   0000   \n",
       "53           nfipCommunityName     VARCHAR     ABBEVILLE COUNTY *   \n",
       "54    nonPaymentReasonContents     VARCHAR                     01   \n",
       "55    nonPaymentReasonBuilding     VARCHAR                     01   \n",
       "56               numberOfUnits     INTEGER                      0   \n",
       "57     buildingReplacementCost      BIGINT                      0   \n",
       "58     contentsReplacementCost      BIGINT                      0   \n",
       "59        replacementCostBasis     VARCHAR                      A   \n",
       "60         stateOwnedIndicator     BOOLEAN                  false   \n",
       "61                  waterDepth    SMALLINT                   -999   \n",
       "62            floodZoneCurrent     VARCHAR                      A   \n",
       "63     buildingDescriptionCode    SMALLINT                      1   \n",
       "64     rentalPropertyIndicator     BOOLEAN                  false   \n",
       "65                       state     VARCHAR                     AK   \n",
       "66                reportedCity     VARCHAR  Currently Unavailable   \n",
       "67             reportedZipCode     VARCHAR                          \n",
       "68                  countyCode     VARCHAR                  01001   \n",
       "69                 censusTract     VARCHAR            01001020100   \n",
       "70        censusBlockGroupFips     VARCHAR           010010201001   \n",
       "71                    latitude      DOUBLE                  -36.0   \n",
       "72                   longitude      DOUBLE                 -170.7   \n",
       "\n",
       "                      max  approx_unique                 avg  \\\n",
       "50                60000.0           8708  361.25060566526923   \n",
       "51                 999999          16288                None   \n",
       "52                 815000          11987                None   \n",
       "53  ZUMBRO FALLS, CITY OF           9794                None   \n",
       "54                     99             23                None   \n",
       "55                     99             23                None   \n",
       "56                  99999            445  1.3626484479940102   \n",
       "57             9999000000         443562   8613520.156327939   \n",
       "58               20000000           8867  2802.1066546353063   \n",
       "59                      R              2                None   \n",
       "60                   true              2                None   \n",
       "61                    999            474   4.302038514059302   \n",
       "62                      X             62                None   \n",
       "63                     21             18   1.335554376519114   \n",
       "64                   true              2                None   \n",
       "65                     WY             57                None   \n",
       "66  Currently Unavailable              1                None   \n",
       "67                  99999          26228                None   \n",
       "68                  78030           2942                None   \n",
       "69            78030961200          54666                None   \n",
       "70           780309612002         110152                None   \n",
       "71                   69.9            335   33.86958959935434   \n",
       "72                  174.0            717  -86.07527425472838   \n",
       "\n",
       "                   std                 q25                 q50  \\\n",
       "50  3098.8927849140314                 0.0                 0.0   \n",
       "51                None                None                None   \n",
       "52                None                None                None   \n",
       "53                None                None                None   \n",
       "54                None                None                None   \n",
       "55                None                None                None   \n",
       "56   66.17856487595769                   1                   1   \n",
       "57  235672086.90846258                   0              120306   \n",
       "58  46646.036696565956                   0                   0   \n",
       "59                None                None                None   \n",
       "60                None                None                None   \n",
       "61  16.433883166615583                   0                   1   \n",
       "62                None                None                None   \n",
       "63  1.4922894731712084                   1                   1   \n",
       "64                None                None                None   \n",
       "65                None                None                None   \n",
       "66                None                None                None   \n",
       "67                None                None                None   \n",
       "68                None                None                None   \n",
       "69                None                None                None   \n",
       "70                None                None                None   \n",
       "71   5.826147648469343  29.885555420103564  31.034309863474476   \n",
       "72   11.03787203900624  -91.22763060784378  -86.95045579385058   \n",
       "\n",
       "                   q75    count  null_percentage  \n",
       "50                 0.0  2609973             0.00  \n",
       "51                None  2609973             0.00  \n",
       "52                None  2609973            72.96  \n",
       "53                None  2609973            72.43  \n",
       "54                None  2609973            68.43  \n",
       "55                None  2609973            77.62  \n",
       "56                   1  2609973             0.11  \n",
       "57              223120  2609973            22.40  \n",
       "58                   0  2609973            59.11  \n",
       "59                None  2609973             7.27  \n",
       "60                None  2609973             0.00  \n",
       "61                   2  2609973             8.50  \n",
       "62                None  2609973            73.00  \n",
       "63                   1  2609973            64.83  \n",
       "64                None  2609973             0.00  \n",
       "65                None  2609973             0.00  \n",
       "66                None  2609973             0.00  \n",
       "67                None  2609973             0.00  \n",
       "68                None  2609973             2.38  \n",
       "69                None  2609973             5.27  \n",
       "70                None  2609973             5.27  \n",
       "71     39.741329849029  2609973             1.53  \n",
       "72  -77.21615237901081  2609973             1.53  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summarize_df.iloc[50:, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "83987c2d",
   "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>null_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>floodCharacteristicsIndicator</td>\n",
       "      <td>98.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>eventDesignationNumber</td>\n",
       "      <td>96.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>crsClassificationCode</td>\n",
       "      <td>82.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>lowestAdjacentGrade</td>\n",
       "      <td>81.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>nonPaymentReasonBuilding</td>\n",
       "      <td>77.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>elevationCertificateIndicator</td>\n",
       "      <td>77.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>lowestFloorElevation</td>\n",
       "      <td>76.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>baseFloodElevation</td>\n",
       "      <td>75.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>floodZoneCurrent</td>\n",
       "      <td>73.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>nfipCommunityNumberCurrent</td>\n",
       "      <td>72.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      column_name  null_percentage\n",
       "45  floodCharacteristicsIndicator            98.47\n",
       "43         eventDesignationNumber            96.01\n",
       "5           crsClassificationCode            82.30\n",
       "14            lowestAdjacentGrade            81.38\n",
       "55       nonPaymentReasonBuilding            77.62\n",
       "8   elevationCertificateIndicator            77.41\n",
       "15           lowestFloorElevation            76.24\n",
       "10             baseFloodElevation            75.68\n",
       "62               floodZoneCurrent            73.00\n",
       "52     nfipCommunityNumberCurrent            72.96"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine columns with high null percentage\n",
    "(summarize_df\n",
    " .sort_values(by='null_percentage', ascending=False)\n",
    " .loc[:, ['column_name', 'null_percentage']]\n",
    " .head(10)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6416efa",
   "metadata": {},
   "source": [
    "## Amount Paid on Claims Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "cf6823b0",
   "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>column_name</th>\n",
       "      <th>amountPaidOnBuildingClaim</th>\n",
       "      <th>amountPaidOnContentsClaim</th>\n",
       "      <th>amountPaidOnIncreasedCostOfComplianceClaim</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-162432.16</td>\n",
       "      <td>-41276.32</td>\n",
       "      <td>-6450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>10000000.0</td>\n",
       "      <td>757048.95</td>\n",
       "      <td>60000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg</th>\n",
       "      <td>31499.0</td>\n",
       "      <td>7115.0</td>\n",
       "      <td>462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>65354.0</td>\n",
       "      <td>22203.0</td>\n",
       "      <td>3498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q25</th>\n",
       "      <td>2325.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q50</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q75</th>\n",
       "      <td>36545.0</td>\n",
       "      <td>4839.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null_percentage</th>\n",
       "      <td>21.5</td>\n",
       "      <td>21.5</td>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "column_name     amountPaidOnBuildingClaim amountPaidOnContentsClaim  \\\n",
       "min                            -162432.16                 -41276.32   \n",
       "max                            10000000.0                 757048.95   \n",
       "avg                               31499.0                    7115.0   \n",
       "std                               65354.0                   22203.0   \n",
       "q25                                2325.0                       0.0   \n",
       "q50                                9640.0                       0.0   \n",
       "q75                               36545.0                    4839.0   \n",
       "null_percentage                      21.5                      21.5   \n",
       "\n",
       "column_name     amountPaidOnIncreasedCostOfComplianceClaim  \n",
       "min                                                -6450.0  \n",
       "max                                                60000.0  \n",
       "avg                                                  462.0  \n",
       "std                                                 3498.0  \n",
       "q25                                                    0.0  \n",
       "q50                                                    0.0  \n",
       "q75                                                    0.0  \n",
       "null_percentage                                       21.5  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine summary statisitics on amount claims paid columns\n",
    "col_names = [\n",
    "    'amountPaidOnBuildingClaim',\n",
    "    'amountPaidOnContentsClaim',\n",
    "    'amountPaidOnIncreasedCostOfComplianceClaim'\n",
    "]\n",
    "\n",
    "cols = [\n",
    "    'column_name', 'min', 'max', 'avg', 'std',\n",
    "    'q25', 'q50', 'q75', 'null_percentage'\n",
    "]\n",
    "\n",
    "(summarize_df\n",
    " .loc[summarize_df['column_name'].isin(col_names), cols]\n",
    " .astype({'avg':'float', 'std':'float', 'q25':'float', 'q50':'float', 'q75':'float'})\n",
    " .round({'avg':0, 'std':0, 'q25':0, 'q50':0, 'q75':0})\n",
    " .set_index('column_name')\n",
    " .reindex(col_names)\n",
    " .T\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93abfab7",
   "metadata": {},
   "source": [
    "## Building Damage, Values, and Replacement Costs Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b9f071ab",
   "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>column_name</th>\n",
       "      <th>buildingDamageAmount</th>\n",
       "      <th>netBuildingPaymentAmount</th>\n",
       "      <th>buildingPropertyValue</th>\n",
       "      <th>buildingReplacementCost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0</td>\n",
       "      <td>-162432.16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>927700000</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>9950000000</td>\n",
       "      <td>9999000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg</th>\n",
       "      <td>36311.0</td>\n",
       "      <td>24695.0</td>\n",
       "      <td>7185179.0</td>\n",
       "      <td>8613520.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>814576.0</td>\n",
       "      <td>59271.0</td>\n",
       "      <td>200484955.0</td>\n",
       "      <td>235672087.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q25</th>\n",
       "      <td>3442.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60707.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q50</th>\n",
       "      <td>11247.0</td>\n",
       "      <td>4723.0</td>\n",
       "      <td>109667.0</td>\n",
       "      <td>120306.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q75</th>\n",
       "      <td>40081.0</td>\n",
       "      <td>24764.0</td>\n",
       "      <td>189103.0</td>\n",
       "      <td>223120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null_percentage</th>\n",
       "      <td>22.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.4</td>\n",
       "      <td>22.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "column_name     buildingDamageAmount netBuildingPaymentAmount  \\\n",
       "min                                0               -162432.16   \n",
       "max                        927700000               10000000.0   \n",
       "avg                          36311.0                  24695.0   \n",
       "std                         814576.0                  59271.0   \n",
       "q25                           3442.0                      0.0   \n",
       "q50                          11247.0                   4723.0   \n",
       "q75                          40081.0                  24764.0   \n",
       "null_percentage                 22.4                      0.0   \n",
       "\n",
       "column_name     buildingPropertyValue buildingReplacementCost  \n",
       "min                                 0                       0  \n",
       "max                        9950000000              9999000000  \n",
       "avg                         7185179.0               8613520.0  \n",
       "std                       200484955.0             235672087.0  \n",
       "q25                           60707.0                     0.0  \n",
       "q50                          109667.0                120306.0  \n",
       "q75                          189103.0                223120.0  \n",
       "null_percentage                  22.4                    22.4  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine summary statisitics on building damage amounts, property values and replacement costs columns\n",
    "col_names = [\n",
    "    'buildingDamageAmount',\n",
    "    'netBuildingPaymentAmount',\n",
    "    'buildingPropertyValue',\n",
    "    'buildingReplacementCost'\n",
    "    \n",
    "]\n",
    "\n",
    "cols = [\n",
    "    'column_name', 'min', 'max', 'avg', 'std',\n",
    "    'q25', 'q50', 'q75', 'null_percentage'\n",
    "]\n",
    "\n",
    "(summarize_df\n",
    " .loc[summarize_df['column_name'].isin(col_names), cols]\n",
    " .astype({'avg':'float', 'std':'float', 'q25':'float', 'q50':'float', 'q75':'float'})\n",
    " .round({'avg':0, 'std':0, 'q25':0, 'q50':0, 'q75':0})\n",
    " .set_index('column_name')\n",
    " .reindex(col_names)\n",
    " .T\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c56bdd9b",
   "metadata": {},
   "source": [
    "## Contents Damage, Values, and Replacement Costs Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "640d081c",
   "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>column_name</th>\n",
       "      <th>contentsDamageAmount</th>\n",
       "      <th>netContentsPaymentAmount</th>\n",
       "      <th>contentsPropertyValue</th>\n",
       "      <th>contentsReplacementCost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0</td>\n",
       "      <td>-41276.32</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>19230507</td>\n",
       "      <td>757048.95</td>\n",
       "      <td>281895323</td>\n",
       "      <td>20000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg</th>\n",
       "      <td>18360.0</td>\n",
       "      <td>5563.0</td>\n",
       "      <td>31188.0</td>\n",
       "      <td>2802.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>85635.0</td>\n",
       "      <td>19864.0</td>\n",
       "      <td>488976.0</td>\n",
       "      <td>46646.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q25</th>\n",
       "      <td>1634.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q50</th>\n",
       "      <td>5724.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q75</th>\n",
       "      <td>18585.0</td>\n",
       "      <td>2229.0</td>\n",
       "      <td>19516.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null_percentage</th>\n",
       "      <td>59.08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59.08</td>\n",
       "      <td>59.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "column_name     contentsDamageAmount netContentsPaymentAmount  \\\n",
       "min                                0                -41276.32   \n",
       "max                         19230507                757048.95   \n",
       "avg                          18360.0                   5563.0   \n",
       "std                          85635.0                  19864.0   \n",
       "q25                           1634.0                      0.0   \n",
       "q50                           5724.0                      0.0   \n",
       "q75                          18585.0                   2229.0   \n",
       "null_percentage                59.08                      0.0   \n",
       "\n",
       "column_name     contentsPropertyValue contentsReplacementCost  \n",
       "min                                -1                       0  \n",
       "max                         281895323                20000000  \n",
       "avg                           31188.0                  2802.0  \n",
       "std                          488976.0                 46646.0  \n",
       "q25                               0.0                     0.0  \n",
       "q50                               0.0                     0.0  \n",
       "q75                           19516.0                     0.0  \n",
       "null_percentage                 59.08                   59.11  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine summary statisitics on contents amounts, property values and replacement costs columns\n",
    "col_names = [\n",
    "    'contentsDamageAmount',\n",
    "    'netContentsPaymentAmount',\n",
    "    'contentsPropertyValue',\n",
    "    'contentsReplacementCost'\n",
    "    \n",
    "]\n",
    "\n",
    "cols = [\n",
    "    'column_name', 'min', 'max', 'avg', 'std',\n",
    "    'q25', 'q50', 'q75', 'null_percentage'\n",
    "]\n",
    "\n",
    "(summarize_df\n",
    " .loc[summarize_df['column_name'].isin(col_names), cols]\n",
    " .astype({'avg':'float', 'std':'float', 'q25':'float', 'q50':'float', 'q75':'float'})\n",
    " .round({'avg':0, 'std':0, 'q25':0, 'q50':0, 'q75':0})\n",
    " .set_index('column_name')\n",
    " .reindex(col_names)\n",
    " .T\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94fbd712",
   "metadata": {},
   "source": [
    "## Elevation and Water Depth Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8905cc25",
   "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>column_name</th>\n",
       "      <th>lowestAdjacentGrade</th>\n",
       "      <th>lowestFloorElevation</th>\n",
       "      <th>baseFloodElevation</th>\n",
       "      <th>elevationDifference</th>\n",
       "      <th>waterDepth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-99999.9</td>\n",
       "      <td>-6002.0</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>-9989.0</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9998.9</td>\n",
       "      <td>9998.0</td>\n",
       "      <td>9998.0</td>\n",
       "      <td>998.0</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg</th>\n",
       "      <td>46.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1570.0</td>\n",
       "      <td>716.0</td>\n",
       "      <td>751.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q25</th>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q50</th>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>q75</th>\n",
       "      <td>11.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null_percentage</th>\n",
       "      <td>81.38</td>\n",
       "      <td>76.24</td>\n",
       "      <td>75.68</td>\n",
       "      <td>72.86</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "column_name     lowestAdjacentGrade lowestFloorElevation baseFloodElevation  \\\n",
       "min                        -99999.9              -6002.0            -9999.0   \n",
       "max                          9998.9               9998.0             9998.0   \n",
       "avg                            46.0                118.0              124.0   \n",
       "std                          1570.0                716.0              751.0   \n",
       "q25                             3.0                  6.0                7.0   \n",
       "q50                             6.0                 10.0                9.0   \n",
       "q75                            11.0                 17.0               14.0   \n",
       "null_percentage               81.38                76.24              75.68   \n",
       "\n",
       "column_name     elevationDifference waterDepth  \n",
       "min                         -9989.0       -999  \n",
       "max                           998.0        999  \n",
       "avg                             1.0        4.0  \n",
       "std                            29.0       16.0  \n",
       "q25                             0.0        0.0  \n",
       "q50                             1.0        1.0  \n",
       "q75                             3.0        2.0  \n",
       "null_percentage               72.86        8.5  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine summary statisitics on elevation and water depth columns\n",
    "col_names = [\n",
    "    'lowestAdjacentGrade',\n",
    "    'lowestFloorElevation',\n",
    "    'baseFloodElevation',\n",
    "    'elevationDifference',    \n",
    "    'waterDepth'\n",
    "    \n",
    "]\n",
    "\n",
    "cols = [\n",
    "    'column_name', 'min', 'max', 'avg', 'std',\n",
    "    'q25', 'q50', 'q75', 'null_percentage'\n",
    "]\n",
    "\n",
    "(summarize_df\n",
    " .loc[summarize_df['column_name'].isin(col_names), cols]\n",
    " .astype({'avg':'float', 'std':'float', 'q25':'float', 'q50':'float', 'q75':'float'})\n",
    " .round({'avg':0, 'std':0, 'q25':0, 'q50':0, 'q75':0})\n",
    " .set_index('column_name')\n",
    " .reindex(col_names)\n",
    " .T\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e39aed71",
   "metadata": {},
   "source": [
    "# Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "beb52417",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 73 columns):\n",
      " #   Column                                      Non-Null Count  Dtype         \n",
      "---  ------                                      --------------  -----         \n",
      " 0   id                                          10 non-null     object        \n",
      " 1   agricultureStructureIndicator               10 non-null     bool          \n",
      " 2   asOfDate                                    10 non-null     datetime64[ns]\n",
      " 3   basementEnclosureCrawlspaceType             3 non-null      float64       \n",
      " 4   policyCount                                 10 non-null     int16         \n",
      " 5   crsClassificationCode                       7 non-null      float64       \n",
      " 6   dateOfLoss                                  10 non-null     datetime64[ns]\n",
      " 7   elevatedBuildingIndicator                   10 non-null     bool          \n",
      " 8   elevationCertificateIndicator               1 non-null      object        \n",
      " 9   elevationDifference                         5 non-null      float64       \n",
      " 10  baseFloodElevation                          5 non-null      float64       \n",
      " 11  ratedFloodZone                              10 non-null     object        \n",
      " 12  houseWorship                                10 non-null     bool          \n",
      " 13  locationOfContents                          6 non-null      float64       \n",
      " 14  lowestAdjacentGrade                         5 non-null      float64       \n",
      " 15  lowestFloorElevation                        5 non-null      float64       \n",
      " 16  numberOfFloorsInTheInsuredBuilding          10 non-null     int16         \n",
      " 17  nonProfitIndicator                          10 non-null     bool          \n",
      " 18  obstructionType                             3 non-null      float64       \n",
      " 19  occupancyType                               10 non-null     int16         \n",
      " 20  originalConstructionDate                    10 non-null     datetime64[ns]\n",
      " 21  originalNBDate                              10 non-null     datetime64[ns]\n",
      " 22  amountPaidOnBuildingClaim                   6 non-null      float64       \n",
      " 23  amountPaidOnContentsClaim                   6 non-null      float64       \n",
      " 24  amountPaidOnIncreasedCostOfComplianceClaim  6 non-null      float64       \n",
      " 25  postFIRMConstructionIndicator               10 non-null     bool          \n",
      " 26  rateMethod                                  10 non-null     object        \n",
      " 27  smallBusinessIndicatorBuilding              10 non-null     bool          \n",
      " 28  totalBuildingInsuranceCoverage              10 non-null     int64         \n",
      " 29  totalContentsInsuranceCoverage              10 non-null     int64         \n",
      " 30  yearOfLoss                                  10 non-null     int16         \n",
      " 31  primaryResidenceIndicator                   10 non-null     bool          \n",
      " 32  buildingDamageAmount                        7 non-null      float64       \n",
      " 33  buildingDeductibleCode                      10 non-null     object        \n",
      " 34  netBuildingPaymentAmount                    10 non-null     float64       \n",
      " 35  buildingPropertyValue                       7 non-null      float64       \n",
      " 36  causeOfDamage                               10 non-null     object        \n",
      " 37  condominiumCoverageTypeCode                 10 non-null     object        \n",
      " 38  contentsDamageAmount                        4 non-null      float64       \n",
      " 39  contentsDeductibleCode                      9 non-null      object        \n",
      " 40  netContentsPaymentAmount                    10 non-null     float64       \n",
      " 41  contentsPropertyValue                       4 non-null      float64       \n",
      " 42  disasterAssistanceCoverageRequired          9 non-null      float64       \n",
      " 43  eventDesignationNumber                      2 non-null      object        \n",
      " 44  ficoNumber                                  3 non-null      float64       \n",
      " 45  floodCharacteristicsIndicator               0 non-null      float64       \n",
      " 46  floodWaterDuration                          7 non-null      float64       \n",
      " 47  floodproofedIndicator                       10 non-null     bool          \n",
      " 48  floodEvent                                  5 non-null      object        \n",
      " 49  iccCoverage                                 9 non-null      float64       \n",
      " 50  netIccPaymentAmount                         10 non-null     float64       \n",
      " 51  nfipRatedCommunityNumber                    10 non-null     object        \n",
      " 52  nfipCommunityNumberCurrent                  10 non-null     object        \n",
      " 53  nfipCommunityName                           8 non-null      object        \n",
      " 54  nonPaymentReasonContents                    7 non-null      object        \n",
      " 55  nonPaymentReasonBuilding                    4 non-null      object        \n",
      " 56  numberOfUnits                               10 non-null     int32         \n",
      " 57  buildingReplacementCost                     7 non-null      float64       \n",
      " 58  contentsReplacementCost                     4 non-null      float64       \n",
      " 59  replacementCostBasis                        10 non-null     object        \n",
      " 60  stateOwnedIndicator                         10 non-null     bool          \n",
      " 61  waterDepth                                  9 non-null      float64       \n",
      " 62  floodZoneCurrent                            9 non-null      object        \n",
      " 63  buildingDescriptionCode                     8 non-null      float64       \n",
      " 64  rentalPropertyIndicator                     10 non-null     bool          \n",
      " 65  state                                       10 non-null     object        \n",
      " 66  reportedCity                                10 non-null     object        \n",
      " 67  reportedZipCode                             10 non-null     object        \n",
      " 68  countyCode                                  10 non-null     object        \n",
      " 69  censusTract                                 10 non-null     object        \n",
      " 70  censusBlockGroupFips                        10 non-null     object        \n",
      " 71  latitude                                    10 non-null     float64       \n",
      " 72  longitude                                   10 non-null     float64       \n",
      "dtypes: bool(10), datetime64[ns](4), float64(29), int16(4), int32(1), int64(2), object(23)\n",
      "memory usage: 4.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# preview column names to use for Analysis section\n",
    "(con\n",
    " .sql(\"SELECT * FROM claims LIMIT 10\")\n",
    " .df()\n",
    " .info()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91fb68cf",
   "metadata": {},
   "source": [
    "## Total Claims"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "063a47ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│    int64    │     int64     │     int64     │             int64              │\n",
       "├─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ 80057600349 │   64534521973 │   14576678596 │                      946399781 │\n",
       "└─────────────┴───────────────┴───────────────┴────────────────────────────────┘"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim, \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64d62770",
   "metadata": {},
   "source": [
    "## Total Claims by State"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "63b6646f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│  state  │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│ varchar │    int64    │     int64     │     int64     │             int64              │\n",
       "├─────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ LA      │ 20838923032 │   16431635833 │    4129930893 │                      277356306 │\n",
       "│ TX      │ 17083042210 │   13136650534 │    3888589851 │                       57801825 │\n",
       "│ FL      │ 11166770151 │    9458190896 │    1671373622 │                       37205633 │\n",
       "│ NJ      │  6465642998 │    5345477364 │     869804214 │                      250361420 │\n",
       "│ NY      │  5714558618 │    4944383614 │     717516208 │                       52658796 │\n",
       "│ MS      │  3121617364 │    2385247785 │     675466706 │                       60902873 │\n",
       "│ NC      │  2002988094 │    1692155613 │     273870198 │                       36962283 │\n",
       "│ PA      │  1419906883 │    1114477846 │     290272784 │                       15156252 │\n",
       "│ AL      │  1191515402 │     966310836 │     212332206 │                       12872360 │\n",
       "│ SC      │  1009713293 │     875828419 │     130140072 │                        3744802 │\n",
       "│ MO      │   963545406 │     715478612 │     229978779 │                       18088015 │\n",
       "│ VA      │   756461546 │     623389586 │     110752992 │                       22318968 │\n",
       "│ CA      │   730184077 │     626410824 │     102706955 │                        1066297 │\n",
       "│ IL      │   585875434 │     489729066 │      80920511 │                       15225858 │\n",
       "│ CT      │   547188311 │     471882777 │      65823933 │                        9481601 │\n",
       "│ GA      │   467141817 │     390502339 │      73912996 │                        2726482 │\n",
       "│ TN      │   444245690 │     365026286 │      76097749 │                        3121656 │\n",
       "│ MA      │   437239680 │     377537795 │      58452296 │                        1249589 │\n",
       "│ KY      │   411975361 │     332630514 │      76430803 │                        2914043 │\n",
       "│ WV      │   363852807 │     269526107 │      91908450 │                        2418250 │\n",
       "├─────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 20 rows                                                                      5 columns │\n",
       "└────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        state,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim, \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02c28c0b",
   "metadata": {},
   "source": [
    "## Total Claims by State for Fiscal Year 2024"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c5d97287",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│  state  │ TotalClaim │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│ varchar │   int64    │     int64     │     int64     │             int64              │\n",
       "├─────────┼────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ ME      │   28553103 │      26108035 │       2435049 │                          10019 │\n",
       "│ NY      │   23722092 │      21998415 │       1723677 │                              0 │\n",
       "│ FL      │   23229815 │      21388638 │       1841177 │                              0 │\n",
       "│ CA      │   21744654 │      20373883 │       1370772 │                              0 │\n",
       "│ NJ      │   19296081 │      17150204 │       2145877 │                              0 │\n",
       "│ RI      │    8546350 │       8129389 │        416960 │                              0 │\n",
       "│ SC      │    8444517 │       7837273 │        607244 │                              0 │\n",
       "│ NH      │    7681732 │       7327507 │        354226 │                              0 │\n",
       "│ TX      │    6479939 │       5251420 │       1228519 │                              0 │\n",
       "│ MD      │    6051024 │       5639316 │        411708 │                              0 │\n",
       "│ MA      │    5775113 │       5491311 │        283802 │                              0 │\n",
       "│ LA      │    5629311 │       5133057 │        496255 │                              0 │\n",
       "│ NC      │    2432483 │       2288067 │        144416 │                              0 │\n",
       "│ PA      │    2061749 │       1950802 │        110947 │                              0 │\n",
       "│ CT      │    1924172 │       1706376 │        217795 │                              0 │\n",
       "│ WA      │    1342499 │       1292909 │         49590 │                              0 │\n",
       "│ AL      │    1053302 │        947418 │        105884 │                              0 │\n",
       "│ GA      │     897460 │        852758 │         44702 │                              0 │\n",
       "│ AR      │     775074 │        701645 │         73428 │                              0 │\n",
       "│ VT      │     652469 │        643033 │          9437 │                              0 │\n",
       "├─────────┴────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 20 rows                                                                     5 columns │\n",
       "└───────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        state, \n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    WHERE dateOfLoss between '2023-10-01' AND '2024-09-30'\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14e9840c",
   "metadata": {},
   "source": [
    "## Total Claims by County"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5da1d09e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────┬────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│  state  │ countyCode │ TotalClaim │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│ varchar │  varchar   │   int64    │     int64     │     int64     │             int64              │\n",
       "├─────────┼────────────┼────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ TX      │ 48201      │ 8705456456 │    6756752295 │    1928687883 │                       20016278 │\n",
       "│ LA      │ 22071      │ 7294446959 │    5890983538 │    1292969906 │                      110493515 │\n",
       "│ LA      │ 22051      │ 3527668438 │    2619027797 │     847127476 │                       61513166 │\n",
       "│ FL      │ 12071      │ 3333790855 │    2975824827 │     356154969 │                        1811059 │\n",
       "│ NJ      │ 34029      │ 2607850866 │    2160793243 │     275246922 │                      171810701 │\n",
       "│ TX      │ 48167      │ 2456431694 │    1887282122 │     548178802 │                       20970769 │\n",
       "│ NY      │ 36059      │ 2279190677 │    1972284395 │     290167645 │                       16738636 │\n",
       "│ LA      │ 22087      │ 2245081015 │    1680089814 │     553956976 │                       11034225 │\n",
       "│ LA      │ 22103      │ 1773207463 │    1375977130 │     382955463 │                       14274869 │\n",
       "│ LA      │ 22033      │ 1311376784 │    1140070894 │     169389043 │                        1916847 │\n",
       "│ MS      │ 28047      │ 1286614181 │     982204061 │     283679372 │                       20730748 │\n",
       "│ TX      │ 48245      │ 1186864130 │     876402239 │     308136117 │                        2325774 │\n",
       "│ NY      │ 36103      │ 1098729384 │     964915186 │     119390483 │                       14423716 │\n",
       "│ TX      │ 48361      │ 1041961019 │     739725425 │     301425692 │                         809902 │\n",
       "│ FL      │ 12021      │  945558908 │     830752639 │     113985936 │                         820333 │\n",
       "│ NJ      │ 34025      │  934764354 │     798611336 │      96549312 │                       39603706 │\n",
       "│ LA      │ 22063      │  834850272 │     716108929 │     113396985 │                        5344358 │\n",
       "│ FL      │ 12086      │  797891472 │     621424622 │     176466850 │                              0 │\n",
       "│ MS      │ 28045      │  744282580 │     556662677 │     168197263 │                       19422640 │\n",
       "│ FL      │ 12033      │  731705851 │     596686781 │     126282897 │                        8736173 │\n",
       "├─────────┴────────────┴────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 20 rows                                                                                  6 columns │\n",
       "└────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        state,\n",
    "        countyCode,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1, 2\n",
    "    ORDER BY 3 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "227b6ea5",
   "metadata": {},
   "source": [
    "## Total Claims by Flood Event"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f2c0bd57",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────────┬────────────┬─────────────┬───────────────┬───────────────┬───────────┐\n",
       "│        floodEvent        │ yearOfLoss │ TotalClaim  │ BuildingClaim │ ContentsClaim │ ICCClaim  │\n",
       "│         varchar          │   int16    │    int64    │     int64     │     int64     │   int64   │\n",
       "├──────────────────────────┼────────────┼─────────────┼───────────────┼───────────────┼───────────┤\n",
       "│ Hurricane Katrina        │       2005 │ 16261697056 │   12659081935 │    3360020221 │ 242594900 │\n",
       "│ Hurricane Harvey         │       2017 │  9055446500 │    6925357028 │    2115077279 │  15012193 │\n",
       "│ Hurricane Sandy          │       2012 │  8956175518 │    7707763973 │     951584858 │ 296826687 │\n",
       "│ Hurricane Ian            │       2022 │  4653622925 │    4132266664 │     519501586 │   1854675 │\n",
       "│ Hurricane Ike            │       2008 │  2702511916 │    2073801567 │     577791589 │  50918760 │\n",
       "│ Mid-summer severe storms │       2016 │  2533368883 │    2175216439 │     347263588 │  10888856 │\n",
       "│ Hurricane Irene          │       2011 │  1347399996 │    1139897981 │     183189121 │  24312894 │\n",
       "│ Hurricane Ida            │       2021 │  1344623451 │    1114341888 │     229231028 │   1050536 │\n",
       "│ Hurricane Ivan           │       2004 │  1325419294 │    1083795424 │     221959720 │  19664150 │\n",
       "│ Hurricane Irma           │       2017 │  1114603478 │     945824454 │     162095015 │   6684008 │\n",
       "│ Tropical Storm Allison   │       2001 │  1104979705 │     820333554 │     279280479 │   5365672 │\n",
       "│ Tropical Storm Imelda    │       2019 │   752451388 │     589796293 │     161686929 │    968166 │\n",
       "│ Hurricane Florence       │       2018 │   743318839 │     630705096 │     104817034 │   7796710 │\n",
       "│ Flooding                 │       1995 │   731729839 │     554632707 │     177097132 │         0 │\n",
       "│ Hurricane Matthew        │       2016 │   662071059 │     563918087 │      92582081 │   5570891 │\n",
       "│ Hurricane Isaac          │       2012 │   564085634 │     416257051 │     138859651 │   8968932 │\n",
       "│ Hurricane Isabel         │       2003 │   500076497 │     408652888 │      59803277 │  31620332 │\n",
       "│ Early summer storms      │       2015 │   487749123 │     393750947 │      91265985 │   2732190 │\n",
       "│ Spring severe storms     │       2016 │   472881300 │     389316635 │      82963197 │    601467 │\n",
       "│ Hurricane Rita           │       2005 │   466353815 │     347426098 │      85449587 │  33478130 │\n",
       "├──────────────────────────┴────────────┴─────────────┴───────────────┴───────────────┴───────────┤\n",
       "│ 20 rows                                                                               6 columns │\n",
       "└─────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        floodEvent,\n",
    "        yearOfLoss,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS ICCClaim\n",
    "    FROM claims\n",
    "    WHERE floodEvent NOT NULL\n",
    "    GROUP BY 1, 2\n",
    "    ORDER BY 3 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "552248d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────────┬────────────┬─────────┬─────────────┬───────────────┬───────────────┬───────────┐\n",
       "│        floodEvent        │ yearOfLoss │  state  │ TotalClaim  │ BuildingClaim │ ContentsClaim │ ICCClaim  │\n",
       "│         varchar          │   int16    │ varchar │    int64    │     int64     │     int64     │   int64   │\n",
       "├──────────────────────────┼────────────┼─────────┼─────────────┼───────────────┼───────────────┼───────────┤\n",
       "│ Hurricane Katrina        │       2005 │ LA      │ 13347164644 │   10441743931 │    2721451049 │ 183969663 │\n",
       "│ Hurricane Harvey         │       2017 │ TX      │  9040104864 │    6912536831 │    2112555840 │  15012193 │\n",
       "│ Hurricane Ian            │       2022 │ FL      │  4608058052 │    4090976598 │     515346779 │   1734675 │\n",
       "│ Hurricane Sandy          │       2012 │ NJ      │  4372117786 │    3673312870 │     457640033 │ 241164883 │\n",
       "│ Hurricane Sandy          │       2012 │ NY      │  4213915671 │    3702363706 │     465358598 │  46193367 │\n",
       "│ Mid-summer severe storms │       2016 │ LA      │  2533368883 │    2175216439 │     347263588 │  10888856 │\n",
       "│ Hurricane Katrina        │       2005 │ MS      │  2521512578 │    1910623703 │     558996059 │  51892816 │\n",
       "│ Hurricane Ike            │       2008 │ TX      │  2230307106 │    1723323896 │     481446923 │  25536286 │\n",
       "│ Hurricane Irma           │       2017 │ FL      │   985516496 │     834444630 │     145099872 │   5971994 │\n",
       "│ Tropical Storm Allison   │       2001 │ TX      │   978096837 │     723017851 │     250198245 │   4880741 │\n",
       "│ Hurricane Ivan           │       2004 │ FL      │   966741463 │     768189097 │     182722004 │  15830361 │\n",
       "│ Tropical Storm Imelda    │       2019 │ TX      │   752451388 │     589796293 │     161686929 │    968166 │\n",
       "│ Hurricane Ida            │       2021 │ LA      │   689706209 │     558289389 │     130608866 │    807954 │\n",
       "│ Hurricane Florence       │       2018 │ NC      │   637152411 │     541223398 │      89079169 │   6849844 │\n",
       "│ Hurricane Irene          │       2011 │ NJ      │   599488993 │     497230608 │      96994208 │   5264178 │\n",
       "│ Flooding                 │       1995 │ LA      │   594357775 │     442113752 │     152244023 │         0 │\n",
       "│ Hurricane Isaac          │       2012 │ LA      │   513274540 │     375382324 │     129362462 │   8529754 │\n",
       "│ Spring severe storms     │       2016 │ TX      │   472718247 │     389154552 │      82962228 │    601467 │\n",
       "│ Early summer storms      │       2015 │ TX      │   472278409 │     380339421 │      89255337 │   2683650 │\n",
       "│ Hurricane Rita           │       2005 │ LA      │   415238845 │     308566580 │      73331784 │  33340481 │\n",
       "├──────────────────────────┴────────────┴─────────┴─────────────┴───────────────┴───────────────┴───────────┤\n",
       "│ 20 rows                                                                                         7 columns │\n",
       "└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        floodEvent,\n",
    "        yearOfLoss,\n",
    "        state,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS ICCClaim\n",
    "    FROM claims\n",
    "    WHERE floodEvent NOT NULL\n",
    "    GROUP BY 1, 2, 3\n",
    "    ORDER BY 4 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3012607e",
   "metadata": {},
   "source": [
    "## Total Claims by Flood Zone\n",
    "*Formerly called floodZone. NFIP Flood Zone derived from the Flood Insurance Rate Map (FIRM) used to rate the insured property. A - Special Flood with no Base Flood Elevation on FIRM; AE, A1-A30 - Special Flood with Base Flood Elevation on FIRM; A99 - Special Flood with Protection Zone; AH, AHB* - Special Flood with Shallow Ponding; AO, AOB* - Special Flood with Sheet Flow; X, B - Moderate Flood from primary water source. Pockets of areas subject to drainage problems; X, C - Minimal Flood from primary water source. Pockets of areas subject to drainage problems; D - Possible Flood; V - Velocity Flood with no Base Flood Elevation on FIRM; VE, V1-V30 - Velocity Flood with Base Flood Elevation on FIRM; AE, VE, X - New zone designations used on new maps starting January 1, 1986, in lieu of A1-A30, V1-V30, and B and C; AR - A Special Flood Hazard Area that results from the decertification of a previously accredited flood protection system that is determined to be in the process of being restored to provide base flood protection; AR Dual Zones - (AR/AE, AR/A1-A30, AR/AH, AR/AO, AR/A) Areas subject to flooding from failure of the flood protection system (Zone AR) which also overlap an existing Special Flood Hazard Area as a dual zone; *AHB, AOB, ARE, ARH, ARO, and ARA are not risk zones shown on a map, but are acceptable values for rating purposes*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "21a9b7e5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ ratedFloodZone │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│    varchar     │    int64    │     int64     │     int64     │             int64              │\n",
       "├────────────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ AE             │ 35111157725 │   29248455727 │    5349491157 │                      513210841 │\n",
       "│ X              │ 13293684798 │   10258338304 │    3006893246 │                       28453247 │\n",
       "│ B              │  3826003058 │    2835227048 │     971988985 │                       18787025 │\n",
       "│ A              │  3097345563 │    2457942866 │     602004885 │                       37397812 │\n",
       "│ C              │  2851078461 │    2117465674 │     723537437 │                       10075351 │\n",
       "│ A04            │  2185900631 │    1800894098 │     364396806 │                       20609726 │\n",
       "│ A03            │  1651280337 │    1338059437 │     284761164 │                       28459737 │\n",
       "│ VE             │  1583170865 │    1406989832 │     158281391 │                       17899641 │\n",
       "│ A01            │  1526482410 │    1189226057 │     321080258 │                       16176094 │\n",
       "│ A08            │  1455686252 │    1185051780 │     237429765 │                       33204708 │\n",
       "│ A05            │  1275442317 │    1059644329 │     186794374 │                       29003615 │\n",
       "│ A10            │  1272815696 │     993289446 │     260644398 │                       18881852 │\n",
       "│ A07            │  1150089917 │     939360121 │     183868487 │                       26861309 │\n",
       "│ A06            │  1045038241 │     844582443 │     171594331 │                       28861467 │\n",
       "│ A02            │   966287720 │     764261755 │     190298815 │                       11727150 │\n",
       "│ A11            │   834722264 │     666423998 │     154405220 │                       13893046 │\n",
       "│ A09            │   793846926 │     608748198 │     167178365 │                       17920363 │\n",
       "│ A14            │   702936065 │     539533525 │     153185936 │                       10216604 │\n",
       "│ NULL           │   696451058 │     491356614 │     205020275 │                          74169 │\n",
       "│ AH             │   511034551 │     426383777 │      84164573 │                         486201 │\n",
       "├────────────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 20 rows                                                                             5 columns │\n",
       "└───────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        ratedFloodZone as ratedFloodZone,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41bad347",
   "metadata": {},
   "source": [
    "## Total Claims by Occupancy Type\n",
    "*Code indicating the use and occupancy type of the insured structure. Note, 2-digit codes are for Risk Rating 2.0 policies. 1=single family residence; 2 = 2 to 4 unit residential building; 3 = residential building with more than 4 units; 4 = Non-residential building; 6 = Non Residential - Business; 11 = Single-family residential building with the exception of a mobile home or a single residential unit within a multi unit building; 12 = A residential non-condo building with 2, 3, or 4 units seeking insurance on all units; 13 = A residential non-condo building with 5 or more units seeking insurance on all units; 14 = Residential mobile/manufactured home; 15 = Residential condo association seeking coverage on a building with one or more units; 16 = Single residential unit within a multi-unit building; 17 = Non-residential mobile/manufactured home; 18 = A non-residential building; 19 = a non-residential unit within a multi-unit building;*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "78eead45",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ occupancyType │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│     int16     │    int64    │     int64     │     int64     │             int64              │\n",
       "├───────────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│             1 │ 58068945128 │   46435050309 │   10743686447 │                      890208372 │\n",
       "│             4 │  6399065992 │    4292302635 │    2095965716 │                       10797641 │\n",
       "│             3 │  4374189293 │    4228633027 │     138249314 │                        7306952 │\n",
       "│             2 │  3730087287 │    3407331381 │     287664029 │                       35091878 │\n",
       "│             6 │  3358494979 │    2511223292 │     845669820 │                        1601866 │\n",
       "│            11 │  2491648290 │    2158598189 │     331902381 │                        1147721 │\n",
       "│            15 │   861271847 │     857328256 │       3943591 │                              0 │\n",
       "│            18 │   422363515 │     346375452 │      75958063 │                          30000 │\n",
       "│            12 │   142853560 │     136584210 │       6238822 │                          30528 │\n",
       "│            13 │   100451788 │      98233246 │       2218542 │                              0 │\n",
       "│            14 │    55279440 │      46999138 │       8117603 │                         162699 │\n",
       "│            16 │    47018012 │      13018812 │      33977076 │                          22124 │\n",
       "│          NULL │     3516572 │       2450945 │       1065627 │                              0 │\n",
       "│            19 │     2009166 │             0 │       2009166 │                              0 │\n",
       "│            17 │      405481 │        393081 │         12400 │                              0 │\n",
       "├───────────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 15 rows                                                                            5 columns │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        occupancyType,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce826ed9",
   "metadata": {},
   "source": [
    "## Total Claims by Flood Zone and Occupancy Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7b1c81d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────────┬───────────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ ratedFloodZone │ occupancyType │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│    varchar     │     int16     │    int64    │     int64     │     int64     │             int64              │\n",
       "├────────────────┼───────────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ AE             │             1 │ 24280260327 │   19986111836 │    3803579791 │                      490568700 │\n",
       "│ X              │             1 │ 10707127507 │    8229644367 │    2450235094 │                       27248046 │\n",
       "│ B              │             1 │  2959535823 │    2183555532 │     758744816 │                       17235475 │\n",
       "│ A              │             1 │  2380346133 │    1945172272 │     399071426 │                       36102435 │\n",
       "│ AE             │             3 │  2150998205 │    2096708194 │      52374043 │                        1915968 │\n",
       "│ C              │             1 │  2096573154 │    1574319538 │     512510502 │                        9743114 │\n",
       "│ AE             │             6 │  2090475275 │    1581997817 │     507345227 │                        1132230 │\n",
       "│ AE             │            11 │  2045795150 │    1769248737 │     275458762 │                        1087650 │\n",
       "│ AE             │             4 │  1867369103 │    1336508870 │     527483788 │                        3376445 │\n",
       "│ A04            │             1 │  1644324118 │    1344636021 │     283100829 │                       16587268 │\n",
       "│ AE             │             2 │  1504094905 │    1399727821 │      89460462 │                       14906622 │\n",
       "│ A03            │             1 │  1191836337 │     951329878 │     216573952 │                       23932508 │\n",
       "│ A01            │             1 │  1142138794 │     864298719 │     263175755 │                       14664320 │\n",
       "│ A10            │             1 │  1067579159 │     831005105 │     218609185 │                       17964869 │\n",
       "│ A08            │             1 │  1026301538 │     832876453 │     163117070 │                       30308015 │\n",
       "│ A05            │             1 │   934210343 │     773560056 │     133360410 │                       27289877 │\n",
       "│ A07            │             1 │   841646865 │     686917214 │     130147916 │                       24581735 │\n",
       "│ A06            │             1 │   814922669 │     664459042 │     122720283 │                       27743344 │\n",
       "│ VE             │             1 │   766499055 │     661527667 │      88485755 │                       16485634 │\n",
       "│ A02            │             1 │   756655785 │     596097628 │     150933027 │                        9625131 │\n",
       "├────────────────┴───────────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 20 rows                                                                                             6 columns │\n",
       "└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        ratedFloodZone,\n",
    "        occupancyType,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1, 2\n",
    "    ORDER BY 3 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e5ffd65",
   "metadata": {},
   "source": [
    "## Total Claims by Basement Enclosure Crawlspace Type\n",
    "*Basement is defined for purposes of the NFIP as any level or story which has its floor subgrade on all sides. Basement structure values are as follows: 0 - None; 1 - Finished Basement/Enclosure; 2 - Unfinished Basement/Enclosure; 3 - Crawlspace; 4 - Subgrade Crawlspace;*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "4cbe341e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────────────────────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ basementEnclosureCrawlspaceType │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│              int16              │    int64    │     int64     │     int64     │             int64              │\n",
       "├─────────────────────────────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│                            NULL │ 55401524778 │   43679431564 │   10971592499 │                      750500715 │\n",
       "│                               0 │ 13914353649 │   11440401898 │    2409318309 │                       64633442 │\n",
       "│                               2 │  6000512303 │    5288346590 │     619947436 │                       92218277 │\n",
       "│                               1 │  3397191736 │    2939798687 │     439264817 │                       18128232 │\n",
       "│                               4 │  1344017882 │    1186543234 │     136555535 │                       20919114 │\n",
       "└─────────────────────────────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┘"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        basementEnclosureCrawlspaceType,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e6ceb03",
   "metadata": {},
   "source": [
    "## Total Claims by Cause of Damage\n",
    "*Indicates the method by which the insured's property and contents were damaged. Legal values (value : description): 0 : Other causes; 1 : Tidal water overflow; 2 : Stream, river, or lake overflow; 3 : Alluvial fan overflow; 4 : Accumulation of rainfall or snowmelt; 7 : Erosion-demolition; 8 : Erosion-removal; 9 : Earth movement, landslide, land subsidence, sinkholes, etc.; A : Closed basin lake; B : Expedited claim handling process without site inspection; C : Expedited claim handling process follow-up site inspection; D : Expedited claim handling process by Adjusting Process Pilot Program (Remote Adjustment); NOTE: Due to certain provisions of the Upton Jones Amendment to the National Flood Insurance Act, cause of loss codes 7 and 8 may be used only if the date of loss is prior to September 23, 1995. More than one cause of loss code can be selected. For example, you may select 2 (stream, river, or lake overflow) and D (remote adjustment) on a single claim, or any combination of letters/numbers as appropriate*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "3e179851",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ causeOfDamage │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│    varchar    │    int64    │     int64     │     int64     │             int64              │\n",
       "├───────────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ 4             │ 31961230504 │   25625037178 │    6055472130 │                      280721196 │\n",
       "│ 1             │ 21036248351 │   17233069923 │    3436391995 │                      366786433 │\n",
       "│ 2             │ 18384564348 │   14699663364 │    3469240983 │                      215660001 │\n",
       "│ 0             │  6142937074 │    4972163141 │    1102060910 │                       68713023 │\n",
       "│ B             │  2101842424 │    1650532490 │     440000279 │                       11309655 │\n",
       "│ 3             │   235661209 │     184005406 │      49416789 │                        2239014 │\n",
       "│ NULL          │    52776078 │      49018049 │       3686604 │                          71424 │\n",
       "│ C             │    45993131 │      36795601 │       9158730 │                          38800 │\n",
       "│ A             │    40244201 │      33297375 │       6465500 │                         481325 │\n",
       "│ 7             │    22755288 │      22337141 │        320587 │                          97560 │\n",
       "│ D             │    17644845 │      14722518 │       2794027 │                         128300 │\n",
       "│ 9             │    11890489 │      10097605 │       1639835 │                         153050 │\n",
       "│ 8             │     3765783 │       3735555 │         30228 │                              0 │\n",
       "│ 5             │       46625 │         46625 │             0 │                              0 │\n",
       "│ 6             │        NULL │          NULL │          NULL │                           NULL │\n",
       "│ @             │        NULL │          NULL │          NULL │                           NULL │\n",
       "│ N             │        NULL │          NULL │          NULL │                           NULL │\n",
       "│ Z             │        NULL │          NULL │          NULL │                           NULL │\n",
       "├───────────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 18 rows                                                                            5 columns │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        causeOfDamage,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50e55a2a",
   "metadata": {},
   "source": [
    "## Total Claims by Building Description\n",
    "*Indicates the description of the use of the insured building. Legal values (value : description): 01 : Main House; 02 : Detached Guest House; 03 : Detached Garage; 04 : Agricultural Building; 05 : Warehouse; 06 : Pool House, Clubhouse, Recreation Building; 07 : Tool/Storage Shed; 08 : Other; 09 : Barn; 10 : Apartment Building; 11 : Apartment - Unit; 12 : Cooperative Building; 13 : Cooperative - Unit; 14 : Commercial Building; 15 : Condominium (Entire Building); 16 : Condominium - Unit; 17 : House of Worship; 18 : Manufactured (Mobile) Home; 19 : Travel Trailer; 20 : Townhouse/Rowhouse;*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "8f1a2116",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────────────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ buildingDescriptionCode │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│          int16          │    int64    │     int64     │     int64     │             int64              │\n",
       "├─────────────────────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│                       1 │ 38774742941 │   32254938612 │    6101059138 │                      418745191 │\n",
       "│                    NULL │ 38024521753 │   29673165989 │    7827229024 │                      524126740 │\n",
       "│                       8 │  2710101630 │    2157722123 │     549626076 │                        2753431 │\n",
       "│                       5 │   194328201 │     125807067 │      68521134 │                              0 │\n",
       "│                      10 │   185624947 │     184269467 │       1355480 │                              0 │\n",
       "│                       2 │    57796789 │      48927167 │       8203633 │                         665989 │\n",
       "│                       6 │    55313582 │      47174109 │       8085420 │                          54053 │\n",
       "│                       7 │    13895060 │       9770718 │       4095639 │                          28703 │\n",
       "│                       3 │    13032758 │      11372275 │       1647609 │                          12875 │\n",
       "│                      12 │     8144532 │       6958488 │       1173244 │                          12800 │\n",
       "│                      11 │     8094693 │       5473735 │       2620958 │                              0 │\n",
       "│                       4 │     7078437 │       5290266 │       1788171 │                              0 │\n",
       "│                       9 │     2837464 │       2264331 │        573133 │                              0 │\n",
       "│                      13 │     1341332 │        737089 │        604243 │                              0 │\n",
       "│                      15 │      396809 │        323146 │         73663 │                              0 │\n",
       "│                      14 │      257190 │        255159 │          2031 │                              0 │\n",
       "│                      18 │       70000 │         50000 │         20000 │                              0 │\n",
       "│                      16 │       16764 │         16764 │             0 │                              0 │\n",
       "│                      21 │        5467 │          5467 │             0 │                              0 │\n",
       "├─────────────────────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┤\n",
       "│ 19 rows                                                                                      5 columns │\n",
       "└────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        buildingDescriptionCode,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "723c44a8",
   "metadata": {},
   "source": [
    "## Total Claims by Rental Property Indicator\n",
    "*Indicates if the property is a rental property. Yes is indicated with either a 'true' or '1'. No is indicated with either a 'false' or '0'. NOTE: A rental property is a property from which the owner receives payment from the occupant(s), known as tenants, in return for occupying or using the property. Rental properties may be either residential or commercial.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "d62db375",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────────────────┬─────────────┬───────────────┬───────────────┬────────────────────────────────┐\n",
       "│ rentalPropertyIndicator │ TotalClaim  │ BuildingClaim │ ContentsClaim │ IncreasedCostOfComplianceClaim │\n",
       "│         boolean         │    int64    │     int64     │     int64     │             int64              │\n",
       "├─────────────────────────┼─────────────┼───────────────┼───────────────┼────────────────────────────────┤\n",
       "│ false                   │ 78673563989 │   63312641722 │   14417199537 │                      943722731 │\n",
       "│ true                    │  1384036360 │    1221880251 │     159479059 │                        2677050 │\n",
       "└─────────────────────────┴─────────────┴───────────────┴───────────────┴────────────────────────────────┘"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        rentalPropertyIndicator,\n",
    "        ROUND(\n",
    "            SUM(amountPaidOnBuildingClaim)\n",
    "            + SUM(amountPaidOnContentsClaim)\n",
    "            + SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS TotalClaim,  \n",
    "        ROUND(SUM(amountPaidOnBuildingClaim), 0)::INT64 AS BuildingClaim,\n",
    "        ROUND(SUM(amountPaidOnContentsClaim), 0)::INT64 AS ContentsClaim, \n",
    "        ROUND(SUM(amountPaidOnIncreasedCostOfComplianceClaim), 0)::INT64 AS IncreasedCostOfComplianceClaim\n",
    "    FROM claims\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 20\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63cfd88b",
   "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.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}