CE 397 Statistics in Water Resources

Exercise 1

Exploring Time Series Data with HydroExcel

By:  Stephanie Johnson, Tim Whiteaker, David Maidment

Center for Research in Water Resources

University of Texas at Austin

January 2009

Contents

Introduction. 2

Goals of this Exercise. 2

Computer Requirements. 2

User Feedback on HydroExcel 2

Procedure. 3

HydroExcel Spreadsheets. 3

Selecting a Data Source. 4

Getting a List of Variables. 5

Getting a List of Sites. 6

Getting a Site Catalog. 9

Getting a Time Series. 12

Summarizing Daily Time Series by Months and Years. 13

Descriptive Statistics in Excel 18

Histograms in Excel 23

To be turned in: 24

Introduction

In this exercise we’re going to explore some examples of temporal and spatial variation in hydrologic variables.  To do our exploration, we will use some tools developed by the Consortium of Universities for the Advancement of Hydrologic Science Inc. (CUAHSI) to explore mean daily streamflows at various sites across the nation.  Streamflow is an excellent example of how variables vary in time and space and it is one of the most common hydrologic variables to explore.

Goals of this Exercise

The goals of this exercise are to explore temporal variations in mean daily stream discharge  using HydroExcel.  These daily mean values are archived and available through the National Water Information System (NWIS); they can be downloaded online at http://waterdata.usgs.gov/nwis/dv/?referred_module=sw.  They can also be obtained using CUASHI web services through HydroExcel.

Computer Requirements

