This Python notebook has code for downloading Census data used by the Gestational Diabetes study (P-1599). It demonstrates fundamental techniques for using the US Census Bureau API to download census data.
Data retrieval is done using the requests package, which is an Apache supported http library written in Python. There are other http libraries out there that leverage requests or have been written from the ground up in an attempt to make the task of data retrieval easier. To keeps things as basic as possible and to reduce confusion caused by combining techniques from multiple modules, I don't use these.
These techniques were learned from the Data Camp course "Analyzing US Census Data in Python", July 2019.
The dataframes that result from the census data downloads are not saved by the following program. You will need to add steps to output the dataframe as an CSV file. For my purposes, I was saving the dataframes to the Value Institute's projects SQL Server. Only the Data Management team has access to this. All the code for doing this has been removed - Mitch.
### Load libraries
import requests # to make http requests for data using census web API
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# import geopandas as gpd # Extension of pandas for working with geospatial *data*
# import geoplot as gplt # Extension of matplotlib for working with geospatial *plots*
import socket # To get name of computer see http://net-informations.com/python/net/hostname.htm
### Set some global variables
# Figure out if this is my Windows CCHS workstation or my Mac
COMPUTERISA = ''
computername = socket.gethostname()
if computername.find("WS") != -1:
COMPUTERISA ='Windows'
elif computername.find("Mac") != -1:
COMPUTERISA ='Mac'
mydir = ''
# Working directory path - This is my path on a Mac
if COMPUTERISA =='Mac':
mydir = "~/Documents/Data Science/USCensus"
# Otherwise it's my Windows PC and the working directory is the directory the Notebook is in.
### Run this Python magic function that allows plots to display within a Notebook
%matplotlib inline
Structuring a Request¶
The request package provides the classes needed for Python to retrieve data over the Internet using http.
A request is structured as an object containing a "base URL" and a list of parameters, called "predicates". The base URL tells the system which dataset and year you are interested in. The predicates tell the system what specific variables to retrieve from the the table and for what geographical areas to return data.
The request object is then passed using the "get" method and the API returns a "response" object containing the data in json format. See below for an example.
Base URL example.
- Host = https://api.census.gov/data
- Year = 2010
- Dataset = dec/sf1
"dec" means Decennial census. "sf1" means summary file #1
### The following returns the populations of every state and Puerto Rico from the 2010 Census.
# Components of the base URL go into variables.
HOST = "https://api.census.gov/data"
year = "2010"
dataset = "dec/sf1"
# Code to join the base url components
base_url = "/".join([HOST, year, dataset])
# Form the predicates dictionary
predicates = {} # initialize empty dictionary
get_vars = ["NAME", "P001001"] # Want the name of the State and the total population.
predicates["get"] = ",".join(get_vars)
predicates["for"] = "state:*" # States are the geography to return data for. "*" means all states.
# Make the request. Results get loaded into the variable "myresponse".
myresponse = requests.get(base_url, params = predicates)
# Display the request that was sent
print("Base URL:", base_url, "\n", "Predicates:",predicates)
Base URL: https://api.census.gov/data/2010/dec/sf1
Predicates: {'get': 'NAME,P001001', 'for': 'state:*'}
# Display the results. It is a list of lists.
print(myresponse.text)
# The first line has the column names. "state" is the Census State Code. Delaware is "10".
# There may be an error message displayed instead of the desired data if you did something wrong or
# something else prevented data from being retrieved.
[["NAME","P001001","state"], ["Alabama","4779736","01"], ["Alaska","710231","02"], ["Arizona","6392017","04"], ["Arkansas","2915918","05"], ["California","37253956","06"], ["Louisiana","4533372","22"], ["Kentucky","4339367","21"], ["Colorado","5029196","08"], ["Connecticut","3574097","09"], ["Delaware","897934","10"], ["District of Columbia","601723","11"], ["Florida","18801310","12"], ["Georgia","9687653","13"], ["Hawaii","1360301","15"], ["Idaho","1567582","16"], ["Illinois","12830632","17"], ["Indiana","6483802","18"], ["Iowa","3046355","19"], ["Kansas","2853118","20"], ["Maine","1328361","23"], ["Maryland","5773552","24"], ["Massachusetts","6547629","25"], ["Michigan","9883640","26"], ["Minnesota","5303925","27"], ["Mississippi","2967297","28"], ["Missouri","5988927","29"], ["Montana","989415","30"], ["Nebraska","1826341","31"], ["Nevada","2700551","32"], ["New Hampshire","1316470","33"], ["New Jersey","8791894","34"], ["New Mexico","2059179","35"], ["New York","19378102","36"], ["North Carolina","9535483","37"], ["North Dakota","672591","38"], ["Ohio","11536504","39"], ["Oklahoma","3751351","40"], ["Oregon","3831074","41"], ["Pennsylvania","12702379","42"], ["Rhode Island","1052567","44"], ["South Carolina","4625364","45"], ["South Dakota","814180","46"], ["Tennessee","6346105","47"], ["Texas","25145561","48"], ["Utah","2763885","49"], ["Vermont","625741","50"], ["Virginia","8001024","51"], ["Washington","6724540","53"], ["West Virginia","1852994","54"], ["Wisconsin","5686986","55"], ["Wyoming","563626","56"], ["Puerto Rico","3725789","72"]]
### Load libraries
import requests # to make http requests for data using census web API
import pandas as pd
### Components of the base URL go into variables.
HOST = "https://api.census.gov/data"
year = "2010"
dataset = "dec/sf1"
### Code to join the base url components
base_url = "/".join([HOST, year, dataset])
### Form the predicates dictionary
predicates = {} # initialize empty dictionary
get_vars = ["NAME", "P001001"] # Want the name of the State and the total population.
predicates["get"] = ",".join(get_vars)
predicates["for"] = "state:*" # States are the geography to return data for. "*" means all states.
### Make the request. Results get loaded into the variable "myresponse".
myresponse = requests.get(base_url, params = predicates)
### Display the results.
print(myresponse.text)
[["NAME","P001001","state"], ["Alabama","4779736","01"], ["Alaska","710231","02"], ["Arizona","6392017","04"], ["Arkansas","2915918","05"], ["California","37253956","06"], ["Louisiana","4533372","22"], ["Kentucky","4339367","21"], ["Colorado","5029196","08"], ["Connecticut","3574097","09"], ["Delaware","897934","10"], ["District of Columbia","601723","11"], ["Florida","18801310","12"], ["Georgia","9687653","13"], ["Hawaii","1360301","15"], ["Idaho","1567582","16"], ["Illinois","12830632","17"], ["Indiana","6483802","18"], ["Iowa","3046355","19"], ["Kansas","2853118","20"], ["Maine","1328361","23"], ["Maryland","5773552","24"], ["Massachusetts","6547629","25"], ["Michigan","9883640","26"], ["Minnesota","5303925","27"], ["Mississippi","2967297","28"], ["Missouri","5988927","29"], ["Montana","989415","30"], ["Nebraska","1826341","31"], ["Nevada","2700551","32"], ["New Hampshire","1316470","33"], ["New Jersey","8791894","34"], ["New Mexico","2059179","35"], ["New York","19378102","36"], ["North Carolina","9535483","37"], ["North Dakota","672591","38"], ["Ohio","11536504","39"], ["Oklahoma","3751351","40"], ["Oregon","3831074","41"], ["Pennsylvania","12702379","42"], ["Rhode Island","1052567","44"], ["South Carolina","4625364","45"], ["South Dakota","814180","46"], ["Tennessee","6346105","47"], ["Texas","25145561","48"], ["Utah","2763885","49"], ["Vermont","625741","50"], ["Virginia","8001024","51"], ["Washington","6724540","53"], ["West Virginia","1852994","54"], ["Wisconsin","5686986","55"], ["Wyoming","563626","56"], ["Puerto Rico","3725789","72"]]
Determining Table and Variable Codes¶
Census data is extensive and is stored in literally thousands of tables and variables. How do you find what you are looking for? Here is some help.
Datasets
The Census Bureau defines a "dataset" as the assembled result of one data collection operation (for example, the 2010 Census) as a whole or in major subsets such as the 2010 Census Summary File (the "sf1"). The following link has a general description of what is in the sf1. https://www.census.gov/data/datasets/2010/dec/summary-file-1.html. Other major sets of data are the decennial sf3, and the American Community Survey acs1 and acs5. In these sets will be dozens of tables (called "groups" by the Census folks) representing various subjects and cross tabulations of data collected by those surveys.
Most of the time if you are looking for socioeconomic data at the census tract level you will want to use the ACS 5 year tables. The 5 year estimate tables will have data down to the smallest, least populated geography presented by the census bureau. 1 or 3 year estimates have a population cutoff below which a geography will not have data.
To see a list of the 40 subjects for the ACS data go to this link: https://www.census.gov/programs-surveys/acs/guidance/which-data-tool/table-ids-explained.html
Geography Codes
Geography codes are the numeric codes that identify particular land areas. There is a hierarchy to the geographies that is described here: https://www.census.gov/programs-surveys/geography/guidance/hierarchy.html. State, county, census tract, metropolitan area, school district, city, town, village and so on, will all have unique numeric codes identifying them. For example the code for Delaware is 10. The code for New Castle County is 003. Other states will also have a county that is "003", so in some circumstances it will be necessary to string both values together as "10003" in order to provide the correct unique identifier for New Castle County, Delaware.
A lookup tool for geography codes can be found at: https://census.missouri.edu/geocodes/.
Census tract 9900 represents a body of water. For my purposes I'll be ignoring these.
Table Codes
Within a census dataset the data will be organized into a series of tables (ie, groups) each focused on a particular topic or cross tabulation between a topic and various population characteristics. To see the list of tables add the string "/groups.html" to the base url for the dataset and go to that address. For example: https://api.census.gov/data/2017/acs/acs5/groups.html will list all the tables containing data from the 2017 American Community Survey. You should see a list that starts with table B00001 UNWEIGHTED SAMPLE COUNT OF THE POPULATION.
To find the table that contains the data you are looking for, perform a "find" on the web page. The web page for the above link has 8 matches for "median income". If you are looking for general results for median income, the table B06011 MEDIAN INCOME IN THE PAST 12 MONTHS (IN 2017 INFLATION-ADJUSTED DOLLARS) BY PLACE OF BIRTH IN THE UNITED STATES is one you could use. (If it's total income you are looking for, not separated by any particular demographic feature, probably any of the tables would work, since there is usually going to be a grand total value at the top of the variable list.)
Another way to find a table code is to use American FactFinder www.factfinder.census.gov, click Advanced Search, click Show Me All.
Variable Codes
Once you have identified the dataset and the table contining your data, click on the link that says "Selected Variables" for that table. A list of variables will show up. In this case you might be interested in B06011_001E Estimate!!Median income in the past 12 months!!Total.
Variable codes are unique across a dataset. That's why it isn't necessary to provide a table name in the http request.
Table IDs Explained Table codes have a systematic structure. For example in a table name like B15001, the "B" means Base table which is the most detailed table for a subject area. "15" means the subject area is educational attainment. "001" is a sequential number that uniquely identifes the table within the subject area. If there is a letter at the end like "A" it means the table id filtered on White citizens. Other letters indicate other races or could indicate Puerto Rico.
See https://www.census.gov/programs-surveys/acs/guidance/which-data-tool/table-ids-explained.html for the meanings of all the codes used in table IDs.
Census Data API User Guide The guide can be downloaded, but it is best viewed on-line because of the embedded Web links it contains. https://www.census.gov/data/developers/guidance/api-user-guide.html. This is an essential piece of API documentation provided by the US Census Bureau. In it you will find a complete list of dataset names, the hierarchy of geographies they can drill down to, what groups (ie, tables) they contain, what variables they contain, and importantly, examples of the URL string needed to make requests. It may wind up being your one-stop-shop for finding the dataset names and variable codes you need for any data search. Here is a shortcut to the main table in the guide: https://api.census.gov/data.html.
Table Shells While the API user guide provides extremely detailed information about the variables in a data table, it can be confusing to use when all you are first trying to figure out what kind of data elements it contains. The so-called "table shell" lists can be useful in this case. They provide a simplified overview of what's contained in a table because they leave out the margin of error and annotation variables and just focus on the data itself. They do not provide variable IDs however, so to get those you will need to go back to the more detailed source of information in the API user guide. See https://www2.census.gov/programs-surveys/acs/tech_docs/table_shells/2017/?# for the list of table shell files.
Other Table Types Data Profile tables start with "DP". Example DP03. These contain broad social, economic, housing, and demographic information in a total of four profiles. Use this to find the variables they contain. There are other dataset types besides "S", "B" and "DP" that are not discussed here. https://api.census.gov/data/2017/acs/acs5/profile/groups/DP03.html
SUMMARY OF USEFUL LINKS
To get variable IDs for dataset tables:
"B" datasets: https://api.census.gov/data/2017/acs/acs5/groups.html
"S" datasets: https://api.census.gov/data/2017/acs/acs5/subject/groups.html
"DP" datasets: https://api.census.gov/data/2017/acs/acs5/profile/groups.html
Lookup tool for geography codes: https://census.missouri.edu/geocodes/
Meaning of table ID codes: https://www.census.gov/programs-surveys/acs/guidance/which-data-tool/table-ids-explained.html
Deciding which census survey result to use: https://www.census.gov/programs-surveys/acs/guidance/which-data-tool/table-ids-explained.html
Topics in the Decennial Census¶
The 2010 census asked the following ten questions:
- How many people were living or staying in this house, apartment, or mobile home on April 1, 2010?
- Were there any additional people staying here April 1, 2010 that you did not include in Question 1?
- Is this house, apartment, or mobile home: owned with mortgage, owned without mortgage, rented, occupied without rent?
- What is your telephone number?
- Please provide information for each person living here. Start with a person here who owns or rents this house, apartment, or mobile home. If the owner or renter lives somewhere else, start with any adult living here. This will be Person 1. What is Person 1's name?
- What is Person 1's sex?
- What is Person 1's age and Date of Birth?
- Is Person 1 of Hispanic, Latino, or Spanish origin?
- What is Person 1's race?
- Does Person 1 sometimes live or stay somewhere else?
The census survey form has room for collecting answers to these questions for up to 6 people per household.
The decennial census helps communities determine where to build everything from schools to supermarkets. It helps the government decide how to distribute funds and assistance to states and localities. It is also used to draw the lines of legislative districts and reapportion the seats each State holds in Congress.
Note that there are no questions concerning income, employment, citizenship status or many other topics in the decennial census. There used to be, but now those questions are asked during the annual American Community Survey. The ACS, which started in 2005, is now the primary source of nationwide socioeconomic data for all levels of geographic areas. Below is a list of the topics covered by the ACS.
Topics in the Annual American Community Survey¶
Ancestry
Citizen Voting-Age Population
Citizenship Status
Disability Status
Educational Attainment
Fertility
Grandparents as Caregivers
Language Spoken at Home
Marital History
Marital Status
Migration/Residence 1 Year Ago
Place of Birth
School Enrollment
Undergraduate Field of Degree
Veteran Status
Period of Military Service
Year of Entry
Class of Worker
Commuting (Journey to Work) and Place of Work
Employment Status
Food Stamps/Supplemental Nutrition Assistance Program (SNAP)
Health Insurance Coverage
Income and Earnings
Industry and Occupation
Poverty Status
Work Status Last Year
Bedrooms
Computer and Internet Use
House Heating Fuel
Kitchen Facilities
Occupancy/Vacancy Status
Occupants per Room
Plumbing Facilities
Rent
Rooms
Selected Monthly Owner Costs
Telephone Service Available
Tenure (Owner/Renter)
Units in Structure
Value of Home
Vehicles Available
Year Householder Moved Into Unit
Year Structure Built
Age
Sex
Group Quarters Population
Hispanic or Latino Origin
Race
Relationship to Householder
Total Population
Walkthrough of Finding a Desired Dataset Name and Variable ID¶
I'll use the example of how to find the percentage of adults over age 25 who have less than a high school diploma in 2017.
Using the link https://api.census.gov/data/2017/acs/acs5/groups.html and searching for educational attainment I found a table called B15003, EDUCATIONAL ATTAINMENT FOR THE POPULATION 25 YEARS AND OVER. Going to the table shell at https://www2.census.gov/programs-surveys/acs/tech_docs/table_shells/2017/?# for this table showed that there are variables for every school grade and college level. Figuring out the total for less than a high school diploma would involve having to add up 15 separate variable values. I wanted to find a table that already summarized the less than high school diploma totals.
To do this I opened www.factfinder.census.gov selected Advanced Search, then Show Me All. Then clicked Topics, People, Education, Educational Attainment. The first table listed, table S1501, shows a more summarized version of the data with a variable called "Percent high school graduate or higher". I can subtract this from 100 to get the percent less than high school graduate. This is the table I want to use.
To find the specific variable ID of this value, open the link https://api.census.gov/data.html found in the API Guide. Scroll down to the row with Vintage value "2017", Title value "ACS 5-Year Subject Tables" (The "S" in the table ID means "Subject"), and Dataset Name "acs› acs5› subject". Click on the Groups link and scroll to S1501, EDUCATIONAL ATTAINMENT. Click on Selected Variables. (Don't use the "1-year" tables because they won't have data for census tracts with less than 65,000 people.) Scroll to variable S1501_C02_014E, Estimate!!Percent!!Population 25 years and over!!Percent high school graduate or higher. This is the variable I will use.
Be careful when chosing variables. If the description says "Estimate!!Total!!something-something!!Percent something-something", it is for a TOTAL COUNT not a percent. Look for a variable that is described as "Estimate!!Percent!!something-something...." for percents. Bottom line, just because you see the word "percent" somewhere in the description, make sure it is reporting percent and not total.
Get the data¶
Build the request and retrieve the education data for Delaware census tracts.
# Base URL
# Components of the base URL go into variables.
HOST = "https://api.census.gov/data"
year = "2017"
dataset = "acs/acs5/subject" # The dataset value needs to have the string "/subject" at the end
# because we are getting the data from an "S" table; a subject table.
# Code to join the base url components
base_url = "/".join([HOST, year, dataset])
# Form the predicates dictionary
predicates = {} # initialize empty dictionary
get_vars = ["NAME", "S1501_C02_014E"] # Want Name of the tract and the percent with high school or higher.
predicates["get"] = ",".join(get_vars)
predicates["for"] = "tract:*" # Census tracts are the desired geography. "*" means all tracts.
predicates["in"] = "state:10" # Delaware only
# Make the request. Results get loaded into the variable "myresponse2".
myresponse3 = requests.get(base_url, params = predicates)
# Display the result
print(myresponse3.text)
# Be careful when chosing variables. If the description says "Estimate!!Total!!something-something!!Percent
# something-something", it is for a TOTAL COUNT not a percent. Look for a variable that is described as
# "Estimate!!Percent!!something-something...." for percents.
[["NAME","S1501_C02_014E","state","county","tract"], ["Census Tract 4, New Castle County, Delaware","87.6","10","003","000400"], ["Census Tract 13, New Castle County, Delaware","99.2","10","003","001300"], ["Census Tract 26, New Castle County, Delaware","80.1","10","003","002600"], ["Census Tract 102, New Castle County, Delaware","92.0","10","003","010200"], ["Census Tract 103, New Castle County, Delaware","88.0","10","003","010300"], ["Census Tract 108, New Castle County, Delaware","93.1","10","003","010800"], ["Census Tract 6.02, New Castle County, Delaware","68.4","10","003","000602"], ["Census Tract 16, New Castle County, Delaware","80.4","10","003","001600"], ["Census Tract 23, New Castle County, Delaware","77.2","10","003","002300"], ["Census Tract 120, New Castle County, Delaware","83.8","10","003","012000"], ["Census Tract 122, New Castle County, Delaware","87.6","10","003","012200"], ["Census Tract 129, New Castle County, Delaware","78.6","10","003","012900"], ["Census Tract 132, New Castle County, Delaware","84.1","10","003","013200"], ["Census Tract 136.04, New Castle County, Delaware","95.8","10","003","013604"], ["Census Tract 142, New Castle County, Delaware","97.9","10","003","014200"], ["Census Tract 147.02, New Castle County, Delaware","95.3","10","003","014702"], ["Census Tract 147.05, New Castle County, Delaware","92.1","10","003","014705"], ["Census Tract 12, New Castle County, Delaware","93.9","10","003","001200"], ["Census Tract 114, New Castle County, Delaware","93.3","10","003","011400"], ["Census Tract 126, New Castle County, Delaware","81.3","10","003","012600"], ["Census Tract 127, New Castle County, Delaware","87.6","10","003","012700"], ["Census Tract 136.08, New Castle County, Delaware","91.9","10","003","013608"], ["Census Tract 136.11, New Castle County, Delaware","95.9","10","003","013611"], ["Census Tract 11, New Castle County, Delaware","94.4","10","003","001100"], ["Census Tract 27, New Castle County, Delaware","85.6","10","003","002700"], ["Census Tract 104, New Castle County, Delaware","97.5","10","003","010400"], ["Census Tract 112.04, New Castle County, Delaware","96.1","10","003","011204"], ["Census Tract 119, New Castle County, Delaware","94.5","10","003","011900"], ["Census Tract 125, New Castle County, Delaware","83.9","10","003","012500"], ["Census Tract 131, New Castle County, Delaware","88.0","10","003","013100"], ["Census Tract 133, New Castle County, Delaware","93.1","10","003","013300"], ["Census Tract 138, New Castle County, Delaware","92.5","10","003","013800"], ["Census Tract 140, New Castle County, Delaware","89.6","10","003","014000"], ["Census Tract 141, New Castle County, Delaware","90.2","10","003","014100"], ["Census Tract 144.03, New Castle County, Delaware","93.0","10","003","014403"], ["Census Tract 148.03, New Castle County, Delaware","90.7","10","003","014803"], ["Census Tract 150, New Castle County, Delaware","90.8","10","003","015000"], ["Census Tract 159, New Castle County, Delaware","85.5","10","003","015900"], ["Census Tract 164.01, New Castle County, Delaware","92.4","10","003","016401"], ["Census Tract 136.12, New Castle County, Delaware","98.8","10","003","013612"], ["Census Tract 145.02, New Castle County, Delaware","84.8","10","003","014502"], ["Census Tract 147.03, New Castle County, Delaware","91.2","10","003","014703"], ["Census Tract 151, New Castle County, Delaware","94.1","10","003","015100"], ["Census Tract 160, New Castle County, Delaware","85.9","10","003","016000"], ["Census Tract 510.05, Sussex County, Delaware","94.0","10","005","051005"], ["Census Tract 510.06, Sussex County, Delaware","95.5","10","005","051006"], ["Census Tract 510.07, Sussex County, Delaware","96.3","10","005","051007"], ["Census Tract 511.01, Sussex County, Delaware","96.9","10","005","051101"], ["Census Tract 511.02, Sussex County, Delaware","98.8","10","005","051102"], ["Census Tract 511.03, Sussex County, Delaware","98.4","10","005","051103"], ["Census Tract 512.01, Sussex County, Delaware","96.2","10","005","051201"], ["Census Tract 512.02, Sussex County, Delaware","99.2","10","005","051202"], ["Census Tract 512.03, Sussex County, Delaware","98.7","10","005","051203"], ["Census Tract 512.04, Sussex County, Delaware","99.3","10","005","051204"], ["Census Tract 512.05, Sussex County, Delaware","97.4","10","005","051205"], ["Census Tract 513.05, Sussex County, Delaware","90.5","10","005","051305"], ["Census Tract 513.06, Sussex County, Delaware","92.8","10","005","051306"], ["Census Tract 9900, Sussex County, Delaware","-666666666.0","10","005","990000"], ["Census Tract 109, New Castle County, Delaware","97.1","10","003","010900"], ["Census Tract 124, New Castle County, Delaware","81.2","10","003","012400"], ["Census Tract 111, New Castle County, Delaware","95.0","10","003","011100"], ["Census Tract 112.02, New Castle County, Delaware","94.6","10","003","011202"], ["Census Tract 118, New Castle County, Delaware","98.1","10","003","011800"], ["Census Tract 134, New Castle County, Delaware","93.9","10","003","013400"], ["Census Tract 136.13, New Castle County, Delaware","96.4","10","003","013613"], ["Census Tract 144.02, New Castle County, Delaware","94.9","10","003","014402"], ["Census Tract 145.01, New Castle County, Delaware","90.4","10","003","014501"], ["Census Tract 148.05, New Castle County, Delaware","97.0","10","003","014805"], ["Census Tract 161, New Castle County, Delaware","95.2","10","003","016100"], ["Census Tract 163.02, New Castle County, Delaware","95.3","10","003","016302"], ["Census Tract 168.01, New Castle County, Delaware","92.4","10","003","016801"], ["Census Tract 22, New Castle County, Delaware","61.0","10","003","002200"], ["Census Tract 110, New Castle County, Delaware","99.2","10","003","011000"], ["Census Tract 116, New Castle County, Delaware","96.3","10","003","011600"], ["Census Tract 130, New Castle County, Delaware","85.9","10","003","013000"], ["Census Tract 166.04, New Castle County, Delaware","96.2","10","003","016604"], ["Census Tract 147.06, New Castle County, Delaware","89.2","10","003","014706"], ["Census Tract 148.07, New Castle County, Delaware","92.5","10","003","014807"], ["Census Tract 154, New Castle County, Delaware","87.0","10","003","015400"], ["Census Tract 2, New Castle County, Delaware","87.3","10","003","000200"], ["Census Tract 9, New Castle County, Delaware","78.8","10","003","000900"], ["Census Tract 101.01, New Castle County, Delaware","90.4","10","003","010101"], ["Census Tract 112.05, New Castle County, Delaware","96.6","10","003","011205"], ["Census Tract 135.01, New Castle County, Delaware","96.1","10","003","013501"], ["Census Tract 143, New Castle County, Delaware","98.8","10","003","014300"], ["Census Tract 139.01, New Castle County, Delaware","83.8","10","003","013901"], ["Census Tract 24, New Castle County, Delaware","85.4","10","003","002400"], ["Census Tract 152, New Castle County, Delaware","76.0","10","003","015200"], ["Census Tract 15, New Castle County, Delaware","93.5","10","003","001500"], ["Census Tract 112.03, New Castle County, Delaware","96.9","10","003","011203"], ["Census Tract 14, New Castle County, Delaware","87.8","10","003","001400"], ["Census Tract 121, New Castle County, Delaware","90.9","10","003","012100"], ["Census Tract 136.07, New Castle County, Delaware","96.5","10","003","013607"], ["Census Tract 156, New Castle County, Delaware","76.6","10","003","015600"], ["Census Tract 112.06, New Castle County, Delaware","98.5","10","003","011206"], ["Census Tract 5, New Castle County, Delaware","84.7","10","003","000500"], ["Census Tract 135.03, New Castle County, Delaware","96.3","10","003","013503"], ["Census Tract 501.01, Sussex County, Delaware","91.0","10","005","050101"], ["Census Tract 504.01, Sussex County, Delaware","87.1","10","005","050401"], ["Census Tract 506.01, Sussex County, Delaware","79.5","10","005","050601"], ["Census Tract 503.01, Sussex County, Delaware","80.5","10","005","050301"], ["Census Tract 505.01, Sussex County, Delaware","84.3","10","005","050501"], ["Census Tract 513.03, Sussex County, Delaware","91.8","10","005","051303"], ["Census Tract 504.03, Sussex County, Delaware","81.6","10","005","050403"], ["Census Tract 508.03, Sussex County, Delaware","91.1","10","005","050803"], ["Census Tract 515, Sussex County, Delaware","85.0","10","005","051500"], ["Census Tract 517.02, Sussex County, Delaware","81.7","10","005","051702"], ["Census Tract 519, Sussex County, Delaware","85.3","10","005","051900"], ["Census Tract 506.02, Sussex County, Delaware","83.6","10","005","050602"], ["Census Tract 518.01, Sussex County, Delaware","84.5","10","005","051801"], ["Census Tract 503.02, Sussex County, Delaware","78.7","10","005","050302"], ["Census Tract 507.01, Sussex County, Delaware","85.2","10","005","050701"], ["Census Tract 513.01, Sussex County, Delaware","93.1","10","005","051301"], ["Census Tract 513.02, Sussex County, Delaware","87.2","10","005","051302"], ["Census Tract 517.01, Sussex County, Delaware","84.5","10","005","051701"], ["Census Tract 518.02, Sussex County, Delaware","82.8","10","005","051802"], ["Census Tract 501.03, Sussex County, Delaware","90.5","10","005","050103"], ["Census Tract 502, Sussex County, Delaware","73.5","10","005","050200"], ["Census Tract 510.03, Sussex County, Delaware","94.0","10","005","051003"], ["Census Tract 508.02, Sussex County, Delaware","91.0","10","005","050802"], ["Census Tract 514, Sussex County, Delaware","77.7","10","005","051400"], ["Census Tract 508.01, Sussex County, Delaware","90.7","10","005","050801"], ["Census Tract 428, Kent County, Delaware","89.5","10","001","042800"], ["Census Tract 422.02, Kent County, Delaware","89.2","10","001","042202"], ["Census Tract 431, Kent County, Delaware","84.5","10","001","043100"], ["Census Tract 402.01, Kent County, Delaware","88.1","10","001","040201"], ["Census Tract 416, Kent County, Delaware","94.0","10","001","041600"], ["Census Tract 421, Kent County, Delaware","83.0","10","001","042100"], ["Census Tract 430, Kent County, Delaware","88.3","10","001","043000"], ["Census Tract 417.02, Kent County, Delaware","90.6","10","001","041702"], ["Census Tract 402.02, Kent County, Delaware","88.1","10","001","040202"], ["Census Tract 402.03, Kent County, Delaware","89.5","10","001","040203"], ["Census Tract 409, Kent County, Delaware","83.9","10","001","040900"], ["Census Tract 413, Kent County, Delaware","88.8","10","001","041300"], ["Census Tract 417.01, Kent County, Delaware","93.3","10","001","041701"], ["Census Tract 420, Kent County, Delaware","81.5","10","001","042000"], ["Census Tract 429, Kent County, Delaware","86.2","10","001","042900"], ["Census Tract 407, Kent County, Delaware","92.9","10","001","040700"], ["Census Tract 414, Kent County, Delaware","89.1","10","001","041400"], ["Census Tract 415, Kent County, Delaware","91.1","10","001","041500"], ["Census Tract 418.01, Kent County, Delaware","86.2","10","001","041801"], ["Census Tract 418.02, Kent County, Delaware","83.2","10","001","041802"], ["Census Tract 401, Kent County, Delaware","79.9","10","001","040100"], ["Census Tract 411, Kent County, Delaware","97.7","10","001","041100"], ["Census Tract 412, Kent County, Delaware","83.5","10","001","041200"], ["Census Tract 419, Kent County, Delaware","78.8","10","001","041900"], ["Census Tract 422.01, Kent County, Delaware","92.4","10","001","042201"], ["Census Tract 425, Kent County, Delaware","82.5","10","001","042500"], ["Census Tract 410, Kent County, Delaware","85.8","10","001","041000"], ["Census Tract 144.04, New Castle County, Delaware","99.0","10","003","014404"], ["Census Tract 149.03, New Castle County, Delaware","73.2","10","003","014903"], ["Census Tract 3, New Castle County, Delaware","87.7","10","003","000300"], ["Census Tract 6.01, New Castle County, Delaware","87.2","10","003","000601"], ["Census Tract 21, New Castle County, Delaware","79.9","10","003","002100"], ["Census Tract 25, New Castle County, Delaware","88.0","10","003","002500"], ["Census Tract 112.01, New Castle County, Delaware","96.2","10","003","011201"], ["Census Tract 113, New Castle County, Delaware","96.4","10","003","011300"], ["Census Tract 115, New Castle County, Delaware","93.8","10","003","011500"], ["Census Tract 117, New Castle County, Delaware","99.5","10","003","011700"], ["Census Tract 123, New Castle County, Delaware","81.6","10","003","012300"], ["Census Tract 148.08, New Castle County, Delaware","91.2","10","003","014808"], ["Census Tract 136.10, New Castle County, Delaware","97.1","10","003","013610"], ["Census Tract 137, New Castle County, Delaware","87.4","10","003","013700"], ["Census Tract 149.04, New Castle County, Delaware","92.8","10","003","014904"], ["Census Tract 162, New Castle County, Delaware","87.8","10","003","016200"], ["Census Tract 163.01, New Castle County, Delaware","89.5","10","003","016301"], ["Census Tract 166.01, New Castle County, Delaware","94.9","10","003","016601"], ["Census Tract 166.02, New Castle County, Delaware","97.4","10","003","016602"], ["Census Tract 169.01, New Castle County, Delaware","91.1","10","003","016901"], ["Census Tract 405.01, Kent County, Delaware","87.2","10","001","040501"], ["Census Tract 405.02, Kent County, Delaware","78.6","10","001","040502"], ["Census Tract 432.02, Kent County, Delaware","83.7","10","001","043202"], ["Census Tract 433, Kent County, Delaware","89.2","10","001","043300"], ["Census Tract 434, Kent County, Delaware","87.8","10","001","043400"], ["Census Tract 9900, Kent County, Delaware","-666666666.0","10","001","990000"], ["Census Tract 19.02, New Castle County, Delaware","89.6","10","003","001902"], ["Census Tract 28, New Castle County, Delaware","94.9","10","003","002800"], ["Census Tract 29, New Castle County, Delaware","82.7","10","003","002900"], ["Census Tract 30.02, New Castle County, Delaware","59.4","10","003","003002"], ["Census Tract 101.04, New Castle County, Delaware","93.3","10","003","010104"], ["Census Tract 105.02, New Castle County, Delaware","90.5","10","003","010502"], ["Census Tract 107.02, New Castle County, Delaware","96.4","10","003","010702"], ["Census Tract 135.05, New Castle County, Delaware","99.4","10","003","013505"], ["Census Tract 135.06, New Castle County, Delaware","97.0","10","003","013506"], ["Census Tract 136.14, New Castle County, Delaware","85.3","10","003","013614"], ["Census Tract 136.15, New Castle County, Delaware","89.7","10","003","013615"], ["Census Tract 139.03, New Castle County, Delaware","95.5","10","003","013903"], ["Census Tract 139.04, New Castle County, Delaware","92.6","10","003","013904"], ["Census Tract 148.09, New Castle County, Delaware","94.4","10","003","014809"], ["Census Tract 148.10, New Castle County, Delaware","94.5","10","003","014810"], ["Census Tract 149.06, New Castle County, Delaware","94.5","10","003","014906"], ["Census Tract 149.07, New Castle County, Delaware","90.3","10","003","014907"], ["Census Tract 149.08, New Castle County, Delaware","92.3","10","003","014908"], ["Census Tract 149.09, New Castle County, Delaware","88.7","10","003","014909"], ["Census Tract 155.02, New Castle County, Delaware","83.6","10","003","015502"], ["Census Tract 158.02, New Castle County, Delaware","76.6","10","003","015802"], ["Census Tract 163.05, New Castle County, Delaware","89.6","10","003","016305"], ["Census Tract 164.04, New Castle County, Delaware","93.1","10","003","016404"], ["Census Tract 166.08, New Castle County, Delaware","92.7","10","003","016608"], ["Census Tract 168.04, New Castle County, Delaware","92.8","10","003","016804"], ["Census Tract 169.04, New Castle County, Delaware","69.2","10","003","016904"], ["Census Tract 9801, New Castle County, Delaware","-666666666.0","10","003","980100"], ["Census Tract 9901, New Castle County, Delaware","-666666666.0","10","003","990100"], ["Census Tract 501.04, Sussex County, Delaware","83.0","10","005","050104"], ["Census Tract 501.05, Sussex County, Delaware","86.6","10","005","050105"], ["Census Tract 504.05, Sussex County, Delaware","77.4","10","005","050405"], ["Census Tract 504.06, Sussex County, Delaware","86.3","10","005","050406"], ["Census Tract 504.07, Sussex County, Delaware","81.0","10","005","050407"], ["Census Tract 504.08, Sussex County, Delaware","78.6","10","005","050408"], ["Census Tract 505.03, Sussex County, Delaware","70.0","10","005","050503"], ["Census Tract 505.04, Sussex County, Delaware","76.5","10","005","050504"], ["Census Tract 507.03, Sussex County, Delaware","78.6","10","005","050703"], ["Census Tract 509.01, Sussex County, Delaware","98.2","10","005","050901"], ["Census Tract 507.04, Sussex County, Delaware","94.4","10","005","050704"], ["Census Tract 507.05, Sussex County, Delaware","85.4","10","005","050705"], ["Census Tract 507.06, Sussex County, Delaware","86.9","10","005","050706"], ["Census Tract 509.02, Sussex County, Delaware","93.4","10","005","050902"], ["Census Tract 510.04, Sussex County, Delaware","92.1","10","005","051004"]]
Use Case #1: Population in 2017 by Delaware Census Tract¶
I'll use the annual American Community Survey 5 year estimates to get the population in each Delaware census tract for year 2017. I need to use the 5 year estimates to be able to get the results for even the smallest, least populated, geographies, in this case, Delaware census tracts. Deciding whether to use the 1 year, or 5 year ACS, depends on your use case. Go here for important information about which estimate to use: https://www.census.gov/programs-surveys/acs/guidance/estimates.html.
ACS results for a year are usually released by the end of the following year. 2017 results were released in December 2018. Results for 2018 are expected to be out December 2019.
Find the desired table code here: https://api.census.gov/data/2017/acs/acs5/groups.html
Search for "population" on the web page.
Find table B01003 "Total Population"
Click on "Selected Variables"
B01003_001E is the population count variable.
# Base URL
# Components of the base URL go into variables.
HOST = "https://api.census.gov/data"
year = "2017"
dataset = "acs/acs5" # The data product
# Code to join the base url components
base_url = "/".join([HOST, year, dataset])
# Form the predicates dictionary
predicates = {} # initialize empty dictionary
get_vars = ["NAME", "B01003_001E"] # Want Name of the tract and the population. The "E" indicates estimate.
# The "B" indicates a base table.
predicates["get"] = ",".join(get_vars)
predicates["for"] = "tract:*" # Census tracts are the desired geography. "*" means all tracts.
predicates["in"] = "state:10" # Delaware only
# Note that the table code was not supplied. Specifying the data product (ACS 5 year), year and variable code is
# all that is needed. Variable codes are unique within data products.
# Make the request. Results get loaded into the variable "myresponse2".
myresponse2 = requests.get(base_url, params = predicates)
# Display the result
print(myresponse2.text)
# When the results are displayed there are going to be five values for each tract. This appears contrary
# to the get_vars list only specifying two variables. The results will have a Name, population (B01003_001E),
# state code, county code and tract code. This is just the way it works. You will need to examine the results
# and then decide which columns to use, or rename, when moving the data into a dataframe.
[["NAME","B01003_001E","state","county","tract"], ["Census Tract 4, New Castle County, Delaware","2940","10","003","000400"], ["Census Tract 13, New Castle County, Delaware","3503","10","003","001300"], ["Census Tract 26, New Castle County, Delaware","3968","10","003","002600"], ["Census Tract 102, New Castle County, Delaware","2144","10","003","010200"], ["Census Tract 103, New Castle County, Delaware","3306","10","003","010300"], ["Census Tract 108, New Castle County, Delaware","4906","10","003","010800"], ["Census Tract 6.02, New Castle County, Delaware","3247","10","003","000602"], ["Census Tract 16, New Castle County, Delaware","2112","10","003","001600"], ["Census Tract 23, New Castle County, Delaware","2814","10","003","002300"], ["Census Tract 120, New Castle County, Delaware","4716","10","003","012000"], ["Census Tract 122, New Castle County, Delaware","4481","10","003","012200"], ["Census Tract 129, New Castle County, Delaware","4593","10","003","012900"], ["Census Tract 132, New Castle County, Delaware","2744","10","003","013200"], ["Census Tract 136.04, New Castle County, Delaware","4458","10","003","013604"], ["Census Tract 142, New Castle County, Delaware","2152","10","003","014200"], ["Census Tract 147.02, New Castle County, Delaware","1785","10","003","014702"], ["Census Tract 147.05, New Castle County, Delaware","5482","10","003","014705"], ["Census Tract 12, New Castle County, Delaware","1618","10","003","001200"], ["Census Tract 114, New Castle County, Delaware","3426","10","003","011400"], ["Census Tract 126, New Castle County, Delaware","2855","10","003","012600"], ["Census Tract 127, New Castle County, Delaware","4027","10","003","012700"], ["Census Tract 136.08, New Castle County, Delaware","2112","10","003","013608"], ["Census Tract 136.11, New Castle County, Delaware","5478","10","003","013611"], ["Census Tract 11, New Castle County, Delaware","3234","10","003","001100"], ["Census Tract 27, New Castle County, Delaware","2433","10","003","002700"], ["Census Tract 104, New Castle County, Delaware","4650","10","003","010400"], ["Census Tract 112.04, New Castle County, Delaware","3434","10","003","011204"], ["Census Tract 119, New Castle County, Delaware","3434","10","003","011900"], ["Census Tract 125, New Castle County, Delaware","5269","10","003","012500"], ["Census Tract 131, New Castle County, Delaware","2468","10","003","013100"], ["Census Tract 133, New Castle County, Delaware","1917","10","003","013300"], ["Census Tract 138, New Castle County, Delaware","5401","10","003","013800"], ["Census Tract 140, New Castle County, Delaware","5192","10","003","014000"], ["Census Tract 141, New Castle County, Delaware","4269","10","003","014100"], ["Census Tract 144.03, New Castle County, Delaware","5809","10","003","014403"], ["Census Tract 148.03, New Castle County, Delaware","4129","10","003","014803"], ["Census Tract 150, New Castle County, Delaware","5824","10","003","015000"], ["Census Tract 159, New Castle County, Delaware","3893","10","003","015900"], ["Census Tract 164.01, New Castle County, Delaware","6641","10","003","016401"], ["Census Tract 136.12, New Castle County, Delaware","6306","10","003","013612"], ["Census Tract 145.02, New Castle County, Delaware","6165","10","003","014502"], ["Census Tract 147.03, New Castle County, Delaware","4585","10","003","014703"], ["Census Tract 151, New Castle County, Delaware","3474","10","003","015100"], ["Census Tract 160, New Castle County, Delaware","2850","10","003","016000"], ["Census Tract 510.05, Sussex County, Delaware","4912","10","005","051005"], ["Census Tract 510.06, Sussex County, Delaware","3046","10","005","051006"], ["Census Tract 510.07, Sussex County, Delaware","5019","10","005","051007"], ["Census Tract 511.01, Sussex County, Delaware","742","10","005","051101"], ["Census Tract 511.02, Sussex County, Delaware","946","10","005","051102"], ["Census Tract 511.03, Sussex County, Delaware","959","10","005","051103"], ["Census Tract 512.01, Sussex County, Delaware","1500","10","005","051201"], ["Census Tract 512.02, Sussex County, Delaware","903","10","005","051202"], ["Census Tract 512.03, Sussex County, Delaware","727","10","005","051203"], ["Census Tract 512.04, Sussex County, Delaware","577","10","005","051204"], ["Census Tract 512.05, Sussex County, Delaware","748","10","005","051205"], ["Census Tract 513.05, Sussex County, Delaware","2652","10","005","051305"], ["Census Tract 513.06, Sussex County, Delaware","2854","10","005","051306"], ["Census Tract 9900, Sussex County, Delaware","0","10","005","990000"], ["Census Tract 109, New Castle County, Delaware","2630","10","003","010900"], ["Census Tract 124, New Castle County, Delaware","4617","10","003","012400"], ["Census Tract 111, New Castle County, Delaware","2900","10","003","011100"], ["Census Tract 112.02, New Castle County, Delaware","4106","10","003","011202"], ["Census Tract 118, New Castle County, Delaware","3991","10","003","011800"], ["Census Tract 134, New Castle County, Delaware","2585","10","003","013400"], ["Census Tract 136.13, New Castle County, Delaware","5814","10","003","013613"], ["Census Tract 144.02, New Castle County, Delaware","3771","10","003","014402"], ["Census Tract 145.01, New Castle County, Delaware","2130","10","003","014501"], ["Census Tract 148.05, New Castle County, Delaware","10261","10","003","014805"], ["Census Tract 161, New Castle County, Delaware","1850","10","003","016100"], ["Census Tract 163.02, New Castle County, Delaware","7470","10","003","016302"], ["Census Tract 168.01, New Castle County, Delaware","5720","10","003","016801"], ["Census Tract 22, New Castle County, Delaware","2527","10","003","002200"], ["Census Tract 110, New Castle County, Delaware","3286","10","003","011000"], ["Census Tract 116, New Castle County, Delaware","3505","10","003","011600"], ["Census Tract 130, New Castle County, Delaware","1850","10","003","013000"], ["Census Tract 166.04, New Castle County, Delaware","12424","10","003","016604"], ["Census Tract 147.06, New Castle County, Delaware","2930","10","003","014706"], ["Census Tract 148.07, New Castle County, Delaware","8885","10","003","014807"], ["Census Tract 154, New Castle County, Delaware","3105","10","003","015400"], ["Census Tract 2, New Castle County, Delaware","6576","10","003","000200"], ["Census Tract 9, New Castle County, Delaware","2256","10","003","000900"], ["Census Tract 101.01, New Castle County, Delaware","4126","10","003","010101"], ["Census Tract 112.05, New Castle County, Delaware","1969","10","003","011205"], ["Census Tract 135.01, New Castle County, Delaware","6791","10","003","013501"], ["Census Tract 143, New Castle County, Delaware","6725","10","003","014300"], ["Census Tract 139.01, New Castle County, Delaware","3241","10","003","013901"], ["Census Tract 24, New Castle County, Delaware","4835","10","003","002400"], ["Census Tract 152, New Castle County, Delaware","5798","10","003","015200"], ["Census Tract 15, New Castle County, Delaware","2116","10","003","001500"], ["Census Tract 112.03, New Castle County, Delaware","4790","10","003","011203"], ["Census Tract 14, New Castle County, Delaware","2367","10","003","001400"], ["Census Tract 121, New Castle County, Delaware","2732","10","003","012100"], ["Census Tract 136.07, New Castle County, Delaware","5751","10","003","013607"], ["Census Tract 156, New Castle County, Delaware","2947","10","003","015600"], ["Census Tract 112.06, New Castle County, Delaware","4362","10","003","011206"], ["Census Tract 5, New Castle County, Delaware","3375","10","003","000500"], ["Census Tract 135.03, New Castle County, Delaware","7525","10","003","013503"], ["Census Tract 501.01, Sussex County, Delaware","3861","10","005","050101"], ["Census Tract 504.01, Sussex County, Delaware","3781","10","005","050401"], ["Census Tract 506.01, Sussex County, Delaware","5410","10","005","050601"], ["Census Tract 503.01, Sussex County, Delaware","8341","10","005","050301"], ["Census Tract 505.01, Sussex County, Delaware","3965","10","005","050501"], ["Census Tract 513.03, Sussex County, Delaware","5069","10","005","051303"], ["Census Tract 504.03, Sussex County, Delaware","3639","10","005","050403"], ["Census Tract 508.03, Sussex County, Delaware","8073","10","005","050803"], ["Census Tract 515, Sussex County, Delaware","4918","10","005","051500"], ["Census Tract 517.02, Sussex County, Delaware","5237","10","005","051702"], ["Census Tract 519, Sussex County, Delaware","4260","10","005","051900"], ["Census Tract 506.02, Sussex County, Delaware","6488","10","005","050602"], ["Census Tract 518.01, Sussex County, Delaware","4565","10","005","051801"], ["Census Tract 503.02, Sussex County, Delaware","5277","10","005","050302"], ["Census Tract 507.01, Sussex County, Delaware","4271","10","005","050701"], ["Census Tract 513.01, Sussex County, Delaware","6565","10","005","051301"], ["Census Tract 513.02, Sussex County, Delaware","3708","10","005","051302"], ["Census Tract 517.01, Sussex County, Delaware","3872","10","005","051701"], ["Census Tract 518.02, Sussex County, Delaware","4716","10","005","051802"], ["Census Tract 501.03, Sussex County, Delaware","4589","10","005","050103"], ["Census Tract 502, Sussex County, Delaware","3894","10","005","050200"], ["Census Tract 510.03, Sussex County, Delaware","5740","10","005","051003"], ["Census Tract 508.02, Sussex County, Delaware","5798","10","005","050802"], ["Census Tract 514, Sussex County, Delaware","3874","10","005","051400"], ["Census Tract 508.01, Sussex County, Delaware","3750","10","005","050801"], ["Census Tract 428, Kent County, Delaware","6877","10","001","042800"], ["Census Tract 422.02, Kent County, Delaware","9532","10","001","042202"], ["Census Tract 431, Kent County, Delaware","2867","10","001","043100"], ["Census Tract 402.01, Kent County, Delaware","5311","10","001","040201"], ["Census Tract 416, Kent County, Delaware","2060","10","001","041600"], ["Census Tract 421, Kent County, Delaware","3766","10","001","042100"], ["Census Tract 430, Kent County, Delaware","5332","10","001","043000"], ["Census Tract 417.02, Kent County, Delaware","4405","10","001","041702"], ["Census Tract 402.02, Kent County, Delaware","15267","10","001","040202"], ["Census Tract 402.03, Kent County, Delaware","6055","10","001","040203"], ["Census Tract 409, Kent County, Delaware","3016","10","001","040900"], ["Census Tract 413, Kent County, Delaware","2273","10","001","041300"], ["Census Tract 417.01, Kent County, Delaware","6097","10","001","041701"], ["Census Tract 420, Kent County, Delaware","3039","10","001","042000"], ["Census Tract 429, Kent County, Delaware","4810","10","001","042900"], ["Census Tract 407, Kent County, Delaware","4649","10","001","040700"], ["Census Tract 414, Kent County, Delaware","3622","10","001","041400"], ["Census Tract 415, Kent County, Delaware","4317","10","001","041500"], ["Census Tract 418.01, Kent County, Delaware","10075","10","001","041801"], ["Census Tract 418.02, Kent County, Delaware","5313","10","001","041802"], ["Census Tract 401, Kent County, Delaware","6849","10","001","040100"], ["Census Tract 411, Kent County, Delaware","3604","10","001","041100"], ["Census Tract 412, Kent County, Delaware","4719","10","001","041200"], ["Census Tract 419, Kent County, Delaware","5485","10","001","041900"], ["Census Tract 422.01, Kent County, Delaware","11915","10","001","042201"], ["Census Tract 425, Kent County, Delaware","3837","10","001","042500"], ["Census Tract 410, Kent County, Delaware","6445","10","001","041000"], ["Census Tract 144.04, New Castle County, Delaware","3894","10","003","014404"], ["Census Tract 149.03, New Castle County, Delaware","7561","10","003","014903"], ["Census Tract 3, New Castle County, Delaware","2794","10","003","000300"], ["Census Tract 6.01, New Castle County, Delaware","3080","10","003","000601"], ["Census Tract 21, New Castle County, Delaware","1968","10","003","002100"], ["Census Tract 25, New Castle County, Delaware","3061","10","003","002500"], ["Census Tract 112.01, New Castle County, Delaware","2428","10","003","011201"], ["Census Tract 113, New Castle County, Delaware","2147","10","003","011300"], ["Census Tract 115, New Castle County, Delaware","3015","10","003","011500"], ["Census Tract 117, New Castle County, Delaware","3992","10","003","011700"], ["Census Tract 123, New Castle County, Delaware","2646","10","003","012300"], ["Census Tract 148.08, New Castle County, Delaware","6461","10","003","014808"], ["Census Tract 136.10, New Castle County, Delaware","6250","10","003","013610"], ["Census Tract 137, New Castle County, Delaware","4228","10","003","013700"], ["Census Tract 149.04, New Castle County, Delaware","5045","10","003","014904"], ["Census Tract 162, New Castle County, Delaware","2485","10","003","016200"], ["Census Tract 163.01, New Castle County, Delaware","5418","10","003","016301"], ["Census Tract 166.01, New Castle County, Delaware","12533","10","003","016601"], ["Census Tract 166.02, New Castle County, Delaware","8455","10","003","016602"], ["Census Tract 169.01, New Castle County, Delaware","2117","10","003","016901"], ["Census Tract 405.01, Kent County, Delaware","4490","10","001","040501"], ["Census Tract 405.02, Kent County, Delaware","2252","10","001","040502"], ["Census Tract 432.02, Kent County, Delaware","4219","10","001","043202"], ["Census Tract 433, Kent County, Delaware","6085","10","001","043300"], ["Census Tract 434, Kent County, Delaware","4562","10","001","043400"], ["Census Tract 9900, Kent County, Delaware","0","10","001","990000"], ["Census Tract 19.02, New Castle County, Delaware","1885","10","003","001902"], ["Census Tract 28, New Castle County, Delaware","1527","10","003","002800"], ["Census Tract 29, New Castle County, Delaware","3587","10","003","002900"], ["Census Tract 30.02, New Castle County, Delaware","3453","10","003","003002"], ["Census Tract 101.04, New Castle County, Delaware","3752","10","003","010104"], ["Census Tract 105.02, New Castle County, Delaware","6121","10","003","010502"], ["Census Tract 107.02, New Castle County, Delaware","5439","10","003","010702"], ["Census Tract 135.05, New Castle County, Delaware","2924","10","003","013505"], ["Census Tract 135.06, New Castle County, Delaware","4466","10","003","013506"], ["Census Tract 136.14, New Castle County, Delaware","3105","10","003","013614"], ["Census Tract 136.15, New Castle County, Delaware","3916","10","003","013615"], ["Census Tract 139.03, New Castle County, Delaware","5356","10","003","013903"], ["Census Tract 139.04, New Castle County, Delaware","8290","10","003","013904"], ["Census Tract 148.09, New Castle County, Delaware","8127","10","003","014809"], ["Census Tract 148.10, New Castle County, Delaware","7820","10","003","014810"], ["Census Tract 149.06, New Castle County, Delaware","4842","10","003","014906"], ["Census Tract 149.07, New Castle County, Delaware","4889","10","003","014907"], ["Census Tract 149.08, New Castle County, Delaware","2088","10","003","014908"], ["Census Tract 149.09, New Castle County, Delaware","6386","10","003","014909"], ["Census Tract 155.02, New Castle County, Delaware","2580","10","003","015502"], ["Census Tract 158.02, New Castle County, Delaware","2354","10","003","015802"], ["Census Tract 163.05, New Castle County, Delaware","8230","10","003","016305"], ["Census Tract 164.04, New Castle County, Delaware","3412","10","003","016404"], ["Census Tract 166.08, New Castle County, Delaware","4738","10","003","016608"], ["Census Tract 168.04, New Castle County, Delaware","7334","10","003","016804"], ["Census Tract 169.04, New Castle County, Delaware","3954","10","003","016904"], ["Census Tract 9801, New Castle County, Delaware","0","10","003","980100"], ["Census Tract 9901, New Castle County, Delaware","0","10","003","990100"], ["Census Tract 501.04, Sussex County, Delaware","4520","10","005","050104"], ["Census Tract 501.05, Sussex County, Delaware","6006","10","005","050105"], ["Census Tract 504.05, Sussex County, Delaware","3983","10","005","050405"], ["Census Tract 504.06, Sussex County, Delaware","5081","10","005","050406"], ["Census Tract 504.07, Sussex County, Delaware","5310","10","005","050407"], ["Census Tract 504.08, Sussex County, Delaware","4488","10","005","050408"], ["Census Tract 505.03, Sussex County, Delaware","5587","10","005","050503"], ["Census Tract 505.04, Sussex County, Delaware","6177","10","005","050504"], ["Census Tract 507.03, Sussex County, Delaware","2021","10","005","050703"], ["Census Tract 509.01, Sussex County, Delaware","2404","10","005","050901"], ["Census Tract 507.04, Sussex County, Delaware","4357","10","005","050704"], ["Census Tract 507.05, Sussex County, Delaware","4994","10","005","050705"], ["Census Tract 507.06, Sussex County, Delaware","1769","10","005","050706"], ["Census Tract 509.02, Sussex County, Delaware","4814","10","005","050902"], ["Census Tract 510.04, Sussex County, Delaware","4794","10","005","051004"]]
Margins of Error¶
The ACS is an estimate based on less than 2% of the population, so margins of error (MOE) can be important. Sparsely populated areas can have a large margin of error which can effect the significance of your analysis.
MOEs are part of the ASC data and can be downloaded and evaluated. Appending an "M" to the end of a variable you are interested in will include the MOE for that variable in your results.
get_vars = ["NAME", "B01003_001E", "B01003_001M"] would have returned the population and the margin of error for each tract. All census MOEs are based on a 90% confidence interval.
You may wonder how the Census Bureau can make an annual estimate of the population of a census tract by mailing a survey to 2% of the households. That is something I don't fully understand myself. I believe it has something to do with using the 2010 decennial census population as the base, and then makes adjustments based on sampling data of births, deaths, migration and response/non-response ratios each year.
Use Case #2: 2017 Median Income by Census Tract for Delaware, New Jersey, Maryland and Pennsylvania¶
Here I show how to use the American Community Survey to get the median income for all census tracts in Delaware and the three surrounding states.
In some situations the "in" predicate can specify more than one state. If we were looking for data at a County level, we could have used this:
predicates["for"] = "county:*"
predicates["in"] = "state:10,24,34,42"
But for Census Tract level data we'll get an error message: "CSV not allowed for ''state'' in geography heirarchy". Possibly because there are more results than the requests library can handle. For this reason I needed to create a loop to get the results for each state and concatenate the results.
# First figure out the state codes using https://census.missouri.edu/geocodes/
# Here they are...
# Maryland = 24
# New Jersy = 34
# Pennsylvania = 42
# Delaware = 10
# Define a function to call for each state
def getincome(statecode):
# Base URL
# Components of the base URL go into variables.
HOST = "https://api.census.gov/data"
year = "2017"
dataset = "acs/acs5" # The data product
# Code to join the base url components
base_url = "/".join([HOST, year, dataset])
# Form the predicates dictionary
predicates = {} # initialize empty dictionary
get_vars = ["NAME", "B06011_001E"] # Want Name of the tract and the median income. The "E" indicates estimate.
# The "B" indicates a base table. I'll ignore MOEs.
predicates["get"] = ",".join(get_vars)
predicates["for"] = "tract:*" # Census tracts are the desired geography. "*" means all tracts.
predicates["in"] = "state:" + statecode
# Note that the table code was not supplied. Specifying the data product (ACS 5 year), year and variable code is
# all that is needed. Variable codes are unique within census datasets.
# Make the request. Results get loaded into the variable "myresponse3".
myresponse3 = requests.get(base_url, params = predicates)
# Make a data frame out of the response for one state. The first row is the column names.
df = pd.DataFrame(columns = myresponse3.json()[0], data = myresponse3.json()[1:])
# Add the dataframe for this state to the list of data frames we will later merge into one data frame.
dfs.append(df)
# Call the function for each state
dfs = [] # Initialize an empty list that will contain the data frames for all the states.
getincome("10")
getincome("24")
getincome("34")
getincome("42")
# The pandas concat method asks for a list of data frames that need to be combined.
# Thats what dfs is, a list of data frames.
df_all_median_income = pd.concat(dfs)
# Set the desired columns names
df_all_median_income.columns = ["Name", "MedianIncome", "StateCode", "CountyCode", "CensusTractCode"]
# Examine the final results of median income by census tract downloaded from the Census website.
df_all_median_income.head()
# A complete unique census tract geoidentifier consists of the combined state,
# county and census tract code. Example, the code for Census Tract 148.08, New Castle County, Delaware
# is 10003014808
| Name | MedianIncome | StateCode | CountyCode | CensusTractCode | |
|---|---|---|---|---|---|
| 0 | Census Tract 4, New Castle County, Delaware | 21443 | 10 | 003 | 000400 |
| 1 | Census Tract 13, New Castle County, Delaware | 53962 | 10 | 003 | 001300 |
| 2 | Census Tract 26, New Castle County, Delaware | 20115 | 10 | 003 | 002600 |
| 3 | Census Tract 102, New Castle County, Delaware | 32409 | 10 | 003 | 010200 |
| 4 | Census Tract 103, New Castle County, Delaware | 36121 | 10 | 003 | 010300 |
Use Case #3: Display a Chloropleth Map of Delaware Census Tract Relative Household Size¶
Next is a plot of Delaware census tracts summarizing household size.
The purpose of this section is to show how numerical data can be represented using color coding. It will also show how the numerical data can be manipulated "pandas fashion" to add new features to the mapping.
This starts by displaying color coding of the number of people per household by census tract.
As always, when working with geospatial shape files we are actually talking about sets of files, all with the same name but having different extensions: .shp, .dbf, .prj, .cpg, .shx, .xml. These files work together and need to be in the same folder.
The shape file was obtained from http://opendata.firstmap.delaware.gov/datasets/delaware-census-tracts-2010/data.
# Downloaded the shape file of Delaware from this website.
# http://opendata.firstmap.delaware.gov/datasets/delaware-census-tracts-2010/data
# Citations on how to draw geospatial maps:
# https://medium.com/using-specialist-business-databases/creating-a-choropleth-map-using-geopandas-and-financial-data-c76419258746
# http://geopandas.org/mapping.html
# https://residentmario.github.io/geoplot/gallery/plot_nyc_parking_tickets.html
# Load the shape file into a geo dataframe
mygdf = gpd.read_file("C:\\Users\\801189855\\Documents\Geocoding\DE_Census_Tracts_2010\Census_Tracts_2010.shp")
# Note - you'll get an error if the other files in the shape file set not in the same folder as the .shp file.
# Examine the geo dataframe.
print(mygdf.head())
# Geo dataframes act a lot like regular dataframes, but they always have a "geometry" columns that lays out
# the points needed to be connected to form the outlines of the shapes that will be drawn.
# Geopandas does not do the drawing. A separate Python package (in this case "geoplot") reads the
# geometry data and does the drawing. There are several other packages that can also do this.
# Columns like POPULATION and HOUSING_UN are data columns that were included by the original creators of this
# shape file to provide some basic data to play with.
# Later I'll use the NAME_ columns to merge the census tract median income dataframe with this geo dataframe to
# map relative income among census tracts.
# Note - there are 218 census tracts in Delaware.
OBJECTID NAME10 NAMELSAD10 POPULATION HOUSING_UN \
0 5 402.01 Census Tract 402.01 5041 2023
1 6 402.03 Census Tract 402.03 5017 2022
2 7 402.02 Census Tract 402.02 12763 4751
3 8 418.02 Census Tract 418.02 5203 2054
4 9 425 Census Tract 425 3455 1490
geometry
0 POLYGON ((-75.62326 39.30803, -75.62315 39.308...
1 POLYGON ((-75.55809 39.33896, -75.55833 39.339...
2 POLYGON ((-75.62572 39.29010, -75.62510 39.290...
3 POLYGON ((-75.63280 39.12490, -75.63282 39.124...
4 POLYGON ((-75.43311 38.91125, -75.43330 38.911...
### Next - draw the map
# Create matplotlib axes object for the plot.
# The color will be based on the ratio of population to housing units.
myaxes = gplt.choropleth(
mygdf,
hue = np.round(mygdf['POPULATION'] / mygdf['HOUSING_UN'], 2),
cmap ='Blues',
figsize = (40, 15),
legend = True,
#k = 5 # number of buckets,
)
# Axes (not to be confused with axis) objects are the sub-areas within an overall figure.
# There are always 1 or more of these in a figure. They can be used to create a grid of rows and
# columns of sub-plots. They can be managed individually to give them separate scales, titles, legends
# etc.
# Set the title property in the axes object and display the map.
myaxes.set_title('Ratio of People Per Housing Unit', fontsize=16)
C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\colors.py:527: RuntimeWarning: invalid value encountered in less xa[xa < 0] = -1
Text(0.5, 1.0, 'Ratio of People Per Housing Unit')
Use Case #4: Display a Chloropleth Map of Delaware Census Tract Median Income¶
Next is a plot of Delaware census tracts summarizing median income.
### Here is where we bring in the income data into the geo dataframe.
# Split the Name field in the income dataframe so the part before the comma can become a new
# column called TractName. This is the column I'll use to join to the geo dataframe.
new = df_all_median_income["Name"].str.split(",", n = 1, expand = True)
df_all_median_income["TractName"] = new[0]
df_all_median_income.head()
# Convert MedianIncome type from object to integer so it can be used to set the colors in the map.
df_all_median_income["MedianIncome"] = df_all_median_income["MedianIncome"].astype(int)
# The income dataframe contains data for 4 states. Create a new dataframe with just Delaware data.
df_all_median_income_DE = df_all_median_income[df_all_median_income["StateCode"] == "10"]
# Merge the Delaware income data into the geo dataframe
gdf_income = mygdf.merge(df_all_median_income_DE, left_on = "NAMELSAD10", right_on = "TractName")
## Examine the geo dataframe of Delaware census tracts that now contains income data.
gdf_income.head()
| OBJECTID | NAME10 | NAMELSAD10 | POPULATION | HOUSING_UN | geometry | Name | MedianIncome | StateCode | CountyCode | CensusTractCode | TractName | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 5 | 402.01 | Census Tract 402.01 | 5041 | 2023 | POLYGON ((-75.62326 39.30803, -75.62315 39.308... | Census Tract 402.01, Kent County, Delaware | 29439 | 10 | 001 | 040201 | Census Tract 402.01 |
| 1 | 6 | 402.03 | Census Tract 402.03 | 5017 | 2022 | POLYGON ((-75.55809 39.33896, -75.55833 39.339... | Census Tract 402.03, Kent County, Delaware | 27706 | 10 | 001 | 040203 | Census Tract 402.03 |
| 2 | 7 | 402.02 | Census Tract 402.02 | 12763 | 4751 | POLYGON ((-75.62572 39.29010, -75.62510 39.290... | Census Tract 402.02, Kent County, Delaware | 35691 | 10 | 001 | 040202 | Census Tract 402.02 |
| 3 | 8 | 418.02 | Census Tract 418.02 | 5203 | 2054 | POLYGON ((-75.63280 39.12490, -75.63282 39.124... | Census Tract 418.02, Kent County, Delaware | 35299 | 10 | 001 | 041802 | Census Tract 418.02 |
| 4 | 9 | 425 | Census Tract 425 | 3455 | 1490 | POLYGON ((-75.43311 38.91125, -75.43330 38.911... | Census Tract 425, Kent County, Delaware | 26982 | 10 | 001 | 042500 | Census Tract 425 |
gdf_income.dtypes
OBJECTID int64 NAME10 object NAMELSAD10 object POPULATION int64 HOUSING_UN int64 geometry geometry Name object MedianIncome int32 StateCode object CountyCode object CensusTractCode object TractName object dtype: object
# Display a geo plot of the income data.
# Create matplotlib axes object for the plot.
# The color will be based on the ratio of population to housing units.
axes_income = gplt.choropleth(
df = gdf_income,
hue = gdf_income['MedianIncome'].astype(int),
cmap ='Blues',
figsize = (40, 15),
legend = True,
k = 7 # number of buckets,
)
# Set the title property in the axes object and display the map.
axes_income.set_title('Median Income Per Census Tract', fontsize=16)
### The darker areas show higher income
--------------------------------------------------------------------------- ValueError Traceback (most recent call last) <ipython-input-27-305ecbbfefa3> in <module> 8 figsize = (40, 15), 9 legend = True, ---> 10 k = 7 # number of buckets, 11 ) 12 C:\ProgramData\Anaconda3\lib\site-packages\geoplot\geoplot.py in choropleth(df, projection, hue, cmap, norm, scheme, legend, legend_kwargs, legend_labels, legend_values, extent, figsize, ax, **kwargs) 980 hue=hue, scheme=scheme, cmap=cmap, norm=norm, 981 legend=legend, legend_values=legend_values, legend_labels=legend_labels, --> 982 legend_kwargs=legend_kwargs, **kwargs 983 ) 984 return plot.draw() C:\ProgramData\Anaconda3\lib\site-packages\geoplot\geoplot.py in __init__(self, df, **kwargs) 947 def __init__(self, df, **kwargs): 948 super().__init__(df, **kwargs) --> 949 self.set_hue_values(color_kwarg=None, default_color=None) 950 self.paint_legend(supports_hue=True, supports_scale=False) 951 C:\ProgramData\Anaconda3\lib\site-packages\geoplot\geoplot.py in set_hue_values(self, color_kwarg, default_color, supports_categorical, verify_input) 42 if 'k' in self.kwargs: 43 raise ValueError( ---> 44 f"The 'k' parameter was removed in geoplot version 0.4.0. To set a specific " 45 f"categorical bin count, pass a mapclassify object to 'scheme' instead. For " 46 f'further information refer to the release notes at ' ValueError: The 'k' parameter was removed in geoplot version 0.4.0. To set a specific categorical bin count, pass a mapclassify object to 'scheme' instead. For further information refer to the release notes at https://github.com/ResidentMario/geoplot/releases/tag/0.4.0
Get Additional Selected Data from the Census Website¶
These are some things we will get:
(NSES) Neighborhood socio-economic status components:
(1) % adults age 25 yrs or older with less than high school graduation
ACS 5-year table S1501. Variable S1501_C02_014E Estimate!!Percent!!Population 25 years and over!!Percent high school graduate or higher. Subtract the values from 100 to get desired value.
(2) % men who are unemployed
ACS 5-year table S2301. S2301_C04_022E Estimate!!Unemployment rate!!Population 20 to 64 years!!SEX!!Male
(3) % households with income below the poverty level
ACS 5-year table DP03. DP03_0119PE Percent Estimate!!PERCENTAGE OF FAMILIES AND PEOPLE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL!!All families
(4) % households receiving public Assistance
ACS 5-year table DP03. DP03_0072PE Percent Estimate!!INCOME AND BENEFITS (IN 2017 INFLATION-ADJUSTED DOLLARS)!!Total households!!With cash public assistance income
(5) % households with children in which the head of household is female
ACS 5-year table B11004. B11004_016E Estimate!!Total!!Other family!!Female householder no husband present!!With related children of the householder under 18 years (numerator)
ACS 5-year table B11004. B11004_001E Estimate!!Total (total number of families will be divisor)
(6) $ median household income
ACS 5-year table DP03. DP03_0062E Estimate!!INCOME AND BENEFITS (IN 2017 INFLATION-ADJUSTED DOLLARS)!!Total households!!Median household income (dollars)
(NSED) Neighborhood socio-economic disadvantage components:
(7) % unemployed individual (unemployment rate)
ACS 5-year table S2301. S2301_C04_001E Estimate!!Unemployment rate!!Population 16 years and over
(8) % households with no cars
ACS 5-year table S2504. S2504_C02_027E Estimate!!Percent occupied housing units!!Occupied housing units!!VEHICLES AVAILABLE!!No vehicle available
(9) % crowded housing
ACS 5-year table S2501. S2501_C02_006E Estimate!!Percent occupied housing units!!Occupied housing units!!OCCUPANTS PER ROOM!!1.00 or less occupants per room.
Subtract from 100 to get percent of households where there is more than one person per room. This is a standard of overcrowding described by https://www.census.gov/content/dam/Census/programs-surveys/ahs/publications/Measuring_Overcrowding_in_Hsg.pdf
(10) % renters
ACS 5-year table B25003. B25003_003E Estimate!!Total!!Renter occupied (numerator)
B25003_001E Estimate!!Total (total number of housing units will be the divisor)
(11) % males not in management & professional occupations
ACS 5-year table S2401. S2401_C03_002E Estimate!!Percent Male!!Civilian employed population 16 years and over!!Management business science and arts occupations. (Only available for 2017, 2016 and 2015)
(12) % households in poverty
ACS 5-year table DP03. DP03_0119PE Percent Estimate!!PERCENTAGE OF FAMILIES AND PEOPLE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL!!All families
(13) % female headed households with dependents
ACS 5-year table B11004. B11004_016E Estimate!!Total!!Other family!!Female householder no husband present!!With related children of the householder under 18 years (numerator)
ACS 5-year table B11004. B11004_001E Estimate!!Total (total number of families will be divisor)
(14) % on public assistance
ACS 5-year table DP03. DP03_0072PE Percent Estimate!!INCOME AND BENEFITS (IN 2017 INFLATION-ADJUSTED DOLLARS)!!Total households!!With cash public assistance income
(15) % earning less than $35,000 (changed from < 30k/year 2000 U.S Census data)
ACS 5-year table S2001. S2001_C02_007E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$25,000to$34,999.
ACS 5-year table S2001. S2001_C02_006E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$15,000 to $24,999.
ACS 5-year table S2001. S2001_C02_005E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$10,000 to $14,999.
ACS 5-year table S2001. S2001_C02_004E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$1 to $9,999 or less.
(Add these up to get percent earning less than $35,000. Could not find a $30,000 cut off in the census data.)
(16) % less than high school education
ACS 5-year table S1501. Variable S1501_C02_014E Estimate!!Percent!!Population 25 years and over!!Percent high school graduate or higher. Subtract the values from 100 to get desired value.
(17) % Black residents
ACS 5-year table DP05. DP05_0065PE Percent Estimate!!Race alone or in combination with one or more other races!!Total population!!Black or African American
(18) % residents under the age of 16 yrs
ACS 5-year table DP05. DP05_0020PE Percent Estimate!!SEX AND AGE!!Total population!!16 years and over.
This grouping was not available before 2017.
(Subtract this value frm 100 to get the percent under age 16.)
(19) COMPUTERS AND INTERNET USE (lets look at this too) Data Profile table DP02
ACS 5-year table DP02. DP02_0151PE Percent Estimate!!COMPUTERS AND INTERNET USE!!Total households!!With a computer.
ACS 5-year table DP02. DP02_0152PE Percent Estimate!!COMPUTERS AND INTERNET USE!!Total households!!With a broadband Internet subscription.
(20) GINI Index of income Inequality
ACS 5-year table B19083. B19083_001E Estimate!!Gini Index
(21) Black and White Populations
ACS 5-year table B02001. B02001_002E Estimate!!Total!!White alone. B02001_003E Estimate!!Total!!Black or African American alone
(22) % Latino Population
ACS 5-year table DP05. DP05_0071PE Percent Estimate!!HISPANIC OR LATINO AND RACE!!Total population!!Hispanic or Latino (of any race).
ACS 5-year table DP05. DP05_0076PE Percent Estimate!!HISPANIC OR LATINO AND RACE!!Total population!!Not Hispanic or Latino.
(23) % Foreign Born
ACS 5-year table DP02. DP02_0092PE Percent Estimate!!PLACE OF BIRTH!!Total population!!Foreign born.
(24) % Disabled
ACS 5-year table DP02.
DP02_0073PE Percent Estimate!!DISABILITY STATUS OF THE CIVILIAN NONINSTITUTIONALIZED POPULATION!!Under 18 years!!With a disability.
DP02_0075PE Percent Estimate!!DISABILITY STATUS OF THE CIVILIAN NONINSTITUTIONALIZED POPULATION!!18 to 64 years!!With a disability.
DP02_0077PE Percent Estimate!!DISABILITY STATUS OF THE CIVILIAN NONINSTITUTIONALIZED POPULATION!!65 years and over!!With a disability.
In most cases above I used https://www.census.gov/programs-surveys/acs/guidance/which-data-tool/table-ids-explained.html to figure out a Subject number (eg. 15 for education, 23 for employment etc.) for the "S" sets.
For "S" datasets I used https://api.census.gov/data/2017/acs/acs5/subject/groups.html to find the table codes associated with the subject number. Then used the Selected Variables link to see the complete list of variables for a table. Then did "finds" on the variables page until I located the desired variable code.
For "DP" (Data Profile) datasets use https://api.census.gov/data/2017/acs/acs5/profile/groups.html (Note the use of the word "profile" instead of "subject".
For "B" (Base table) datasets to get table ID and variable ID use https://api.census.gov/data/2017/acs/acs5/groups.html (Note the lack of the word "profile" or "subject".
Note that in the above URLs for getting lists of table IDs, the survey year is part of the address. You will need to repeat the search process for each year you are getting data because variable IDs can change between years.
"Table shells" are a simplified hierarchical view of each table's variables https://www2.census.gov/programs-surveys/acs/tech_docs/table_shells/2017/?#
There is a bit of an art to this. You may want to use American FactFinder to interact with the datasets to help find the table and variable that is most appropriate. When searching for variable codes, watch out for subtle mistakes like not accounting for the distinction between counts and percents, between values and margins of errir, between the concepts of household, family and individual, between earnings and income, etc.
### CODE TESTING AREA ###
# Base URL
# Components of the base URL go into variables.
HOST = "https://api.census.gov/data"
year = "2014"
dataset = "acs/acs5/subject" # The dataset value needs to have the string "/subject" at the end
# because we are getting the data from an "S" table; a subject table.
# dataset = "acs/acs5/profile" # The dataset value needs to have the string "/profile" at the end
# because we are getting the data from a "DP" table; a data profile table.
# dataset = "acs/acs5/" # The dataset value because we are getting the data from a Base "B" table.
# Code to join the base url components
base_url = "/".join([HOST, year, dataset])
# Form the predicates dictionary
predicates = {} # initialize empty dictionary
get_vars = ["NAME", "S2504_C01_026E"] # Want Name of the tract and variable values.
predicates["get"] = ",".join(get_vars)
predicates["for"] = "tract:*" # Census tracts are the desired geography. "*" means all tracts.
predicates["in"] = "state:10+county:003" # Delaware only
# Make the request. Results get loaded into the variable "myresponse2".
myresponse3 = requests.get(base_url, params = predicates)
# Display the result
print(myresponse3.text)
[["NAME","S2504_C01_026E","state","county","tract"], ["Census Tract 16, New Castle County, Delaware","40.0","10","003","001600"], ["Census Tract 23, New Castle County, Delaware","27.9","10","003","002300"], ["Census Tract 120, New Castle County, Delaware","6.4","10","003","012000"], ["Census Tract 122, New Castle County, Delaware","11.6","10","003","012200"], ["Census Tract 129, New Castle County, Delaware","10.3","10","003","012900"], ["Census Tract 132, New Castle County, Delaware","1.8","10","003","013200"], ["Census Tract 136.04, New Castle County, Delaware","2.7","10","003","013604"], ["Census Tract 142, New Castle County, Delaware","0.7","10","003","014200"], ["Census Tract 147.02, New Castle County, Delaware","3.6","10","003","014702"], ["Census Tract 147.05, New Castle County, Delaware","2.7","10","003","014705"], ["Census Tract 12, New Castle County, Delaware","5.8","10","003","001200"], ["Census Tract 114, New Castle County, Delaware","8.3","10","003","011400"], ["Census Tract 126, New Castle County, Delaware","3.5","10","003","012600"], ["Census Tract 127, New Castle County, Delaware","10.0","10","003","012700"], ["Census Tract 136.08, New Castle County, Delaware","5.1","10","003","013608"], ["Census Tract 136.11, New Castle County, Delaware","1.7","10","003","013611"], ["Census Tract 11, New Castle County, Delaware","24.1","10","003","001100"], ["Census Tract 27, New Castle County, Delaware","15.4","10","003","002700"], ["Census Tract 104, New Castle County, Delaware","7.8","10","003","010400"], ["Census Tract 112.04, New Castle County, Delaware","6.2","10","003","011204"], ["Census Tract 133, New Castle County, Delaware","0.5","10","003","013300"], ["Census Tract 119, New Castle County, Delaware","1.6","10","003","011900"], ["Census Tract 125, New Castle County, Delaware","10.3","10","003","012500"], ["Census Tract 131, New Castle County, Delaware","4.4","10","003","013100"], ["Census Tract 138, New Castle County, Delaware","3.7","10","003","013800"], ["Census Tract 140, New Castle County, Delaware","6.8","10","003","014000"], ["Census Tract 141, New Castle County, Delaware","1.7","10","003","014100"], ["Census Tract 144.03, New Castle County, Delaware","10.6","10","003","014403"], ["Census Tract 148.03, New Castle County, Delaware","2.4","10","003","014803"], ["Census Tract 150, New Castle County, Delaware","6.1","10","003","015000"], ["Census Tract 159, New Castle County, Delaware","2.1","10","003","015900"], ["Census Tract 139.03, New Castle County, Delaware","2.9","10","003","013903"], ["Census Tract 139.04, New Castle County, Delaware","1.4","10","003","013904"], ["Census Tract 148.09, New Castle County, Delaware","6.1","10","003","014809"], ["Census Tract 144.04, New Castle County, Delaware","1.2","10","003","014404"], ["Census Tract 149.03, New Castle County, Delaware","6.2","10","003","014903"], ["Census Tract 3, New Castle County, Delaware","19.4","10","003","000300"], ["Census Tract 6.01, New Castle County, Delaware","20.2","10","003","000601"], ["Census Tract 21, New Castle County, Delaware","50.2","10","003","002100"], ["Census Tract 25, New Castle County, Delaware","10.4","10","003","002500"], ["Census Tract 112.01, New Castle County, Delaware","6.3","10","003","011201"], ["Census Tract 113, New Castle County, Delaware","7.9","10","003","011300"], ["Census Tract 115, New Castle County, Delaware","1.2","10","003","011500"], ["Census Tract 117, New Castle County, Delaware","1.6","10","003","011700"], ["Census Tract 123, New Castle County, Delaware","14.2","10","003","012300"], ["Census Tract 148.08, New Castle County, Delaware","1.4","10","003","014808"], ["Census Tract 136.10, New Castle County, Delaware","5.3","10","003","013610"], ["Census Tract 137, New Castle County, Delaware","11.7","10","003","013700"], ["Census Tract 149.04, New Castle County, Delaware","2.6","10","003","014904"], ["Census Tract 162, New Castle County, Delaware","5.8","10","003","016200"], ["Census Tract 163.01, New Castle County, Delaware","1.8","10","003","016301"], ["Census Tract 166.01, New Castle County, Delaware","1.4","10","003","016601"], ["Census Tract 166.02, New Castle County, Delaware","0.8","10","003","016602"], ["Census Tract 169.01, New Castle County, Delaware","0.0","10","003","016901"], ["Census Tract 6.02, New Castle County, Delaware","36.1","10","003","000602"], ["Census Tract 28, New Castle County, Delaware","21.1","10","003","002800"], ["Census Tract 29, New Castle County, Delaware","54.1","10","003","002900"], ["Census Tract 105.02, New Castle County, Delaware","7.0","10","003","010502"], ["Census Tract 19.02, New Castle County, Delaware","25.0","10","003","001902"], ["Census Tract 30.02, New Castle County, Delaware","41.8","10","003","003002"], ["Census Tract 101.04, New Castle County, Delaware","12.6","10","003","010104"], ["Census Tract 107.02, New Castle County, Delaware","2.6","10","003","010702"], ["Census Tract 135.05, New Castle County, Delaware","2.5","10","003","013505"], ["Census Tract 135.06, New Castle County, Delaware","0.0","10","003","013506"], ["Census Tract 136.14, New Castle County, Delaware","4.8","10","003","013614"], ["Census Tract 136.15, New Castle County, Delaware","4.4","10","003","013615"], ["Census Tract 147.06, New Castle County, Delaware","13.9","10","003","014706"], ["Census Tract 148.07, New Castle County, Delaware","0.9","10","003","014807"], ["Census Tract 154, New Castle County, Delaware","9.5","10","003","015400"], ["Census Tract 2, New Castle County, Delaware","8.2","10","003","000200"], ["Census Tract 9, New Castle County, Delaware","34.6","10","003","000900"], ["Census Tract 101.01, New Castle County, Delaware","14.7","10","003","010101"], ["Census Tract 112.05, New Castle County, Delaware","2.6","10","003","011205"], ["Census Tract 135.01, New Castle County, Delaware","4.9","10","003","013501"], ["Census Tract 143, New Castle County, Delaware","5.2","10","003","014300"], ["Census Tract 139.01, New Castle County, Delaware","2.8","10","003","013901"], ["Census Tract 24, New Castle County, Delaware","7.1","10","003","002400"], ["Census Tract 152, New Castle County, Delaware","8.1","10","003","015200"], ["Census Tract 15, New Castle County, Delaware","35.5","10","003","001500"], ["Census Tract 112.03, New Castle County, Delaware","1.5","10","003","011203"], ["Census Tract 14, New Castle County, Delaware","17.3","10","003","001400"], ["Census Tract 121, New Castle County, Delaware","2.5","10","003","012100"], ["Census Tract 136.07, New Castle County, Delaware","4.3","10","003","013607"], ["Census Tract 156, New Castle County, Delaware","7.5","10","003","015600"], ["Census Tract 112.06, New Castle County, Delaware","2.9","10","003","011206"], ["Census Tract 5, New Castle County, Delaware","24.4","10","003","000500"], ["Census Tract 135.03, New Castle County, Delaware","3.2","10","003","013503"], ["Census Tract 164.01, New Castle County, Delaware","1.1","10","003","016401"], ["Census Tract 136.12, New Castle County, Delaware","0.0","10","003","013612"], ["Census Tract 145.02, New Castle County, Delaware","13.7","10","003","014502"], ["Census Tract 147.03, New Castle County, Delaware","12.6","10","003","014703"], ["Census Tract 151, New Castle County, Delaware","7.4","10","003","015100"], ["Census Tract 160, New Castle County, Delaware","14.2","10","003","016000"], ["Census Tract 4, New Castle County, Delaware","36.4","10","003","000400"], ["Census Tract 13, New Castle County, Delaware","1.5","10","003","001300"], ["Census Tract 26, New Castle County, Delaware","23.0","10","003","002600"], ["Census Tract 109, New Castle County, Delaware","1.6","10","003","010900"], ["Census Tract 102, New Castle County, Delaware","2.8","10","003","010200"], ["Census Tract 103, New Castle County, Delaware","1.8","10","003","010300"], ["Census Tract 108, New Castle County, Delaware","3.7","10","003","010800"], ["Census Tract 124, New Castle County, Delaware","5.1","10","003","012400"], ["Census Tract 111, New Castle County, Delaware","0.8","10","003","011100"], ["Census Tract 112.02, New Castle County, Delaware","0.7","10","003","011202"], ["Census Tract 118, New Castle County, Delaware","7.0","10","003","011800"], ["Census Tract 134, New Castle County, Delaware","3.4","10","003","013400"], ["Census Tract 136.13, New Castle County, Delaware","1.4","10","003","013613"], ["Census Tract 144.02, New Castle County, Delaware","4.2","10","003","014402"], ["Census Tract 145.01, New Castle County, Delaware","21.7","10","003","014501"], ["Census Tract 148.05, New Castle County, Delaware","1.4","10","003","014805"], ["Census Tract 161, New Castle County, Delaware","4.0","10","003","016100"], ["Census Tract 163.02, New Castle County, Delaware","1.9","10","003","016302"], ["Census Tract 168.01, New Castle County, Delaware","1.6","10","003","016801"], ["Census Tract 22, New Castle County, Delaware","25.2","10","003","002200"], ["Census Tract 110, New Castle County, Delaware","0.7","10","003","011000"], ["Census Tract 116, New Castle County, Delaware","2.3","10","003","011600"], ["Census Tract 130, New Castle County, Delaware","1.4","10","003","013000"], ["Census Tract 166.04, New Castle County, Delaware","3.0","10","003","016604"], ["Census Tract 148.10, New Castle County, Delaware","0.0","10","003","014810"], ["Census Tract 149.06, New Castle County, Delaware","3.9","10","003","014906"], ["Census Tract 149.07, New Castle County, Delaware","6.1","10","003","014907"], ["Census Tract 149.08, New Castle County, Delaware","12.8","10","003","014908"], ["Census Tract 149.09, New Castle County, Delaware","5.9","10","003","014909"], ["Census Tract 155.02, New Castle County, Delaware","11.7","10","003","015502"], ["Census Tract 158.02, New Castle County, Delaware","5.3","10","003","015802"], ["Census Tract 163.05, New Castle County, Delaware","3.7","10","003","016305"], ["Census Tract 164.04, New Castle County, Delaware","8.3","10","003","016404"], ["Census Tract 166.08, New Castle County, Delaware","0.8","10","003","016608"], ["Census Tract 168.04, New Castle County, Delaware","4.1","10","003","016804"], ["Census Tract 169.04, New Castle County, Delaware","4.0","10","003","016904"], ["Census Tract 9801, New Castle County, Delaware","-666666666.0","10","003","980100"], ["Census Tract 9901, New Castle County, Delaware","-666666666.0","10","003","990100"]]
Define a Function to Retrieve Delaware Census Data¶
I define a function that takes as inputs a single table code, a list of one or more variable codes belonging to that table, and a list of one or more years. The table must start with one of these prefix letter codes: B, S or DP. It will return data for all Delaware census tracts for those variables.
The function will only work for subject tables, base tables and data profile tables.
The reason it is necessary to pass a list of variables, one for each year, is that variable codes can change over time.
The only way to determine what the correct variable code is for each year is by manually searching each year's table structure using the groups Web page. Example. https://api.census.gov/data/2017/acs/acs5/groups.html. In this example you would replace the 2017 with another year and search for the table and look at the variable names. Do that for each year you are extracting data.
def fn_delaware_tract_data(tablecode, variable_dict, yearlist, geolevel = 'tract'):
# Inputs:
# tablecode: a single table code that starts with B, S or DP
# variable_dict: a dictionary of lists of variable codes, one key per year. One or more codes per year.
# Example, {'2014': ["DP05_0060PE", "DP05_0060PEA"], '2015': ["DP05_0060PE", "DP05_0060PEA"],
# '2016': ["DP05_0060PE", "DP05_0060PEA"], '2017': ["DP05_0065PE", "DP05_0065PEA"]}
# yearlist: a list of 4 digit years
# geolevel: the geography type. Defaults to census tract if not specified. Can specify "block" for census block.
#
# Output:
# A dataframe containing data for all the variables for all the years
# Root url path
HOST = "https://api.census.gov/data"
# Make sure request is for a Base, Subject or Data Profile table as indicated by the leading letters
# in the table code.
s = tablecode[0]
if s not in ("S", "D", "B", "P"):
print("Error: table did not start with B, S, D or P")
return
# The leading letter determines the base of the URL
if s == "S":
dataset = "acs/acs5/subject"
elif s == "D":
dataset = "acs/acs5/profile"
elif s == "B":
dataset = "acs/acs5/"
elif s == "P":
dataset = "dec/sf1/"
## Variable codes can change over the years, however we need a single code to identify each variable in our output
## so we can concatenate the different years results into one dataframe. It won't matter which code we choose for this.
## I decided to use the code for the latest year ("max") in the set of years we are working with as the common code.
## When the same census variable ID is used to identify different concepts in different years, you are going to get
## ValueError: Plan shapes are not aligned. To get around this you will needed to do separate batches of output
## by year and merge them manually.
variablecodes_final = variable_dict.get(max(variable_dict))
# Loop through the years
for year in yearlist:
# Code to join the base url components
base_url = "/".join([HOST, year, dataset])
# Form the predicates dictionary
predicates = {} # initialize empty dictionary
## Build the variable list.
# Get the list of variables for the year.
year_vars_list = variable_dict.get(year)
# Loop through each variable for the year and add it to the get_vars list.
# First variable in the list is always "NAME".
get_vars = ["NAME"]
for var in year_vars_list:
get_vars.append(var) # Add the others varaibles
predicates["get"] = ",".join(get_vars)
predicates["for"] = geolevel + ":*" # Get values for all the geolevels (census tracts or census blocks)
# Geolevel defaults to "tract" of not specified when this function is called.
### Some years there is a constraint on the State > Tract geography hierarchy which prevents getting all the
### census tract results in one pass for the State and there needs to be a separate pass for each county.
### Rather than figuring out when that's actually a problem, I just do it for all the requests.
# loop through Kent, New Castle, Sussex in turn.
for cnty in ("001", "003", "005"):
predicates["in"] = "state:10+county:" + cnty # Delaware only
# Make the request. Results get loaded into the variable "myresponse2".
theresponse = requests.get(base_url, params = predicates)
# explore the response during testing
# print(year)
# print(theresponse.text)
# Make a data frame out of the response. The first row is the column names.
df = pd.DataFrame(columns = theresponse.json()[0], data = theresponse.json()[1:])
# Add a "year" column
df["year"] = year
# Rename the variable columns to the variable codes of the final year.
i = 0 # initialize a counter
# Loop through the variable codes and rename their columns in the dataframe.
for v in variablecodes_final:
# year_vars_list is the list of variable codes we are getting for the current loop year
# This changes the column name in the results to the corresponding variable code for the
# final year.
df.rename(columns = {year_vars_list[i]: v}, inplace = True)
i = i + 1
# Add this dataframe to a list of data frames that we will later concatenate into one data frame.
# Assumes that dfs[] has already been initialized by the calling code.
dfs.append(df)
# test code - delete cell when live
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP05"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
variables = {'2014': ["DP05_0060PE", "DP05_0060PEA"], '2015': ["DP05_0060PE", "DP05_0060PEA"], \
'2016': ["DP05_0060PE", "DP05_0060PEA"], '2017': ["DP05_0065PE", "DP05_0065PEA"]}
years = ["2014","2015","2016","2017"]
# Call the function that retrieves the data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs.
# 2018 data not available yet for any dataset.
# Some datasets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
df_detractdata
| NAME | DP05_0065PE | DP05_0065PEA | state | county | tract | year | |
|---|---|---|---|---|---|---|---|
| 0 | Census Tract 422.02, Kent County, Delaware | 18.8 | None | 10 | 001 | 042202 | 2014 |
| 1 | Census Tract 428, Kent County, Delaware | 21.3 | None | 10 | 001 | 042800 | 2014 |
| 2 | Census Tract 431, Kent County, Delaware | 7.5 | None | 10 | 001 | 043100 | 2014 |
| 3 | Census Tract 402.01, Kent County, Delaware | 25.3 | None | 10 | 001 | 040201 | 2014 |
| 4 | Census Tract 416, Kent County, Delaware | 20.7 | None | 10 | 001 | 041600 | 2014 |
| 5 | Census Tract 421, Kent County, Delaware | 11.5 | None | 10 | 001 | 042100 | 2014 |
| 6 | Census Tract 430, Kent County, Delaware | 22.5 | None | 10 | 001 | 043000 | 2014 |
| 7 | Census Tract 417.02, Kent County, Delaware | 32.0 | None | 10 | 001 | 041702 | 2014 |
| 8 | Census Tract 402.02, Kent County, Delaware | 28.5 | None | 10 | 001 | 040202 | 2014 |
| 9 | Census Tract 402.03, Kent County, Delaware | 27.2 | None | 10 | 001 | 040203 | 2014 |
| 10 | Census Tract 409, Kent County, Delaware | 35.6 | None | 10 | 001 | 040900 | 2014 |
| 11 | Census Tract 413, Kent County, Delaware | 60.1 | None | 10 | 001 | 041300 | 2014 |
| 12 | Census Tract 417.01, Kent County, Delaware | 17.2 | None | 10 | 001 | 041701 | 2014 |
| 13 | Census Tract 420, Kent County, Delaware | 2.6 | None | 10 | 001 | 042000 | 2014 |
| 14 | Census Tract 429, Kent County, Delaware | 13.8 | None | 10 | 001 | 042900 | 2014 |
| 15 | Census Tract 407, Kent County, Delaware | 50.5 | None | 10 | 001 | 040700 | 2014 |
| 16 | Census Tract 414, Kent County, Delaware | 46.2 | None | 10 | 001 | 041400 | 2014 |
| 17 | Census Tract 415, Kent County, Delaware | 39.6 | None | 10 | 001 | 041500 | 2014 |
| 18 | Census Tract 418.01, Kent County, Delaware | 27.4 | None | 10 | 001 | 041801 | 2014 |
| 19 | Census Tract 418.02, Kent County, Delaware | 20.3 | None | 10 | 001 | 041802 | 2014 |
| 20 | Census Tract 401, Kent County, Delaware | 9.1 | None | 10 | 001 | 040100 | 2014 |
| 21 | Census Tract 411, Kent County, Delaware | 13.7 | None | 10 | 001 | 041100 | 2014 |
| 22 | Census Tract 412, Kent County, Delaware | 35.4 | None | 10 | 001 | 041200 | 2014 |
| 23 | Census Tract 419, Kent County, Delaware | 13.6 | None | 10 | 001 | 041900 | 2014 |
| 24 | Census Tract 422.01, Kent County, Delaware | 29.3 | None | 10 | 001 | 042201 | 2014 |
| 25 | Census Tract 425, Kent County, Delaware | 35.0 | None | 10 | 001 | 042500 | 2014 |
| 26 | Census Tract 410, Kent County, Delaware | 36.9 | None | 10 | 001 | 041000 | 2014 |
| 27 | Census Tract 405.01, Kent County, Delaware | 47.1 | None | 10 | 001 | 040501 | 2014 |
| 28 | Census Tract 405.02, Kent County, Delaware | 35.3 | None | 10 | 001 | 040502 | 2014 |
| 29 | Census Tract 432.02, Kent County, Delaware | 9.0 | None | 10 | 001 | 043202 | 2014 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 24 | Census Tract 519, Sussex County, Delaware | 13.5 | None | 10 | 005 | 051900 | 2017 |
| 25 | Census Tract 506.02, Sussex County, Delaware | 20.9 | None | 10 | 005 | 050602 | 2017 |
| 26 | Census Tract 518.01, Sussex County, Delaware | 10.2 | None | 10 | 005 | 051801 | 2017 |
| 27 | Census Tract 503.02, Sussex County, Delaware | 13.2 | None | 10 | 005 | 050302 | 2017 |
| 28 | Census Tract 507.01, Sussex County, Delaware | 21.0 | None | 10 | 005 | 050701 | 2017 |
| 29 | Census Tract 513.01, Sussex County, Delaware | 5.8 | None | 10 | 005 | 051301 | 2017 |
| 30 | Census Tract 513.02, Sussex County, Delaware | 8.0 | None | 10 | 005 | 051302 | 2017 |
| 31 | Census Tract 517.01, Sussex County, Delaware | 3.5 | None | 10 | 005 | 051701 | 2017 |
| 32 | Census Tract 518.02, Sussex County, Delaware | 46.4 | None | 10 | 005 | 051802 | 2017 |
| 33 | Census Tract 501.03, Sussex County, Delaware | 5.6 | None | 10 | 005 | 050103 | 2017 |
| 34 | Census Tract 502, Sussex County, Delaware | 30.7 | None | 10 | 005 | 050200 | 2017 |
| 35 | Census Tract 510.03, Sussex County, Delaware | 10.2 | None | 10 | 005 | 051003 | 2017 |
| 36 | Census Tract 508.02, Sussex County, Delaware | 9.7 | None | 10 | 005 | 050802 | 2017 |
| 37 | Census Tract 514, Sussex County, Delaware | 17.1 | None | 10 | 005 | 051400 | 2017 |
| 38 | Census Tract 508.01, Sussex County, Delaware | 12.0 | None | 10 | 005 | 050801 | 2017 |
| 39 | Census Tract 501.04, Sussex County, Delaware | 10.1 | None | 10 | 005 | 050104 | 2017 |
| 40 | Census Tract 501.05, Sussex County, Delaware | 27.2 | None | 10 | 005 | 050105 | 2017 |
| 41 | Census Tract 504.05, Sussex County, Delaware | 17.0 | None | 10 | 005 | 050405 | 2017 |
| 42 | Census Tract 504.06, Sussex County, Delaware | 39.1 | None | 10 | 005 | 050406 | 2017 |
| 43 | Census Tract 504.07, Sussex County, Delaware | 32.2 | None | 10 | 005 | 050407 | 2017 |
| 44 | Census Tract 504.08, Sussex County, Delaware | 29.4 | None | 10 | 005 | 050408 | 2017 |
| 45 | Census Tract 505.03, Sussex County, Delaware | 16.2 | None | 10 | 005 | 050503 | 2017 |
| 46 | Census Tract 505.04, Sussex County, Delaware | 17.6 | None | 10 | 005 | 050504 | 2017 |
| 47 | Census Tract 507.03, Sussex County, Delaware | 14.3 | None | 10 | 005 | 050703 | 2017 |
| 48 | Census Tract 509.01, Sussex County, Delaware | 3.7 | None | 10 | 005 | 050901 | 2017 |
| 49 | Census Tract 507.04, Sussex County, Delaware | 8.1 | None | 10 | 005 | 050704 | 2017 |
| 50 | Census Tract 507.05, Sussex County, Delaware | 9.7 | None | 10 | 005 | 050705 | 2017 |
| 51 | Census Tract 507.06, Sussex County, Delaware | 0.0 | None | 10 | 005 | 050706 | 2017 |
| 52 | Census Tract 509.02, Sussex County, Delaware | 7.0 | None | 10 | 005 | 050902 | 2017 |
| 53 | Census Tract 510.04, Sussex County, Delaware | 6.4 | None | 10 | 005 | 051004 | 2017 |
872 rows × 7 columns
Percent adults age 25 yrs or older with less than high school graduation¶
ACS 5-year table S1501. Variable S1501_C02_014E Estimate!!Percent!!Population 25 years and over!!Percent high school graduate or higher.
Use S1501_C01_014E for 2014.
The results are for percent with high school or higher. Will need to subtract from 100 to get less than high school.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "S1501"
variables = {'2014': ["S1501_C01_014E"], '2015': ["S1501_C02_014E"], '2016': ["S1501_C02_014E"], '2017': ["S1501_C02_014E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
df_detractdata
| NAME | S1501_C02_014E | state | county | tract | year | |
|---|---|---|---|---|---|---|
| 0 | Census Tract 422.02, Kent County, Delaware | 90.6 | 10 | 001 | 042202 | 2014 |
| 1 | Census Tract 428, Kent County, Delaware | 87.0 | 10 | 001 | 042800 | 2014 |
| 2 | Census Tract 431, Kent County, Delaware | 86.6 | 10 | 001 | 043100 | 2014 |
| 3 | Census Tract 402.01, Kent County, Delaware | 87.0 | 10 | 001 | 040201 | 2014 |
| 4 | Census Tract 416, Kent County, Delaware | 91.8 | 10 | 001 | 041600 | 2014 |
| 5 | Census Tract 421, Kent County, Delaware | 82.3 | 10 | 001 | 042100 | 2014 |
| 6 | Census Tract 430, Kent County, Delaware | 84.4 | 10 | 001 | 043000 | 2014 |
| 7 | Census Tract 417.02, Kent County, Delaware | 93.8 | 10 | 001 | 041702 | 2014 |
| 8 | Census Tract 402.02, Kent County, Delaware | 88.8 | 10 | 001 | 040202 | 2014 |
| 9 | Census Tract 402.03, Kent County, Delaware | 83.1 | 10 | 001 | 040203 | 2014 |
| 10 | Census Tract 409, Kent County, Delaware | 80.7 | 10 | 001 | 040900 | 2014 |
| 11 | Census Tract 413, Kent County, Delaware | 87.0 | 10 | 001 | 041300 | 2014 |
| 12 | Census Tract 417.01, Kent County, Delaware | 92.4 | 10 | 001 | 041701 | 2014 |
| 13 | Census Tract 420, Kent County, Delaware | 75.1 | 10 | 001 | 042000 | 2014 |
| 14 | Census Tract 429, Kent County, Delaware | 84.5 | 10 | 001 | 042900 | 2014 |
| 15 | Census Tract 407, Kent County, Delaware | 89.7 | 10 | 001 | 040700 | 2014 |
| 16 | Census Tract 414, Kent County, Delaware | 84.9 | 10 | 001 | 041400 | 2014 |
| 17 | Census Tract 415, Kent County, Delaware | 92.0 | 10 | 001 | 041500 | 2014 |
| 18 | Census Tract 418.01, Kent County, Delaware | 84.8 | 10 | 001 | 041801 | 2014 |
| 19 | Census Tract 418.02, Kent County, Delaware | 81.0 | 10 | 001 | 041802 | 2014 |
| 20 | Census Tract 401, Kent County, Delaware | 79.0 | 10 | 001 | 040100 | 2014 |
| 21 | Census Tract 411, Kent County, Delaware | 97.5 | 10 | 001 | 041100 | 2014 |
| 22 | Census Tract 412, Kent County, Delaware | 85.4 | 10 | 001 | 041200 | 2014 |
| 23 | Census Tract 419, Kent County, Delaware | 80.6 | 10 | 001 | 041900 | 2014 |
| 24 | Census Tract 422.01, Kent County, Delaware | 92.8 | 10 | 001 | 042201 | 2014 |
| 25 | Census Tract 425, Kent County, Delaware | 63.5 | 10 | 001 | 042500 | 2014 |
| 26 | Census Tract 410, Kent County, Delaware | 84.9 | 10 | 001 | 041000 | 2014 |
| 27 | Census Tract 405.01, Kent County, Delaware | 89.1 | 10 | 001 | 040501 | 2014 |
| 28 | Census Tract 405.02, Kent County, Delaware | 87.3 | 10 | 001 | 040502 | 2014 |
| 29 | Census Tract 432.02, Kent County, Delaware | 79.3 | 10 | 001 | 043202 | 2014 |
| ... | ... | ... | ... | ... | ... | ... |
| 24 | Census Tract 519, Sussex County, Delaware | 85.3 | 10 | 005 | 051900 | 2017 |
| 25 | Census Tract 506.02, Sussex County, Delaware | 83.6 | 10 | 005 | 050602 | 2017 |
| 26 | Census Tract 518.01, Sussex County, Delaware | 84.5 | 10 | 005 | 051801 | 2017 |
| 27 | Census Tract 503.02, Sussex County, Delaware | 78.7 | 10 | 005 | 050302 | 2017 |
| 28 | Census Tract 507.01, Sussex County, Delaware | 85.2 | 10 | 005 | 050701 | 2017 |
| 29 | Census Tract 513.01, Sussex County, Delaware | 93.1 | 10 | 005 | 051301 | 2017 |
| 30 | Census Tract 513.02, Sussex County, Delaware | 87.2 | 10 | 005 | 051302 | 2017 |
| 31 | Census Tract 517.01, Sussex County, Delaware | 84.5 | 10 | 005 | 051701 | 2017 |
| 32 | Census Tract 518.02, Sussex County, Delaware | 82.8 | 10 | 005 | 051802 | 2017 |
| 33 | Census Tract 501.03, Sussex County, Delaware | 90.5 | 10 | 005 | 050103 | 2017 |
| 34 | Census Tract 502, Sussex County, Delaware | 73.5 | 10 | 005 | 050200 | 2017 |
| 35 | Census Tract 510.03, Sussex County, Delaware | 94.0 | 10 | 005 | 051003 | 2017 |
| 36 | Census Tract 508.02, Sussex County, Delaware | 91.0 | 10 | 005 | 050802 | 2017 |
| 37 | Census Tract 514, Sussex County, Delaware | 77.7 | 10 | 005 | 051400 | 2017 |
| 38 | Census Tract 508.01, Sussex County, Delaware | 90.7 | 10 | 005 | 050801 | 2017 |
| 39 | Census Tract 501.04, Sussex County, Delaware | 83.0 | 10 | 005 | 050104 | 2017 |
| 40 | Census Tract 501.05, Sussex County, Delaware | 86.6 | 10 | 005 | 050105 | 2017 |
| 41 | Census Tract 504.05, Sussex County, Delaware | 77.4 | 10 | 005 | 050405 | 2017 |
| 42 | Census Tract 504.06, Sussex County, Delaware | 86.3 | 10 | 005 | 050406 | 2017 |
| 43 | Census Tract 504.07, Sussex County, Delaware | 81.0 | 10 | 005 | 050407 | 2017 |
| 44 | Census Tract 504.08, Sussex County, Delaware | 78.6 | 10 | 005 | 050408 | 2017 |
| 45 | Census Tract 505.03, Sussex County, Delaware | 70.0 | 10 | 005 | 050503 | 2017 |
| 46 | Census Tract 505.04, Sussex County, Delaware | 76.5 | 10 | 005 | 050504 | 2017 |
| 47 | Census Tract 507.03, Sussex County, Delaware | 78.6 | 10 | 005 | 050703 | 2017 |
| 48 | Census Tract 509.01, Sussex County, Delaware | 98.2 | 10 | 005 | 050901 | 2017 |
| 49 | Census Tract 507.04, Sussex County, Delaware | 94.4 | 10 | 005 | 050704 | 2017 |
| 50 | Census Tract 507.05, Sussex County, Delaware | 85.4 | 10 | 005 | 050705 | 2017 |
| 51 | Census Tract 507.06, Sussex County, Delaware | 86.9 | 10 | 005 | 050706 | 2017 |
| 52 | Census Tract 509.02, Sussex County, Delaware | 93.4 | 10 | 005 | 050902 | 2017 |
| 53 | Census Tract 510.04, Sussex County, Delaware | 92.1 | 10 | 005 | 051004 | 2017 |
872 rows × 6 columns
Percent males who are unemployed¶
ACS 5-year table S2301. S2301_C04_022E Estimate!!Unemployment rate!!Population 20 to 64 years!!SEX!!Male
Use S2301_C04_020E for 2014.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "S2301"
variables = {'2014': ["S2301_C04_020M"],'2015': ["S2301_C04_022M"], '2016': ["S2301_C04_022M"], '2017': ["S2301_C04_022E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent households with income below the poverty level¶
ACS 5-year table DP03. DP03_0119PE Percent Estimate!!PERCENTAGE OF FAMILIES AND PEOPLE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL!!All families
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP03"
variables = {'2014': ["DP03_0119PE"],'2015': ["DP03_0119PE"], '2016': ["DP03_0119PE"], '2017': ["DP03_0119PE"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent households receiving public Assistance¶
ACS 5-year table DP03. DP03_0072PE Percent Estimate!!INCOME AND BENEFITS (IN 2017 INFLATION-ADJUSTED DOLLARS)!!Total households!!With cash public assistance income
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP03"
variables = {'2014': ["DP03_0072PE"],'2015': ["DP03_0072PE"], '2016': ["DP03_0072PE"], '2017': ["DP03_0072PE"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent households with children in which the head of household is female¶
ACS 5-year table B11004. B11004_016E Estimate!!Total!!Other family!!Female householder no husband present!!With related children of the householder under 18 years (numerator) ACS 5-year table B11004. B11004_001E Estimate!!Total (total number of families will be divisor)
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "B11004"
variables = {'2014': ["B11004_016E","B11004_001E"],'2015': ["B11004_016E","B11004_001E"], \
'2016': ["B11004_016E","B11004_001E"], '2017': ["B11004_016E","B11004_001E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Median household income¶
ACS 5-year table DP03. DP03_0062E Estimate!!INCOME AND BENEFITS (IN 2017 INFLATION-ADJUSTED DOLLARS)!!Total households!!Median household income (dollars)
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP03"
variables = {'2014': ["DP03_0062E"],'2015': ["DP03_0062E"], \
'2016': ["DP03_0062E"], '2017': ["DP03_0062E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent unemployed individual (unemployment rate)¶
ACS 5-year table S2301. S2301_C04_001E Estimate!!Unemployment rate!!Population 16 years and over
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "S2301"
variables = {'2014': ["S2301_C04_001E"],'2015': ["S2301_C04_001E"], \
'2016': ["S2301_C04_001E"], '2017': ["S2301_C04_001E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent households with no cars¶
ACS 5-year table S2504. S2504_C02_027E Estimate!!Percent occupied housing units!!Occupied housing units!!VEHICLES AVAILABLE!!No vehicle available
Use S2504_C01_026E for 2014.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "S2504"
variables = {'2014': ["S2504_C01_026E"],'2015': ["S2504_C01_027E"], \
'2016': ["S2504_C02_027E"], '2017': ["S2504_C02_027E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent crowded housing¶
ACS 5-year table S2501. S2501_C02_006E Estimate!!Percent occupied housing units!!Occupied housing units!!OCCUPANTS PER ROOM!!1.00 or less occupants per room.
Subtract from 100 to get percent of households where there is more than one person per room. This is a standard of overcrowding described by https://www.census.gov/content/dam/Census/programs-surveys/ahs/publications/Measuring_Overcrowding_in_Hsg.pdf
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "S2501"
variables = {'2014': ["S2501_C02_006E"],'2015': ["S2501_C02_006E"], \
'2016': ["S2501_C02_006E"], '2017': ["S2501_C02_006E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent renters¶
ACS 5-year table B25003. B25003_003E Estimate!!Total!!Renter occupied (numerator)
B25003_001E Estimate!!Total (total number of housing units will be the divisor)
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "B25003"
variables = {'2014': ["B25003_003E", "B25003_001E"],'2015': ["B25003_003E", "B25003_001E"], \
'2016': ["B25003_003E", "B25003_001E"], '2017': ["B25003_003E", "B25003_001E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent males not in management & professional occupations¶
ACS 5-year table S2401. S2401_C03_002E Estimate!!Percent Male!!Civilian employed population 16 years and over!!Management business science and arts occupations. (Only available for 2017, 2016 and 2015)
Subtract this value from 100 to get the percent not in management.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "S2401"
variables = {'2015': ["S2401_C03_002E"], \
'2016': ["S2401_C03_002E"], '2017': ["S2401_C03_002E"]}
years = ["2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent earning less than 30K per year¶
(Changed from less than 30k/year 2000 U.S Census data)
ACS 5-year table S2001. S2001_C02_007E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$25,000to$34,999.
ACS 5-year table S2001. S2001_C02_006E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$15,000 to $24,999.
ACS 5-year table S2001. S2001_C02_005E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$10,000 to $14,999.
ACS 5-year table S2001. S2001_C02_004E Estimate!!Percent!!Population 16 years and over with earnings
!!FULL-TIME YEAR-ROUND WORKERS WITH EARNINGS!!$1 to $9,999 or less.
Add these up to get percent earning less than $35,000. Could not find a $30,000 cut off in the census data.
Use "C01" versions of the variables for 2014.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "S2001"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
variables = {'2014': ["S2001_C01_004E", "S2001_C01_005E", "S2001_C01_006E", "S2001_C01_007E"], \
'2015': ["S2001_C01_004E", "S2001_C01_005E", "S2001_C01_006E", "S2001_C01_007E"], \
'2016': ["S2001_C01_004E", "S2001_C01_005E", "S2001_C01_006E", "S2001_C01_007E"], \
'2017': ["S2001_C02_004E", "S2001_C02_005E", "S2001_C02_006E", "S2001_C02_007E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
df_detractdata
| NAME | S2001_C02_004E | S2001_C02_005E | S2001_C02_006E | S2001_C02_007E | state | county | tract | year | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | Census Tract 422.02, Kent County, Delaware | 4.6 | 2.9 | 11.6 | 11.8 | 10 | 001 | 042202 | 2014 |
| 1 | Census Tract 428, Kent County, Delaware | 3.7 | 2.3 | 12.9 | 14.4 | 10 | 001 | 042800 | 2014 |
| 2 | Census Tract 431, Kent County, Delaware | 0.8 | 3.2 | 22.1 | 15.8 | 10 | 001 | 043100 | 2014 |
| 3 | Census Tract 402.01, Kent County, Delaware | 1.1 | 7.9 | 11.3 | 19.1 | 10 | 001 | 040201 | 2014 |
| 4 | Census Tract 416, Kent County, Delaware | 0.9 | 0.0 | 10.0 | 17.4 | 10 | 001 | 041600 | 2014 |
| 5 | Census Tract 421, Kent County, Delaware | 2.0 | 5.2 | 17.4 | 15.8 | 10 | 001 | 042100 | 2014 |
| 6 | Census Tract 430, Kent County, Delaware | 1.1 | 2.8 | 17.7 | 20.7 | 10 | 001 | 043000 | 2014 |
| 7 | Census Tract 417.02, Kent County, Delaware | 0.0 | 1.9 | 7.0 | 16.2 | 10 | 001 | 041702 | 2014 |
| 8 | Census Tract 402.02, Kent County, Delaware | 0.4 | 0.9 | 10.6 | 7.8 | 10 | 001 | 040202 | 2014 |
| 9 | Census Tract 402.03, Kent County, Delaware | 1.2 | 1.1 | 7.7 | 22.1 | 10 | 001 | 040203 | 2014 |
| 10 | Census Tract 409, Kent County, Delaware | 2.2 | 5.5 | 12.2 | 5.1 | 10 | 001 | 040900 | 2014 |
| 11 | Census Tract 413, Kent County, Delaware | 1.8 | 1.6 | 13.1 | 24.7 | 10 | 001 | 041300 | 2014 |
| 12 | Census Tract 417.01, Kent County, Delaware | 2.6 | 4.9 | 10.5 | 19.4 | 10 | 001 | 041701 | 2014 |
| 13 | Census Tract 420, Kent County, Delaware | 8.4 | 3.7 | 13.3 | 31.8 | 10 | 001 | 042000 | 2014 |
| 14 | Census Tract 429, Kent County, Delaware | 0.3 | 0.8 | 15.3 | 18.6 | 10 | 001 | 042900 | 2014 |
| 15 | Census Tract 407, Kent County, Delaware | 4.8 | 3.3 | 10.6 | 14.9 | 10 | 001 | 040700 | 2014 |
| 16 | Census Tract 414, Kent County, Delaware | 0.0 | 11.3 | 23.9 | 23.6 | 10 | 001 | 041400 | 2014 |
| 17 | Census Tract 415, Kent County, Delaware | 0.0 | 7.1 | 10.4 | 15.0 | 10 | 001 | 041500 | 2014 |
| 18 | Census Tract 418.01, Kent County, Delaware | 1.7 | 2.4 | 9.5 | 22.7 | 10 | 001 | 041801 | 2014 |
| 19 | Census Tract 418.02, Kent County, Delaware | 1.2 | 3.0 | 17.8 | 11.7 | 10 | 001 | 041802 | 2014 |
| 20 | Census Tract 401, Kent County, Delaware | 0.1 | 2.0 | 10.0 | 26.5 | 10 | 001 | 040100 | 2014 |
| 21 | Census Tract 411, Kent County, Delaware | 2.4 | 2.9 | 23.1 | 15.8 | 10 | 001 | 041100 | 2014 |
| 22 | Census Tract 412, Kent County, Delaware | 0.9 | 5.0 | 20.2 | 14.0 | 10 | 001 | 041200 | 2014 |
| 23 | Census Tract 419, Kent County, Delaware | 0.0 | 4.9 | 12.4 | 13.8 | 10 | 001 | 041900 | 2014 |
| 24 | Census Tract 422.01, Kent County, Delaware | 0.7 | 3.8 | 6.6 | 23.5 | 10 | 001 | 042201 | 2014 |
| 25 | Census Tract 425, Kent County, Delaware | 1.1 | 1.8 | 26.4 | 37.0 | 10 | 001 | 042500 | 2014 |
| 26 | Census Tract 410, Kent County, Delaware | 1.5 | 5.5 | 10.0 | 24.1 | 10 | 001 | 041000 | 2014 |
| 27 | Census Tract 405.01, Kent County, Delaware | 2.3 | 0.0 | 11.4 | 22.6 | 10 | 001 | 040501 | 2014 |
| 28 | Census Tract 405.02, Kent County, Delaware | 0.0 | 0.8 | 6.5 | 34.5 | 10 | 001 | 040502 | 2014 |
| 29 | Census Tract 432.02, Kent County, Delaware | 0.7 | 3.6 | 15.2 | 18.0 | 10 | 001 | 043202 | 2014 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 24 | Census Tract 519, Sussex County, Delaware | 0.6 | 2.2 | 17.1 | 15.7 | 10 | 005 | 051900 | 2017 |
| 25 | Census Tract 506.02, Sussex County, Delaware | 0.8 | 2.7 | 21.0 | 15.2 | 10 | 005 | 050602 | 2017 |
| 26 | Census Tract 518.01, Sussex County, Delaware | 3.2 | 1.6 | 15.6 | 19.9 | 10 | 005 | 051801 | 2017 |
| 27 | Census Tract 503.02, Sussex County, Delaware | 0.0 | 2.6 | 13.9 | 27.1 | 10 | 005 | 050302 | 2017 |
| 28 | Census Tract 507.01, Sussex County, Delaware | 3.7 | 3.4 | 13.2 | 25.8 | 10 | 005 | 050701 | 2017 |
| 29 | Census Tract 513.01, Sussex County, Delaware | 0.3 | 2.9 | 5.0 | 16.4 | 10 | 005 | 051301 | 2017 |
| 30 | Census Tract 513.02, Sussex County, Delaware | 0.6 | 2.8 | 18.3 | 15.3 | 10 | 005 | 051302 | 2017 |
| 31 | Census Tract 517.01, Sussex County, Delaware | 0.6 | 3.4 | 10.5 | 26.4 | 10 | 005 | 051701 | 2017 |
| 32 | Census Tract 518.02, Sussex County, Delaware | 1.4 | 3.9 | 27.0 | 20.8 | 10 | 005 | 051802 | 2017 |
| 33 | Census Tract 501.03, Sussex County, Delaware | 0.9 | 1.6 | 12.2 | 10.9 | 10 | 005 | 050103 | 2017 |
| 34 | Census Tract 502, Sussex County, Delaware | 2.0 | 5.0 | 17.6 | 12.3 | 10 | 005 | 050200 | 2017 |
| 35 | Census Tract 510.03, Sussex County, Delaware | 3.0 | 4.3 | 9.1 | 10.6 | 10 | 005 | 051003 | 2017 |
| 36 | Census Tract 508.02, Sussex County, Delaware | 0.5 | 2.4 | 9.7 | 12.4 | 10 | 005 | 050802 | 2017 |
| 37 | Census Tract 514, Sussex County, Delaware | 4.6 | 5.8 | 12.4 | 19.8 | 10 | 005 | 051400 | 2017 |
| 38 | Census Tract 508.01, Sussex County, Delaware | 1.3 | 1.9 | 13.5 | 22.2 | 10 | 005 | 050801 | 2017 |
| 39 | Census Tract 501.04, Sussex County, Delaware | 0.9 | 2.1 | 16.5 | 14.0 | 10 | 005 | 050104 | 2017 |
| 40 | Census Tract 501.05, Sussex County, Delaware | 0.5 | 2.5 | 21.3 | 17.9 | 10 | 005 | 050105 | 2017 |
| 41 | Census Tract 504.05, Sussex County, Delaware | 0.0 | 2.8 | 11.4 | 30.5 | 10 | 005 | 050405 | 2017 |
| 42 | Census Tract 504.06, Sussex County, Delaware | 1.6 | 2.9 | 17.1 | 29.8 | 10 | 005 | 050406 | 2017 |
| 43 | Census Tract 504.07, Sussex County, Delaware | 1.5 | 3.2 | 14.9 | 19.2 | 10 | 005 | 050407 | 2017 |
| 44 | Census Tract 504.08, Sussex County, Delaware | 3.7 | 3.2 | 15.3 | 19.8 | 10 | 005 | 050408 | 2017 |
| 45 | Census Tract 505.03, Sussex County, Delaware | 7.1 | 2.9 | 31.7 | 20.4 | 10 | 005 | 050503 | 2017 |
| 46 | Census Tract 505.04, Sussex County, Delaware | 0.2 | 1.6 | 19.9 | 14.7 | 10 | 005 | 050504 | 2017 |
| 47 | Census Tract 507.03, Sussex County, Delaware | 0.0 | 5.4 | 13.0 | 20.2 | 10 | 005 | 050703 | 2017 |
| 48 | Census Tract 509.01, Sussex County, Delaware | 0.9 | 0.0 | 9.2 | 6.3 | 10 | 005 | 050901 | 2017 |
| 49 | Census Tract 507.04, Sussex County, Delaware | 1.4 | 2.9 | 17.8 | 10.6 | 10 | 005 | 050704 | 2017 |
| 50 | Census Tract 507.05, Sussex County, Delaware | 0.0 | 0.0 | 27.5 | 23.5 | 10 | 005 | 050705 | 2017 |
| 51 | Census Tract 507.06, Sussex County, Delaware | 0.0 | 0.0 | 20.1 | 13.0 | 10 | 005 | 050706 | 2017 |
| 52 | Census Tract 509.02, Sussex County, Delaware | 4.3 | 3.5 | 3.2 | 12.7 | 10 | 005 | 050902 | 2017 |
| 53 | Census Tract 510.04, Sussex County, Delaware | 0.9 | 0.9 | 13.3 | 6.8 | 10 | 005 | 051004 | 2017 |
872 rows × 9 columns
Percent less than high school education¶
ACS 5-year table S1501. Variable S1501_C02_014E Estimate!!Percent!!Population 25 years and over!!Percent high school graduate or higher. Subtract the values from 100 to get desired value.
This was previously collected.
Percent of residents under the age of 16 yrs¶
ACS 5-year table DP05. DP05_0020PE Percent Estimate!!SEX AND AGE!!Total population!!16 years and over. This grouping was not available before 2017. (Subtract this value from 100 to get the percent under age 16.)
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP05"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
variables = {'2017': ["DP05_0020PE"]}
years = ["2017"]
# Call the function that retrieves the data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs.
# 2018 data not available yet for any dataset.
# Some datasets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Percent Black, White & Other residents¶
ACS 5-year table DP05 DP05_0038PE. Percent Estimate!!RACE!!Total population!!One race!!Black or African American. DP05_0037PE. Percent Estimate!!RACE!!Total population!!One race!!White. Other = 100 - (DP05_0065PE + DP05_0064PE)
For Black, use DP05_0033PE for 2014, 2015 and 2016. Use DP05_0038PE for 2017. For White, use DP05_0032PE for 2014, 2015 and 2016. Use DP05_0037PE for 2017.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP05"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
variables = {'2014': ["DP05_0033PE", "DP05_0032PE"], '2015': ["DP05_0033PE", "DP05_0032PE"],
'2016': ["DP05_0033PE", "DP05_0032PE"], '2017': ["DP05_0038PE", "DP05_0037PE"]}
years = ["2014","2015","2016","2017"]
# Call the function that retrieves the data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs.
# 2018 data not available yet for any dataset.
# Some datasets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
COMPUTERS AND INTERNET USE¶
ACS 5-year table DP02. DP02_0151PE Percent Estimate!!COMPUTERS AND INTERNET USE!!Total households!!With a computer.
ACS 5-year table DP02. DP02_0152PE Percent Estimate!!COMPUTERS AND INTERNET USE!!Total households!!With a broadband Internet subscription.
Not available before 2017.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP02"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
variables = {'2017': ["DP02_0151PE", "DP02_0152PE"]}
years = ["2017"]
# Call the function that retrieves the data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
GINI Index of income Inequality¶
ACS 5-year table B19083. B19083_001E Estimate!!Gini Index
Use B19083_001E for all years 2014 to 2017.
The GINI index is an indicator of income equality. It ranges from 0 to 1. 0 indicates perfect equality of income among the population; everyone has the same income. A value of 1 indicates that one person has all the income. So, with GINI values, lower values are an indication of a more equitable income distribution than higher values.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "B19083"
variables = {'2014': ["B19083_001E"],'2015': ["B19083_001E"], \
'2016': ["B19083_001E"], '2017': ["B19083_001E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
Black and White Populations¶
ACS 5-year table B02001. B02001_002E Estimate!!Total!!White alone. B02001_003E Estimate!!Total!!Black or African American alone.
This builds a table of the White and Black population of each census tract in Delaware.
Use variable codes B02001_002E and B02001_003E for all years between 2014 and 2017.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "B02001"
variables = {'2014': ["B02001_002E", "B02001_003E"],'2015': ["B02001_002E", "B02001_003E"], \
'2016': ["B02001_002E", "B02001_003E"], '2017': ["B02001_002E", "B02001_003E"]}
years = ["2014","2015","2016","2017"]
fn_delaware_tract_data(table, variables, years)
# 2018 data not available yet for any dataset.
# Some datsets do not have 2014 data.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
### CODE TESTING AREA - can delete
### This verifies that a base url and predicates can be used to bring back census block, total, black and white population.
# Base URL
# Components of the base URL go into variables.
HOST = "https://api.census.gov/data"
year = "2010"
dataset = "dec/sf1" # SF1 contains the data compiled from the questions asked of all people and about every housing unit
# Code to join the base url components
base_url = "/".join([HOST, year, dataset])
# This is the base URL: "https://api.census.gov/data/2010/sf1"
# TIP: Add "/groups.html" to the end of the Base URL to view a list of groups (aka tables) in the dataset.
# https://api.census.gov/data/2010/dec/sf1/groups.html
# Going to this link in an Internet browser will display the available tables that are based on the Summary File 1 (SF1) dataset from the 2010 Decennial Census.
# Each table in the list has a link to a list of variables for the table.
# Form the predicates dictionary
predicates = {} # initialize empty dictionary
get_vars = ["NAME", "P008001", "P008003", "P008004"] # Name of geographic area, total population, white pop., black pop.
predicates["get"] = ",".join(get_vars)
predicates["for"] = "block:*" # Census Blocks are the desired geography. "*" means all blocks.
predicates["in"] = "state:10+county:003" # Delaware, New Castle County only
# Make the request. Results get loaded into the variable "myresponse2".
myresponse3 = requests.get(base_url, params = predicates)
# Display the result
print(myresponse3.head)
--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-24-f2a4f0e2302d> in <module> 28 29 # Display the result ---> 30 print(myresponse3.head) AttributeError: 'Response' object has no attribute 'head'
# Get the census block populations of black and white for Delware
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "P8" ## SF1 table containing Race data for the Decennial Census
# Put the variable codes into a dictionary using one key per year.
variables = {'2010': ["P008001", "P008003", "P008004"]}
years = ["2010"]
# Call the function that retrieves the data from the census bureau.
# Note the use of 'block' for the 4th parameter to get census block level data.
fn_delaware_tract_data(table, variables, years, 'block') # This will populate dfs.
df_detractdata = pd.concat(dfs) ## This is the dataframe that needs to be saved as a SQL table
# See how many rows and columns are in the result
print (df_detractdata.shape)
Percent Latino Population¶
ACS 5-year table DP05. DP05_0071PE Percent Estimate!!HISPANIC OR LATINO AND RACE!!Total population!!Hispanic or Latino (of any race). ACS 5-year table DP05. DP05_0076PE Percent Estimate!!HISPANIC OR LATINO AND RACE!!Total population!!Not Hispanic or Latino.
For Latino, use DP05_0065PE for 2014, 2015 and 2016. Use DP05_0071PE for 2017 For non-Latino, use DP05_0071PE for 2014, 2015 and 2016. Use DP05_0076PE for 2017
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP05"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
# variables = {'2014': ["DP05_0066PE", "DP05_0071PE"], '2015': ["DP05_0066PE", "DP05_0071PE"],
# '2016': ["DP05_0066PE", "DP05_0071PE"], '2017': ["DP05_0071PE", "DP05_0076PE"]}
# years = ["2014","2015","2016","2017"]
## Because the variable DP05_0071PE means Latino for year 2017 but non-Latino for years 2014, 2015, 2016 the concat
## will throw an error:
## ValueError: Plan shapes are not aligned. To get around this you will needed to do separate batches of output
## by year and merge them in another step.
## The only way around this is to run two separate batches of output and merge the data manually into one SQL table
#### Get the data for 2014-2016
variables = {'2014': ["DP05_0066PE", "DP05_0071PE"], '2015': ["DP05_0066PE", "DP05_0071PE"],
'2016': ["DP05_0066PE", "DP05_0071PE"]}
years = ["2014","2015","2016"]
# Call the function that retrieves the data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs.
# Concatenate all the years data into one long dataframe
df_detractdata = pd.concat(dfs, sort = False) ## This is the dataframe for years 2014 to 2016
# Change the column headings for the data frame so they can be aligned with the next batch of data for 2017
df_detractdata = df_detractdata.rename(columns = {'DP05_0066PE':'DP05_0071PE', 'DP05_0071PE':'DP05_0076PE'})
#### Get the data for 2017
dfs = [df_detractdata] # Initialize dfs list of data frames with the 2014-2016 data
# Set variable IDs and year for 2017
variables = {'2017': ["DP05_0071PE", "DP05_0076PE"]}
years = ["2017"]
# Call function to retrieve 2017 data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs.
# Merge the 2017 data with the 2014-2016 data
df_detractdata = pd.concat(dfs, sort = False)
Percent Foreign Born¶
ACS 5-year table DP02. DP02_0092PE Percent Estimate!!PLACE OF BIRTH!!Total population!!Foreign born.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP02"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
variables = {'2014': ["DP02_0092PE"], '2015': ["DP02_0092PE"],
'2016': ["DP02_0092PE"], '2017': ["DP02_0092PE"]}
years = ["2014","2015","2016","2017"]
# Call the function that retrieves the data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs[].
# Concatenate all the years data into one long dataframe
df_detractdata = pd.concat(dfs, sort = False) ## This is the dataframe for years 2014 to 2016
Percent Disabled¶
ACS 5-year table DP02.
DP02_0073PE Percent Estimate!!DISABILITY STATUS OF THE CIVILIAN NONINSTITUTIONALIZED POPULATION!!Under 18 years!!With a disability.
DP02_0075PE Percent Estimate!!DISABILITY STATUS OF THE CIVILIAN NONINSTITUTIONALIZED POPULATION!!18 to 64 years!!With a disability.
DP02_0077PE Percent Estimate!!DISABILITY STATUS OF THE CIVILIAN NONINSTITUTIONALIZED POPULATION!!65 years and over!!With a disability.
dfs = [] # Initialize an empty list that will contain the data frames for all the years
### Get the census data and put it into dataframe ###
table = "DP02"
# Put the variable codes into a dictionary using one key per year. Note the codes changed over the years.
variables = {'2014': ["DP02_0073PE","DP02_0075PE","DP02_0077PE"], '2015': ["DP02_0073PE","DP02_0075PE","DP02_0077PE"],
'2016': ["DP02_0073PE","DP02_0075PE","DP02_0077PE"], '2017': ["DP02_0073PE","DP02_0075PE","DP02_0077PE"]}
years = ["2014","2015","2016","2017"]
# Call the function that retrieves the data from the census bureau.
fn_delaware_tract_data(table, variables, years) # This will populate dfs[].
# Concatenate all the years data into one long dataframe
df_detractdata = pd.concat(dfs, sort = False) ## This is the dataframe for years 2014 to 2016