Calculate the amount of new event water in the stream at each time point using isotope and discharge data

Students will quantify the fractions of pre-event and event water during a storm.
Students will discuss what insights the hydrograph discuss flow generation mechanisms and hydrologic behavior of the study watershed.

Students will manipulate data in Excel or another program to perform a series of calculations.

2-3 hours

7 selected data sets downloaded from Hydroclient in a previous step
Graphs of stream discharge and isotopes and precipitation isotopes created in a previous step
Decisions about old water and new water endmembers made in a previous step.

Excel, R, Matlab, or any spreadsheet or data analysis program of the student's choice. The specific examples and screenshots will use Excel for Mac 2011.

Rearrange the two endmember mixing model formula to solve for the unknown quantity: the amount of new water in the stream

We've defined two endmembers for our hydrograph separation: new (event) and old (pre-event) water. The water in the stream is a mixture of those two endmembers, so we use what is called a two endmember mixing model to separate the hydrograph into those components. The equation for a two endmember mixing model is:

(equation 1)

where Qs is the discharge in the stream, Qn is the discharge that comes from the new water, Qo is the discharge that comes from old water, δs is the isotopic composition measured in the stream, δn is the isotopic composition of the new water endmember, and δo is the isotopic composition of the old water endmember.

From the data we downloaded in Hydroclient, we know Qs and δs. Based on the decisions we made about endmembers, we think we know δn and δo. That means that the only variables we don't know are Qn and Qo. Fortunately,

Qs = Qn +Qo (equation 2)

We can rearrange that to solve for the quantity of old water:

Qo = Qs - Qn (equation 3)

Then:

(equation 4)

Next we need to apply this equation to our real data, but first it would be helpful to organize the data to make this job easier.

Organize the dataset to match stream water isotope samples to discharge measurements

In hydrology, we are often faced with the problem of dealing with data collected at different frequencies. Many sensors that can autonomously take data frequently, but we are usually more limited in terms of data that comes from water samples that must be collected and brought back to the laboratory for analysis. In this case, we have discharge at 15 minute intervals, but isotope data in the stream every 1-2 hours. To further complicate matters, the time points of the isotope samples and the discharge measurements don't quite match up.

There are two basic approaches to dealing with this frequency mismatch. You can interpolate between infrequent water sample data points or you can reduce the resolution of the sensor data. The second approach is what we will do here. There are some elegant ways to do this, but we will take a more manual approach that works in Excel and is fairly simple. Since we are going to do some things manually, now might be a good time to make a new working file for the discharge data and save it in a place and with a name that makes sense to you. After you have a new working file, you might also want to just delete the discharge points before April 3rd and after April 6th, since we won't be doing any isotope calculations with them. This will make your dataset smaller.

The isotope data are collected at four minutes after the hour for the first 23 hours of the storm. Since the discharge data are reported in 15 minute intervals, we want to select every fourth measurement, picking the ones that are 8 minutes after the hour. You could do this manually, but there is a slightly more elegant way to do it. Create a new column, and add the numbers 1-4 in it next to the first four time points in your dataset (starting with a data point that is 8 minutes after the hour). Now, next to the 5th row of data, write a formula that reference the cell where you wrote number 1. Then, copy this formula down to the bottom of your dataset. The end result should be a repeating series of 1-4, with the 1 always next time the data collected 8 minutes after the hour.



Copy that column, and then do a paste special to replace the formulas with values. Now you can use the sort button to sort your data by number. Once you've done that you have an hourly dataset collected at 8 minutes after the hour next to your number 1s. Next to your number 2s, you have an hourly dataset collected at 23 minutes after the hour, etc.



When you have your data arranged this way, it should be pretty easy to match up discharge values with each isotope sample for the first 23 hours. But watch out! On April 4, after 3:00 pm local time, the sampling interval changes. Now the isotope data occurs every 2 hours, collected at 51 minutes after the hour. At this point, you can decide to do another set of sorts, making a repeating series of 1-8 to capture the 2 hour desired interval, or to manually associate the discharge measurements with the sampling times. In the end, however, you do it, you want to know the discharge that was measured at the time closest to each isotope measurement.

Calculate the new water discharge for each time point

You are finally ready to find the new water discharge in the West Branch of the Mahoning River. In Excel or the program of your choice, you want to write an equation that uses equation 4. Remember that the old water and new water endmembers (delta-o or delta-n) are going to be constants, and that the Qs and delta-s terms change with each time point. In Excel, you should be able to write an equation that you can copy down your column, so that each row's calculations are done automatically.

If you have written your equation correctly, your new water discharge will be less than your total discharge. If that's not the case for all or most of your data, chances are good that you've made an error in your equation somewhere. If most of your time points have a new water discharge that is less than the total discharge at that time, but a few points are showing greater new water than total discharge, consider your endmembers. In order for a mixing equation to work, your endmembers need to bracket the mixed dataset (i.e., the stream isotopes). In other words, one endmember should be isotopically more negative while the other endmember is isotopically more negative than the stream water. If that's not the case, you can't do a separation for that time point and it points to problems either with the isotopic analyses or with the choice of endmembers.

You may want to make a hydrograph showing your total discharge and your new water discharge. You can also easily calculate your old water discharge using equation 3.

Calculate the fraction of new water at peak streamflow

Identify the time point with the highest total discharge and divide the new water discharge by the total discharge to calculate the fraction of new water at peak streamflow. You can also convert this to a percent.

Calculate the fraction of new water for the storm event as a whole

To calculate the fraction of new water for the event as a whole, we want to convert a series of discharge numbers, which represent fluxes (cubic meters per second) into volumes (cubic meters) over the whole timespan that we are taking them to be representative of. For example, if you have hourly data, you want to multiply discharge by the number of seconds in an hour (3600 s/hr) to calculate a volume of streamflow in that hour. Do this for every time point in your total discharge and new water discharge columns, paying attention to the changing sampling frequency of your dataset. Be smart and use equations where possible.

Once you have a volume associated with each time point, you can sum the numbers for the total discharge volume, to get a total volume over the whole event. You can do the same for the new water discharge volumes. Then divide the new water volume by the total volume to get a fraction of new water for the storm event as a whole.

Compare your new water fractions for peak discharge and the whole event to the literature.

In humid, forested regions, new water is typically <50% of the flow at the time of peak discharge and is typically 20-40% of the total event volume. Agricultural areas tend to have more new water, and urban areas tend to have even more new water, especially at the time of peak discharge. Wetlands and impoundments in the watershed tend to decrease the fraction of new water.

Consider what the results of your hydrograph separation tell you about likely streamflow generation mechanisms and hydrologic behavior of the West Branch of the Mahoning River.

Based on what you have learned in other parts of your hydrology education, what do you think the fraction of new water you calculated for the West Branch of the Mahoning River tells you about streamflow generation mechanisms in the watershed?

Optional additional work: Evaluate the effects of the ambiguity in choice of isotopes and endmembers on the results of your hydrograph separation.