<jupyter_start><jupyter_text>**NOTE**This is a *draft* version of the notebook that is used for the workshop, and includes a lot of scratch work that was done to chart a path from the Census Data API web page through to reproducting the 2021 data in the 2022 press release on educational attainment and the corresponding infographic. This notebook has been retained to document a messy process. For the finished process, see the notebook in this repository, *Census-API-Tutorial.*  Census APIsRequest a key at An email will be sent to the addres you provide. The email will include a link to activate the key. It may take a few minutes before the key can be activated.<jupyter_code># Load API key

# Note the file with the API key is not in the GitHub repo.
# Keys need to be read from a local directory
with open("jwCensusApi", "r") as i:
    key = i.read()
import json
import requests
import pandas as pd
import matplotlib as plt
'''
Current Population Survey Annual Social and Economic Supplement
https://api.census.gov/data.html

Vars: https://api.census.gov/data/2021/cps/asec/mar/variables.html
'''

endpoint = "http://api.census.gov/data/2021/cps/asec/mar" # state geographies
ex1_url = "https://api.census.gov/data/2021/cps/asec/mar?get=NOW_COV,MARSUPWT,A_MARITL&for=state:35&A_HGA=39"
ex2_url = "https://api.census.gov/data/2021/cps/asec/mar?tabulate=weight(MARSUPWT)&col+NOW_COV&row+A_MARITL&for=state:35&A_HGA=39"
api_request_url = ex1_url + "&key=" + key
api_request = requests.get(api_request_url)
api_json = api_request.json()
# print(json.dumps(api_json, indent=4)) # long output<jupyter_output><empty_output><jupyter_text>Identifying the Data SourceWe are going to use the Census API to retrieve data to reproduce some of the statistics from the Feb 24, 2022, press release and infographic *A Higher Degree.***Press release:** **Infographic:** Below is the infographic. Note that for today's workshop we are using only 2021 data.[Source: U.S. Census Bureau] According to the press release, the data are from the *2000–2002 March Current Population Survey and 2003–2021 Annual Social and Economic Supplement to the Current Population Survey.*  Q: How to get info on variables via API?We can look it up online, but we may also get this info dynamically.  Steps:1. Go to the *Census Data API Discovery Tool*: 1. Preview the datasets and available information using the HTML format: 1. Using a process similar to the above, we can also retrieve the information in JSON format.<jupyter_code># Request the JSON information about available datasets

datasets_url = "https://api.census.gov/data.json"
datasets_request = requests.get(datasets_url)
datasets_json = datasets_request.json()
# this took a little sleuthing...

# Get the titles of the datasets
dataset_list = datasets_json["dataset"]

# title: Current Population Survey Annual Social and Economic Supplement
# c_vintage: 2021
# id: https://api.census.gov/data/id/CPSASEC2021

cpd_asec_mar = [d for d in dataset_list if d["identifier"] == "https://api.census.gov/data/id/CPSASEC2021"]
print(json.dumps(cpd_asec_mar, indent=4)) # long output
# Get info on variables

dataset_vars_url = cpd_asec_mar[0]["c_variablesLink"]
dataset_vars_request = requests.get(dataset_vars_url)
datasets_vars_json = dataset_vars_request.json()
# print(json.dumps(datasets_vars_json, indent=4)) # long output - many variables
# just print the variable and the label
dataset_vars = datasets_vars_json["variables"]
# sort variable names
sorted_vars = sorted(dataset_vars.keys())
for k in sorted_vars[:10]:
    var_info = dataset_vars[k]
    print(k, ":", var_info["label"])
# Maybe create dictionaries of vars:defs
# populate via iteration

var = "NOW_COV"
var_def_url = endpoint + "/variables/" + var
var_def_request = requests.get(var_def_url)
var_json = var_def_request.json()
print(json.dumps(var_json, indent=4))
'''
Variables of interest

A_HGA - educational attainment
AGE1 - age
AGI - federal adjusted gross income

NM FIPS ID = 35
'''

vars_list = ["A_HGA", "AGE1", "AGI"]
vars_dict = {}

for v in vars_list:
    var_def_url = endpoint + "/variables/" + v
    var_def_request = requests.get(var_def_url)
    var_json = var_def_request.json()
    vars_dict[v] = var_json
vars_dict
# API expects vars to be comma separated, no spaces

vars_query = ','.join(str(v) for v in vars_list)
print(vars_query)
# request non-tabulated data
# URL format: "https://api.census.gov/data/2021/cps/asec/mar?get=NOW_COV,MARSUPWT,A_MARITL&for=state:35"

