20160110-etl-census-with-python.md

[stharrold]

why acs:

• [x] it's well structured (cultivated) and has both regression and classification tasks easily understood
• [x] data set is not so large that requires extra infrastructure to handle
• [x] motivated by https://www.kaggle.com/c/2013-american-community-survey
• [x] footnote: race is a problematic term: https://en.wikipedia.org/wiki/Race_(human_categorization)
• [x] use american community survey first since csv formatted:
  • [x] use 5 year for small populations https://www.census.gov/programs-surveys/acs/guidance/estimates.html
  • [x] acs vs census:
  • [x] https://www.census.gov/programs-surveys/acs/guidance.html
  • [x] http://www.prb.org/Publications/Articles/2009/differences.aspx
• [x] why acs: https://www.census.gov/content/dam/Census/programs-surveys/acs/about/ACS_Information_Guide.pdf. About 1/38 households receives an invitation every year. These data are used to allocate federal funds.
• [x] Under why census data?: The census is a relatable dataset. Everyday people can understand census data because it's about them. The census is an applicable dataset. The census has many use-cases from local and national government to market validation. (link to use-cases) The census is a cultivated dataset. The census bureau goes to great lengths to ensure that the census is representative of the people it samples. The census is a large dataset. There are many features in the dataset, which permits many questions that can be answered from it. (footnote: I avoid the term "big data" here because, for this blog post, the data is not "big data" since it and all operations on it fit within RAM.) The census is a timeseries dataset. As a timeseries, the data can be used to predict current and future quantities. With the above points in mind, I recognize that using census data can be problematic. Census data has been used to harm people (For examples of privacy concerns, see the ACLU's FAQ section about census data. https://www.aclu.org/frequently-asked-questions-national-census), and census data categorizes people by loaded terms, such as "race" (For reasons why "race" is a problematic term, see the Wikipedia article "Race (human categorization)". https://en.wikipedia.org/wiki/Race_(human_categorization). For the sake of this exercise, I take the census data at face value. It's important to recognize that these aren't simply statistics; these are people.

About ACS:

• [x] ACS docs:
  • [x] To access API: http://www.census.gov/data/developers/data-sets/acs-survey-5-year-data.html
  • [x] Use cases for ACS data: https://www.census.gov/programs-surveys/acs/about/survey-is-mandatory/acs-helps-communities.html
  • [x] Link to Kaggle scripts competition
  • [x] Instructions for ACS ftp: https://www.census.gov/programs-surveys/acs/data/data-via-ftp.html
  • [x] $3000, 8 weeks for custom table: https://www.census.gov/acs/www/data/custom-tables/request-a-custom-tab/
  • [x] ACS guidance: https://www.census.gov/programs-surveys/acs/guidance.html
  • [x] Use cases by data tool: https://www.census.gov/acs/www/guidance/which-data-tool/
  • [x] Tools chart: https://www.census.gov/programs-surveys/acs/guidance/which-data-tool/data-tools-chart.html
  • [x] When to use which year estimate: https://www.census.gov/programs-surveys/acs/guidance/estimates.html
  • [x] Handbooks: https://www.census.gov/programs-surveys/acs/guidance/handbooks.html
  • [x] From handbook App 1, use the dataset centered on each year with the largest window. This includes as many blocks as possible while keeping the data as recent as possible. Note that 1-year data is included in the 3-year estimate, which is included in the 5-year estimate. The data for each block also has its own confidence interval. Give example following Handbook Appendix 1
  • [x] Useful handbooks:
  • [x] for general users with case studies: https://www.census.gov/content/dam/Census/library/publications/2008/acs/ACSGeneralHandbook.pdf
  • [x] for Public Use Micro Sample: https://www.census.gov/library/publications/2009/acs/pums.html
  • [x] for researchers (give link)
  • [x] Point-in-time estimates needs to have a shared baseline (e.g. 5-year)
  • [x] Include CPS data for income (from handbook)
  • [x] What is the American Community Survey? (link to main page, and kaggle competition) from handbooks """ The American Community Survey (ACS) is the new source for the information previously collected through the decennial census long form. This information includes topics such as income, employment status, housing costs, and housing conditions. Unlike the decennial census, ACS data are collected on a continuous basis. """ About 250k addresses each year are requested to participate.
  • [x] """The advantage of going through the ACS Web site is that the PUMS user verifi cation fi les are listed. User verifi cation fi les provide estimates for selected housing and population characteristics to help data users determine that they are using the weights correctly. """
  • [x] Download the data dictionary to understand the columns.
  • [x] The handbooks include statistical formulas for calculating standard error, margin of error and confidence intervals.
  • [x] Use replicate weights methods for errors since doesn't rely on distribution type.
  • [x] Differentiate between Census Bureau data and census data
  • [x] Much of the information in the handbooks is also replicated through the site.
  • [x] The handbook is from 2008, but only some of the links are out-of-date. The information is very thorough with many examples replete with use-cases.
  • [x] The margin of error calculations assume that the data is normally distributed, which often isn't the case for real-world data (an emperical, rank-based test for normally distributed data is z1,z2, astroML link)
  • [x] Consult "ACS Quality Measures" for nonsample error.
  • [x] For official poverty measures, see http://www.census.gov/hhes/
  • [x] Handbook for reasearchers https://www.census.gov/library/publications/2009/acs/researchers.html
  • [x] Complete "PUMS estimates for user verification"
• [x] Design and methodology: https://www.census.gov/programs-surveys/acs/methodology/design-and-methodology.html
• [x] Census Bureau's collection of reports and inforgraphics. : https://www.census.gov/programs-surveys/acs/library.All.html About google cloud services:
• [x] Storing data: https://cloud.google.com/docs/storing-your-data
  • [x] BigTable is for large applications
  • [x] Cloud Storage for immutable blobs
  • [x] BigQuery for online analytical processing.
  • [x] DataProc as a managed Hadoop and Spark service.
  • [x] Use DataLab when deploying analytics.
  • [x] Access the AppEngine application that runs the DataLab from datalab.cloud.google.com and Developers Console > Products & services > App Engine > Versions > click the version to access (e.g. "main (default)")
  • [x] Both BigQuery and Cloud Storage integrate with Google Prediction API.
  • [x] Compare to Google Prediction API.
  • [x] Use BigQuery when deploying to app. Use Postgres for individual development.
• [x] Use Cloud Storage to save ACS data.
  • [x] Use gsutil to make a bucket in Cloud Storage. Requires to python 2.7. https://cloud.google.com/storage/docs/getting-started-gsutil
  • [x] Use stharrold.github.io as the bucket name: https://cloud.google.com/storage/docs/bucket-naming
  • [x] To verify site ownership: https://cloud.google.com/storage/docs/bucket-naming#domain-verification
  • [x] Use Durable Reduced Availability storage: https://cloud.google.com/storage/docs/storage-classes
  • [x] Cloud storage best practices: https://cloud.google.com/storage/docs/best-practices
  • [x] When scaling, use hadoop connector: https://cloud.google.com/hadoop/google-cloud-storage-connector
  • [x] As part of setup, ensure that the instance has "Read Only" access to "Storage": when creating a new instance, under "Project Access" > "Access and security"
  • [x] Rather than bucket, could just save file and make postgres db: https://cloud.google.com/solutions/setup-postgres-data-disk
  • [x] Use StringIO to read from Cloud Storage: http://pandas.pydata.org/pandas-docs/version/0.17.1/io.html#io-read-csv-table, https://cloud.google.com/storage/docs/gsutil/commands/cat, but gsutil requires python 2.7, so would have to download the file anyway. So need to make a spare disk anyhow.

ETL:

• [x] Use own drive for data
• [x] Read the data from ACS using urllib2.urllib(url).read()
• [x] out-of-core computations: http://nbviewer.ipython.org/github/jcrist/Dask_PyData_NYC/blob/master/Dask_DataFrame_Airline.ipynb
• [x] Download:
  • [x] http://www2.census.gov/programs-surveys/acs/data/pums/2013/5-Year/csv_hus.zip
  • [x] http://www2.census.gov/programs-surveys/acs/data/pums/2013/5-Year/csv_pus.zip
  • [x] http://www2.census.gov/programs-surveys/acs/tech_docs/pums/data_dict/PUMSDataDict13.txt
  • [x] Note: Data is mirrored to multiple locations within the ftp site. Files under http://www2.census.gov/programs-surveys/acs/ correspond to the sitemap of http://www.census.gov/programs-surveys/acs/

[stharrold]

• [x] create frozen copy of code with tar -czf code_YYYYMMDDTHHMMSSZ.tar.gz code; unpack with tar -xzf code_YYYYMMDDTHHMMSSZ.tar.gz

[stharrold]

• [x] relevant SO question: http://stackoverflow.com/questions/26564775/regex-to-parse-well-formated-multi-line-data-dictionary

[stharrold]

moved many tasks to #36