<jupyter_start><jupyter_text>School Progress Report (SPR) Data<jupyter_code># read SPR data
spr = pd.read_excel('data/SPR_SY1617_Data/SPR_SY1617_School_Metric_Scores_20180206.xlsx', sheet_name=1)

# convert School ID to string
spr = spr.rename(columns={'SRC School ID': 'School ID'})
spr['School ID'] = spr['School ID'].astype(str)

school_info = spr[['School', 'School ID', 'Rpt Type Long', 'Governance', 'Grades Served', 'Enrollment']].drop_duplicates()

school_info.head(3)<jupyter_output><empty_output><jupyter_text>Serious Incidents (aka Safety)<jupyter_code>si_1314 = pd.read_csv('data/Serious_Incidents/Serious_Incidents_2013-14.TXT')
si_1415 = pd.read_excel('data/Serious_Incidents/Serious_Incidents_2014-15.xlsx', sheet_name=0)
si_1415.rename(columns={'ULCS Code':'ULCS_NO', 
                        'School Year':'SCHOOL_YEAR', 
                        'Incident Type':'INCIDENT_TYPE',
                        'Incident Count':'INCIDENT_COUNT',
                        'School ID':'SCHOOL_ID'}, inplace=True)

si_1516 = pd.read_excel('data/Serious_Incidents/Serious_Incidents_2015-16.xls', sheet_name=0)
si_1617 = pd.read_excel('data/Serious_Incidents/Serious_Incidents_2016-17.xlsx', sheet_name=0)

si = pd.concat([si_1314, si_1415, si_1516, si_1617]).reset_index(drop=True)
si.drop('ULCS_NO', axis=1, inplace=True)
si['INCIDENT_TYPE'] = si['INCIDENT_TYPE'].str.upper()
si['INCIDENT_TYPE'].replace('SUICIDE - ATTEMPTS & THREATS', 'SUICIDE - ATTEMPTS AND THREATS', inplace=True)
si['INCIDENT_TYPE'].replace('DRUGS & ALCOHOL OFFENSE', 'DRUG AND ALCOHOL OFFENSES', inplace=True)
si['INCIDENT_TYPE'].replace('DRUG & ALCOHOL OFFENSES', 'DRUG AND ALCOHOL OFFENSES', inplace=True)
si['INCIDENT_TYPE'].replace('THREATS', 'THREAT', inplace=True)
si['INCIDENT_TYPE'].replace('THEFTS', 'THEFT', inplace=True)
si['INCIDENT_TYPE'].replace('BOMB SCARES', 'BOMB SCARE', inplace=True)
si['INCIDENT_TYPE'].replace('FIRES AND FALSE ALARMS', 'FIRE - INCENDIARY', inplace=True)
si['INCIDENT_TYPE'].replace('THREATS - VERBAL AND WRITTEN', 'THREAT', inplace=True)
si['INCIDENT_TYPE'].replace('WEAPONS OFFENSES', 'WEAPONS', inplace=True)
si.head()
# convert School ID to string
si['SCHOOL_ID'] = si['SCHOOL_ID'].astype(str)

df = pd.merge(si, school_info, how='left', left_on='SCHOOL_ID', right_on='School ID')
df['Incident_rate'] = df['INCIDENT_COUNT'] * 100 / df['Enrollment']

df['INCIDENT_TYPE'] = df['INCIDENT_TYPE'].apply(lambda x: x.title())
df = df[~pd.isnull(df['School'])]

df.drop('School ID', axis=1, inplace=True)
df.head()<jupyter_output><empty_output><jupyter_text>Export Data<jupyter_code># save data
df.to_csv("philly_school_safety.csv", sep='|', index=False)
df[df['School'].str.contains('Treaty')]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16788588149231895, 'fraction_numerical': 0.03145574250182882, 'mean_word_length': 6.747875354107649, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '220833', 'license': 'MIT', 'n_tokens_mistral': 1158, 'n_tokens_neox': 1046, 'n_words': 161, 'path': 'Archive/Clean_safety_data.ipynb', 'repo_name': 'benattix/philly-schools', 'size': '2734'}