Part 2—Prepare Data for Analysis
Step 1 –
Decompress the Data from EOS EarthData
After you've downloaded the two data orders, you will need to decompress the data. There are many software utilities available to do this, many of which are free of charge.
Once you've decompressed the data, you will have six files per state: four data files (one each for minimum temperature, maximum temperature, solar radiation, and precipitation) and two auxiliary files (Note: these are not included in the archived files linked in Part 1). The auxiliary files provide background information about the data.
Step 2 –
Import the Data into Microsoft Excel
Import each of the files into one of four separate Microsoft Excel spreadsheets, one workbook file for each of the four types of data. The result will be four 'master files' that contain similar types of data for both California and Minnesota. Begin by creating a 'master' file of the precipitation data. The master file will contain all the precipitation data for California and Minnesota.
- Launch Excel, by clicking its icon in the Dock (Mac) or choosing it from the Start menu (PC).
- Choose File > Open... and navigate to the location where you saved the 12812pptTC-C1.txt file. Select the file and click Open.
- Choose File > Open... and navigate to the location where you saved the 12812pptTC-C1.txt file. Select the file and click Open.
- Single click to select the file. Then click Open.
- The "12812pptTC-C1" Excel file will open. The Text Import Wizard will automatically launch. Follow the prompts to format the text data for use in an Excel spreadsheet. Next, save the reformatted file as a Microsoft Excel file with the file name Precipitation.xls.
- Launch Excel, and choose File > Open to open the Precipitation.txt file. This will automatically open the Text Import Wizard, which formats the text data for use in the Excel spreadsheet. The data is separated (or delimited) by tabs, so click the Delimited option.
You will want to have the column headings for the data (in this example, Center Lon, Center Lat, 2000, 2001, etc.) in Row 1. Therefore, if those headings are not already in Row 1, change the number in the Start import at row box to correspond with the row number for the headings.
Click Next.
- Change the Delimiters option from the default setting to Comma, since the data in the original text file uses commas to separate it. Click Next.
- Keep the default setting for Column data format and click Finish.
- Next, select the header information rows and delete them from the file.
- Label the data using the name of the state. You may find it useful to insert a few rows into the worksheet. Since this master file will have data from both states, you will need to identify which data come from which state. Above the rows of data, enter the label "California Precipitation (mm/yy).
- Save the file that opens as a Microsoft Excel file type, so that the file name has the .xls. Give it a name similar to Precipitation.xls. Note: Depending upon your version of Excel, your file might have a slightly different extension.
- Repeat Steps 1-3 for Minnesota and label the state file for Minnesota. Next, highlight all the data in the Minnesota file and copy and paste the contents to the bottom of the California file. Check your completed file to ensure that the California data is on top and the Minnesota data is on the bottom. The column headings should be consistent for both sets of data.
- Save your Precipitation.xls file.
- Repeat Steps 1-5 to create master files for minimum temperature, maximum temperature, and solar radiation. Each master file should have the data for both California and Minnesota.
Step 3 –
Create State Averages from the Data
Average the gridcells in each state for each year to obtain a state average for each of the states.
The datasets that you downloaded are in half-degree by half-degree gridcells for each state (scientists refer to this as the 'spatial resolution of the data'). In order to make state comparisons, we will need to calculate a state average for each state from the gridcell data.
- Clean up the data file by removing missing values from cells. These are coded as -9999. These values must be deleted from the worksheet before the gridcell data can be averaged. Use the Excel Replace feature to clean up the data.
- From the main menu, choose Edit > Replace. Replace all the -9999 numbers with blank cells.
- On your worksheet, check that there is space between the bottom of the California data and the top of the Minnesota data. You may want to insert a couple of rows in this area of the spreadsheet. To do this, from the main menu, choose Insert > Rows.
- Place your cursor in the blank cell in the first column of your worksheet below the California data and click on this cell. Since you will be creating state averages from this gridcell data, label this row with the name of the state and the type of data; i.e. California Precipitation.
- Next, place your cursor in the cell below the column of gridcell data that you want to average. This column of data corresponds to the year 2000 and should be the third column in the worksheet—in this example the cell is C111. (The first two columns are longitude and latitude). Click on this cell. To calculate a state average for this column of data, use the Excel Average formula. Type into this blank cell the text:
=Average(
- The equals sign before the word 'Average' tells Excel that you want to calculate a new value from data in the spreadsheet. Since you want to calculate a state average for each column of data (the years) select the column of data that corresponds to the first column of precipitation data. Be careful and only select the data, and not the row containing the Year labels.
- Once you have selected the column of data, you need to close the formula by using a close parenthesis. ")"
- Your formula should look something like this (Your data range may vary slightly depending on the number of blank rows you've added).
=AVERAGE(C10:C181)
- Next, with your cursor still in the cell, press Return to move your cursor from the cell and for Excel to complete the computation. Excel will compute the average for this column of data.
- The next step is to copy the formula that you just created to all the cells to the right of the formula that correspond to the years 2001 through 2100. Put your cursor in the cell that contains the formula. While your cursor is still "selecting" the cell, grab the lower-right hand corner of the cell and drag it your selection area across the entire row. The formulas will be filled in for each cell to complete the averages for the years 2001 through 2100.
- Almost done! Finally, convert these formulas to values by selecting the entire row of data and then selecting Edit > Copy. Go to the cell where you want to place the average and choose Edit > Paste Special. Paste the data as Values.
- Repeat Steps 4-11 for the Minnesota data.
- Copy and paste the averaged data and the year label into a new workspace on your spread sheet. Your spreadsheet should now look like this.
- Choose File > Save As... to save the file with a new name. Because you will add similar data for precipitation and solar radiation to this file, you may want to name the file something like 'climate data'.
- Repeat Steps 1 through 11 to create files of state average data for minimum temperature, maximum temperature, and solar radiation. Once you've computed the state averages, add these data to the master file you created in the previous step. Your completed spreadsheet should now look like this.
Note: you may wish to simplify your data by reducing the number of decimal places in the Excel cells. From the main menu, select Format > Cells > Numbers > Number and then confirm the number of Decimal places: is set to 2.
- Your four completed Excel files should look something like the ones linked below when you are done.
Step 4 –
Graph the State Averages
Use the Excel Chart Wizard to create line graphs of the state data and compare climate change projections between California and Minnesota.
- Open the spreadsheet that contains the state averages you calculated in the previous step. Select the Year row and the California and Minnesota data for any one of the four variables. In the example below, Precipitation is selected.
- Select Insert > Chart... or click the Chart Wizard button to graph these data.
- Select the XY (Scatterplot) chart type.
- Select the chart sub-type Smooth Lined Scatter.
Your chart should look similar to the one below. Note: Excel may assign colors randomly for your data points.
- Format your chart now to provide detail. From the main Excel menu, select View > Toolbox > Formatting Palette. Click once in the main body of your chart to select it and expand the Formatting Palette to include all its formatting features.
- Under Chart Options > Titles, make the following selections from the drop-down menu: Select Chart Title and enter "Projected Precipitation (mm/yy) 2000 - 2010"; select Horizontal (Category) Axis and enter "Year"; and select Vertical (Value) Axis and enter "Precipitation (mm/yy)".
- Next, you may need to modify the appearance of your chart. Double click on one of the X-axis labels. The Format Axis screen displays. Select Scale and adjust the default settings to the following:
Your final graph should look something like this:
- Repeat Steps 1 through 7 and create similar plots for minimum temperature, maximum temperature, and solar radiation. When creating each of the remaining three plots, adjust the scale following the instructions listed in Step 7 above. The following examples define the recommended scale settings for the Y-axes.
Minimum Temperature
Maximum Temperature
Solar Radiation
- Your final graphs should look something like the ones below.
Show me how to open the file
Hide
