Assignment 6: Flooding
Name: FORMTEXT myUH ID# FORMTEXT In this assignment, you will investigate peak yearly discharges for Buffalo Bayou using data collected by a USGS stream gauge since 1949 (after construction of the Barker and Addicks reservoirs). Be sure to download the Excel file. You may need to get help from a TA in the GLC. Due Monday, May 2.
Rules Acknowledgement:
Before beginning the assignment, acknowledge that you know the rules of the assignment as listed above. Type “I understand answers need to be written in my own words in full sentences with the exception of values in tables. I also know screenshots/images need to be my own” (5 pts)
FORMTEXT Part 1: Basic analysis of peak yearly discharge data for Buffalo Bayou
Using the Excel data on the “Buffalo Bayou Discharge” sheet, create a scatterplot with the dates on the x-axis and the peak discharge on the y-axis. Select the scatterplot option that only has points plotted, no lines. Copy and paste your graph below. (6pts)
[Paste graph here]
Describe what the graph is showing you (how would you explain this graph to someone else?). (4pts)
FORMTEXT You may be able to notice a trend or pattern in your graph. To more accurately determine that pattern, create a linear trendline and display the R-squared value.
What is the R-squared value? (4pts)
FORMTEXT Describe the trend in your data and what it means. (4pts)
FORMTEXT Some people may be quick to blame climate change for this trend. The “Houston Rainfall” tab in the Excel document contains yearly rainfall totals for Houston since 2001. Create a scatterplot of the data with years on the x-axis and rainfall total on the y-axis. Paste your graph below. (5pts)
[Paste graph here]
Create a linear trendline for this data. Has there been any significant change in the amount of rainfall that Houston receives over the past 21 years? (4pts)
FORMTEXT What else do you think is responsible for the trend you described in part a? (5pts)
FORMTEXT READ CAREFULLY: There is a trend as to what month or months of the year Buffalo Bayou reaches its peak discharge, which indicates our rainy seasons. First, you’ll need to determine what month each peak discharge occurs. Using the empty column to the left of the Date column, type =TEXT(A2,”mmmm”). Copy this formula down the rows. This will extract the name of the month from each date. Now, you need to figure out how many times each month had a peak discharge on the bayou. We are going to use a new function in Excel, the countif function. The names of the months are in column G. Go to cell H2 and type =COUNTIF($A$2:A$75, G2). This will look at all the names of the months in column A and tell you how many times January (which is the G2 value) appears. Copy that formula down to December to see the data for each month.
Fill in the table: (6pts)
Month Number of Peak Discharges
January FORMTEXT February FORMTEXT March FORMTEXT April FORMTEXT May FORMTEXT June FORMTEXT July FORMTEXT August FORMTEXT September FORMTEXT October FORMTEXT November FORMTEXT December FORMTEXT Now create a line graph of this data with months on the x-axis and the number of peak discharges on the y-axis. Use the “Line with Markers”. Be sure to use the line graph, not the scatter plot. Paste your graph below. (4pts)
[Paste graph here]
Interpret your graph and discuss what time or times during the year Buffalo Bayou is most likely to reach its peak discharge. Which time or times of the year is it least likely to reach peak discharge? What does this say about our seasonal weather? (7pts)
FORMTEXT Part 2: In-depth analysis of flooding recurrence on Buffalo Bayou
In the Excel file, complete the rank column on the “Buffalo Bayou Discharge” sheet by ranking all of the peak discharges from 1 to 74, with 1 being the highest discharge and 74 being the lowest discharge. What are the dates of the highest and lowest peak discharges? (6pts)
Date of highest discharge (1): FORMTEXT Date of lowest discharge (74): FORMTEXT Now calculate the recurrence intervals for each discharge. N is the number of years on record (74) and m is the rank. What are the recurrence intervals (R) for the following peak discharges? (6pts)
Date Discharge R
5/26/15 17,400 FORMTEXT 9/13/08 10,100 FORMTEXT 3/12/97 7,650 FORMTEXT Create a scatter plot with recurrence interval on the x-axis and discharge on the y-axis.
Copy and paste your graph below. (6pts)
[Paste graph here]
Which type of trendline best fits this data? (3pts)
FORMTEXT Add the trendline and show the line equation and R-squared value. What is the R-squared value for this data? (2pts)
FORMTEXT You’ve probably heard that Hurricane Harvey was a 100+ year storm event. However, your current graph doesn’t show that. In your data, what is the recurrence interval for the flooding from Hurricane Harvey and why isn’t it over 100? (5pts)
FORMTEXT
Using your trendline equation, we can calculate what the discharge of a 100-year event should be. You know what m, x, and b are in the equation, just substitute the values. What is the discharge for the 100-year event based on the trendline? (5pts)
FORMTEXT The discharge in Buffalo Bayou for Hurricane Harvey was 32,600 cfs, which is very different from the 100-year discharge you calculated in question 10. So, what is the true recurrence interval for a discharge of 32,600 based on your equation? For this question, we’ll need to solve for x in the line equation instead of y because now we know what y (32,600 cfs) is and need to figure out x. You can manipulate the line equation variables to isolate x; we’ve done this on several previous assignments. Enter this equation in excel =exp((y-b)/m) and substitute the values accordingly. What is the recurrence interval for the stream discharge associated with Hurricane Harvey? (5pts)
FORMTEXT Complete this questionnaire about assignments and the use of Excel and Google Earth in this class. You’ll need to be logged into your cougarnet account. (8pts)
