Is Your State Experiencing Global Warming?
The Problem:
By examining the average monthly and average annual temperature for a location in your state, is there evidence that over a long period of time (40 or more years) that your state is experiencing a warming trend?
This is an example of importing "raw" text data that is organized in aligned
columns. Excel has a "text to column" feature to facilitate the data transfer.
For this example, we will use data taken at the Las Vegas Airport. You can repeat the activity for your city (or a city nearby) by selecting your region from the National Climate Data Center and following the links to the city of your choice.
Harvesting the Data:
Step 1: Begin at the Regional Climate Data page found at the
National Climate Data Center (Figure 1)
http://lwf.ncdc.noaa.gov/oa/climate/regionalclimatecenters.html
and click on the Western Regional Climate Center. A web page like Figure 2 will appear.
Figure 1: Figure 2:
Step 2: Find and click on the hyperlink titled Western U.S. Climate Historical Summaries by scrolling down and looking to the right under Climate and Weather Information. A web page like Figure 3 will appear
http://www.wrcc.dri.edu/climsum.html
and click on the state of Nevada (image of the state or the word) a and a page like Figure 4 will appear.
http://www.wrcc.dri.edu/summary/climsmnv.html
Figure 3: Figure 4:
Step 3: Find and click on Las Vegas Airport. It is listed by name in the frame on the left or is number 132 on the list or number 132 on the map at bottom of the state. A web page like Figure 5 will appear.
http://www.wrcc.dri.edu/cgi-bin/cliMAIN.pl?nvlasv
On the left frame window, scroll down to the section titled Period of Record and look for the title Monthly Temperature Listings. Click on hyperlink titled Average. In the right frame, a web page like Figure 6 will appear. This frame contains the data we are seeking.
Figure 5: Figure 6:
Accessing the Web Data in Excel:
Find the page with the data you wish to import into Excel. If you have completed Step 4 of the previous section you should see a page that looks like this:
With Internet Explorer (version 6)
Option 1: In the right frame (in the white space under the title LAS VEGAS WSO AIRPORT), hold down RIGHT mouse button and select Export to Microsoft Excel. The Excel spreadsheet should open with the data in cells.
Option 2: Highlight desired data (by dragging the mouse over the rows and columns you wish use), copy (right click on mouse and select copy), open Excel, and then paste (right click on cell A1) into Excel.
With Netscape (version 4.7 or 6):
(Note: the data importing process is a bit more involved using Netscape because the browser is not manufactured by Microsoft, the manufacturer of Excel.)
Step 1: In the right frame (in the white space under the title LAS VEGAS WSO AIRPORT), right click the mouse button and select Open Frame in New Window. The data will now be available in its on browser window. In this new window select and copy the address or URL of the data site (http://www.wrcc.dri.edu/cgi-bin/cliMONtavt.pl?nvlasv). You can do this by highlighting the address, right clicking, and selecting COPY.
Step 2: Open the Excel spreadsheet. Go to the Data menu, select Import External Data, and then select New Web Query· . A new window will appear. In the box titled Address, paste the URL that you copied in Step 1. Press the Go button next to the address and the web page containing the data you seek will appear like the figure below:
Step 3: Press the Import button at the bottom of the window. A dialog box will appear asking you where you want the data located. Indicate cell A1 and press "ok." It may take a few minutes for Excel to pull the data from the web page to the spreadsheet.
Whether you imported the data using Internet Explorer or Netscape, you should now have the Las Vegas Airport temperature data in an Excel worksheet similar to the figure below:
Analyzing the Data in Excel:
Step 1: Remove extraneous data (1909-1938, 1948, 2002)
Highlight each row number on the left you wish to remove from row 2 to row 47 by first click on the 2 of the second row and dragging down to row 47. Once the rows you want to delete are highlighted, go to Edit, and select Delete. Also delete row 10 (no data available for 1948) and row 63 (insufficient data for 2002). You can also delete the summary statistics in rows 65 to 71 since we will compute our own.
Step 2: Adjust column A to be narrower.
Move the cursor to the line between Column A and B, and drag left. The spreadsheet should now resemble the window below:
Step 3: Find average temperature for each month and average annual temperature.
In cell B63 enter the formula =AVERAGE (B2:B62). Press Enter. With cell B63 highlighted, drag the small square "handle" at the bottom right of the cell until you reach cell Z63 (this copies the formula of cell B63 to each successive cell).
Step 4: Find differences between the "average temperature for each year" and "average of the annual averages."
In final column AA in row 2, enter =Z2 - $Z$63. With cell AA2 highlighted, drag the black square on the lower right corner down to cell AA63. This copies the difference formula for each year.
Step 5: Graph the differences from the average
Highlight column AA, Click on Chart Wizard (on the second row of the menu bar, it looks bar graph with blue, yellow and red bars). Select Custom Chart and then press Finish.
Questions to answer using the numerical data:
Questions to answer using the graphical data: