Skip to content

Analyzing FEMA's National Flood Insurance Program (NFIP) Data With DuckDB.

License

Notifications You must be signed in to change notification settings

mebauer/duckdb-fema-nfip

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

46 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Analyzing FEMA's National Flood Insurance Program (NFIP) Data With DuckDB

Author: Mark Bauer

Table of Contents

Note: This analysis uses the Federal Emergency Management Agency’s OpenFEMA API, but is not endorsed by FEMA. The Federal Government or FEMA cannot vouch for the data or analyses derived from these data after the data have been retrieved from the Agency's website(s).

Read more about OpenFEMA's Terms and Conditions.

Data as of 10-04-2024.

--

Table xx. Number and Amount Paid on NFIP Claims (both in millions). Total amount paid, in this analysis, is defined as the total amount paid for all Building, Contents, and Increased Cost of Compliance (ICC) claims.

countClaimsM paidTotalClaimM paidBuildingClaimM paidContentsClaimM paidICCM
2.67 80638.4 65049.6 14640.3 948.44

year-count Figure xx. Number of NFIP Claims by Year.

year-amount-paid Figure xx. Total Amount Paid on NFIP Claims by Year (Adjusted in 2023 Dollars).

Table xx. Top 10 Most Costly Flood Events By Total Amount Paid on NFIP Claims ($ millions). Table is ranked on Total Amount Paid in 2023 dollars (i.e. paidTotalClaimM2023). The column paidTotalClaimM is the amount paid ($ millions) when occurred.

rank yearOfLoss floodEvent countClaims paidTotalClaimM paidTotalClaimM2023 averagePaidClaim2023
1 2005 Hurricane Katrina 208348 16261 25371 121772
2 2012 Hurricane Sandy 144848 8956 11886 82062
3 2017 Hurricane Harvey 92396 9055 11256 121830
4 2022 Hurricane Ian 48721 4757 4953 101664
5 2008 Hurricane Ike 58126 2702 3824 65799
6 2016 Mid-summer severe storms 30017 2533 3216 107150
7 2004 Hurricane Ivan 20137 1325 2137 106170
8 2001 Tropical Storm Allison 35561 1104 1901 53461
9 2011 Hurricane Irene 52493 1347 1825 34770
10 2021 Hurricane Ida 28317 1346 1514 53480

figures/count-claims-map Figure xx. Number of NFIP Claims by State.

figures/count-claims-norm-map Figure xx. Number of NFIP Claims Normalized by State Area.

1. Introduction

The National Flood Insurance Program (NFIP) is managed by FEMA and provides flood insurance to mitigate the socio-economic impacts of floods. In 2019, OpenFEMA released two datasets related to the NFIP to promote transparency, reduce complexity for public data requests, and to improve how the agency’s stakeholders interact with and understand the NFIP:

  1. NFIP Redacted Policies
  2. NFIP Redacted Claims

With over 68 million policies and 2.6 million claims transactions as of October 04, 2024, this is one of the largest openly available insurance datasets in the United States and possibly the world. This project examines the NFIP Redacted Claims dataset.

Due to its large file size, accessing the dataset can be a challenge, even for experienced analysts. To address this, I designed a tutorial demonstrating how to analyze the dataset with my local laptop. To achieve this, I utilized DuckDB, a lightweight, high-performance SQL OLAP database management system. DuckDB offers a smooth experience, is blazing-fast, includes a robust Python API, and is open-source. I used SQL via the Python Client API for data analysis and used GeoPandas for mapping.

The ultimate goal of this project is to promote the dataset for academic research and to assist communities in analyzing and downloading the data. This dataset is one of my favorites, and I hope you find these tutorials helpful in advancing the study and analysis of the NFIP.

To learn more about the NFIP:

Note: This analysis uses the Federal Emergency Management Agency’s OpenFEMA API, but is not endorsed by FEMA. The Federal Government or FEMA cannot vouch for the data or analyses derived from these data after the data have been retrieved from the Agency's website(s).

2. Notebooks

  • Analysis: Explore NFIP Claims data with DuckDB with ease.
  • Download Data: Demonstrates how to download the NFIP Redacted Claims dataset from OpenFEMA.
  • Generate Figures: This notebook is dedicated to generating figures and tables displayed on this page. For a more detailed analysis of the NFIP data, refer to the analysis.ipynb notebook.

3. Data

OpenFEMA Dataset: FIMA NFIP Redacted Claims - v2. Retrieved from https://www.fema.gov/openfema-data-page/fima-nfip-redacted-claims-v2. This product uses the FEMA OpenFEMA API, but is not endorsed by FEMA. The Federal Government or FEMA cannot vouch for the data or analyses derived from these data after the data have been retrieved from the Agency's website(s).

Please refer to the OpenFEMA Terms and Conditions for further information regarding the usage and access of OpenFEMA datasets.

4. Additional Resources

FEMA:

Academic Research:

NFIP Redacted Claims & Policies Analysis:

The Wharton School of the University of Pennsylvania

The Natural Resources Defense Council (NRDC)

Verisk

Insurance Information Institute

Community Open Data:

Norfolk Open Data

Other:

DHS/FEMA

OIG

5. Say Hello!

Feel free to reach out.