CE 397 Statistics in Water Resources

Exercise 4

Correlating Streamflow

by:

Eliora Bujari, Brad Eck, Bryan Enslein, Eric Hersh and David Maidment

University of Texas at Austin

February 2009

Contents

Introduction

Goals of this Exercise

Computer Requirements

Procedure

Correlation between Variables

Autocorrelation

Effects of Time-Averaging on Correlation

Lagged Cross-Correlation

To be turned in

Introduction

In this exercise we will explore how to deal with correlation.  How is one variable correlated with another?  How is one variable correlated with itself in time?  How does this correlation change if you average through time?  How are variables correlated with each other in time and space?

Goals of this Exercise

To address these questions, we will embark on four brief exercises, one to illustrate each concept: (1) correlation between variables (Kendall’s tau, Spearman’s rho, and Pearson’s r); (2) autocorrelation; (3) effects of time-averaging on correlation; and (4) lagged cross-correlation.

Computer Requirements

This exercise is to be performed in Microsoft Excel (2007 version used here) and SAS.  SAS is available in the LRC on all of the computers in ECJ 3.301 (the first door on the right).  The data for this exercise are at: http://www.ce.utexas.edu/prof/maidment/StatWR2009/Ex4/Ex4Data.zip

This exercise uses the SAS package operating in the Civil Engineering Department’s Learning Resource Center, located on the third floor of ECJ, in room ECJ 3.301 which is the first room to the right that you pass going past the Proctor’s Office in the LRC.  If you do not already have a CE-LRC login, you will need to get one before you can access this system.  To create your ce-lrc account, please contact Danny Quiroz, [email-masked] 471-4016, and provide him your name, phone number, email address, and UTEID.    Tell him that this is for CE 397 Statistics in Water Resources.  I have sent Danny a list of the students enrolled in this course so that he is alerted to who is eligible for these accounts (you have to be enrolled in a CE course)

Procedure Part 1:  Correlation between Variables

