<jupyter_start><jupyter_text>Housing Inventory - Explore Foremost, let's get the data staged in our dataframe.<jupyter_code>import sqlite3
import pandas as pd
from tabulate import tabulate

def create_connection(db_file: str) -> list:
    """
    Create a database connection to the SQLite database specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    cur = None
    try:
        conn = sqlite3.connect(db_file)
        cur = conn.cursor()
    except sqlite3.Error as e:
        print(e)
    return [conn, cur]


def close_connection(db_conn: sqlite3.Connection) -> None:
    return db_conn.close()


db_file = 'housing_inventory.db'
db_con, db_cur = create_connection(db_file)

query = """
WITH mortgage_monthly(year_month, mortgage_rate) AS (
    SELECT year_month, avg(mortgage_rate) from mortgage_rates
    group by year_month
)
SELECT  hi.total_listing_count as 'housing_inventory', bp.total_units as 'housing_permits',
        mm.mortgage_rate, pr.prime_rate, rc.credit, hi.cbsa_code, mm.year_month
FROM housing_inventory as hi
INNER JOIN building_permits bp
    on hi.year_month = bp.year_month and hi.cbsa_code = bp.cbsa_code
INNER JOIN mortgage_monthly mm
    on hi.year_month = mm.year_month
INNER JOIN prime_rates pr
    on hi.year_month = pr.year_month
INNER JOIN revolving_credit rc
    on hi.year_month = rc.year_month
