Unit 18.  Graphing and interpreting hydrographs using moving averages

Last modified by Steve Custer 29 October 2001

Moving Average

    Much of the precipitation, stream flow, and peak flow data from natural systems is noisy.  The data show high variance.  As a result, trends in the data are difficult to see.  One tool for analysis is the moving average.  This tool is illustrated in the paper by Yang and others (1998).  There are a variety of ways a moving average can be calculated.   The principle is that a window is selected (3 years in the case of the Yellow River).  The average for that three year window is calculated and the answer is placed in the middle year; the window is moved, the average for the next three year period is calculated, and the value is placed in the middle year of that range, and so forth.  The window can be any number of years (2, 3, 5, and  10 are common).  Let's examine the 3 year moving average in more detail.  There is more than one way the average can actually be calculated.   The window can be the average of the year and the next two, the average of the year and the previous two, or the average of the year and the previous and following year.  The difference is illustrated in the table below.  Notice that the answer one gets is different for each strategy.  The same principle applies for the 5 and 10 year moving average.  Notice too that the larger the window for the moving average the more smoothing that will be achieved but the smaller the number of results that is possible.  The number of years that can be calculated is number of years in the sample minus the number of years in the moving average minus one.  Thus, if you have 20 years of data, using a 10 year moving average will produce little information.  The methodology works best on very long records.

Table 1.  Three ways to calculate a three year moving average  for a dummy data set.
 
 
Average Average Average
Year Value year year year
and  and and 
following previous previous
three three and 
following year
1990 2 3.7 can't do can't do
1991 4 4.0 can't do 3.7
1992 5 3.3 3.7 4.0
1993 3 2.0 4.0 3.3
1994 2 1.7 3.3 2.0
1995 1         1.3 2.0 1.7
1996 2 2.7 1.7 1.3
1997 1 can't do 1.3 2.7
1998 5 can't do 2.7 can't do

 Task 1:  Calculate a Moving Average of the annual discharge of the Gallatin River at Gallatin Gateway.  Is there a trend?

Now that you have seen an example and the concept has been developed, try a moving average for yourself.  Go to the Gallatin River at Gallatin Gateway.  The question I want to explore is whether there is a trend in the stream flow for the Gallatin River that is similar to  that for the Yellow River. I frankly expect the  Gallatin River at Gateway might show no pattern since the river is a free flowing river with no dams.  Most of the drainage basin is US Forest service land. There is little agriculture (although irrigation take outs do occur above Gateway).  The primary development in the drainage basin is the Big Sky Ski Resort.   By most US standards this river would be considered undeveloped.  The data is at the Montana  USGS Water Resources Division Site.  I have provided the data below, but have outlined the procedure below to help you and me remember how the data was obtained.

Montana  USGS Water Resources Division
First I looked up the code number for the station
    Historical Stream flow data
        Annual
            County
                Submit
                    Found the number and wrote it down 06043500

Then I went to annual data to get what I wanted
    Historical Stream flow data
        Annual  (there are several other types of data)
            Site number
                submit
                    06043500 (obtained by a search of site names)
                        HTML Table
                            Submit

I then edited the data in Netscape composer
                                File
                                    Editpage
                                        Cut table of interest out
                                            Paste table into a new page
                                                Save the page in HTML
                                                    open the page in EXCEL
                                                        Cut the columns side by side
                                                            Paste the columns so a single column is left
                                                                 Save in HTML or in Excel.
 

One can follow the same procedure for Peak discharge which we will use later.
Montana  USGS Water Resources Division
First I looked up the code number for the station
    Historical Stream flow data
        Annual
            County
                Submit
                    Found the number and wrote it down 06043500

Montana  USGS Water Resources Division
    Historical Stream flow data
        Peak
            Site number
                submit
                    06043500 (obtained by a search of site names)
                    Retrieve graphs
                            Submit
                                click on table
 I then edited the data in Netscape composer
                                File
                                    Editpage
                                        Cut table of interest out
                                            Paste table into a new page
                                                Save the page in HTML
                                                    open the page in EXCEL
                                                        Cut the columns side by side
                                                            Paste the columns so a single column is left
                                                                 Save in HTML or in Excel.
 
 

To save you time, I have provided the files I made to this point.  Go to the hot links above  and get the files in HTML format, parse the data, and begin work here.

