Analyzing River Discharge Patterns

Purpose

1. Become acquainted with using a spreadsheet program (Excel) to perform simple data analysis; in particular:
2. Become acquainted with interpreting river discharge data including:
3. Relating discharge patterns to seasonality and geographic setting.
4. Furthermore you should think about how these types of data could be used for water management.

Outline

1. You will be analyzing River Discharge Data from two rivers. The data tables will list the discharge for every day for each of 11 or 12 years on record. Discharge is the total amount of water per unit time passing by a point along the river, and is presented here in units of cubic feet per second. It is computed from river level data, as measured by an instrument mounted along the bank of the river. The stations used here are among thousands operated by the U.S. Geological Survey (which can be accessed through their web-based data retrieval service):
   • Wisconsin River (Muscoda, WI): Info, Data, Map (Eleven years of data beginning on Oct.1)
   • Salt River (Phoenix, AZ): Info, Data, Map (Twelve years of data beginning on Jan. 1)

   (The maps were made with the U.S. Census Bureau's Tiger Map Browser).

2. The Info section for each of the rivers explains where each station is located. Click on the Info and the Map links above to familiarize yourself with each river, its geographical setting, and the dates that the data cover.
3. Use Excel to make a graph of river discharge vs. time for each of the two stations. This will involve highlighting the data on your spreadsheet and choosing the Chart Wizard from the Excel Menu bar. You will probably want to make an X-Y scatter plot and leave off the actual data points, otherwise the finished graph will be too cluttered. Study the pattern on the 2 graphs:
   • Describe the overall seasonal cycle of water flow in these two rivers.
   • During what months of the year is the discharge the greatest and least? (Remember that the Wisconsin River data starts on Oct. 1, not January 1.) What is the reason for these variations (e.g. melting of snow, seasonal rains, dry seasons, etc.)?
   • Can individual storms be identified? If so, how long does it take for river discharge to return to normal levels after a storm? (You may have to change the scale on your graph to answer this and other questions.)
   • Compute the total amount of water discharged per year for each of the 11 or 12 years of data. (This will involve making a new column in each spreadsheet, summing the 365/366 observations for each year, and remembering to convert the units from CFS (cubic feet per second) to CFY (cubic feet per year). How much does this sum vary? What water management problems may occur for each river?
   • Estimate the frequency of floods on the Wisconsin River that have a discharge of 90,000 cubic feet per second or greater. Do this by completing the following steps:
4. Make a histogram (click here for a primer on histograms) using the Histogram Tool (select it under Data Analysis... on the Tools Menu) of the discharge data in column B of your spreadsheet. (Use the Help facility built into Excel to answer some of your questions about how to use this feature of Excel). Although you can let Excel assign the data bins itself, to get sensible bins bounded by whole numbers such as 1000, 2000, etc, you will have to choose a reasonable number and size of the bins into which the histogram tool will distribute the data. You must enter these numbers into a column (use column D) of your spreadsheet (make use of the Fill: Series feature found on the File Menu) prior to invoking the Histogram Tool. Once you have started the Histogram Tool, you will need to tell it the range of cells containing your data, the range of cells containing your bin designations, and where you want it to place the results - on the same sheet (this is preferred; give the Tool an empty cell location such as E1) or on another 'ply of the same Workbook (the default radio button in the Histogram Tool). The Frequency column generated (column F) by the Histogram Tool gives the number of days during the 11 year period of data that the discharge is in a given range. (Click here if Data Analysis... doesn't show up on your copy of Excel.)
5. In the next column (G if you are following exactly the directions), multiply the day count by 1000 and divide it by 11. You should enter a formula in one of the desired cells and then Copy and Paste this formula into all other cells in column G to make the same calculations for the other data bins. This is an estimate of the number of days in a 1000 year period that the discharge is in a given range.
6. In the final column (H) of your spreadsheet, sum the day count, from Bottom to Top. This is now a cumulative histogram reporting the number of days that the discharge exceeds a given value.
7. Plot, and print out, this cumulative histogram data on a log-log plot, with the discharge on the x-axis and the day count on the y-axis. You will need to learn how to graph non-adjacent columns in Excel (E vs H) to make this plot. You will also need to change the axes to logarithmic after first plotting the data on linear axes.
8. Draw a straight line over an appropriate portion of the data, extrapolating it downward and to the right.
9. Read off the number of days that the discharge is predicted to exceed 90,000 cubic feet per second. This gives the frequency, that is the number of such floods per thousand years.
10. How much error do you think is associated with your prediction?

To be Submitted

1. A one paragraph summary of what you learned about river discharge.
2. Anotated printouts of the discharge vs. time plots of the two rivers.
3. Your log-log cumulative histogram plot showing your line through the data and your predicted flood frequency.
4. One page of text which addresses the questions posed in part 3 of the Outline.