<jupyter_start><jupyter_text>Modeling*<NAME>*  *Executive Summary*This section represents the **Data Preparation** and **Modeling** sections of the CRISP-DM process.  *Objectives*1. **[Feature Selection]** To prototype the selection of features and data processing required before modeling.2. **[Modeling]** To prototype various models (regression, correlation, clustering) as a means to better understand the data.  Setup<jupyter_code># Import libraries
import os 
import math
import pprint
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns 
import statsmodels.api as sm
from ipywidgets import * 
from sklearn.cluster import KMeans, AgglomerativeClustering
from sklearn.linear_model import LinearRegression
from sklearn import preprocessing
from sklearn.metrics import mean_squared_error
from sklearn.tree import DecisionTreeClassifier, plot_tree
from scipy.cluster.hierarchy import dendrogram
# Declare global variables
DATA_DIR = os.path.join('../data/processed')
DATA_FILE = os.path.join(DATA_DIR, 'processed.csv')
plt.style.use('ggplot')

CLUSTERING_COLS = ['leaid', 'year', 'lea_name', 'fips', 'number_of_schools', 
                  'teachers_total_fte', 'spec_ed_students', 
                  'enrollment_x',
                  'read_test_num_valid', 'read_test_pct_prof_midpt', 'math_test_num_valid',
                   'math_test_pct_prof_midpt', 'rev_total', 'exp_total']

## Set a target year for early analysis
TGT_YEAR = 2016
# Useful functions
def null_counter(df):
    record_nulls = []
    for col in df.columns:
        nulls = df[col].isnull().sum()
        percent_null = round((nulls / df.shape[0]) * 100, 2)
        record_nulls.append([col, nulls, percent_null])
    output = pd.DataFrame(record_nulls, columns=['Attribute', 'Null Count', '% Null'])
    return output

def get_year_range(df):
    year_range = list(df['year'].unique())
    year_range.sort()
    return year_range

def subset_by_states_only(df):
    df = df[df['fips'] <= 56]
    return df

def sound_off(df):
    nRow, nCol = cluster_df.shape
    print(f'There are {nRow} rows and {nCol} columns.')
    print('')

    YEAR_RANGE = get_year_range(cluster_df)
    print(f'Data spans the years {YEAR_RANGE[0]} to {YEAR_RANGE[-1]}.')
    print('')

    print('Available columns include:')
    display(null_counter(cluster_df))
    
def filter_out_factor(df, column_name):
    ## Identify records with null values in column
    bad_records = df[df[column_name].isnull()]
    bad_records.to_csv(f'missing_{column_name}.csv')

    ## Drop records with null values in column
    df = df[df[column_name].notnull()]
    return df<jupyter_output><empty_output><jupyter_text>Data PreparationIn this section we load the data, running various scripts to format the contents properly.***High-Level Overview***We tried to choose a subset of columns in which the data was mostly complete. That meant disqualifying rows that were:* ... not states (i.e. territories).* ... did not have reported scores for standardized tests.* ... were outliers (three school districts with >500 schools).* ... had flagged data (i.e. a "-2" value indicating that results could not be provided for privacy reasons)We were especially disappointed to have to remove "english_language_learners" from the modeling data. In literature this factor is frequently referred to as significant. But, more than 6,000 records in our limited set simply have no reported value for this metric. Interpolation was briefly considered but is not likely to be an accurate approach.**Normalization** was also performed in the last few cells. Given the large differences between districts with varying school counts, this was judged to be a necessary step.<jupyter_code># Load and preview data
## Isolate by specific columns
cluster_df = pd.read_csv(DATA_FILE)[CLUSTERING_COLS]
## Filter out non-state records
cluster_df = subset_by_states_only(cluster_df)
## Filter by year
cluster_df = cluster_df[cluster_df['year'] == TGT_YEAR]

sound_off(cluster_df)
# Convert negative values (flags) to Null
for col in cluster_df.columns[3:]:
    cluster_df[col] = cluster_df[col].apply(lambda x: np.nan if x<0 else x)
               
# sound_off(cluster_df)
# Remove records with missing values
for col in cluster_df.columns:
    cluster_df = filter_out_factor(cluster_df, col)

sound_off(cluster_df)
# Identify and remove outliers
plt.scatter(cluster_df['leaid'], cluster_df['number_of_schools'])

