Hands-on Activity Understanding the Air through Data Analysis

Learning Objectives

After this activity, students should be able to:

• Use Microsoft® Excel® for data analysis, including entering formulas and creating graphs.
• Define statistical terms and plot types.
• Use both scientific knowledge and supporting evidence (that is, statistics and plots) to explain observations related to a data set.

Educational Standards

Each TeachEngineering lesson or activity is correlated to one or more K-12 science, technology, engineering or math (STEM) educational standards.

All 100,000+ K-12 STEM standards covered in TeachEngineering are collected, maintained and packaged by the Achievement Standards Network (ASN), a project of D2L (www.achievementstandards.org).

In the ASN, standards are hierarchically structured: first by source; e.g., by state; within source by type; e.g., science or mathematics; within type by subtype, then by grade, etc.

NGSS: Next Generation Science Standards - Science

Common Core State Standards - Math

International Technology and Engineering Educators Association - Technology

State Standards

Materials List

Before the activity, each student needs:

• Pre-Activity Reading
• Pre-Activity Worksheet
• pen or pencil

Each student needs:

• computer with Microsoft® Excel® spreadsheet application; alternatively, use Google Sheets
• CO₂ Activity Worksheet OR O₃ Activity Worksheet, and its respective accompanying data set, CO₂ Case Study Data OR O₃ Case Study Data
• Excel Reference Sheet
• Vocabulary List
• Connecting to the Big Picture Handout

Worksheets and Attachments

Pre-Req Knowledge

It is helpful if students are familiar with basic statistics such as mean, median, mode, range. The Pre-Activity Reading provides an overview of these as well as some concepts that may be newer.

Prior experience with Microsoft® Excel® is not required, but makes a difference in how much time is required. If students have already used Excel, expect this activity to be completed in ~45 minutes, while if they have never used Excel, the activity may take more time and facilitation (~135 minutes).

Background information from the associated lesson, An Introduction to Air Quality Research, is extremely beneficial and enhances students learning during this activity; alternatively, this air quality background information could be obtained from a different source, such as a different lesson or reading.

Introduction/Motivation

(The following introduction is ideal for students who have never used a spreadsheet application before because it shows them the benefit of using a tool such as Excel. If students have used Excel before, this introduction is less effective, so adjust the introduction accordingly.)

(Once students are at their computers with the worksheets, direct them to open either data set in the spreadsheet application.)

Let’s apply what we have learned about air quality to a real-world data set. In front of you, you have four days of pollutant data from two different months along with time and date information and other environmental factors like temperature. Begin by calculating the average pollutant concentration for the first month. (At this point, expect students to realize that each month has 100 data points.)

Would anyone like to calculate this value by hand? If not, why? (Expect students to realize how long that would take! Next, walk them through this first calculation using the spreadsheet application.)

• Click on cell P8, and then type the following:
• An equals sign (which is how all formulas begin in Excel)
• The word, “average”
• A left parentheses
• Then either highlight the first month’s pollutant data OR enter D8:D104
• Close the parentheses and hit the “enter” or “return” key
• The formula associated with cell P8 should look like this = AVERAGE(D8:D104)

After having done this, that cell should display the calculated answer. (For CO₂ = 437.1 ppm and for O₃ = 37.1 ppb)

You’ve just seen the value of using tools to aid in your data analysis. Tools, such as Excel, enable engineers and scientists to work at a much faster pace and with much larger sets of data, pushing the boundaries of research and innovation. Next, work through the rest of the worksheet and refer to the Excel Reference Sheet for help.

Procedure

Background

This activity is intended to introduce students to a new tool that can aid them in their air quality research projects (if they are completing the subsequent AQ-IQ activities) and into the future as well. Particularly, if your students have never used Microsoft® Excel®, it is recommended that the teacher practice in advance all of the formulas and plots using the data sets so as to be able to nimbly demonstrate for students/groups who have difficulties. For more Excel practice, see the tutorials and resources listed in the Additional Multimedia Support section. The Pre-Activity Reading also contains examples of plots and more detailed descriptions of certain concepts (such as R-squared and linear regression).

In terms of the science and engineering content, students are challenged to build on what they learned in the previous AQ-IQ lesson and activities. More information specifically on carbon dioxide (CO₂) and ozone (O₃) is provided below.

Carbon Dioxide Data Set