Barton Creek and Bull Creek are two well known streams in the Austin area.  This part of the exercise investigates the correlation between streamflow on Barton Creek and Bull Creek.  Changes in flow can happen very quickly on these creeks so we will look at 15-minute data to try and capture some of the variations.  The data that we’ll use come from the Lower Colorado River Authority’s website for Hydrologic and Meteorological data (http://hydromet.lcra.org ).   LCRA data was used because the Bull creek site is not part of the USGS instantaneous data archive.

Station 1 = Barton Creek at State Highway 71 near Oak Hill

Station 2 = Bull Creek at Loop 360, Austin

LCRA’s Hydromet doesn’t provide the data in as nice of form as the USGS so some pre-processing was used to make the dataset that you will use.

The dataset is a table of streamflow values on Barton Creek and Bull Creek every fifteen minutes for the first six months of 2008.  There are 17,464 records!  Due to the large number of records, this is a good time to look at SAS, a handy tool for statistical analysis.  Also, Excel does not have statistical correlation functions to calculate some of the coefficients that we’re interested in.   A sample of the data in Tab Part1 of Spreadsheet Ex4Correlation.xlsx  are shown below.

Correlation is a measure of the relationship between variables.  There are three ways to measure correlation that we will look at in this exercise:

1.      Pearson’s r statistic

2.      Kendall’s tau

3.      Spearman’s rho

To get a first look at these data, take a look at the two time series.

To be turned in:  A plot of the flow at Barton Creek and Bull Creek for 1/1/2008 through 6/30/2008 plotted on the same time axis.  It might be useful to use a log scale for the flow to show the variations of the low flows more clearly.

We can see from these data that both creeks had a few storm events during this period.  Now let’s use SAS to investigate a relationship between the flow in these two streams.

Working with SAS

Now let’s take a look at using SAS.    How about a little orientation in for the SAS window. SAS works on something of a command line interface.  You write a series of commands describing what you want the program to do, then you submit the commands and the program returns the results.  One advantage of this type of system is that when you are finished, you have the list of commands that you submitted so you can duplicate your analysis.    The data that we want to look at is in a file called Ex4SAS.csv.  The extension ‘.csv’ stands for comma separated values, a fairly universal file type.

Open SAS and take a look around.  The key features of the SAS window are the Editor pane, where you enter commands, the Log window that records what you did and other details of the processes, and the Output window that shows the results.

To import the data into SAS, type the following commands into the editor window (or copy and paste).  NOTE: You will need to change the datafile path to where your file is:

PROC IMPORT OUT= WORK.Flow

DATAFILE= "Z:\Stats in Water Resources\Ex4 Prep\EX4SAS.csv"

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

To avoid having to type all this stuff over again if you make a mistake, another way to do this is to write the program in Notepad and store it as a text file Part1.txt.  You can then access the program with File/Open Program

To file your file, you need change the Files of type to All Files at the bottom of the screen below.

You can execute this program using the “Submit” command, which is the little running person at the top of the menu bar .

What you’ve specified here is to create a dataset called “Flow” and to store it in the SAS workspace.  The file for the dataset is the csv file.  GETNAMES says that the first row has names of the variables, and DATAROW says that the actual data starts in row 2.  All SAS procedures must end with a RUN; statement.  When you submit these commands to SAS it goes off and imports the data.

Now let’s take a look at what got imported. Add this text to your program

PROC PRINT data=flow (OBS=15);

RUN;

Save your altered progam:

Click submit selection.  Now go look at the Output window.  What SAS has done is to print the first 15 observations (OBS=15) of the flow dataset.  So you can see what was imported here.  Note that all of the Date_Times appear to be the same.  This happens because our import routine did not convert the data as dates with times.  Getting this to work is something of a dark art in SAS so we will ignore the times for now.  The key thing is that the flow data is correctly paired between the creeks.

Now lets see about correlation between these streams.  First let’s look at the data and make a plot.  Type the following commands into your progam and save the resulting amended program:

PROC GPLOT data=flow;

PLOT Bull_Flow*Barton_Flow;

RUN;

Then choose ‘submit  selection.’  And Viola! A graph pops up.  Examine the plot of streamflow versus streamflow with the time series plots.  Can you see the spikes on the time series appear in the X-Y plot?

Now let’s use SAS to calculate the correlation coefficients that we’re interested in.  Type the following into your program:

PROC CORR data=flow;

VAR Barton_Flow Bull_Flow;

RUN;

Save the resulting file and Submit it for execution

What these commands say is to run the correlation procedure (PROC CORR) on the flow dataset with the variables Barton_Flow and Bull_Flow. Now check the Output window.

The output gives us some summary statistics about each variable, and calculates the Cross-correlation matrix between the two flow series, the correlation coefficient between the variables (0.4705) and also the p-value for the variable (<.0001).  This means that the correlation between the flow is statistically very significant.

Now if we want to compute Kendall’s Tau correlation coefficient, the commands to SAS only change slightly:

PROC CORR KENDALL data=flow;

VAR Barton_Flow Bull_Flow;

RUN;

And for Spearman’s Rho, the commands are:

PROC CORR SPEARMAN data=flow;

VAR Barton_Flow Bull_Flow;

RUN;

In the SAS language KENDALL and SPEARMAN are options for the correlation procedure.

Save your program since this is the last step in this Part of the Exercise.    You can use this program later for other purposes or modify it to do new things as we learn more about SAS.

SAS lists all the steps you’ve executed in the Results pane. You can choose one part of your output to display by expanding and highlighting it.   You can also right click on these results entries and delete them if you want to run the whole process again.

TO BE TURNED IN: (a) SAS plot of streamflow against streamflow. (d) Calculate all three correlation coefficients, and discuss the differences among them.

Procedure Part 2:  How are variables correlated in time?

Is tomorrow’s streamflow affected by today’s?  Is streamflow a month from today affected by today’s streamflow?  How about for rainfall?  In this section, we will look at the dependence of a random variable upon itself through time by calculating autocorrelation, sometimes known as serial correlation.

We’re going to use high-resolution streamflow data from the USGS Instantaneous Data Archive (http://ida.water.usgs.gov/ida/) for two local gages: #08155200, Barton Crk @ SH71 nr Oak Hill, TX and #08170500, San Marcos Rv @ San Marcos, TX.  The period we are evaluating is spring 2006 and the data has been provided for you in the spreadsheet Ex4Correlation under the tab Part2.   The period is April 1 through June 30, 2006.  The data for Barton Creek have a five minute time interval and those for the San Marcos River have a 15 minute time interval.

First, let’s take a look at the data.  On one graph, plot a hydrograph for each gage with streamflow on the y-axis and time on the x-axis.  Notice the flashiness of Barton Creek (upstream of the springs) versus the stability of the groundwater-dominated San Marcos River.

To be turned in:  A plot of the Barton Creek and San Marcos River data

Next, we’re going to calculate the autocorrelation for each time series.  In Excel, use the CORREL function to correlate the entire time series for Barton Creek with lagged values of the same time series.  In the cell below that, correlate the entire time series with the same time series, but shift the second one down one cell.   Repeat this calculation, lagging one additional time step in each subsequent cell, until the autocorrelation equals zero (see screen capture).

The horizontal axis of this chart is the lag time in days.  Plot this out to 10 days.

For the first value, use Excel Correl function as

=CORREL($B$3:$B$15000, $B3:$B15000)

which gives a value of 1.  Then for the lag 1 value, use

=CORREL($B$3:$B$15000, $B4:$B15001)

Which gives a value of 0.9997, and so on.

Repeat this for the San Marcos River.

On one graph, plot the calculated autocorrelation for each gage as a function of the cumulative time.  This autocorrelation plot is sometimes called a correlogram.

From the autocorrelation statistic, a correlation length can be calculated which describes the memory of a process.  In our case, the correlation length tells us for how long future streamflow measurements will typically be dependent on the flow in the river right now.

Let’s calculate the correlation length for each gage.  To do this, we need to integrate the area under the autocorrelation functions.  In Excel, we can approximate this integral by summing the area under the curve for each time step:

The cumulative sum of the approximate areas is the correlation length; since we multiplied a dimensionless number (the averaged correlations) by a time increment, the resulting correlation length is in the units of time we used in our calculation.  Be careful with how you display and calculate dates and times here- the results will be in fractions of Julian days if you use the provided data so you may want to create a more meaningful measure of time (like cumulative days or hours).

What is the correlation length for each gage?  How do they compare?  What do you think explains the discrepancy?

To be turned in:  A plot of both correlograms.  Autocorrelation length for each gage with a comparison and brief discussion.

Procedure Part 3:  What effect does averaging have on correlation?

Data:  Barton Creek Streamflow from Q2.

What effect does time interval averaging have on a sample’s correlation length? Do you expect the correlation length to increase or decrease as you increase the time step? What would you expect the correlation length to be from a larger time step than is being used in this problem?

Problem 3 looks into the effects of correlation length to the time interval that the flow is averaged over. Since all flows rates are given as flows averaged over a finite time step (15 minute, daily, monthly etc.), how do these different averaging intervals change the way the data behaves? Can a correlation length still be calculated if the interval length is greater, and if so how useful is this data?

Using the same 5 minute Barton Spring dataset from problem 2, a worksheet Part3 of Ex4Correlation has been developed that provides each datavalue in terms of a different time step, 15 minute, 1 hour and 1 day. Using Excel’s pivot table function, the data can be averaged according to each of these time steps.

Choose a time interval by selecting its field from the “Field List” and copy and paste the pivot table outputs onto another area of the spreadsheet to be worked on later. Repeat this for every time interval. Make sure that the correct time interval is in the “Row Labels” box and “Average of Flow” is the only field in the “Values” box. The result should be three datasets that average flow over 15 minute, 1 hour and 1 day intervals.  Here is the result for hourly data

And here is the result for daily data:

Run the same auto correlation test on each of these data sets and determine the correlation length of each interval. Finally, plot these correlation lengths against their time interval to determine each interval’s relative influence on the dataset. When calculating the integral of the autocorrelation make sure to keep in mind the units being used for each interval.

To be turned in: The plots of each time interval’s autocorrelation as well as a plot of time interval versus correlation length.  Do these plots make sense? What would you expect from correlation lengths of more time intervals?

Procedure Part 4: Lagged Cross Correlation

Cross-correlation is an analysis used to determine if there is a relationship between two time series, in this case streamflow and precipitation, where a time-lag is applied to one of them. The range of the cross correlated coefficients should be between -1 and 1. The closer it is to 1 the closer the two series of sets are.

The following is an analysis of how to asses cross-correlations between two time series. Dripping springs and Barton Creek Hill are two station in the below watershed that measure precipitation and streamflow, respectively. Data from the two stations were retrieved from LCRA Hydromet and imported into Excel, shown in the worksheet Part4 of Ex4Correlation.

Using Excel function CORREL compute the cross correlation values for both stations by lagging precipitation against streamflow. Plot the streamflow and precipitation and then plot the cross correlation values as a function of lag time.  If the two sets show a structured pattern then there is a relationship between them, and a random pattern indicates that the two time series are uncorrelated.  Now you can calculate the lag time from the graph which is 300 min from looking at the first peak.

To be turned in: A plot of cross correlation of streamflow and precipitation for Dripping Springs and Barton Creek near Oak Hill. Determine the lag time and discuss your results.

To be turned in

(1)    A plot of the flow at Barton Creek and Bull Creek for 1/1/2008 through 6/30/2008 plotted on the same time axis.  It might be useful to use a log scale for the flow to show the variations of the low flows more clearly.

(2)   SAS plot of streamflow against streamflow. (d) Calculate all three correlation coefficients, and discuss the differences among them.

(3)   A plot of the Barton Creek and San Marcos River data

(4)   A plot of both correlograms.  Autocorrelation length for each gage with a comparison and brief discussion.

(5)   The plots of each time interval’s autocorrelation for Barton Creek and the San Marcos River as well as a plot of time interval versus correlation length.  Do these plots make sense? What would you expect from correlation lengths of more time intervals?

(6)   A plot of cross correlation of streamflow and precipitation for Dripping Springs and Barton Creek near Oak Hill. Determine the lag time and discuss your results.