<jupyter_start><jupyter_text>Read in federal level data<jupyter_code>fiscal = pd.read_sas('../../data/fiscal2018', format = 'sas7bdat', encoding='iso-8859-1')<jupyter_output><empty_output><jupyter_text>Generate list of districts in the state in the federal data<jupyter_code>state_fiscal = fiscal[(fiscal['STABBR'] == abbr) & (fiscal['GSHI'] == '12')]
len(state_fiscal)
state_fiscal.head()<jupyter_output><empty_output><jupyter_text>Read in state level data<jupyter_code>state_grads = pd.read_csv('../../data/state_data_raw/' + file, skiprows=9)
state_grads = state_grads.iloc[:-18]
state_grads.dropna(axis=1, how='all', inplace=True)
state_grads<jupyter_output><empty_output><jupyter_text>Filter results.<jupyter_code># state_grads = state_grads[(state_grads[''] == '')]<jupyter_output><empty_output><jupyter_text>Select and rename columns.<jupyter_code>state_grads.loc[83:, 'Unnamed: 2'] = state_grads.loc[83:, 'Unnamed: 3']
state_grads = state_grads.dropna(axis=0, how='all')
state_grads
state_grads = state_grads[['Unnamed: 2', 'Class of \n2018 Cohort\n(1)', 'Graduation\nRate']]
state_grads.columns = ['District Name', 'Total', 'Graduation Rate']
state_grads<jupyter_output><empty_output><jupyter_text>Remove duplicates/unneeded rows.<jupyter_code>state_grads = state_grads.drop(labels=[1, 2, 95, 96])
state_grads<jupyter_output><empty_output><jupyter_text>Convert data types.<jupyter_code>state_grads = state_grads.fillna('')
state_grads['Graduation Rate'] = state_grads['Graduation Rate'].astype(str).str.replace('%', '')
state_grads['Total'] = pd.to_numeric(state_grads['Total'])
state_grads['Graduation Rate'] = pd.to_numeric(state_grads['Graduation Rate']) / 100<jupyter_output><empty_output><jupyter_text>Check for matches and non-matches in the two lists.<jupyter_code>state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace('.', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(',', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' District', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' Dist', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' Public', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' Schools', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' School', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' County', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' Charter', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' \(H\)', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' Cooperative', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' Coop', '')

state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace('.', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(',', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' District', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' Dist', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' Public', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' Schools', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' School', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' Charter', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' \(H\)', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' Cooperative', '')
state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(' Coop', '')
matches = [name for name in list(state_grads['District Name']) if name in list(state_fiscal['NAME'])]
matches.sort()
len(matches)
A = [name for name in list(state_grads['District Name']) if name not in list(state_fiscal['NAME'])]
A.sort()
A
B = [name for name in list(state_fiscal['NAME']) if name not in list(state_grads['District Name'])]
B.sort()
B<jupyter_output><empty_output><jupyter_text>Match remaining samples I can find.<jupyter_code>state_fiscal_rename = {
    'Cocheco Arts And Technology Academy' : 'Cocheco Academy for the Arts',
    'Coe Brown Academy' : 'Coe-Brown Northwood Academy',
    'Governor Wentworth Reg' : 'Governor Wentworth Regional',
    'Great Bay Elearning' : 'Great Bay',
    'Hillsboro Deering' : 'Hillsboro-Deering',
    'Hollis/Brookline' : 'Hollis-Brookline',
    'Inter-lakes' : 'Inter-Lakes',
    #'LEAF',
    'Ledyard Academy' : 'Ledyard',
    'Making Community Connections' : 'Making Community Connections Total',
    'Mascoma Valley Reg' : 'Mascoma Valley Regional',
    'Newfound Area',
    'Pemi-baker Regional',
    'Prospect Mountain',
    'White Mountain Regional',
    'Wilton-Lyndeboro'
}
state_fiscal = state_fiscal.replace(state_fiscal_rename)<jupyter_output><empty_output><jupyter_text>Merge federal and state data, keeping only matches between the two.<jupyter_code>state_grads_merged = pd.merge(state_fiscal, state_grads, how='inner', left_on='NAME', right_on='District Name')<jupyter_output><empty_output><jupyter_text>Save cleaned data.<jupyter_code>state_grads_merged.to_csv('../../data/state_data_merged/' + abbr + '.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18253243193860771, 'fraction_numerical': 0.00694317558925635, 'mean_word_length': 7.929853181076672, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '631523', 'license': 'CC0-1.0', 'n_tokens_mistral': 2014, 'n_tokens_neox': 1936, 'n_words': 314, 'path': 'cleaning/01_cleaning_code_by_state/NH_cleaning.ipynb', 'repo_name': 'vansheer/high_school_graduation_rates', 'size': '5473'}