<jupyter_start><jupyter_text>Read in federal level data<jupyter_code>fiscal = pd.read_sas('../../data/fiscal2018', format = 'sas7bdat', encoding='iso-8859-1')
fiscal<jupyter_output><empty_output><jupyter_text>Generate list of districts in the state in the federal data<jupyter_code>fiscal_names = list(fiscal[fiscal['STNAME'] == 'Alabama']['NAME'])
len(fiscal_names)<jupyter_output><empty_output><jupyter_text>Read in state level data<jupyter_code>AL = pd.read_excel('../../data/state_data_raw/alabama2019.xlsx')
AL.head()<jupyter_output><empty_output><jupyter_text>Reset columns.<jupyter_code>AL.columns = AL.iloc[3]
AL = AL[4:]<jupyter_output><empty_output><jupyter_text>Filter for only the district level, total population samples.<jupyter_code>AL = AL[(AL['System Name'] == AL['School Name']) &
        (AL['Grade'] == 'Grade 12') &
        (AL['Gender'] == 'All Gender') &
        (AL['Race'] == 'All Race') &
        (AL['Ethnicity'] == 'All Ethnicity') &
        (AL['SubPopulationName'] == 'All SubPopulation')]
AL.head()
len(AL)<jupyter_output><empty_output><jupyter_text>Check for non-matches in the two lists<jupyter_code>[name for name in list(AL['System Name']) if name not in fiscal_names]
[name for name in fiscal_names if name not in list(AL['System Name'])]<jupyter_output><empty_output><jupyter_text>Examine data types and missing values.<jupyter_code>AL.info()
AL = AL[['System Name', 'Total', 'Graduation Rate']]
AL<jupyter_output><empty_output><jupyter_text>Remove duplicate.<jupyter_code>AL = AL.iloc[:-1]<jupyter_output><empty_output><jupyter_text>Alabama source indicates that '~' is for graduation rates >= 95% or <= 5%. Because this represents about half of the data, we can't just drop these samples. We'll assume that very few, if none, of the districts have graduation rates below 5% and instead assign them the minimum value of 95%.<jupyter_code>len(AL[AL['Graduation Rate'] == '~'])
AL['Graduation Rate'] = AL['Graduation Rate'].replace('~', .95)<jupyter_output><ipython-input-66-be897acf32db>:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  AL['Graduation Rate'] = AL['Graduation Rate'].replace('~', .95)<jupyter_text>Change data types.<jupyter_code>AL['Graduation Rate'] = pd.to_numeric(AL['Graduation Rate'])
AL['Total'] = pd.to_numeric(AL['Total'])
AL = AL.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Change column names for consistency across states.<jupyter_code>AL = AL.rename(columns={'System Name' : 'District Name'})<jupyter_output><empty_output><jupyter_text>Merge federal and state data, keeping only matches between the two.<jupyter_code>AL_merged = pd.merge(fiscal[fiscal['STABBR'] == 'AL'], AL, how='inner', left_on='NAME', right_on='District Name')<jupyter_output><empty_output><jupyter_text>Save cleaned data.<jupyter_code>AL_merged.to_csv('../../data/state_data_merged/AL.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1527423469387755, 'fraction_numerical': 0.011798469387755101, 'mean_word_length': 7.085051546391752, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 54, 'https://': 1, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '311819', 'license': 'CC0-1.0', 'n_tokens_mistral': 1138, 'n_tokens_neox': 1089, 'n_words': 230, 'path': 'cleaning/01_cleaning_code_by_state/AL_cleaning.ipynb', 'repo_name': 'vansheer/high_school_graduation_rates', 'size': '3136'}