|
| | 
Review
Project
 | Get Data: |
- Get Data - Access the following website: http://www.weather.org/weatherorg_records_and_averages.htm
. Now Click on US Average Rainfall.
- Select all the data (Select All - Ctrl + A).
- Copy the data that has been selected (Ctrl + C).
- Open Microsoft Excel.
- Paste the data (Ctrl + V).
| Parse Data (separate copied text data
into columns): |
- Highlight the data in column A by clicking the first item in column A
(NORMALS), then hold the shift key down and press the End key, then tap
the down arrow once to select all the cities.
- Click the Data menu, choose Text to Columns, click Fixed Width, click
Next, click Next again, and finally, click Finish.
- Next, we'll need to do some clean up. Double-click on the border
between column headings A and B to widen the information contained in
those cells.
- Right-click on column header B and choose Delete to delete column B.
Next we need to parse the data in Column A to separate the cities from
the states and place the states in a new column:
- Create 2 new columns in which to place the new data by selecting the
column B and C headers, then right-click and choose Insert.
- Resize the two new columns by clicking and dragging a column border to
the left a bit so the width is around 14.
- Click on cell A4 and select all the cells containing cities: hold the
shift key down and press the End key, then tap the down arrow once to
select all the cities.
- Choose Data, Text to Columns once again to parse the entries column A
so that the city information becomes separated from the state
information.
- Choose Delimited and Next, then Comma and Next, then Finish.
- Scroll through columns B and C to find any information labeled PC so
that we can delete any information for locations outside the United
States - Select row headers 207 - 216, then right-click the row headers
and choose Delete.
- Type in PA in cell B210, OH in cell B110, KY in cell B114.
Next we need to eliminate some spaces (so that
we can sort properly), a row and a column, and format the labels:
- Right-click on the column C header and choose Delete.
- Type STATES in cell B3.
- Then we want to eliminate the spaces in front of the cells in column B
that contain an extra space or two: select all of the cells that
contain the postal abbreviations for the states by clicking the first
cell (B3) and holding down the Shift key while pressing the End key,
then the down arrow key.
- While all of the state cells are selected, choose the Replace command
from the Edit menu or press Ctrl + H.
- Click in the Find what box and press the backspace key to delete any
extra spaces, then press the spacebar once.
- Click in the Replace with box and don't type anything.
- Click the Replace All button to replace all spaces in the selected
cells.
- Format the Labels row to make them bold and centered by clicking the B
and the center button on the formatting toolbar.
- Center the States column (column B).
| Manipulate the data |
Now that you have the information in a format that you can use, you can
start to manipulate the information.
Follow the directions in the Database Instructions handout available here.
(Word document to download - right-click and choose Save
Target As.)
Additional Resources
Files to use (right-click
and choose Save Target As)
|
