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

&nbs= p; Correlation between Variables

&nbs= p; Autocorrelation

&nbs= p; Effects of Time-Averaging on Correlation

&nbs= p; 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 fo= ur 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 Ex= cel (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.&nbs= p; To create your ce-lrc account, please contact Danny Quiroz, quiroz@mail.utexas.edu 471-4016, a= nd 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 enrolle= d in a CE course)

Procedure Part 1: Correlation between Variables<= /o:p>

Barton Creek and Bu= ll Creek are two well known streams in the Austin area. This part of the exercise investig= ates the correlation between streamflow on Barton Creek and Bull Creek. Changes in flow can happen very qu= ickly 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 b= ecause the Bull creek site is not part of the USGS instantaneous data archive.

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

Station 2 =3D Bull Creek at Loop 360, Austin

LCRA’s Hydrom= et 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 ta= ble of streamflow values on Barton Creek and Bull Creek every fifteen minutes f= or 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 f= or statistical analysis. Also, E= xcel 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.&n= bsp; Pearson’s r statistic

2.&n= bsp; Kendall’s tau

3.&n= bsp; 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 throu= gh 6/30/2008 plotted on the same time axis.&n= bsp; 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 the= se data that both creeks had a few storm events during this period. Now let’s use SAS to investi= gate a relationship between the flow in these two streams.

Working with SAS

Now let’s tak= e 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 w= rite a series of commands describing what you want the program to do, then you sub= mit the commands and the program returns the results. One advantage of this type of syst= em is that when you are finished, you have the list of commands that you submitte= d so you can duplicate your analysis. The data that we want = to look at is in a file called Ex4SAS.csv.&nb= sp; The extension ‘.csv’ stands for comma separated values, a fairly universal file type.

Open SAS and take a look around. The k= ey 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 t= he 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 IMPOR= T OUT=3D WORK.Flow

= DATAFILE=3D "Z:\Stats in Water Resources\Ex4 Prep\EX4SAS.csv&qu= ot;

= DBMS=3DCSV REPLACE;

GETNAMES=3DYES;<= o:p>

DATAROW=3D2;

RUN;<= /o:p>

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

To file your file, you need change t= he Files of type to All Files at the bottom of the screen below.<= /p>

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 s= pecified here is to create a dataset called “Flow” and to store it in the SAS workspace. The file for t= he dataset is the csv file. GETN= AMES says that the first row has names of the variables, and DATAROW says that t= he 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 tak= e a look at what got imported. Add this text to your program<= /p>

PROC PRINT data=3Dflow (OBS=3D15);=

RUN;<= /o:p>

Save your altered progam:

Click submit select= ion. Now go look at the Output window. Wh= at SAS has done is to print the first 15 observations (OBS=3D15) 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 thi= s to work is something of a dark art in SAS so we will ignore the times for now. The key thing is that th= e 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 y= our progam and save the resulting amended program:

PROC GPLOT data=3Dflow;

&= nbsp; PLOT Bull_Flow*Barton_Flow;

&= nbsp; RUN;

Then choose ‘= submit selection.’ And Viola! A graph pops up. Examine the plot of streamflow ver= sus streamflow with the time series plots.&nbs= p; 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 progr= am:

PROC CORR data=3Dflow;

&= nbsp; 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 me= ans that the correlation between the flow is statistically very significant.

No= w if we want to compute Kendall’s Tau correlation coefficient, the command= s to SAS only change slightly:

&= nbsp; PROC CORR KENDALL data=3Dflow;

&= nbsp; VAR Barton_Flow Bull_Flow;

&= nbsp; RUN;

And for Spearman’s Rho, the commands are:

PROC CORR SPEARMAN data=3Dflow;

&= nbsp; VAR Barton_Flow Bull_Flow;

&= nbsp; RUN;

In the SAS language KENDALL and SPEARMAN are options for the correlation procedure.<= /span>

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 s= teps 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 correlati= on.

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<= /b> under the tab Part2. The period is April 1 th= rough June 30, 2006. The data for B= arton Creek have a five minute time interval and those for the San Marcos River h= ave a 15 minute time interval.

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