<jupyter_start><jupyter_text>Begin Exploring Data and Building FeaturesThe data disaggregates variables multiple ways, male and female, white, African-American, Hispanic, Asian-American, IEP/504, etc. and covers 34 topical areas including chronic absenteeism of both students and teachers, subjects, curriculum focus, sports participation, harassment and bullying, teacher certification, discipline, budgets, and more. In all situations, the features used the summed the male and female numbers and did not consider disaggregated groups. Even so, there were more than 170 columns of total male, female or other variables. The combined data set was more than 1,800 columns wide and had data on more 21,000 high schools.Basic data cleaning involved deleting unused columns, totaling male and female number to get a total for the school. Often when schools did not have the data available or immediately available they were given the option of adding a negative number as an indicator to explain the missing data. Those negative numbers were converted to NANs.The variables are all created in the notebook. The exploratory data and visualization are in other notebooks, especially, Simple_numbers, Big_and_Small EDA, and Large_Schools_First_Models.<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
#combo is the combined graduation and features data sets
combo = pd.read_csv('/Users/flatironschool/Absenteeism_Project/data/processed/grad_data.csv')
combo.head()
combo.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 21867 entries, 0 to 21866
Columns: 1866 entries, Unnamed: 0 to IDSCH
dtypes: float64(32), int64(1748), object(86)
memory usage: 311.3+ MB<jupyter_text>The OCR (features) data set disaggregates the data by ethnicity, gender, 504/IEP/disability status, English language learner, etc. This project uses the total for each variable, not the disaggregated. The cell belowreturned totals to begin the aggregation process<jupyter_code>total_cols = [col for col in combo.columns if 'TOT' in col]
print(total_cols)
len(total_cols)
#drop columns of disaggregated graduation rates
combo.drop(['FIPST', 'LEAID_x', 'MAM_COHORT_1516', 'MAM_RATE_1516',
                    'MAS_COHORT_1516', 'MAS_RATE_1516', 'MBL_COHORT_1516', 
                    'MBL_RATE_1516','MHI_COHORT_1516', 'MHI_RATE_1516', 
                    'MTR_COHORT_1516','MTR_RATE_1516', 'MWH_COHORT_1516',
                    'MWH_RATE_1516', 'CWD_COHORT_1516','CWD_RATE_1516',
                    'ECD_COHORT_1516','ECD_RATE_1516','LEP_COHORT_1516',
                    'LEP_RATE_1516','DATE_CUR'], axis=1, inplace=True)

combo.head()<jupyter_output><empty_output><jupyter_text>Replace All Negative Values with NANsIf a category did not apply to the school (for example, the school does not offer AP), they plan on getting the data later, small cell value, missing data, system error or some other reason, the variables were assigned a negative number. These have been replaced with NANs.<jupyter_code>combo.replace([-2, -5, -6, -7, -8, -9], np.nan, inplace=True)
combo['TOT_ALGPASS_GS0910_F'].describe()<jupyter_output><empty_output><jupyter_text>Create New Features, Delete Disaggregated Variables<jupyter_code>#create total enrollment feature and drop the rest of the 
#disaggregated enrollment features
combo['total_enrollment'] = combo['TOT_ENR_M'] + combo['TOT_ENR_F']
combo.drop(combo.columns.to_series()['SCH_PSENR_NONIDEA_A3':'SCH_ENR_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_ENR_LEP_M':'SCH_504ENR_LEP_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_GRADE_PS':'SCH_UGDETAIL_HS'], axis=1, inplace=True)
#create new feature combining advanced classes: AP, IB, Duel Enrollment 
#(enrolling in a class at the local community college)

combo['total_ap_ib_de'] = combo['TOT_APENR_M'] + combo['TOT_APENR_F'] + combo['TOT_IBENR_M'] + combo['TOT_IBENR_F'] + combo['TOT_DUAL_M'] + combo['TOT_DUAL_F']
combo['ap_ib_de_rate'] = combo['total_ap_ib_de']/combo['total_enrollment']
combo.drop(['SCH_DUALENR_LEP_M', 'SCH_DUALENR_LEP_F', 'SCH_DUALENR_IDEA_M', 'SCH_DUALENR_IDEA_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_APENR_IND':'SCH_APENR_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_APENR_LEP_M':'SCH_APPASS_NONE_IDEA_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_IBENR_IND':'SCH_IBENR_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_IBENR_LEP_M':'SCH_IBENR_IDEA_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_GTENR_LEP_M':'SCH_DUALENR_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_GT_IND':'SCH_GTENR_TR_F'], axis=1, inplace=True)
#create new feature of number and rate of students taking SAT or ACT for
#college admission. Deleting disaggregated features
combo['total_sat_act'] = combo['TOT_SATACT_M'] + combo['TOT_SATACT_F']
combo['sat_act_rate'] = combo['total_sat_act']/combo['total_enrollment']
combo.drop(combo.columns.to_series()['SCH_SATACT_HI_M':'SCH_SATACT_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_SATACT_LEP_M':'SCH_SATACT_IDEA_F'], axis=1, inplace=True)
#create new feature of number and rate of students passing Algebra for all grades
combo['total_pass_algebra'] = combo['TOT_ALGPASS_GS0910_M'] + combo['TOT_ALGPASS_GS0910_F'] + combo['TOT_ALGPASS_GS1112_M'] + combo['TOT_ALGPASS_GS1112_F']
combo['pass_algebra_rate'] = combo['total_pass_algebra']/combo['total_enrollment']
#create new feature of students enrolled in geometry
combo['total_geometry'] = combo['TOT_GEOM_M'] + combo['TOT_GEOM_F']
combo['geometry_rate'] = combo['total_geometry']/combo['total_enrollment']
#create new feature of students enrolled in algebra 2
combo['total_algebra2'] = combo['TOT_MATHENR_ALG2_M'] + combo['TOT_MATHENR_ALG2_F']
combo['algebra2_rate'] = combo['total_algebra2']/combo['total_enrollment']
#create new feature of students enrolled in calculus
combo['total_calc'] = combo['TOT_MATHENR_CALC_M'] + combo['TOT_MATHENR_CALC_F']
combo['calc_rate'] = combo['total_calc']/combo['total_enrollment']
#create new feature of number and rate of students that are chronically 
#absent, missing 15 or more school days in a school year. All absences, 
#excused and unexcused are included. Deleting disaggreated features.
combo['total_chronic_absent'] = combo['TOT_ABSENT_M'] + combo['TOT_ABSENT_F']
combo['chronic_absent_rate'] = combo['total_chronic_absent']/combo['total_enrollment']
combo.drop(combo.columns.to_series()['SCH_ABSENT_HI_M':'SCH_ABSENT_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_ABSENT_LEP_M':'SCH_ABSENT_IDEA_F'], axis=1, inplace=True)
#create new feature of number and rate of students participating in sports
#delete disaggregated variables
combo['sports_part'] = combo['SCH_SSPART_M'] + combo['SCH_SSPART_F']
combo['sports_rate'] = combo['sports_part']/combo['total_enrollment']
combo.drop(combo.columns.to_series()['SCH_SSATHLETICS_IND':'TOT_SSTEAMS'], axis=1, inplace=True)
#create new feature related to the number of days of school lost to suspensions.
#Using this feature will penalize longer suspensions.
combo['total_suspension_days'] = combo['TOT_DAYSMISSED_F'] + combo['TOT_DAYSMISSED_F']
combo['suspensed_day_rate'] = combo['total_suspension_days']/combo['total_enrollment']
combo.drop(combo.columns.to_series()['SCH_PSDISC_SINGOOS_HI_M':'SCH_DAYSMISSED_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_DAYSMISSED_LEP_M':'SCH_DAYSMISSED_IDEA_F'], axis=1, inplace=True)
#Create new feature related to number and rate of alleged harassment or bullying 
#on the basis of gender, race, disability, sexual orientation, or religion.
combo['harassed'] = combo['SCH_HBALLEGATIONS_SEX'] + combo['SCH_HBALLEGATIONS_RAC'] + combo['SCH_HBALLEGATIONS_DIS'] + combo['SCH_HBALLEGATIONS_ORI'] + combo['SCH_HBALLEGATIONS_REL']
combo['harassed_rate'] = combo['harassed']/combo['total_enrollment']
combo.drop(combo.columns.to_series()['SCH_HBREPORTED_SEX_HI_M':'SCH_HBDISCIPLINED_DIS_504_F'], axis=1, inplace=True)
#Create new feature related to non-personnel expenditures related to 
#activities funded by Federal, State and local funds. Delete other features 
combo['activities_funds_rate'] = (combo['SCH_NPE_WOFED'] + combo['SCH_NPE_WFED'])/combo['total_enrollment']
combo.drop(['SCH_SAL_TOTPERS_WOFED', 'SCH_FTE_TEACH_WOFED', 'SCH_SAL_TEACH_WOFED'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_FTE_AID_WOFED':'SCH_SAL_TOTPERS_WFED'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Features related to teachers and staff<jupyter_code>#rate of non-certified teachers
combo['non_cert_rate'] = combo['SCH_FTETEACH_NOTCERT']/combo['SCH_FTETEACH_TOT']
#rate of school counselors per student
combo['counselor_rate']= combo['SCH_FTECOUNSELORS']/combo['total_enrollment']
#rate of chronically absent teachers
combo['absent_teacher_rate'] = combo['SCH_FTETEACH_ABSENT']/combo['SCH_FTETEACH_TOT']
combo.drop(['SCH_FTETEACH_CERT', 'SCH_FTETEACH_FY', 'SCH_FTETEACH_SY', 'SCH_TEACHERS_CURR_TOT',
           'SCH_TEACHERS_PREV_TOT', 'SCH_FTESECURITY_LEO', 'SCH_FTESECURITY_GUA', 'SCH_FTESERVICES_NUR',
           'SCH_FTESERVICES_PSY','SCH_FTESERVICES_SOC'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Delete Disaggregated Variables Drop sections on retention, corporal discipline, explusions, transfers, referrals, arrests, offenses, restraint, seclusion, disabilites, and juvenile justice<jupyter_code>combo.drop(combo.columns.to_series()['SCH_CREDITRECOVERY_IND':'SCH_ALGENR_GS0910_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_ALGENR_GS0910_LEP_M':'SCH_ALGENR_GS1112_TR_F'],axis=1, inplace= True)
combo.drop(combo.columns.to_series()['SCH_ALGENR_GS1112_LEP_M':'SCH_ALGPASS_GS0910_TR_F'],axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_ALGPASS_GS1112_LEP_M':'SCH_MATHENR_GEOM_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_MATHENR_GEOM_LEP_M':'SCH_MATHENR_ALG2_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_MATHENR_ALG2_LEP_M':'SCH_MATHENR_CALC_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_MATHENR_CALC_LEP_M':'SCH_MATHENR_ADVM_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_MATHENR_ADVM_LEP_M':'SCH_SCIENR_BIOL_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_SCIENR_BIOL_LEP_M':'SCH_SCIENR_CHEM_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_SCIENR_CHEM_LEP_M':'SCH_SCIENR_PHYS_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_SCIENR_PHYS_LEP_M':'TOT_SSCLASSES_OTHA'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_ALGPASS_GS0910_LEP_M':'SCH_ALGPASS_GS1112_TR_F'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_RS_NONIDEA_MECH_HI_M':'SCH_RSINSTANCES_SECL_504'],axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_CORPINSTANCES_IND':'SCH_CORPINSTANCES_WODIS'], axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_PSDISC_EXP_HI_M':'SCH_OFFENSE_POSSWX'],axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_RET_KG_IND':'SCH_RET_G12_504_F'],axis=1, inplace=True)
combo.drop(combo.columns.to_series()['SCH_JJTYPE':'SCH_JJPART_OV180'], axis=1, inplace=True)
combo.drop('SCH_CORPINSTANCES_WDIS', axis=1, inplace=True)
columns = combo.columns
for col in columns:
    print(col)
combo.describe()
combo.to_csv('combo_cleaned.csv')<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup