Downloading Discharge and Precipitation Data from HydroClient and Interpreting it with Graphs in Excel

Liza Brazil, Jon Pollak, Consortium of Universities for the Advancement of Hydrologic Science, Inc

Initial Publication Date: October 8, 2015

Introduction

The following step demonstrates how to use the HydroClient web interface to get time series data for discharge and precipitation from multiple data sources located near Lake Tahoe, CA during 2012. The procedure to download data for other stations is identical and can be followed to get precipitation, discharge, and several other parameters for other locations in the United States and around the world.

Conceptual Outcomes

Students will examine the relationship between precipitation and streamflow by downloading data with CUAHSI HydroClient and plotting the data in Microsoft Excel. Students are also introduced to interpreting metadata for the downloaded time series data.

Practical Outcomes

MS Excel spreadsheet with precipitation and discharge data and graph.

Time Required

30 minutes to 1 hour

Computing/Data Outputs

Hardware/Software Required

Internet access and a major internet browser (e.g. Chrome, Firefox, Internet Explorer, or Safari)
MS Excel

Instructions

Open your internet browser and go to the HydroClient URL data.cuahsi.org. The HydroClient uses the Google Maps interface as shown below.

In this exercise we will visit a location of interest, search for, and download precipitation and streamflow data. First, go to the "Enter a location" search box at the top left of the screen to zoom to a geographic area. This example uses Carnelian Bay, CA. Type this or another location in the search box and press enter. You will notice the Map zooms to the location of interest as shown below.

The map is zoomed into a very small area, click on the zoom out option four times so that you can view most of Lake Tahoe - an area of about 4,000 to 6,000 square kilometers, which is indicated in the bottom center of the map view. Your map should look similar to the screenshot below.

Now you will define your search criteria in the right side tool bar to find all of the precipitation and discharge data in the area that your map shows from 1/1/2012 to 12/31/2012. Begin by clicking on the blue Select Date Range button and typing in 1/1/2012 to 12/31/2012 in the From and To boxes then click Save. Next, use the Select Keyword(s) button to select Discharge and Precipitation under the "Most Common" keyword tab as shown below. Click Save to finish selecting your keywords.

Click on the blue Select Data Service(s) to limit the data services being searched. To search data sources with field observations only, click the "Select all non-gridded data services, e.g. NLDAS" checkbox in the right top corner as shown below and click Close.

Click Search Map to begin the search.

You may get a pop up notification that says the following...

Go ahead and click OK. After a few seconds several blue markers will pop up on the map as shown below. Each blue marker has a number that indicates how many data sets exist for that location. The markers cluster dynamically; if you zoom in you will get more precise locations, while if you zoom out the locations will be more generalized.

To see all of the datasets, click on "Data" in the top right next to "Map", as shown below.

Once you click on the Data button, a list of all of the data sets that are displayed by the blue markers in the map above will appear in a table as shown below.

Narrow your search by typing "SNOTEL 784" in the top right search box. This will filter the table to a specific SNOTEL station that has precipitation data. Highlight the first row of the list as shown below.

Take a minute to understand the data set that you have found. Scrolling from left to right, you will see the following columns amongst others such as Start Date, End Date, and Site Name:
- Keyword: Precipitation
- DataType: Incremental
- ValueType: Field Observation
- Sample Medium: Precipitation
- Time Unit: Day
- Time Support: 1
- IsRegular: True

This information about the data, or metadata, tells us a lot about the particular data set. From this information, you can discern that this is a precipitation time series that is measured in the field and summed on a daily basis.

Once you understand the precipitation data set, continue to locating the discharge data set below.

Leaving this SNOTEL precipitation row highlighted, go back to the search box and type "WARD C AT HWY 89 NR TAHOE PINES CA", which will filter the table to a USGS monitoring station that has discharge data, and then select the first row.
Take a minute to review the various metadata for the highlighted Discharge data set. Again, scrolling from left to right, you will see the following fields amongst others such as Start Date, End Date, and Site Name:
- DataType: Mean
- Value Type: Derived Value
- Sample Medium: Surface Water
- TimeUnit: Day
- TimeSupport: 1
- IsRegular: True

From this information, you can see that this is a daily average of discharge of surface water.The values are derived from other measured observations and computed on a daily basis.
Once you understand discharge data, continue to downloading the data sets below.
Notice the Process Selections box now says "Process 2 Selections". Click Process 2 Selections. Now go to the Download Manager located in Header Toolbar. The Download Manager will show the Processing Status as shown below.

Once the Processing Status says "Completed!!" you can click "Download Archive" and a pop up window with the options to Open, Save, or Save as will appear.

The location of these monitoring stations are approximated in the screenshot below. The markers may look different and have different numbers, as the HIS Central Catalog, and thus the data returned in a search, is updated regularly.

If you click on a marker in the map, a table of the data sets at that location is displayed. For instance, clicking on the marker where the USGS data are located will display the table below that includes the Site called "WARD C AT HWY 89 NR TAHOE PINES CA".

After your download has completed, open the folder in which it has been saved. The data sets will be downloaded as two .csv files compressed as one .zip file. You may need to extract the .csv files from the .zip file to open them in Excel. If your computer has automatically extracted the .csv files and opened them in Excel, you can skip the following steps on importing the data into Excel, but should follow steps below to save the document as an Excel file rather than a Unicode file.
The Text Import Wizard will appear. Select Delimited as the file type that best describes your data and click the box for My data has headers, as shown in the screenshot below. Now click Next.

Now select Comma under Delimiters. Lastly, click Finish.

If your computer did not prompt you with the Text Import Wizard steps, you must change the file type. Do this before continuing to the steps below. Select Save As in Excel and change the file type from Unicode Text to Excel Workbook then click Save. This is shown in the two screenshots below.

Change to:

You will now plot the data using the graphing tool in Excel, but before you do, notice the additional information – metadata- that is contained within each file that describes the data within. Click here to read more about the downloaded files from HydroClient. To plot the data, first highlight all of the precipitation data and copy and paste it next to the discharge data in one Excel sheet. You can highlight by left clicking cell A1 and dragging your mouse down and over to select all of the data as shown below.

**Notice the first two columns appear as ######, to see the actual numbers expand the width of the column by clicking on the right side of each of the columns and dragging it outward.

Now go into the Discharge Excel sheet and select X1 cell to paste the data. Your Excel sheet should look like the following.

Now go to the Insert tab and select the Scatter with Straight lines and markers graph. Right click on the blank graph and choose Select Data. The Select Data window is shown below.

Click Add and the Edit Series window will appear, as shown below. In the Edit Series window begin typing in the Series Name as Precipitation, millimeter (incremental) at Tahoe City. Now for the Series X values, click in the blank box and then highlight the UTCTimeStamp column for the Precipitation data and for the Y values highlight the Values column. The Edit Series box below shows some part of what was highlighted:

Repeat this process for the Discharge data. To highlight the forcing relationship between precipitation and discharge, graph the discharge data on a secondary y-axis. You can do this by right clicking on the Discharge series line and clicking Format Data Series. The Format Data Series toolbar will pop up on the right where you can select to Plot Series On: Secondary Axis, as shown below:

**Make sure you selected the Discharge Series as the secondary axis and not the Precipitation Series.

Finish your graph by adding a legend and axis titles. Do this by left clicking on the chart area and then clicking on the + button in the top right as shown below. Options to select Legend and Axis Titles will appear. After typing in the correct legend and titles, your graph should look similar to the graph shown below.

As shown above, the signal of the precipitation measured in Tahoe City, CA can be seen in the discharge measured in Homewood, CA. Generally, an increase in precipitation is followed by an increase in discharge.