# We can parameterize the state but for now we will hard code all and NM (35)
api_request_url = endpoint + "?get=" + vars_query + "&for=state:*&key=" + key
api_request = requests.get(api_request_url)
api_json = api_request.json()
type(api_json) # results seem to generally be lists, but check in case a dictionary is returned
census_df = pd.read_json(json.dumps(api_json), typ = "frame", orient = "values")
census_df.head()
census_df.columns = census_df.iloc[0]
census_df.head()
census_df = census_df.drop(census_df.index[0])
census_df.head()
census_df.info()
# We can separately request data for just NM, or we can subset
# recall that the FIPS code for NM is 35

nm_census_data = census_df[census_df["state"] == "35"].copy() # quotes around "35" b/c it is an object not an int
nm_census_data.info()
# out of curiosity let's get the NM data via API
# same as before with one different parameter

api_request_url = endpoint + "?get=" + vars_query + "&for=state:35&key=" + key
api_request = requests.get(api_request_url)
api_json = api_request.json()

nm_census_df = pd.read_json(json.dumps(api_json), typ = "frame", orient = "values")
nm_census_df.columns = nm_census_df.iloc[0]
nm_census_df = nm_census_df.drop(nm_census_df.index[0])
nm_census_df.info() # the data have the same number of rows
# Let's make a function to get the data and convert to data frame
# "https://api.census.gov/data/2021/cps/asec/mar?tabulate=weight(MARSUPWT)&col+NOW_COV&row+A_MARITL&for=state:35&A_HGA=39"
def get_census_data(endpoint_url, vars_list, key, state = "*"):
    # First, convert vars_list to string
    vars_query = ','.join(str(v) for v in vars_list)
    
    # Build the request URL
    api_request_url = endpoint_url + "?get=" + vars_query + "&for=state:" + state + "&key=" + key
    
    # Request the data
    api_request = requests.get(api_request_url)
    api_json = api_request.json()
    
    # Convert returned JSON to dataframe
    df = pd.read_json(json.dumps(api_json), typ = "frame", orient = "values")
    
    # Use first row values for col headers
    df.columns = df.iloc[0]
    
    # Drop first row, which is still header info
    df = df.drop(df.index[0])
    
    return df

# While we're at it, create a function to get var defs
# No key needed
def get_census_vars(vars_list):
    vars_dict = {}
    for v in vars_list:
        var_def_url = endpoint + "/variables/" + v
        var_def_request = requests.get(var_def_url)
        var_json = var_def_request.json()
        vars_dict[v] = var_json
    return vars_dict
# Now we can make ad hoc requests
# but we will redo our initial request

vars_list = ["A_HGA", "AGE1", "AGI"]
var_info = get_census_vars(vars_list)
census_data = get_census_data(endpoint, vars_list, key) # use default * for all state data
census_data.info()
# try again with just NM
# census_data = get_census_data(endpoint, vars_list, key, state = "35") # NM
# census_data.info()
# Things work - let's see about reproducing stats from the press release
# Right now we have age and education attainment (and income) for 2021

'''
In 2021, the highest level of education of the population age 25 and older in the United States 
was distributed as follows: 

    8.9% had less than a high school diploma or equivalent.
    27.9% had high school graduate as their highest level of school completed. 
    14.9% had completed some college but not a degree.
    10.5% had an associate degree as their highest level of school completed.
    23.5% had a bachelor’s degree as their highest degree.
    14.4% had completed an advanced degree such as a master’s degree, professional degree or doctoral degree. 
'''

var_info
# First subset to AGE1 values >= 6 (25 and older)

# convert cols to int
census_data = census_data.astype(int)
census_data.info()
age_gte25 = census_data[census_data["AGE1"] >= 6].copy()
age_gte25.info()
age_gte25.head()
var_info
# convert A_HGA, AGE1, and state to categories
age_gte25.A_HGA = age_gte25.A_HGA.astype("category")
age_gte25.AGE1 = age_gte25.AGE1.astype("category")
age_gte25.state = age_gte25.state.astype("category")
age_gte25.info()
type(age_gte25.iloc[0, 0])
age_gte25.iloc[0, 0]
# so cols are categories but cell values are still int (?)

# Educational attainment: <= 38 less than HS diploma or eq
# 39 == HS diploma, 40 == some college ND, 41 & 42 == assoc Deg, 43 == Bachelor's, 
# 44 & 45 Masters or professional degree, 46 == doctorate