## Somewhat arbitrary threshold of >500 schools
outliers = cluster_df[cluster_df['number_of_schools'] > 500]
display(outliers)

print(cluster_df.shape)
cluster_df = cluster_df.drop(outliers.index)
print(cluster_df.shape)
# Remove the columns that won't be used as features
cluster_prepared_df = cluster_df.drop(['leaid', 'year', 'lea_name', 'fips'], axis=1)<jupyter_output><empty_output><jupyter_text>ClusteringThe purpose of this tool is specifically *descriptive* analytics. In short, we are looking to understand our underlying data, rather than build predictions. To that end, we try a few different setups to produce varying results. ***High-Level Overview**** An elbow chart of sum-of-squared-distances shows that a k value of 6 is well-suited for this clustering.* The resulting cluster is fairly uneven in size (13,126, 133, 38, 1,969, 11, 492). I wonder if it would be meaningful to run a second clustering on the largest category?* Clusterings seem to naturally correspond to number_of_schools. This seems reasonable, given that number_of_schools is going to influence most of the metrics used (enrollment, revenue, expenditure).<jupyter_code># Build an elbow chart to find a good cluster range
def build_elbow_chart(df, min_clusters=2, max_clusters=10, random_seed=777):
    ## Calculate sum of squared distances for each cluster
    ssd = []
    for k in range(min_clusters, max_clusters+1):
        kmeans_learner = KMeans(n_clusters=k, random_state=random_seed)
        kmeans_learner.fit(df)
        ssd.append(kmeans_learner.inertia_)

    ## Plot sum of squared distances
    plt.plot(range(min_clusters, max_clusters+1), ssd)
    plt.title('Elbow Chart')
    plt.xlabel('K')
    plt.ylabel('Sum of Squared Distances')
    
    
# Build a good cluster and view the resulting data
def build_cluster(df, k=6, random_seed=777):
    kmeans_learner = KMeans(n_clusters=k, random_state=random_seed)
    results = kmeans_learner.fit_predict(df)
    return results


# View the characteristics of each labeled dataset
def view_cluster(df, results, k=6):
    df['labels'] = results
    for i in range(0, k):
        subset = df[df['labels'] == i]
        subset.to_csv(f'labeled_{i}.csv')
        print(i)
        display(subset.describe())
        print()
# normed_data = pd.DataFrame(preprocessing.StandardScaler().fit_transform(cluster_prepared_df))

# build_elbow_chart(normed_data)
# results = build_cluster(normed_data, k=7)
# view_cluster(cluster_prepared_df, results, k=7)
# Place data into four categories, to prevent clustering from emphasizing size
single_df = cluster_prepared_df[cluster_prepared_df['number_of_schools'] == 1]

small_df = cluster_prepared_df[(cluster_prepared_df['number_of_schools'] > 1) & 
                               (cluster_prepared_df['number_of_schools'] <= 3)]

medium_df = cluster_prepared_df[(cluster_prepared_df['number_of_schools'] > 3) & 
                               (cluster_prepared_df['number_of_schools'] <= 10)]

large_df = cluster_prepared_df[(cluster_prepared_df['number_of_schools'] > 10)]

df_list = [single_df, small_df, medium_df, large_df]
counts = [x['number_of_schools'].count() for x in df_list]
print(counts)
# Normalize within clusters to detect patterns besides size
normed_df_list = []
for df in df_list:
        normed_df_list.append(preprocessing.StandardScaler().fit_transform(df))
build_elbow_chart(normed_df_list[0])
build_elbow_chart(normed_df_list[1])
build_elbow_chart(normed_df_list[2])
build_elbow_chart(normed_df_list[3])
results = []
for df in normed_df_list:
    results.append(build_cluster(df, k=4))
# Districts w/ One School
view_cluster(single_df, results[0], k=4)
# Districts w/ 2-3 Schools
view_cluster(small_df, results[1], k=4)
# Districts w/ 4-10 Schools
view_cluster(medium_df, results[2], k=4)
# Districts w/ One School
view_cluster(large_df, results[3], k=4)<jupyter_output>0<jupyter_text>Appendix A: Idea Graveyard ***Decision Tree***<jupyter_code># cluster_prepared_df['avg_midpoint'] = \
# cluster_prepared_df[['read_test_pct_prof_midpt', 'math_test_pct_prof_midpt']].mean(axis=1)

# features = cluster_prepared_df.drop(['math_test_pct_prof_midpt', 'read_test_pct_prof_midpt', 'avg_midpoint'], axis=1)
# labels = cluster_prepared_df['avg_midpoint']

# display(features.describe())
# tree_model = DecisionTreeClassifier(random_state=777, max_depth=3)
# discrete_labels = preprocessing.KBinsDiscretizer(n_bins=3, encode='ordinal').fit_transform(np.asarray(labels).reshape(-1, 1))
# tree_model.fit(features, discrete_labels)
# plt.figure(figsize=(15,15))
# plot_tree(tree_model)
# plt.show()<jupyter_output><empty_output><jupyter_text>***Nested Clustering***<jupyter_code># # Build an elbow chart to find a good cluster range
# def build_elbow_chart(df):
#     ## Set parameters
#     min_clusters = 2
#     max_clusters = 10
#     random_seed = 777

#     ## Calculate sum of squared distances for each cluster
#     ssd = []
#     for k in range(min_clusters, max_clusters+1):
#         kmeans_learner = KMeans(n_clusters=k, random_state=random_seed)
#         kmeans_learner.fit(df)
#         ssd.append(kmeans_learner.inertia_)

#     ## Plot sum of squared distances
#     plt.plot(range(min_clusters, max_clusters+1), ssd)
#     plt.title('Elbow Chart')
#     plt.xlabel('K')
#     plt.ylabel('Sum of Squared Distances')
    
# build_elbow_chart(cluster_prepared_df)
# # Build a good cluster and view the resulting data
# def build_cluster(df):
#     chosen_k = 6
#     random_seed = 777
#     kmeans_learner = KMeans(n_clusters=chosen_k, random_state=random_seed)
#     results = kmeans_learner.fit_predict(df)
#     return results

# results = build_cluster(cluster_prepared_df)
    
# # Attach our new clustering labels to the original dataframe
# cluster_df['labels'] = results
# # View the characteristics of each labeled dataset
# def view_cluster(df, chosen_k=6):
#     for i in range(0, chosen_k):
#         subset = df[df['labels'] == i]
#         subset.to_csv(f'labeled_{i}.csv')
#         print(i)
#         display(subset.describe())
#         print()
        
# view_cluster(cluster_df)
# ## Try a second clustering on the largest category
# sub_cluster_df = cluster_prepared_df[cluster_df['labels'] == 0]
# build_elbow_chart(sub_cluster_df)

# results = build_cluster(sub_cluster_df)
# sub_cluster_df['labels'] = results
# view_cluster(sub_cluster_df)<jupyter_output><empty_output><jupyter_text>***Hierarchical Clustering***<jupyter_code># # Build a hierarchical cluster
# n = 10
# h_cluster = AgglomerativeClustering(n_clusters=10, compute_distances=True)

# # Fit it to normalized data
# norm_data = preprocessing.StandardScaler().fit_transform(cluster_prepared_df)
# results = h_cluster.fit_predict(norm_data)

# # Visualize
# def plot_dendrogram(model, **kwargs):
#     # Create linkage matrix and then plot the dendrogram

#     # create the counts of samples under each node
#     counts = np.zeros(model.children_.shape[0])
#     n_samples = len(model.labels_)
#     for i, merge in enumerate(model.children_):
#         current_count = 0
#         for child_idx in merge:
#             if child_idx < n_samples:
#                 current_count += 1  # leaf node
#             else:
#                 current_count += counts[child_idx - n_samples]
#         counts[i] = current_count

#     linkage_matrix = np.column_stack([model.children_, model.distances_,
#                                       counts]).astype(float)

#     # Plot the corresponding dendrogram
#     dendrogram(linkage_matrix, **kwargs)

# plt.figure(figsize=(8,8))
# plot_dendrogram(h_cluster, truncate_mode='level', p=3)
# plt.xticks(rotation=-90)
# plt.savefig('dendro.png')

# # View the characteristics of each labeled dataset
# def view_cluster(df, results, k=6):
#     df['labels'] = results
#     for i in range(0, k):
#         subset = df[df['labels'] == i]
#         subset.to_csv(f'labeled_{i}.csv')
#         print(i)
#         display(subset.describe())
#         print()

# k = len(np.unique(results))
# view_cluster(cluster_df, results, k)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup