# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd

# Read in federal level data

fiscal = pd.read_sas('../../data/fiscal2018', format = 'sas7bdat', encoding='iso-8859-1')

# Generate list of districts in the state in the federal data

fiscal_AZ = fiscal[(fiscal['STNAME'] == 'Arizona') & (fiscal['GSHI'] == '12')]

len(fiscal_AZ)

# + jupyter={"outputs_hidden": true}
fiscal_AZ.head()
# -

# Read in state level data

AZ = pd.read_excel('../../data/state_data_raw/arizona2018.xls', sheet_name='LEA by Subgroup')

# + jupyter={"outputs_hidden": true}
AZ.head()
# -

# Filter for only the district level, total population samples.

AZ = AZ[(AZ['Graduation Rate Type'] == '4-Year') &
        (AZ['Subgroup'] == 'All') &
        (AZ['Percent Graduated in 4 Years'] != '*')]

len(AZ)

# Check for non-matches in the two lists

fiscal_AZ['NAME'] = fiscal_AZ['NAME'].astype(str).str.replace('  Inc.', ', Inc.')

A = [name for name in list(AZ['LEA Name']) if name not in fiscal_AZ['NAME']]
A.sort()
A

# + jupyter={"outputs_hidden": true}
B = [name for name in fiscal_AZ['NAME'] if name not in list(AZ['LEA Name'])]
B.sort()
B
# -

# Replace the names I can find matches for.

