Jun 03, 2026
Gage-Specific Water Height Acceleration Thresholds
- 1University of Kentucky
Protocol Citation: Meredith Swallom 2026. Gage-Specific Water Height Acceleration Thresholds . protocols.io https://dx.doi.org/10.17504/protocols.io.j8nlkyo46g5r/v1
License: This is an open access protocol distributed under the terms of the Creative Commons Attribution License, which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited
Protocol status: In development
We are still developing and optimizing this protocol
Created: August 14, 2025
Last Modified: June 03, 2026
Protocol Integer ID: 224725
Keywords: flash flood, hydrology, stream gage, extreme weather, specific water height acceleration threshold, water height acceleration threshold, gage height data, usgs guadalupe river at hunt gage, usgs guadalupe river, flash flooding, gage, hunt gage
Disclaimer
This protocol details one method for identifying site-specific flash flood indicators in the hydrologic record. Individual communities may explore its utility in their area as a supplement to, but not a replacement for, weather-related alerts issued by the National Weather Service.
Abstract
The water height acceleration threshold (WHAT) procedure may be applied in any region where flash flooding is a concern and U.S. Geological Survey (USGS) gage height data are available. This protocol details this procedure using the USGS Guadalupe River at Hunt gage (site no. 08165300) as an example.
Materials
To implement this protocol, you will need an internet connection to access USGS stream gage data and Microsoft Excel for data processing.
Threshold Derivation Using Existing Stream Gages
Identify stream gages in a flood-prone catchment using https://waterdata.usgs.gov/. Real time data should be explored by state. For this example, select "Texas."
Fig. 1 - USGS Water Data for the Nation portal for exploring stream gage locations.
Select "Gage height" from the list of available data types for the selected state.
Fig. 2 - Data types available at USGS stream gages. For this procedure, only gage heights are needed.
Navigate to the catchment of interest, select a gage of interest (08165500 in this case), and click "View Monitoring location page."
Fig. 3 - Spatial distribution of USGS gages with gage height data.
This will take you to a new page, where the default display should be instantaneous gage height data, usually at 15-minute or 5-minute intervals. The following steps use 15-minute data and provide a method for converting 5-minute data to 15-minute data.
Fig. 4 - The default view of gage height data at a selected gage is the seven-day record of instantaneous (15-minute or 5-minute) observations.
Scroll down and "Change time span" to a period of interest. For this example, thresholds will be based on a flash flood in October 2018, so a seven-day period encompassing that event, October 12-18, was selected.
Fig. 5 - Adjust time span to a date range that includes a historical storm in selected catchment to be used for thresholding.
After the time span updates in the graph view, select “Download data," choose the option for “Continuous data,” and click “Retrieve.”
Fig. 6 - Data download for custom time span.
A new window will open with the selected data. Copy all columnar data, beginning with the column labeled “agency_cd.”
Fig. 7 - All data from the chosen time span can be copied from the .txt file, which opens in a new window.
Open Microsoft Excel, right click in a cell, and Paste Special --> Text to paste data into appropriate columns.
Fig. 8 - Paste copied data into Excel to be processed.
Any duplicate observations will result in a division by zero error with respect to time when calculating 1st and 2nd derivatives. Remove any duplicate rows by selecting the entire sheet, navigating to Data --> Remove Duplicates, and deselecting all columns except the date/time (Column C, in this case).
Fig. 9 - Removal of duplicate values ensures that there are time differences between each observation. Failure to remove duplicates will result in division by zero errors in Steps 10 and 11.
In addition to missing or repeated time observations, some gages alternate temporal resolution. 15-minute data generally yield lower 1st and 2nd derivatives. Derivatives using 5-minute data can be higher due to division of potentially rapid fluctuations in gage height by a shorter time window. As 15-minute data are more common, we determine WHATs based on that temporal resolution.
To expediently cleanse the hydrologic record of 5-minute interval data, a coding solution may be implemented. Because this may present a barrier to using the WHAT method for many people, one option for correcting the data using only Microsoft Excel is detailed below.
Select the column with date/time (Column C).
Find and replace all time increments not at 15-minute intervals (:05, :10, :20, :25, :35, :40, :50, :55) with "!!!!".
Fig. 10 - The "Find and Select --> Replace" option should be used on the date/time column.
Fig. 11 - Dialog box for finding and replacing 5-minute observations with "!!!!".
Sort the entire dataset (newest to oldest) by the date/time column. Rows containing the adjusted dates/times (those containing "!!!!") should now be at the top and can be deleted.
Fig. 12 - All rows with date/times containing "!!!!" can be deleted.
Re-sort by date/time so the whole datasheet is chronological.
To use time differences to calculate 1st and 2nd derivatives, the date/time needs to be converted to hours.
Copy/paste column C and format the new column as a number. This will change the date/time to days (column G).
Fig. 13 - Copy the date/time column and reformat as "number."
This will change the date/time to a decimal number of days.
Make a new "Corrected Time" column (Column H) and populate cell H2 with "=(G2*24)". Copy that formula down all of Column H. This will convert days in Column G to hours.
Times should all be at quarter hour increments now (Fig. 14, Column H) and 1st and 2nd derivatives can be calculated.
Label Column I "1st derivative” and populate cell I2 with “=(E2-E1)/(H2-H1)”, then copy that formula down all of Column I. This will calculate a rate of change in gage height over each 15-minute period.
Fig. 14 - Calculation of 1st derivative using gage height (ft, Column E) and corrected time (hrs, Column H).
Label Column J “2nd derivative” and populate cell J2 with with “=(I3-I2)/(H3-H2)”, then copy that formula down all of Column J. This will calculate a rate of change in the 1st derivative over each 15-minute period, which represents the 2nd derivative (acceleration).
Fig. 15 - Calculation of 2nd derivative using 1st derivative (ft/hr, Column I) and corrected time (hrs, Column H).
Label Column K “Height times acceleration” and populate cell K2 with “=(E2*J2)” to multiply water height at the gage by its acceleration. Copy that formula down all of Column K.
Fig. 16 - Calculation of acceleration (2nd derivative, Column J) times gage height (ft, Column E). The maximum value during a chosen flood represents the "water height x acceleration threshold" for this particular gage.
Extract the maximum value of (water height x acceleration) over the dataset (Column K). At this gage, the maximum was 313.8 ft^2/hr^2 during the storm event. This represents the WHAT for future flood scenarios.
Repeat this procedure for other gages in your catchment.