1 / 22

Lab 3 Data Sources: Locating, Importing, Manipulating

Lab 3 Data Sources: Locating, Importing, Manipulating. URBPL 5/6010: Urban Research University of Utah Pam Perlich Revised 9/10/2006. Objectives. Pasting from other programs Importing from other programs Text HTML Picture Database Federal statistical agency web sites. Files for Lab 3.

Download Presentation

Lab 3 Data Sources: Locating, Importing, Manipulating

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Lab 3Data Sources: Locating, Importing, Manipulating URBPL 5/6010: Urban Research University of Utah Pam Perlich Revised 9/10/2006

  2. Objectives • Pasting from other programs • Importing from other programs • Text • HTML • Picture • Database • Federal statistical agency web sites

  3. Files for Lab 3 • CopyData.doc • CopyData.txt • TextColumns.xls • Database.mdb

  4. Link Data to Other ProgramsCopyData.doc • Open the word file • Select the text to be copied • Open Excel – Explore different paste options • Paste • Paste special • Text • MS word object • MS word object paste link • Change text in original document and it will update in the spreadsheet as well. • Picture • Go to a website and copy a graphic • Paste this into Excel

  5. Importing Delimited Text DataCopyData.txt • Open CopyData.txt in a text editor and examine contents • Data => Import (as delimited) • Files can be comma, quote, comma and quote, or tab delimited • Delimiters interpret and format the data

  6. Importing Fixed Width Data • www.census.gov • Select People & Households Historical Census Data Item 17: Urban and Rural Populations: 1900 to 1990 • Save this file to your computer (urpop0090.txt) • Examine the file in a text editor • Import it to Excel • Note: the title lines are not important. Just import the data to create a clean data set. It is a fixed width file.

  7. Changing Text to ColumnsTextColumns.xls • Open the file • Select the cell that contain data • Data =>Text to columns • Fixed width • Specify dividing columns • Finish

  8. Import Database FilesDatabase.mdb • Open Excel • Data => Import External Data => Import Data • Point to the database file • Examine the “properties” choices • Import to a new worksheet • Explore the record based nature of a database file

  9. Web Files • Import from a web page • Data => import external data => new web query • Navigate to www.bls.gov • Select any page of data and import • Alternative methods • Try to copy a data table from the web site and paste into Excel • Save data as HTML file then import it to Excel • Many organizations are building much better data downloading functionality into their websites.

  10. File Export • Explore the file  save as options • Try a few of these to test the results.

  11. Federal Statistical Agencies • Bureau of the Census • Bureau of Labor Statistics • Bureau of Economic Analysis

  12. American Factfinder • www.census.gov • American Factfinder • Click on “About the Data” and review the contents • Types of tables – Quick tables, geographic comparison tables, etc. • Data sets – Decennial census, ACS, etc. • Select each top level menu and examine contents – Population finder, fact sheet, etc.

  13. Select Decennial Census

  14. List all tables

  15. AFF Features – Project 2 • Data sets  Decennial Census  Census 2000  SF1 List all tables • Study the types – Quick tables, geographic comparison table, detailed tables (population and household) • Select P37: Group Quarters Population by Group Quarters Type  Next 

  16. Select Geo within Geo

  17. Select “All Places” Then “Add” Then “Show Result”

  18. AFF: Custom Tables & Geo within Geo • Select data sets to return to data menu • Select SF1  Custom Tables • Select “Geo within Geo” • Show me all the “Places” within a “County”  Select geography of your choice • Select P1 – Population; P37 – Population in Group Quarters; P16 – Population in Households • Select which data elements you need  Show results. • Explore the filtering option • Get you data into Excel and format it

  19. AFF: Explore Other Available Data Sets and Functions • Click “FAQ” • Click “How to Use it” • Spend some time reviewing this • If students have specific research issues, have the lab assemble the data.

More Related