Should Your Kids go to Charter School?

import pandas as pd
import sqlite3
import numpy as np 
import matplotlib.pyplot as plt 
import matplotlib.patches as mpatches
from scipy.stats import relfreq
from scipy.stats import ttest_ind
from scipy.stats import f_oneway


df_ca = pd.read_csv("ca2017.txt")
df_ca_entities = pd.read_csv("ca2017_entities.txt")

df_ca.columns = df_ca.columns.str.replace(' ','_' ).str.lower()
df_ca_entities.columns = df_ca_entities.columns.str.replace(' ','_' ).str.lower()


#remove entries with missing data 
df_ca = df_ca[df_ca.total_tested_at_entity_level != '*']
df_ca = df_ca[df_ca.percentage_standard_met != '*']

df_ca.head()

#change dtype from object to float
df_ca = df_ca.astype({col: float for col in df_ca.columns[11:]})

df_ca.iloc[:,:-12].describe()

#Create dbs from caaspp data 
conn = sqlite3.connect('ca2017.db')

df_ca.to_sql('caasp', conn, if_exists = 'replace')
df_ca_entities.to_sql('entities', conn, if_exists = 'replace')

#query schools in the 6, 7, 8 grades where 100% of students either met or did not met standards

q = """SELECT c.school_code
        , e.county_name
        , e.district_name
        , e.school_name
        , c.percentage_standard_met_and_above
        , c.percentage_standard_not_met
        , c.subgroup_id
        FROM caasp c 
        LEFT JOIN entities e ON c.school_code = e.school_code AND c.district_code = e.district_code
        WHERE  (c.percentage_standard_met_and_above = 100 OR c.percentage_standard_not_met = 100) 
                AND c.grade IN (6, 7, 8) 
        ORDER BY c.percentage_standard_met_and_above DESC;
        """

schools_hund_percent = pd.read_sql_query(q, conn)
schools_hund_percent.head()

df_ca_entities.describe()

#create reference table for labels to id codes
test_types = pd.DataFrame([[1,'ELA'], [2, 'Mathematics']], columns =['test_id', 'test_type']).set_index('test_id')
school_types = pd.DataFrame([[7, 'Public School'], [9, 'Direct Funded Charter'], [10, 'Locally Funded Charter']], 
             columns = ['type_id', 'type']).set_index('type_id')

print('Reference Tables\n\n')
print(test_types, '\n')
print(school_types)

"""query averages for each performance category for students in the 6th, 7th, and 8th grades. 
    Group results by school type, student grade, and test type

"""

perform_avgs_query = """SELECT COUNT(*) total_schools
                            , e.type_id
                            , c.grade
                            , c.test_id
                            , AVG(c.students_tested)
                            , AVG(c.mean_scale_score)
                            , AVG(c.percentage_standard_exceeded)
                            , AVG(c.percentage_standard_met)
                            , AVG(c.percentage_standard_met_and_above)
                            , AVG(c.percentage_standard_nearly_met)
                            , AVG(c.percentage_standard_not_met)
                            , AVG(c.students_with_scores)
                    FROM caasp c 
                    INNER JOIN entities e ON c.school_code = e.school_code 
                        AND c.county_code = e.county_code 
                        AND c.district_code = e.district_code 
                    WHERE c.grade IN (6, 7, 8) AND e.type_id IN (7, 9, 10)
                        AND c.subgroup_id = 1 AND c.school_code >0
                    GROUP BY e.type_id, c.grade, c.test_id
                    ORDER BY c.grade, c.test_id;
                    """

middle_test_avgs = pd.read_sql_query(perform_avgs_query, conn)

middle_test_avgs.loc[middle_test_avgs.grade == 6]

middle_test_avgs.loc[middle_test_avgs.grade == 7]

middle_test_avgs.loc[middle_test_avgs.grade == 8]

#Create view for avg test results above and below standards by school type, test type and grade