AZ_fiscal_rename = {
 'AIBT Non-Profit Charter High School, Inc.' : 'AIBT Non-Profit Charter High School - Phoenix',
 #'ARIZONA STATE HOSPITAL',
 ##'ASU Preparatory Academy 2',
 ##'ASU Preparatory Academy 3',
 ##'ASU Preparatory Academy Digital',
 ##'ASU Preparatory Academy Tempe',
 #'AZ Dept of Juvenile Corrections',
 #'Academy of Mathematics and Science Inc. 1',
 'American Charter Schools Foundation d.b.a. Alta Vista High S' : 'American Charter Schools Foundation d.b.a. Alta Vista High School',
 'American Charter Schools Foundation d.b.a. Apache Trail High' : 'American Charter Schools Foundation d.b.a. Apache Trail High School',
 'American Charter Schools Foundation d.b.a. Crestview College' : 'American Charter Schools Foundation d.b.a. Crestview College Preparatory High Sc',
 'American Charter Schools Foundation d.b.a. Desert Hills High' : 'American Charter Schools Foundation d.b.a. Desert Hills High School',
 'American Charter Schools Foundation d.b.a. Estrella High Sch' : 'American Charter Schools Foundation d.b.a. Estrella High School',
 'American Charter Schools Foundation d.b.a. Peoria Accelerate' : 'American Charter Schools Foundation d.b.a. Peoria Accelerated High School',
 'American Charter Schools Foundation d.b.a. South Pointe High' : 'American Charter Schools Foundation d.b.a. South Pointe High School',
 'American Charter Schools Foundation d.b.a. South Ridge High' : 'American Charter Schools Foundation d.b.a. South Ridge High School',
 'American Charter Schools Foundation d.b.a. Sun Valley High S' : 'American Charter Schools Foundation d.b.a. Sun Valley High School',
 'American Charter Schools Foundation d.b.a. West Phoenix High' : 'American Charter Schools Foundation d.b.a. West Phoenix High School',    
 #'Apache County Sheriffs Office',
 #'Archway Classical Academy Chandler',
 #'Archway Classical Academy Scottsdale',
 #'Archway Classical Academy Trivium West',
 #'Archway Classical Academy Veritas',
 ##'Arizona Agribusiness & Equine Center Inc. 1',
 ##'Arizona Agribusiness & Equine Center Inc. 2',
 ##'Arizona Agribusiness & Equine Center Inc. 3',
 ##'Arizona Agribusiness & Equine Center Inc. 4',
 ##'Arizona Agribusiness & Equine Center Inc. 5',
 ##'Arizona Agribusiness & Equine Center Inc. 6',
 ##'Arizona Department of Corrections',
 #'Arizona State School for the Deaf and Blind',
 #'Arizona Supreme Court',
 ##'BASIS School Inc. 1',
 ##'BASIS School Inc. 10',
 ##'BASIS School Inc. 12',
 ##'BASIS School Inc. 13',
 ##'BASIS School Inc. 2',
 ##'BASIS School Inc. 3',
 ##'BASIS School Inc. 4',
 ##'BASIS School Inc. 5',
 ##'BASIS School Inc. 6',
 ##'BASIS School Inc. 7',
 ##'BASIS School Inc. 8',
 ##'BASIS School Inc. 9',
 #'Blue Elementary District',
 #'Bowie Unified District',
 #'CAFA, Inc. dba Learning Foundation Performing Arts School',
 'CAFA, Inc. dba Learning Foundation and Performing Arts Gilbe' : 'CAFA, Inc. dba Learning Foundation and Performing Arts Gilbert',
 'CPLC Community Schools dba Envision High School' : 'CPLC Community Schools dba Toltecalli High School',
 #'Cedar Unified District',
 #'Central Arizona Valley Institute of Technology',
 #'Cobre Valley Institute of Technology District',
 #'Cochise County Juvenile Detention',
 #'Cochise Technology District',
 #'Coconino Association for Vocation Industry and Technology',
 'Compass Points International  Inc' : 'Compass Points International, Inc',
 'Cornerstone Charter School Inc' : 'Cornerstone Charter School,Inc',
 #'Cottonwood-Oak Creek Elementary District',
 #'Country Gardens Charter Schools',
 'Daisy Education Corporation dba Sonoran Science Academy - Ph' : 'Daisy Education Corporation dba Sonoran Science Academy - Phoenix',
 'Daisy Education Corporation dba. Sonoran Science Academy Dav' : 'Daisy Education Corporation dba. Sonoran Science Academy <NAME>',
 'Daisy Education Corporation dba. Sonoran Science Academy Peo' : 'Daisy Education Corporation dba Sonoran Science Academy',
 'Desert Rose Academy Inc.' : 'Desert Rose Academy,Inc.',
 #'EAGLE South Mountain Charter, Inc.',
 #'East Valley Institute of Technology',
 'Edge School, Inc.  The' : 'Edge School, Inc., The',
 'Edkey, Inc. - Sequoia Ranch School' : 'Edkey, Inc. - Sequoia Charter School',
 #'Edkey, Inc. - Sequoia School for the Deaf and Hard of Hearin',
 'Eduprize Schools  LLC' : 'Eduprize Schools, LLC',
 'Espiritu Community Development Corp. 1' : 'Espiritu Community Development Corp.',
 #'Espiritu Schools',
 #'Excalibur Charter Schools, Inc.',
 'GAR  LLC dba Student Choice High School' : 'GAR, LLC dba Student Choice High School',
 #'Gadsden Elementary District',
 #'Gila County Juvenile Detention',
 #'Gila County Regional School District',
 #'Gila County Sheriffs Office',
 #'Gila Institute for Technology',
 #'Graham County Juvenile Detention',
 #'Graham County School Superintendent',
 #'Graham County Special Services',
 #'Greenlee County Accommodation District',
 #'Greenlee County Sheriffs Office',
 #'Highland Prep',
 'Horizon Community Learning Center Inc. 1' : 'Horizon Community Learning Center, Inc.', 
 #'Innovative Humanities Education Corporation',
 #'Integrity Education Incorporated',
 'Kaizen Education Foundation dba Mission Heights Preparatory' :  'Kaizen Education Foundation dba Mission Heights Preparatory High School',
 'Kaizen Education Foundation dba Tempe Accelerated High Schoo' : 'Kaizen Education Foundation dba Tempe Accelerated High School',
 #'Kirkland Elementary District',
 'LEAD Charter Schools dba Leading Edge Academy Queen Creek' : 'LEAD Charter Schools',
 #'Leading Edge Academy Maricopa',
 'Maricopa County Community College District dba Gateway Early' : 'Maricopa County Community College District dba Gateway Early College High School',
 'Maricopa County Regional Special Services District' : 'Maricopa County Regional District',
 #'Maricopa County Sheriffs Office',
 '<NAME> Educational Foundation. dba: Tri-City P' : '<NAME> Educational Foundation. dba: Tri-City Prep High School',
 #'Mohave County Juvenile Detention',
 #'Mountain Institute JTED',
 #'Navajo County School Superintendents Office',
 #'New World Educational Center',
 #'Northeast Arizona Technological Institute of Vocational Educ',
 #'Northern Arizona Vocational Institute of Technology',
 'Nosotros  Inc' : 'Nosotros, Inc',
 'Ombudsman Educational Services Ltd. a subsidiary of Educ 1' : 'Ombudsman Educational Services, Ltd.,a subsidiary of Educational Services of Ame',
 'PAS Charter, Inc.  dba Intelli-School' : 'PAS Charter, Inc., dba Intelli-School',
 #'Park View School, Inc.',
 'Pathways KM Charter Schools  Inc' : 'Pathways In Education-Arizona, Inc.',
 'Phoenix Collegiate Academy, Inc.' : 'Phoenix Collegiate Academy High LLC',
 #'Pima County JTED',
 'Pima Prevention Partnership dba Pima Partnership School  The' : 'Pima Prevention Partnership dba Pima Partnership School, The',
 #'Pinal County Juvenile Detention',
 ##'Pinnacle Education-Casa Grande, Inc.',
 ##'Portable Practical Educational Preparation Inc. (PPEP In 1',
 ##'Portable Practical Educational Preparation Inc. (PPEP In 2',
 #'Prescott Valley Charter School',
 #'Presidio School',
 #'Ray of Light Academy',
 #'Rising Schools, Inc.',
 #'San Simon Unified District',
 #'Santa Cruz County Sheriffs Office',
 #'Shonto Governing Board of Education, Inc.',
 'Skyline Gila River Schools  LLC' : 'Skyline Gila River Schools, LLC',
 #'Sonoran Desert School',
 #'Southwest Technical Education District of Yuma (STEDY)',
 #'SySTEM Schools',
 #'Teleos Preparatory Academy',
 #'The Charter Foundation, Inc.',
 #'Toltec School District',
 'Vail Unified School District' : 'Vail Unified District',
 #'Valley Academy for Career and Technology Education',
 'Valley of the Sun Waldorf Education Association  dba Desert' : 'Valley of the Sun Waldorf Education Association, dba Desert Marigold School',
 #'Victory High School, Inc.',
 #'Vision Charter School, Inc.',
 #'Vista Charter School',
 #'West-MEC - Western Maricopa Education Center',
 #'Western Arizona Vocational District #50',
 #'Yavapai County Juvenile Justice Center',
 #'Young Elementary District',
 #'Yuma County Juvenile Justice Center',
 #'Yuma County Sheriffs Office'
}

fiscal_AZ = fiscal_AZ.replace(AZ_fiscal_rename)

# Examine data types and missing values.

AZ.info()

# Change column names for consistency across states. 

AZ = AZ[['LEA Name', 'Number in Cohort', 'Percent Graduated in 4 Years']]

AZ = AZ.rename(columns={'LEA Name' : 'District Name', 'Number in Cohort' : 'Total', 'Percent Graduated in 4 Years' : 'Graduation Rate'})

# Change data types.

AZ['Graduation Rate'] = pd.to_numeric(AZ['Graduation Rate']) / 100

AZ['Total'] = pd.to_numeric(AZ['Total'])

AZ = AZ.reset_index(drop=True)

sum(AZ['Total'])

# Merge federal and state data, keeping only matches between the two. 

AZ_merged = pd.merge(fiscal[fiscal['STABBR'] == 'AZ'], AZ, how='inner', left_on='NAME', right_on='District Name')

# Save cleaned data. 

AZ_merged.to_csv('../../data/state_data_merged/AZ.csv', index=False)

StarcoderdataJupyterScriptsDedupFiltered