Computation and Computers in Geology

Computation and Computers in Geology

Flood Frequency Analysis using USGS web-based discharge data

I’ve relied directly on Berhnardt’s (1998) article for this exercise – for full details refer to the original article: A WWW and spreadsheet-based exercise on flood-frequency analysis, Journal of Geoscience Education, v. 46, 1998. The USGS provides discharge data for our rivers on their web site. We’ll download some of those data and calculate recurrence intervals for various sized floods.

· To acquire data, use your browser to connect to the USGS site: http://water.usgs.gov

Select the Water Data link to get to http://water.usgs.gov/public/data.html.

Select NWIS-Historical Data to get to http://waterdata.usgs.gov/nwis-w/US/.

Select a state to get information from, search for and select a gauging station.

Click Peak Flow and the years to retrieve and click the following options:
only annual peaks

tab delimited

mm/dd/yyyy - Hit the Retrieve Data button – you should see a screen of data.

In the browser’s File Menu, name the file: Select Save as, the appropriate directory and, under Save as type, select text file and save it.

· Now import your discharge data into Excel. You start this operation by trying to open your file as you would a normal Excel worksheet with the exception that in the Files of type: box that shows up in the Open screen you select "Text Files" or "all files" rather the .xls files. Now double-click the data file you wish to open. Follow the instructions provided by Excel’s Text Import Wizard. In following the wizard you will make a number of decisions. For example, do tabs, spaces or commas separate the data fields or are they fixed in width? As you make the choice Excel shows you how it will parse your file based on your decisions. When you have the data in the correct format, simply click Finish.

· Highlight all the data, select Sort under Excel’s Data Menu, click Descending and the Discharge column, then click OK. Clean up the data file as necessary and appropriate.

· Insert two new columns, one on either side of the discharge column. In the left-hand column generate a list of integers, starting at one and proceeding through the number of data points (for 12 observations, 1 – 12). In the right-hand column use the Weibull equation to calculate:

Where RI is the recurrence interval, n is the number of observations, and m is the rank (1 to n) of the discharge observed. The reciprocal of the recurrence interval is the probability of occurrence for a flood of that discharge. For example, the 5^th highest flood in a 9-year record would have RI=2 and the probability of a flood of this size in any given year is about 50%.

Exercise: Search through the gauging stations and choose a major river in the US and one of its significant tributaries. For both:

1. Use the recurrence interval, on a log-axis, as the independent variable (x axis) and discharge as the dependent variable (y axis) to create a curve of discharge versus frequency.

2. Now, calculate best-fit lines through these (discharge vs recurrence) data and add those lines to your graph. There are two ways to do this:

Use LINEST() to calculate the line. Remember you are plotting the x-axis as a LOG₁₀ axis. Thus you need to do your linear regression on LOG(x) values. So you need to create a new column which transforms the recurrence intervals to LOG(recurrence interval). Next use LINEST() with the discharge as the y values, and your LOG(recurrence) column as the x values; this yields a slope and intercept. Use that slope and intercept to create a new set of y values {y=slope*recurrence + intercept) and plot them on the discharge vs recurrence graph. Remember, the result should look like a least squares best fit line (on the discharge vs recurrence graph) where the x axis is a LOG axis.

Use Excel's trendline process to calculate the line:
Right click the data series on the graph.

Select Add Trendline from the drop-down menu.

Select logarithmic and Excel will calculate and plot the least squares best fit line. If you check the Display Equation box under the Option Tab, Excel will post the line's parameters on the chart. You can also plot R-squared, a measure of fit, the same way.

Use the slope and intercept values from your best fit lines to calculate the discharge for 10-year, 100-year, and 1,000-year floods.

3. Use Excel’s MEDIAN() and AVERAGE() functions to calculate those values for your discharge data.

4. What is the recurrence interval for the median flood on your rivers?

5. Think about the geology of all this; learning Excel is just the insidious side effect.

Back to Course Contents.