Part 2—Prepare and Graph Daily Data for One Station

Step 1 Open and Examine Global Summary of the Day (GSOD) Temperature Data

  1. Use the Excel Import Wizard and import the BarrowGSOD.txt file into an Excel workbook.
  2. Briefly look over the data. Scroll across and down the workbook to view the column headers and data values. The first two columns contain the station IDs and column three shows the date. The mean temperature for the day in degrees Fahrenheit is in the fourth column. Other columns show dew point, barometric pressure, visibility, wind speed, maximum and minimum temperature for the day, precipitation, snow depth, and so on. The code in the last column is a weather indicator for things like fog, hail, or tornadoes. As shown in Part 1, Step 3, the data format description file that accompanies each order describes each field and the units of measure used.

Step 2 Make a Worksheet With One Year's GSOD Temperature Data

A good way to visualize the temperature changes over a year is to graph the data. Start with 1946 because 1945 does not have a full 365 days of data. For comparison, you will also graph 2010 data. The dataset includes thee reported temperaturesthe average of all the readings in a day, the daily minimum temperature, and the daily maximum temperature.

  1. Choose View > Normal to make the worksheet display as shown in the examples.
  2. Choose Insert > Sheet > Blank Sheet (or Insert > Worksheet in older versions of Excel) to add a new sheet to the workbook. Label the new sheet "Graphs."
  3. To switch between sheets in an Excel workbook, click the tabs at the bottom of the window. The main data tab with all the columns is labeled "CDOxxxx.txt" (or whatever name you gave the data file before loading it into Excel). The blank sheet is named "Graphs."
  4. Copy the 1946 date and average temperature data from the original data sheet to the graphs sheet.
  5. Repeat this process to copy the Max temperature (column R) and Min temperature (column T) data for 1946 from the full data sheet to columns C and D of the graphs sheet, again leaving room for headings in Row 1.
  6. Label column C Max_Temp_1946 and column D Min_Temp_1946.
  7. Go back to the full sheet tab and scroll down to row 23710 (Jan 1, 2010 or 20080101). Click cell D23710 and drag down to cell D24074 to select the 2010 mean temperature data, and choose Edit > Copy.
  8. Click the graphs tab, paste the 2010 data into cell E2, and label the new column Mean_Temp_2010.

If you had trouble downloading data or creating the graphs sheet, download the barrow_part2_step2.xls (Excel 7.4MB May30 11) file to use for the rest of the investigation. Right-click (PC) or control-click (Mac) the link to download the file.


Step 3 Graph a Year of GSOD Mean Temperature Data

  1. Create a line graph of the 1946 mean temperatures using the Excel chart wizard.
  2. Your resulting graph should look something like the one shown below.

Step 4 Graph a Year of Mean Daily Temperature and Daily Max and Min Temperatures

  1. Select the data in the Mean_Temp_1946, Min_Temp_1946, and Max_Temp_1946 columns and use the chart wizard to make a new line graph as you did in Step 3. The final graph should look like the one below.

  2. Look at the graphs of annual temperature. What do you notice? Are there days when the sun does not shine? Are there times of the year when the temperature extremes (min to max) are greater? Is this easier to see on this graph or in the Excel spreadsheet?

Step 5 Graph Mean Daily Temperature Data for 1946 and 2010

  1. Use the chart wizard to create a line graph of the Mean_Temp_1946 and Mean_Temp_2010 data. The result should resemble the graph pictured below. Note: the years in the pictured graph are not the same as the graph you have created.

Step 6 Calculate the Average Annual Temperature from Mean Daily Temperature Data

Column B of the Graphs sheet contains the mean (average) temperature for every day in 1946. Use Excel's AVERAGE function to calculate the average temperature in Barrow, Alaska for 1946.

Repeat this process to calculate the average temperature for 2010 in cell E367. (Hint: Use the same formula but substitute E for B.)

Based on your calculations, what is the difference in the average temperature in 1946 and 64 years later in 2010. Do these two averages suggest an overall trend in the average temperature in Barrow? How could you investigate whether a real trend exists?

If you have trouble making graphs or finding averages in Excel, right-click (PC) or control-click (Mac) the barrow_part2_final.xls ( 4.5MB Jan17 11) link to download a complete Excel file.