US Drinking Water Utility Climate Change Projections and Combined Hazard Index Scores

This dataset includes climate change hazard projections and combined climate hazard index values for 42,786 drinking water utilities accross the continental United States (US). The projections are compiled from multiple sources, including the Climate Mapping for Resilience and Adaptation tool (CMRA) and Climate Risk and Resilience Portal (ClimRR), and use mid-century (2050) Representative Concentration Pathway 4.5 CMIP5 Localized Constructed Analogs (LOCA) CMIP5 Projections for North America. The included climate hazards are extreme heat, energy demand, freeze-thaw cycles, extreme precipitation, wildfires, water supply stress, and sea level rise. Each row of the dataset corresponds to a different community water system within the contiguous US, each identified using their assigned Public Water System Identification number More details about the data sources and modeled combined climate hazard index can be found in the publication: Lyle et al 2025, Environ. Res.: Climate,  https://doi.org/10.1088/2752-5295/adab10. Code can be found here: https://github.com/zialyle/DW-climate-change-hazard-index

The columns in the database are as follows:

pwsid: Public Water System Identification Number

primacy_agency_code: Two character postal code for the state or territory having regulatory oversight for the water system.

pws_name: Name of the water system

State: State in which water system is located

city_served: City in which water system is located

County: County in which water system is located

population_served_count: Number of customers served by water system

service_connections_count: Number of service connections maintained by water system

service_area_type_code: Service area type code

owner_type_code: Code that dentifies the ownership category of the water system consisting of: F (Federal Government), L (Local Government), M (Public/Private), N (Native American), P (Private), or S (State Government)

is_wholesaler_ind: Indicates whether the system is a wholesaler of water

primacy_type: Code that indicates if the water system is regulated by a state, tribal, or territorial primacy program. Note that EPA direct implementation programs, except for Wyoming, are tribal primacy programs

primary_source_code: The code showing the differentiation between the sources of water: ground water (GW),groundwater purchased (GWP), surface water (SW), surface water purchased (SWP), groundwater under influence of surface water (GU), or purchased ground water under influence of surface water source (GUP)

centroid_lat: Latitude ocation of water system

centroid_lon: Longitude ocation of water system

NOAA.Region: NOAA Climate Region in which water system is located

heat_index: Extreme heat index value

historic_mean_maxtemp_5d: Annual highest maximum temperature averaged over a 5-day period [degF], historical mean 

RCP4.5_mid_mean_maxtemp_5d: Annual highest maximum temperature averaged over a 5-day period [degF], RCP 4.5 mid-century

RC_maxtemp_5d: Relative change in annual highest maximum temperature averaged over a 5-day period [degF] from historical to RCP 4.5 mid-century 

Diff_maxtemp_5d: Absolute change in annual highest maximum temperature averaged over a 5-day period [degF] from historical to RCP 4.5 mid-century 

extremeprecip_index: Extreme precipitation index value

historic_mean_highest_precip_5d: Annual highest precipitation total over a 5-day period [inches] , historical mean 

RCP4.5_mid_mean_highest_precip_5d: Annual highest precipitation total over a 5-day period [inches] , RCP 4.5 mid-century

RC_highest_precip_5d: Relative change in annual highest precipitation total over a 5-day period [inches] from historical to RCP 4.5 mid-century

Diff_highest_precip_5d: Absolute change in annual highest precipitation total over a 5-day period [inches] from historical to RCP 4.5 mid-century

SLR_index: Sea level rise index value

SLR_indicator: Sea level rise indicator, where 0 indicates utility is not in a county expecting some amount of sea level rise by 2100 and 1 indicates utility is in a county expecting some amount of sea level rise by 2100.

wildfirerisk_index: Wildfire index value

RC_avg_wildfire: Relative change in Fire Weather Index from historical to RCP 4.5 mid-century 

D_avg_wildfire: Absolute change in Fire Weather Index from historical to RCP 4.5 mid-century 

FT_index: Freeze-Thaw cycle index value

RCP_mid_mean_FT: Number of freeze-thaw days (days as those with a maximum daily temperature above 0 degC and a minimum temperature below 0 degC), RCP 4.5

historical_mean_FT: Number of freeze-thaw days (days as those with a maximum daily temperature above 0 degC and a minimum temperature below 0 degC), historical mean

RC_FT: Relative change in the umber of freeze-thaw days (days as those with a maximum daily temperature above 0 degC and a minimum temperature below 0 degC) from historical to RCP 4.5 mid-century 

Diff_FT: Absolute change in the umber of freeze-thaw days (days as those with a maximum daily temperature above 0 degC and a minimum temperature below 0 degC) from historical to RCP 4.5 mid-century 

waterrisk_index: Water stress index value, using (Dickson & Dzombak, 2019)

water_stress: Change in water supply stress from historical to RCP 4.5 mid-century, using Water Supply Stress Index from (Dickson & Dzombak, 2019) 

energydemand_index: Energy demand index value, using regression model developed by (Sowby & Burian, 2022)

energy_demand: Change in energy demand by mid-century under RCP 4.5 scenarios, using utility energy use model from (Sowby & Hales, 2022).

historic_mean_avg_temp: Daily average temperature [degF] , historical mean 

RCP4.5_mid_mean_avg_temp: Daily average temperature [degF] , RCP 4.5 mid-century

RC_avg_temp: Relative change in daily average temperature [degF] from historical to RCP 4.5 mid-century 

Diff_avg_temp: Absolute change in daily average temperature [degF] from historical to RCP 4.5 mid-century 

historic_mean_avg_precip: Daily average precipitation [inches] , historical mean

RCP4.5_mid_mean_avg_precip: Daily average precipitation [inches] , RCP 4.5 mid-century

RC_avg_precip: Relative change in daily average precipitation [inches] from historical to RCP 4.5 mid-century 

Diff_avg_precip: Absolute change in daily average precipitation [inches] from historical to RCP 4.5 mid-century 

hazard_index: Combined climate change hazard index value, normalized from 0 to 1

hazard_index_group: Classification group for combined climate change hazard index value (minimal, low, moderate, high)

heat_threshold: Binary value indicating whether PWS exceeded risk threshold level for extreme heat (0 indicating no, 1 indicating yes)

precip_threshold: Binary value indicating whether PWS exceeded risk threshold level for extreme precipitation (0 indicating no, 1 indicating yes)

SLR_threshold: Binary value indicating whether PWS exceeded risk threshold level for sea level rise (0 indicating no, 1 indicating yes)

wildfire_threshold: Binary value indicating whether PWS exceeded risk threshold level for wildfires (0 indicating no, 1 indicating yes)

FT_threshold: Binary value indicating whether PWS exceeded risk threshold level for freeze-thaw cycles (0 indicating no, 1 indicating yes)

waterstress_threshold: Binary value indicating whether PWS exceeded risk threshold level for water stress (0 indicating no, 1 indicating yes)

energydemand_threshold: Binary value indicating whether PWS exceeded risk threshold level for enegery demand (0 indicating no, 1 indicating yes)

sum: Total number of climate hazard risk threshold values exceeded

exposure: Product of combined climate change hazard index value and population served