Part 3—Prepare the Data for Graphing

Step 1 –
Import Weather_2000.txt into Excel

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 Weather_2000.txt file. Select the file and click Open.

The "Weather 2000" 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 Weather_2000.xls.

Show Details

Hide
1. Launch Excel, and choose 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. 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, 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. 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.
3. 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 has the .xls extension to Weather_2000.xls. Note: Depending upon your version of Excel, your file might have a slightly different extension.

Step 2 –
Format Weather_2000.xls

Begin by cleaning up the data. In Weather_2000.xls, eliminate the empty rows that appear 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. This means that there was precipitation on that date, but that it was less than one hundredth of an inch. The T may interfere with the graphing. Replace all Ts on Column M with a numerical value of 0.0005 by using the Find and Replace function of Excel.
Save the changes.

Eliminating the rows will allow the precipitation data in Column M to align with the streamflow data, which does not have a space at the end of each month.

Note: the number 9999.9 is a code indicating that No Observation was made. In this example, the code 9999.9 does not appear in column M. If it appears in other examples, you will need to replace it with another value (possibly 0) since the value 9999.9 will greatly distort the graph.

Show me how to clean up the data

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 choosing Edit > Delete.

Continue this process until all eleven spacer rows have been eliminated.
Next, you will need to substitute the value 0.0005 for each occurrence of T. Highlight the M column.
Choose Edit > Replace..., or on a PC Find & Select in the editing tools, to open the Replace dialog box. In this box, under Find what: enter T; under Replace with: enter 0.0005; and then click the Replace All... button. Excel will search the spreadsheet and make 110 replacements. When it is done, click OK, and then close the window.
Save the changes.

Show me the Replace dialog box

Step 3 –
Import flowsud_2000 into Excel.

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.

In Step 1 of 3 of the Text Import Wizard, you will see 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.
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.

Show me the Text Import Details

Launch Excel, and choose File > Open to open the flowsud_2000.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 Row 1. Change the number in the Start import at row box to row 27 and click Next.

Keep the Delimiters option at the default setting of Tab. Click Next.

Keep the default setting for Column data format and click Finish.

Save the file that opens as a Microsoft Excel file type, so that the file name has the .xls extension to flowsud_2000.xls. Note: Depending upon your version of Excel, your file might have a slightly different extension.

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

Step 4 –
Copy the Precipitation Data from Weather_2000.xls

Launch Excel, and choose File > Open to open the Weather_2000.xls file.
Highlight column M and copy the 367 rows of data.

You will be pasting this precipitation data alongside the streamflow data in the file flowsud_2000.xls.

Show me what the screen looks like

Step 5 –
Paste the Precipitation Data into flowsud_2000.xls.

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

The order of the columns is significant because it will have an effect on the appearance of the graph generated from the data.

Show Details

Launch Excel, and choose File > Open to open the flowsud_2000.xls file.
Highlight column D, which holds the streamflow data, and choose Insert > Columns, to insert a new column D between the date column and the streamflow column.