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:
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:
You can find other common examples here.
To better understand what goes into the definition of the query URL, let's break it down into two distinct components:
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.
&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.
Now 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:
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.
To run the snippet follow these steps:
get=B01003_001E,B00002_001E,B19013_001E,B23025_004E,C17002_008E&for=block+group:*&in=state:" + [FIPS State] + "+county:" + [FIPS County]
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.
Originally posted on the All About Alteryx blog: https://alteryxuser.blogspot.com/
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:
Data Profile Example: 2016 Housing Occupancy, Total Housing Units for all census tracts in the State of California:
Subject Table Example: 2016 Female Total Population Age 60 to 64 by Zip code Tabulation Area:
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.