Part 3 — Prepare the Data for Graphing

In Excel, select File->Open. In the window that opens, select the option to Enable All Readable Documents and open Weather_2000.txt. Use the Text Import Wizard to format the data, and save the reformatted file as a Microsoft Excel file with the file name Weather_2000.xls.

Show Details

Hide

1. Open Excel, and use File->Open to open the Weather_2000.txt file. This will automatically open the Text Import Wizard, which formats the text data for use in the Excel spreadsheet. Notice that you are now in Step 1 of 3 of the Text Import Wizard, and that the data that appears in the box at the bottom of the Wizard is separated (or delimited) by commas. Keep the default setting for Original data type, which is Delimited.

   The consecutive numbers you see along the left edge of the data represent the rows of the Excel spread sheet you are building. You will want to have the column headings for the data (in this example, COOPID, WBANID, Prelim, 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.
   



2. You are now in Step 2 of 3 of the Text Import Wizard. Change the Delimiters option from the default setting, which is Tab, to Comma, since the data in the original text file uses commas to separate it. Click Next.
   


3. You are now in Step 3 of 3 of the Text Import Wizard. Keep the default setting for Column data format and click Finish.
   


4. Save the file that opens as a Microsoft Excel file type, so that the file name changes to Weather_2000.xls.
   

In Weather_2000.xls, eliminate the empty row that appears at the end of every month. This will result in 367 rows of data, one row for the column headings and 366 rows of weather data for the Leap Year 2000.

The letter T in Column M stands for Trace, and means that there was precipitation on that date, but that it was less than one hundredth of an inch. The T may interfere the the graphing. To be safe, replace it with a numerical value. Use the Find and Replace functions of Excel to replace the T's in column M with the numerical value 0.

Save the change.

Show Details

Hide

1. Scroll down the Weather_2000.xls file until you find the empty row that exists between the January and February data. Highlight the empty row and delete it by going to the menu bar and using the Edit->Delete function.
   


2. Continue this process until all eleven spacer rows have been eliminated.
   Deleting the rows will allow the precipitation data in Column M to align with the stream flow data, which does not have a space at the end of each month.
3. The letter T in Column M stands for Trace, and means that there was precipitation on that date, but that it was less than one hundredth of an inch. The T may interfere the the graphing, so it needs to be replaced with a numerical value.
   • Highlight the M column.
   • In the menu bar, under Edit, open the Find function
   • In the window that opens, under Find What enter T.
   • Click the Replace button and under Replace with what enter the numerical value .005.
4. Save the changes.

The procedure for importing flowsud_2000 into Excel is the same as the procedure described in Step 1 for importing Weather_2000.txt into Excel, with two important differences.

A. In Step 1 of 3 of the Text Import Wizard, you will see quite a bit of information above the streamflow data that is important, but that does not need to be in the final Excel spreadsheet. To keep this information out of the spreadsheet, change the setting in the box labeled Start import at row to correspond with the row immediately above the data (the heading row). In this case, the headings are in row 27, so change the Start import row to 27.

B. The flowsud_2000 data is tab-delimited and not comma delimited. Therefore, in Step 2 of 3 of the Text Import Wizard, leave the Delimiters setting as Tab.

Save the file as a Microsoft Excel file, flowsud_2000.xls

Highlight and copy the 367 rows in column M of Weather_2000.xls.

Show Details

Hide

1. Highlight and copy the 367 rows of data in Column M of Weather_2000.xls.
   
   You will be pasting this precipitation data alongside the streamflow data in the file flowsud_2000.xls.

Open flowsud_2000.xls. Insert a new column between the date column and the streamflow column, and paste the precipitation data into the new column so that it aligns properly with the dates. Save the file.

Show Details

Hide

1. Open flowsud_2000.xls. Highlight column D, which holds the streamflow data, and from the menu bar select Insert->Columns, to insert a new column D between the date column and the streamflow column.
   
2. Paste the precipitation data into this empty column.
   The reason for inserting precipitation data between the date column and the streamflow column is related to the way the data will appear in graphs.


3. Save the changes.