Dry Spells, Wet Spells: How Common Are They?

1. Do your background research so that you are knowledgeable about the terms, concepts and questions.
2. If you are not familiar with using a spreadsheet program, be sure to take the time to go through the Excel tutorial listed in the Bibliography.
3. Here is a short version of the data analysis steps you'll be following in order to create your drought frequency histograms. Use the links to jump down to the detailed sections. Use your browser's "back" button to return to this brief list:
   1. Download the historical data and import into Excel. (Downloading and Importing Data)
   2. Format the data for statistical analysis. Copy the data for each climatic region to a separate page (there are nine separate regions, plus an averaged data set for the entire continental U.S.). You should also add column headers to each page to identify the data columns. (Formatting the Data)
   3. Calculate PHDI category frequencies for each region. (Calculating PHDI Category Frequencies for Each Region)
   4. Create frequency histograms for the PHDI categories for each region. (Creating Frequency Histograms)
   5. Compare the results. (Comparing the Results)
4. If spreadsheets are something new for you, then the detailed explanations that follow should help. If you are already comfortable with using spreadsheets, then you should be in good shape on your own.

Downloading and Importing Data

1. Download the State/Regional/National PHDI data file from NCDC. Right-click on the following link and select "Save As..." to save the PHDI data file on your computer. (To see all of the other available data files, use the link in the Bibliography.)
2. Import the saved climate data into your spreadsheet program. Here's how to do it in Excel:
   1. From the menu, select File/Open.... You'll see a dialog like the one here.
      
      
      
      
      
      
   2. At the bottom of the File Open dialog, under "Files of type:" use the drop-down list to select "Text Files (*.prn, *.txt, *.csv)".
   3. Navigate to the directory where you saved the climate data file ("drd964x.phdist.txt"), select it, and click "Open."
   4. Excel now takes you through the Text Import Wizard, a series of three dialogs. The first dialog looks like this (climate data file shown):
      
      
      

      Excel text import wizard (step 1) with "Fixed Width" selected under original data type. The text import wizard also includes a preview of the selected data format.

      
      
      
   5. Make sure "Fixed width" file type is selected, then click "Next."
   6. The second Text Import Wizard dialog is used to set the field widths. It looks like this:
      
      
      

      Excel shows an example of how the text file is going to be imported. The data needs to be recognized and broken into rows and columns for analysis.

      
      
      
   7. The lines with arrows show where Excel will be breaking the data into columns. (Table 2 in the Introduction describes how the data is arranged in the file.) Check to make sure that each of the data columns has been recognized (use the horizontal and vertical scroll bars to view all of the columns).
   8. For the PHDI data file, you'll probably find that you need to add two column breaks: 1) after the 3-digit region code (cols 1–3) and 2) after the data code (col 5). The image has the two additional column breaks (compare with the Text Import Wizard image). Also, note that the dialog box has instructions for adding, moving and deleting column breaks.
      
      
      

      For the PHDI data file, you'll probably need to add two column breaks: 1) after the 3-digit region code (cols 1–3) and 2) after the data code (col 5). Also, note that the dialog box has instructions for adding, moving and deleting column breaks.

      
      
      
   9. When all of the data columns are set to your satisfaction, click "Next".
   10. The final step of the Text Import Wizard is to select data formats for each of the columns, as shown here.
       
       
       

       Excel text import wizard (step 3) with "General" selected under column data format. The text import wizard also includes a preview of the data format selected.

       
       
       
   11. The default selection, "General", is what you want for any column with numerical data. Since all of the data in the file is numeric, the default selections are fine, so click "Finish" to import the data.

Formatting the Data

The first part of the file contains data for the individual states, followed by regional averages for nine separate regions (described in Table 3 in the Introduction). At the end of the file is one final data set with averages over the entire continental U.S. This project uses the regional data. In this section, you will be copying the data for each region and pasting it onto its own worksheet. You'll also add a header line to identify each data column. You'll also learn a little trick for keeping the header line visible even when you're scrolling through the data.

1. Let's start with the header line first, since it will make it easier to navigate through the data file. Here's how to add a header line to identify each data column.
   1. First you'll need to insert an empty row. Right-click on the row label for row 1, and then select "Insert" from the popup menu, as shown here:
      
      
      

      An empty row is inserted by right-clicking row 1 and selecting insert.

      
      
      
   2. The data columns are described in Table 2 in the Introduction. Your spreadsheet should have data in columns A–O. Label column A "Region Code". Label column B "Data Code". Label column C "Year". The remaining twelve columns are the months of the year. Label each with a three-letter abbreviation.
   3. Select row 1 and then use the menu to select "Format/Column/AutoFit Selection."
   4. If you wish, you can also make other formatting adjustments (bold, centering, gray background, etc.) to the header line (see example here).
      
      
      

      The column label begins with Region Code for column 1, Data Code for column 2, Year for column 3, and the months of the year starting from January for the remainder of the columns 4-15.

      
      
      
   5. To keep this header line visible even when you scroll down in the file, first select the row here the header line (row 2). Then, use the menu to select "Window/Freeze Panes." That's it! Your header line will stay put when you scroll down in the file.
2. Here's how to copy the regional data to individual worksheets.
   1. Insert a blank worksheet by using the menu to select "Insert/Worksheet."
   2. Select the data for one region. The nine different regions (described in Table 3 in the Introduction) have Region Codes in the range 101–109. You can scroll down with the mouse to find the next region, and make your data selections by clicking and dragging (which is easy, but gets tedious after awhile). Or, you can do a little math to calculate where each block of data begins and ends and use the "Name Box" to do the selections by typing.
   3. Once you've selected all of the data (from 1895 to present) for a single region, copy it and then paste it in to the new, blank worksheet. Paste it in starting at row 2, to save a row for the header line.
   4. It's a good idea to name the page with an abbreviation for the region so you can keep track of the data more easily. To re-name a page, you can double-click on the tab at the bottom, then type in the new name.
   5. Repeat these steps for each of the nine regions.
