280 likes | 556 Views
Lab 5: Pivot Tables / Pivot Charts Simon: Chapters 7 & 8 . URBPL 5/6010: Urban Analysis University of Utah Pam Perlich Rev. 09/27/2006. Files for this Lab. CreatePivotTable.xls AddCalcField.xls GetPivotTable.xls Optional : PivotChartDB.mdb Project 4. Pivot Tables.
E N D
Lab 5:Pivot Tables / Pivot ChartsSimon: Chapters 7 & 8 URBPL 5/6010: Urban Analysis University of Utah Pam Perlich Rev. 09/27/2006
Files for this Lab • CreatePivotTable.xls • AddCalcField.xls • GetPivotTable.xls • Optional : PivotChartDB.mdb • Project 4
Pivot Tables • Summarize and analyze database files • Database files • Record based • Every column must have a title • Maximum size: 8,000 total items and 256 fields • Data Pivot table • Cross tabs of data • Row by Column for Data
Create a Pivot Table • Open CreatePivotTable.xls • Study the rectangular data set • Notice the columns with headings • Select the entire data block • Data PivotTable and PivotChart • Pivot Table / Chart Wizard appears
Pivot Table Wizard • Data source is an excel database • Objective is to create a pivot table • Make sure you have the entire data block selected (including headings) • Step 3 • Put data in a new worksheet • Click “Layout” to specify configuration
Insert a Calculated Field • Open AddCalcField.xls • Select a specific location (cell) for calculation (e.g., F6) • Insert Calculated Field • Within dialogue box • Name the field • Specify the formula • Insert variable names if necessary • Notice how Excel automatically adds fields to all products and areas
Insert a calculated field 1) Select cell 2) Insert => calculated field
Link Pivot Table Results to Table • Open GetPivotTable.xls • Specify desired location of value • Insert Function GETPIVOTDATA
Retrieve value from pivot table • 1) Insert => Function => GETPIVOTDATA • Specify function arguments • =GETPIVOTDATA(PivotTable!C4,PivotTable!A5)
Pivot Chart • Create with same principles • Capabilities • Change chart type • Change computation (from sum to average, etc.) • Add and alter fields • Format charts
Pivot Chart from Database(Optional – time permitting) • Open a new blank excel workbook • DataPivot ChartExternal data source • Dialogue box opens Get Data • MS Access Database • Navigate to and select PivotChartDB.mdb • Select and import all fields • Drag fields onto graph to create crosstabulations
Project 4 - Overview • Review state estimates (from the Bureau of the Census) to establish control totals • Retrieve and review data and documentation • Prepare data set for pivot work • Build pivot tables/charts • Reformat and prepare final presentation • Interpret results
Note state control totals and categories Race and ethnicity are distinct 2,469,585 is 7/1/2005 estimate for Utah
Project 4: Data Retrieval http://www.census.gov/popest/estimates.php Estimates Data Counties County estimates by demographic category Download entire data set County estimates by demographic characteristics - age, sex, race, and Hispanic Origin Race and Hispanic Origin 6 race groups - 5 race alone groups and one multiple race group State datasets http://www.census.gov/popest/counties/asrh/CC-EST2005-RACE6.htm
Retrieve and Review Layout and Documentation Files CC_EST2005_6RACE_layout.txt compraceho.html Note: The original data set has been reclassified to eliminate the “Some Other Race” category and distribute this across all race categories.
Reformat the Data Set For Pivot Table Analysis • Change the time labels to match those in the file layout document (e.g., time = POPESTIMATE2001 => July 1, 2001; etc.) Note that there are two entries for April 1, 2000 – the decennial enumeration and an adjusted estimates base. These will vary in some cases. • Use search and replace operations (or some other method) to change codes to labels in the sex, origin, and race columns (e.g., search and replace “0” in the sex column with “Total” etc.)
2,469,585 is 7/1/2005 estimate for Utah – It matches our result. Why is “grand total” double this amount?
Data Set Tips • Note that if you include all race categories and Hispanic, not Hispanic, and Total, and male, female, and total, that you get a number that is MUCH too large. • Grand total will be 4 times the state total population estimate. • Select only a subset of fields to extract.
Error Check Totals Make sure you “totals” and “grand totals” for all counties match the reported state population estimates: http://www.census.gov/popest/states/tables/NST-EST2005-01.xls