In [1]:
import requests
import json
import pandas as pd

The data is collected by using API requests to the U.S. Census Bureau’s ACS 2023 5-Year datasets.

For Connecticut, ACS uses planning regions instead of counties. Hence, data for Connecticut should be collected separately at the town level (county subdivision) and town-county crosswalk file is used to map towns onto corresponding counties. Then, the town-level data is aggregated to retrieve county-level data.

Reference files:

  • data\ny_nj_ct_fips.csv: list of all 91 counties and their FIPS codes in the 3 states (NY, NJ, CT)
  • reference\ct_crosswalk\ct_town_crosswalk.csv: crosswalk file for mapping Connecticut's towns to counties based on FIPS codes.

Constructing API Request URLs¶

To construct the API request URLs, we first need to identify the URL, parameters for county-level/town-level data in selected states and variable codes.

URLs¶

A table ID starts with a letter indicating the type of table:

  • S - Subject Table. Ex: S0101
  • B - Detailed Table. Ex: B01001
  • DP - Data Profile Table. Ex: DP01

URLs for 3 types of table in ACS data:

  • Subject Tables: https://api.census.gov/data/2023/acs/acs5/subject
  • Detailed Tables: https://api.census.gov/data/2023/acs/acs5
  • Data Profile Tables: https://api.census.gov/data/2023/acs/acs5//profile

Parameters for county-level or town-level data in selected states¶

  • Parameter for selected states is the FIPS codes of the states (CT - 09, NY - 36, NJ - 34): in=state:09 or in=state:36,34. These parameters will return column state for the state FIPS Parameter for county level data: for=county:* will return column county for county FIPS
  • Parameter for town-level data: for=county%20subdivision:* will return column county and county subdivision

Variable codes¶

A variable code always starts with a table ID. Ex: variable S1701_C03_001E is in table S1701.

Table ID and variable labels are used to look up variable codes from the variable metadata documentation:

  • Subject Tables
  • Detailed Tables
  • Data Profile Tables

Structure of variable labels in the metadata documentation: Child column!!Parent column!!Parent label!!Child label 1!!Child label 2!!Child label 3!!

Table ID and variable labels can be found by browsing the desired variable on U.S. Census Bureau website.

My Local GIF

For a previous project, I built a Python script to help with looking up the variable codes for ACS datasets using metadata API (JSON format). Inputs include the table ID and components of variable label.

Example: to get variable code for poverty rate:

  • Inputs:
    Table ID: s1701
    Parent column: percent below poverty level
    Child column: estimate
    Parent label: Population for whom poverty status is determined
    Child label 1: Press Enter to skip
    Child label 2: Press Enter to skip
    Child label 3: Press Enter to skip
  • Outputs:
    Table: S1701
    Variable label: Estimate!!Percent below poverty level!!Population for whom poverty status is determined
    Variable code: S1701_C03_001E
In [2]:
# Run this line to find variable codes
#%run retrieve_variable_codes.py

Variables for NY & NJ¶

Collected Column Description Table Variable code
county County name all table NAME
total_pop Total population S0101 S0101_C01_001E
pcnt_65_plus The percentage of people aged 65 and over S0101 S0101_C02_030E
poverty_rate The percentage of people who are below poverty threshold S1701 S1701_C03_001E
pcnt_insured The percentage of people having health insurance S2701 S2701_C03_001E
pop_18_plus The number of people aged 18+ S0101 S0101_C01_026E
bachelors_18-24 The number of people aged 18–24 with at least a bachelor’s degree S1501 S1501_C01_005E
bachelors_25_plus The number of people aged 25+ with at least a bachelor’s degree S1501 S1501_C01_015E
pcnt_under_10k The percentage of households with income less than $10k S1901 S1901_C01_002E
pcnt_10k_15k The percentage of households with income \$10k - \$15k S1901 S1901_C01_003E
pcnt_15k_25k The percentage of households with income \$15k - \$25k S1901 S1901_C01_004E
pcnt_25k_35k The percentage of households with income \$25k - \$35k S1901 S1901_C01_005E
pcnt_35k_50k The percentage of households with income \$35k - \$50k S1901 S1901_C01_006E
pcnt_50k_75k The percentage of households with income \$50k - \$75k S1901 S1901_C01_007E
pcnt_75k_100k The percentage of households with income \$75k - \$100k S1901 S1901_C01_008E
pcnt_100k_150k The percentage of households with income \$100k - \$150k S1901 S1901_C01_009E
pcnt_150k_200k The percentage of households with income \$150k - \$200k S1901 S1901_C01_010E
pcnt_upper_class The percentage of households with income higher than \$200k S1901 S1901_C01_011E
state_fips State FIPS (string) state
county_fips County FIPS (string) county
Derived Column Description Calculation
pcnt_bachelors Percentage of peole 18_plus with a bachelor's degree or higher (bachelor_18-24 + bachelor_25_plus) / (pop_18_plus) x 100
low_income Percentage of low-income households pcnt_under_10k + pcnt_10k_15k + pcnt_15k_25k + pcnt_25k_35k + pcnt_35k_50k
middle_class Percentage of middle-class households pcnt_50k_75k + pcnt_75k_100k + pcnt_150k_200k
fips Full county FIPS with state FIPS prefix state_fips + county_fips
In [3]:
# Create a dictionary of variable codes for API URL
var_dict_nynj = {"NAME": "county",
    "S0101_C01_001E": "total_pop",
    "S0101_C02_030E": "pcnt_65_plus",
    "S1701_C03_001E": "poverty_rate",
    "S2701_C03_001E": "pcnt_insured",
    "S0101_C01_026E": "pop_18_plus",
    "S1501_C01_005E": "bachelors_18-24",
    "S1501_C01_015E": "bachelors_25_plus",
    "S1901_C01_002E": "pcnt_under_10k",
    "S1901_C01_003E": "pcnt_10k_15k",
    "S1901_C01_004E": "pcnt_15k_25k",
    "S1901_C01_005E": "pcnt_25k_35k",
    "S1901_C01_006E": "pcnt_35k_50k",
    "S1901_C01_007E": "pcnt_50k_75k",
    "S1901_C01_008E": "pcnt_75k_100k",
    "S1901_C01_009E": "pcnt_100k_150k",
    "S1901_C01_010E": "pcnt_150k_200k",
    "S1901_C01_011E": "pcnt_upper_class"}