3. Finally, you can copy the header line to each of the pages and do the "Freeze Panes" trick.

Calculating Drought Severity Frequencies for Each Region

Now the real fun starts. You have the data for each region on its own worksheet, and now you can easily create formulas to calculate how frequently the various moisture conditions occurred for each region. Table 1 in the Introduction defines seven different moisture conditions, ranging from "Extreme Wetness," to "Extreme Drought." You want to know how frequently each condition occurred over the last hundred-odd years in each region. The information is all there in the worksheets, but how do you get it out?

The answer is, "By counting!" Fortunately, you don't have to count by hand. You can have Excel count for you. At the bottom of each regional worksheet, you're going to make a table like this one:

The moisture condition table has a totals column that adds the values for each row. This moisture table has 7 different moisture levels across 12 months with a totals column and frequency column at the end of the table.

There are seven rows, one for each moisture condition. For each of the twelve month columns (Jan–Dec), you'll have Excel scan through all hundred-plus years of data and count the number of occurrences of each condition. Then, you'll add up the totals (across and down, as shown by the arrows), and use the totals to calculate the frequency of each condition. The rest of this section will take you through step-by-step.

1. To do the counting, you'll write a formula for each moisture condition using Excel's "COUNTIF()" function.
   1. COUNTIF() will go through a range of cells, counting each cell whose value meets the criterion that you specify. COUNTIF() takes two arguments: the range of cells to scan, and the criterion for counting.
   2. Table 1 shows that "Extreme Wetness" corresponds to the range PHDI >= 4.00.
   3. So, to count all of the cells that belong in this category for January, you would click on cell D115 and type in: =COUNTIF(D$2:D$113, ">= 4.00").
      
      
      

      The first cell in Extreme wetness for January uses a formula to count all January cells that have a value greater than or equal to 4. The COUNTIF function is used to accomplish this.

      
      
      
   4. Table 1 shows that the next condition, "Severe Wetness," corresponds to the range 3.00 <= PHDI <= 3.99. How do we count a range with COUNTIF()? You ask Excel to count all of the cells with PHDI values greater than or equal to 3.00, and then to subtract the counts that are above 3.99. Like this: =COUNTIF(D$2:D$113,">= 3.00") - COUNTIF(D$2:D$113, "> 3.99").
      
      
      

      The first cell in Severe wetness for January uses a formula to count all January cells that have values between 3 and 3.99. The COUNTIF function is used to accomplish this so manual counting is not needed. This step is repeated using the table values for the rest of the rows.

      
      
      
   5. With these two examples, you should be able to fill in the rest of the formulas. Use Table 1 to find the ranges for each condition.
   6. Once you've filled in the formulas for January, you can copy them to the other months.
   7. The next step is to calculate the totals for the rows and columns. Use Excel's SUM() function for this.
      
      
      

      Each row and column is added together using the SUM function in excel. The SUM function allows you to select certain cells (like each value in January), and add them at the bottom of the table. This is done for every row and column in the table.

      
      
      
   8. Column P now contains the totals for each condition for the entire time period. You can use column Q to show the frequency for each condition. For each category, calculate the proportion of the total counts and multiply by 100. An example is shown here:
      
      
      

      Table showing the frequency of wetness conditions by month from extreme wetness to extreme drought. Overall frequency is calculated by taking the total value of each row, dividing it by the total of all rows, and the multiplying it by 100.

      
      
      
   9. Notice that the totals in Row 123 serve as a way to check your work. Each cell should be counted once and only once, so the totals for each column should equal the number of years that data was collected for that column. (This file was from February, 2006, so January has 112 years of data and all the other months have 111 years of data.) The frequency column (Q) should add up to 100%. If your numbers don't add up right, check your COUNTIF formulas carefully. Make sure the ranges correspond exactly to what is in Table 1.
2. When your table for calculating frequencies is working properly, you can copy it to each of the other regional worksheets.

Creating Frequency Histograms

1. Now that you've generated the data, creating the histogram is easy. Start by selecting the frequency values in column Q, then use the menu to select "Insert/Chart..." You'll see a dialog like the one here:
   
   
   
   
   
   
2. Select the "Column" Chart Type and then push "Next."
3. In the "Source Data" dialog, select the "Series" tab. The "Values" field should already be filled in from your selection. Select the category labels from column A as the "Category (X) axis labels." Name the series for the region, if you wish. Then press "Next."
   
   
   
   
   
   
4. The next dialog is for Chart Options. You can use the tabs to make some adjustments to the graph, like adding titles and removing the legend.
   
   
   
   
   
   
5. In the last dialog, you select a destination for your graph, then press "Finish."
   
   
   
   
   
   
6. If you want to make additional tweaks to the appearance of the chart, you can usually get to the appropriate dialog by double-clicking on the item you want to change.

Comparing the Results

1. Create histograms for each of the regions.
2. Be sure to make all of them the same size and scale so that you can make direct comparisons.
3. You may also want to create a single large histogram with data from all of the regions. Some comparisons are easier to make on a single, large graph, and others are easier to make with multiple, small graphs.
4. Study the graphs and see what differences, similarities and patterns you can find.
5. Identify the regions on a map of the U.S. What geographical features might explain some of the patterns you found in the climate data?