Explore the data by creating graphs of discharge and water isotopes

Students will learn to explore time series data collected from different sites and with different measurement frequencies using graphs

Students will learn how to manipulate data downloaded from HydroClient within a spreadsheet program.
Students will learn how to create a graph in Excel with multiple time series.

1 hour

Zip file containing 7 selected data sets created in previous step

Student will generate graphs that might be saved as part of an Excel file, R script or other format.

Program to open a .zip file (e.g., 7zip)
Excel, R, Matlab, or any spreadsheet or data analysis program of the student's choice. The specific examples and screenshots will use Excel 2010.

Locate and Unzip the Zip file containing the Hydroclient Datasets

Locate the zip file you downloaded from Hydroclient in the previous step. Using any unzip utility on your computer (e.g., 7zip), extract the data files and save them to your computer. Make sure you choose a location you can find again and access later.

Locate your unzipped data files and open the "odm_kentstate-west_branch_mahoning_river_in_jenning's_woods-discharge.csv" in Excel or a program of your choice.

Data from Hydroclient is downloaded in a comma separated values format, which can easily be read in Excel, Matlab, R, or many other programs. The screenshot below shows what an example data file looks like in Excel 2010. If you are using Excel, when you open the file, a text import wizard will pop up. You should select "delimited" on the first screen, then click next. On the second screen, select "comma" and then hit finish. You may also need to increase the width of the first two columns if they look something like this #### at first.

The first two lines provide information on the variable (discharge), units (cubic meters per second), a description of the method used to create the data, a site name (West Branch Mahoning River in Jenning's Woods) and its latitude and longitude. There is also information on what value shows up when there is missing data. This is called the NoDataValue (-9999 for this dataset).

Line 3 gives the headers for each column, and lines 4-1929 provide the data. Make sure you understand what each column is telling you.

Create an xy scatterplot of the discharge versus local time.

A plot of discharge versus time is called a hydrograph. The following directions are for Excel 2010, but you can make similar graphs in other programs. In Excel, select Columns B and D, Rows 3-1929. Then go to the insert tab, and click on the little triangle below the scatter chart type. You can select the "scatter with only markers" or the "scatter.

Once you've got your graph, be sure to label your x-axis and y-axis appropriately, including units. You may also want to adjust the ranges of the axes, so that your data takes up nearly all of the space on the graph. Pictured below is a simple Excel plot of the discharge data.

Your hydrograph should show three distinct periods when discharge is elevated in the stream. Now that you know what the stream is doing, let's take a look at the isotope data. But first, you might want to save your work in a file name, format, and location that make sense to you.

Add stream water isotopes to your hydrograph.

Here we are going to look at the hydrogen isotopes in the water molecule. The heavier isotope is called deuterium, so sometimes the hydrogen isotope ratio is called delta-D. For more on water isotopes and notation, look at the resources provided with the unit or check out the wonderful SAHRA resource on Isotopes and hydrology: http://web.sahra.arizona.edu/programs/isotopes/index.html.

Open the odm_kentstate-west_branch_mahoning_river_in_jenning's_woods-delta-d_of_h2o.csv file in Excel or the program you are using. If you are using Excel, you should be able to follow the same import steps as you did for the discharge data.

Again, take a few moments to understand the metadata in the top two lines of the file and the header information in line 3. Note that the UTC offset value is -4, so the local time is the same as for the discharge data.

Note that the no data value is -9999. In Excel, this will be treated as a real data value, so you might want to remove those values before going further. One way that you can do this is select your whole dataset, and use sort button under the data tab. Sort by value to identify the missing values and then clear the contents of those value cells. Then sort again by LocalTimestamp from oldest to newest. How you handle these missing values will depend on the program you are using.



The process for creating a graph that has both discharge and stream water isotopes is going to vary depending on your program. What follows are instructions for doing it in Excel 2010. Note that some people interested in data visualization are not in favor of graphs with multiple series, much less multiple axes. If that's you or your instructor, you will just want to make a second graph at the same horizontal scale as your first one to display the isotope data.

In Excel, go to your hydrograph, and click the button to select data. In the popup window, click the button to add a new series. You might name this new series "Stream H isotopes." Select the local time data for the isotopes as the x-values for the new series. Select the precipitation values for the y-values. Hit OK in the popup windows and take a look at your graph.

In Excel, chances are that your graph now appears to have a slightly wiggly line at 0 and another broken line between -60 and -70. That's not super helpful, so we want to display isotopes on a second y-axis. Select your isotope series, go to format that series. In the popup window, under series options, select secondary axis.



Make sure you label your second axis with the correct units. I suggest something like "Hydrogen isotope ratio (‰)."

Now you should be able to see how isotope ratios in the stream vary in the stream within and between events. You could repeat this with oxygen isotope ratios, using another file that you downloaded. While hydrogen and oxygen isotope ratios generally co-vary in streams, you might find that one is more helpful than the others when you get to the hydrograph separation.

Make a graph of stream water isotopes versus precipitation isotopes.

Using the instructions above for Excel, or the program of your choice, make a graph that shows both precipitation isotopes and stream water isotopes. What do you see about the range of precipitation isotopes versus stream water isotopes?

Optional: Make a graph of precipitation at Portage County airport versus discharge.

While not necessary to perform a hydrograph separation, using the available data to explore the relationship between precipitation and discharge will help you better understand the hydrology of the watershed.

As you make your graphs, make sure you save your work and write down your observations about the relationships.