Tips for Data Manipulation: Tutorial
|
Information to get started:
- The lesson below contains step-by-step instructions and "snapshots" of what each step
looks like when carried out in a Microsoft Excel workbook. Blue shading of information
in the Excel illustrations denotes changes made from the previous step. Dots placed in
three consecutive rows indicate that a portion of data is hidden from sight.
- You can download an Excel workbook containing the complete data set by clicking on the
"Download Data" link below. It contains
each calculation step on a separate worksheet. To move between steps, click on the
tabs at the bottom of the excel window.
- When you download the file, it may open in your browser window. You may wish to use the
"save as" function to save the file to a local drive and then reopen it in Excel. This
will make it easier to flip between the online lesson and the example workbook.
- Finally, we want to remind you that the techniques explained on this site are statistically
based; therefore results must be viewed as predictions and not as facts. Please use
the techniques and the information obtained from them responsibly!
|
Step 1: Copy Daily Streamflow Data from USGS web site into Excel Spreadsheet
- Go to http://oregon.usgs.gov
- On the left sidebar, under Historical Data, select Streamflow
- Select Daily Data
- Check box under Site Identifier for Site Name and Submit
- Type in Alsea
under Site Name and select match any part and Submit
- Select gage near TIDEWATER
(14306500)
- In the Retrieve data from boxes enter the date range of "1990-10-01"
to "2000-10-01"
- Select Tab-separated data and Display in browser and
Submit
- Select the
entire data set to copy
- Paste Special as text (this will separate the data
into columns)
Step 2: Organize spreadsheet with data
- Eliminate extraneous data (i.e., column E)
- Add titles to remaining Four
columns
NOTE: Data are listed in water years, hence 10/1/1990– 9/30/1990
is Water Year 1990.
Step 3: Obtain Monthly Averages
MANUALLY
- In fifth column, use the average function in Excel to obtain the average for
each month for the first four years.
Copy the Monthly Average Column for the first four years and paste to rest
of data set. This will compute the monthly averages for the remaining years.
- Organize Monthly Data by Year
IF USING A USGS GAGE, MONTHLY STATISTICS ARE ALREADY CALCULATED
- Go to http://oregon.usgs.gov
- Select Historical Water Data
- Select Surface Water
- Select Statistics (Monthly)
- Check box under Site Identifier for Site Name
and Submit
- Type in Alsea under Site Name and select match any part and
Submit
- In the Retrieve data from boxes enter the date range of "1990-10-01" to "2000-10-01"
- Select Tab-separated data and Display in browser and Submit
- Select gage
at TIDEWATER (14306500)
** Data included in this table are for all of the Alsea gages, be absolutely
certain that you are using the data for your desired gage (in this case 14306500)
- Select Data Set for desired gage to copy
- Paste Special as text (this will
separate the data into columns)
Step 4: Obtain Annual Averages
MANUALLY (Annual Averages are done based on
Water Years 10/1/XX-9/30/XX)
- In Fifth Column, use the average function in Excel to obtain the average for
each water year for the first four water years in the period of record.
- Copy the Annual Average Column for the first four years and paste to rest
of Data Set. This will compute the annual averages for the remaining years.
- Organize Annual Data by Year for Period of Record
IF USING A USGS GAGE, ANNUAL STATISTICS ARE ALREADY CALCULATED
(Annual averages
are based on calendar year 1/1/XX – 12/31/XX)
The monthly values can be
used to generate mean annual flows by water year.
- Go to http://oregon.usgs.gov
- Select Historical Water Data
- Select Surface Water
- Select Statistics (Annual)
- Check box under Site Identifier for Site Name
and Submit
- Type in Alsea under Site Name and select match any part and
Submit
- In the Retrieve data from boxes enter the date range of "1990-10-01" to "2000-10-01"
- Select Tab-separated data and Display in browser and Submit
- Select gage
at TIDEWATER (14306500)
** Data included in this table are for all of the Alsea gages, be absolutely
certain that you are using the data for your desired gage (in this case 14306500)
- Select data set for desired gage to copy
Paste Special as text (this will separate the data into columns)
|
