# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Query and consolidate median household income and population data by Illinois census tract

# +
# packages
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as tkr
import os
from datetime import datetime
import requests, json

# display all columns and rows
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

# display plots in ipynb
# %matplotlib inline
# -

# # Median household income

# request data from census api
r = requests.get("https://api.census.gov/data/2017/acs/acs5?get=NAME,B19013_001E&for=tract:*&in=state:17&in=county:*")

# json to dictionary
income_data = r.json()
income_data[:5] # looking at first five elements

# +
# convert dictionary to df
income_df = pd.DataFrame(income_data[1:], columns = income_data[0]).rename(columns={
    "B19013_001E":"median_household_income"})

# display first five rows
income_df.head()
# -

# split the NAME column on the comma
income_df[["census_tract_name", "county_name", "state_name"]] = income_df["NAME"].str.split(", ", expand = True)

# now that the column is split, remove "NAME" column
del income_df["NAME"]

# create a fips (five digit code for the county) column
income_df["fips"] = income_df["state"] + income_df["county"]

# #### create a census tract code (this consists of state|county|tract)

# create a census tract code by pasting 'state', 'county' and 'tract' columns
income_df['census_tract'] = income_df['state'] + income_df['county'] + income_df['tract']

# display first five rows
income_df.head()

# info on the table
income_df.info()

# median_household_income to numeric
income_df["median_household_income"] = pd.to_numeric(income_df["median_household_income"])

# descriptive stats on the table
# .apply portion removes scientific notation
income_df.describe().apply(lambda s: s.apply(lambda x: format(x, 'f')))

# #### It was a bit surprising to see a min value of -666666666.000000. Code below will show what values appear below 0.

# +
# unique median_household
print(income_df.median_household_income[income_df.median_household_income < 0].unique())

# count of said unique values under 0
print(income_df.median_household_income[income_df.median_household_income < 0].value_counts())
# -

# what census_tract values have this median household income?
income_df[income_df.median_household_income.isin([-666666666])]

# +
# do any of those census_tract values appear more than once?
negative_census_tract = income_df[income_df.median_household_income.isin([-666666666])]['census_tract']

# sum ocurrence of these census tracts across entire df
income_df['census_tract'].isin(negative_census_tract.tolist()).sum()
# -

# #### So it was just that one very negative value, and it occurred for 13 records. The code below will filter out the -666666666 from the df.

# subset income_df to rows with median_household_income > 0
income_df_sub = income_df.query('median_household_income > 0')
income_df_sub.shape

# # Bring in population

# second request - this time for population
r2 = requests.get("https://api.census.gov/data/2017/acs/acs5?get=NAME,B01001_001E&for=tract:*&in=state:17&in=county:*")
print(r2)

# +
# json to dictionary
pop_data = r2.json()
pop_data[:5] # looking at first five rows

# convert dictionary to df
pop_df = pd.DataFrame(pop_data[1:], columns = pop_data[0]).rename(columns={
    "B01001_001E":"population"})

# display first five rows
pop_df.head()
# -

# #### As with median household income, split the "NAME" column.

# split the NAME column on the comma
pop_df[["census_tract_name", "county_name", "state_name"]] = pop_df["NAME"].str.split(", ", expand = True)

# now that the column is split, remove "NAME" column
del pop_df["NAME"]

# create a fips (five digit code for the county) column
pop_df["fips"] = pop_df["state"] + pop_df["county"]

# #### Also, as with income, create a census tract number from state, county, and tract

pop_df['census_tract'] = pop_df['state'] + pop_df['county'] + pop_df['tract']

# +
# info
print(pop_df.info())

# display first five rows as well
pop_df.head()
# -

# population to numeric
pop_df["population"] = pd.to_numeric(pop_df["population"])

# descriptive stats on table
pop_df.describe(include='all')

# How many census tracts have 0 people?

# sort by lowest population
pop_df['population'].value_counts().reset_index().sort_values(by = 'index').rename(
    columns = {'index':'population', 'population':'record count'})[:10]


# ## Combine income and pop tables

# Code below will allow for displaying multiple objects from one cell output. Code pulled from [Jake Vanderplas' book](https://jakevdp.github.io/PythonDataScienceHandbook/03.06-concat-and-append.html)

class display(object):
    """Display HTML representation of multiple objects"""
    template = """<div style="float: left; padding: 10px;">
    <p style='font-family:"Courier New", Courier, monospace'>{0}</p>{1}
    </div>"""
    def __init__(self, *args):
        self.args = args
        
    def _repr_html_(self):
        return '\n'.join(self.template.format(a, eval(a)._repr_html_())
                         for a in self.args)
    
    def __repr__(self):
        return '\n\n'.join(a + '\n' + repr(eval(a))
                           for a in self.args)


# display first five rows of income and population dataframes
display('income_df_sub.head()', 'pop_df.head()')

# is the tract a unique key?
display("pop_df['census_tract'].value_counts().reset_index().head()",
        "income_df['census_tract'].value_counts().reset_index().head()") 

# join two tables on 'tract' and 'fips'
combined = pd.merge(income_df_sub, pop_df, on = 'census_tract', how = 'left',
                   suffixes = ('_income_df', '_pop_df'))
print(combined.shape)
combined.head()

# # Explore combined table

# looking at histogram of median household income in Illinois 
combined.median_household_income.hist()

# what is the median value?
combined.median_household_income.median()

# Important caveat on above median: It is the median value looking at tract level, but would be different if looking statewide (about $62,000).

# What are the unique values in the FIPS column?

# +
# count of unique values in 'fips' column
print(combined['fips_income_df'].nunique())

# unique values in 'fips' column
print(combined['fips_income_df'].unique())
# -

# What census tract has the highest income?

# return row with max value for 'median_household_income'
combined.iloc[combined['median_household_income'].idxmax()]

# # Final clean up and export

combined.head()

# #### Define columns to keep and select those columns

combined.columns

# +
# mask to be used to filter
cols_to_keep = ['fips_income_df', 'tract_income_df', 'census_tract', 'county_name_income_df',
                'state_name_income_df', 'median_household_income', 'population']

# filter on above mask
combined_2 = combined[cols_to_keep]
# -

# display first five rows
combined_2.head()

# +
# rename the columns
combined_final = combined_2.rename(columns = {'fips_income_df': 'fips',
                                              'tract_income_df': 'tract',
                                              'county_name_income_df': 'county_name',
                                              'state_name_income_df': 'state'})

# print shape of final file
print(combined_final.shape)

# display first five rows
combined_final.head()
# -

# #### Write above table to CSV

# write to outputs folder (one level up, then outputs)
# using to_excel to maintain 'fips', 'tract', and 'census_tract' columns as character
combined_final.to_excel("../outputs/Illinois_Median_Household_Income_and_Population_by_Tract.xlsx",
                        index = False)









StarcoderdataJupyterScriptsDedupFiltered