view = """ CREATE VIEW averages AS
        SELECT c.grade
        , c.test_id
        , e.type_id
        , AVG(c.percentage_standard_not_met) avg_percent_std_not_met
        , AVG(c.percentage_standard_met_and_above) avg_percentage_standard_met_and_above
        FROM caasp c 
        INNER JOIN entities e ON c.school_code = e.school_code 
            AND c.county_code = e.county_code 
            AND c.district_code = e.district_code 
        WHERE c.grade IN (6, 7, 8) AND e.type_id IN (7, 9, 10) AND c.subgroup_id = 1 AND c.school_code >0
        GROUP BY e.type_id, c.grade, c.test_id
        ORDER BY c.grade, c.test_id;
        """
       
#query num of schools grouped by type, test type and grade with more than avg percentage of students below stnds

below_avg_query = """SELECT c.grade
                        , c.test_id
                        , e.type_id
                        , a.avg_percent_std_not_met
                        , COUNT(*) num_percent_std_not_met_grter_avg
                    FROM caasp c 
                    INNER JOIN entities e ON c.school_code = e.school_code 
                        AND c.county_code = e.county_code 
                        AND c.district_code = e.district_code 
                    INNER JOIN averages a ON c.grade = a.grade AND c.test_id = a.test_id
                        AND e.type_id = a.type_id
                    WHERE c.grade IN (6, 7, 8) AND e.type_id IN (7, 9, 10) 
                        AND  c.percentage_standard_not_met > a.avg_percent_std_not_met 
                    GROUP BY e.type_id, c.grade, c.test_id
                    ORDER BY c.grade, c.test_id;
       
                 """

#pd.read_sql_query('DROP VIEW averages;', conn)
#pd.read_sql_query(view, conn)
stds_not_met = pd.read_sql_query(below_avg_query, conn)

def convert_name(num_tup):
    #converts lables to tup of names
    return(num_tup[0], test_types.loc[num_tup[1]].values[0], school_types.loc[num_tup[2]].values[0])

labels = avg_diffs_from_pub[['grade', 'test_id', 'type_id']].values.tolist()
labels_convert = [convert_name(x) for x in labels]
label_names = [(x[0], x[2]) for x in labels_convert]
colors = ['#5c678a' if x[1] == 'ELA' else '#00b3b3' for x in labels_convert]
x_values = range(avg_diffs_from_pub.shape[0])

stds_not_met['total_schools'] = middle_test_avgs.total_schools
stds_not_met['percentage_greater_than_avg'] = (stds_not_met.num_percent_std_not_met_grter_avg/
                                               stds_not_met.total_schools)

plt.figure(figsize =(8,6))
plt.bar(range(avg_diffs_from_pub.shape[0]), stds_not_met.percentage_greater_than_avg, color = colors)
plt.xticks(range(avg_diffs_from_pub.shape[0]),label_names, rotation = 'vertical')

#create legend 
ela_patch = mpatches.Patch(color='#5c678a', label='ELA')
math_patch = mpatches.Patch(color='#00b3b3', label='Mathematics')

plt.title('Percentage of schools with higher than average students not meeing standards')
plt.legend(handles=[ela_patch, math_patch],bbox_to_anchor=(1.35, 1), frameon = False )
ax = plt.gca()
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
ax.spines['left'].set_visible(False)

plt.show()

above_avg_query =""" SELECT c.grade
                    , c.test_id
                    , e.type_id
                    , a.avg_percent_std_not_met
                    , COUNT(*) num_percent_std_met_above_grter_avg
                    FROM caasp c 
                    INNER JOIN entities e ON c.school_code = e.school_code 
                        AND c.county_code = e.county_code 
                        AND c.district_code = e.district_code 
                    INNER JOIN averages a ON c.grade = a.grade AND c.test_id = a.test_id 
                        AND e.type_id = a.type_id
                    WHERE c.grade IN (6, 7, 8) AND e.type_id IN (7, 9, 10) 
                        AND  c.percentage_standard_met_and_above > a.avg_percentage_standard_met_and_above 
                        AND  c.school_code > 0 
                    GROUP BY e.type_id, c.grade, c.test_id
                    ORDER BY c.grade, c.test_id;
                """

