CE
397 Statistics in Water Resources
Exercise
3
Testing
Homogeneity of Suspended Sediment Data
By:
Virginia Smith, Brigit Afshar, Bryan Barnett, Brett Buff, and David Maidment
Environmen=
tal
and Water Resource Engineering
University=
of
Texas at Austin
February
2009
Contents
T-Test
for Difference in Means8
Introduction:
This exercise will be an investigation into meth=
ods
for testing the homogeneity of databases.&=
nbsp;
The tests that will we be using are t-test. Specifically, we will be applying =
the
paired t-test and the grouped t-test.
Sediment data will be used to experiment with these types of tests. =
Goals of this Exercise
Sediment data is useful for several types of
hydrologic investigations. Ho=
wever,
often records of sediment data are short or interrupted. Additionally, sediment data collec=
tion
methods vary greatly. In this
exercise we will look at the sediment record of one area using three sedime=
nt
datasets. We will use the paired t-tests and grouped t-tests to test the
homogeneity of sediment data. This
will be accomplished using statistical functions in Excel. The datasets for this exercise were
produced by the Texas Water Development Board (TWDB) and the USGS
Suspended-Sediment database.
Computer Requirements
This exercise will be done using Excel. The exercise was designed using th=
e 2007
version of Excel. To accompli=
sh the
exercise the Data Analysis Toolpak must be used. To use this tool it must first be
activated. The directions for=
this
can be found in the attached appendix.
The original data for this project can be downlo=
aded
in this spreadsheet: SedimentData.xls
The monthly discharge a=
nd
loading data for this exercise are at: Ex3Data.xls =
Data
The data to be used in this exercise was provide=
d by
TWDB and USGS. The TWDB data =
was
taken from monthly summaries presented in several “Suspended-Sediment
Load of Texas Streams” reports (e.g. See report 184 at http://www.twdb.state.tx.us/publications/reports/GroundWaterReports/=
GWReports/GWreports.asp
). The reports feature data f=
rom
sediment collections over a period of months or years for specific location=
s in
Texas. The sediment data in t=
hese
reports were collected using the “Texas Method” of sediment
collection. The “Texas
Method” of collection requires an 8-ounce narrow-neck bottle position=
ed
approximately one foot below the water surface near midstream. The percentage of suspended sedimen=
t by
weight obtained from the sample is multiplied by the factor 1.102 to obtain=
the
mean percentage of suspended sediment in the vertical profile. All of the TWDB stations are locat=
ed at
or near a USGS stream gage. T=
he
gage to be used in this exercise is number 07299200, at Prairie Dog Town Fo=
rk on
the Red River near Lakeview, Texas. The TWDB data are for the per=
iod
October 1964 through September 1975.
The USGS also provides sediment data for gage 07=
299200. However, the data provided by the =
USGS
covers a different period of record, October 1949 through July 1951, and us=
e a
different method of sampling. The USGS data was collected using
“Depth-Integrated Suspended-Sediment Sampling.” This method entails lowering and r=
aising
the sampler into the stream at a constant rate to continuously accumulate a=
representative
sample from a stream vertically.
The sampler itself is typically a simple tube shaped bottle with a
nozzle on the end. Over the y=
ears
the design of the sampler has changed slightly.
In addition to gage 07299200, the USGS also
collected data approximately
20.500 km upstream at gage 07298500, Prairie Dog Town Fork=
on
the Red River near Brice, Texas, for the period February 1979 through Septe=
mber
1980.. The data from gage 072=
98500
was also collected using the “Depth-Integrated Suspended-Sediment
Sampling.” Figure 1 sho=
ws a
map of the two locations. Figure 1 shows the location of the gages in relat=
ion
to each other. For the purpos=
e of
this exercise we will call the 07299200 gage location Site 1 and the 072985=
00
gage location Site 2.
USGS, TWDB Data USGS Data
![](3D"Exercise3_files/image002.jpg")
Figure
1: Location of the gages near Brice a=
nd
Lakeview Texas on the Red River.
Procedure:
You have been given the sediment loading data of=
a
sampling location in a stream near Lakeview, Texas at the locations pointed=
out
in the map. Two different methods, USGS Integrated Method and Texas Method,
were used to determine the sediment loading values (tons/day).
|
|
USGS1 |
USGS2 |
TWDB |
|
Method |
USGS Integrated Method |
USGS Integrated Method |
Texas Method |
|
Site |
Site 2 (near Brice, TX) |
Site 1 (near Lakeview, TX) |
Site 1 (near Lakeview, TX) |
The USGS values are from around the same section=
s of
the stream (Site 1 and Site 2) and the USGS2 and TWDB values were both
collected from Site 1 but with different methods. We need to determine if these data=
are homogeneous.
In other words, can we consider all of the data sets to be similar enough
despite that they sampled using different locations, and despite the differ=
ence
in location?
Descriptive Statistics
First, use the data to run Descriptive Statistics on the data using the Data Analysis ToolPak for Excel under the Data tab (see Appendix if the Data Analysis button is not avail=
able).
Run the Descriptive Statistics=
analysis
for each of the data sets individually: USGS1, USGS2, and TWDB.
![](3D"Exercise3_files/image004.jpg")
To
turn in:
Create plots of the sediment loading from each data set. In addition, compare and contrast =
the descriptive
statistics for each datasets. Based on the descriptive statistics alone, do=
you
think that the datasets can be considered homogenous with one another?
Histograms
Next, create histograms using the histogram func=
tion
on the data analysis toolpak for each of the data sets using the same metho=
ds
described in exercise 2. Be sure to include cumulative perce=
ntages
in your plots.
![](3D"Exercise3_files/image006.jpg")
To
turn in:
Show the histograms of the data sets and discuss similarities and differenc=
es
in the distribution of sediment loading.
Equality of the Variance
First, we need to determine which grouped t-test=
to
use: equal variance or unequal variance. We can determine the variance by u=
sing
an f-test. Perform an f-test t=
hrough
the Data Analysis button on US=
GS1
and TWDB.
Choose F-Test Two-Sample for Variances
![](3D"Exercise3_files/image008.jpg")
And fill in the data ranges
![](3D"Exercise3_files/image010.jpg")
Which produces this result: F-Test Two-Sample for Variances
|
|
USGS1 |
TWDB |
|
|
Mean |
218366.48 |
189388.4083 |
Mean |
|
Variance |
5.11143E+11 |
3.43838E+11 |
Variance |
|
Observations |
25 |
120 |
Observations |
|
df |
24 |
119 |
df |
|
F |
1.486580112 |
F |
|
|
P(F<=3Df) one-t=
ail |
0.085067046 |
P(F<=3Df) one-t=
ail |
|
|
F Critical one-tai=
l |
1.609204301 |
|
F Critical one-tai=
l |
The F statistic measures the ratio of the two
variances against a theoretical distribution (the F-distribution named after
R.A. Fisher) that is the ratio of a comparable pair of Chi-square
distributions. In this case, =
the
computed F-value of 1.48 has a p-value of 0.08. This means that there is only 8% c=
hance
that the difference between the variances is due to randomness alone. Lets assume the variances are uneq=
ual.
It is shown below that their coefficients of variation are actually almost =
the
same.
![](3D"Exercise3_files/image012.jpg")
T-Test for Difference in Means
Next, perform a t-test assuming unequal variance=
s using
the analysis toolpak the both datasets from Site 1. In this exercise we will be u=
sing
the t-test to compare two different sampling methods for sediment sampling,=
depth
integrated sampling (used by the USGS) and the Texas method (used by the TW=
DB). The t-test will determine if the d=
ata
can be said to be statistically not different from one another.
![](3D"Exercise3_files/image014.jpg")
Using the Data
Analysis button on the Data tab
of Excel, go to the t-Test: Two Sa=
mple
Assuming Unequal Variances
![](3D"Exercise3_files/image016.jpg")
Select all the data available in the USGS
(Variable1) and TWDB(Variable2) datasets, and do the test. Put the output Range in a new
worksheet.
The following result appears (the standard devia=
tion
and coefficient of variation values were added manually to the table below)=
. What this means is that the =
USGS
data have a mean of 218,000 tons/day and a standard deviation of 715,000
tons/day, while the TWDB data have a mean of 189,000 tons/day and a standard
deviation of 586,000 tons/day.
The difference in the means of the two datasets is 29,000 tons/day.<=
span
style=3D'mso-spacerun:yes'> This is a small number relat=
ive to
the standard deviations of the two datasets. The value of the computed
t-statistic is 0.189. General=
ly if
the absolute value of the t-statistic is less than 2 then the null hypothes=
is
cannot be rejected. That is w=
hat we
conclude in this case. The p-=
value
for the two-sided test is 0.85 and for the one-sided test is 0.425. These are both well above 0.05, so=
we
cannot reject the null hypothesis that the two datasets come from the same
population.
|
t-Test: Two-Sample Assuming Unequal Variances |
||||||
|
|
USGS1 |
TWDB |
|
|||
|
Mean |
218366.48 |
189388.4083 |
Mean |
|||
|
Variance |
5.11143E+11 |
3.43838E+11 |
Variance |
|||
|
Standard Deviation=
|
714942.97 |
586377.42 |
Standard Deviation=
|
|||
|
Coefficient of
Variation |
3.27 |
3.10 |
||||
|
Observations |
25 |
120 |
Observations |
|||
|
Hypothesized Mean
Difference |
0 |
Hypothesized Mean
Difference |
||||
|
df |
31 |
df |
||||
|
t Stat |
0.190 |
t Stat |
||||
|
P(T<=3Dt) one-t=
ail |
0.425 |
P(T<=3Dt) one-t=
ail |
||||
|
t Critical one-tai=
l |
1.696 |
t Critical one-tai=
l |
||||
|
P(T<=3Dt) two-t=
ail |
0.851 |
P(T<=3Dt) two-t=
ail |
||||
|
t Critical two-tai=
l |
2.040 |
|
t Critical two-tai=
l |
|||
To
be turned in:
Repeat this test assuming equal variances.=
Do you arrive at a different conclusion? Repeat this exercise compar=
ing
the USGS1 and USGS2 datasets, and comparing the USGS2 and the TWDB
datasets. Turn in the output =
for
the three t-tests. What do the
results of the test tell you? Which
datasets are more similar to one another?&=
nbsp;
Based on these tests, are the different methods, sampling periods, a=
nd
measurement locations, used for the USGS and TWDB datasets significant enou=
gh
to prevent us from combining datasets, or could the pooled dataset be used =
to
characterize the sediment transport rate at this location on the Red River?=
To be turned in:
(1) sediment lo=
ading
from each data set. In additi=
on,
compare and contrast the descriptive statistics for each datasets. Based on=
the
descriptive statistics alone, do you think that any of the datasets can be
considered homogenous with one another?
(2)
(3)&= nbsp; Repeat this= test assuming equal variances. Do = you arrive at a different conclusion? Repeat this exercise comparing the USGS1 = and USGS2 datasets, and comparing the USGS2 and the TWDB datasets. Turn in the output for the three t= -tests. What do the results of the test te= ll you? Which datasets are more similar to one another? Based= on these tests, are the different methods, sampling periods, and measurement locatio= ns, used for the USGS and TWDB datasets significant enough to prevent us from combining datasets, or could the pooled dataset be used to characterize the sediment transport rate at this location on the Red River?
Appendix
Excel has a number of statistical procedures bui=
lt
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 too=
l is
not there, you have to activate it.
![](3D"Exercise3_files/image018.jpg")
To activate the Data
Analysis tool you choose the O=
ffice
Button and then Excel Options<=
/b>
and the bottom of the page that appears.
![](3D"Exercise3_files/image020.jpg")
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 ju=
st
to double click on the Analysis Toolpak entry in the table).
![](3D"Exercise3_files/image022.jpg")
.
In the =
Add-ins
pop up box, activate the Analysis
Toolpak by clicking in its check box.
![](3D"Exercise3_files/image024.jpg")
In the =
Data
tab in Excel, you should now see an Analysis
box appear to the right of the Out=
line
box.
![](3D"Exercise3_files/image026.jpg")
![](3D"Exercise3_files/image028.jpg")
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.<= span style=3D'mso-spacerun:yes'> Under the Data tab, select the D= ata Analysis tool, and select the statistics option you would like to use.<= o:p>
![3D"ScreenHunter_03](3D"Exercise3_files/image030.jpg")