Part 3—Prepare the Data for Graphing

Step 1 –
Import Weather_2000.txt into Excel

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

Step 2 –
Format Weather_2000.xls

  1. 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 dataone row for the column headings and 366 rows of weather data for the Leap Year 2000.
  2. 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.
  3. Save the changes.
  4. 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.

Step 3 –
Import flowsud_2000 into Excel.

  1. 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.
  2. Save the file as a Microsoft Excel file, flowsud_2000.xls

Step 4 –
Copy the Precipitation Data from Weather_2000.xls

  1. Launch Excel, and choose File > Open to open the Weather_2000.xls file.
  2. Highlight column M and copy the 367 rows of data.
  3. You will be pasting this precipitation data alongside the streamflow data in the file flowsud_2000.xls.

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

  1. Open flowsud_2000.xls.
  2. Insert a new column between the date column and the streamflow column.
  3. Paste the precipitation data into the new column so that it aligns properly with the dates.
  4. Save the file.
  5. The order of the columns is significant because it will have an effect on the appearance of the graph generated from the data.