<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_excel('../../data/state_data_raw/' + file)
state_grads.head()<jupyter_output><empty_output><jupyter_text>Reset columns.<jupyter_code>state_grads.columns = state_grads.iloc[2]
state_grads = state_grads.iloc[3:]
state_grads<jupyter_output><empty_output><jupyter_text>Select and rename columns.<jupyter_code>state_grads['Total'] = np.full_like(state_grads['Overall Cohort Grad Rate'], '')
state_grads = state_grads[['School/School System Name', 'Total', 'Overall Cohort Grad Rate']]
state_grads.columns = ['District Name', 'Total', 'Graduation Rate']
state_grads
state_grads.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 419 entries, 3 to 421
Data columns (total 3 columns):
 #   Column           Non-Null Count  Dtype 
---  ------           --------------  ----- 
 0   District Name    419 non-null    object
 1   Total            419 non-null    object
 2   Graduation Rate  419 non-null    object
dtypes: object(3)
memory usage: 9.9+ KB<jupyter_text>Convert data types.<jupyter_code>state_grads['Graduation Rate'] = state_grads['Graduation Rate'].astype(str).str.replace('>', '')
state_grads['Graduation Rate'] = state_grads['Graduation Rate'].astype(str).str.replace('<', '')
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>/opt/anaconda3/envs/dsi/lib/python3.7/site-packages/ipykernel_launcher.py: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
  """Entry point for launching an IPython kernel.
/opt/anaconda3/envs/dsi/lib/python3.7/site-packages/ipykernel_launcher.py:2: 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<jupyter_text>Check for matches and non-matches in the two lists<jupyter_code>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>Rename the samples I can find matches for.<jupyter_code>state_grads_rename = {
    #'Algiers Technology Academy',
    #'Community School for Apprenticeship Learning Inc.',
    'Delta Charter Group' : 'Delta Charter School MST',
    'Dr Martin Luther King Charter School for Sci/Tech' : 'Dr. Martin Luther King Charter School for Sci Tech',
    #'Howard School',
    'JCFA Lafayette' : 'JCFA',
    'KIPP Renaissance High School' : 'KIPP Renaissance',
    #'LA Schools for the Deaf and Visually Impaired',
    #'Lake Area New Tech Early College High School',
    #'Louisiana Special Education Center',
    #'The NET2 High School',
    'University View Academy Inc. (FRM LA Connections)' :  'University View Academy, Inc. (FRM LA Connections)',
    #'Voices for International Business & Education'
}
state_fiscal = state_fiscal.replace(state_grads_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>