To perform this exercise you will need a computer with the Windows operating system, a live internet connection, Microsoft Excel 2007, and Google Earth (download and install it here:  http://earth.google.com/).  We will be using HydroExcel, developed by CUAHSI researchers to adapt Microsoft Excel for CUAHSI web services input, serving data both from national data providers and universities. The spreadsheet uses VBA macros and an object library called HydroObjects http://his.cuahsi.org/hydroobjects.html  to communicate with and retrieve data from WaterOneFlow web services.  The latest version of HydroExcel available online is at http://his.cuahsi.org/hydroexcel.html. It is Version 1.1.  We will be using HydroExcel Version 1.1.1 which is contained in the file HydroExcel-Jan09.xlsb, which is accessible as a zip file at HydroExcel-Jan09.zip, which uses the same version of HydroObjects as Version 1.1

If you’d like to download HydroExcel and install it on your personal computer, you can do so.  Otherwise, the tool has already been installed on the computers in the Civil Engineering Learning Resource Center (LRC) on the 3rd floor of ECJ.  If you choose to install HydroExcel on your personal computer you first need to install the object library, HydroObjects.  HydroObjects is available at http://his.cuahsi.org/hydroobjects.html ; simply run the .exe file and follow the installation procedure.  (HydroObjects is already installed and enabled on the computers in the LRC).

User Feedback on HydroExcel

We would like feedback on user improvements that could be made to HydroExcel.  If you have problems using this tool or see places where it could be improved, please note them as you do this exercise and then respond at the end to the following questions:

(1)    Installation – were you able to install the tool correctly?

(2)   Errors – Did you encounter any errors?  Is the error reproducible?  If so, please list the steps so that the developers can recreate the error.

(3)   User Interface – Is the user interface intuitive?  Are buttons labeled appropriately?  Is it clear where to input the parameters for each worksheet?

(4)   Explanatory Text – Is the text on the left side of each worksheet useful?  There's a lot of text there.  Can it be trimmed down?  Should more be added to it?

(5)   Operability – Is the spreadsheet responsive?  When things happen, do you get an appropriate message box or status message?

(6)   Distractions – List up to three things that annoy you about the spreadsheet.  If it annoys you, how could it do those things differently so as not to annoy you?

(7)   Capabilities – List up to three things that you wish the spreadsheet did that it does not currently do.

Procedure

HydroExcel Spreadsheets

Open HydroExcel from the file HydroExcel-Jan09.xlsb.   HydroExcel uses macros to perform its duties, so you may have to enable the macros functionality by going to the security warning at the top of the screen and telling it to “Enable this Content”.

Save the HydroExcel file to a new name Ex1HydroExcel-Jan09.xlsb so that if you want to go back and get your original version again at any time you can do so.

Once HydroExcel is running, you’ll see a row of tabs at the bottom of the spreadsheet.

These tabs have the following functions:

Introduction provides explanatory text about how to install and operate HydroExcel;

Data Source lists WSDL addresses for WaterOneFlow web service sources and allows you to select one of these sources for accessing data;

Sites enables you to download the list of observation sites in this network;

Variables lists the quantities measured at some or all of these sites;

SiteInfo lists the variables measured at one site, over what time interval and how many data are available;

SiteCatalog lists the SiteInfo for all sites on the network;

SiteSummary summarize the data from the catalog;

Time Series enables you to get a time series for one variable at one site;

Statistics and Charts enables you to summarize the time series using different time periods (hourly, daily, monthly, annual).

Selecting a Data Source

Open the Data Source worksheet.  Here we see a list of all the web services that HydroExcel can operate. Each one of these services using a live internet connection to access a particular dataset (national, state, or university-based) and return the information that was requested.  The default WSDL location is the data series for the Little Bear River observation network at Utah State University.

For our work, we want to retrieve mean daily streamflow data from the USGS NWIS daily value database http://river.sdsc.edu/wateroneflow/NWIS/DailyValues.asmx?WSDL so select this WSDL address and copy it into the box at the top labeled WSDL Location.  You can also accomplish this task by right clicking on the address in the list, going to the HydroExcel tab in the context menu that appears, and choosing “Set active web service to …..” as shown below.

The address should now appear in the “WSDL Location” box as shown.

Lets check to make sure that the web services are working.  Hit the  button.  You’ll hear a “Clunk” and then the following message should appear, which shows you that all the web services are up and runnnig.  Ok, we are in business!

Getting a List of Variables

Let’s take a look at what kind of data we can get from the NWIS daily values web service.  Select the   button, and you’ll see that the view switches to the Variables and after a short wait a list of variables is returned.  These are all the variables for which data are recorded at some sites in the NWIS Daily Values network.  Scroll down and take a look at all the variables that are available.  From this list we can see that there are 382 of them in total.  Here are a couple of useful ones:

NWISDV:00060

Discharge, cubic feet per second

NWISDV:00600

Total nitrogen, water, unfiltered, milligrams per liter

Here, NWISDV stands for NWIS Daily Values and the DV distinguishes these data from others measured at these sites (such as grab sample water quality data), or those measured on other NWIS networks, such as for groundwater.

Getting a List of Sites

OK.  Now let’s tell the program what geographical location we’re interested in retrieving daily values for.  We do this under the  tab.  If at this point we told the tool to “Get Sites”, it would go out and retrieve a list of all the sites that have daily values available in the NWIS daily values database.  Since that’s over 24,000 sites, let’s rein in our search by telling it to only locate sites in a certain geographical area –  in the Get Sites Options box, check the Use Lat/Lon Box (if you don’t have the check mark as shown below, HydroExcel ignores the parameters that you have added in the lat-long box to limit the search and just acquires all the sites in the network).

Lets also map the sites in Google Earth after downloading by selecting TRUE in the Create and open KML file after download.  We now input a lat/lon box that frames in the area around Austin, Texas (see figure below), a range from 29.5ºN to 31ºN in latitude, and 97ºW to 98ºW in longitude (remember that -97 and -98 are needed when specifying longitudes in decimal degrees, because West Longitudes are considered negative, while East Longitudes are positive).

Select Get Sites and wait a few seconds.  HydroExcel goes over the internet connection, accesses the NWIS database, and downloads the 79 sites that have daily values in our indicated area of interest.  By indicating “TRUE” in the box, we also told HydroExcel to create and open a KML file showing us where these sites are located.  The tool automatically creates this layer and opens Google Earth with the sites indicated with pins.  Pretty Cool!

If you go to the Google Earth Places menu on the left of the display and click on the + sign by NWISDV network, it will expand and you’ll see a listing of all the sites in the downloaded network.

If you scroll down, you’ll find site 08158000, Colorado River at Austin. And if you click on its pin, you’ll see where it is located – on the Colarado River, North of Bergstrom Airport, just upstream of where Highway 183 crosses the Colorado River.

Getting a Site Catalog

Let’s narrow our search again.  To do that, we will Sort the 79 sites shown in our Excel worksheet by County.  Highlight the columns and rows 13-92 and columns F to K that have the Site data in them, beginning with the header row at the top and then use Data/Sort with Sort by County as the selected column.

Excel Tip: When you wish to highlight a set of cells in Excel, Press Ctrl-Shift and then → if you want to highlight cells to the right, or ↓if you want to highlight cells below. Thus, if you highlight the SiteCode cell at the top left of the table and hit Ctrl-Shift→ the cells to the right that are filled are highlighted, and then if you use Ctrl-Shift↓ the cells below are highlighted to the extent of the cells that have active contents.

If we now scroll down a little bit, we see that 41 of the 79 sites are located in Travis County.  What kind of information is available at each of these sites?  To answer that, let’s select all of the sites in Travis County (as shown below), right click, and select the Get Site Catalog for Selected Sites option under the HydroExcel context menu (Note that you cannot include any headers in the selected set of sites or the context sensitive menu for HydroExcel won’t appear).

Excel Tip: When you wish to highlight a set of rows whose values in one column are the same, you can use a Data/Filter.

Highlight all the cells in the Sites list as before, select Data/Filter, and you’ll see a set of highlight headers on the columns. Under the required column, deselect Select All and then click on the county that you want:

This step takes a few minutes as HydroExcel accesses the database and figures out what kind of daily data are available at each of these sites.  When the query is complete, the Site Catalog worksheet is automatically opened.  If we scroll through this worksheet, we can see that we now have more information about each of these sites and what type of data is available at each.  At station 08158920, for example, we can get average daily gauge height in units of feet and average daily streamflow in units of cubic feet per second (cfs).  Both datasets are available through the current date; the streamflow data starts in 1978, however, while the gauge height data starts in 1982.

Getting a Time Series

For our work, we are going to look at the mean daily streamflow at USGS station 08158000, the Colorado River at Austin, TX.  To download the time series associated with this variable, we select one of the cells that correspond to the streamflow at the station, right click, and select Download Time Series for NWISDIV:00060 – Discharge, cubic feet per second.

We are now moved to the Time Series worksheet and wait for a bit while the program downloads all the mean daily streamflow values available at this station.  It’s downloading over 40,000 values, so this may take a while!  When it is done you’ll hear a “Clunk” and the “Download Complete” box appears.

Once the data is downloaded, we now have values for mean daily streamflow at this location on the Colorado River for every day from 1893 to present.  Wow!  We should definitely see some variety in these numbers.

If you want a shorter period of record, Choose the Populate Time Series Parameters …. option (as shown below) and then you can fill in a new StartDate and EndDate in the Time Series spreadsheet and, and hit .

Summarizing Daily Time Series by Months and Years

Well, let’s look at them and see what we’ve got.  Go to the Statistics and Charts tab.  Here we have some macros programmed to look at the data that we just downloaded and make it easier to ingest.  By default, the tool makes a graph of all the data that we downloaded.  Since Excel can only graph 32,000 numbers and we have so much data, however, our graph is only going to go up through 1983 – which is when it runs out of space on the graph.  That’s OK for now, but we may want to adjust our dates in other attempts.

Notice that on this graph, we see the maximum, minimum, and average values that are reported for the time step that we’re graphing.  Since we’re graphing data for mean daily streamflow, however, the maximum, minimum, and average number are all going to be the same in this case.  Obviously we see lots of variety here.  We have some really low flows during the 1940, some really high flows in the mid to late 1930s and some more intermediate flows in between.

Let’s look at another time step.  Click on the chart (graph) area and a side bar will appear on the right hand side of your worksheet.  This side bar shows us what our plot is showing, in this case the Values of the mean daily streamflow displayed by DateTime.  The “Legend Fields” are denoted by Σ Values and in this case the legend displays Average, Max, and Min, according to the Axis Field DateTime.  In this case, the three values are the same for each day since we have only a single value in our data series for each DateTime.

Uncheck the DateTime box and instead select the Month box.  Our graph now shows us the maximum, minimum, and average mean daily discharge values during each month for our entire period of record.  That’s cool.  We can now see the fluctuations in these values over time.  Look at the seasonality here.  We see a lot of variation from month to month.

If you look on the left hand side of the display, you’ll see the numbers plotted in the graphs.  Excel time works in Julian days starting at Jan 1, 1900, so it doesn’t like data that fall in the 1890’s and summarizes them as a single value.  Lets ignore that for now.  Its clear from the graph above that the construction of the Highland Lakes had a significant effect on the flow characteristics of the Colorado River at Austin.

To be turned in:  Compare the monthly flow characteristics (average, max, min) of the Colorado River at Austin from 1900 to 1940 and from 1940 to present.   How has the construction of the Highland Lakes chain of reservoirs affected these characteristics?

We can view the same data, on a yearly basis, by unselecting the month box and selecting the year box.  Neat.

To be turned in:  Compare the daily flow characteristics summarized over years (average, max, min) of the Colorado River at Austin from 1900 to 1940 and from 1940 to present.   How has the construction of the Highland Lakes chain of reservoirs affected these annual flow characteristics?

Descriptive Statistics in Excel

Next, let’s take a 10-year portion of the data that we just downloaded and perform some statistics on it outside of HydroExcel.  Return to the Time Series tab and select the dates and values for the time from 1/1/1999 to 12/31/2008, and hit GetValues.  You’ll see that this download occurs a lot faster than the earlier one since you have only 10 years of data to acquire.

Copy thes data.  Open a new Excel workbook and paste the mean daily flow data from 1/1/1999 to 12/31/2008 into a new worksheet.  Save the workbook as “Ex1.xlsb”.

Excel has a number of statistical procedures built into it.  We will use some of these now.  First of all, let’s calculate some basic statistics of the mean daily flow values.  Under the Data tab, you should find the Data Analysis tool.  If the tool is not there, you have to activate it.

To activate the Data Analysis tool you choose the Office Button and then Excel Options and the bottom of the page that appears.

In the Excel Options window go to Add-ins tab (select on left); at the bottom of the box select the Analysis Toolpak, and then hit the Go… button next to the Manage Excel Add-ins dropdown box at the bottom of the page (its not enough just to double click on the Analysis Toolpak entry in the table).

.

In the Add-ins pop up box, activate the Analysis Toolpak by clicking in its check box.

In the Data tab in Excel, you should now see an Analysis box appear to the right of the Outline box.

Now we can use the Data Analysis tools.  First let’s use the tools to calculate the summary statistics for our 10-years of mean daily flow data.  Under the Data tab, select the Data Analysis tool, and select the Descriptive Statistics option.

The input range includes our 10-years of mean daily flow values (don’t include the column header); the output range is to the right of the input data (cell D1).  We choose to calculate the summary statistics and 95% confidence interval for the mean, for these data by activating its box and selecting OK.

Which produces the following result.   Excel calculates 13 common statistics for the dataset.  We now have a summary of this data and can get some insight on what we’re looking at.  We can see, for example, that the average daily flow during this time was 1,587 cfs, but at times it got up as high as 27,1000 cfs.  The standard error of estimate is 48.46 cfs (SQRT(Variance/Count)), and the 95% confidence limit on the mean is 95.0 cfs (1.96 * Standard error).  This means that the true mean streamflow for this period lies between 1587 +/- 95 cfs with 95% confidence.

These statistics look fairly horrible in so far as fitting a distribution to the data because they show that the data are highly skewed (6.15) and the mean is well above the median and mode.

If we take y = Log10(Q) and repeat this exercise, we get the following result, which looks a lot nicer.  Skewness is now near 0 and mean, median and mode are all consistent with one another.  This suggests that the daily streamflow data may be well fitted by a lognormal distribution.

To be turned in: What are the mean, median, standard error of estimate and 95% confidence level on the mean of the logarithms of the data?  What are these values when converted to cfs?  How do these compare with the descriptive statistics compiled directly from the data without taking logs?

Histograms in Excel

We can also get an understanding of our data by creating a visual.  One common example of data visualization is the histogram.  A histogram is a plot that shows the number of values that lie in various categories or “bins” of the data.  To develop a histogram, we must split our data into these bins.  Our summary statistics give us some guidance on how to define these bins.  Let’s start by dividing the data into equal bins of 1800 cfs each.  To do this, we simply type our bin divisions in the workbook as shown.

We then select the Data Analysis tool in the “Data” tab.  This time we chose the histogram option.  Our input data is, again, our 10-years of mean daily flows; the bins are the numbers that we just entered.  We chose to output the histogram data in the same spreadsheet.

We now have an output of the bins and the number of data values that lie in each bin.  The easiest way to understand this data is by plotting a bar graph of it, as shown.  This is how histograms are typically plotted, since it gives a good visual summary of how the data varies.

To be turned in:  Compute and compare the histograms of the data and of the logs of the data

To be turned in: Now go back to HydroExcel and use it to tell your own story about time trends in streamflow at another location anywhere in the United States.  Use the functionality of HydroExcel to show to show the characteristics of the flow and how they vary through time.  Why did you choose this story?  (Your story should include some plots and about a half-page of typed explanation.)

To be turned in:

(1)   Compare the monthly flow characteristics (average, max, min) of the Colorado River at Austin from 1900 to 1940 and from 1940 to present.   How has the construction of the Highland Lakes chain of reservoirs affected these characteristics?

(2)   Compare the daily flow characteristics summarized over years (average, max, min) of the Colorado River at Austin from 1900 to 1940 and from 1940 to present.   How has the construction of the Highland Lakes chain of reservoirs affected these annual flow characteristics?

(3)   What are the mean, median, standard error of estimate and 95% confidence level on the mean of the logarithms of the data?  What are these values when converted to cfs?  How do these compare with the descriptive statistics compiled directly from the data without taking logs?

(4)   Compute and compare the histograms of the data and of the logs of the data.

(5)   Now go back to HydroExcel and use it to tell your own story about time trends in streamflow at another location anywhere in the United States.  Use the functionality of HydroExcel to show to show the characteristics of the flow and how they vary through time.  Why did you choose this story?  (Your story should include some plots and about a half-page of typed explanation.)

(6)   User feedback on HydroExcel, as specified below

User Feedback on HydroExcel

We would like feedback on user improvements that could be made to HydroExcel.  If you have problems using this tool or see places where it could be improved, please note them as you do this exercise and then respond at the end to the following questions:

(1)    Installation – were you able to install the tool correctly?

(2)   Errors – Did you encounter any errors?  Is the error reproducible?  If so, please list the steps so that the developers can recreate the error.

(3)   User Interface – Is the user interface intuitive?  Are buttons labeled appropriately?  Is it clear where to input the parameters for each worksheet?

(4)   Explanatory Text – Is the text on the left side of each worksheet useful?  There's a lot of text there.  Can it be trimmed down?  Should more be added to it?

(5)   Operability – Is the spreadsheet responsive?  When things happen, do you get an appropriate message box or status message?

(6)   Distractions – List up to three things that annoy you about the spreadsheet.  If it annoys you, how could it do those things differently so as not to annoy you?

(7)   Capabilities – List up to three things that you wish the spreadsheet did that it does not currently do.

OK.  Now you’re done!