import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
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.
The 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
with 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.
Each 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',
From 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."
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()
array(['Identification', 'School Characteristics', 'Enrollment', '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', 'Suspensions', 'Expulsions', 'Transfers', 'Referrals and Arrests', 'Offenses', 'Restraint and Seclusion', 'Harassment and Bullying', 'Chronic Absenteeism', 'Retention', 'Single-Sex Athletics', 'School Expenditures', 'School Support', 'Justice Facility'], dtype=object)
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']
0 LEA_STATE 1 LEA_STATE_NAME 2 LEAID 3 LEA_NAME 4 SCHID 5 SCH_NAME 6 COMBOKEY 7 JJ 8 SCH_GRADE_PS 9 SCH_GRADE_KG 10 SCH_GRADE_G01 11 SCH_GRADE_G02 12 SCH_GRADE_G03 13 SCH_GRADE_G04 14 SCH_GRADE_G05 15 SCH_GRADE_G06 16 SCH_GRADE_G07 17 SCH_GRADE_G08 18 SCH_GRADE_G09 19 SCH_GRADE_G10 20 SCH_GRADE_G11 21 SCH_GRADE_G12 22 SCH_GRADE_UG 23 SCH_UGDETAIL_ES 24 SCH_UGDETAIL_MS 25 SCH_UGDETAIL_HS 26 SCH_STATUS_SPED 27 SCH_STATUS_MAGNET 28 SCH_STATUS_CHARTER 29 SCH_STATUS_ALT ... 1777 TOT_RET_G12_M 1778 TOT_RET_G12_F 1779 SCH_RET_G12_LEP_M 1780 SCH_RET_G12_LEP_F 1781 SCH_RET_G12_IDEA_M 1782 SCH_RET_G12_IDEA_F 1783 SCH_RET_G12_504_M 1784 SCH_RET_G12_504_F 1814 SCH_FTETEACH_TOT 1815 SCH_FTETEACH_CERT 1816 SCH_FTETEACH_NOTCERT 1817 SCH_FTETEACH_FY 1818 SCH_FTETEACH_SY 1819 SCH_TEACHERS_CURR_TOT 1820 SCH_TEACHERS_PREV_TOT 1821 SCH_FTECOUNSELORS 1822 SCH_FTETEACH_ABSENT 1823 SCH_FTESECURITY_LEO 1824 SCH_FTESECURITY_GUA 1825 SCH_FTESERVICES_NUR 1826 SCH_FTESERVICES_PSY 1827 SCH_FTESERVICES_SOC 1828 SCH_JJTYPE 1829 SCH_JJSYDAYS 1830 SCH_JJHOURS 1831 SCH_JJPART_LT15 1832 SCH_JJPART_15T30 1833 SCH_JJPART_31T90 1834 SCH_JJPART_91T180 1835 SCH_JJPART_OV180 Name: Field_Name, Length: 1056, dtype: object
Note that this file is identical to the one that can be downloaded on the CRDC website
# 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
--------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-6-a6ebbeaa2a63> in <module>() 3 crdc_data = pd.read_csv( 4 DATA_FILE, ----> 5 usecols=cols_of_interest['Field_Name'], 6 low_memory=False, 7 encoding="ISO-8859-1" NameError: name 'cols_of_interest' is not defined
crdc_data.dtypes
LEA_STATE object LEA_STATE_NAME object LEAID object LEA_NAME object SCHID object SCH_NAME object COMBOKEY object JJ object SCH_GRADE_PS object SCH_GRADE_KG object SCH_GRADE_G01 object SCH_GRADE_G02 object SCH_GRADE_G03 object SCH_GRADE_G04 object SCH_GRADE_G05 object SCH_GRADE_G06 object SCH_GRADE_G07 object SCH_GRADE_G08 object SCH_GRADE_G09 object SCH_GRADE_G10 object SCH_GRADE_G11 object SCH_GRADE_G12 object SCH_GRADE_UG object SCH_UGDETAIL_ES object SCH_UGDETAIL_MS object SCH_UGDETAIL_HS object SCH_STATUS_SPED object SCH_STATUS_MAGNET object SCH_STATUS_CHARTER object SCH_STATUS_ALT object ... TOT_RET_G12_M int64 TOT_RET_G12_F int64 SCH_RET_G12_LEP_M int64 SCH_RET_G12_LEP_F int64 SCH_RET_G12_IDEA_M int64 SCH_RET_G12_IDEA_F int64 SCH_RET_G12_504_M int64 SCH_RET_G12_504_F int64 SCH_FTETEACH_TOT float64 SCH_FTETEACH_CERT float64 SCH_FTETEACH_NOTCERT float64 SCH_FTETEACH_FY float64 SCH_FTETEACH_SY float64 SCH_TEACHERS_CURR_TOT int64 SCH_TEACHERS_PREV_TOT int64 SCH_FTECOUNSELORS float64 SCH_FTETEACH_ABSENT float64 SCH_FTESECURITY_LEO float64 SCH_FTESECURITY_GUA float64 SCH_FTESERVICES_NUR float64 SCH_FTESERVICES_PSY float64 SCH_FTESERVICES_SOC float64 SCH_JJTYPE object SCH_JJSYDAYS int64 SCH_JJHOURS int64 SCH_JJPART_LT15 int64 SCH_JJPART_15T30 int64 SCH_JJPART_31T90 int64 SCH_JJPART_91T180 int64 SCH_JJPART_OV180 int64 Length: 1056, dtype: object
crdc_data[-1:]['COMBOKEY'].values[0]#get('COMBOKEY')
# LIMIT TO JUST NORTH CAROLINA (FOR DEVELOPMENT SPEED)
# data = crdc_data[crdc_data['LEA_STATE'] == 'NC']
'5.68025E+11'
def f(row):
return str(row['LEAID']) + str(row['SCHID']).zfill(5)
crdc_data['NCESSCH'] = crdc_data.apply(f, axis='columns')
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
NCESSCH object NAME object LAT1516 float64 LON1516 float64 dtype: object
# Join on SCHOOL CODE.
final = crdc_data.merge(
school_lat_longs,
how='left',
left_on='NCESSCH',
right_on='NCESSCH',
)
final
LEA_STATE | LEA_STATE_NAME | LEAID | LEA_NAME | SCHID | SCH_NAME | COMBOKEY | JJ | SCH_GRADE_PS | SCH_GRADE_KG | ... | SCH_JJHOURS | SCH_JJPART_LT15 | SCH_JJPART_15T30 | SCH_JJPART_31T90 | SCH_JJPART_91T180 | SCH_JJPART_OV180 | NCESSCH | NAME | LAT1516 | LON1516 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | AL | ALABAMA | 100002 | Alabama Youth Services | 1705 | Wallace Sch - Mt Meigs Campus | 10000201705 | Yes | No | No | ... | -7 | -7 | -7 | -7 | -7 | -7 | 10000201705 | Wallace Sch - Mt Meigs Campus | 32.374812 | -86.082360 |
1 | AL | ALABAMA | 100002 | Alabama Youth Services | 1706 | McNeel Sch - Vacca Campus | 10000201706 | Yes | No | No | ... | -7 | -7 | -7 | -7 | -7 | -7 | 10000201706 | McNeel Sch - Vacca Campus | 33.583385 | -86.710058 |
2 | AL | ALABAMA | 100002 | Alabama Youth Services | 1876 | Alabama Youth Services | 10000201876 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000201876 | Alabama Youth Services | 32.374847 | -86.082332 |
3 | AL | ALABAMA | 100002 | Alabama Youth Services | 99995 | AUTAUGA CAMPUS | 10000299995 | Yes | No | No | ... | -7 | -7 | -7 | -7 | -7 | -7 | 10000299995 | NaN | NaN | NaN |
4 | AL | ALABAMA | 100005 | Albertville City | 870 | Albertville Middle School | 10000500870 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000500870 | Ala Avenue Middle Sch | 34.260194 | -86.206174 |
5 | AL | ALABAMA | 100005 | Albertville City | 871 | Albertville High Sch | 10000500871 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000500871 | Albertville High Sch | 34.262154 | -86.204863 |
6 | AL | ALABAMA | 100005 | Albertville City | 879 | Evans Elem Sch | 10000500879 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000500879 | Evans Elem Sch | 34.273161 | -86.220086 |
7 | AL | ALABAMA | 100005 | Albertville City | 889 | Albertville Elem Sch | 10000500889 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000500889 | Albertville Elem Sch | 34.253251 | -86.221834 |
8 | AL | ALABAMA | 100005 | Albertville City | 1616 | Big Spring Lake Kinderg Sch | 10000501616 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000501616 | Big Spring Lake Kinderg Sch | 34.290220 | -86.192490 |
9 | AL | ALABAMA | 100005 | Albertville City | 2150 | Albertville Primary Sch | 10000502150 | No | Yes | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000502150 | Albertville Primary Sch | 34.253251 | -86.221834 |
10 | AL | ALABAMA | 100006 | Marshall County | 193 | Kate Duncan Smith DAR Middle | 10000600193 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600193 | Kate Duncan Smith DAR Middle | 34.533721 | -86.253681 |
11 | AL | ALABAMA | 100006 | Marshall County | 872 | Asbury Sch | 10000600872 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600872 | Asbury Sch | 34.362770 | -86.142240 |
12 | AL | ALABAMA | 100006 | Marshall County | 876 | Claysville Jr High Sch | 10000600876 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600876 | Claysville Jr High Sch | 34.406429 | -86.270689 |
13 | AL | ALABAMA | 100006 | Marshall County | 877 | Douglas Elem Sch | 10000600877 | No | Yes | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600877 | Douglas Elem Sch | 34.176234 | -86.321259 |
14 | AL | ALABAMA | 100006 | Marshall County | 878 | Douglas High Sch | 10000600878 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600878 | Douglas High Sch | 34.178157 | -86.319947 |
15 | AL | ALABAMA | 100006 | Marshall County | 880 | Brindlee Mountain Elementary School | 10000600880 | No | Yes | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600880 | Grassy Elem Sch | 34.344388 | -86.442199 |
16 | AL | ALABAMA | 100006 | Marshall County | 883 | Kate D Smith DAR High Sch | 10000600883 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600883 | Kate D Smith DAR High Sch | 34.533721 | -86.253681 |
17 | AL | ALABAMA | 100006 | Marshall County | 887 | Brindlee Mountain Primary School | 10000600887 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000600887 | Union Grove Elem Sch | 34.399966 | -86.446812 |
18 | AL | ALABAMA | 100006 | Marshall County | 1413 | Robert D Sloman Primary | 10000601413 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000601413 | Robert D Sloman Primary | 34.176713 | -86.323279 |
19 | AL | ALABAMA | 100006 | Marshall County | 1434 | Brindlee Mt Middle Sch | 10000601434 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000601434 | Brindlee Mt Middle Sch | 34.377158 | -86.422337 |
20 | AL | ALABAMA | 100006 | Marshall County | 1585 | Brindlee Mt High Sch | 10000601585 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000601585 | Brindlee Mt High Sch | 34.376400 | -86.421876 |
21 | AL | ALABAMA | 100006 | Marshall County | 1685 | Kate D Smith DAR Elem Sch | 10000601685 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000601685 | Kate D Smith DAR Elem Sch | 34.533721 | -86.253681 |
22 | AL | ALABAMA | 100006 | Marshall County | 1812 | Douglas Middle Sch | 10000601812 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000601812 | Douglas Middle Sch | 34.176234 | -86.321259 |
23 | AL | ALABAMA | 100006 | Marshall County | 2209 | Asbury Elem Sch | 10000602209 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000602209 | Asbury Elem Sch | 34.362794 | -86.142507 |
24 | AL | ALABAMA | 100007 | Hoover City | 91 | Trace Crossings Elem Sch | 10000700091 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000700091 | Trace Crossings Elem Sch | 33.340886 | -86.844733 |
25 | AL | ALABAMA | 100007 | Hoover City | 248 | Greystone Elem Sch | 10000700248 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000700248 | Greystone Elem Sch | 33.413047 | -86.658547 |
26 | AL | ALABAMA | 100007 | Hoover City | 251 | Hoover High Sch | 10000700251 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000700251 | Hoover High Sch | 33.344370 | -86.837683 |
27 | AL | ALABAMA | 100007 | Hoover City | 337 | Berry Middle Sch | 10000700337 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000700337 | Berry Middle Sch | 33.395648 | -86.732180 |
28 | AL | ALABAMA | 100007 | Hoover City | 342 | South Shades Crest Elem Sch | 10000700342 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000700342 | South Shades Crest Elem Sch | 33.337527 | -86.878390 |
29 | AL | ALABAMA | 100007 | Hoover City | 1422 | Robert F Bumpus Middle Sch | 10000701422 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 10000701422 | Robert F Bumpus Middle Sch | 33.330911 | -86.852477 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
96330 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 332 | Washington Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560576200332 | NaN | NaN | NaN |
96331 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 399 | Lincoln Middle School | 5.60576E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560576200399 | NaN | NaN | NaN |
96332 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 424 | Jackson Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560576200424 | NaN | NaN | NaN |
96333 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 425 | Truman Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560576200425 | NaN | NaN | NaN |
96334 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 439 | Harrison Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560576200439 | NaN | NaN | NaN |
96335 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 528 | Thoman Ranch Elementary | 5.60576E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560576200528 | NaN | NaN | NaN |
96336 | WY | WYOMING | 5605820 | Washakie County School District #2 | 393 | Ten Sleep K-12 | 5.60582E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560582000393 | NaN | NaN | NaN |
96337 | WY | WYOMING | 5605830 | Teton County School District #1 | 289 | Colter Elementary | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000289 | NaN | NaN | NaN |
96338 | WY | WYOMING | 5605830 | Teton County School District #1 | 313 | Jackson Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000313 | NaN | NaN | NaN |
96339 | WY | WYOMING | 5605830 | Teton County School District #1 | 335 | Jackson Hole High School | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000335 | NaN | NaN | NaN |
96340 | WY | WYOMING | 5605830 | Teton County School District #1 | 336 | Jackson Hole Middle School | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000336 | NaN | NaN | NaN |
96341 | WY | WYOMING | 5605830 | Teton County School District #1 | 337 | Alta Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000337 | NaN | NaN | NaN |
96342 | WY | WYOMING | 5605830 | Teton County School District #1 | 339 | Kelly Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000339 | NaN | NaN | NaN |
96343 | WY | WYOMING | 5605830 | Teton County School District #1 | 340 | Moran Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000340 | NaN | NaN | NaN |
96344 | WY | WYOMING | 5605830 | Teton County School District #1 | 341 | Wilson Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000341 | NaN | NaN | NaN |
96345 | WY | WYOMING | 5605830 | Teton County School District #1 | 512 | Summit High School | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560583000512 | NaN | NaN | NaN |
96346 | WY | WYOMING | 5606090 | Weston County School District #7 | 342 | Upton Middle School | 5.60609E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560609000342 | NaN | NaN | NaN |
96347 | WY | WYOMING | 5606090 | Weston County School District #7 | 363 | Upton Elementary | 5.60609E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560609000363 | NaN | NaN | NaN |
96348 | WY | WYOMING | 5606090 | Weston County School District #7 | 401 | Upton High School | 5.60609E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560609000401 | NaN | NaN | NaN |
96349 | WY | WYOMING | 5606240 | Washakie County School District #1 | 343 | Worland High School | 5.60624E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560624000343 | NaN | NaN | NaN |
96350 | WY | WYOMING | 5606240 | Washakie County School District #1 | 344 | Worland Middle School | 5.60624E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560624000344 | NaN | NaN | NaN |
96351 | WY | WYOMING | 5606240 | Washakie County School District #1 | 345 | East Side Elementary | 5.60624E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560624000345 | NaN | NaN | NaN |
96352 | WY | WYOMING | 5606240 | Washakie County School District #1 | 346 | South Side Elementary | 5.60624E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560624000346 | NaN | NaN | NaN |
96353 | WY | WYOMING | 5606240 | Washakie County School District #1 | 347 | West Side Elementary | 5.60624E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 560624000347 | NaN | NaN | NaN |
96354 | WY | WYOMING | 5680180 | Northeast Wyoming BOCES | 31 | Powder River Basin Children's Center | 5.68018E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 568018000031 | NaN | NaN | NaN |
96355 | WY | WYOMING | 5680250 | Region V BOCES | 48 | C-Bar-V Ranch | 5.68025E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 568025000048 | NaN | NaN | NaN |
96356 | WY | WYOMING | 5680251 | Wyoming Department of Family Services | 534 | Wyoming Girls School | 5.68025E+11 | Yes | No | No | ... | 27 | 2 | 5 | 8 | 17 | 50 | 568025100534 | NaN | NaN | NaN |
96357 | WY | WYOMING | 5680251 | Wyoming Department of Family Services | 538 | Wyoming Boys School | 5.68025E+11 | Yes | No | No | ... | 30 | 17 | 11 | 68 | 86 | 8 | 568025100538 | NaN | NaN | NaN |
96358 | WY | WYOMING | 5680252 | Youth Emergency Services Inc. - Administration... | 350 | Youth Emergency Services Inc. | 5.68025E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | -9 | -9 | 568025200350 | NaN | NaN | NaN |
96359 | WY | WYOMING | 5680254 | Saint Stephen's Indian School Admin Office | \t | Saint Stephen's Indian School | 5.68025E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | -9 | -9 | 56802540000\t | NaN | NaN | NaN |
96360 rows × 1060 columns
plt.scatter(x=final['LON1516'], y=final['LAT1516'])
<matplotlib.collections.PathCollection at 0x126270668>
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
LEAID | LEA_LAT1516 | LEA_LON1516 | |
---|---|---|---|
0 | 0100240 | 32.462750 | -86.474211 |
1 | 0100270 | 30.911431 | -87.787485 |
2 | 0100300 | 31.878283 | -85.453785 |
3 | 0101410 | 31.868304 | -85.151290 |
4 | 0100028 | 33.167521 | -87.191200 |
5 | 0100360 | 32.945405 | -87.143254 |
6 | 0100420 | 33.945640 | -86.476448 |
7 | 0102550 | 33.933262 | -86.495930 |
8 | 0100480 | 32.144541 | -85.716350 |
9 | 0100510 | 31.815298 | -86.628715 |
10 | 0100090 | 33.707086 | -85.821631 |
11 | 0100540 | 33.697530 | -85.824331 |
12 | 0101860 | 33.815375 | -85.761522 |
13 | 0102635 | 33.615880 | -85.835271 |
14 | 0102760 | 33.926817 | -85.615822 |
15 | 0100600 | 32.890345 | -85.382695 |
16 | 0101980 | 32.868360 | -85.191461 |
17 | 0100630 | 34.151788 | -85.678430 |
18 | 0100660 | 32.868550 | -86.621991 |
19 | 0100690 | 32.079440 | -88.233236 |
20 | 0100720 | 31.709818 | -87.780277 |
21 | 0103300 | 31.887730 | -87.735221 |
22 | 0100750 | 33.274462 | -85.835284 |
23 | 0100780 | 33.677803 | -85.552196 |
24 | 0100038 | 31.418700 | -85.894400 |
25 | 0100810 | 31.410900 | -86.056300 |
26 | 0101260 | 31.428518 | -86.101554 |
27 | 0101320 | 31.323218 | -85.851124 |
28 | 0100840 | 34.714834 | -87.702449 |
29 | 0102520 | 34.733552 | -87.635204 |
... | ... | ... | ... |
18832 | 5600014 | 44.775594 | -107.016285 |
18833 | 5605680 | 44.637273 | -106.381798 |
18834 | 5605690 | 44.906370 | -107.173864 |
18835 | 5605695 | 44.799510 | -106.951301 |
18836 | 5601260 | 42.538186 | -110.114744 |
18837 | 5604860 | 42.872895 | -109.861242 |
18838 | 5605302 | 41.587910 | -109.252991 |
18839 | 5605762 | 41.510680 | -109.465821 |
18840 | 5600015 | 43.434100 | -110.860500 |
18841 | 5605830 | 43.462312 | -110.797767 |
18842 | 5680250 | 43.535575 | -110.830607 |
18843 | 5602760 | 41.265200 | -110.968900 |
18844 | 5604260 | 41.327948 | -110.290546 |
18845 | 5604500 | 41.270593 | -110.340170 |
18846 | 5605820 | 44.036012 | -107.447922 |
18847 | 5606240 | 44.011520 | -107.943721 |
18848 | 5604830 | 43.851100 | -104.208200 |
18849 | 5606090 | 44.101000 | -104.623594 |
18850 | 6300019 | 13.475196 | 144.736443 |
18851 | 6600002 | 13.476911 | 144.800161 |
18852 | 7200030 | 18.155370 | -65.987770 |
18853 | 7800002 | 17.702500 | -64.866900 |
18854 | 7800030 | 18.336545 | -64.889056 |
18855 | 6300007 | 49.305346 | 10.635923 |
18856 | 6300010 | 37.527461 | 126.980962 |
18857 | 6300011 | 26.346153 | 127.784655 |
18858 | 6300023 | 52.394301 | 0.536978 |
18859 | 6300024 | 49.428840 | 7.697105 |
18860 | 6300025 | 45.548835 | 11.578043 |
18861 | 6300026 | 35.729377 | 139.345405 |
18862 rows × 3 columns
lea_lat_longs.dtypes
LEAID object LEA_LAT1516 float64 LEA_LON1516 float64 dtype: object
# Join on LEA CODE.
final_2 = final.merge(
lea_lat_longs,
how='left',
left_on='LEAID',
right_on='LEAID',
)
final_2
LEA_STATE | LEA_STATE_NAME | LEAID | LEA_NAME | SCHID | SCH_NAME | COMBOKEY | JJ | SCH_GRADE_PS | SCH_GRADE_KG | ... | SCH_JJPART_15T30 | SCH_JJPART_31T90 | SCH_JJPART_91T180 | SCH_JJPART_OV180 | NCESSCH | NAME | LAT1516 | LON1516 | LEA_LAT1516 | LEA_LON1516 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | AL | ALABAMA | 100002 | Alabama Youth Services | 1705 | Wallace Sch - Mt Meigs Campus | 10000201705 | Yes | No | No | ... | -7 | -7 | -7 | -7 | 10000201705 | Wallace Sch - Mt Meigs Campus | 32.374812 | -86.082360 | NaN | NaN |
1 | AL | ALABAMA | 100002 | Alabama Youth Services | 1706 | McNeel Sch - Vacca Campus | 10000201706 | Yes | No | No | ... | -7 | -7 | -7 | -7 | 10000201706 | McNeel Sch - Vacca Campus | 33.583385 | -86.710058 | NaN | NaN |
2 | AL | ALABAMA | 100002 | Alabama Youth Services | 1876 | Alabama Youth Services | 10000201876 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000201876 | Alabama Youth Services | 32.374847 | -86.082332 | NaN | NaN |
3 | AL | ALABAMA | 100002 | Alabama Youth Services | 99995 | AUTAUGA CAMPUS | 10000299995 | Yes | No | No | ... | -7 | -7 | -7 | -7 | 10000299995 | NaN | NaN | NaN | NaN | NaN |
4 | AL | ALABAMA | 100005 | Albertville City | 870 | Albertville Middle School | 10000500870 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000500870 | Ala Avenue Middle Sch | 34.260194 | -86.206174 | NaN | NaN |
5 | AL | ALABAMA | 100005 | Albertville City | 871 | Albertville High Sch | 10000500871 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000500871 | Albertville High Sch | 34.262154 | -86.204863 | NaN | NaN |
6 | AL | ALABAMA | 100005 | Albertville City | 879 | Evans Elem Sch | 10000500879 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000500879 | Evans Elem Sch | 34.273161 | -86.220086 | NaN | NaN |
7 | AL | ALABAMA | 100005 | Albertville City | 889 | Albertville Elem Sch | 10000500889 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000500889 | Albertville Elem Sch | 34.253251 | -86.221834 | NaN | NaN |
8 | AL | ALABAMA | 100005 | Albertville City | 1616 | Big Spring Lake Kinderg Sch | 10000501616 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10000501616 | Big Spring Lake Kinderg Sch | 34.290220 | -86.192490 | NaN | NaN |
9 | AL | ALABAMA | 100005 | Albertville City | 2150 | Albertville Primary Sch | 10000502150 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10000502150 | Albertville Primary Sch | 34.253251 | -86.221834 | NaN | NaN |
10 | AL | ALABAMA | 100006 | Marshall County | 193 | Kate Duncan Smith DAR Middle | 10000600193 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000600193 | Kate Duncan Smith DAR Middle | 34.533721 | -86.253681 | NaN | NaN |
11 | AL | ALABAMA | 100006 | Marshall County | 872 | Asbury Sch | 10000600872 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000600872 | Asbury Sch | 34.362770 | -86.142240 | NaN | NaN |
12 | AL | ALABAMA | 100006 | Marshall County | 876 | Claysville Jr High Sch | 10000600876 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10000600876 | Claysville Jr High Sch | 34.406429 | -86.270689 | NaN | NaN |
13 | AL | ALABAMA | 100006 | Marshall County | 877 | Douglas Elem Sch | 10000600877 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10000600877 | Douglas Elem Sch | 34.176234 | -86.321259 | NaN | NaN |
14 | AL | ALABAMA | 100006 | Marshall County | 878 | Douglas High Sch | 10000600878 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000600878 | Douglas High Sch | 34.178157 | -86.319947 | NaN | NaN |
15 | AL | ALABAMA | 100006 | Marshall County | 880 | Brindlee Mountain Elementary School | 10000600880 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10000600880 | Grassy Elem Sch | 34.344388 | -86.442199 | NaN | NaN |
16 | AL | ALABAMA | 100006 | Marshall County | 883 | Kate D Smith DAR High Sch | 10000600883 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000600883 | Kate D Smith DAR High Sch | 34.533721 | -86.253681 | NaN | NaN |
17 | AL | ALABAMA | 100006 | Marshall County | 887 | Brindlee Mountain Primary School | 10000600887 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10000600887 | Union Grove Elem Sch | 34.399966 | -86.446812 | NaN | NaN |
18 | AL | ALABAMA | 100006 | Marshall County | 1413 | Robert D Sloman Primary | 10000601413 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10000601413 | Robert D Sloman Primary | 34.176713 | -86.323279 | NaN | NaN |
19 | AL | ALABAMA | 100006 | Marshall County | 1434 | Brindlee Mt Middle Sch | 10000601434 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000601434 | Brindlee Mt Middle Sch | 34.377158 | -86.422337 | NaN | NaN |
20 | AL | ALABAMA | 100006 | Marshall County | 1585 | Brindlee Mt High Sch | 10000601585 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000601585 | Brindlee Mt High Sch | 34.376400 | -86.421876 | NaN | NaN |
21 | AL | ALABAMA | 100006 | Marshall County | 1685 | Kate D Smith DAR Elem Sch | 10000601685 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10000601685 | Kate D Smith DAR Elem Sch | 34.533721 | -86.253681 | NaN | NaN |
22 | AL | ALABAMA | 100006 | Marshall County | 1812 | Douglas Middle Sch | 10000601812 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000601812 | Douglas Middle Sch | 34.176234 | -86.321259 | NaN | NaN |
23 | AL | ALABAMA | 100006 | Marshall County | 2209 | Asbury Elem Sch | 10000602209 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10000602209 | Asbury Elem Sch | 34.362794 | -86.142507 | NaN | NaN |
24 | AL | ALABAMA | 100007 | Hoover City | 91 | Trace Crossings Elem Sch | 10000700091 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10000700091 | Trace Crossings Elem Sch | 33.340886 | -86.844733 | NaN | NaN |
25 | AL | ALABAMA | 100007 | Hoover City | 248 | Greystone Elem Sch | 10000700248 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 10000700248 | Greystone Elem Sch | 33.413047 | -86.658547 | NaN | NaN |
26 | AL | ALABAMA | 100007 | Hoover City | 251 | Hoover High Sch | 10000700251 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000700251 | Hoover High Sch | 33.344370 | -86.837683 | NaN | NaN |
27 | AL | ALABAMA | 100007 | Hoover City | 337 | Berry Middle Sch | 10000700337 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000700337 | Berry Middle Sch | 33.395648 | -86.732180 | NaN | NaN |
28 | AL | ALABAMA | 100007 | Hoover City | 342 | South Shades Crest Elem Sch | 10000700342 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 10000700342 | South Shades Crest Elem Sch | 33.337527 | -86.878390 | NaN | NaN |
29 | AL | ALABAMA | 100007 | Hoover City | 1422 | Robert F Bumpus Middle Sch | 10000701422 | No | No | No | ... | -9 | -9 | -9 | -9 | 10000701422 | Robert F Bumpus Middle Sch | 33.330911 | -86.852477 | NaN | NaN |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
96330 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 332 | Washington Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560576200332 | NaN | NaN | NaN | 41.510680 | -109.465821 |
96331 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 399 | Lincoln Middle School | 5.60576E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560576200399 | NaN | NaN | NaN | 41.510680 | -109.465821 |
96332 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 424 | Jackson Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560576200424 | NaN | NaN | NaN | 41.510680 | -109.465821 |
96333 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 425 | Truman Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560576200425 | NaN | NaN | NaN | 41.510680 | -109.465821 |
96334 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 439 | Harrison Elementary | 5.60576E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560576200439 | NaN | NaN | NaN | 41.510680 | -109.465821 |
96335 | WY | WYOMING | 5605762 | Sweetwater County School District #2 | 528 | Thoman Ranch Elementary | 5.60576E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560576200528 | NaN | NaN | NaN | 41.510680 | -109.465821 |
96336 | WY | WYOMING | 5605820 | Washakie County School District #2 | 393 | Ten Sleep K-12 | 5.60582E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560582000393 | NaN | NaN | NaN | 44.036012 | -107.447922 |
96337 | WY | WYOMING | 5605830 | Teton County School District #1 | 289 | Colter Elementary | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560583000289 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96338 | WY | WYOMING | 5605830 | Teton County School District #1 | 313 | Jackson Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560583000313 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96339 | WY | WYOMING | 5605830 | Teton County School District #1 | 335 | Jackson Hole High School | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560583000335 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96340 | WY | WYOMING | 5605830 | Teton County School District #1 | 336 | Jackson Hole Middle School | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560583000336 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96341 | WY | WYOMING | 5605830 | Teton County School District #1 | 337 | Alta Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560583000337 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96342 | WY | WYOMING | 5605830 | Teton County School District #1 | 339 | Kelly Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560583000339 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96343 | WY | WYOMING | 5605830 | Teton County School District #1 | 340 | Moran Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560583000340 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96344 | WY | WYOMING | 5605830 | Teton County School District #1 | 341 | Wilson Elementary | 5.60583E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560583000341 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96345 | WY | WYOMING | 5605830 | Teton County School District #1 | 512 | Summit High School | 5.60583E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560583000512 | NaN | NaN | NaN | 43.462312 | -110.797767 |
96346 | WY | WYOMING | 5606090 | Weston County School District #7 | 342 | Upton Middle School | 5.60609E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560609000342 | NaN | NaN | NaN | 44.101000 | -104.623594 |
96347 | WY | WYOMING | 5606090 | Weston County School District #7 | 363 | Upton Elementary | 5.60609E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560609000363 | NaN | NaN | NaN | 44.101000 | -104.623594 |
96348 | WY | WYOMING | 5606090 | Weston County School District #7 | 401 | Upton High School | 5.60609E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560609000401 | NaN | NaN | NaN | 44.101000 | -104.623594 |
96349 | WY | WYOMING | 5606240 | Washakie County School District #1 | 343 | Worland High School | 5.60624E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560624000343 | NaN | NaN | NaN | 44.011520 | -107.943721 |
96350 | WY | WYOMING | 5606240 | Washakie County School District #1 | 344 | Worland Middle School | 5.60624E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 560624000344 | NaN | NaN | NaN | 44.011520 | -107.943721 |
96351 | WY | WYOMING | 5606240 | Washakie County School District #1 | 345 | East Side Elementary | 5.60624E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560624000345 | NaN | NaN | NaN | 44.011520 | -107.943721 |
96352 | WY | WYOMING | 5606240 | Washakie County School District #1 | 346 | South Side Elementary | 5.60624E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560624000346 | NaN | NaN | NaN | 44.011520 | -107.943721 |
96353 | WY | WYOMING | 5606240 | Washakie County School District #1 | 347 | West Side Elementary | 5.60624E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 560624000347 | NaN | NaN | NaN | 44.011520 | -107.943721 |
96354 | WY | WYOMING | 5680180 | Northeast Wyoming BOCES | 31 | Powder River Basin Children's Center | 5.68018E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 568018000031 | NaN | NaN | NaN | 44.297605 | -105.494905 |
96355 | WY | WYOMING | 5680250 | Region V BOCES | 48 | C-Bar-V Ranch | 5.68025E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 568025000048 | NaN | NaN | NaN | 43.535575 | -110.830607 |
96356 | WY | WYOMING | 5680251 | Wyoming Department of Family Services | 534 | Wyoming Girls School | 5.68025E+11 | Yes | No | No | ... | 5 | 8 | 17 | 50 | 568025100534 | NaN | NaN | NaN | 41.138600 | -104.819200 |
96357 | WY | WYOMING | 5680251 | Wyoming Department of Family Services | 538 | Wyoming Boys School | 5.68025E+11 | Yes | No | No | ... | 11 | 68 | 86 | 8 | 568025100538 | NaN | NaN | NaN | 41.138600 | -104.819200 |
96358 | WY | WYOMING | 5680252 | Youth Emergency Services Inc. - Administration... | 350 | Youth Emergency Services Inc. | 5.68025E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 568025200350 | NaN | NaN | NaN | 44.296500 | -105.494900 |
96359 | WY | WYOMING | 5680254 | Saint Stephen's Indian School Admin Office | \t | Saint Stephen's Indian School | 5.68025E+11 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 56802540000\t | NaN | NaN | NaN | 42.985268 | -108.420787 |
96360 rows × 1062 columns
final_2[pd.isnull(final_2['LEA_LAT1516']) & pd.isnull(final_2['LAT1516'])]
LEA_STATE | LEA_STATE_NAME | LEAID | LEA_NAME | SCHID | SCH_NAME | COMBOKEY | JJ | SCH_GRADE_PS | SCH_GRADE_KG | ... | SCH_JJPART_15T30 | SCH_JJPART_31T90 | SCH_JJPART_91T180 | SCH_JJPART_OV180 | NCESSCH | NAME | LAT1516 | LON1516 | LEA_LAT1516 | LEA_LON1516 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
3 | AL | ALABAMA | 100002 | Alabama Youth Services | 99995 | AUTAUGA CAMPUS | 10000299995 | Yes | No | No | ... | -7 | -7 | -7 | -7 | 10000299995 | NaN | NaN | NaN | NaN | NaN |
69 | AL | ALABAMA | 100021 | Brewer-Porch Childrens Center | 1832 | Brewer Porch Children's Ctr | 10002101832 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10002101832 | NaN | NaN | NaN | NaN | NaN |
70 | AL | ALABAMA | 100022 | Sequel TSI Courtland | 1833 | Three Springs Courtland Sch | 10002201833 | No | No | No | ... | -9 | -9 | -9 | -9 | 10002201833 | NaN | NaN | NaN | NaN | NaN |
71 | AL | ALABAMA | 100023 | Sequel Madison TSI | 1834 | Sequel Madison TSI | 10002301834 | No | No | No | ... | -9 | -9 | -9 | -9 | 10002301834 | NaN | NaN | NaN | NaN | NaN |
72 | AL | ALABAMA | 100024 | Sequel TSI- New Beginnings School/ Owens Cross... | 1835 | Three Springs New Beginnings | 10002401835 | No | No | No | ... | -9 | -9 | -9 | -9 | 10002401835 | NaN | NaN | NaN | NaN | NaN |
73 | AL | ALABAMA | 100026 | Sequel TSI Tuskegee | 2352 | Sequel TSI Tuskegee LLC | 10002602352 | No | No | No | ... | -9 | -9 | -9 | -9 | 10002602352 | NaN | NaN | NaN | NaN | NaN |
79 | AL | ALABAMA | 100033 | Glenwood Mental Health Services | 99998 | Allan Cott | 10003399998 | No | No | No | ... | -9 | -9 | -9 | -9 | 10003399998 | NaN | NaN | NaN | NaN | NaN |
80 | AL | ALABAMA | 100033 | Glenwood Mental Health Services | 99999 | Lakeview | 10003399999 | No | No | Yes | ... | -9 | -9 | -9 | -9 | 10003399999 | NaN | NaN | NaN | NaN | NaN |
81 | AL | ALABAMA | 100035 | Learning Tree Inc | 1845 | Learning Tree Inc Semmes | 10003501845 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10003501845 | NaN | NaN | NaN | NaN | NaN |
82 | AL | ALABAMA | 100035 | Learning Tree Inc | 1846 | Learning Tree Inc Webb | 10003501846 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10003501846 | NaN | NaN | NaN | NaN | NaN |
83 | AL | ALABAMA | 100035 | Learning Tree Inc | 2105 | The Learning Tree-Tallassee | 10003502105 | No | No | No | ... | -9 | -9 | -9 | -9 | 10003502105 | NaN | NaN | NaN | NaN | NaN |
84 | AL | ALABAMA | 100036 | Lee Co Youth Development Ctr | 99999 | CHANTICLEER LEARNING CENTER | 10003699999 | No | No | No | ... | -9 | -9 | -9 | -9 | 10003699999 | NaN | NaN | NaN | NaN | NaN |
86 | AL | ALABAMA | 100039 | Presbyterian Home For Children | 1850 | Hope Academy | 10003901850 | No | No | No | ... | -9 | -9 | -9 | -9 | 10003901850 | NaN | NaN | NaN | NaN | NaN |
88 | AL | ALABAMA | 100042 | Laurel Oaks Behavioral Health | 1853 | Laurel Oaks Behavioral Health Ctr | 10004201853 | No | No | No | ... | -9 | -9 | -9 | -9 | 10004201853 | NaN | NaN | NaN | NaN | NaN |
89 | AL | ALABAMA | 100043 | Mountainview Hospital | 1854 | Mountainview Hospital | 10004301854 | No | No | No | ... | -9 | -9 | -9 | -9 | 10004301854 | NaN | NaN | NaN | NaN | NaN |
114 | AL | ALABAMA | 100178 | Higdon Hill School | 2374 | Higdon Hill | 10017802374 | No | No | No | ... | -9 | -9 | -9 | -9 | 10017802374 | NaN | NaN | NaN | NaN | NaN |
115 | AL | ALABAMA | 100180 | Attalla City | 32 | ATTALLA ELEMENTARY | 10018000032 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10018000032 | NaN | NaN | NaN | NaN | NaN |
125 | AL | ALABAMA | 100188 | Chickasaw City | 99999 | Chickasaw Early Learning Center | 10018899999 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10018899999 | NaN | NaN | NaN | NaN | NaN |
214 | AL | ALABAMA | 100330 | Bessemer City | 99999 | BESSEMER PRESCHOOL | 10033099999 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10033099999 | NaN | NaN | NaN | NaN | NaN |
222 | AL | ALABAMA | 100360 | Bibb County | 99998 | BIBB CO PRESCHOOL | 10036099998 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10036099998 | NaN | NaN | NaN | NaN | NaN |
223 | AL | ALABAMA | 100360 | Bibb County | 99999 | CENTREVILLE MIDDLE SCHOOL | 10036099999 | No | No | No | ... | -9 | -9 | -9 | -9 | 10036099999 | NaN | NaN | NaN | NaN | NaN |
266 | AL | ALABAMA | 100390 | Birmingham City | 99996 | Jones Valley Middle School | 10039099996 | No | No | No | ... | -9 | -9 | -9 | -9 | 10039099996 | NaN | NaN | NaN | NaN | NaN |
267 | AL | ALABAMA | 100390 | Birmingham City | 99999 | Oliver Elementary | 10039099999 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 10039099999 | NaN | NaN | NaN | NaN | NaN |
375 | AL | ALABAMA | 100840 | Colbert County | 99999 | Tennessee Valley Juvenile Detention Center | 10084099999 | No | No | No | ... | -9 | -9 | -9 | -9 | 10084099999 | NaN | NaN | NaN | NaN | NaN |
435 | AL | ALABAMA | 101050 | Dale County | 2373 | Unique Situations | 10105002373 | No | No | No | ... | -9 | -9 | -9 | -9 | 10105002373 | NaN | NaN | NaN | NaN | NaN |
461 | AL | ALABAMA | 101140 | Dekalb County | 99999 | Crossville MIddle School | 10114099999 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10114099999 | NaN | NaN | NaN | NaN | NaN |
500 | AL | ALABAMA | 101230 | Dothan City | 1634 | DCS Head Start Preschool Center | 10123001634 | No | Yes | No | ... | -9 | -9 | -9 | -9 | 10123001634 | NaN | NaN | NaN | NaN | NaN |
519 | AL | ALABAMA | 101290 | Elmore County | 99998 | ELMORE COUNTY ALTERNATIVE PROGRAMs | 10129099998 | No | No | No | ... | -9 | -9 | -9 | -9 | 10129099998 | NaN | NaN | NaN | NaN | NaN |
652 | AL | ALABAMA | 101740 | Henry County | 99999 | Camp Sayla | 10174099999 | No | No | No | ... | -9 | -9 | -9 | -9 | 10174099999 | NaN | NaN | NaN | NaN | NaN |
834 | AL | ALABAMA | 102070 | Lee County | 99999 | Lee County Learning Center | 10207099999 | No | No | No | ... | -9 | -9 | -9 | -9 | 10207099999 | NaN | NaN | NaN | NaN | NaN |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
71749 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99989 | NORCOR | 4.2E+11 | Yes | No | No | ... | 41 | 29 | 8 | 0 | 419999999989 | NaN | NaN | NaN | NaN | NaN |
71750 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99990 | YAMHILL COUNTY JUVENILE | 4.2E+11 | Yes | No | No | ... | 38 | 17 | 0 | 0 | 419999999990 | NaN | NaN | NaN | NaN | NaN |
71751 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99992 | DONALD E LONG SCHOOL | 4.2E+11 | Yes | No | No | ... | 164 | 122 | 11 | 2 | 419999999992 | NaN | NaN | NaN | NaN | NaN |
71752 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99993 | MARION COUNTY JUV DET | 4.2E+11 | Yes | No | No | ... | 68 | 44 | 5 | 0 | 419999999993 | NaN | NaN | NaN | NaN | NaN |
71753 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99994 | LINN COUNTY JUV DET | 4.2E+11 | Yes | No | No | ... | 29 | 20 | 0 | 0 | 419999999994 | NaN | NaN | NaN | NaN | NaN |
71754 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99995 | LINCOLN COUNTY JUVENILE | 4.2E+11 | Yes | No | No | ... | 2 | 8 | 0 | 0 | 419999999995 | NaN | NaN | NaN | NaN | NaN |
71755 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99996 | LANE COUNTY DEPT OF YOUTH | 4.2E+11 | Yes | No | No | ... | 44 | 14 | 0 | 0 | 419999999996 | NaN | NaN | NaN | NaN | NaN |
71756 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99997 | JACKSON COUNTY JUVENILE | 4.2E+11 | Yes | No | No | ... | 53 | 32 | 2 | 0 | 419999999997 | NaN | NaN | NaN | NaN | NaN |
71757 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99998 | KLAMATH COUNTY JUVENILE | 4.2E+11 | Yes | No | No | ... | 29 | 26 | 0 | 0 | 419999999998 | NaN | NaN | NaN | NaN | NaN |
71758 | OR | OREGON | 4199999 | ODE Juvenile Detention Education Program (JDEP) | 99999 | COIC SKILL LAB | 4.2E+11 | Yes | No | No | ... | 35 | 17 | 0 | 0 | 419999999999 | NaN | NaN | NaN | NaN | NaN |
71871 | PA | PENNSYLVANIA | 4200779 | ACLD TILLOTSON SCHOOL | 99999 | ACLD TILLOTSON SCHOOL | 4.20078E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420077999999 | NaN | NaN | NaN | NaN | NaN |
71872 | PA | PENNSYLVANIA | 4200782 | ROYER-GREAVES SCHOOL FOR BLIND | 99999 | ROYER-GREAVES SCHOOL FOR BLIND | 4.20078E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420078299999 | NaN | NaN | NaN | NaN | NaN |
71873 | PA | PENNSYLVANIA | 4200784 | WESTERN PA SCHOOL FOR BLIND CHILDREN | 99999 | WESTERN PA SCHOOL FOR BLIND CHILDREN | 4.20078E+11 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 420078499999 | NaN | NaN | NaN | NaN | NaN |
71874 | PA | PENNSYLVANIA | 4200796 | CENTENNIAL SCHOOL LEHIGH UNIV | 99999 | CENTENNIAL SCHOOL OF LEHIGH UNIVERSITY | 4.2008E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420079699999 | NaN | NaN | NaN | NaN | NaN |
71875 | PA | PENNSYLVANIA | 4200801 | SCI-FOREST | 99999 | SCI-FOREST | 4.2008E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420080199999 | NaN | NaN | NaN | NaN | NaN |
71876 | PA | PENNSYLVANIA | 4200802 | SCI-MUNCY | 99999 | SCI-Muncy | 4.2008E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420080299999 | NaN | NaN | NaN | NaN | NaN |
71877 | PA | PENNSYLVANIA | 4200803 | SCI-SOMERSET | 99999 | SCI Somerset | 4.2008E+11 | Yes | No | No | ... | -7 | -7 | -7 | -7 | 420080399999 | NaN | NaN | NaN | NaN | NaN |
71878 | PA | PENNSYLVANIA | 4200805 | SCI-HOUTZDALE | 99999 | SCI-HOUTZDALE | 4.20081E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420080599999 | NaN | NaN | NaN | NaN | NaN |
71879 | PA | PENNSYLVANIA | 4200806 | SCI- Camp Hill | 99999 | SCI- Camp Hill | 4.20081E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420080699999 | NaN | NaN | NaN | NaN | NaN |
71880 | PA | PENNSYLVANIA | 4200807 | SCI-COAL TOWNSHIP | 99999 | SCI-Coal Township | 4.20081E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420080799999 | NaN | NaN | NaN | NaN | NaN |
71881 | PA | PENNSYLVANIA | 4200808 | Sci-Retreat | 99999 | SCI Retreat | 4.20081E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420080899999 | NaN | NaN | NaN | NaN | NaN |
71882 | PA | PENNSYLVANIA | 4200809 | SCI-PINE GROVE | 99999 | SCI PINE GROVE | 4.20081E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420080999999 | NaN | NaN | NaN | NaN | NaN |
71884 | PA | PENNSYLVANIA | 4200811 | FRANKLIN LEARNING CENTER | 99999 | FRANKLIN LEARNING CENTER | 4.20081E+11 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 420081199999 | NaN | NaN | NaN | NaN | NaN |
71939 | PA | PENNSYLVANIA | 4200871 | Sci-Dallas | 99999 | SCI-Dallas | 4.20087E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 420087199999 | NaN | NaN | NaN | NaN | NaN |
74735 | PA | PENNSYLVANIA | 4226830 | YORK ADAMS ACADEMY | 99999 | York Adams Academy | 4.22683E+11 | No | No | No | ... | -9 | -9 | -9 | -9 | 422683099999 | NaN | NaN | NaN | NaN | NaN |
74784 | PA | PENNSYLVANIA | 4299091 | Western Pennsylvania School for the Deaf | 99998 | Scranton School for Deaf and Hard of Hearing C... | 4.29909E+11 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 429909199998 | NaN | NaN | NaN | NaN | NaN |
74785 | PA | PENNSYLVANIA | 4299091 | Western Pennsylvania School for the Deaf | 99999 | Western Pennsylvania School for the Deaf | 4.29909E+11 | No | Yes | Yes | ... | -9 | -9 | -9 | -9 | 429909199999 | NaN | NaN | NaN | NaN | NaN |
78833 | TN | TENNESSEE | 47SOP04 | TENNESSEE DEPARTMENT OF CHILDREN'S SERVICES | 99991 | WOODLAND HILLS YOUTH DEVELOPMENT CENTER | 47SOP0499991 | Yes | No | No | ... | 0 | 20 | 41 | 20 | 47SOP0499991 | NaN | NaN | NaN | NaN | NaN |
78834 | TN | TENNESSEE | 47SOP04 | TENNESSEE DEPARTMENT OF CHILDREN'S SERVICES | 99994 | JOHN S. WILDER YOUTH DEVELOPMENT CENTER | 47SOP0499994 | Yes | No | No | ... | 11 | 41 | 80 | 83 | 47SOP0499994 | NaN | NaN | NaN | NaN | NaN |
78835 | TN | TENNESSEE | 47SOP04 | TENNESSEE DEPARTMENT OF CHILDREN'S SERVICES | 99999 | MOUNTAIN VIEW YOUTH DEVELOPMENT CENTER | 47SOP0499999 | Yes | No | No | ... | 8 | 29 | 47 | 44 | 47SOP0499999 | NaN | NaN | NaN | NaN | NaN |
942 rows × 1062 columns
plt.scatter(x=final_2['LEA_LON1516'], y=final_2['LEA_LAT1516'])
<matplotlib.collections.PathCollection at 0x128983208>
Note that there are 942 schools with neither school-level or LEA-level coordinates.
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()
SCH_PSENR_HI_M | SCH_PSENR_HI_F | SCH_PSENR_AM_M | SCH_PSENR_AM_F | SCH_PSENR_AS_M | SCH_PSENR_AS_F | SCH_PSENR_HP_M | SCH_PSENR_HP_F | SCH_PSENR_BL_M | SCH_PSENR_BL_F | ... | SCH_JJHOURS | SCH_JJPART_LT15 | SCH_JJPART_15T30 | SCH_JJPART_31T90 | SCH_JJPART_91T180 | SCH_JJPART_OV180 | LAT1516 | LON1516 | LEA_LAT1516 | LEA_LON1516 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
count | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | ... | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 96360.000000 | 95418.000000 | 95418.000000 | 78056.000000 | 78056.000000 |
mean | -3.683562 | -3.949336 | -6.089705 | -6.102491 | -5.846876 | -5.909423 | -6.175467 | -6.182015 | -4.579763 | -4.733105 | ... | -8.912557 | -8.821254 | -8.911914 | -8.896658 | -8.927615 | -8.947395 | 38.134749 | -92.907420 | 38.431997 | -88.955788 |
std | 12.322090 | 11.545621 | 4.482761 | 4.432986 | 5.113281 | 4.902721 | 4.221814 | 4.210343 | 9.595140 | 9.148189 | ... | 1.837969 | 9.053119 | 3.167878 | 3.435528 | 2.177994 | 1.551425 | 5.316225 | 16.024319 | 5.319623 | 13.063124 |
min | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | ... | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | 19.039730 | -176.639853 | 21.308737 | -157.854998 |
25% | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | ... | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | 34.092015 | -100.016383 | 34.935200 | -95.789531 |
50% | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | ... | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | 38.949950 | -89.018601 | 39.804010 | -86.431091 |
75% | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | ... | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | -9.000000 | 41.715340 | -81.103500 | 42.161800 | -80.093271 |
max | 392.000000 | 365.000000 | 68.000000 | 65.000000 | 131.000000 | 104.000000 | 23.000000 | 26.000000 | 335.000000 | 326.000000 | ... | 180.000000 | 2150.000000 | 611.000000 | 515.000000 | 320.000000 | 152.000000 | 71.300337 | -66.989387 | 48.993407 | -66.989387 |
8 rows × 1009 columns
final_2.to_csv('crdc-data-with-lat-long.csv')
Ask three analytic questions and answer each one with a combination of statistics and visualizations. These analytic questions can focus on individuals behaviors or comparisons of the population.
Lastly, make a proposal for a realistic future research project on this dataset that would use some data science techniques you'd like to learn in the bootcamp. Just like your earlier questions, your research proposal should present one or more clear questions. Then you should describe the techniques you would apply in order to arrive at an answer.