Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
community Alteryx IO Mission Control
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.

Easily Integrating Census Data into your Alteryx Workflow With the Census Data API

ronh_allaboutgis
6 - Meteoroid
‎05-16-2018 08:00 AM

census.png

I often work on Code for KC (America) projects where there is the need to incorporate census demographic data into my Alteryx workflow beyond what is available within the standard Data license. Typically I'll work with the current American Community Survey (ACS) data only and other times I'll need to compare the most recent ACS estimate to older census data or utilize some of the Census Comparison profiles. In each of these instances, I've found using the Census Data API to be invaluable because it is so easy to include data using APIs within Alteryx. I've used this recently to pull certain demographic variables into a workflow on an Abandoned to Vibrant Properties project conducted in conjunction with the University of Missouri-Kansas City (UMKC) and the Land Bank of Kansas City. This work was submitted as an Alteryx Excellence Award and you can read about (and ideally vote for!) it here: a2V-Lb: Abandoned to Vibrant Land Bank Housing in Kansas City, Missouri

 

This post will walk you through working with the Census Data API and how to create a reusable snippet to streamline the process of bringing the data into your Alteryx workflow. As with any API, understanding how to request the data and how to utilize the incoming data in your workflow is key. So let's learn a bit about the API and how to format the URL to request the data you want. In this example, we'll focus only on using the API created for variables accessed through 2016 ACS Detail tables. In addition to this dataset, there are a variety of other census datasets that can be accessed:

  • Detail tables contain the most detailed data down to the block group level in population counts. You can view variables and examples of the detail tables and supported geometry types here.
  • Subject tables provide an overview of the estimates on a variety of subjects. You can view the subject tables variables and examples here.
  • Data profiles are presented as population counts and percentages and provide access to a wide variety of social, economic, housing, and demographic information. Examples of these profiles and variables, examples and supported geometry types are listed here.
  • Comparison profiles contain similar data as the Data Profiles above yet also include a current year comparison to the prior five year data along with statistical significance. View the available variables, examples and supported geometry types of the Comparison profiles here.

An example is shown below of a complete, encoded Data Profile Census Data API URL. This example returns the total population count for each block group within Johnson County, Kansas:

 

https://api.census.gov/data/2016/acs/acs5?get=NAME,B01001_001E&for=block%20group:*&in=state:20%20county:091&key=YOUR_KEY_GOES_HERE


You can find other common examples here.

The Breakdown of Query Components

To better understand what goes into the definition of the query URL, let's break it down into two distinct components: 

 

  1. The API Base URL - The base portion of the API URL defines the census year and the dataset you wish to access. To obtain data from each of these datasets, you will use a unique base API URL (Note: The base URL changed in 2016, check here for previous year examples):
    • Detail tables:  
      https://api.census.gov/data/2016/acs/acs5?
    • Subject tables:  
      https://api.census.gov/data/2016/acs/acs5/subject?
    • Data profiles:  
      https://api.census.gov/data/2016/acs/acs5/profile?
    • Comparison profiles: 
      https://api.census.gov/data/2016/acs/acs5/cprofile?
  2. The GET function - This function contains the required census variables you are requesting and starts with get=. This function also includes the predicate, which identifies how the census data will be filtered or limited. Predicates can be created using geometry, string or numerical variables, or by time if accessing the time series datasets. In the example above, this will request the census data for a specific state and county identified by their corresponding Federal Information Process Standard Code, or FIPS Codes.
    • Geometry Filters - The level of geometry that can be used within the predicate to filter the data varies for each dataset. For example, you can request data at the block group level using the for= statement: (&for=block group:*&in=state:20 county:091). The wildcard (😘) can only be used to search for all values of a variable for geographies and string variables. You can view the available geographies for each dataset by selecting on the corresponding link below: 

The Census API Key - Along with each request you'll also need to provide an API key that identifies you as the requestor, which you can submit for here. This must be included as a part of your data request URL string.

 

Working with the Census Data API Snippet

 

census.pngNow that we know how to request the data, let's take a look at the snippet (shown in the image above) that we'll use to request and download the data. This was specifically designed to handle variables requested from the 2016 ACS Detail table. Depending on the data you request and the level of geometry desired, you will need to modify the snippet accordingly.  At a birds-eye view, these are the basic steps used to access any of the census datasets with this snippet:

 

  1. Define the FIPS Code(s) associated with the geography you wish to filter the census data with the Filter Tool. 
  2. Using the Formula Tool, create the query definition with the desired census variables and predicate. This will dynamically create the API endpoint based on the selected FIPS Codes. Remember that you can modify the for= statement with a wildcard (😘) in the predicate (for=block group:*) to return variables for all block groups within the chosen geography. In this snippet, the State and County FIPS is used. Choose multiple State FIPS Codes in step 1 if you wish to pull the variables for all Counties and block groups within those States.
  3. Use the Download Tool to submit the request.
  4. The JSON Parse Tool parses the result, and the Text to Column Tool splits the JSON_Name column into two columns.
  5. The stream is split into Header and Data streams using the Sample Tool, based on the level of geometry desired and number of variables requested defined in the predicate. 
  6. After formatting the two streams, join them back together using the Join Tool.
  7. Rename fields from variable names to more descriptive common names with the Select Tool.

Running the Census Data API Snippet

To run the snippet follow these steps:

  1. Select the State FIPS Code(s) you wish to pull data for in the FIPS Code Filter.
  2. Choose the variables you want to obtain. The available variables for the Detail tables can be viewed via this link. Each variable must be separated by a comma and I recommend to keep your variable list to less than 10. Start with just a few variables at first then add more with each subsequent run. This is an example of what your predicate should look like with 5 variables in the URL Creator Formula Tool within the snippet:  
    get=B01003_001E,B00002_001E,B19013_001E,B23025_004E,C17002_008E&for=block+group:*&in=state:" + [FIPS State] + "+county:" + [FIPS County]
  3. Submit for a Census API key and substitute it for the words "YOUR_KEY_GOES_HERE". 
  4. Now you're ready to go! Run the snippet.

I've packaged the snippet for your use, see attached. Feel free to modify it as you see fit and remember to share your modifications with the Alteryx Community. If you like, you can fork the snippet from my Github Repository.

 

Enjoy!

 

Ron

 

Originally posted on the All About Alteryx blog: https://alteryxuser.blogspot.com/

And Now in Your Spare Time...

 

Want to test your knowledge of the Census Data API pulling other data variables? Modify the snippet to work with these other examples:

 

Comparison Profile Example - 2007-2011 School Enrollment of Population 3 years and over enrolled in High school (grades 9-12) by MSA:

https://api.census.gov/data/2016/acs/acs5/cprofile?get=CP02_2007_2011_056E,NAME&for=metropolitan%20statistical%20area/micropolitan%20statistical%20area:*

Data Profile Example: 2016 Housing Occupancy, Total Housing Units for all census tracts in the State of California:

https://api.census.gov/data/2016/acs/acs5/profile?get=DP04_0001E,NAME&for=tract:*&in=state:06

 Subject Table Example: 2016 Female Total Population Age 60 to 64 by Zip code Tabulation Area:

https://api.census.gov/data/2016/acs/acs5/subject?get=NAME,S0101_C03_014E&for=zip%20code%20tabulation%20area:*

Note 1: You can determine census variables for other geography types by using the Census Relationship files found here.

 

Note 2: Although I have not tried it yet, you can also access the census data via the Census Data API using walkerke's tidycensus R package found here (using the R Tool).

CensusDataAPI.yxzp
Comments