[Music]
Did you know that in just a few steps, you can use the Census Bureau’s application
programming interface or API to get results that look very similar to table downloads
from data.census.gov?
One of the nice features of the API is that it provides the flexibility to quickly get
your results across a collection of geographies,
such as all metropolitan areas or congressional districts in the United States.
The process is as easy as tweaking an example URL, saving your results, and making a few
quick edits in Excel.
Let’s see how it works.
In this tutorial, we’ll show you how to get data for an entire table
from the American Community Survey.
I’ll walk through the steps using DP03
from the 2019 ACS 1-year estimates for all metro areas.
Before we get started, I’ll show you a quick trick on data.census.gov
to find the type of table that is associated with your table ID.
We’ll need this detail later.
From the landing page of data.census.gov,
type the table ID “DP03”
into the single search bar, and press enter.
On the all results page, click “Tables” in the upper left.
Now we can see the table result for DP03.
You can click the “Product” drop down menu in the upper right of the screen
to see the type of table in the result.
In this case, we see “Data Profiles.”
Now let’s get started navigating the API by opening a new tab and going to census.gov/developers.
We recommend using Google Chrome so you can see the API results in your web browser.
My favorite way to navigate to our dataset is to click “Available APIs” on the left side of the page.
Then we can scroll through our list of options.
Here I’ll choose “American Community Survey 1-Year Data”
And now we can see some information about the different types of tables from the American
Community Survey 1-Year Estimates.
Scrolling down, we’ll click into our desired year, such as 2019.
Then we choose the type of table we’re interested in getting data for,
scrolling to the heading for “Data Profiles”
To get results for the entire table we’ll work off the first link
labeled “Example Call”
that has “group” in the URL.
From the example URL, we can see it calls the API to get data from the 2019
American Community Survey 1-Year Estimates Data Profiles.
It uses the group functionality to query all variable results from table DP02 at the national level.
Select the first link and copy it.
Then open a new tab and paste the link.
Delete the portion that says ampersand key equals, your key goes here.
Then press enter.
I like to press enter between each step.
That way, if we make a mistake, we know exactly where we went wrong.
From here, we just need to edit the URL
to change our table to DP03,
and change the geographic level from the nation to all metropolitan areas.
First, I’ll edit the URL from DP02 to DP03.
Press enter and view our updated results.
You can see how our list of variables have been updated to DP03.
Next we’ll need to edit the geography.
My favorite place to get this is to go back to my previous tab, and click on the “Examples
and Supported Geography” section under Data Profiles.
This takes us to the API Discovery Tool, which provides a list of variables, geographies,
and other technical details needed to make API calls for this dataset.
Right click where it says “examples” and open it in a new tab.
This page provides a list of all of the different geographic areas compatible with the 2019
ACS 1-year Data Profiles,
and a variety of examples to fine tune our results for each type of geography.
For our example, I’ll scroll down to where it says “metropolitan statistical area,
micropolitan statistical area” on the left.
Here we see two examples that look very similar,
but you’ll notice differences in the geography
portion near the end of each URL.
The one with a wildcard at the end, denoted by an asterisk,
will give results for all metro areas.
The second link with the code “10420” at the end
provides results for a single metro
area – in this example, the Akron, Ohio Metro area.
Since we want data for all metro areas,
copy the first URL with the wildcard and paste it in a new tab.
Then select and copy the geography portion at the end
“ampersand for equals,
metropolitan statistical area,
micropolitan statistical area, colon wildcard”
Then go back to our other tab to continue to update our API call.
Select the geography portion “ampersand for equals US”
and paste our copied text to change our results from the U.S. to all metro areas.
Then press enter.
Once the results have loaded, you can create your csv file.
Right click the page and select “Save as”
We’ll name this “DP03
All Metro-Micro Areas.csv”
You must include “.csv” at the end the file name.
Click the drop-down menu where it says “JSON”
and change it to “All Files” Then click Save.
Use Microsoft Excel to open the file where you saved it.
With just a few easy edits, we can create a clean view that looks similar to downloads from data.census.gov.
First, we’ll get rid of some of the special characters
using Excel’s Find and Replace functionality:
In the upper right of the home tab,
Click “Find and Select”
and then “Replace”.
In the first box, we’ll type in the open bracket
Leave the “Replace with” box blank
Then click “Replace All”
From here we can see it’s found and deleted all 520 instances of the open bracket.
Repeat this process for the closed bracket
and for the quotes
Close out the Find and Replace box.
Now let’s find the geography labels and move them to the first column of our table results:
Press Control F to search the contents of this worksheet.
Type “Name” in the “Find What” box.
This is the API variable that provides our geography labels.
Press enter and then close out the Find and Replace box.
Notice Excel has navigated to the column for NAME.
This column contains the labels for each metro and micro area in our API results.
Let’s move this to column A:
Right click column APF
Choose Cut
Scroll back to column A
Right click column A and choose Insert Cut Cells
Now we see have our geography labels in the first column for easy viewing.
As a note, if you go through these steps and do not find any matches for Name during your search in Excel,
it means the NAME variable was not included as part of your API group call results.
The good news is that you can manually add NAME as an additional variable to your API query,
but we only recommend doing this in the rare instances when
it is not automatically included in the group call results.
This is because adding the variable this way often requires additional formatting steps
that are covered in our other API video:
Using the API to Get Results for Multiple Estimates.
The last step is to pull in the labels so that you know what each of the variables mean,
such as DP03 underscore 0001E.
I’ll show you an easy trick to bring these in using the VLOOKUP function in Excel.
Navigate back to our earlier page in the Discovery Tool for our dataset.
From here, click “Variables.”
This provides a list of the variables for the ACS Data Profiles and the labels for them.
To get this into Excel, follow these easy steps:
Click into the address bar
Delete .html
from the end of the URL and press enter
Once the results have loaded, right click
the page and select “Save As”
Add .csv to the end of the file name
Choose “All Files” from the drop-down menu
Then click “Save” and open the file
When you open the file, notice the first column provides names for all variables in the ACS Data Profiles,
and the second column provides the corresponding variable labels.
Just like before, we’ll use Excel’s find and replace
functionality to remove special characters.
Here we’ll run through those steps again quickly
Next, we need to copy and paste this data into our other spreadsheet:
Click cell A1
Press Control A to select all contents of the table
Right click the selected area and choose “Copy”
Navigate to the workbook DP03 All Metro-Micro Areas
At the bottom of the workbook, click the plus sign next to DP03
All Metro-Micros Areas to open a new worksheet
While we’re clicked into cell A1, press Control V to paste the content.
Now click back on the first worksheet, DP03 All Metro-Micro Areas.
From here, we’ll use a quick trick in Excel that allows us to easily match up the variable names
and add the corresponding labels just below it using the VLOOKUP function.
To start, right click row 2 and select “Insert”
Click cell B2 and type equals VLOOKUP
Then type an open parenthesis
Click cell B1 and type a comma.
This tells Excel that you want to find a match for DP03_0001E.
Now we need to tell Excel where to look for this matching label.
Click at the bottom where it says Sheet 1
Select all contents of the table.
One method to do this is to: Click into cell A1
Press Control Shift Right Arrow
And Control Shift Down Arrow
Then press the F4 button one time.
This will lock down where Excel looks for the
matching labels to the area we just selected
Next, click into the formula bar and type a comma
Enter the number 2 and type another comma.
This tells Excel when it finds a match for the variable name, we want it to pull the
content from the second column in this worksheet, column B.
Then, type 0 and a closed parenthesis.
This tells Excel that we want to look for exact matches.
Press enter to complete the VLOOKUP function.
Now we can expand cell B2
and see that it’s found a match for DP03_0001E
and pulled the corresponding label of Estimate, Employment Status, Population 16 years and over.
While we’re clicked into cell B2, locate the green box in the lower right corner of the cell.
Drag the formula to the right of the table to apply it to other variable names.
And now we have the labels associated with our API variable names.
Each geography has its own row,
and as we follow the row across, we can see all of the
variables from DP03 for that geography.
Having the data in this format makes it easy if we’d like to manipulate, sort, or map it.
As a final note:
One small difference between the csv results from the API and data.census.gov
downloads are the treatment of special values and the inclusion of additional columns to
denote them.
For example, when we scroll to column H for DP03_0001PM, the percent margin of error for
the population 16 years and over, you can see special values of -888,888,888.
To get more information, view the corresponding column that ends in A to see the annotation
for this particular variable.
In this example,
we see the annotation is right beside it in column i,
with a variable name ending in PMA.
Notice a special value of X appears in the annotation column,
which means that the estimate is not applicable.
In data.census.gov downloads, these special values are handled in a single column rather
than being split across two separate columns
as they are when you request the data from the API.
This is done in the API due to technical format requirements for ACS variables.
We do some clean-up on our end during the data.census.gov download process to make the
output more user-friendly.
Depending on the ACS data year you are using in the API,
these annotation columns that end in A
or they may be intermingled with your API results as they are in this example,
or they may appear as a separate set of columns at the end of your results.
If they are intermingled and you would like to learn about a quick trick to delete them,
please see the PDF linked with this video.
Finally, if you would like to explore the meaning for all special values, navigate back
to the developers page for your dataset.
Under “Related Information” click the “Notes on ACS Estimate and Annotation Values.”
Then browse the table to learn more about special values, annotations, and their meanings.
Thank you for tuning into this video.
We hope you find this trick useful on how to get all results for an ACS table
using the Census data API.
[Music]