In [4]:
# Retrieve census data for NY and NJ
col_ids = ",".join(list(var_dict_nynj.keys()))
url = f'https://api.census.gov/data/2023/acs/acs5/subject?get={col_ids}&for=county:*&in=state:36,34&key=c8bb46862affebce7371a4122259fdb9e6de92fb'
response = requests.get(url)
content = response.json()

# Store the raw data in local data store
with open('acs_nynj.json', 'w') as file:
    json.dump(content, file)

df1 = pd.read_json('acs_nynj.json')
df1.head(3)
Out[4]:
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
0 NAME S0101_C01_001E S0101_C02_030E S1701_C03_001E S2701_C03_001E S0101_C01_026E S1501_C01_005E S1501_C01_015E S1901_C01_002E S1901_C01_003E S1901_C01_004E S1901_C01_005E S1901_C01_006E S1901_C01_007E S1901_C01_008E S1901_C01_009E S1901_C01_010E S1901_C01_011E state county
1 Atlantic County, New Jersey 274704 19.3 13.1 92.5 217089 3258 60079 4.9 3.4 6.9 7.5 10.8 15.5 12.3 18.7 9.6 10.5 34 001
2 Bergen County, New Jersey 954717 17.8 6.7 93.8 753888 19996 356861 3.2 2.0 3.8 4.3 6.5 11.3 9.9 17.8 12.8 28.4 34 003
In [5]:
# Set the first row as the temporary column headers
df1.columns = df1.iloc[0]
# Drop the variable row
df1 = df1[1:].reset_index(drop=True)
df1[1:].reset_index(drop=True, inplace=True)

df1.head(2)
Out[5]:
NAME S0101_C01_001E S0101_C02_030E S1701_C03_001E S2701_C03_001E S0101_C01_026E S1501_C01_005E S1501_C01_015E S1901_C01_002E S1901_C01_003E S1901_C01_004E S1901_C01_005E S1901_C01_006E S1901_C01_007E S1901_C01_008E S1901_C01_009E S1901_C01_010E S1901_C01_011E state county
0 Atlantic County, New Jersey 274704 19.3 13.1 92.5 217089 3258 60079 4.9 3.4 6.9 7.5 10.8 15.5 12.3 18.7 9.6 10.5 34 001
1 Bergen County, New Jersey 954717 17.8 6.7 93.8 753888 19996 356861 3.2 2.0 3.8 4.3 6.5 11.3 9.9 17.8 12.8 28.4 34 003
In [6]:
# Rename the column headers
df1.rename(columns={'state': 'state_fips', 'county': 'county_fips'}, inplace=True)
df1.rename(columns=var_dict_nynj, inplace=True)

df1.head(3)
Out[6]:
county total_pop pcnt_65_plus poverty_rate pcnt_insured pop_18_plus bachelors_18-24 bachelors_25_plus pcnt_under_10k pcnt_10k_15k pcnt_15k_25k pcnt_25k_35k pcnt_35k_50k pcnt_50k_75k pcnt_75k_100k pcnt_100k_150k pcnt_150k_200k pcnt_upper_class state_fips county_fips
0 Atlantic County, New Jersey 274704 19.3 13.1 92.5 217089 3258 60079 4.9 3.4 6.9 7.5 10.8 15.5 12.3 18.7 9.6 10.5 34 001
1 Bergen County, New Jersey 954717 17.8 6.7 93.8 753888 19996 356861 3.2 2.0 3.8 4.3 6.5 11.3 9.9 17.8 12.8 28.4 34 003
2 Burlington County, New Jersey 464226 17.9 6.8 96.2 368180 7335 139451 3.0 1.7 3.7 4.6 7.9 13.0 13.2 20.2 13.4 19.2 34 005
In [7]:
df1.info()

<class 'pandas.DataFrame'>
RangeIndex: 83 entries, 0 to 82
Data columns (total 20 columns):
 #   Column             Non-Null Count  Dtype
---  ------             --------------  -----
 0   county             83 non-null     str  
 1   total_pop          83 non-null     str  
 2   pcnt_65_plus       83 non-null     str  
 3   poverty_rate       83 non-null     str  
 4   pcnt_insured       83 non-null     str  
 5   pop_18_plus        83 non-null     str  
 6   bachelors_18-24    83 non-null     str  
 7   bachelors_25_plus  83 non-null     str  
 8   pcnt_under_10k     83 non-null     str  
 9   pcnt_10k_15k       83 non-null     str  
 10  pcnt_15k_25k       83 non-null     str  
 11  pcnt_25k_35k       83 non-null     str  
 12  pcnt_35k_50k       83 non-null     str  
 13  pcnt_50k_75k       83 non-null     str  
 14  pcnt_75k_100k      83 non-null     str  
 15  pcnt_100k_150k     83 non-null     str  
 16  pcnt_150k_200k     83 non-null     str  
 17  pcnt_upper_class   83 non-null     str  
 18  state_fips         83 non-null     str  
 19  county_fips        83 non-null     str  
dtypes: str(20)
memory usage: 13.1 KB
In [8]:
# Change the data type of numeric columns
df1 = df1.astype({'total_pop': 'int64', 'pcnt_65_plus':'float64', 'poverty_rate':'float64', 'pcnt_insured':'float64', 'pop_18_plus':'int64', 'bachelors_18-24':'int64', 'bachelors_25_plus':'int64'})

income_cols = df1.columns[8:18]
df1[income_cols] = df1[income_cols].astype('float64')

df1.info()

<class 'pandas.DataFrame'>
RangeIndex: 83 entries, 0 to 82
Data columns (total 20 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   county             83 non-null     str    
 1   total_pop          83 non-null     int64  
 2   pcnt_65_plus       83 non-null     float64
 3   poverty_rate       83 non-null     float64
 4   pcnt_insured       83 non-null     float64
 5   pop_18_plus        83 non-null     int64  
 6   bachelors_18-24    83 non-null     int64  
 7   bachelors_25_plus  83 non-null     int64  
 8   pcnt_under_10k     83 non-null     float64
 9   pcnt_10k_15k       83 non-null     float64
 10  pcnt_15k_25k       83 non-null     float64
 11  pcnt_25k_35k       83 non-null     float64
 12  pcnt_35k_50k       83 non-null     float64
 13  pcnt_50k_75k       83 non-null     float64
 14  pcnt_75k_100k      83 non-null     float64
 15  pcnt_100k_150k     83 non-null     float64
 16  pcnt_150k_200k     83 non-null     float64
 17  pcnt_upper_class   83 non-null     float64
 18  state_fips         83 non-null     str    
 19  county_fips        83 non-null     str    
dtypes: float64(13), int64(4), str(3)
memory usage: 13.1 KB

pcnt_bachelors: The percentage of people aged 18_plus with at least a Bachelor's degree
pcnt_bachelors = (bachelors_18-24 + bachelors_25_plus) / (18_plus_pop) x 100

In [9]:
# Get column for percentage of people aged 18_plus with a bachelor's degree
df1['pcnt_bachelors'] = ((df1['bachelors_18-24'] + df1['bachelors_25_plus']) / (df1['pop_18_plus']) * 100).round(1)
df1.head(3)
Out[9]:
county total_pop pcnt_65_plus poverty_rate pcnt_insured pop_18_plus bachelors_18-24 bachelors_25_plus pcnt_under_10k pcnt_10k_15k ... pcnt_25k_35k pcnt_35k_50k pcnt_50k_75k pcnt_75k_100k pcnt_100k_150k pcnt_150k_200k pcnt_upper_class state_fips county_fips pcnt_bachelors
0 Atlantic County, New Jersey 274704 19.3 13.1 92.5 217089 3258 60079 4.9 3.4 ... 7.5 10.8 15.5 12.3 18.7 9.6 10.5 34 001 29.2
1 Bergen County, New Jersey 954717 17.8 6.7 93.8 753888 19996 356861 3.2 2.0 ... 4.3 6.5 11.3 9.9 17.8 12.8 28.4 34 003 50.0
2 Burlington County, New Jersey 464226 17.9 6.8 96.2 368180 7335 139451 3.0 1.7 ... 4.6 7.9 13.0 13.2 20.2 13.4 19.2 34 005 39.9

3 rows × 21 columns

In [10]:
# Get full FIPS code for counties
df1['fips'] = df1['state_fips'] + df1['county_fips']

df1.head(2)
Out[10]:
county total_pop pcnt_65_plus poverty_rate pcnt_insured pop_18_plus bachelors_18-24 bachelors_25_plus pcnt_under_10k pcnt_10k_15k ... pcnt_35k_50k pcnt_50k_75k pcnt_75k_100k pcnt_100k_150k pcnt_150k_200k pcnt_upper_class state_fips county_fips pcnt_bachelors fips
0 Atlantic County, New Jersey 274704 19.3 13.1 92.5 217089 3258 60079 4.9 3.4 ... 10.8 15.5 12.3 18.7 9.6 10.5 34 001 29.2 34001
1 Bergen County, New Jersey 954717 17.8 6.7 93.8 753888 19996 356861 3.2 2.0 ... 6.5 11.3 9.9 17.8 12.8 28.4 34 003 50.0 34003

2 rows × 22 columns

In [11]:
# Get percentage columns for low-income and middle class groups
df1['pcnt_low_income'] = df1['pcnt_under_10k'] + df1['pcnt_10k_15k'] + df1['pcnt_15k_25k'] + df1['pcnt_25k_35k'] + df1['pcnt_35k_50k']
df1['pcnt_middle_class'] = df1['pcnt_50k_75k'] + df1['pcnt_75k_100k'] + df1['pcnt_150k_200k']
df1.head(3)
Out[11]:
county total_pop pcnt_65_plus poverty_rate pcnt_insured pop_18_plus bachelors_18-24 bachelors_25_plus pcnt_under_10k pcnt_10k_15k ... pcnt_75k_100k pcnt_100k_150k pcnt_150k_200k pcnt_upper_class state_fips county_fips pcnt_bachelors fips pcnt_low_income pcnt_middle_class
0 Atlantic County, New Jersey 274704 19.3 13.1 92.5 217089 3258 60079 4.9 3.4 ... 12.3 18.7 9.6 10.5 34 001 29.2 34001 33.5 37.4
1 Bergen County, New Jersey 954717 17.8 6.7 93.8 753888 19996 356861 3.2 2.0 ... 9.9 17.8 12.8 28.4 34 003 50.0 34003 19.8 34.0
2 Burlington County, New Jersey 464226 17.9 6.8 96.2 368180 7335 139451 3.0 1.7 ... 13.2 20.2 13.4 19.2 34 005 39.9 34005 20.9 39.6

3 rows × 24 columns

In [12]:
# Keep only important columns
df1 = df1[['fips', 'county', 'total_pop', 'pcnt_65_plus', 'poverty_rate', 'pcnt_insured', 'pcnt_bachelors', 'pcnt_low_income', 'pcnt_middle_class', 'pcnt_upper_class']]
df1.head(3)
Out[12]:
fips county total_pop pcnt_65_plus poverty_rate pcnt_insured pcnt_bachelors pcnt_low_income pcnt_middle_class pcnt_upper_class
0 34001 Atlantic County, New Jersey 274704 19.3 13.1 92.5 29.2 33.5 37.4 10.5
1 34003 Bergen County, New Jersey 954717 17.8 6.7 93.8 50.0 19.8 34.0 28.4
2 34005 Burlington County, New Jersey 464226 17.9 6.8 96.2 39.9 20.9 39.6 19.2
In [13]:
# Import list of all counties
df=pd.read_csv('../ny_nj_ct_fips.csv', dtype={'fips':str})
df.info()

<class 'pandas.DataFrame'>
RangeIndex: 91 entries, 0 to 90
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype
---  ------  --------------  -----
 0   fips    91 non-null     str  
 1   county  91 non-null     str  
 2   state   91 non-null     str  
dtypes: str(3)
memory usage: 2.3 KB
In [14]:
# Get only the county list for NY and NJ
ny_nj = df[df['state'].isin(['NY', 'NJ'])]
ny_nj.tail()
Out[14]:
fips county state
78 34033 Salem NJ
79 34035 Somerset NJ
80 34037 Sussex NJ
81 34039 Union NJ
82 34041 Warren NJ
In [15]:
# Merge acs data with the county list
merged_nynj = pd.merge(ny_nj, df1, how='left', on=['fips'])
merged_nynj.head(3)
Out[15]:
fips county_x state county_y total_pop pcnt_65_plus poverty_rate pcnt_insured pcnt_bachelors pcnt_low_income pcnt_middle_class pcnt_upper_class
0 36001 Albany NY Albany County, New York 315374 17.8 12.9 97.0 41.6 29.9 39.1 12.1
1 36003 Allegany NY Allegany County, New York 47027 19.7 16.8 95.1 21.0 40.2 39.1 4.1
2 36005 Bronx NY Bronx County, New York 1419250 13.9 26.9 92.7 20.6 50.8 32.2 5.2
In [16]:
# Change county column name
merged_nynj.rename(columns={'county_x':'county'}, inplace=True)
merged_nynj.drop(columns='county_y', inplace=True)
In [17]:
merged_nynj.info()

<class 'pandas.DataFrame'>
RangeIndex: 83 entries, 0 to 82
Data columns (total 11 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   fips               83 non-null     str    
 1   county             83 non-null     str    
 2   state              83 non-null     str    
 3   total_pop          83 non-null     int64  
 4   pcnt_65_plus       83 non-null     float64
 5   poverty_rate       83 non-null     float64
 6   pcnt_insured       83 non-null     float64
 7   pcnt_bachelors     83 non-null     float64
 8   pcnt_low_income    83 non-null     float64
 9   pcnt_middle_class  83 non-null     float64
 10  pcnt_upper_class   83 non-null     float64
dtypes: float64(7), int64(1), str(3)
memory usage: 7.3 KB

Variables for Connecticut¶

Column Name Description Table Variable code
town Town name (name of location at the chosen geo level) all table NAME
total_pop Total population S0101 S0101_C01_001E
pop_65_plus Number of people aged 65 and over S0101 S0101_C01_030E
poverty_universe Population for whom poverty status is determined S1701 S1701_C01_001E
below_poverty Number of people who are below poverty threshold S1701 S1701_C02_001E
civ_noninst_pop Civilian noninstitutionalized population S2701 S2701_C01_001E
insured_pop Number of people in civ_noninst_pop having health insurance S2701 S2701_C02_001E
pop_18_plus Number of people aged 18_plus S0101 S0101_C01_026E
bachelors_18-24 Number of people aged 18–24 with a bachelor’s degree or higher S1501 S1501_C01_005E
bachelors_25_plus Number of people aged 25_plus with a bachelor’s degree or higher S1501 S1501_C01_015E
total_households Total number of households S1901 S1901_C01_001E
pcnt_under_10k Percentage of households with income less than $10k S1901 S1901_C01_002E
pcnt_10k_15k Percentage of households with income \$10k - \$15k S1901 S1901_C01_003E
pcnt_15k_25k Percentage of households with income \$15k - \$25k S1901 S1901_C01_004E
pcnt_25k_35k Percentage of households with income \$25k - \$35k S1901 S1901_C01_005E
pcnt_35k_50k Percentage of households with income \$35k - \$50k S1901 S1901_C01_006E
pcnt_50k_75k Percentage of households with income \$50k - \$75k S1901 S1901_C01_007E
pcnt_75k_100k Percentage of households with income \$75k - \$100k S1901 S1901_C01_008E
pcnt_100k_150k Percentage of households with income \$100k - \$150k S1901 S1901_C01_009E
pcnt_150k_200k Percentage of households with income \$150k - \$200k S1901 S1901_C01_010E
pcnt_town_upper_class Percentage of households with income higher than \$200k S1901 S1901_C01_011E
state_fips State FIPS (string) state
region_fips Planning Regions FIPS (string) county
town_fips Town FIPS (string) county subdivision

1. Derived Columns before County Mapping¶

Derived Column Description Calculation
bachelors_18_plus The number of people aged 18_plus with a bachelor’s degree or higher bachelors_18-24 + bachelors_25_plus
pcnt_town_low_income The percentage of households with income under \$50k pcnt_under_10k + pcnt_10k_15k + pcnt_15k_25k + pcnt_25k_35k + pcnt_35k_50k
pcnt_town_middle_class The percentage of households with income \$50k - \$200k pcnt_50k_75k + pcnt_75k_100k + pcnt_100k_150k + pcnt_150k_200k

2. Mapping Towns to Counties¶

After retrieving all of the above columns, map each town to its corresponding county using FIPS codes.

Added Column Description
town_name Short town name
county_fips Full county FIPS with state FIPS prefix
county_name County name

3. Re-Basing Income Percentages to the County Level¶

pcnt_low_income, pcnt_middle_class, and pcnt_upper_class columns are each a percentage of that town's population, not the county's. So we can't just average the percentages across towns in a county, because towns have different population sizes. We need a population-weighted recalculation.

Procedure:

  • Recover the raw count for each town
  • Get the county's total count of households
  • Sum raw count columns within each county
  • Recalculate the percentages at county level
Derived Column Description Calculation
pop_low_income Number of low-income households pcnt_town_low_income * total_households / 100
pop_middle_class Number of middle-class households pcnt_town_middle_class * total_households / 100
pop_upper_class Number of upper-class households pcnt_town_upper_class * total_households / 100

4. Aggregation by County¶

All variables are grouped by county. The raw count columns (total_pop, pop_65_plus, poverty_universe, below_poverty, civ_noninst_pop, insured_pop, pop_18_plus, bachelors_18_plus, total_households, pop_low_income, pop_middle_class, pop_upper_class) are summed across towns within each county before any ratios are computed.

5. Computing County-Level Columns¶

County-Level Column Description Calculation
pcnt_65_plus Percentage of people aged 65 and over (pop_65_plus / total_pop) *100
poverty_rate Percentage of people who are below poverty threshold (below_poverty / poverty_universe) *100
pcnt_insured Percentage of people having health insurance (insured_pop / civ_noninst_pop) *100
pcnt_bachelors Percentage of people 18_plus with a bachelor's degree or higher (bachelors_18_plus / pop_18_plus) *100
pcnt_low_income Percentage of the county's households that is in low-income group (pop_low_income / total_households) *100
pcnt_middle_class Percentage of the county's households that is in middle-class (pop_middle_class / total_households) *100
pcnt_upper_class Percentage of the county's households that is upper-class (pop_upper_class / total_households) *100
In [18]:
# Create a dictionary of variable codes for API URL
var_dict_ct = {"NAME": "town",
    "S0101_C01_001E": "total_pop",
    "S0101_C01_030E": "pop_65_plus",
    "S1701_C01_001E": "poverty_universe",
    "S1701_C02_001E": "below_poverty",
    "S2701_C01_001E": "civ_noninst_pop",
    "S2701_C02_001E": "insured_pop",
    "S0101_C01_026E": "pop_18_plus",
    "S1501_C01_005E": "bachelors_18-24",
    "S1501_C01_015E": "bachelors_25_plus",
    "S1901_C01_001E": "total_households",
    "S1901_C01_002E": "pcnt_under_10k",
    "S1901_C01_003E": "pcnt_10k_15k",
    "S1901_C01_004E": "pcnt_15k_25k",
    "S1901_C01_005E": "pcnt_25k_35k",
    "S1901_C01_006E": "pcnt_35k_50k",
    "S1901_C01_007E": "pcnt_50k_75k",
    "S1901_C01_008E": "pcnt_75k_100k",
    "S1901_C01_009E": "pcnt_100k_150k",
    "S1901_C01_010E": "pcnt_150k_200k",
    "S1901_C01_011E": "pcnt_town_upper_class"}
In [19]:
# Retrieve census data for CT
col_ids = ",".join(list(var_dict_ct.keys()))
url = f'https://api.census.gov/data/2023/acs/acs5/subject?get={col_ids}&for=county%20subdivision:*&in=state:09&key=c8bb46862affebce7371a4122259fdb9e6de92fb'
response = requests.get(url)
content = response.json()

# Store the raw data in local data store
with open('acs_ct.json', 'w') as file:
    json.dump(content, file)

df2 = pd.read_json('acs_ct.json')
df2.head(3)
Out[19]:
0 1 2 3 4 5 6 7 8 9 ... 14 15 16 17 18 19 20 21 22 23
0 NAME S0101_C01_001E S0101_C01_030E S1701_C01_001E S1701_C02_001E S2701_C01_001E S2701_C02_001E S0101_C01_026E S1501_C01_005E S1501_C01_015E ... S1901_C01_005E S1901_C01_006E S1901_C01_007E S1901_C01_008E S1901_C01_009E S1901_C01_010E S1901_C01_011E state county county subdivision
1 Andover town, Capitol Planning Region, Connect... 3148 651 3148 29 3148 3029 2671 69 1129 ... 0.7 8.0 14.0 13.6 24.6 15.0 21.9 09 110 01080
2 Avon town, Capitol Planning Region, Connecticut 18856 3964 18667 888 18665 18509 14555 230 9752 ... 5.6 5.6 8.1 7.3 16.1 11.8 39.5 09 110 02060

3 rows × 24 columns

In [20]:
# Set the first row as the temporary column headers
df2.columns = df2.iloc[0]
# Drop the variable row
df2 = df2[1:].reset_index(drop=True)
df2[1:].reset_index(drop=True, inplace=True)

df2.head(2)
Out[20]:
NAME S0101_C01_001E S0101_C01_030E S1701_C01_001E S1701_C02_001E S2701_C01_001E S2701_C02_001E S0101_C01_026E S1501_C01_005E S1501_C01_015E ... S1901_C01_005E S1901_C01_006E S1901_C01_007E S1901_C01_008E S1901_C01_009E S1901_C01_010E S1901_C01_011E state county county subdivision
0 Andover town, Capitol Planning Region, Connect... 3148 651 3148 29 3148 3029 2671 69 1129 ... 0.7 8.0 14.0 13.6 24.6 15.0 21.9 09 110 01080
1 Avon town, Capitol Planning Region, Connecticut 18856 3964 18667 888 18665 18509 14555 230 9752 ... 5.6 5.6 8.1 7.3 16.1 11.8 39.5 09 110 02060

2 rows × 24 columns

In [21]:
# Rename the column headers
df2.rename(columns={'state': 'state_fips', 'county': 'region_fips', 'county subdivision': 'town_fips'}, inplace=True)
df2.rename(columns=var_dict_ct, inplace=True)

df2.head(3)
Out[21]:
town total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18-24 bachelors_25_plus ... pcnt_25k_35k pcnt_35k_50k pcnt_50k_75k pcnt_75k_100k pcnt_100k_150k pcnt_150k_200k pcnt_town_upper_class state_fips region_fips town_fips
0 Andover town, Capitol Planning Region, Connect... 3148 651 3148 29 3148 3029 2671 69 1129 ... 0.7 8.0 14.0 13.6 24.6 15.0 21.9 09 110 01080
1 Avon town, Capitol Planning Region, Connecticut 18856 3964 18667 888 18665 18509 14555 230 9752 ... 5.6 5.6 8.1 7.3 16.1 11.8 39.5 09 110 02060
2 Berlin town, Capitol Planning Region, Connecticut 20210 4837 20132 911 20190 19805 16186 588 6344 ... 4.8 11.2 11.5 12.0 19.0 15.8 19.6 09 110 04300

3 rows × 24 columns

In [22]:
df2.info()

<class 'pandas.DataFrame'>
RangeIndex: 174 entries, 0 to 173
Data columns (total 24 columns):
 #   Column                 Non-Null Count  Dtype
---  ------                 --------------  -----
 0   town                   174 non-null    str  
 1   total_pop              174 non-null    str  
 2   pop_65_plus            174 non-null    str  
 3   poverty_universe       174 non-null    str  
 4   below_poverty          174 non-null    str  
 5   civ_noninst_pop        174 non-null    str  
 6   insured_pop            174 non-null    str  
 7   pop_18_plus            174 non-null    str  
 8   bachelors_18-24        174 non-null    str  
 9   bachelors_25_plus      174 non-null    str  
 10  total_households       174 non-null    str  
 11  pcnt_under_10k         174 non-null    str  
 12  pcnt_10k_15k           174 non-null    str  
 13  pcnt_15k_25k           174 non-null    str  
 14  pcnt_25k_35k           174 non-null    str  
 15  pcnt_35k_50k           174 non-null    str  
 16  pcnt_50k_75k           174 non-null    str  
 17  pcnt_75k_100k          174 non-null    str  
 18  pcnt_100k_150k         174 non-null    str  
 19  pcnt_150k_200k         174 non-null    str  
 20  pcnt_town_upper_class  174 non-null    str  
 21  state_fips             174 non-null    str  
 22  region_fips            174 non-null    str  
 23  town_fips              174 non-null    str  
dtypes: str(24)
memory usage: 32.8 KB
In [23]:
# Change the data type of numeric columns
int_cols = df2.columns[1:11]
df2[int_cols] = df2[int_cols].astype('int64')

income_cols = df2.columns[8:21]
df2[income_cols] = df2[income_cols].astype('float64')

df2.info()

<class 'pandas.DataFrame'>
RangeIndex: 174 entries, 0 to 173
Data columns (total 24 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   town                   174 non-null    str    
 1   total_pop              174 non-null    int64  
 2   pop_65_plus            174 non-null    int64  
 3   poverty_universe       174 non-null    int64  
 4   below_poverty          174 non-null    int64  
 5   civ_noninst_pop        174 non-null    int64  
 6   insured_pop            174 non-null    int64  
 7   pop_18_plus            174 non-null    int64  
 8   bachelors_18-24        174 non-null    float64
 9   bachelors_25_plus      174 non-null    float64
 10  total_households       174 non-null    float64
 11  pcnt_under_10k         174 non-null    float64
 12  pcnt_10k_15k           174 non-null    float64
 13  pcnt_15k_25k           174 non-null    float64
 14  pcnt_25k_35k           174 non-null    float64
 15  pcnt_35k_50k           174 non-null    float64
 16  pcnt_50k_75k           174 non-null    float64
 17  pcnt_75k_100k          174 non-null    float64
 18  pcnt_100k_150k         174 non-null    float64
 19  pcnt_150k_200k         174 non-null    float64
 20  pcnt_town_upper_class  174 non-null    float64
 21  state_fips             174 non-null    str    
 22  region_fips            174 non-null    str    
 23  town_fips              174 non-null    str    
dtypes: float64(13), int64(7), str(4)
memory usage: 32.8 KB
In [24]:
# Calculate the number of people aged 18_plus with a bachelor's degree
df2['bachelors_18_plus'] = df2['bachelors_18-24'] + df2['bachelors_25_plus']
# # Get percentage columns for low-income and middle class groups
df2['pcnt_town_low_income'] = df2['pcnt_under_10k'] + df2['pcnt_10k_15k'] + df2['pcnt_15k_25k'] + df2['pcnt_25k_35k'] + df2['pcnt_35k_50k']
df2['pcnt_town_middle_class'] = df2['pcnt_50k_75k'] + df2['pcnt_75k_100k'] + df2['pcnt_150k_200k']
df2.tail(3)
Out[24]:
town total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18-24 bachelors_25_plus ... pcnt_75k_100k pcnt_100k_150k pcnt_150k_200k pcnt_town_upper_class state_fips region_fips town_fips bachelors_18_plus pcnt_town_low_income pcnt_town_middle_class
171 Weston town, Western Connecticut Planning Regi... 10335 1573 10335 204 10335 10157 7546 236.0 5588.0 ... 5.7 8.7 9.9 63.1 09 190 83430 5824.0 7.7 20.5
172 Westport town, Western Connecticut Planning Re... 27282 5068 27075 983 27192 26780 19665 748.0 14074.0 ... 4.5 11.2 7.8 58.2 09 190 83500 14822.0 10.5 20.1
173 Wilton town, Western Connecticut Planning Regi... 18439 3026 18200 459 18233 17953 13571 435.0 8947.0 ... 4.5 10.7 10.8 56.8 09 190 86370 9382.0 11.8 20.7

3 rows × 27 columns

In [25]:
# Keep only important columns
df2 = df2[['town_fips', 'town', 'total_pop', 'pop_65_plus', 'poverty_universe', 'below_poverty', 'civ_noninst_pop', 'insured_pop', 'pop_18_plus', 'bachelors_18_plus', 
           'total_households', 'pcnt_town_low_income', 'pcnt_town_middle_class', 'pcnt_town_upper_class']]
df2.tail(3)
Out[25]:
town_fips town total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18_plus total_households pcnt_town_low_income pcnt_town_middle_class pcnt_town_upper_class
171 83430 Weston town, Western Connecticut Planning Regi... 10335 1573 10335 204 10335 10157 7546 5824.0 3549.0 7.7 20.5 63.1
172 83500 Westport town, Western Connecticut Planning Re... 27282 5068 27075 983 27192 26780 19665 14822.0 9698.0 10.5 20.1 58.2
173 86370 Wilton town, Western Connecticut Planning Regi... 18439 3026 18200 459 18233 17953 13571 9382.0 6203.0 11.8 20.7 56.8
In [26]:
# Import the CT town-county crosswalk file
crosswalk = pd.read_csv('../../reference/ct_crosswalk/ct_town_crosswalk.csv', dtype={'town_fips_2020': str, 'county_fips': str})
crosswalk.info()

<class 'pandas.DataFrame'>
RangeIndex: 169 entries, 0 to 168
Data columns (total 7 columns):
 #   Column          Non-Null Count  Dtype
---  ------          --------------  -----
 0   town_name       169 non-null    str  
 1   town_fips_2020  169 non-null    str  
 2   county_fips     169 non-null    str  
 3   county_name     169 non-null    str  
 4   town_fips_2022  169 non-null    int64
 5   region_fips     169 non-null    int64
 6   region_name     169 non-null    str  
dtypes: int64(2), str(5)
memory usage: 9.4 KB
In [27]:
# Keep only important columns in crosswalk
crosswalk = crosswalk[['town_name','town_fips_2020', 'county_fips', 'county_name']]
crosswalk.head(3)
Out[27]:
town_name town_fips_2020 county_fips county_name
0 Andover 0901301080 09013 Tolland County
1 Ansonia 0900901220 09009 New Haven County
2 Ashford 0901501430 09015 Windham County
In [28]:
# Remove County suffix in county_name
crosswalk['county_name'] = crosswalk['county_name'].str.replace(' County', '', regex=True)
crosswalk.head(3)
Out[28]:
town_name town_fips_2020 county_fips county_name
0 Andover 0901301080 09013 Tolland
1 Ansonia 0900901220 09009 New Haven
2 Ashford 0901501430 09015 Windham

Town FIPS code structure: XX (State) + YYY (County/Region) + ZZZZZ (Town Code)

We need to extract the town FIPS from town_fips_2020 in crosswalk to match with town_fips column in df2 dataframe before merging the two.

In [29]:
# The last 5 digits of town_fips_2020 are for the town
crosswalk['town_fips'] = crosswalk['town_fips_2020'].str[-5:]
crosswalk.head()
Out[29]:
town_name town_fips_2020 county_fips county_name town_fips
0 Andover 0901301080 09013 Tolland 01080
1 Ansonia 0900901220 09009 New Haven 01220
2 Ashford 0901501430 09015 Windham 01430
3 Avon 0900302060 09003 Hartford 02060
4 Barkhamsted 0900502760 09005 Litchfield 02760
In [30]:
# Map town to counties based on town FIPS
merged_ct = pd.merge(crosswalk, df2, how='left', on='town_fips')
merged_ct.head(3)
Out[30]:
town_name town_fips_2020 county_fips county_name town_fips town total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18_plus total_households pcnt_town_low_income pcnt_town_middle_class pcnt_town_upper_class
0 Andover 0901301080 09013 Tolland 01080 Andover town, Capitol Planning Region, Connect... 3148 651 3148 29 3148 3029 2671 1198.0 1173.0 10.9 42.6 21.9
1 Ansonia 0900901220 09009 New Haven 01220 Ansonia town, Naugatuck Valley Planning Region... 18951 3453 18951 1621 18951 17816 15432 3375.0 7455.0 32.3 42.7 6.5
2 Ashford 0901501430 09015 Windham 01430 Ashford town, Northeastern Connecticut Plannin... 4220 745 4214 204 4201 4017 3454 1317.0 1827.0 16.4 35.0 31.6
In [31]:
# Arrange columns
ct = merged_ct[['county_fips', 'county_name', 'town_fips', 'town_name', 'total_pop', 'pop_65_plus', 'poverty_universe', 'below_poverty', 'civ_noninst_pop', 'insured_pop', 'pop_18_plus', 'bachelors_18_plus', 
           'total_households', 'pcnt_town_low_income', 'pcnt_town_middle_class', 'pcnt_town_upper_class']]
ct.info()

<class 'pandas.DataFrame'>
RangeIndex: 169 entries, 0 to 168
Data columns (total 16 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 0   county_fips             169 non-null    str    
 1   county_name             169 non-null    str    
 2   town_fips               169 non-null    str    
 3   town_name               169 non-null    str    
 4   total_pop               169 non-null    int64  
 5   pop_65_plus             169 non-null    int64  
 6   poverty_universe        169 non-null    int64  
 7   below_poverty           169 non-null    int64  
 8   civ_noninst_pop         169 non-null    int64  
 9   insured_pop             169 non-null    int64  
 10  pop_18_plus             169 non-null    int64  
 11  bachelors_18_plus       169 non-null    float64
 12  total_households        169 non-null    float64
 13  pcnt_town_low_income    169 non-null    float64
 14  pcnt_town_middle_class  169 non-null    float64
 15  pcnt_town_upper_class   169 non-null    float64
dtypes: float64(5), int64(7), str(4)
memory usage: 21.3 KB
In [32]:
# Get the raw count of income groups at town level
ct['pop_low_income'] = ct['pcnt_town_low_income'] * ct['total_households'] / 100
ct['pop_middle_class'] = ct['pcnt_town_middle_class'] * ct['total_households'] / 100
ct['pop_upper_class'] = ct['pcnt_town_upper_class'] * ct['total_households'] / 100

ct.sort_values(['county_name', 'town_name']).head(3)
Out[32]:
county_fips county_name town_fips town_name total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18_plus total_households pcnt_town_low_income pcnt_town_middle_class pcnt_town_upper_class pop_low_income pop_middle_class pop_upper_class
8 09001 Fairfield 04720 Bethel 20498 3165 20325 870 20350 18555 15917 6620.0 7538.0 20.7 35.6 23.9 1560.366 2683.528 1801.582
14 09001 Fairfield 08070 Bridgeport 148012 19666 144753 32623 146666 125589 116282 25244.0 55498.0 45.3 35.8 5.3 25140.594 19868.284 2941.394
17 09001 Fairfield 08980 Brookfield 17490 3704 17361 1007 17426 16554 13618 7051.0 6542.0 14.7 32.3 32.9 961.674 2113.066 2152.318
In [33]:
# Drop town-level percentage columns before aggregating
ct.drop(columns=['pcnt_town_low_income', 'pcnt_town_middle_class', 'pcnt_town_upper_class'], inplace=True)
ct.sort_values(['county_name', 'town_name']).head(3)
Out[33]:
county_fips county_name town_fips town_name total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18_plus total_households pop_low_income pop_middle_class pop_upper_class
8 09001 Fairfield 04720 Bethel 20498 3165 20325 870 20350 18555 15917 6620.0 7538.0 1560.366 2683.528 1801.582
14 09001 Fairfield 08070 Bridgeport 148012 19666 144753 32623 146666 125589 116282 25244.0 55498.0 25140.594 19868.284 2941.394
17 09001 Fairfield 08980 Brookfield 17490 3704 17361 1007 17426 16554 13618 7051.0 6542.0 961.674 2113.066 2152.318
In [34]:
# Aggregate CT data to county level
agg_ct = ct.drop(columns=['town_fips', 'town_name']).groupby(['county_name','county_fips']).sum().reset_index()
agg_ct.head()
Out[34]:
county_name county_fips total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18_plus total_households pop_low_income pop_middle_class pop_upper_class
0 Fairfield 09001 959099 160339 944054 87612 952361 878871 748766 354808.0 355129.0 84802.339 114801.547 98329.013
1 Hartford 09003 895736 156768 876704 90572 883860 846367 709563 272623.0 363137.0 103522.358 137422.205 56606.041
2 Litchfield 09005 185732 40743 183874 14978 184347 177173 151677 55408.0 76102.0 18232.728 30768.988 11763.526
3 Middlesex 09007 164983 35131 159809 11399 163024 157264 137102 57631.0 69565.0 16888.536 27014.818 12147.884
4 New Haven 09009 862028 157625 838493 99297 854091 810751 687341 244079.0 343733.0 108070.894 125082.806 50086.741
In [35]:
agg_ct.info()

<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 14 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   county_name        8 non-null      str    
 1   county_fips        8 non-null      str    
 2   total_pop          8 non-null      int64  
 3   pop_65_plus        8 non-null      int64  
 4   poverty_universe   8 non-null      int64  
 5   below_poverty      8 non-null      int64  
 6   civ_noninst_pop    8 non-null      int64  
 7   insured_pop        8 non-null      int64  
 8   pop_18_plus        8 non-null      int64  
 9   bachelors_18_plus  8 non-null      float64
 10  total_households   8 non-null      float64
 11  pop_low_income     8 non-null      float64
 12  pop_middle_class   8 non-null      float64
 13  pop_upper_class    8 non-null      float64
dtypes: float64(5), int64(7), str(2)
memory usage: 1.0 KB
In [36]:
# Calculate percentages for CT
agg_ct['pcnt_65_plus'] = ((agg_ct['pop_65_plus'] / agg_ct['total_pop']) *100).round(1)
agg_ct['poverty_rate'] = ((agg_ct['below_poverty'] / agg_ct['poverty_universe']) *100).round(1)
agg_ct['pcnt_insured'] = ((agg_ct['insured_pop'] / agg_ct['civ_noninst_pop']) *100).round(1)
agg_ct['pcnt_bachelors'] = ((agg_ct['bachelors_18_plus'] / agg_ct['pop_18_plus']) *100).round(1)
agg_ct['pcnt_low_income'] = ((agg_ct['pop_low_income'] / agg_ct['total_households']) *100).round(1)
agg_ct['pcnt_middle_class'] = ((agg_ct['pop_middle_class'] / agg_ct['total_households']) *100).round(1)
agg_ct['pcnt_upper_class'] = ((agg_ct['pop_upper_class'] / agg_ct['total_households']) *100).round(1)

agg_ct.head()
Out[36]:
county_name county_fips total_pop pop_65_plus poverty_universe below_poverty civ_noninst_pop insured_pop pop_18_plus bachelors_18_plus ... pop_low_income pop_middle_class pop_upper_class pcnt_65_plus poverty_rate pcnt_insured pcnt_bachelors pcnt_low_income pcnt_middle_class pcnt_upper_class
0 Fairfield 09001 959099 160339 944054 87612 952361 878871 748766 354808.0 ... 84802.339 114801.547 98329.013 16.7 9.3 92.3 47.4 23.9 32.3 27.7
1 Hartford 09003 895736 156768 876704 90572 883860 846367 709563 272623.0 ... 103522.358 137422.205 56606.041 17.5 10.3 95.8 38.4 28.5 37.8 15.6
2 Litchfield 09005 185732 40743 183874 14978 184347 177173 151677 55408.0 ... 18232.728 30768.988 11763.526 21.9 8.1 96.1 36.5 24.0 40.4 15.5
3 Middlesex 09007 164983 35131 159809 11399 163024 157264 137102 57631.0 ... 16888.536 27014.818 12147.884 21.3 7.1 96.5 42.0 24.3 38.8 17.5
4 New Haven 09009 862028 157625 838493 99297 854091 810751 687341 244079.0 ... 108070.894 125082.806 50086.741 18.3 11.8 94.9 35.5 31.4 36.4 14.6

5 rows × 21 columns

In [37]:
# Rename and rearrange columns before appending CT data to NY & NJ
agg_ct.rename(columns={'county_fips': 'fips', 'county_name': 'county'}, inplace=True)
agg_ct['state'] = 'CT'
agg_ct = agg_ct[['fips', 'county', 'state', 'total_pop', 'pcnt_65_plus', 'poverty_rate', 'pcnt_insured', 'pcnt_bachelors', 'pcnt_low_income', 'pcnt_middle_class', 'pcnt_upper_class']]

agg_ct.head(3)
Out[37]:
fips county state total_pop pcnt_65_plus poverty_rate pcnt_insured pcnt_bachelors pcnt_low_income pcnt_middle_class pcnt_upper_class
0 09001 Fairfield CT 959099 16.7 9.3 92.3 47.4 23.9 32.3 27.7
1 09003 Hartford CT 895736 17.5 10.3 95.8 38.4 28.5 37.8 15.6
2 09005 Litchfield CT 185732 21.9 8.1 96.1 36.5 24.0 40.4 15.5
In [38]:
agg_ct.info()

<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 11 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   fips               8 non-null      str    
 1   county             8 non-null      str    
 2   state              8 non-null      str    
 3   total_pop          8 non-null      int64  
 4   pcnt_65_plus       8 non-null      float64
 5   poverty_rate       8 non-null      float64
 6   pcnt_insured       8 non-null      float64
 7   pcnt_bachelors     8 non-null      float64
 8   pcnt_low_income    8 non-null      float64
 9   pcnt_middle_class  8 non-null      float64
 10  pcnt_upper_class   8 non-null      float64
dtypes: float64(7), int64(1), str(3)
memory usage: 836.0 bytes

Combining NY-NJ data and CT data¶

In [39]:
appended_df = pd.concat ([merged_nynj, agg_ct])
appended_df.info()

<class 'pandas.DataFrame'>
Index: 91 entries, 0 to 7
Data columns (total 11 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   fips               91 non-null     str    
 1   county             91 non-null     str    
 2   state              91 non-null     str    
 3   total_pop          91 non-null     int64  
 4   pcnt_65_plus       91 non-null     float64
 5   poverty_rate       91 non-null     float64
 6   pcnt_insured       91 non-null     float64
 7   pcnt_bachelors     91 non-null     float64
 8   pcnt_low_income    91 non-null     float64
 9   pcnt_middle_class  91 non-null     float64
 10  pcnt_upper_class   91 non-null     float64
dtypes: float64(7), int64(1), str(3)
memory usage: 8.5 KB
In [40]:
appended_df.head()
Out[40]:
fips county state total_pop pcnt_65_plus poverty_rate pcnt_insured pcnt_bachelors pcnt_low_income pcnt_middle_class pcnt_upper_class
0 36001 Albany NY 315374 17.8 12.9 97.0 41.6 29.9 39.1 12.1
1 36003 Allegany NY 47027 19.7 16.8 95.1 21.0 40.2 39.1 4.1
2 36005 Bronx NY 1419250 13.9 26.9 92.7 20.6 50.8 32.2 5.2
3 36007 Broome NY 197738 19.9 18.9 96.3 27.6 41.4 36.2 6.6
4 36009 Cattaraugus NY 76479 20.3 17.6 93.7 19.3 44.2 35.7 3.6
In [41]:
appended_df.tail()
Out[41]:
fips county state total_pop pcnt_65_plus poverty_rate pcnt_insured pcnt_bachelors pcnt_low_income pcnt_middle_class pcnt_upper_class
3 09007 Middlesex CT 164983 21.3 7.1 96.5 42.0 24.3 38.8 17.5
4 09009 New Haven CT 862028 18.3 11.8 94.9 35.5 31.4 36.4 14.6
5 09011 New London CT 267707 19.7 9.2 95.8 34.3 27.7 39.5 13.8
6 09013 Tolland CT 146907 17.9 8.5 96.3 37.5 24.4 37.4 18.3
7 09015 Windham CT 116156 18.2 11.3 95.6 23.2 29.4 40.5 9.8
In [42]:
appended_df.to_csv('../acs_data.csv', index=False)