# there is probably a faster way to do this
ed_groups = age_gte25.groupby("A_HGA")
#ed_groups.groups
# 27.9% HS grads per press release
hs_grads = ed_groups.get_group(39)
len(hs_grads)
(30656 / 108998) * 100
# 14.9% had some college

some_college_nd = ed_groups.get_group(40)
len(some_college_nd)
(16369/108998) * 100
# 31-38 for no HS
# 8.9% report less than HS diploma or eq

no_diploma = 0
for i in range(31, 39):
    g = ed_groups.get_group(i)
    no_diploma += len(g)
no_diploma
(10289/108998) * 100
# Values are different from press release - try weighted data

census_wtd_url = "https://api.census.gov/data/2021/cps/asec/mar?tabulate=weight(MARSUPWT)&col+A_HGA&row+AGE1&for=state:*"
census_wtd_request = requests.get(census_wtd_url)
census_wtd_json = census_wtd_request.json()
#print(json.dumps(census_wtd_json, indent=4)) # long output
# Convert returned JSON to dataframe
drop_keys = census_wtd_json[1:]
get_keys = census_wtd_json[0]
w_df = pd.read_json(json.dumps(drop_keys), typ = "frame", orient = "values")
cols = []
for k in get_keys:
    if isinstance(k, dict):
        v = [e[:] for e in k.values()]
        cols.append(v[0])
    if isinstance(k, str):
        cols.append(k)
w_df.columns = cols
w_df.set_index("AGE1", inplace = True)
print(w_df.info())
w_df.sort_index(inplace = True)
w_df.head()
w_df.loc[6:]
w_df.to_numpy().sum()
w_df[6:].to_numpy().sum()
sum_age_gte25 = w_df[6:].to_numpy().sum()
# all pop >= 25, HS diploma only (A_HGA == 39)
w_df[6:]["39"]
w_df[6:]["39"].sum()
hs_diploma = w_df[6:]["39"].sum()
round((hs_diploma/sum_age_gte25) * 100, 1) # This is the correct %
# some college, no degree (40)
round((w_df[6:]["40"].sum()/sum_age_gte25) * 100, 1) # also the correct &
# no HS diploma
no_hs_diploma = w_df[6:][["31", "32", "33", "34", "35", "36", "37", "38"]].to_numpy().sum()
no_hs_diploma
round((no_hs_diploma/sum_age_gte25) * 100, 1) # also correct
w_df[6:].plot.bar(stacked = True, legend = False) # ed attainment stacked bar plot (x = age, y = count)
'''
    '44': "Master's degree (MA,MS,MENG,MED,MSW,MBA)",
    '43': "Bachelor's degree (BA,AB,BS)",
    '45': 'Professional school degree (MD,DDS,DVM,L',
    '46': 'Doctorate degree (PHD,EDD)',
'''
w_df[6:][["43", "44", "45", "46"]].plot.bar(stacked = True).legend(loc = "best", bbox_to_anchor = (-0.05, -0.05))
w_df[6:][["43", "44", "45", "46"]].sum().plot.bar(legend = False) # close! - y scale is 10m
w_df[6:][["43", "44", "45", "46"]].sum()
plot_data = pd.DataFrame(w_df[6:][["43", "44", "45", "46"]].sum())
type(plot_data)
plot_data
plot_data.columns = ["2021"]
round(plot_data.iloc[0]["2021"]/1000000, 1)
l = ["Bachelor's Degre: " + str(round(plot_data.iloc[0]["2021"]/1000000, 1)), 
     "Master's Degree: " + str(round(plot_data.iloc[1]["2021"]/1000000, 1)), 
     "Professional School Degree: " + str(round(plot_data.iloc[2]["2021"]/1000000, 1)),
     "Doctorate Degree: " + str(round(plot_data.iloc[3]["2021"]/1000000, 1))]
plot_data.T.plot.bar(stacked=True).legend(l, loc = "best", bbox_to_anchor = (-0.05, -0.05))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1275223980574395, 'fraction_numerical': 0.0387674788579084, 'mean_word_length': 4.761698022190063, 'pattern_counts': {'":': 1, '<': 10, '<?xml version=': 0, '>': 12, 'https://': 10, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '651088', 'license': 'Apache-2.0', 'n_tokens_mistral': 4600, 'n_tokens_neox': 4239, 'n_words': 1243, 'path': 'Census-API-Tutorial-Scratch.ipynb', 'repo_name': 'unmrds/cc-census-api', 'size': '11943'}