Using the API to Get Results For Multiple Estimates

[Music] Are you interested in getting statistics for
a few topics across a collection of geographies? The Census Bureau’s application programming
interface or API provides the unique functionality to call results for only the variables you
need. And the great news is that you can save the
data as a csv file to view in Excel. The process is as easy as tweaking an example
URL, saving your results, and making a few quick edits in Excel. Let’s check it out. In this tutorial, we’ll show you how to get
these data from the 2018 ACS 1-Year Data Profiles for all Congressional Districts in California. Specifically we’ll find data for the percent
of households with no vehicles, people in poverty, and people with a disability. Each of these come from different tables,
but the API allows us to see the results in a single view. To get started, visit census.gov/developers. We recommend using Google Chrome or Firefox
so you can see the API results in your web browser. Click “Available APIs” on the left side of
the page. Then we can scroll through the available 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 ACS 1-Year Estimates. Scrolling down, we see we’re clicked into
the most recent year by default – 2018. Then we choose the type of table we’re interested
in getting data for. Today we’re going to work with the Data Profiles. They’re a great starting point to get broad
social, economic, housing, and demographic statistics across all topics from the American
Community Survey. And they include estimates, as well as percentages. The easiest way to get started is to click
“Examples and Supported Geography” under Data Profiles. Then 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 2018 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 “state, congressional district” on the left. Here we see three examples to choose from. You’ll notice differences in the geography
portion near the end of each URL. The first two examples with wildcard symbols,
shown by the asterisks, will give results for all congressional districts. The first link calls all congressional districts
and the second link calls all congressional districts in all states – which are different
ways to ask the API for the same data. The last link provides results for a single
congressional district – Congressional District 01 in state code 01, which is the code for
Alabama. Let’s pick the second example because it’s
easy to edit that URL to get data for all congressional districts in a single state. Copy the link, open a new tab, and paste it. Delete the portion of the link 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. Now we can focus on the first two lines to
start reading our results. We see it gives us the name for each geography. Next it tells us the value for variable “DP02
underscore 0001E”, which is the variable for total households. The naming convetion 0001E means the variable
is the estimate from the first line of the table from Data Profile 2. Finally, we see the results include the two
digit codes for each state and congressional district. As an example, looking at the second line
of our results, we can see that for Congressional District 7 in Alabama, there are approximately
250,210 households. The state code for Alabama is 01 and the Congressional
District code is 07. To narrow our geography selection, we can
use Control F, start typing in California, and quickly see that the state code for California
is 06. Click into the address bar and replace the
wildcard symbol after “state” with 06. Then press enter. Now we’ve narrowed our results to provide
total households for each Congressional District in California. But how can we change this to get data for
the variables we are interested in? Click back to the “Examples and supported
geography page” and then click where it says “Variables.” This provides a complete list of variables
from the 2018 ACS Data Profiles. From here I’ll press Control F and type a
keyword to start looking for our variable. After typing in vehicle, we see a few matches. Read the labels carefully to see that the
variable name DP04 underscore 0058E provides the estimate total number of housing units with no vehicles
available, and DP04 underscore 0058PE provides the percentage of housing units with no vehicles
available. We want the percent, so let’s copy that variable,
go back to our other tab, and replace DP02 underscore 0001E with DP04 underscore 0058PE. Then press enter. Repeating this process, we can search the
keywords “poverty” and “disability” on the variables page and add up to 50 variables
to our query, separating each one with a comma. I’ve done that already so I’ll add them to
our query. Type a comma after 0058PE, then add the variable
for poverty DP03 underscore 0128PE, comma, and then add the variable for disability DP02
underscore 0071PE. Then press enter and see that each of the
three variables have been updated in the first line of our results. Reading our results, we can see that congressional
district 5 in California, 5.2% of households have no vehicle available, 9.5% of all people
live in poverty, 12.2% of people have a disability. If we’d like to look at this in Excel, we
can easily create a csv file, right click the page and select “Save as”. We’ll name this “All CDs in California.csv”
You must include “.csv” at the end the file name. Click the drop down menu where it says “JSON”, or J-S-O-N, 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. 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 55 instances of the open bracket. Repeat this process for the closed bracket
and for the quotes. Now we need to clean up the label for our
geographic area. Because of the comma in the API between the
congressional district and state, the geography name was split across two cells. You can see this where Congressional District
5 116th Congress appears in cell A2 with California in cell B2 even though both cells make up
the full geography name. Notice that right now, column B has the header
for DP04_0058PE, but the percent estimates for households with no vehicles available
are actually in Column C. We need to line up the data with the correct
headers. If you need keep the full label and merge
it into a single cell that says Congressional District 5 116th Congress comma California
– you can check out that process in our other video on using the API to get all results
for an ACS table. But depending on your needs, you may decide
that you don’t actually need the repeating labels for California at all. One quick way to get rid of them is to click
into cell B2. Press Control, Shift, and then down arrow. Right click the selection and choose “Delete”. Leave the radio button checked to “Shift Cells
Left” and click “OK”. Now we have our table. If you’d like, you can add a row, then copy
and paste the variables labels from the site into your table. Here I’ll show this step where I navigate
back to the variables page, search for DP04 underscore 0058PE, copy the variable label,
and paste it into Excel. You can repeat this process for the remaining
2 variables or do other formatting improvements. When you’re finished, save the file as an
Excel Workbook to keep your changes. Thank you for tuning into this video. We hope that you find these tricks useful
on how to get results for a selection of variables using the Census data API. [Music]

Tags: , , , , , , , , , ,

There are no comments yet

Why not be the first

Leave a Reply

Your email address will not be published. Required fields are marked *