CT-STEM

Student Directions and Resources

You will need the following resources to complete this assignment.

1. Introduction

Purpose
Today you are going to analyze nitrate water quality data from an Iowa water collection site and determine how often the water exceeds the nitrate health limit (of 10 mg/L). Your class will then come together to plot your water collection sites and their corresponding frequency of unhealthy nitrate levels over the year on a digital map of Iowa to better understand where placing water treatment facilities may be most helpful.

Using Computational Tools
Once computational scientists have collected data relevant to the question they are studying, they need to analyze this data and be able to report their findings to a larger audience. Instead of having to use a calculator and pencil to analyze each data point by hand, computational tools such as Excel and Google Sheets are able to help scientists analyze their data both quickly and accurately.

2. Analyze Data in Google Sheets/Excel

Analyze Data in Google Sheets/Excel to determine if your assigned location has a nitrate pollution problem.

Step 1: Open water quality data in Google Sheets/Excel. Use the data you collected previously, open the attached .xlsx file, or may a copy of the linked Google Sheet.

Step 2: Find the data for your water collection site. Each sheet/tab in the Excel file represents a different water collection site. Search tip: “Ctrl+F” (FIND) “[your site number]"

Site locations (ask your teacher for your assigned site)

Group 1 Turkey River at Garber, IA (# USGS 05412500)
Group 2 Maquoketa River near Green Island, IA (# USGS 05418720)
Group 3 Mississippi River at Clinton, IA (# USGS 05420500)
Group 4 Old Mans Creek near Iowa City, IA (# USGS 05455100)
Group 5 Cedar River at Blairs Ferry Road at Palo, IA (# USGS 05464420)
Group 6 Hoover Creek near 2nd Street at West Branch, IA (# USGS 0546494205)
Group 7 Iowa River at Wapello, IA (# USGS 05465500)
Group 8 Boone River near Webster City, IA 9 (# USGS 05481000)
Group 9 Des Moines River at 2nd Avenue at Des Moines, IA (# USGS 05482000)
Group 10 North Raccoon River near Sac City, IA (# USGS 05482300)
Group 11 North Raccoon River near Jefferson, IA (# USGS 05482500)
Group 12 South Raccoon River at Redfield, IA (# USGS 05484000)
Group 13 Raccoon River at Van Meter, IA (# USGS 05484500)
Group 14 Little Sioux River at 300th St near Spencer, IA (# USGS 06604440)
Group 15 West Nishnabotna River at Randolph, IA (# USGS 06808500)
Group 16 Nodaway River at Clarinda, IA (# USGS 06817000)

Question 2.1

Which site location are you analyzing?

Question 2.2

Is your site missing any data from the year? If so, which parts of the year does your data best represent?

Question 2.3

How many days in the past year does your site have data for?

Use a function to count this efficiently.
Option 1: Select a blank cell. Type “=count” into this cell, and click on the COUNT option. Then select the nitrate data again to computationally count the number of days with data at your site.
Option 2: Select a blank cell. Insert -> Function -> Count. Then select the nitrate data again to computationally count the number of days with data at your site.

Question 2.4

How frequently does the nitrate level at your site exceed the health standard?

Select all numeric data representing the “Daily mean of Nitrate plus nitrite, water, in situ, milligrams per liter as nitrogen” (in column D under the cell that says “14n”)

Format -> Conditional Formatting: select cell value is “greater than” “10”, click the “Formatting Style” dropdown to select a color or make bold all value that are above the health limit for nitrate in water.

Type “=countif” into a blank cell, and select the COUNTIF option to count how many days the water at your site exceeded the nitrate health limit of 10mg/L. =“COUNTIF(data range,criteria)” for example “=COUNTIF(D11:D377,">10”)”

Question 2.5

What percent of the time does the nitrate level exceed the health standard?
Calculate the percentage of total days above the health limit using your calculated numbers.

3. Visualizing Nitrate Level Data Over Time at a Water Collection Site

Are there any times of the year when your site’s water source has a stronger concentration of nitrates? Speculate why this may be the case.

Consider the following excerpt from an article "High Nitrate Levels Plague 60 Iowa Cities, Data Show". In July, 2015, the Des Moines Register reported the following:

Identifying the sources
The EPA says nitrate threats can come from several sources, including farm fertilizer runoff, septic system leaks and movement of naturally occurring nitrates in the soil. Des Moines Water Works has blamed farming for the high nitrate levels that it has paid more than $1 million since 2013 to treat.

Across the state, many communities get below the federal cap by blending water from several underground wells or river collectors with differing nitrate levels. Still, that might not be enough for some towns, which could be forced to invest millions of dollars in new equipment or wells to ensure drinking water is safe.

The nitrates solution
Weather can play a large role in nitrate levels, along with land use, drainage, soil types and other factors. Farm groups have argued that reducing nitrate levels is extremely complex and will require increased conservation efforts from most of the state's nearly 90,000 farmers.

Cutting nitrate and phosphorus levels is expected to be costly, an estimated $1.2 billion annually over five decades. Conservation practices include building wetlands, bioreactors and saturated buffers and expanding cover crop usage.

You can read the rest of the article here: http://www.desmoinesregister.com/story/money/agriculture/2015/07/04/high-nitrates-iowa-cities/29720695/

To get a better sense of your site’s nitrate levels over the course of the year, you will be creating a chart with the date on the horizontal axis and nitrate level on the vertical axis.

Step 1: Select the complete range of the two columns of dates and their corresponding nitrate levels.

Step 2: Insert -> Chart. Choose a chart that you think best represents the data.

Step 3: Right click on your chart, and add a title.

Step 4: Right click on your chart. Under “Axis,” click on the horizontal and vertical axis titles, and then rename them to something more appropriate.

Question 3.1

Does your site location have a nitrate problem? Is there a pattern to the problem? Would you recommend a nitrate removal system for your site? Use the data analysis you have done and other relevant factors to support your response.

Question 3.2

Upload your spreadsheet as an Excel file.
Step 1: In Google Sheets, rename your spreadsheet to include your group members’ names, and drag this sheet to the front of the workbook.
Step 2: Download your Google Sheet as an Excel file named after your group.

Upload files that are less than 5MB in size.

File	Delete

Upload files to the space allocated by your teacher.

4. Visualizing Nitrate Level Data across All Water Collection Sites in Iowa

Step 1: Open the attached file (IowaWaterQualityByLocation.txt) in Codap (https://codap.concord.org/).

Go to https://codap.concord.org/. Click the orange “Try It Now” button.
Create a new document by clicking on the menu button in the top left corner.

Click the menu button, and select Import. Import the text file attachment you previously downloaded (IowaWaterQualityByLocation.txt).

Step 2: Add the percentages that your classmates have calculated for each water collection site.

Expand the window and the width of the “Location” column, so that your table looks like this and then add the data.

Step 3: Visualize the data on a map

In the bar at the top of the screen, click “Map.” A map with each water collection site represented as a red dot should pop up. You can click on each site on the map to see which site it corresponds to in your data table.

Drag the “%” column header from the data table to your map. The darker points on the map should correspond with higher percentages of unhealthy nitrate levels.

Click on your map, and then click on the paintbrush icon. Change the stroke color to something that will stand out on your map, and feel free to adjust the point size.

Feel free to zoom in and out on your map as necessary. In a zoomed in view, more rivers appear on the map.

Question 4.1

Upload the screenshot of your map. (Make sure to include all water collection sites!)

Upload files that are less than 5MB in size.

File	Delete

Upload files to the space allocated by your teacher.

Question 4.2

Looking at your map, which major cities seem to be more at risk of drinking water from nitrate-polluted water sources? (Major cities in Iowa include Sioux City, Des Moines, Mason City, Waterloo, Cedar Rapids, Dubuque, and Davenport.) Do any other small- or mid-sized cities in Iowa also seem to be at risk?

Question 4.3

Can you draw any useful conclusions by looking at your map? What additional information would be helpful?

5. Reflection

Today you analyzed water quality data and visualized the nitrate levels of Iowan water collection sites on a map.

Nitrates and Water Quality (Part 2: Analyzing & Visualizing Data)

Overview

Standards

Next Generation Science Standards

Computational Thinking in STEM

Activities