Before we begin, some definitions may help.  The USGS is reporting the following:
    Daily mean stream flow in cfs is the flow which when multiplied by the number of seconds in a day would produce the volume of water (cubic feet) that passed the gage on that day.  (The data set of instantaneous discharge is too large to serve.  Consider how many lines of data would be present if one measured the discharge every second of every day (or every hour for that matter).
    If you would like to see a hydrograph of the daily mean stream flow for water year 2000 (1 October 1999 to 30 September 2000)  you can go to
Montana  USGS Water Resources Division
    Historical Stream flow data
        Daily
            Site number
                submit
                    06043500 (obtained by a search of site names)
                    Set the date to the appropriate time period  (1 October 1999 to 30 September 2000)
                        Retrieve graphs
                            Submit
Remember that you are looking at daily mean flows not instantaneous flows.
If you try to retrieve the data for all dates, you will have a hard time reading the data because there is so much.  That is why I selected a year.  You can imagine how large the data base of instantaneous discharge values measured every 15 minutes or every hour would be.  To see the instantaneous flow you could ask the computer at the USGS web site to serve the real time data which shows recent flow data.

    Monthly mean stream flow in cfs is the flow which when multiplied by the number of seconds in a month would produce the volume of water (cubic feet) that passed the station during the month of interest in the year of interest.

    Annual mean stream flow in cfs is the flow which when multiplied by the number of seconds in a year would produce the volume of water (cubic feet) that passed the station during the year of interest.

These concepts are useful when trying to get a sense of the volume of water that can be relied on for irrigation in an average year, average month or average day.  The concept is different than the instantaneous discharge which would come from a raw stage-discharge relationship and would be converted to instantaneous discharge.

The real task at hand is to make a plot of the annual water discharge just as Yang et al. did for the Yellow River.  To do this you must first obtain annual mean stream flow data in cfs for the Gallatin River near Gallatin Gateway.  I have obtained that data and put it in an HTML file so you do not have to do that step.  (You can do that step by following the instructions if you wish).

The Annual Average Daily Mean Flow needs some work yet.
    I added lines for missing years so there were blanks
    I then typed years rather than dates in a column I inserted to the left of the  of the data.
        In D4 I typed 1932
        In D5 I typed +D4+1  (=+D4+1 in Excel)
        I copied D5 to all columns below D5
        Now I have a column of numeric years for each date.
    I created a moving average column in E  Column
    I calculated the three year moving average as did Yang et al.
        In Cell  E5 (the second line of data) I wrote a formula (=Average E2:E4 in Excel)  (@avg(E2..E4) in Quattro Pro)
        I then copied the average to all cells below E5 in the spread sheet.
        I then removed the formula from cells that spanned the blank years
            Click
            Delete
NOTE:  In Quattro Pro, there is a function called moving average.  I believe there is such a function in Excel but need to find it.  If you want to try this function, you can.  I decided I would share the actual formulas in the hope that you would see the arithmetic of the moving average.

    I then made a graph.

            In Excel

                Highlight the data (click and drag)
                 Copy
                Move to a new spread sheet or a blank part of the current spreadsheet
                    Click on a cell
                    Paste Special
                    Value
                    Delete all columns other than the two you wish to graph and move the columns next to each other with date on left
                             I know there are more elegant ways of doing this.  Be fancy if you wish.  I am doing the brute force method.
                Click and drag over the data to highlight it.
                Click on the chart wizard on the tool bar.
                     XY Scatter
                    Highlight Line Graph
                    Next
                    Next
                    type in titles
                    Finish
                        Save as object on spreadsheet.  I prefer this for review.
                                           OR
                        Save on another spread sheet.

                In Quattro Pro

                Highlight the data (click and drag)
                Copy
                Move to a new spread sheet or a blank part of the current spreadsheet
                    Click on a cell
                    Paste Special
                    Value
                    Delete all columns other than the two you wish to graph and move the columns next to each other with date on left
                           I know there are more elegant ways of doing this.  Be fancy if you wish.  I am doing the brute force method.
                Click and drag over the data to highlight it.
                Click on the chart wizard on the tool bar.
                    Click on Specialty
                    Click on Dot and line
                    Right click on the chart error  area.
                        X axis = A1-A65  (or what ever area you put the date data in)
                        Series = B1-B65   (or what ever area you put the moving averages in)
                    Right click on titles
                        type in nice explanatory titles
                    Finish.

Assignment


1.  (4 pts) When you have finished this work open your word processor, copy the graph object into your word processor and save it as an rtf file with the name U18_galgateavg_yourlastname_filetype.  If you want to send the graph in some other format that is ok as long as I can read it.  I do not however want the whole spread sheet.  My zip disk is already too full because of images.  Put the file in Assignment dropbox  U18a_MoveAvg.  Please send only the graphical image not the whole spreadsheet.
 

2.  (2 pts)  Discuss whether you see a trend in the plot and discuss what you think the origin of that trend might be. (I described the basin earlier.) The discussion should be in U18b_GatewayTrend.
 
 

Your Classroom (optional)

Note that the Gallatin River was provided as an example.  This example is likely not relevant to your classroom.  You can calculate moving averages for a river of interest to you or your class. You might develop a lesson plan for the second lesson plan if this interests you.  You could use the hydrograph of your river as described under hydrograph of the daily mean stream flow for water year 2000, or you could develop a moving average for a river near your school and ask the students what if any trends are shown and what the origin of that trend might be.)
 

Due Date:  10 November 2001 at Midnight.