Combustion processes and biological respiration emit carbon dioxide. A major carbon sink (a natural environment capable of absorbing carbon dioxide from the atmosphere) is vegetation. Thus, in the winter, carbon dioxide levels tend to be higher because less active vegetation exists to remove it from the air. Additionally, in the winter, more carbon dioxide may be released due to increased home heating. This is especially important in valleys where winter-time temperature inversions are common, such as Paonia, CO, where this data was collected.

On a daily basis, we also see fluctuations in CO₂ called “diurnal trends.” The Earth’s planetary boundary layer is an atmospheric layer that acts somewhat like a lid. (This atmospheric boundary layer is the lowest part of the Earth’s atmosphere, from the ground to the bottom of where cumulus clouds form, and its behavior is directly influenced by its contact with the planetary surface.) Due to atmospheric dynamics, the planetary boundary layer lowers at night and rises during the day. The result is higher CO₂ concentrations at night and lower during the day. Imagine having a given concentration of something in a container, as the container shrinks, the concentration must increase. This is what happens in our atmosphere nightly. Even if students are not familiar with the concept of a boundary layer, expect them to observe in the data higher concentrations of CO₂ at night and lower concentrations during the day; they may also mention temperature inversions as being important, which is also correct. The boundary layer’s fluctuations and temperature inversions have the same effect. However, temperature inversions are more localized and driven by meteorology, whereas the boundary layer fluctuations happen on a larger scale and occur year round.

The comparison between CO₂ and temperature provides an opportunity to discuss “correlation vs. causation.” Expect students to notice a relationship between the two (that is, when it is colder, CO₂ is higher); however, cold temperatures do not cause CO₂ levels to rise; rather, CO₂ increases at night, which is when temperatures tend to drop. Temperature and CO₂ are correlated, but cold temperatures do not cause higher CO₂. The relationship is likely stronger in the winter due to the added impact of temperature inversions.

Ozone Data Set

Ozone requires three “ingredients” to form: 1) NO_x or nitrogen oxides, 2) VOCs or volatile organic compounds, and 3) sunlight. Ozone forms via a photochemical reaction and thus can only occur in the presence of sunlight, which is what drives ozone’s daily trends. In the morning, O₃ begins to form, then it increases through the day, peaking in the afternoon, and then begins to decrease, dropping overnight. This requirement for sunlight also explains the seasonal trends. Sunlight is much more available and stronger in May than October; hence, more O₃ forms, as evidenced by the higher daily peaks. These seasonal conditions are why we tend to see ozone alerts (for example, in Colorado) during the summer and not in the winter.

This data set provides another opportunity to discuss “correlation vs. causation.” A positive correlation exists between ozone and temperature, but the high temperatures are not causing the ozone levels to rise; rather, the higher temperatures and the high ozone levels are both products of a larger amount of available sunlight. Conversely, we see less ozone when it is cold because at night, without the sun, ozone cannot form and temperatures drop.

Before the Activity

• Make copies of the Pre-Activity Reading and Pre-Activity Worksheet and assign them as homework the night before the activity.
• Reserve the computers—enough for each student or small student groups.
• Make copies of the Excel Reference Sheet, Vocabulary List and Connecting to the Big Picture Handout.
• Determine how you want students to access the worksheets and (Excel) data sets: CO₂ Activity Worksheet OR O₃ Activity Worksheet, and its respective accompanying data set, CO₂ Case Study Data OR O₃ Case Study Data. For example, print out the worksheets, download them together as a class from the TeachEngineering website or pre-load them in advance onto a class Google Drive.
• Also decide if you want the entire class to work on the same worksheet/data set, or give students the choice of picking one of the two options. Both options walk students through the same calculations and plots. The difference between the two is the air quality content, that is, the sources and sinks of each pollutant and how these impact seasonal and daily variation. You could also choose the one that is more useful for your students, OR have half the class work on CO₂ and half work on O₃ and then share their findings verbally.
• It is recommended that teachers review the Vocabulary List Answer Key and use one of the data sets to practice the formulas, as well as the other activities covered in the worksheet.

With the Students

1. With students at the computers, direct them to look at one worksheet and open its associated data set.
2. Present the Introduction/Motivation content to the class.
3. Remind students that their completed pre-activity readings and worksheets may be helpful. Also, hand out the Excel Reference Sheet as another resource.
4. Direct students to work through the worksheet questions in order.
5. As students work, walk around the class and assist as needed. Figure 1 shows an example of what an in-progress Excel workbook looks like.

6. As students complete the activity, discuss in small groups or as a class the following questions:

• Does the data support your hypotheses, or not? Why?
• What are your conclusions regarding daily and seasonal pollutant trends?
• What evidence in your data analyses supports these conclusions?
• What are your thoughts about using Excel?

7. After the activity, assign students to complete the Connecting to the Big Picture Handout or the Vocabulary List as homework.

Vocabulary/Definitions

coefficient of determination : In statistics, a number that explains how well the data fits the model (such as a linear model). A value of 1 indicates a perfect fit, while a value of 0 indicates a poor fit. If data fits a model well, all of the points are close to the line or pattern; if the data is a poor fit, the points are far away or spread out from the line or pattern. (Refer to the example plots in the Pre-Activity Reading for a more detailed explanation.) Also called “R squared” and noted as R2.

histogram : A type of plot used to understand the distribution of a data set. All possible data points are plotted along the x-axis, and the y-axis is the frequency with which the points occur. Data is typically binned into columns so that a higher column indicates that values falling in the binned section occur more frequently.

linear regression : In statistics, a method by which a standard “y = m*x+b” model is fit to a data set to solve for the coefficients (m and b) that result in the best fit, or the line with the least distance to each data point. Used to explain relationships in data or predict future data. (See the example plots in the Pre-Activity Reading for a more detailed explanation.) Also called linear fit.

mean: The average number in a data set. The sum of every number in a data set divided by the number of points in that data set.

median: The middle number (or the average of the two middle numbers) after a data set has been arranged from least to greatest. Also: the 50th percentile.

mode (statistics): The most frequently occurring number in a data set.

population: The entire pool from which a sample is drawn (every possible data point).

range (statistics): The difference between the largest and smallest values in a data set.

sample (statistics): A reasonable selected subset of a population. Samples are used when it is unreasonable to measure an entire population.

scatter plot: A graph that enables the examination of the relationships between variables. Each variable is plotted on an axis, and patterns or fitted models suggest relationships in the data.

standard deviation: standard deviation In statistics, the variance in the data, or how “spread out” the data is.

time series plot: A graph with time on the x-axis and values of interest on the y-axis that is used to evaluate patterns and behavior in data over time. Useful for examining temporal trends (or trends in time).

Assessment

Pre-Activity Assessment

Reading-Worksheet Assignment: The night before the activity, assign students to use the Pre-Activity Reading to complete the Pre-Activity Worksheet. The reading includes information about basic statistics, data visualization techniques and data interpretation. The worksheet includes a problem set that is intended to help students apply what they learn in the reading. Review their worksheets to see if they are ready for the activity or if any topics need more explanation.

Activity Embedded Assessment

Worksheet: The bulk of this activity is students individually working through a worksheet (CO₂ Activity Worksheet or O₃ Activity Worksheet) with the intent to cultivate their familiarity with data analysis using Excel. While each student is responsible to complete his/her own worksheet, encourage discussion and collaboration, especially if Excel is new to most students. Review their worksheets to gauge their depth of comprehension.

Post-Activity Assessment

Connecting to the Big Picture: Assign as homework the Connecting to the Big Picture Handout, which is a set of open-ended questions that asks students to apply their knowledge of air quality acquired thus far in the unit to the local community. This exercise also aids students in thinking of potential topics for their upcoming projects. If possible, take time to discuss their answers during the following class periods.

Vocab List: Assign as homework the Vocabulary List, which is a list of statistics and data analysis concepts. By the end of the activity, expect students to be able identify and define these terms. Use this handout to assess how much new information students retained.

Additional Multimedia Support

Getting started with Excel: Introduction to Excel Starter: https://support.microsoft.com/en-us/office/introduction-to-excel-starter-601794a9-b73d-4d04-b2d4-eed4c40f98be?ui=en-us&rs=en-us&ad=us

Help on specific concepts: Excel Easy (Tutorial): https://www.excel-easy.com/

Excel videos and tutorials for specific tasks: https://support.microsoft.com/en-us/office/excel-2013-training-aaae974d-3f47-41d9-895e-97a71c2e8a4a?ui=en-us&rs=en-us&ad=us

Note: This activity does not require the use of a Pod.

References

Copyright

Contributors

Supporting Program

Acknowledgements

This material is based upon work by the AirWaterGas Sustainability Research Network Education and Outreach Project in the College of Engineering at the University of Colorado Boulder, supported by National Science Foundation grant no. CBET 1240584. However, these contents do not necessarily represent the policies of the National Science Foundation, and you should not assume endorsement by the federal government.

The authors also express their appreciation for the support of the University of Colorado’s Office of Outreach and Engagement.