Part 4—Analyze Borehole Data for Temperature Trends
Step 1 – Download the Borehole Data for Yakutsk and Explore
1. Download the borehole data for Yakutsk from my Website and open it up using ExcelHide
1. Choose File > Open... and navigate to the location where you saved the hpip_yakutsk_borehole_data.xls file.
2. Single click to select the file. Then click Open.
2. This subset file was extracted from the complete dataset by eliminating the data for all but the depths of 0.8 meters and 3.2 meters. When the file opens you should see four columns of data.
3. The first tab in the file, "GO2189 info", shows basic information for all five borehole locations, their station numbers, latitude and longitude, height above sea level, and source of surface temperature data for each location. The other two tabs: "January soil & surface temp" and "July soil & surface temp", are specific to Yakutsk. Important: If you cannot see the tabs at the bottom, double-click on the top bar of Excel. This will maximize the file and make all the tabs viewable.
4. Click on the "Jan soil & surface temp tab" and examine the data.
o On the chart, Column A is the year of the record from 1950 to 2000. For each year, the average soil temperature for the month of January is indicated at depths of 0.8 meters and 3.2 meters below the surface; this data is shown in Columns B and C, respectively. The instruments used to measure the temperature are specialized thermometers that can withstand the harsh conditions in the soil and ice.
o Column D indicates average surface air temperature for the month of January in each year, in degrees Celsius. Recall that surface temperatures are air temperatures recorded by thermometers located approximately 1 meter (3 feet) above the surface of Earth. These thermometers are placed in special shelters that protect them from direct sunlight and rain. (Note: all temperatures are in degrees Celsius.)
Scroll through the data and answer the following questions (Label your observations “Part 4 – Questions 1-3” in your notes)
Q#1 Why do you suppose two borehole temperature depths are given?
Q#2 Do you notice any differences in the temperatures at different depths?
Step 3 – Use Excel to Create a Time Series Plot for Yakutsk Data
Using the January soil and surface temperature data that you downloaded in Step 1, use the chart tools to make an XY (Scatter) plot, of temperature versus time, for the time period 1950-2000.
Hide
Note: You may need to click Replace one time to get the full dialog box.
1. Create your graph. Click and drag to select all of the data (rows) from 1950 to 2000 (columns A, B, C and D). Then, select Insert > Chart ... or click the Chart Wizard button. In the types of charts, choose the "XY (Scatter)" option. Of the XY (Scatter) options, click on the "Marked Scatter" option for your graph. It is the first one in the list.
2. Once your chart is drawn, choose Layout 1 from the Chart Layout menu to format your axes. Choose Temperature, in degrees Celsius, for the Y-axis; choose Years, from 1950 to 2000 for the X-axis. Make sure the minimum temperature for the Y axis is set at -60.0 degrees. (Recall that permafrost is frozen.)
4. Hide
1. Click and drag to select all of the data (rows) from 1950 to 2000 (columns A, B, C and D).
2. Then, select Insert > Chart ... or click the Chart Wizard button. In the menu bar that opens, choose the "XY (Scatter)" option. Of the "XY (Scatter)" options, click on the "Marked Scatter" option for your graph type.
3. On a Mac, click the Toolbox button to open the Formatting Palette. On a PC use the tabs at the top of the spreadsheet and locate the Layout menu. Open the Chart Options section of the Formatting Palette to change the Chart title, Horizontal and Vertical axis titles. Under Chart Options > Titles, select the drop-down menu for each of the Title and both axes and enter their title names.
§ Change the Chart title to January Yakutsk Borehole Data
§ Label the Vertical (Y) axis - Temperature; Horizontal (X) axis - Year
4. Single click once on the Vertical Axis to select it, then choose Format > Axis. On a PC, Choose Layout > Axes > More Primary Vertical Axis Options, to get to this dialog box.
5. In the Format Axis... dialog window, click the Scale button and set the Horizontal axis to cross at -60.0 and click OK or Close.
Right click on the Legend, where it says Series 1, 2 and 3. Then, choose select data.... In the dialog box that opens, edit the names of labels of your lines. Click OK when you have made your changes.
o Name Series 1 - soil temperature at 0.8 meters.
o Name Series 2 - soil temperature at 3.2 meters.
o Name Series 3 - surface temperature.
5. Hide
4. Choose Chart > Source Data... on a Mac.
5. Rename each of the 3 series as follows:
§ Name Series 1 - soil temperature at 0.8 meters.
§ Name Series 2 - soil temperature at 3.2 meters.
§ Name Series 3 - surface temperature.
Click OK when you have made your changes.
Once you have created the plot, make a trendline for each set of the three sets of data. In order to do this, select your graph, and then choose Layout > Trendline > Linear Trendline. Remember you must do this 3 times, one for each of the 3 datasets (soil temp, soil temp, and surface temp)
Hide
1. On the chart, select (single click) the dataset that you wish to investigate. Then, choose Chart > Add Trendline ... or Layout > Trendline (PC) to add a trend line to your graph.
2. In the Format Trendline dialog box, choose Line (Mac) or Linear (PC) and click OK.
When you have completed the graph, including the trend lines, answer the following questions (label your questions “Part 4 – Questions 1-3” in your notes):
Q#1 What do you notice about each of the trend lines?
Q#2 Compare the permafrost borehole plots with surface temperature plots for this station. Do you notice any patterns?
Hide
There is a definite pattern to the trend lines. As the winter (Jan) surface air temperatures increase so does the temperature of both the boreholes The 0.8 meter borehole shows a steeper trend.
Next, repeat steps 1-5 for July temperatures. (They are on the 3rd tab in the same Excel document). Answer the following questions:
Q#4 What do you notice about each of the trend lines?
Q#5 Compare the permafrost borehole plots with surface temperature plots for this station. Do you notice any patterns?
Cut and paste the two graphs you generated in Excel into a Word document. Save this document as “PermafrostFinal_yourname” and save it into your H drive. This will become part of your final writeup and must be turned in at the end. Make sure all partners have a copy of this NOW.
Hide
Churapcha
Isit'
Povrovsk
Verkhoyansk