"""
result2 = list(db_con.execute(query))
df = pd.read_sql_query(sql=query,con=db_con)
close_connection(db_con)<jupyter_output><empty_output><jupyter_text>Let's check the count of observations before we start looking at both single and paired variables analysis in-depth.<jupyter_code>print('We have {a} observations in our aggregate dataset.'.format(a=df.shape[0]))<jupyter_output>We have 18534 observations in our aggregate dataset.<jupyter_text>**EDA helper functions**<jupyter_code>import math
import matplotlib.pyplot as plt
import scipy.stats as stats
import pandas as pd
import seaborn as sns
sns.set(style="whitegrid")

def freeman_diaconis(data):
    quartiles = stats.mstats.mquantiles( data, [0.25, 0.5, 0.75])
    iqr = quartiles[2] - quartiles[ 0]
    n = len( data)
    h = 2.0 * (iqr/n**(1.0/3.0))
    return int( h)

def plot_hist_categorical(t_col: pd.Series, do_normalize: bool):
    data = t_col.value_counts(normalize=do_normalize)
    x = list(data.index.sort_values())
    width = 1 / 1.5
    figure = plt.figure(figsize=(8, 6))

    axes = figure.add_subplot(1, 1, 1)
    axes.bar(x, data, width, align="center", color="darkslategray")
    axes.set_xticks(x)
    axes.set_xticklabels(data.axes[0])
    axes.set_title(' '.join(['Distribution of', t_col.name.title()]))
    axes.set_xlabel(t_col.name.title())
    axes.set_ylabel('Percent' if do_normalize else 'Count')
    axes.xaxis.grid(False)

    plt.show()
    plt.close()
    return

def plot_hist_numeric(t_col: pd.Series, backup_step=2, multiplier_factor = 1):
    mn = int(t_col.min())
    mx = int(t_col.max())
    h = freeman_diaconis(t_col) * multiplier_factor
    if h == 0: h = backup_step
    bins = [i for i in range(mn, mx, h)]

    figure = plt.figure(figsize=(10, 6))
    axes = figure.add_subplot(1, 1, 1)
    axes.hist(t_col, bins=bins, color="darkslategray")
    axes.set_title(' '.join([t_col.name.title(), 'Distribution']))
    axes.set_xlabel(t_col.name.title())

    plt.show()
    plt.close()
    return

def plot_hist_numeric_custom(t_col: pd.Series, bins: list):
    figure = plt.figure(figsize=(10, 6))
    axes = figure.add_subplot(1, 1, 1)
    axes.hist(t_col, bins=bins, color="darkslategray")
    axes.set_title(' '.join([t_col.name.title(), 'Distribution']))
    axes.set_xlabel(t_col.name.title())

    plt.show()
    plt.close()
    return

def get_correlations(df: pd.DataFrame, colA: str, colB: str) -> dict:
    results = {}
    results['pearson'] = stats.pearsonr(df[colA], df[colB])[0]
    results['spearman'] = stats.spearmanr(df[colA], df[colB])[0]
    return results

def describe_by_category(my_data: pd.DataFrame, numeric: str, categorical: str, transpose=False):
    t_grouped = my_data.groupby(categorical)
    t_grouped_y = t_grouped[numeric].describe()
    if transpose:
        print(t_grouped_y.transpose())
    else:
        print(t_grouped_y)
    return t_grouped

def plot_scatter(my_data: pd.DataFrame, y_col: str, x_col: str):
    figure = plt.figure(figsize=(8, 6))
    axes = figure.add_subplot(1, 1, 1)
    axes.scatter(y=my_data[y_col], x=my_data[x_col], marker='o', color='darkslategray')
    axes.set_ylabel(y_col.title())
    axes.set_xlabel(x_col.title())
    axes.set_title(' '.join([y_col, 'vs.', x_col]))

    plt.show()
    plt.close()

def plot_by_category(my_data: pd.DataFrame, response_col: str, explanatory_col: str, relative: bool):
    n_cols = 3
    h = freeman_diaconis(my_data[response_col])
    grouped = my_data.groupby(explanatory_col)
    figure = plt.figure(figsize=(20, 6))

    n_rows = math.ceil(grouped.ngroups / n_cols)

    for plot_index, k in enumerate(grouped.groups.keys()):
        axes = figure.add_subplot(n_rows, n_cols, plot_index + 1)
        axes.hist(grouped[response_col].get_group(k), bins=h, color="darkslategray", density=relative, range=(0, 40))
        axes.set_title(
            ' '.join([str(k), explanatory_col.title(), '-', response_col.title(), '\ndistribution - <NAME>']))
        axes.set_xlabel(response_col)

    figure.tight_layout()
    plt.show()
    plt.close()
    return<jupyter_output><empty_output><jupyter_text>1. Housing Inventory - EDA: Single Variable Analysis- [housing_inventory](housing_inventory)- [housing_permits](housing_permits)- [mortgage_rate](mortgage_rate)- [credit](credit)- [prime_rate](prime_rate)- [cbsa_code](cbsa_code)  1.1 housing_inventoryFirst let's look at the housing inventory numbers. This is our explanatory variable, the total number of houses in inventory across CBSA codes for a year/month combination (note: we did use the month/year to associate records, but the time is irrelevant to the scope of this analysis and is, therefore, omitted). For purposes of this analysis, the housing inventory numbers come from the monthly 'total listing count' which the total of both active and pending (offers accepted by deal not closed) by month per geographic area. [1]<jupyter_code>df['housing_inventory'].describe()<jupyter_output><empty_output><jupyter_text>The mean is quite a bit to the right of the median; so, there is a strong positive/right skew to this data.<jupyter_code>plot_hist_numeric(df['housing_inventory'], multiplier_factor=25)<jupyter_output><empty_output><jupyter_text>This is much heavier skewed to the right than the tabular data indicated. Looking across all geographic areas, we can we there are many months when there are well less than 1,000 units available to buy or to rent.  1.2 housing_permitsHousing permits the subset of building permits (the approval given by a local governing entity across the United States) to commence with new constructtion.[2] Essentially building permits are a check to ensure that licensed contractors are going to be performing the work aligned with zoning restrictions and building codes. Let's look at the housing_permits. This is the number of housing permits per time period per cbsa code (geeographic area).<jupyter_code>df['housing_permits'].describe()<jupyter_output><empty_output><jupyter_text>Again, the mean number of housing permits is quite a bit larger than the median, so we can expect this to strongly right-skewed again.<jupyter_code>plot_hist_numeric(df['housing_permits'], multiplier_factor=25)<jupyter_output><empty_output><jupyter_text>This has a very similar shape to the housing_inventory variable, but it is important to note the housing permit numbers are an order of magnitude less than the housing inventory numbers.  1.3 mortgage_rateMortgage rates are the loans individuals take out to purchase home. Federal Home Loan Mortgage Corporation (FHLMC), aka <NAME>, is a large, government-owned but publicly traded corporation that extends loans to many individuals. In addition to offering mortgage loans, they also do a weekly summary of mortgage rates of different types called the _Primary Mortgage Market Survey_.[3] For purposes of this analysis we focus on their aggregation and averaging of 30-year fixed rated mortgages.<jupyter_code>df['mortgage_rate'].describe()<jupyter_output><empty_output><jupyter_text>For the mortgage rates, the mean is a little less than median, so we can expect this to have a slight negative skew.<jupyter_code>plot_hist_numeric_custom(df['mortgage_rate'],[2.0, 2.5, 3, 3.5, 4, 4.5, 5])<jupyter_output><empty_output><jupyter_text>Looking at the visualization above, we can see that is the case as there is a slight negative skew with the most prevlant rate being between 3.5 and 4.0%.  1.4 prime_rateThe prime rate is an interest rate actually determined by individual banks and not the Federal Reserve itself; rather the Federal Reserve Board reports the 'prime rate' reported by the majority of the 25 largest banks. [4] Many of the other interest rates use the prime rate as their foundational rate on which to build.<jupyter_code>df['prime_rate'].describe()<jupyter_output><empty_output><jupyter_text>The median and mean of the prime rate are really close around 4.1-4.2%; so, we might expect these rates to be normally distributed.<jupyter_code>#plot_hist_numeric(df['prime_rate'], backup_step=.25)
plot_hist_numeric_custom(df['prime_rate'], [3, 3.5, 4, 4.5, 5, 5.5,6])<jupyter_output><empty_output><jupyter_text>The visualization fo the prime rate is definitely not normally distributed; in fact is nearly appears to be bi-modal. Curiously the plot for the mortgage rate and the prime rates are not twin images as one might expect. Often 'folksy-wisdom' asserts that the mortgage rates always follow the prime rates; however, we can immediately see that the prime rates are actually greater than the mortgage rates.  1.5 creditThe Federal Reserve defines consumer credit as "... most credit extended to individuals, excluding loans secured by real estate; includes receivables carried on the balance sheet of the institution as well as outstanding balances of pools upon which securities have been issued."[5] In aggregate millions of dollars per month, this essentially reflects how much credit, in aggregate, the individuals are using each month. More credit used means more bills that will need to be paid and cannot be obligated to housing costs.<jupyter_code>df['credit'].describe()<jupyter_output><empty_output><jupyter_text>Looking at the summary revolving credit information, the mean is larger than the median; so, we would expect some positive, right-skewness, to the plot.<jupyter_code>#plot_hist_numeric(df['credit'])
plot_hist_numeric_custom(df['credit'], [850000, 900000, 950000, 1000000, 1050000, 1100000])<jupyter_output><empty_output><jupyter_text>Aligned with expectations, the visualization above is decidedly right-skewed.  1.6 cbsa_codeThe Core-Based Statistical Area codes are a set of metropolitan identifiers set by the Office of Management and Budget. [6] These are key to many of the macro-level economic calculations across the United States. For this analysis, these represent the geographic breakdowns of housing inventory data. While there also combined statistical areas (CSAs), this analysis leverages the CBSA codes as defined by OMB in 2015 (based on the 2010 Census). The total set of CBSA codes i s 935; so, let's see how many unique CBSA codes we have in our data set.[7]<jupyter_code>print('Across this dataset, there are {a} unique CBSA codes'.format(a=df['cbsa_code'].nunique()))<jupyter_output>Across this dataset, there are 383 unique CBSA codes<jupyter_text>It looks like we have 383 unique CBSA codes in our dataset. This is substantially less than the 935 total. Let's do a quick check against the CBSA codes in the raw housing inventory data.<jupyter_code>db_file = 'housing_inventory.db'
db_con, db_cur = create_connection(db_file)

query = """
SELECT count(distinct(cbsa_code)) from housing_inventory
"""
result = list(db_con.execute(query))
tabulate(result, headers=['Unique CBSA Codes'], tablefmt='html')<jupyter_output><empty_output><jupyter_text>Well 383 is definitely less than 917. This is probably do to the manner in which we inner-join across multiple tables to minimize missing data. In the future is would be good to re-analyze with meaningful approxiations/estimates instead of functionally dropping the entire observation. Let's look at the number for each CBSA code we do have in our final aggregate dataset.<jupyter_code>df['cbsa_code'].value_counts().sort_values(ascending=False)<jupyter_output><empty_output><jupyter_text>Looking at the counts, 49 is the expected number (12 months * 4 years + 1 extra month of data). 22 is interesting as is implies there are time-frames when certain areas did not provide data.  2. Housing Inventory - EDA: Paired Variable AnalysisMoving from the single variable analysis to the paired analysis, the relationships we are exploring to explain housing inventory across multiple datasets as expressed in the [Housing Inventory - Ask](Housing%20Inventory%20-%20Ask.ipynb) notebook, we should really look at correlations between each of the explanatory variables we've included- [housing_permits](v_housing_permits)- [mortgage_rate](v_mortgage_rate)- [credit](v_credit)- [prime_rate](v_prime_rate)- [cbsa_code](v_cbsa_code)  2.1 housing_inventory vs. housing_permitsRealistically, there should be some lag between the number of housing permits and the number of houses available in inventory at any point in time. Depending on particular localities with respect to work force availability and building supplies, it could take anywhere from 3 weeks to 6 months from the time a building permit is awarded to the time it is available in inventory. This caveat aside, given the time-agnostic focus of this study, we might expect the there to be a noticeable trend between the number of building permits issued and a rise in housing inventory. Let's check the correlation coefficients.<jupyter_code>corr = get_correlations(df=df, colA='housing_inventory', colB='housing_permits')
print('Pearson\'s correlation coefficient is {a}\n\t and Spearman\'s rho is {b}'.format(a=round(corr['pearson'], 3),
                                                                                        b=round(corr['spearman'], 3)))<jupyter_output>Pearson's correlation coefficient is 0.772
	 and Spearman's rho is 0.789<jupyter_text>Both numeric measures indicate a strong positive correlation between housing inventory and housing permits. Let's check the scatter plot.<jupyter_code>plot_scatter(my_data=df, y_col='housing_inventory', x_col='housing_permits')<jupyter_output><empty_output><jupyter_text>While the plot is not the cleanest, there is still a clear positive correlation between the number of building permits issued and the number of houses in inventory.  2.2 housing_inventory vs. mortgage_rateGiven that higher mortgage rates lead to fewer people being able to afford new home loans (and secondarily potentially causing landlords to raise rent), we would expect a positive correlation between mortgage rates and housing inventory. Restated, the higher the mortgage rates, the more housing inventory we should expect. Let's check the correlation coefficients.<jupyter_code>corr = get_correlations(df=df, colA='housing_inventory', colB='mortgage_rate')
print('Pearson\'s correlation coefficient is {a}\n\t and Spearman\'s rho is {b}'.format(a=round(corr['pearson'], 3),
                                                                                        b=round(corr['spearman'], 3)))<jupyter_output>Pearson's correlation coefficient is 0.014
	 and Spearman's rho is 0.027<jupyter_text>Mathematically, it looks like this is a very weak positive correlation. It is only weakly linear, and it is barely monotonic at that. Let's check the scatter plot.<jupyter_code>plot_scatter(my_data=df, y_col='housing_inventory', x_col='mortgage_rate')<jupyter_output><empty_output><jupyter_text>This is interesting in that is illustrates there is almost no relationship between mortgage rates and housing inventory. While there are some outliers of high-inventory at low interest rates, on the whole, there is only a very small relationship between the mortgage rate and the available housing inventory.  2.3 housing_inventory vs. creditOutstanding revolving credit would be expected to have weak direct relation to an increase in housing inventory. If more people are taking on more debt (not associated with real estate), then that can be expected to correlate with fewer house purchases and a slight increase in housing inventory. Let's check the correlation coefficients.<jupyter_code>corr = get_correlations(df=df, colA='housing_inventory', colB='credit')
print('Pearson\'s correlation coefficient is {a}\n\t and Spearman\'s rho is {b}'.format(a=round(corr['pearson'], 3),
                                                                                        b=round(corr['spearman'], 3)))<jupyter_output>Pearson's correlation coefficient is -0.004
	 and Spearman's rho is -0.029<jupyter_text>Very interesting as there appears to be **very slight** negative correlation; this would imply there is actually a slight decrease in housing inventory when people accumulate credit card debt. Perhaps the builders and other potential seller anticipate a tightening of the market and don't build as many or put their own house on the market, respectively. Moreover, aligned with this, Spearman's $\rho$ is also slightly negative; so, slightly monotonicly decreasing. Let's look at the scatter plot.<jupyter_code>plot_scatter(my_data=df, y_col='housing_inventory', x_col='credit')<jupyter_output><empty_output><jupyter_text>This plot is very similar in appearance to the housing inventory vs. mortgage rates visualization. There is no apparent correlation between the overall amount of outstanding credit (also, recall that each unit of credit is 1,000,000, to 1.1E6 on the graph is actually $1.1E12 across the population of the United States) and amount of housing inventory available.  2.4 housing_inventory vs. prime_rateGiven the prime rate actually the average rate of what large banks use for lending money and given that mortgage interest is a substantial revenue stream for most banks and credit unions, one would expect the prime rate and mortgage rate to be correlated strongly. Resultantly, here, we would expect the prime rate to look very similar to the mortgage rate. Let's check the correlation coeffients.<jupyter_code>corr = get_correlations(df=df, colA='housing_inventory', colB='prime_rate')
print('Pearson\'s correlation coefficient is {a}\n\t and Spearman\'s rho is {b}'.format(a=round(corr['pearson'], 3),
                                                                                        b=round(corr['spearman'], 3)))<jupyter_output>Pearson's correlation coefficient is 0.01
	 and Spearman's rho is 0.03<jupyter_text>As expected, the prime rate has almost the exact correlation coefficient and Spearman's $\rho$ as the prime rate. Let's check the scatter plot.<jupyter_code>plot_scatter(my_data=df, y_col='housing_inventory', x_col='prime_rate')<jupyter_output><empty_output><jupyter_text>As expected, the shape of the prime rate visualization is very similar to the shape of the mortgage rate visualization against housing inventory as the response variable. The only real difference of note is that the mortgage rate is actually slightly **lower** than the prime rate. At the surface, this does not make great sense as many of the advertisements for lending (credit cards, in particular) talk in terms of the prime rate plus a percentage. This is due to mortgage rates for 30-year fixed rate mortgages (like we are working to explain in this analysis) are to the 10-year Treasury bond rate.[8]  2.5 housing_inventory vs. cbsa_codeAs we are using raw CBSA codes without additional area aggregation, there is no expectation to identify codes with surge or decline in housing inventory. This caveat aside, let's tabulate the housing inventory by cbsa code.<jupyter_code>t_grouped = df.groupby('cbsa_code')
t_grouped_y = t_grouped['housing_inventory'].describe().sort_values('max', ascending=False)
tabulate(t_grouped_y, headers=['cbsa','count','mean', 'sd', 'min','Q1','Q2','Q3','max'], tablefmt='html')<jupyter_output><empty_output><jupyter_text>Let's lookup the top three to see where they are at:<jupyter_code>db_file = 'housing_inventory.db'
db_con, db_cur = create_connection(db_file)

query = """
SELECT distinct(cbsa_title)
from housing_inventory
WHERE cbsa_code = 35620
   OR cbsa_code = 33100
   OR cbsa_code = 16980

"""
result = list(db_con.execute(query))
tabulate(result, headers=['CBSA Title'], tablefmt='html')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08541367766618843, 'fraction_numerical': 0.014155906264945003, 'mean_word_length': 4.900395033860045, 'pattern_counts': {'":': 0, '<': 101, '<?xml version=': 0, '>': 104, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '516654', 'license': 'Apache-2.0', 'n_tokens_mistral': 6410, 'n_tokens_neox': 5935, 'n_words': 2271, 'path': 'Housing Inventory - Explore.ipynb', 'repo_name': 'tstuckey/housing-inventory', 'size': '20925'}