Introduction to Missouri Census Data Center Data Tools
Webinar Presentation by John Blodgett, OSEDA Oct. 27, 2015
- The MCDC's Uexplore/Dexter software. Brief overview followed by
live examples of using Uexplore/Dexter to locate and extract data.
- Accessing ACS base/summary tables using Uexplore/Dexter.
- Quick look at MCDC web applications to display profile reports, and to do custom extracts from American Community Survey data.
- Overview and live examples of the MABLE/Geocorr web application.
Note that we have created video modules corresponding to the sample Dexter queries and MABLE/Geocorr samples. These
can be accessed at ctpp27Oct15_videos
- From MCDC home page (mcdc.missouri.edu) navigate to "MCDC Data Archive". (/applications/uexplore.shtml)
- Note tutorials and overview document links on this page. "On-line Tutorials" is the best link.
- Best metadata modules (IMHO) are:
- the Dexter Quick Start Guide (html and video versions), and
- the Dexter Online Help.
Both of these are linked to from the Dexter Query Form (the page you fill out to define your dexter query).
- The Archive Directory provides links to "filetypes" (data product categories).
- The acs2013 filetype directory page is generated by Uexplore. Alphabetic list of files with some description.
But it has a Datasets.html page which is much better for seeing what's in the data collection.
- Extract example 1: Pull selected data re poverty for the nation, states and counties. Sort by state and county
and create csv output file suitable for input to Excel.
- Select data set usmcdcprofile5yralt as data set. View the metadata page, and note esp the SumLev values.
- I. Choose a Listing/Report output. II. Filter rows based on values of SumLev and Geocomp. III. Use "Filter" button in
Sec. III to change display of Numerics using poor|poverty. (Click on section III header to see Online help regarding this).
- Run the query using "Extract Data" button. View output files, esp Summary. Copy list of variables.
- Go back to previous screen (Dexter Query Form) and modify query. Paste variable list into text box in
Sec.III. Add sort (by esriid) and enter a title. Then rerun query.
- Save the csv file to the desktop and open in Excel.
- Extract example 2: From acs2013 again, pull all the data (including MOE's and 5 additional data items taken from SF1, 2010 census) for all the
census tracts in the Philadelphia metro area. CBSA code is 37980.
- From the current DQF page follow link at top to the acs2013 directory page (link is after Data Set:).
- Click on file/data set ustracts5yr.sas7bdat .
- Right click on link to "detailed metadata" and open in new tab. Note Key variables link labeled CBSA.
- Back on DQF page be sure to specify "None" for Listing/Report. In II. specify filter: CBSA Equal To 37980
- In III. check at top to specify you want to keep ALL columns.
- Click an Extract Data button. Output can be saved to desktop and opened in Excel. (SAS people can
specify SAS data set output instead of comma-delimited, of course.) Note extra 5 variables on this data set that
were not on the previous usmcdcprofiles5yalt set. Two internal pt coords, 2 areas, and TotPopsf1.
- Base (Summary) Tables Overview
- From Uexplore page for acs2013 note the text describing the directory at the top of the page. Specifically
where it says the more detailed base (aka "Summary") tables...
- Select basetbls directory to display its contents.
- Look at TableTopicCodes.txt and discuss table naming conventions.
- Note that the data sets in the basetbls directory come in six pairs of two. The six are the table topic groups
and the two are for estimates vs. moe's. These are then sometimes repeated for periods (single year vs. 3-year).
- Data set ustabs00_073yr.sas7bdat contains data for topics 00 to 07 and contains 3-year period estimates.
- Data set ustabs17_20_moes.sas7bdat contains moe values for tables 17 to 20, single year data.
- All of the 5-year period estimates are stored separately in the btabs5yr subdirectory.
- These datasets are recognized by Dexter as being table based and you'll be given a menu of tables to
choose from instead individual numeric variables (columns).
- The Varlabs directory contains table definitions. The best metadata for searching for a table.
- Extract example 3: Pull two summary (base) tables from the vintage 2013 5-year period estimates
census tracts in the PA portion of the Philadelphia metro area. CBSA code is still 37980.
- Navigate back to the acs2013 uexplore page (there is a direct link at the top of the DQF if you are
currently accessing a data set in that directory).
- Choose subdirectory basetbls.
- Choose sub-subdirectory Varlabs. Note how these data have been partioned according to "table topic codes".
- Note that topic 08 is Journey to Work, Worker Characteristics. There are so many tables for this
topic that it constitutes its own Topic Group. View the 08labels.txt file in Varlabs. Note tables B08006 and B08012.
- Because we want to access data at the census tract level we have to use 5-year data. So we need to navigate
back to the acs2013 data directory and select the btabs5yr subdirectory.
- Select data set ustracts08. Click on it to display DQF for this data set.
- Code query. Leave output formats at default. Code filter in Sec. II as CBSA = 37980 -and- State = 42.
- Select tables B08006 and B08012 from right select list in Sec. III. Select state, county, tract as IDs.
- Note variable names as first row of output csv file. With labels in second row. Really hard to read in
browser. Much better in excel (we saved it as an xlsx file).
Web apps for accessing ACS and 2010 SF1 data.
- Go to MCDC home page and click on first item in the Quick Links box: ACS Profile Reports.
- Click on second item in the Quick Links box: ACS Profile extract app.
- Note that:
- there is an online video tutorial.
- Aimed at more casual user. You don't need to know state codes, summary levels, or even uexplore/dexter to use
(although Dexter will play a part).
- We want to extract all the data from the Economic Profile for all the PUMAs in Pennsylvania.
- Once dropped into Dexter just click on Extract Data buttom to get default csv output.
- The benefit of doing this through Dexter is that you can customize. E.g. specify a data filter such as
only choosing areas with median HH income over a certain amount. Or generating output as a dbf file instead of csv.
- Note that there are twin apps (see Quick Links box) that do the same sort of thing using data from the 2010 census, SF1.
This is a geographic utility application that can be useful when trying to relate geographic coverages to see how they
relate (intersect) based on population or land area. It can also be used to do simple "roster" ouputs such as listing all
counties in a state, or all PUMAs in the United States.
We just announced a revised version using some updated (more recent) geographic codes.
- In Colorado relate county to latest (2015) CBSA.
- List all towns in Massachusetts broken down by urban/rural portion.
- In PA show counties in the Philadelphia CBSA with Metropolitan Divisions.
- Do county-to-PUMA correlation with second afactor for multiple states.
- Display related links at top. Other versions (earlier geography back to 1990 vintage), lots of examples.
- Note link to MAGGOT file just above geocode select lists.