The Roaring Fork River in Colorado stretches about 70 miles from Independence Pass, east of Aspen, to Glenwood Springs , where it meets the Colorado River. Though the Roaring Fork Watershed (pictured below) represents only about 1% of the greater Colorado River watershed (~1,400 square miles, about the size of Rhode Island), it is responsible for 11% of the water that flows into Lake Powell via the Colorado.
Despite this high flow rate, not all of the water that flows into the Roaring Fork watershed ends up in the Colorado River. Tunnels, known as transmountain diversions, transfer water from the Western Slope of the Continental Divide to the Eastern Slope, supplying the cities of Leadville and Denver. (For more on this, check out this article).
As seen in the map above, the main diversion tunnel from the Roaring Fork watershed is the Twin Lakes tunnel and reservoir system. I intended to explore the relationship between the rate of flow of the Roaring Fork River and the amount of diversion in the Twin Lakes tunnel. Below is the work that went into producing the plot at the bottom of this article, whic is explained along the way.
Thanks to several organizations, we are able to access flow rate data for not only the Roaring Fork River but also for the Twin Lakes diversion. The United States Geological Survey (USGS) maintains flow rate gauges along all major rivers in the Roaring Fork Valley, and the Colorado Department of Water Resources publishes flow rate information for the diversion tunnels. In the map below, stream gauges are marked in blue, and the tunnel diversions are in yellow.
from IPython.display import IFrame
IFrame("https://www.google.com/maps/d/embed?mid=zteYjf_pZ-j0.klHGdffc3ZXM&hl=en_US", 640, 480)
Because I wanted to see the effect of the Twin Lakes Tunnel (data here) on the Roaring Fork River, I chose the USGS stream gauge at the Difficult Campground, which is the closest gauge to the Twin Lakes tunnel diversion. The following steps use data downloaded from the links above and load it into a Pandas DataFrame object.
2012 was an interesting year because it was a drought. Let's start with that.
import pandas as pd
import plotly.plotly as py
import plotly.graph_objs as graphobjs
import dataparsers
testYear = 2012
## import 2012 data
riverdata = dataparsers.WaterData("data/2012/roaring_at_difficult_2012.txt")
tunneldata = dataparsers.WaterData("data/2012/twin_lakes_2012.txt")
The next step is to "massage" the data set from its raw downloaded form into one that makes sense to the system. We first index the data by the timestamp, which will allow us to sum them together to calculate the "natural" flow. Then we trim the data down to just the right information for processing.
# we have to massage the river and tunnel data to get what we need, also so the data is indexible by time.
riverdata.drop_row(0) # contains useless info
riverdata.reindex("datetime")
tunneldata.reindex("Date/Time")
## now, we have too many columns. see:
print "River data columns:", riverdata.data.columns.values
print "Tunnel data columns:", tunneldata.data.columns.values
## We need the "01_00060" column, which is discharge (CFS) from the river data. We'll rename it.
riverdata.remove_columns(["agency_cd", "site_no", "tz_cd", "01_00060_cd"])
riverdata.data.columns.values[0] = "River Discharge (cfs)"
## The tunnel data is more difficult. The columns are misaligned, which means the "Date/Time (NA)" column is
## actually the discharge column.
tunneldata.data.columns.values[1] = "Tunnel Discharge (cfs)"
tunneldata.remove_columns(["Station", "PRECIP (inches) (NA)", "DISCHRG (cfs) (cfs)"])
After messaging the data, notice that all that remains is the discharge value and the timestamp used to index it.
## Now we have:
print "River data columns:", riverdata.data.columns.values
print "Tunnel data columns:", tunneldata.data.columns.values
riverdata.data.head(2)
tunneldata.data.head(2)
The next step is to merge the data sets so we can calculate the "natural flow."
merged2012 = pd.merge(riverdata.data, tunneldata.data, left_index=True, right_index=True)
merged2012.head(5)
# sanity check for accuracy:
merged_point = merged2012.loc["2012-05-02 00:45:00"]
assert riverdata.data.loc["2012-05-02 00:45:00"]["River Discharge (cfs)"] == merged_point["River Discharge (cfs)"]
assert tunneldata.data.loc["2012-05-02 00:45:00"]["Tunnel Discharge (cfs)"] == merged_point["Tunnel Discharge (cfs)"]
print "test passed"
## now we add river and tunnel discharge to acquire the "natural" flow without diversions
merged2012[["River Discharge (cfs)"]] = merged2012[["River Discharge (cfs)"]].astype(float)
## something strange happened, had to drop this row
merged2012 = merged2012.drop("2012-01-22 12:00:00")
merged2012[["Tunnel Discharge (cfs)"]] = merged2012[["Tunnel Discharge (cfs)"]].astype(float)
merged2012["Natural Flow (cfs)"] = merged2012.sum(axis=1)
merged2012.head(5)
Finally, all that remains is to plot the three datasets: the river discharge, tunnel discharge, and natural flow, on one plot.
## Plot!
rivertrace = graphobjs.Scatter(x = merged2012.index,
y = merged2012["River Discharge (cfs)"],
name = "River Discharge")
tunneltrace = graphobjs.Scatter(x = merged2012.index,
y = merged2012["Tunnel Discharge (cfs)"],
name = "Tunnel Discharge")
naturaltrace = graphobjs.Scatter(x = merged2012.index,
y = merged2012["Natural Flow (cfs)"],
name = "\"Natural\" Flow",
fill = "tonexty")
data = graphobjs.Data([rivertrace, tunneltrace, naturaltrace])
layout = graphobjs.Layout(
title='Roaring Fork Diversion 2012',
yaxis=graphobjs.YAxis(
title='Discharge (cfs)',
)
)
figure = graphobjs.Figure(data=data, layout=layout)
plot_url = py.iplot(figure, filename="Roaring Fork Diversion 2012")
plot_url
print "If the plot above doesn't load, view it here: ", plot_url.resource
On the above plot it's possible to toggle the appearance of each line by clicking on its color in the legend at the top right. For example, turning off the tunnel discharge (orange) allows us to see the difference between the actual measured Roaring Fork flow rate and the flow rate expected without the diversion. Turning off the Tunnel Discharge highlights in light-green the area under the "Natural Flow" line, which is the difference between the two values.
import dataparsers
river2015 = dataparsers.WaterData("data/2015/roaring_at_difficult_2015.txt")
river2015.remove_columns(["agency_cd", "site_no", "tz_cd", "01_00060_cd", "03_00065", "03_00065_cd"])
river2015.drop_row(0)
river2015.data.columns.values[1] = "River Discharge (cfs)"
river2015.reindex("datetime")
river2015.data.head()
tunnel2015 = dataparsers.WaterData("data/2015/TWITUNCO_82715111916.txt")
tunnel2015.remove_columns(["Station", "DISCHRG (cfs) (cfs)", "GAGE_HT (ft) (ft)", "GH_ADVM (ft) (ft)", "PRECIP (inches) (NA)", "VEL_ADVM (ft/sec) (ft/sec)"])
tunnel2015.data.columns.values[1] = "Tunnel Discharge (cfs)"
tunnel2015.reindex("Date/Time")
tunnel2015.data.head()
merged2015 = pd.merge(river2015.data, tunnel2015.data, left_index=True, right_index=True)
merged2015.head(5)
merged2015[["River Discharge (cfs)"]] = merged2015[["River Discharge (cfs)"]].astype(float)
## something strange happened, had to drop this row
merged2015[["Tunnel Discharge (cfs)"]] = merged2015[["Tunnel Discharge (cfs)"]].astype(float)
merged2015["Natural Flow (cfs)"] = merged2015.sum(axis=1)
merged2015.head(5)
## Plot!
rivertrace = graphobjs.Scatter(x = merged2015.index,
y = merged2015["River Discharge (cfs)"],
name = "River Discharge")
tunneltrace = graphobjs.Scatter(x = merged2015.index,
y = merged2015["Tunnel Discharge (cfs)"],
name = "Tunnel Discharge")
naturaltrace = graphobjs.Scatter(x = merged2015.index,
y = merged2015["Natural Flow (cfs)"],
name = "\"Natural\" Flow",
fill = "tonexty")
data = graphobjs.Data([rivertrace, tunneltrace, naturaltrace])
layout = graphobjs.Layout(
title='Roaring Fork Diversion 2015',
yaxis=graphobjs.YAxis(
title='Discharge (cfs)',
)
)
figure = graphobjs.Figure(data=data, layout=layout)
new_plot_url = py.iplot(figure, filename="Roaring Fork Diversion 2015")
new_plot_url
print "If the plot above doesn't load, view it here: ", new_plot_url.resource
import dataparsers
import pandas as pd
riverdata = dataparsers.WaterData("data/2008/roaring_fork_2008.txt")
riverdata.remove_columns(["site_no", "agency_cd", "tz_cd", "01_00060_cd"])
riverdata.data.columns.values[1] = "River Discharge (cfs)"
riverdata.reindex("datetime")
riverdata.data.head()
tunneldata = dataparsers.WaterData("data/2008/TWITUNCO_90515064855.txt")
tunneldata.remove_columns(["Station", "DISCHRG (cfs) (cfs)"])
tunneldata.data.columns.values[1] = "Tunnel Discharge (cfs)"
tunneldata.reindex("Date/Time")
tunneldata.data.head()
merged2008 = pd.merge(tunneldata.data, riverdata.data, left_index=True, right_index=True)
merged2008.head(5)
merged2008[["River Discharge (cfs)"]] = merged2008[["River Discharge (cfs)"]].astype(float)
## something strange happened, had to drop this row
merged2008[["Tunnel Discharge (cfs)"]] = merged2008[["Tunnel Discharge (cfs)"]].astype(float)
merged2008["Natural Flow (cfs)"] = merged2008.sum(axis=1)
merged2008.head(5)
rivertrace = graphobjs.Scatter(x = merged2008.index,
y = merged2008["River Discharge (cfs)"],
name = "River Discharge")
tunneltrace = graphobjs.Scatter(x = merged2008.index,
y = merged2008["Tunnel Discharge (cfs)"],
name = "Tunnel Discharge")
naturaltrace = graphobjs.Scatter(x = merged2008.index,
y = merged2008["Natural Flow (cfs)"],
name = "\"Natural\" Flow",
fill = "tonexty")
data = graphobjs.Data([rivertrace, tunneltrace, naturaltrace])
layout = graphobjs.Layout(
title='Roaring Fork Diversion 2008',
yaxis=graphobjs.YAxis(
title='Discharge (cfs)',
)
)
figure = graphobjs.Figure(data=data, layout=layout)
plot_url_2008 = py.iplot(figure, filename="Roaring Fork Diversion 2008")
plot_url_2008
print "If the plot above doesn't load, view it here: ", plot_url_2008.resource