Part 4 - Analyze Borehole Data for Temperature Trends

Step 1 – Download the Borehole Data for Yakutsk

  1. On the Google Earth map, click on the Yakutsk placemark to access the data files from this borehole. The "Borehole dataset" link will take you to the NSIDC Web site where the data for this chapter is housed. If this link is not available, you can download the files from the links below.
  2. Click on the following link to download the files from the NSIDC ftp site. This is the "official" location for the data.
    Excel Files of Subset data for all 5 locations opens in a new window. Once in the download window, click on the files that you would like to download. Then right-click (PC) or control-click (Mac) on its name and choose "Save Target As..." or "Save Link As..." Save the files to a location where you can easily find them, such as your Desktop or Documents folder.
  3. If you are not able to access the files from the link above, you can download these back up files from the links below. To download the files, right-click (PC) or control-click (Mac) each file and choose "Save File As...". Save the files to a location where you can easily find them, such as your Desktop or Documents folder.
  4. Place the hpip_yakutsk_borehole_data.xls file in the Permafrost EET chapter folder that you created in Part 2, or in another convenient location.

Step 2 – Launch Excel, Open and Explore the Yakutsk Borehole Data File

  1. Launch Excel, by clicking its icon in the Dock (Mac) or choosing it from the Start menu (PC).
  2. Choose File > Open... and navigate to the location where you saved the hpip_yakutsk_borehole_data.xls file. Select the file and click Open.
  3. The "HPIP Yakutsk" Excel file will open. 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. Later, if you are interested, you can compare this data to the full dataset. (The full data files are located in the Going Further section of this chapter, under Variations.) When the file opens you should see four columns of data.

    file is open

  4. 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.
  5. Click on the "Jan soil & surface temp tab" and examine the data. On a Mac, the tabs are at the bottom of the spreadsheet. PC users may have to switch to "Full Screen" view to see the tabs.
    where to find tabs
  • On the chart, Column A is the year of the record from 1915 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. Soil temperatures were measured at a number of depths ranging from 0.02 to 3.2 meters. However, for this exercise only two depths are included, 0.8 meters and 3.2 meters. The instruments used to measure the temperature are specialized thermometers that can withstand the harsh conditions in the soil and ice.
  • 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.)
  • Missing data is indicated by the number -999.0.
  • Scroll through the data while considering the following questions:
    • What years have missing borehole data?
    • Why do you suppose two borehole temperature depths are given?
    • 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.

    1. Begin by cleaning up the data. To accomplish this, substitute a blank value (empty cell) for -999.0. Choose Edit > Replace..., or on a PC Find & Select in the editing tools, to open the Replace dialog box. In this box, next to Find what: enter the number -999; check the "Match case" and "Find entire cells only" check boxes; leave the Replace with: field empty; and then click the Replace All... button. Excel will search the spreadsheet and make 32 replacements. When it is done, click OK, and then close the window.
    2. Next, 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.
    3. Once your chart is drawn, use the Formatting Palette, and other formatting tools to finalize the chart. Choose Temperature, in degrees Celsius, for the Y-axis; choose Years, from 1950 to 2000 for the X-axis. Set the intersection of the two axes at -60.0 degrees. (Recall that permafrost is frozen.)
      • One line of the graph is soil temperature at 0.8 meters.
      • A second line is soil temperature at 3.2 meters.
      • The third line for surface temperature.
    4. Click on the Legend (right-click with a PC), where it says Series 1, 2 and 3. Then, choose Chart > Source Data.... In the dialog box that opens, edit the names of labels of your lines. Click OK when you have made your changes.
      • Name Series 1 - soil temperature at 0.8 meters.
      • Name Series 2 - soil temperature at 3.2 meters.
      • Name Series 3 - surface temperature.
    5. Once you have created the plot, make a trendline for each set of the three sets of data.
    6. When you have completed the graph, including the trend lines, answer the following questions:
      • What do you notice about each of the trend lines?
      • Compare the permafrost time series plots with surface temperature plots for this station. Do you notice any patterns?
      • Describe your observations.
    7. Next, repeat the process for July temperatures. Before graphing, you will need to clean up the data, as described in Step 3.
    8. When you are finished, your plots should look similar to the ones shown below.
      9. If you have difficulty with this section or just want to check your work, click the link to download a completed Excel file with both graphs. HPIP Yakutsk Borehole data with completed graphs (Excel 48kB Feb18 11)

    Step 4 – Compare January and July Plots

    1. Place the two borehole data graphs side by side and compare the data. What do you notice when you compare the trend lines for January and July for each set of data?
      2. Here is what Tingjun Zhang, NSIDC permafrost scientist, says about relationships between surface temperature and soil temperature at different depths:
      Soil temperature at depth reflects changes in air temperature at the surface, but with a time lag. For example, the temperature at 0.8-meter depth changes to reflect changes at the surface with about a 2-week lag, while the temperature at 3.2-meter depth has a 6-month lag. Rapid fluctuations are filtered out as well: day-to-day, or high frequency, fluctuations are not seen at depth.

      To say that soil temperature is rising in response to rising surface air temperature is a valid generalization. But on a case-by-case basis it is not so simple. At Yakutsk, for example, the presence or absence of snow cover, and the depth of the snow, plays a large role. In winter, snow insulates. In summer, as the snow that fell in the previous winter melts it increases the soil moisture. As this moisture in the soil evaporates, the process of evaporation cools the soil (just like sweat serves to cool your skin). Locally, topography, land cover, and weather play an important role in the response of the permafrost to changes in surface temperature. The July 0.8-meter borehole temperature has decreased between the years 1950 and 2000, potentially as a result of wetter summers.

    2. Select another station, plot and examine the time series display for that station. Do you see any similarity to your first station, Yakutsk? Choose from the files linked below. Note: Several of these files do not include surface air temperature data.
      Click on the following link to download the files from the NSIDC ftp site.
      Excel Files of Subset data for all 5 locations opens in a new window. Once in the download window, click on the files that you would like to download.
    3. Optional: Repeat this procedure with the other three remaining stations; what patterns do you observe in the data?

    Step 5 – Save your Excel Spreadsheet and Graphs

    1. Optional: Create a new word-processing document. Select and copy your graphs and paste them into the document so you easily can compare them.
    2. Save your worksheet and graphs in the Excel file by choosing File > Save As... and giving each of them an appropriate name. Save your files in the folder with the other work for this project.
    3. Quit Microsoft Excel.