<jupyter_start><jupyter_text>Describe your dataset. Describe and explore your dataset in the initial section of your Report. What does your data contain and what is its background? Where does it come from? Why is it interesting or significant? Conduct summary statistics and produce visualizations for the particular variables from the dataset that you will use.  Dataset: The Civil Rights Data Collection, school-level data for 2015-2016The Civil Rights Data Collection (CRDC) is a biennial survey required by the U.S. Department of Education’s (Department) Office for Civil Rights (OCR) since 1968. (Note, howerever, that survey content changes over time.)The 2015–16 CRDC collects data from all public local educational agencies (LEAs, ie *School Districts*) and schools, including - long-term secure juvenile justice facilities- charter schools- alternative schools- and schools serving students with disabilitieswith a responce rate of 99.8% from __17,337 LEAs and 96,360 schools__. Speicifically, I will be looking at the finer-grained *data disagregated by school*. Data content and formatEach school (row) in the dataset includes 1,800 columns (typically a student count disagregated by race and gender) regarding 32 general topics, (__the topics I will investigate are in bold__):__'Identification', 'School Characteristics', 'Enrollment','Suspensions', 'Expulsions', 'Transfers', 'Referrals and Arrests', 'Offenses', 'Restraint and Seclusion', 'Chronic Absenteeism', 'Retention', 'Justice Facility',__        'Gifted and Talented', 'Dual Enrollment', 'Credit Recovery',       'Algebra I', 'Geometry', 'Algebra II', 'Calculus',       'Advanced Mathematics', 'Biology', 'Chemistry', 'Physics',       'Single-sex Classes', 'Advanced Placement',       'International Baccalaureate', 'SAT and ACT Exams',       'Corporal Punishment',       'Harassment and Bullying',       'Single-Sex Athletics', 'School Expenditures', 'School Support',        Known issues with datasetFrom the CRDC report: "An important consideration for response rates is that the reporting process requires all schools and LEAs to respond to each survey item on the CRDC. Some LEAs, that did not have complete data, __reported a zero value__. It is not possible to determine all possible situations where this may have occurred. As such, it may be the case that the item response rates may be positively biased. For the large majority of CRDC survey items, __the rate of missing data ranged from 0-5% of reported values__."<jupyter_code>cols_description = pd.read_csv('data (download CSVs here)/CRDC 2015-16 School Data Record Layout.csv', encoding="ISO-8859-1")
cols_description['Module'].unique()
modules_of_interest = ['Identification', 'School Characteristics', 'Enrollment','Suspensions', 'Expulsions', 'Transfers', 'Referrals and Arrests', 'Offenses', 'Restraint and Seclusion', 'Chronic Absenteeism', 'Retention', 'School Support', 'Justice Facility']
# Get all cols within our modules of interest
cols_of_interest = cols_description[cols_description['Module'].isin(modules_of_interest)]
cols_of_interest['Field_Name']<jupyter_output><empty_output><jupyter_text>Load and Format the data (after downloading)[Download the 400MB file here.](https://drive.google.com/file/d/1yOqzjwinFp4THrDX0s543LtIEXO9B-Ap/view?usp=sharing)Note that this file is *identical* to the one that can be downloaded [on the CRDC website](https://www2.ed.gov/about/offices/list/ocr/data.html)<jupyter_code># In take data
DATA_FILE = 'data (download CSVs here)/CRDC 2015-16 School Data.csv'
crdc_data = pd.read_csv(
    DATA_FILE,
    usecols=cols_of_interest['Field_Name'],
    low_memory=False,
    encoding="ISO-8859-1"
)
crdc_data
crdc_data.dtypes<jupyter_output><empty_output><jupyter_text>The raw CRDC unhelpfully converted large ID values to scientific notation, so we'll reconstruct the IDs, which are a combination of district, school, and state IDs<jupyter_code>crdc_data[-1:]['COMBOKEY'].values[0]#get('COMBOKEY')
# LIMIT TO JUST NORTH CAROLINA (FOR DEVELOPMENT SPEED)
# data = crdc_data[crdc_data['LEA_STATE'] == 'NC']<jupyter_output><empty_output><jupyter_text>Create the NCESSCH field on the CRDC Data<jupyter_code>def f(row):
    return str(row['LEAID']) + str(row['SCHID']).zfill(5)

crdc_data['NCESSCH'] = crdc_data.apply(f, axis='columns')<jupyter_output><empty_output><jupyter_text>Merge with Lat/Long coordinate data  A. Merge with Public School Data<jupyter_code>LAT_LONG_FILE = 'EDGE_GEOCODE_PUBLICSCH_1516/EDGE_GEOCODE_PUBLICSCH_1516.csv'
lat_long_cols_of_interest = ['NCESSCH', 'LAT1516', 'LON1516', 'NAME']
school_lat_longs = pd.read_csv(
    LAT_LONG_FILE,
    usecols=lat_long_cols_of_interest,
    dtype={'NCESSCH': int},
    low_memory=False,
    encoding = "ISO-8859-1"
)
school_lat_longs['NCESSCH'] = school_lat_longs['NCESSCH'].apply(str)
school_lat_longs.dtypes
# Join on SCHOOL CODE. 
final = crdc_data.merge(
    school_lat_longs,
    how='left',
    left_on='NCESSCH',
    right_on='NCESSCH',
)
final<jupyter_output><empty_output><jupyter_text>We're still missing lat/long for a substantial  of schools<jupyter_code>plt.scatter(x=final['LON1516'], y=final['LAT1516'])<jupyter_output><empty_output><jupyter_text>B. Merge with LEA (District) Data<jupyter_code>LEA_LAT_LONG_FILE = 'EDGE_GEOCODE_PUBLICLEA_1516/EDGE_GEOCODE_PUBLICLEA_1516.csv'
lea_lat_long_cols_of_interest = ['LEAID', 'LAT1516', 'LON1516']
lea_lat_longs = pd.read_csv(
    LEA_LAT_LONG_FILE,
    usecols=lea_lat_long_cols_of_interest,
    dtype={'LEAID': str},
    low_memory=False,
    encoding = "ISO-8859-1"
)
lea_lat_longs = lea_lat_longs.rename(index=str, columns={"LAT1516": "LEA_LAT1516", "LON1516": "LEA_LON1516"})
lea_lat_longs
lea_lat_longs.dtypes
# Join on LEA CODE. 
final_2 = final.merge(
    lea_lat_longs,
    how='left',
    left_on='LEAID',
    right_on='LEAID',
)
final_2<jupyter_output><empty_output><jupyter_text>(942 Schools have *no* lat/long data available, not even for LEA)<jupyter_code>final_2[pd.isnull(final_2['LEA_LAT1516']) & pd.isnull(final_2['LAT1516'])]<jupyter_output><empty_output><jupyter_text>LEA lat/longs fill in the remaining schools locations (minus 942 schools)<jupyter_code>plt.scatter(x=final_2['LEA_LON1516'], y=final_2['LEA_LAT1516'])<jupyter_output><empty_output><jupyter_text>Where there were no school-level Lat/Long, use LEA (district) Lat/Longs.Note that there are 942 schools with neither school-level or LEA-level coordinates.<jupyter_code>final_2.loc[pd.isnull(final_2['LAT1516']), 'LAT1516'] = final_2['LEA_LAT1516']
final_2.loc[pd.isnull(final_2['LON1516']), 'LON1516'] = final_2['LEA_LON1516']

# final_2[pd.isnull(final_2['LON1516'])]
final_2.describe()<jupyter_output><empty_output><jupyter_text>Save CRDC data with Lat/Longs to CSV<jupyter_code>final_2.to_csv('crdc-data-with-lat-long.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10958302852962692, 'fraction_numerical': 0.03087051938551573, 'mean_word_length': 6.47103825136612, 'pattern_counts': {'":': 2, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '181541', 'license': 'MIT', 'n_tokens_mistral': 2471, 'n_tokens_neox': 2290, 'n_words': 611, 'path': 'Data prep - remove unneeded columns, add lat long coords.ipynb', 'repo_name': 'davidnmora/school-to-prison-pipeline', 'size': '6835'}