American Community Survey (ACS) Data

From edegan.com
Revision as of 18:24, 11 October 2019 by Ed (talk | contribs)
Jump to navigation Jump to search


McNair Project
American Community Survey (ACS) Data
Project logo 02.png
Project Information
Project Title American Community Survey (ACS) Data
Owner Jeemin Sim
Start Date
Deadline
Primary Billing
Notes
Has project status
Copyright © 2016 edegan.com. All Rights Reserved.


See also:

Notes

The approach below seems to have been superseded. However, it does look like it works if you choose the right data year -- 2012 ACS 1-year estimates -- seems to have all of the information needed.

One can download the PUMS data for 2017 here: https://www2.census.gov/programs-surveys/acs/data/pums/2017/1-Year/

And state-by-state, it looks possible to get the latest data profiles (2017 at present): https://www.census.gov/acs/www/data/data-tables-and-tools/data-profiles/

For just population: http://census.ire.org/data/bulkdata.html

File Location

Python Script to pull certain columns from excel file:

  • E:\McNair\Projects\Agglomeration\ACS_Downloaded_Data\pullCertainColumns.py

SQL Commands to create tables and load data:

  • E:\McNair\Projects\Agglomeration\ACS_Downloaded_Data\DataLoading_SQL_Commands

Excel files downloads from ACS website:

  • E:\McNair\Projects\Agglomeration\ACS_Downloaded_Data\csvFiles

Steps to Obtain Data

1) Go to https://factfinder.census.gov/faces/nav/jsf/pages/download_center.xhtml
2) Select 'I know the dataset or table(s) that I want to download.'
3) Press Next
4) For 'Select a program:' choose
       'American Community Survey'
5) For 'Select a dataset and click Add to Your Selections:' choose
       '<YEAR OF INTEREST> ACS 1-year estimates'
6) Press 'Add To Your Selections'
7) Press Next
8) For 'Select a geographic type:' choose
       'Place - 160'
9) For Select a state:
       Don't choose a state, as we wish to download all.
10) For 'Select one or more geographic areas...' choose
       'All Places within United States and Puerto Rico'
11) Press Next

Tables Pulled

S1501 EDUCATIONAL ATTAINMENT
S2301 EMPLOYMENT STATUS
B01003 TOTAL POPULATION
B02001 RACE
B07201 GEOGRAPHICAL MOBILITY
B08303 TRAVEL TIME TO WORK
B19013 MEDIAN HOUSEHOLD INCOME
B19053 SELF-EMPLOYMENT INCOME IN THE PAST 12 MONTHS FOR HOUSEHOLDS
B19083 GINI INDEX OF INCOME INEQUALITY
B25003 TENURE
B25105 MEDIAN MONTHLY HOUSING COSTS
B28011 INTERNET SUBSCRIPTIONS IN HOUSEHOLD
G001 GEOGRAPHIC IDENTIFIERS

Notes on Data

  • Educational Attainment are based on population of age 25 years or higher (except for years 2010, 2011, and 2012 - that's why there are fewer entries in those tables)

To Access Downloaded Tables

1) ssh researcher@128.42.44.181 on terminal
2) cd /bulk
3) psql tigertest

To view all tables:
\dt

To view contents of a table:
\d (Table Name)