Getting Physical with Estuaries

NERRS System-wide Monitoring Program Data Base Student Worksheet

Long-term environmental monitoring is a key activity of NOAA’s National Estuarine Research Reserve System (NERRS). The NERRS System-wide Monitoring Program (SWMP; pronounced “swamp”) includes regular measurements of water quality indicators, meteorological data and nutrient data. Water quality indicators include water temperature, specific conductivity, salinity, dissolved oxygen, depth, pH, and turbidity.

Meteorological data include air temperature, relative humidity, barometric pressure, wind speed, wind direction, precipitation, and solar radiation. Nutrient data include orthophosphate, ammonium, nitrite, nitrate, nitrite+nitrate and chlorophyll. The NERRS Centralized Data Management Office (CDMO) provides technical support to monitoring programs in each of the 26 NERRS estuaries, and also provides access to data from these programs to researchers and anyone else who is interested in this information.

Your assignment is to retrieve data from the CDMO database for a specific estuary, and analyze these data to make inferences about some of the processes that affect environmental conditions in this estuary.

The easiest way to do this analysis is to import the data into a spreadsheet program that will allow you to sort, summarize, and graph them. Here’s how to retrieve SWMP data and import them into Microsoft Excel® using the ACE Basin estuary as an example:

  1. Open the CDMO Home page at http://cdmo.baruch.sc.edu/. Click on "Get Data" on the left side of the page, then click on “Search Data.” A map will appear showing the Estuarine Research Reserves for which data are available. Click “Map” or “Hybrid” to display place names and state outlines. Click on the red dot on the South Carolina coast between Charleston and Savannah, or click the link to "ACE Basin, SC" on the list found on the right side of the page. A new window will appear that includes a satellite view of the reserve area showing the locations of sampling stations, the types of data collected at each station, and links to each station.

  2. Click on sampling station link for “3. Mosquito Creek,” then on “Water Data” in the pop-up window that appears. Click on “Export Data.” A new window will appear with directions for exporting data. Be sure “ACE Basin, SC: acemcwq - Mosquito Creek” is highlighted, select “2002” in the “Range” window, and click on the “Export data” button.

  3. You will see a new page titled “ Data/Metadata Download” and a messaging stating the the files are being generated. When this process is complete, another page will appear with a user information form. Fill out the form, including the email address to which the data file should be sent. Click “Submit.” A new page will appear stating that downloading instructions have been sent to the email address you provided, that a link to the exported data will be provided in those instructions, and you should  click on the link to download your data. Check your email for these instructions and click on the link. A compressed (“zip”) file will download to your computer.

    Unzip the file (either by clicking on the file icon or by using an “unstuffing” utility. You should now have a folder containing five files. Open the file named  “acemcwq01012002-01012003.csv” in a spreadsheet program such as Microsoft Excel® to simplify graphing and manipulating the data. To open the file in Microsoft Excel®:

    • Launch Microsoft Excel® and select “Open” from the “File” menu. Select “acemcwq01012002-01012003.csv” and click “Open” (if the file name is dimmed, select “All documents” in the “Enable” window). You should now have a spreadsheet containing 20 columns (A through T) and 3,721 rows with titles of each column in row 1. If a column contains “#” symbols, increase the width of that column until data appear.

    • Select column B (“TimeDateStamp”). Under the “Format” menu select “Cells.” Click on the “Number” tab, and select “Date” in the window next to “Category” then choose “3/14/01 13:30” in the “Type:” window. Click “OK.”

  4. Prepare data summary graphs for temperature, salinity, dissolved oxygen (mg/L), and depth as follows:

    1. Highlight the cells in columns E, I, M, and O (temperature, salinity, dissolved oxygen (mg/L), and depth) for the interval November 8 – 18, 2002 (rows 1130 – 1657).

    2. Click on the Chart Wizard icon. Select “Line” under “Chart type” and the upper left icon under “Chart subtype.” Click “Next.”

    3. Be sure the button next to “Columns” is selected under “Series in.” Click the “Series” button at the top of the window. Notice that the four data sets are named, “Series 1,” “Series 2,” etc. You can replace these names by highlighting the name in the lefthand box, then typing a new name in the “Name:” box. Replace “Series 1” with “Temperature”. Replace “Series 2” with “Salinity”. Replace “Series 3” with “Dissolved Oxygen”. Replace “Series 4” with “Depth”. Set the X-axis labels by typing in the reference for the “TimeDateStamp” cells in the window next to “Category (X) axis labels:” The reference for these cells is:
    4.      =‘acemcwq01012002-01012003.csv’!$B$1130:$B$1657

      Click “Next.”

    5. Click the “Titles” tab. Enter a title for your chart. Enter “Sample Interval (30 minutes)” in the “Category (X) axis:” box and “Temp (°C), Sal (ppt), DO (ppt), Depth (m)” in the “Value (Y) axis:” box. Click the “Axes” tab. Click the button next to “Category”. Click “Next.”

    6. Click the button next to “As new sheet” and enter “Temp, Sal, DO, Depth”. Click “Finish.” You now have a chart that shows variations in temperature, salinity, dissolved oxygen, and depth at the Mosquito Creek monitoring station during the November 8 – 18, 2002 interval.

    7. If you want to print your chart and do not have a color printer, you may want to modify the line patterns and background. To do this, double click on the background area of your chart. The “Format Plot Area” dialogue box will open. Select “No Fill” button for the Fill color. Click “OK.” Now double click on one of the plotted lines on your chart. The “Format Data Series” dialogue box will open. Select the solid line or one of the patterned lines in the window next to “Dashed:” and black in the window next to “Color:” You may also want to select a heavier line in the window next to “Weight.” Click “OK.” Repeat these steps for the other lines on your chart.

  5. Return to the ACE Basin Site Map page. Click on the link for the Bennett’s Point sampling station, then on “Weather Data” in the pop-up window that appears. Click on “Export Data” (near the bottom of the page). A new window will appear with directions for exporting data. Be sure “ACE Basin, SC: acebpmet - Bennett’s Point (Real Time)” is highlighted, select “2002” in the “Range” window, and click on the “Export data” button.
  6. Continue as directed in step 2, above. The information form should contain the same entries that you made before, so you don’t need to enter anything new unless you want the data file to be sent to a different email address. Download and unzip the file as directed above.

    Open the file named “acebpmet01012002-01012003.csv” in Microsoft Excel® as directed above. You should now have a spreadsheet containing 23 columns of weather data from the Bennett’s Point station for the year 2002. If a column contains “#” symbols, increase the width of that column until data appear.

    We need a graph that shows rainfall for the period November 8 – 18, 2002. For this graph, you will use data in column R (“TotPrcp” = total precipitation). Notice that most of the entries in this column are either “0” or “-99”. These entries show that no rain fell during the sampling period. If you try to graph these data with all those “-99”s, the actual rainfall events will be hard to see. So what we need to do is to filter out any entry that is less than “0.” To do this, click on the “More buttons” arrow on the right side of the lower menu bar at the top of the Excel® window. Next click on “AutoFilter” in the popup menu that appears. Next, click on the two arrows that are now visible on the right side of the title box for column R (“TotPrcp”), then select (Custom Filter . . .) from the popup menu. The “Custom AutoFilter” window should appear. Select “is greater than or equal to” in the upper left box, then type “0” in the upper right box. Click “OK.” Now, the spreadsheet should only contain entries where “TotPrcp” is 0 or greater, and you can proceed to construct the graph:

    1. Highlight the cells in column R for the interval November 8 – 18, 2002.

    2. Click on the Chart Wizard icon. Select “Line” under “Chart type” and the upper left icon under “Chart subtype.” Click “Next.”

    3. Be sure the button next to “Columns” is selected under “Series in.” Click the “Series” button at the top of the window. Rename “Series 1” to “Precipitation”. Set the X-axis labels by typing in the reference for the “TimeDateStamp” cells in the window next to “Category (X) axis labels:” The reference for these cells is:
    4.      =‘acebpmet01012002-01012003.csv’!$B$37631:$B$38957

      Click “Next.”

    5. Click the “Titles” tab. Enter a title for your chart. Enter “Sample Interval (30 minutes)” in the “Category (X) axis:” box and “Precipitation” in the “Value (Y) axis:” box. Click the “Axes” tab. Click the button next to “Category”. Click “Next.”

    6. Click the button next to “As new sheet” and enter “Precipitation”. Click “Finish.” You now have a chart that shows variations in rainfall at the Bennett’s Point monitoring station during the November 8 – 18, 2002 interval.

    7. Modify the line patterns and background if necessary, and print the graph.

  7. Repeat Step 2 to obtain a file of water quality data from Mosquito Creek for 2003, and open the data file in your spreadsheet program. Prepare graphs of temperature, salinity, dissolved oxygen, and depth for January 15, 2003; April 15, 2003; July 15, 2003; and October 15, 2003.

  8. Use your graphs for clues to the following questions:

    1. What pattern do you see in variations in water depth? What do you think causes these variations?

    2. What other factors (temperature, salinity, and/or dissolved oxygen) seem to have variations that coincide with variations in water depth? Why do they coincide?

    3. How do the overall values of dissolved oxygen vary at different times of the year? Does there seem to be a relationship between dissolved oxygen levels and temperature, salinity, and/or water depth?

    4. Are there any indications that precipitation affected water depth, temperature, salinity, and/or dissolved oxygen between November 8 - 18, 2002? If so, how do you explain the observed effects?

    5. Organisms living in estuaries are subjected to long- and short-term variations in temperature, salinity, dissolved oxygen, and other important environmental conditions. Based on your charts for January 15, April 15, July 15, and October 15, 2003, what is the maximum daily range of temperature, salinity, and dissolved oxygen experienced by estuarine organisms living in Mosquito Creek? What is the annual range of these factors?

 

footer art