Water Data Boot Camp: Fall 2019

Unit 1: Task3
Evaluating impact on minimum flows

Background: Minimum flows & "7Q10"

The passing of the Clean Water Act in 1972 and the Endangered Species Act in 1973 has resulted in many reservoirs having to meet downstream flow requirements for either water quality purposes or species protection. For example, at the Clayton gauge, minimum flow requirements have ranged from 184 to 404 cfs since 1983. Here we want to see if the establishment of Falls Lake has raised minimum flows.

There are many ways to approach low flow to understand how minimum streamflow has changed since Falls Lake was constructed. We will look at a common metric known as 7Q10. 7Q10 is the lowest average discharge over a one [week/month/year] period with a recurrence interval of 10 years. This means there is only a 10% probability that there will be lower flows than the 7Q10 threshold in any given year.

To get more practice with pivot tables and if statements, we will calculate this metric using the 7 month period. To do this we need to construct a rolling average of monthly discharges spanning 7 month, which we can do using a series of pivot tables.

The first pivot table aggregates our daily discharge data into total monthly discharge values for each year. From this we table, we can compute a 7-month rolling average of minimum-flows from the given month's total discharge and those from 6 months preceding it.

Next, we construct a second Pivot Table from the above data. This one aggregates the monthly data by year, extracting the minimum of the 7-month average for each year. This will enable us to compute a regression similar the one we constructed for the flood return interval, but this regression is to reveal the recurrence interval of low flows so that we can determine the streamflow of a 10% low flow event.

We then sort and rank these annual monthly-minimum values, similar to how we computed flood return intervals to compute 7 month minimum-flow (7Q) return interval and then the low flow probability of recurrence (POR) of these flows, again using the same methods used for calculating flood return intervals and probabilities of recurrence. From this we can compute a regression between our yearly 7Q flows and POR, and use that regression equation to determine 7Q10, or the expected minimum flow across a span of 10 years.

The analysis

1. Compute average daily discharge by year and month via a Pivot Table

 

2. Create a static copy of the Pivot Table values

3. Fill all the blanks in the year column with the appropriate year.

4. Calculate the 7-month minimum flow averages (i.e., "7Q")

5. Create a new pivot table listing the minimum 7Q flow for each year, using the above table as its source.

6. Compute the rank, return interval, and probability of recurrence of these minimum flows

Fig13X_Pivot2Result.jpg

7. Plot the 7Q flow (Y) against the Probability of Recurrence (X)

8. Use the equation to estimate the 7Q10, i.e., the threshold where the 10% of the observed flows are smaller:

9. Apply your results: How many months in the monthly Pivot Table fell below the estimated 7Q10?

Here we want to produce a plot that shows when and how frequently low flows have occurred. We do this by first created a new column of just the monthly discharges falling below our 7Q10 threshold, and then creating a plot where these are highlighted against all monthly discharge values.

10. Count the number of Q710 events per year:

Continued practice