<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, header=1)
state_grads.head(5)<jupyter_output><empty_output><jupyter_text>Reset columns. Filter results.<jupyter_code>state_grads = state_grads[(state_grads['SCHOOL_NAME'] == '[Districtwide]') &
                         (state_grads['GROUP_BY_VALUE'] == 'All Students') &
                         (state_grads['TIMEFRAME'] == '4-Year rate') &
                         (state_grads['COMPLETION_STATUS'] == 'Completed - Regular High School Diploma')]
state_grads<jupyter_output><empty_output><jupyter_text>Select and rename columns.<jupyter_code>state_grads['STUDENT_COUNT'] = pd.to_numeric(state_grads['STUDENT_COUNT'])
state_grads['COHORT_COUNT']  = pd.to_numeric(state_grads['COHORT_COUNT'] )
state_grads['Graduation Rate'] = state_grads['STUDENT_COUNT'] / state_grads['COHORT_COUNT'] 
state_grads = state_grads[['DISTRICT_NAME', 
                           'COHORT_COUNT',
                           'Graduation Rate']]
state_grads.columns = ['District Name', 'Total', 'Graduation Rate']
state_grads.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 760 entries, 451 to 184032
Data columns (total 3 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   District Name    760 non-null    object 
 1   Total            760 non-null    int64  
 2   Graduation Rate  760 non-null    float64
dtypes: float64(1), int64(1), object(1)
memory usage: 23.8+ KB<jupyter_text>Convert data types.<jupyter_code># state_grads['Total'] = state_grads['Total'].astype(str).str.replace('<', '')
# state_grads['Graduation Rate'] = state_grads['Graduation Rate'].astype(str).str.replace('%', '')<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.upper().str.strip()
state_grads['District Name'] = state_grads['District Name'].astype(str).str.upper().str.strip()
# state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(r'\sSu$', '')
# state_grads['District Name'] = state_grads['District Name'].astype(str).str.replace(r'\sSd$', '')

state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' SCHOOL DISTRICT', '')
state_fiscal['NAME'] = state_fiscal['NAME'].astype(str).str.replace(' AGENCY', '')
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>Make any additional matches I can find.<jupyter_code># state_fiscal_rename = {}
# 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.16057818659658343, 'fraction_numerical': 0.014717477003942181, 'mean_word_length': 5.407407407407407, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 41, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '560969', 'license': 'CC0-1.0', 'n_tokens_mistral': 1393, 'n_tokens_neox': 1330, 'n_words': 222, 'path': 'cleaning/01_cleaning_code_by_state/WI_cleaning.ipynb', 'repo_name': 'vansheer/high_school_graduation_rates', 'size': '3805'}