stds_met_above = pd.read_sql_query(above_avg_query, conn)

stds_met_above['total_schools'] = middle_test_avgs.total_schools
stds_met_above['percentage_greater_than_avg'] = (stds_met_above.num_percent_std_met_above_grter_avg/
                                                 stds_met_above.total_schools)
plt.figure(figsize =(8,6))
plt.bar(range(avg_diffs_from_pub.shape[0]), stds_met_above.percentage_greater_than_avg, color = colors)
plt.xticks(range(avg_diffs_from_pub.shape[0]),label_names, rotation = 'vertical')

#create legend 
ela_patch = mpatches.Patch(color='#5c678a', label='ELA')
math_patch = mpatches.Patch(color='#00b3b3', label='Mathematics')

plt.title('Percentage of schools with higher than average students performing above average')
plt.legend(handles=[ela_patch, math_patch],bbox_to_anchor=(1.35, 1), frameon = False )
ax = plt.gca()
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
ax.spines['left'].set_visible(False)

plt.show()

avg_not_met_and_met = middle_test_avgs[['grade', 'type_id', 'test_id','AVG(c.percentage_standard_not_met)',
                                        'AVG(c.percentage_standard_met_and_above)' ]]

avg_not_met_and_met['diff_not_met'] = 0
avg_not_met_and_met['diff_met'] = 0

avg_diffs_from_pub = pd.DataFrame(columns = avg_not_met_and_met.columns)

for _, g in avg_not_met_and_met.groupby(['grade', 'test_id']):
    g.diff_not_met = g.iloc[0, 3] 
    g.diff_met = g.iloc[0, 4]
    avg_diffs_from_pub = avg_diffs_from_pub.append(g)
        

avg_diffs_from_pub.diff_not_met = (avg_diffs_from_pub['AVG(c.percentage_standard_not_met)']
                                   - avg_diffs_from_pub.diff_not_met)

avg_diffs_from_pub.diff_met = (avg_diffs_from_pub['AVG(c.percentage_standard_met_and_above)'] 
                               - avg_diffs_from_pub.diff_met)

#absolute differences charter avg percentage - public school avg percentage
avg_diffs_from_pub

#plot standards not met
plt.figure(figsize = (12, 6))
plt.bar(x_values, avg_diffs_from_pub['AVG(c.percentage_standard_not_met)'], 
        label = label_names, color= colors)
plt.ylabel('mean percentage')
plt.xticks(range(avg_diffs_from_pub.shape[0]),label_names, rotation = 'vertical')
plt.title('Standards not met')

ax = plt.gca()
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
ax.spines['left'].set_visible(False)

#draw lengend and add legend
ela_patch = mpatches.Patch(color='#5c678a', label='ELA')
math_patch = mpatches.Patch(color='#00b3b3', label='Mathematics')

plt.legend(handles=[ela_patch, math_patch],bbox_to_anchor=(1.35, 1), frameon = False )
plt.show()



#plot standards met and above
plt.figure(figsize = (12, 6))

plt.bar(x_values, avg_diffs_from_pub['AVG(c.percentage_standard_met_and_above)'], 
        label = label_names, color = colors)
plt.ylabel('mean percentage')
plt.xticks(range(avg_diffs_from_pub.shape[0]),label_names, rotation = 'vertical')
plt.title('\n\nStandards met and above')

#clean plots
ax = plt.gca()
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
ax.spines['left'].set_visible(False)



#add legend 
plt.legend(handles=[ela_patch, math_patch],bbox_to_anchor=(1.35, 1), frameon = False);

percent_stnds_query = """SELECT c.grade
                                , c.test_id
                                , e.type_id
                                , c.percentage_standard_not_met
                                , c.percentage_standard_met_and_above
                            FROM caasp c 
                            INNER JOIN entities e ON c.school_code = e.school_code 
                                AND c.county_code = e.county_code 
                                AND c.district_code = e.district_code 
                            WHERE c.grade IN (6, 7, 8) AND e.type_id IN (7, 9, 10)
                                AND c.subgroup_id = 1
                            ORDER BY c.grade, c.test_id;
           
                            """

results = pd.read_sql_query(percent_stnds_query, conn)
results.head()

f, ax = plt.subplots(6, 3, figsize = (15, 25), sharey = True)

x = range(0, 100, 10)
i, j = 0,0
for name, group in results.groupby(['grade', 'test_id', 'type_id']):
    res_met = relfreq(group.percentage_standard_met_and_above, numbins = 10)
    res_not_met = relfreq(group.percentage_standard_not_met, numbins = 10)
    ax[i, j].bar(x, res_met.frequency, width = res_met.binsize, label = 'meeting and above standard',
                 alpha = .5, color = '#187eba')
    ax[i, j].bar(x, res_not_met.frequency, width = res_not_met.binsize, label = 'not meeting standard',
                 alpha = .7, color = '#f2a771')
    ax[i, j].set_xlabel(convert_name(name), fontsize = 13)
    ax[i, j].spines['top'].set_visible(False)
    ax[i, j].spines['right'].set_visible(False)
    
    if i == 0 and j == 2:
        ax[i, j].legend(frameon = False, bbox_to_anchor=(1.5, 1.55), fontsize = 12)
    j +=1
    if j ==3:
        i +=1
        j = 0 

f.suptitle("\nRelative frequencies of schools with percentage of students in each scoring category", fontsize = 16);

    

def calc_effect_size(s1, s2):
    """calculates effect size for ind sample t test
        input: samples 1 and 2 
        output: effect size 
    """
    std_est_s1_s2 = np.sqrt(len(s1))*np.sqrt(np.std(s1)**2/len(s1) + np.std(s2)**2/len(s2))
    effect_size_s1_s2 = (np.mean(s1) - np.mean(s2))/ std_est_s1_s2
    return effect_size_s1_s2


   

"""conduct ttest and ANOVA to compare group means"""


for name, g in results.groupby(['grade', 'test_id']):
    not_met_pub = g[g.type_id == 7].percentage_standard_not_met
    not_met_pubchart = g[g.type_id == 9].percentage_standard_not_met
    not_met_loclchart = g[g.type_id == 10].percentage_standard_not_met
    
    met_pub = g[g.type_id == 7].percentage_standard_met_and_above
    met_pubchart = g[g.type_id == 9].percentage_standard_met_and_above
    met_loclchart = g[g.type_id == 10].percentage_standard_met_and_above
    
    
    print(name)
    print("\nPercentage Standard Not Met\n")
    print("\tPublic vs Local Funded Charter:  ", ttest_ind(not_met_pub, not_met_loclchart, equal_var = False))
    print("\tEffect size:", calc_effect_size(not_met_loclchart, not_met_pub))
    
    print("\n\tPublic vs Direct Funded Charter:  ", ttest_ind(not_met_pub, not_met_pubchart, equal_var = False))
    print("\tEffect size:", calc_effect_size(not_met_pubchart, not_met_pub))
    print("\n\tANOVA: ", f_oneway(not_met_loclchart, not_met_pub, not_met_pubchart))
    
    
    print("\n\nPercentage Standard Met and Above:\n")
    print("\tPublic vs Local Funded Charter:  ", ttest_ind(met_pub, met_loclchart, equal_var = False))
    print("\tEffect size:", calc_effect_size(met_loclchart, met_pub))
    
    print("\n\tPublic vs Direct Funded Charter:  ", ttest_ind(met_pub, met_pubchart, equal_var = False))
    print("\tEffect size:", calc_effect_size(met_pubchart, met_pub))
    print("\n\tANOVA: ", f_oneway(met_loclchart, met_pub, met_pubchart), '\n')