Sign in for more options. Each user starts with 1,000 free credits.
gist
Opening your session in 90 seconds...
Please note that we are starting a new machine for you, which takes more time.
If your browser keeps redirecting back and forth from this page in an endless loop, it is possible that you are using an older browser. Please update Google Chrome or use Mozilla Firefox.
In [1]:
#CrossCompute
# import the data from the district maps dataset
# import demographics data
# append the diversity index to the district maps dataset
# create a cloropleth
target_folder = '/tmp'
In [2]:
url = 'https://data.cityofnewyork.us/api/geospatial/r8nu-ymqj?method=export&format=Shapefile'
In [3]:
import geotable
In [4]:
dist_table = geotable.load(url)
In [5]:
#t.draw()
In [6]:
dist_table[:3]
Out[6]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| school_dis | shape_area | shape_leng | geometry_object | geometry_layer | geometry_proj4 | |
|---|---|---|---|---|---|---|
| 0 | 16.0 | 4.676362e+07 | 35848.904605 | POLYGON ((-73.93311862859143 40.69579115384632... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 1 | 32.0 | 5.189850e+07 | 37251.057847 | POLYGON ((-73.91180710069435 40.70343495202662... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 2 | 6.0 | 9.634170e+07 | 70447.849084 | POLYGON ((-73.92640556921116 40.87762147653734... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
In [7]:
dist_table.iloc[0]
Out[7]:
school_dis 16 shape_area 4.67636e+07 shape_leng 35848.9 geometry_object POLYGON ((-73.93311862859143 40.69579115384632... geometry_layer geo_export_9660305b-263b-4410-9560-e071b847c573 geometry_proj4 +proj=longlat +ellps=WGS84 +no_defs Name: 0, dtype: object
In [8]:
url = 'https://data.cityofnewyork.us/resource/98et-3mve.csv'
In [9]:
import pandas as pd
def load(
endpoint_url,
selected_columns=None,
buffer_size=1000,
search_term_by_column=None,
**kw,
):
buffer_url = (f'{endpoint_url}?$limit={buffer_size}')
if selected_columns:
select_string = ','.join(selected_columns)
buffer_url += f'&$select={select_string}'
for column, search_term in (search_term_by_column or {}).items():
buffer_url += f'&$where={column}+like+"%25{search_term}%25"'
print(buffer_url)
tables = []
if endpoint_url.endswith('.json'):
f = pd.read_json
else:
f = pd.read_csv
t = f(buffer_url, **kw)
while len(t):
print(len(tables) * buffer_size + len(t))
tables.append(t)
offset = buffer_size * len(tables)
t = f(buffer_url + f'&$offset={offset}', **kw)
return pd.concat(tables, ignore_index=True, sort=False)
In [10]:
demo_table = load(url, na_values=['No Data'])
https://data.cityofnewyork.us/resource/98et-3mve.csv?$limit=1000 1000 2000 3000 4000 5000 6000 7000 8000 8972
In [ ]:
In [11]:
# len(demo_table)
In [12]:
# demo_table.iloc[0]
In [13]:
# demo_table = pd.read_csv(url, na_values=['No Data'])
In [14]:
demo_table.iloc[0]
Out[14]:
asian_1 9 asian_2 4.7 black_1 72 black_2 37.9 dbn 01M015 economic_need_index NaN english_language_learners_1 19 english_language_learners_2 10 female_1 93 female_2 48.9 grade_1 39 grade_10 0 grade_11 0 grade_12 0 grade_2 21 grade_3 16 grade_4 26 grade_5 23 grade_6 0 grade_7 0 grade_8 0 grade_9 0 grade_k 39 grade_pk_half_day_full_day 26 hispanic_1 104 hispanic_2 54.7 male_1 97 male_2 51.1 multiple_race_categories_not_represented_1 2 multiple_race_categories_not_represented_2 1.1 poverty_1 171 poverty_2 90 school_name P.S. 015 Roberto Clemente students_with_disabilities_1 65 students_with_disabilities_2 34.2 total_enrollment 190 white_1 3 white_2 1.6 year 2013-14 Name: 0, dtype: object
In [15]:
len(demo_table)
Out[15]:
8972
In [16]:
demo_table = demo_table.dropna(subset=['economic_need_index'])
In [17]:
len(demo_table)
Out[17]:
7242
In [18]:
demo_table.dtypes
Out[18]:
asian_1 int64 asian_2 float64 black_1 int64 black_2 float64 dbn object economic_need_index object english_language_learners_1 int64 english_language_learners_2 float64 female_1 int64 female_2 float64 grade_1 int64 grade_10 int64 grade_11 int64 grade_12 int64 grade_2 int64 grade_3 int64 grade_4 int64 grade_5 int64 grade_6 int64 grade_7 int64 grade_8 int64 grade_9 int64 grade_k int64 grade_pk_half_day_full_day int64 hispanic_1 int64 hispanic_2 float64 male_1 int64 male_2 float64 multiple_race_categories_not_represented_1 int64 multiple_race_categories_not_represented_2 float64 poverty_1 int64 poverty_2 float64 school_name object students_with_disabilities_1 int64 students_with_disabilities_2 float64 total_enrollment int64 white_1 int64 white_2 float64 year object dtype: object
In [19]:
demo_table['economic_need_index'] = demo_table['economic_need_index'].str.replace('%', '')
In [20]:
demo_table['economic_need_index'] = demo_table['economic_need_index'].astype('float')
In [21]:
# demo_table.economic_need_index.unique()
In [22]:
len(demo_table)
Out[22]:
7242
In [23]:
# # CALCULATE DIVERSITY INDEX OF EACH SCHOOL & ADD TO DEMODF DATAFRAME
# # demo_table['Diversity Index'] = 1
# def d_index(row):
# o = row['% Multiple Race Categories Not Represented']
# total = 100 - o
# target = total / 4
# abs_distance = (abs(target - row['% Asian'])) + abs((target - row['% Black'])) + abs((target - row['% Hispanic'])) + abs((target - row['% White']))
# if o == 0.0:
# return abs_distance
# if o > 0.0:
# # diversity index
# return abs_distance * (1/o)
# # append diversity index to demodf
# # demo_table['Diversity Index'] = demo_table.apply(d_index, axis=1)
# demo_table['Diversity Index'] = demo_table['economic_need_index']
In [24]:
demo_table['Diversity Index'] = demo_table['economic_need_index']
In [25]:
endpoint_url = 'https://data.cityofnewyork.us/resource/r2nx-nhxe.csv'
In [26]:
# Load schools
school_location_table = load(
endpoint_url,
buffer_size=1000)
school_location_table[:5]
https://data.cityofnewyork.us/resource/r2nx-nhxe.csv?$limit=1000 1000 1823
Out[26]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<p>5 rows × 48 columns</p>
| :@computed_region_92fq_4b7q | :@computed_region_efsh_h5xi | :@computed_region_f5dn_yrer | :@computed_region_sbqj_enih | :@computed_region_yeji_bk3q | admin_district_location_code | administrative_district_name | ats_system_code | beds_number | borough_block_lot | ... | primary_building_code | principal_name | principal_phone_number | principal_title | school_support_team_leader_name | school_support_team_name | state_code | status_descriptions | x_coordinate | y_coordinate | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 50.0 | 11729.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M015 | 310100010015 | 1003740020 | ... | M015 | IRENE SANCHEZ | 212-228-8730 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 990141.0 | 202349.0 |
| 1 | 50.0 | 11724.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M019 | 310100010019 | 1004530034 | ... | M019 | JACQUELINE FLANAGAN | 212-533-5340 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 988547.0 | 205239.0 |
| 2 | 32.0 | 11723.0 | 70.0 | 4.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M020 | 310100010020 | 1003550001 | ... | M020 | SARAH PINTO VIAGRAN | 212-254-9577 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 988044.0 | 202068.0 |
| 3 | 50.0 | 11729.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M034 | 310100010034 | 1003810038 | ... | M034 | ANGELIKI LOUKATOS | 212-228-4433 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 991163.0 | 203782.0 |
| 4 | 50.0 | 11729.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M063 | 310100010063 | 1004310014 | ... | M063 | DARLENE CAMERON | 212-674-3180 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 988071.0 | 203210.0 |
In [27]:
school_location_table.iloc[0]
Out[27]:
:@computed_region_92fq_4b7q 50 :@computed_region_efsh_h5xi 11729 :@computed_region_f5dn_yrer 70 :@computed_region_sbqj_enih 5 :@computed_region_yeji_bk3q 4 admin_district_location_code M801 administrative_district_name COMMUNITY SCHOOL DISTRICT 01 ats_system_code 01M015 beds_number 310100010015 borough_block_lot 1003740020 census_tract 2601 community_district 103 community_school_sup_name PHILLIPS, DANIELLA council_district 2 fax_number 212-477-0931 field_support_center_leader_name CHU, YUET field_support_center_name Field Support Center - Manhattan fiscal_year 2018 geographical_district_code 1 grades_final_text PK,0K,01,02,03,04,05 grades_text PK,0K,01,02,03,04,05,SE highschool_network_location_code NaN highschool_network_name NaN highschool_network_superintendent NaN location_1 POINT (-73.978747 40.722075) location_1_address 333 EAST 4 STREET location_1_city MANHATTAN location_1_state NY location_1_zip 10009 location_category_description Elementary location_code M015 location_name P.S. 015 Roberto Clemente location_type_description General Academic managed_by_name DOE nta MN28 nta_name Lower East Side ... open_date 1904-07-01T00:00:00.000 primary_address_line_1 333 EAST 4 STREET primary_building_code M015 principal_name IRENE SANCHEZ principal_phone_number 212-228-8730 principal_title PRINCIPAL school_support_team_leader_name NaN school_support_team_name School Support Team 3- Manhattan state_code NY status_descriptions Open x_coordinate 990141 y_coordinate 202349 Name: 0, dtype: object
In [28]:
school_location_table.iloc[0]['location_1']
Out[28]:
'POINT (-73.978747 40.722075)'
In [29]:
school_location_table.iloc[0]['ats_system_code']
Out[29]:
'01M015 '
In [30]:
school_location_table['DBN'] = school_location_table['ats_system_code'].str.strip()
In [31]:
school_location_table.iloc[0]['DBN']
Out[31]:
'01M015'
In [32]:
school_location_table = school_location_table.rename(columns={
'location_1': 'WKT',
'location_name': 'School Name',
})
In [33]:
trimmed_school_location_table = school_location_table[[
'DBN',
'WKT',
'School Name',
]]
In [34]:
school_table = pd.merge(
trimmed_school_location_table,
demo_table,
left_on='DBN',
right_on='dbn')
len(school_table)
Out[34]:
7177
In [35]:
school_table = school_table.dropna(subset=['WKT'])
In [36]:
len(school_table)
Out[36]:
7173
In [ ]:
In [37]:
school_table.iloc[0]
Out[37]:
DBN 01M015 WKT POINT (-73.978747 40.722075) School Name P.S. 015 Roberto Clemente asian_1 8 asian_2 4.4 black_1 65 black_2 35.5 dbn 01M015 economic_need_index 93.5 english_language_learners_1 17 english_language_learners_2 9.3 female_1 84 female_2 45.9 grade_1 47 grade_10 0 grade_11 0 grade_12 0 grade_2 31 grade_3 19 grade_4 17 grade_5 24 grade_6 0 grade_7 0 grade_8 0 grade_9 0 grade_k 27 grade_pk_half_day_full_day 18 hispanic_1 107 hispanic_2 58.5 male_1 99 male_2 54.1 multiple_race_categories_not_represented_1 1 multiple_race_categories_not_represented_2 0.5 poverty_1 169 poverty_2 92.3 school_name P.S. 015 Roberto Clemente students_with_disabilities_1 64 students_with_disabilities_2 35 total_enrollment 183 white_1 2 white_2 1.1 year 2014-15 Diversity Index 93.5 Name: 0, dtype: object
In [38]:
dist_table.iloc[0]
Out[38]:
school_dis 16 shape_area 4.67636e+07 shape_leng 35848.9 geometry_object POLYGON ((-73.93311862859143 40.69579115384632... geometry_layer geo_export_9660305b-263b-4410-9560-e071b847c573 geometry_proj4 +proj=longlat +ellps=WGS84 +no_defs Name: 0, dtype: object
In [39]:
geometry_wkt = school_table.iloc[0]['WKT']
In [40]:
geometry_wkt
Out[40]:
'POINT (-73.978747 40.722075)'
In [41]:
from shapely import wkt
In [42]:
g = wkt.loads(geometry_wkt)
In [43]:
g
Out[43]:
<shapely.geometry.point.Point at 0x7f8b6554cba8>
In [44]:
district_polygons = dist_table['geometry_object']
In [45]:
flags = [x.contains(g) for x in district_polygons]
flags
Out[45]:
[False, False, False, False, False, False, False, False, False, False, False, False, False, False, False, False, False, False, False, True, False, False, False, False, False, False, False, False, False, False, False, False, False]
In [46]:
import numpy as np
In [47]:
index = np.argmax(flags)
In [48]:
dist_table.iloc[index]
Out[48]:
school_dis 1 shape_area 3.51607e+07 shape_leng 28641.3 geometry_object POLYGON ((-73.97177410965313 40.72582128133706... geometry_layer geo_export_9660305b-263b-4410-9560-e071b847c573 geometry_proj4 +proj=longlat +ellps=WGS84 +no_defs Name: 19, dtype: object
In [49]:
index
Out[49]:
19
In [50]:
len(flags)
Out[50]:
33
In [51]:
int(dist_table.iloc[index]['school_dis'])
Out[51]:
1
In [52]:
len(school_table)
Out[52]:
7173
In [53]:
from geotable import ColorfulGeometryCollection, GeoTable
In [54]:
dist_table
Out[54]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| school_dis | shape_area | shape_leng | geometry_object | geometry_layer | geometry_proj4 | |
|---|---|---|---|---|---|---|
| 0 | 16.0 | 4.676362e+07 | 35848.904605 | POLYGON ((-73.93311862859143 40.69579115384632... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 1 | 32.0 | 5.189850e+07 | 37251.057847 | POLYGON ((-73.91180710069435 40.70343495202662... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 2 | 6.0 | 9.634170e+07 | 70447.849084 | POLYGON ((-73.92640556921116 40.87762147653734... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 3 | 31.0 | 1.604472e+09 | 434471.412859 | (POLYGON ((-74.05050806403247 40.5664220341608... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 4 | 8.0 | 2.588266e+08 | 223080.142631 | (POLYGON ((-73.83979488562171 40.8356192074928... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 5 | 7.0 | 9.226247e+07 | 65294.452403 | (POLYGON ((-73.89680883223774 40.7958084451597... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 6 | 23.0 | 4.740069e+07 | 40317.452033 | (POLYGON ((-73.92044366203014 40.6656262871675... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 7 | 29.0 | 4.201981e+08 | 135035.241651 | POLYGON ((-73.73816144093141 40.72895809117297... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 8 | 26.0 | 4.247909e+08 | 125677.678898 | POLYGON ((-73.74344992332192 40.77824115291502... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 9 | 15.0 | 1.961534e+08 | 153439.165680 | POLYGON ((-73.98633135042395 40.69105051012824... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 10 | 17.0 | 1.284414e+08 | 68341.398899 | POLYGON ((-73.92044366203014 40.6656262871675,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 11 | 19.0 | 2.034175e+08 | 184183.167312 | (POLYGON ((-73.846736514711 40.60485301485166,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 12 | 27.0 | 7.955971e+08 | 589135.502728 | (POLYGON ((-73.82784008953526 40.5887858248046... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 13 | 13.0 | 1.048706e+08 | 86635.210559 | POLYGON ((-73.97906084911834 40.70594602894087... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 14 | 18.0 | 1.751488e+08 | 121184.158477 | (POLYGON ((-73.86706149472118 40.5820879767934... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 15 | 20.0 | 2.426965e+08 | 94309.778946 | POLYGON ((-74.02552971543656 40.65147855069281... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 16 | 3.0 | 1.134889e+08 | 52072.051321 | POLYGON ((-73.95671863064405 40.78660079332199... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 17 | 5.0 | 5.251977e+07 | 44469.588221 | POLYGON ((-73.93515659239551 40.83268240623763... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 18 | 9.0 | 8.341539e+07 | 46648.958586 | POLYGON ((-73.9212971968614 40.85428933985649,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 19 | 1.0 | 3.516075e+07 | 28641.276279 | POLYGON ((-73.97177410965313 40.72582128133706... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 20 | 14.0 | 1.503102e+08 | 95792.082090 | POLYGON ((-73.95439555417087 40.73911477252251... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 21 | 2.0 | 2.804677e+08 | 214498.703163 | (POLYGON ((-74.0438776163991 40.69018767537123... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 22 | 4.0 | 5.262043e+07 | 52061.828459 | (POLYGON ((-73.92133752419399 40.8008521064970... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 23 | 10.0 | 2.825415e+08 | 94957.570537 | POLYGON ((-73.86789798628736 40.90294017690526... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 24 | 12.0 | 6.907182e+07 | 48527.595776 | POLYGON ((-73.88284445574813 40.84781722645163... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 25 | 25.0 | 4.436285e+08 | 175827.007127 | POLYGON ((-73.82049919995312 40.80101146781899... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 26 | 28.0 | 2.475679e+08 | 114694.912786 | POLYGON ((-73.84485477879177 40.7357514698091,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 27 | 24.0 | 3.949782e+08 | 127343.703736 | (POLYGON ((-73.90641585511733 40.7398683641967... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 28 | 30.0 | 3.181290e+08 | 150392.978241 | POLYGON ((-73.90647314610101 40.79018117520807... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 29 | 21.0 | 2.101971e+08 | 123858.087345 | POLYGON ((-73.96184657346174 40.62757081710622... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 30 | 22.0 | 3.855533e+08 | 271718.504936 | (POLYGON ((-73.91990064270161 40.5996005215871... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 31 | 11.0 | 3.926651e+08 | 305305.863567 | (POLYGON ((-73.78833349834532 40.8346671297593... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
| 32 | 10.0 | 3.282963e+06 | 7883.372664 | (POLYGON ((-73.9089323517538 40.8721573479701,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs |
In [55]:
dist_table.geometry_object[0].wkt
Out[55]:
'POLYGON ((-73.93311862859143 40.69579115384632, -73.93236605675587 40.69535837729649, -73.9319289006206 40.6951146575314, -73.93177404555487 40.69502821484371, -73.93147184545109 40.69486090242515, -73.93121199886629 40.69471276978224, -73.93114516482393 40.69467397486358, -73.930511465969 40.69431652304853, -73.92970192402746 40.69386339761171, -73.92882897891457 40.69335047407161, -73.92882834168937 40.6933500992537, -73.92864268981751 40.69324101255102, -73.92850659671787 40.69316572635911, -73.92804330687132 40.69290417236994, -73.92781120671985 40.6927742086535, -73.92750685270927 40.69260381145623, -73.92707069461525 40.69234622359373, -73.92644992133326 40.69200176882866, -73.92631612531525 40.69192845396802, -73.92556064122292 40.691490840003, -73.92534903613239 40.69136694183798, -73.92439183303664 40.69083174877439, -73.92429213268012 40.69077335685399, -73.92364379835564 40.6903988375817, -73.92316966710699 40.69012340599072, -73.92280702359515 40.68992392534457, -73.92207133797079 40.68948984079818, -73.92130587808545 40.68907164359784, -73.92121369950202 40.68901947711544, -73.92096103866248 40.68887205183747, -73.92055013077213 40.68864092211603, -73.91980961874593 40.6882112414115, -73.91906109224908 40.68778436875932, -73.91878323293736 40.68762608844376, -73.91829345832642 40.68735666781527, -73.91804606958546 40.68721324726834, -73.91768113918805 40.68700409429878, -73.91755226067554 40.68693022304933, -73.91679832884886 40.68649719987184, -73.91679482480477 40.68647585566188, -73.91677745031008 40.68637001456988, -73.9167269298151 40.6861315560806, -73.91739374561757 40.68605758524438, -73.91784937410198 40.68623316741141, -73.91800616261635 40.6858220326253, -73.91770109452972 40.68550035866507, -73.9178810736956 40.68509394091895, -73.91755763291395 40.68476719192117, -73.91776142009367 40.68432465483016, -73.91741294336087 40.68403405152289, -73.91758679937763 40.68391130921148, -73.91754176551858 40.68365836480015, -73.91749199778137 40.68340840097156, -73.91726632656643 40.68330322455323, -73.91748255687848 40.68289735539763, -73.91712138528143 40.68256847168654, -73.91732345753172 40.68244571271426, -73.9172785838021 40.68218148175983, -73.91722749280017 40.68193500491278, -73.9169763289098 40.68183415891587, -73.91717325370381 40.68163401677827, -73.91713117516936 40.68143498166116, -73.91708045205013 40.68119847288923, -73.91683011483136 40.68110098072739, -73.91676207442167 40.68075559994087, -73.91668638370811 40.68037137878948, -73.9165399872181 40.67963749735295, -73.91639300075401 40.67890584888099, -73.91630119795475 40.67857711243357, -73.91635492184039 40.6778235366874, -73.91645747530021 40.67672602823275, -73.91646486032333 40.67664704004651, -73.91647371343048 40.67655700413084, -73.91654436298559 40.67583843461703, -73.91661697848021 40.67505994757939, -73.91668911898741 40.67428057620312, -73.91677231309316 40.67339069306274, -73.91721449620965 40.67385599371995, -73.91907827608151 40.67397531376398, -73.91954092546408 40.67354187795393, -73.9200025573228 40.67398128283877, -73.92158580075649 40.67405555683759, -73.92213153379178 40.67368388950964, -73.92298622413946 40.67373038435373, -73.92309797315002 40.67373646296576, -73.92324436873267 40.67374442565551, -73.92336008453272 40.67375071908456, -73.92508152301917 40.67384433817344, -73.92550081445938 40.67361688849451, -73.92743998148244 40.67370890462769, -73.92784085188026 40.67399598368088, -73.92788485920073 40.67355077838425, -73.92792887672408 40.67310556484484, -73.9282445167355 40.67293933694761, -73.9282717980865 40.67252471210702, -73.92801317657924 40.67220920817189, -73.92826528535652 40.67222507724347, -73.92833507240064 40.67222889379835, -73.92843446083054 40.67223432828423, -73.92851854078968 40.67223892474855, -73.92862604211787 40.67224480269724, -73.92870956326151 40.67224936969293, -73.92882833736527 40.67225586323351, -73.92891232629145 40.67226045499795, -73.92939948737151 40.67228708901998, -73.9307932883315 40.67236495699643, -73.93355357161703 40.67251450202632, -73.9363431994143 40.67269957696197, -73.93667540626117 40.6727143953574, -73.93682717835949 40.67271928165021, -73.93708255238936 40.67272750062104, -73.93754574483314 40.672742485227, -73.93753568983917 40.67332211170566, -73.93753114689092 40.67358374466557, -73.93752485721895 40.67394597310864, -73.93751992857378 40.67422980176767, -73.93806258654983 40.67447201037736, -73.93832822275243 40.67458578836195, -73.93889518673745 40.67459873386304, -73.93877769674783 40.67472089131839, -73.93873696341934 40.67538905896871, -73.93884149817444 40.67549215171548, -73.93843297207545 40.67556514632768, -73.93852793096612 40.67617239034053, -73.93876783935517 40.67627240938755, -73.93853686439037 40.67634626398586, -73.93843334915296 40.67693817086859, -73.9386949143591 40.67705082000203, -73.93851537899995 40.67741865687472, -73.93863289914781 40.67776992010254, -73.93862492563589 40.67786256958296, -73.9386168305744 40.67794381002248, -73.93850818855235 40.67903432002991, -73.93843613326652 40.67978585170399, -73.93739763665229 40.67972990017674, -73.93764243456839 40.68096300863983, -73.93778801579033 40.68169433234844, -73.9348625715524 40.68203056869989, -73.93500821720075 40.68276376850579, -73.93515472893986 40.68349718465807, -73.93807695931454 40.68316090298936, -73.9382232586039 40.68389329800515, -73.93836816506894 40.68462560189113, -73.93851929553905 40.68535451524578, -73.93865905188188 40.68609038907362, -73.93880456947376 40.68682474574926, -73.93895074350998 40.68755641035276, -73.93938561082579 40.68787113499809, -73.9413859614605 40.6876682688049, -73.94178527584324 40.68722837212115, -73.9423520278467 40.68759150082202, -73.94428391577316 40.68738505531383, -73.94462127846789 40.68690436166384, -73.94471297541574 40.68737334166219, -73.94472684342399 40.68744426893098, -73.94476925905255 40.6876366000215, -73.94489497385723 40.68828685373113, -73.94491170954201 40.68837022260124, -73.94505645129829 40.68910234632374, -73.94519881242742 40.68983988030254, -73.94534976297854 40.69056729694257, -73.94549553209639 40.69130013188384, -73.94564166805849 40.69203345190423, -73.94578713653169 40.69276523278351, -73.94593170750511 40.69349761558435, -73.94607851790597 40.69423024360605, -73.94324249086142 40.69455748523078, -73.94338802084518 40.69528898951384, -73.94352527471476 40.69603085523811, -73.94335303762136 40.69605059879613, -73.94321604415771 40.69606631370468, -73.94279300209647 40.69611479586432, -73.94242149063366 40.69615738067107, -73.9423368032754 40.69616708769403, -73.94226167920422 40.696175698798, -73.94217061244066 40.69618613693807, -73.94208643350876 40.69619578590027, -73.94198436724183 40.69620748536691, -73.94152445883948 40.6962601986313, -73.94070625153131 40.69635305387921, -73.94040726721202 40.69638987090211, -73.94020994337993 40.69641107787343, -73.94003822508267 40.69643083533578, -73.93976769964368 40.69646329104629, -73.93948446000253 40.69649454647407, -73.93917199789162 40.69653049512544, -73.93884866773332 40.69656769159932, -73.93867948785814 40.69658715370254, -73.93779110726068 40.69668934223279, -73.93763027449141 40.69595086258837, -73.93755700214285 40.69558451152044, -73.93748376644189 40.6952184321377, -73.9370544081801 40.69553123358047, -73.9348298043286 40.69575489184654, -73.9345601174396 40.69555483174558, -73.93463155014204 40.69592163383321, -73.93470512606679 40.69628830975414, -73.9347969968416 40.69673659235447, -73.93468892593663 40.69667495388458, -73.9346067517839 40.69662808583959, -73.93457999376531 40.69661282447046, -73.93448784825009 40.69656026847871, -73.93413397294013 40.69635843140355, -73.93382817155928 40.69618427730787, -73.93311862859143 40.69579115384632))'
In [56]:
school_table.WKT[0]
Out[56]:
'POINT (-73.978747 40.722075)'
In [57]:
len(dist_table.school_dis.unique())
Out[57]:
32
In [58]:
# aggregate districts
from shapely import wkt
import numpy as np
district_polygons = dist_table['geometry_object']
def get_district(geometry_wkt):
g = wkt.loads(geometry_wkt)
#flags = [x.contains(g) for x in district_polygons]
flags = [x.intersects(g) for x in district_polygons]
index = np.argmax(flags)
district = int(dist_table.iloc[index]['school_dis'])
return district
school_table['district'] = school_table['WKT'].apply(get_district)
In [59]:
len(school_table.district.unique())
Out[59]:
32
In [60]:
len(dist_table)
Out[60]:
33
In [61]:
mean_table = school_table.groupby('district').mean()
In [62]:
mean_table = mean_table.reset_index()
In [63]:
mean_table = mean_table[[
'district',
'economic_need_index',
]]
In [64]:
t = pd.merge(dist_table, mean_table, left_on='school_dis', right_on='district')
In [65]:
map_table = t.copy()
In [66]:
map_table['FillBlues'] = map_table.economic_need_index
In [67]:
school_map_table = school_table.copy()
In [68]:
school_map_table['geometry_object'] = school_map_table.WKT.apply(wkt.loads)
In [69]:
district_map_table = t.copy()
district_map_table['FillBlues'] = district_map_table['economic_need_index']
school_map_table = school_table.copy()
#school_map_table['FillBlues'] = district_map_table['economic_need_index'].max()
map_table = pd.concat([
#district_map_table,
school_map_table,
], sort=False)
In [70]:
url = 'https://data.cityofnewyork.us/api/views/vh2h-md7a/rows.csv'
school_graduation_table = pd.read_csv(url)
len(school_graduation_table)
Out[70]:
25096
In [71]:
school_graduation_table.iloc[0]
Out[71]:
Demographic Total Cohort DBN 01M292 School Name HENRY STREET SCHOOL FOR INTERNATIONAL Cohort 2003 Total Cohort 5 Total Grads - n s Total Grads - % of cohort NaN Total Regents - n s Total Regents - % of cohort NaN Total Regents - % of grads NaN Advanced Regents - n s Advanced Regents - % of cohort NaN Advanced Regents - % of grads NaN Regents w/o Advanced - n s Regents w/o Advanced - % of cohort NaN Regents w/o Advanced - % of grads NaN Local - n s Local - % of cohort NaN Local - % of grads NaN Still Enrolled - n s Still Enrolled - % of cohort NaN Dropped Out - n s Dropped Out - % of cohort NaN Name: 0, dtype: object
In [72]:
school_graduation_table.groupby('DBN').mean()[:5]
Out[72]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| Total Cohort | Total Grads - % of cohort | Total Regents - % of cohort | Total Regents - % of grads | Advanced Regents - % of cohort | Advanced Regents - % of grads | Regents w/o Advanced - % of cohort | Regents w/o Advanced - % of grads | Local - % of cohort | Local - % of grads | Still Enrolled - % of cohort | Dropped Out - % of cohort | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| DBN | ||||||||||||
| 01M292 | 25.870370 | 61.755556 | 42.737037 | 70.448148 | 0.000000 | 0.000000 | 42.737037 | 70.448148 | 19.000000 | 29.551852 | 20.337037 | 12.100000 |
| 01M448 | 44.051948 | 58.637736 | 36.352830 | 61.415094 | 8.462264 | 13.481132 | 27.884906 | 47.928302 | 22.281132 | 38.588679 | 28.264151 | 9.958491 |
| 01M450 | 36.565789 | 70.462500 | 66.075000 | 93.714583 | 0.000000 | 0.000000 | 66.075000 | 93.714583 | 4.383333 | 6.285417 | 18.210417 | 9.985417 |
| 01M509 | 34.797297 | 49.902381 | 31.861905 | 61.852381 | 10.540476 | 19.133333 | 21.321429 | 42.711905 | 18.028571 | 38.150000 | 30.502381 | 14.266667 |
| 01M515 | 87.876712 | 49.151064 | 36.508511 | 74.361702 | 21.876596 | 44.129787 | 14.631915 | 30.225532 | 12.725532 | 25.787234 | 34.219149 | 16.370213 |
In [73]:
school_graduation_table = school_graduation_table[['DBN', 'School Name', 'Total Grads - % of cohort']].copy()
len(school_graduation_table)
Out[73]:
25096
In [74]:
school_graduation_table = school_graduation_table.dropna()
sum(school_graduation_table['Total Grads - % of cohort'] == 's')
/home/user/.virtualenvs/crosscompute/lib/python3.6/site-packages/pandas/core/ops.py:1649: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison result = method(y)
Out[74]:
0
In [75]:
# Define which values mean null
school_graduation_table = pd.read_csv(url, na_values=['s'])
school_graduation_table = school_graduation_table[['DBN', 'School Name', 'Total Grads - % of cohort']].copy()
school_graduation_table = school_graduation_table.dropna()
school_graduation_table = school_graduation_table.rename(
columns={'Total Grads - % of cohort': 'Graduation Rate'})
len(school_graduation_table)
Out[75]:
16704
In [76]:
school_graduation_table.dtypes
Out[76]:
DBN object School Name object Graduation Rate float64 dtype: object
In [77]:
school_graduation_table
Out[77]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<p>16704 rows × 3 columns</p>
| DBN | School Name | Graduation Rate | |
|---|---|---|---|
| 1 | 01M292 | HENRY STREET SCHOOL FOR INTERNATIONAL | 67.3 |
| 2 | 01M292 | HENRY STREET SCHOOL FOR INTERNATIONAL | 67.2 |
| 3 | 01M292 | HENRY STREET SCHOOL FOR INTERNATIONAL | 55.1 |
| 4 | 01M292 | HENRY STREET SCHOOL FOR INTERNATIONAL | 56.4 |
| 5 | 01M448 | UNIVERSITY NEIGHBORHOOD HIGH SCHOOL | 71.9 |
| 6 | 01M448 | UNIVERSITY NEIGHBORHOOD HIGH SCHOOL | 63.5 |
| 7 | 01M448 | UNIVERSITY NEIGHBORHOOD HIGH SCHOOL | 77.0 |
| 8 | 01M448 | UNIVERSITY NEIGHBORHOOD HIGH SCHOOL | 67.0 |
| 9 | 01M448 | UNIVERSITY NEIGHBORHOOD HIGH SCHOOL | 52.9 |
| 10 | 01M448 | UNIVERSITY NEIGHBORHOOD HIGH SCHOOL | 42.7 |
| 11 | 01M448 | UNIVERSITY NEIGHBORHOOD HIGH SCHOOL | 48.4 |
| 12 | 01M450 | EAST SIDE COMMUNITY SCHOOL | 67.2 |
| 13 | 01M450 | EAST SIDE COMMUNITY SCHOOL | 62.5 |
| 14 | 01M450 | EAST SIDE COMMUNITY SCHOOL | 62.0 |
| 15 | 01M450 | EAST SIDE COMMUNITY SCHOOL | 72.5 |
| 16 | 01M450 | EAST SIDE COMMUNITY SCHOOL | 71.8 |
| 17 | 01M450 | EAST SIDE COMMUNITY SCHOOL | 77.8 |
| 18 | 01M450 | EAST SIDE COMMUNITY SCHOOL | 78.9 |
| 19 | 01M509 | MARTA VALLE HIGH SCHOOL | 49.3 |
| 20 | 01M509 | MARTA VALLE HIGH SCHOOL | 40.0 |
| 21 | 01M509 | MARTA VALLE HIGH SCHOOL | 44.3 |
| 22 | 01M509 | MARTA VALLE HIGH SCHOOL | 44.6 |
| 23 | 01M509 | MARTA VALLE HIGH SCHOOL | 55.7 |
| 24 | 01M509 | MARTA VALLE HIGH SCHOOL | 56.0 |
| 25 | 01M509 | MARTA VALLE HIGH SCHOOL | 59.5 |
| 26 | 01M515 | LOWER EAST SIDE PREPARATORY HIGH SCHO | 53.0 |
| 27 | 01M515 | LOWER EAST SIDE PREPARATORY HIGH SCHO | 44.8 |
| 28 | 01M515 | LOWER EAST SIDE PREPARATORY HIGH SCHO | 42.0 |
| 29 | 01M515 | LOWER EAST SIDE PREPARATORY HIGH SCHO | 48.5 |
| 30 | 01M515 | LOWER EAST SIDE PREPARATORY HIGH SCHO | 45.5 |
| ... | ... | ... | ... |
| 25060 | 32K545 | EBC HIGH SCHOOL FOR PUBLIC SERVICE?BU | 58.6 |
| 25061 | 32K545 | EBC HIGH SCHOOL FOR PUBLIC SERVICE?BU | 50.0 |
| 25062 | 32K545 | EBC HIGH SCHOOL FOR PUBLIC SERVICE?BU | 60.3 |
| 25063 | 32K545 | EBC HIGH SCHOOL FOR PUBLIC SERVICE?BU | 56.2 |
| 25064 | 32K545 | EBC HIGH SCHOOL FOR PUBLIC SERVICE?BU | 63.0 |
| 25066 | 32K549 | BUSHWICK SCHOOL FOR SOCIAL JUSTICE | 57.1 |
| 25067 | 32K549 | BUSHWICK SCHOOL FOR SOCIAL JUSTICE | 70.4 |
| 25068 | 32K549 | BUSHWICK SCHOOL FOR SOCIAL JUSTICE | 69.4 |
| 25069 | 32K549 | BUSHWICK SCHOOL FOR SOCIAL JUSTICE | 71.1 |
| 25070 | 32K549 | BUSHWICK SCHOOL FOR SOCIAL JUSTICE | 71.1 |
| 25072 | 32K552 | ACADEMY OF URBAN PLANNING | 49.2 |
| 25073 | 32K552 | ACADEMY OF URBAN PLANNING | 53.2 |
| 25074 | 32K552 | ACADEMY OF URBAN PLANNING | 52.8 |
| 25075 | 32K552 | ACADEMY OF URBAN PLANNING | 42.0 |
| 25076 | 32K552 | ACADEMY OF URBAN PLANNING | 54.0 |
| 25080 | 32K554 | ALL CITY LEADERSHIP SECONDARY SCHOOL | 69.6 |
| 25081 | 32K554 | ALL CITY LEADERSHIP SECONDARY SCHOOL | 87.5 |
| 25082 | 32K554 | ALL CITY LEADERSHIP SECONDARY SCHOOL | 91.7 |
| 25084 | 32K556 | BUSHWICK LEADERS HIGH SCHOOL FOR ACAD | 63.8 |
| 25085 | 32K556 | BUSHWICK LEADERS HIGH SCHOOL FOR ACAD | 64.7 |
| 25086 | 32K556 | BUSHWICK LEADERS HIGH SCHOOL FOR ACAD | 62.5 |
| 25087 | 32K556 | BUSHWICK LEADERS HIGH SCHOOL FOR ACAD | 49.0 |
| 25088 | 32K556 | BUSHWICK LEADERS HIGH SCHOOL FOR ACAD | 51.0 |
| 25089 | 32K564 | BUSHWICK COMMUNITY HIGH SCHOOL | 2.9 |
| 25090 | 32K564 | BUSHWICK COMMUNITY HIGH SCHOOL | 5.5 |
| 25091 | 32K564 | BUSHWICK COMMUNITY HIGH SCHOOL | 1.5 |
| 25092 | 32K564 | BUSHWICK COMMUNITY HIGH SCHOOL | 3.1 |
| 25093 | 32K564 | BUSHWICK COMMUNITY HIGH SCHOOL | 3.8 |
| 25094 | 32K564 | BUSHWICK COMMUNITY HIGH SCHOOL | 7.0 |
| 25095 | 32K564 | BUSHWICK COMMUNITY HIGH SCHOOL | 7.0 |
In [78]:
school_graduation_table = school_graduation_table.groupby('DBN').mean()
school_graduation_table[:5]
Out[78]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| Graduation Rate | |
|---|---|
| DBN | |
| 01M292 | 61.755556 |
| 01M448 | 58.637736 |
| 01M450 | 70.462500 |
| 01M509 | 49.902381 |
| 01M515 | 49.151064 |
In [79]:
endpoint_url = 'https://data.cityofnewyork.us/resource/r2nx-nhxe.csv'
In [80]:
# Load schools
school_location_table = load(
endpoint_url,
buffer_size=1000)
school_location_table[:5]
https://data.cityofnewyork.us/resource/r2nx-nhxe.csv?$limit=1000 1000 1823
Out[80]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<p>5 rows × 48 columns</p>
| :@computed_region_92fq_4b7q | :@computed_region_efsh_h5xi | :@computed_region_f5dn_yrer | :@computed_region_sbqj_enih | :@computed_region_yeji_bk3q | admin_district_location_code | administrative_district_name | ats_system_code | beds_number | borough_block_lot | ... | primary_building_code | principal_name | principal_phone_number | principal_title | school_support_team_leader_name | school_support_team_name | state_code | status_descriptions | x_coordinate | y_coordinate | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 50.0 | 11729.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M015 | 310100010015 | 1003740020 | ... | M015 | IRENE SANCHEZ | 212-228-8730 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 990141.0 | 202349.0 |
| 1 | 50.0 | 11724.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M019 | 310100010019 | 1004530034 | ... | M019 | JACQUELINE FLANAGAN | 212-533-5340 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 988547.0 | 205239.0 |
| 2 | 32.0 | 11723.0 | 70.0 | 4.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M020 | 310100010020 | 1003550001 | ... | M020 | SARAH PINTO VIAGRAN | 212-254-9577 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 988044.0 | 202068.0 |
| 3 | 50.0 | 11729.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M034 | 310100010034 | 1003810038 | ... | M034 | ANGELIKI LOUKATOS | 212-228-4433 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 991163.0 | 203782.0 |
| 4 | 50.0 | 11729.0 | 70.0 | 5.0 | 4.0 | M801 | COMMUNITY SCHOOL DISTRICT 01 | 01M063 | 310100010063 | 1004310014 | ... | M063 | DARLENE CAMERON | 212-674-3180 | PRINCIPAL | NaN | School Support Team 3- Manhattan | NY | Open | 988071.0 | 203210.0 |
In [81]:
school_location_table.iloc[0]
Out[81]:
:@computed_region_92fq_4b7q 50 :@computed_region_efsh_h5xi 11729 :@computed_region_f5dn_yrer 70 :@computed_region_sbqj_enih 5 :@computed_region_yeji_bk3q 4 admin_district_location_code M801 administrative_district_name COMMUNITY SCHOOL DISTRICT 01 ats_system_code 01M015 beds_number 310100010015 borough_block_lot 1003740020 census_tract 2601 community_district 103 community_school_sup_name PHILLIPS, DANIELLA council_district 2 fax_number 212-477-0931 field_support_center_leader_name CHU, YUET field_support_center_name Field Support Center - Manhattan fiscal_year 2018 geographical_district_code 1 grades_final_text PK,0K,01,02,03,04,05 grades_text PK,0K,01,02,03,04,05,SE highschool_network_location_code NaN highschool_network_name NaN highschool_network_superintendent NaN location_1 POINT (-73.978747 40.722075) location_1_address 333 EAST 4 STREET location_1_city MANHATTAN location_1_state NY location_1_zip 10009 location_category_description Elementary location_code M015 location_name P.S. 015 Roberto Clemente location_type_description General Academic managed_by_name DOE nta MN28 nta_name Lower East Side ... open_date 1904-07-01T00:00:00.000 primary_address_line_1 333 EAST 4 STREET primary_building_code M015 principal_name IRENE SANCHEZ principal_phone_number 212-228-8730 principal_title PRINCIPAL school_support_team_leader_name NaN school_support_team_name School Support Team 3- Manhattan state_code NY status_descriptions Open x_coordinate 990141 y_coordinate 202349 Name: 0, dtype: object
In [82]:
school_location_table.iloc[0]['location_1']
Out[82]:
'POINT (-73.978747 40.722075)'
In [83]:
school_location_table.iloc[0]['ats_system_code']
Out[83]:
'01M015 '
In [84]:
school_location_table['DBN'] = school_location_table['ats_system_code'].str.strip()
In [85]:
school_location_table.iloc[0]['DBN']
Out[85]:
'01M015'
In [86]:
school_location_table = school_location_table.rename(columns={
'location_1': 'WKT',
'location_name': 'School Name',
})
In [87]:
trimmed_school_location_table = school_location_table[[
'DBN',
'WKT',
'School Name',
]]
In [88]:
merged_school_table = pd.merge(
school_table,
school_graduation_table,
left_on='DBN',
right_on='DBN')
len(school_table)
Out[88]:
7173
In [89]:
merged_school_table.iloc[0]
Out[89]:
DBN 01M292 WKT POINT (-73.986051 40.713362) School Name Orchard Collegiate Academy asian_1 32 asian_2 12.5 black_1 66 black_2 25.9 dbn 01M292 economic_need_index 83.9 english_language_learners_1 30 english_language_learners_2 11.8 female_1 96 female_2 37.6 grade_1 0 grade_10 52 grade_11 36 grade_12 58 grade_2 0 grade_3 0 grade_4 0 grade_5 0 grade_6 17 grade_7 14 grade_8 19 grade_9 59 grade_k 0 grade_pk_half_day_full_day 0 hispanic_1 142 hispanic_2 55.7 male_1 159 male_2 62.4 multiple_race_categories_not_represented_1 6 multiple_race_categories_not_represented_2 2.4 poverty_1 227 poverty_2 89 school_name Orchard Collegiate Academy students_with_disabilities_1 83 students_with_disabilities_2 32.5 total_enrollment 255 white_1 9 white_2 3.5 year 2014-15 Diversity Index 83.9 district 1 Graduation Rate 61.7556 Name: 0, dtype: object
In [90]:
sorted_school_table = merged_school_table.sort_values('Graduation Rate', ascending=False)
In [91]:
sorted_school_table[:3]
Out[91]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<p>3 rows × 45 columns</p>
| DBN | WKT | School Name | asian_1 | asian_2 | black_1 | black_2 | dbn | economic_need_index | english_language_learners_1 | ... | school_name | students_with_disabilities_1 | students_with_disabilities_2 | total_enrollment | white_1 | white_2 | year | Diversity Index | district | Graduation Rate | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1085 | 25Q525 | POINT (-73.821532 40.737038) | Townsend Harris High School | 663 | 58.5 | 64 | 5.6 | 25Q525 | 28.2 | 0 | ... | Townsend Harris High School | 19 | 1.7 | 1133 | 247 | 21.8 | 2015-16 | 28.2 | 25 | 99.552632 |
| 1086 | 25Q525 | POINT (-73.821532 40.737038) | Townsend Harris High School | 644 | 58.0 | 60 | 5.4 | 25Q525 | 26.1 | 0 | ... | Townsend Harris High School | 29 | 2.6 | 1110 | 234 | 21.1 | 2016-17 | 26.1 | 25 | 99.552632 |
| 1087 | 25Q525 | POINT (-73.821532 40.737038) | Townsend Harris High School | 662 | 58.0 | 59 | 5.2 | 25Q525 | 38.5 | 0 | ... | Townsend Harris High School | 40 | 3.5 | 1141 | 231 | 20.2 | 2017-18 | 38.5 | 25 | 99.552632 |
In [92]:
sorted_school_table[-3:]
Out[92]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<p>3 rows × 45 columns</p>
| DBN | WKT | School Name | asian_1 | asian_2 | black_1 | black_2 | dbn | economic_need_index | english_language_learners_1 | ... | school_name | students_with_disabilities_1 | students_with_disabilities_2 | total_enrollment | white_1 | white_2 | year | Diversity Index | district | Graduation Rate | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 728 | 13K616 | POINT (-73.95818 40.692015) | Brooklyn High School for Leadership and Community | 2 | 0.9 | 155 | 70.8 | 13K616 | 75.1 | 8 | ... | Brooklyn High School for Leadership and Community | 43 | 19.6 | 219 | 3 | 1.4 | 2014-15 | 75.1 | 13 | 5.322222 |
| 731 | 13K616 | POINT (-73.95818 40.692015) | Brooklyn High School for Leadership and Community | 3 | 1.5 | 140 | 68.3 | 13K616 | 84.5 | 12 | ... | Brooklyn High School for Leadership and Community | 42 | 20.5 | 205 | 2 | 1.0 | 2017-18 | 84.5 | 13 | 5.322222 |
| 729 | 13K616 | POINT (-73.95818 40.692015) | Brooklyn High School for Leadership and Community | 1 | 0.4 | 161 | 70.0 | 13K616 | 71.6 | 11 | ... | Brooklyn High School for Leadership and Community | 37 | 16.1 | 230 | 4 | 1.7 | 2015-16 | 71.6 | 13 | 5.322222 |
In [ ]:
In [93]:
school_table.iloc[0]
Out[93]:
DBN 01M015 WKT POINT (-73.978747 40.722075) School Name P.S. 015 Roberto Clemente asian_1 8 asian_2 4.4 black_1 65 black_2 35.5 dbn 01M015 economic_need_index 93.5 english_language_learners_1 17 english_language_learners_2 9.3 female_1 84 female_2 45.9 grade_1 47 grade_10 0 grade_11 0 grade_12 0 grade_2 31 grade_3 19 grade_4 17 grade_5 24 grade_6 0 grade_7 0 grade_8 0 grade_9 0 grade_k 27 grade_pk_half_day_full_day 18 hispanic_1 107 hispanic_2 58.5 male_1 99 male_2 54.1 multiple_race_categories_not_represented_1 1 multiple_race_categories_not_represented_2 0.5 poverty_1 169 poverty_2 92.3 school_name P.S. 015 Roberto Clemente students_with_disabilities_1 64 students_with_disabilities_2 35 total_enrollment 183 white_1 2 white_2 1.1 year 2014-15 Diversity Index 93.5 district 1 Name: 0, dtype: object
In [94]:
# INSERTED FROM HERE
a_school_map_table = sorted_school_table[:100].copy()
a_school_map_table = a_school_map_table[['WKT', 'School Name','total_enrollment']]
a_school_map_table = a_school_map_table.rename({
'School Name': 'Name',
})
a_school_map_table['FillColor'] = 'g'
In [95]:
b_school_map_table = sorted_school_table[-100:].copy()
b_school_map_table = b_school_map_table[['WKT', 'School Name','total_enrollment']]
b_school_map_table = b_school_map_table.rename({
'School Name': 'Name',
})
b_school_map_table['FillColor'] = 'r'
In [96]:
t = pd.merge(dist_table, mean_table, left_on='school_dis', right_on='district')
In [97]:
t[:3]
Out[97]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| school_dis | shape_area | shape_leng | geometry_object | geometry_layer | geometry_proj4 | district | economic_need_index | |
|---|---|---|---|---|---|---|---|---|
| 0 | 16.0 | 4.676362e+07 | 35848.904605 | POLYGON ((-73.93311862859143 40.69579115384632... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 16 | 79.038028 |
| 1 | 32.0 | 5.189850e+07 | 37251.057847 | POLYGON ((-73.91180710069435 40.70343495202662... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 32 | 81.194444 |
| 2 | 6.0 | 9.634170e+07 | 70447.849084 | POLYGON ((-73.92640556921116 40.87762147653734... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 6 | 77.312322 |
In [98]:
district_map_table = t.copy()
district_map_table['WKT'] = district_map_table['geometry_object'].apply(
lambda x: x.wkt)
# Define FillColor
from sklearn.preprocessing import MinMaxScaler
scaler = MinMaxScaler(feature_range=(1, 100))
In [99]:
array = district_map_table['economic_need_index'].values.reshape(-1, 1)
scaler.fit(array)
district_map_table['scaled_index'] = scaler.transform(array)
In [100]:
district_map_table.head()
Out[100]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| school_dis | shape_area | shape_leng | geometry_object | geometry_layer | geometry_proj4 | district | economic_need_index | WKT | scaled_index | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 16.0 | 4.676362e+07 | 35848.904605 | POLYGON ((-73.93311862859143 40.69579115384632... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 16 | 79.038028 | POLYGON ((-73.93311862859143 40.69579115384632... | 84.226077 |
| 1 | 32.0 | 5.189850e+07 | 37251.057847 | POLYGON ((-73.91180710069435 40.70343495202662... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 32 | 81.194444 | POLYGON ((-73.91180710069435 40.70343495202662... | 88.344579 |
| 2 | 6.0 | 9.634170e+07 | 70447.849084 | POLYGON ((-73.92640556921116 40.87762147653734... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 6 | 77.312322 | POLYGON ((-73.92640556921116 40.87762147653734... | 80.930181 |
| 3 | 31.0 | 1.604472e+09 | 434471.412859 | (POLYGON ((-74.05050806403247 40.5664220341608... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 31 | 49.263141 | MULTIPOLYGON (((-74.05050806403247 40.56642203... | 27.359534 |
| 4 | 8.0 | 2.588266e+08 | 223080.142631 | (POLYGON ((-73.83979488562171 40.8356192074928... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 8 | 78.060000 | MULTIPOLYGON (((-73.83979488562171 40.83561920... | 82.358158 |
In [101]:
district_map_table.loc[district_map_table['scaled_index'] < 25, 'FillColor'] = '#d73027'
In [102]:
district_map_table.loc[(district_map_table['scaled_index'] >= 25)
& (district_map_table['scaled_index'] < 50), 'FillColor'] = '#fee08b'
In [103]:
district_map_table.loc[(district_map_table['scaled_index'] >= 50)
& (district_map_table['scaled_index'] < 75), 'FillColor'] = '#a6d96a'
In [104]:
district_map_table.loc[(district_map_table['scaled_index'] >= 75), 'FillColor'] = '#1a9850'
In [105]:
district_map_table
Out[105]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| school_dis | shape_area | shape_leng | geometry_object | geometry_layer | geometry_proj4 | district | economic_need_index | WKT | scaled_index | FillColor | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 16.0 | 4.676362e+07 | 35848.904605 | POLYGON ((-73.93311862859143 40.69579115384632... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 16 | 79.038028 | POLYGON ((-73.93311862859143 40.69579115384632... | 84.226077 | #1a9850 |
| 1 | 32.0 | 5.189850e+07 | 37251.057847 | POLYGON ((-73.91180710069435 40.70343495202662... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 32 | 81.194444 | POLYGON ((-73.91180710069435 40.70343495202662... | 88.344579 | #1a9850 |
| 2 | 6.0 | 9.634170e+07 | 70447.849084 | POLYGON ((-73.92640556921116 40.87762147653734... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 6 | 77.312322 | POLYGON ((-73.92640556921116 40.87762147653734... | 80.930181 | #1a9850 |
| 3 | 31.0 | 1.604472e+09 | 434471.412859 | (POLYGON ((-74.05050806403247 40.5664220341608... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 31 | 49.263141 | MULTIPOLYGON (((-74.05050806403247 40.56642203... | 27.359534 | #fee08b |
| 4 | 8.0 | 2.588266e+08 | 223080.142631 | (POLYGON ((-73.83979488562171 40.8356192074928... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 8 | 78.060000 | MULTIPOLYGON (((-73.83979488562171 40.83561920... | 82.358158 | #1a9850 |
| 5 | 7.0 | 9.226247e+07 | 65294.452403 | (POLYGON ((-73.89680883223774 40.7958084451597... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 7 | 85.659751 | MULTIPOLYGON (((-73.89680883223774 40.79580844... | 96.872791 | #1a9850 |
| 6 | 23.0 | 4.740069e+07 | 40317.452033 | (POLYGON ((-73.92044366203014 40.6656262871675... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 23 | 80.548125 | MULTIPOLYGON (((-73.92044366203014 40.66562628... | 87.110185 | #1a9850 |
| 7 | 29.0 | 4.201981e+08 | 135035.241651 | POLYGON ((-73.73816144093141 40.72895809117297... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 29 | 52.408205 | POLYGON ((-73.73816144093141 40.72895809117297... | 33.366237 | #fee08b |
| 8 | 26.0 | 4.247909e+08 | 125677.678898 | POLYGON ((-73.74344992332192 40.77824115291502... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 26 | 35.461491 | POLYGON ((-73.74344992332192 40.77824115291502... | 1.000000 | #d73027 |
| 9 | 15.0 | 1.961534e+08 | 153439.165680 | POLYGON ((-73.98633135042395 40.69105051012824... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 15 | 56.116456 | POLYGON ((-73.98633135042395 40.69105051012824... | 40.448561 | #fee08b |
| 10 | 17.0 | 1.284414e+08 | 68341.398899 | POLYGON ((-73.92044366203014 40.6656262871675,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 17 | 70.930000 | POLYGON ((-73.92044366203014 40.6656262871675,... | 68.740694 | #a6d96a |
| 11 | 19.0 | 2.034175e+08 | 184183.167312 | (POLYGON ((-73.846736514711 40.60485301485166,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 19 | 79.049174 | MULTIPOLYGON (((-73.846736514711 40.6048530148... | 84.247363 | #1a9850 |
| 12 | 27.0 | 7.955971e+08 | 589135.502728 | (POLYGON ((-73.82784008953526 40.5887858248046... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 27 | 61.418587 | MULTIPOLYGON (((-73.82784008953526 40.58878582... | 50.575011 | #a6d96a |
| 13 | 13.0 | 1.048706e+08 | 86635.210559 | POLYGON ((-73.97906084911834 40.70594602894087... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 13 | 67.609955 | POLYGON ((-73.97906084911834 40.70594602894087... | 62.399797 | #a6d96a |
| 14 | 18.0 | 1.751488e+08 | 121184.158477 | (POLYGON ((-73.86706149472118 40.5820879767934... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 18 | 63.183750 | MULTIPOLYGON (((-73.86706149472118 40.58208797... | 53.946265 | #a6d96a |
| 15 | 20.0 | 2.426965e+08 | 94309.778946 | POLYGON ((-74.02552971543656 40.65147855069281... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 20 | 67.645238 | POLYGON ((-74.02552971543656 40.65147855069281... | 62.467184 | #a6d96a |
| 16 | 3.0 | 1.134889e+08 | 52072.051321 | POLYGON ((-73.95671863064405 40.78660079332199... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 3 | 58.725581 | POLYGON ((-73.95671863064405 40.78660079332199... | 45.431685 | #fee08b |
| 17 | 5.0 | 5.251977e+07 | 44469.588221 | POLYGON ((-73.93515659239551 40.83268240623763... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 5 | 79.583051 | POLYGON ((-73.93515659239551 40.83268240623763... | 85.267006 | #1a9850 |
| 18 | 9.0 | 8.341539e+07 | 46648.958586 | POLYGON ((-73.9212971968614 40.85428933985649,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 9 | 87.297134 | POLYGON ((-73.9212971968614 40.85428933985649,... | 100.000000 | #1a9850 |
| 19 | 1.0 | 3.516075e+07 | 28641.276279 | POLYGON ((-73.97177410965313 40.72582128133706... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 1 | 69.308088 | POLYGON ((-73.97177410965313 40.72582128133706... | 65.643032 | #a6d96a |
| 20 | 14.0 | 1.503102e+08 | 95792.082090 | POLYGON ((-73.95439555417087 40.73911477252251... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 14 | 72.794898 | POLYGON ((-73.95439555417087 40.73911477252251... | 72.302430 | #a6d96a |
| 21 | 2.0 | 2.804677e+08 | 214498.703163 | (POLYGON ((-74.0438776163991 40.69018767537123... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 2 | 53.006055 | MULTIPOLYGON (((-74.0438776163991 40.690187675... | 34.508060 | #fee08b |
| 22 | 4.0 | 5.262043e+07 | 52061.828459 | (POLYGON ((-73.92133752419399 40.8008521064970... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 4 | 78.418235 | MULTIPOLYGON (((-73.92133752419399 40.80085210... | 83.042345 | #1a9850 |
| 23 | 10.0 | 2.825415e+08 | 94957.570537 | POLYGON ((-73.86789798628736 40.90294017690526... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 10 | 81.173067 | POLYGON ((-73.86789798628736 40.90294017690526... | 88.303750 | #1a9850 |
| 24 | 10.0 | 3.282963e+06 | 7883.372664 | (POLYGON ((-73.9089323517538 40.8721573479701,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 10 | 81.173067 | MULTIPOLYGON (((-73.9089323517538 40.872157347... | 88.303750 | #1a9850 |
| 25 | 12.0 | 6.907182e+07 | 48527.595776 | POLYGON ((-73.88284445574813 40.84781722645163... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 12 | 86.122078 | POLYGON ((-73.88284445574813 40.84781722645163... | 97.755781 | #1a9850 |
| 26 | 25.0 | 4.436285e+08 | 175827.007127 | POLYGON ((-73.82049919995312 40.80101146781899... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 25 | 49.729444 | POLYGON ((-73.82049919995312 40.80101146781899... | 28.250119 | #fee08b |
| 27 | 28.0 | 2.475679e+08 | 114694.912786 | POLYGON ((-73.84485477879177 40.7357514698091,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 28 | 53.155288 | POLYGON ((-73.84485477879177 40.7357514698091,... | 34.793079 | #fee08b |
| 28 | 24.0 | 3.949782e+08 | 127343.703736 | (POLYGON ((-73.90641585511733 40.7398683641967... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 24 | 62.594286 | MULTIPOLYGON (((-73.90641585511733 40.73986836... | 52.820457 | #a6d96a |
| 29 | 30.0 | 3.181290e+08 | 150392.978241 | POLYGON ((-73.90647314610101 40.79018117520807... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 30 | 59.615349 | POLYGON ((-73.90647314610101 40.79018117520807... | 47.131037 | #fee08b |
| 30 | 21.0 | 2.101971e+08 | 123858.087345 | POLYGON ((-73.96184657346174 40.62757081710622... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 21 | 66.737037 | POLYGON ((-73.96184657346174 40.62757081710622... | 60.732626 | #a6d96a |
| 31 | 22.0 | 3.855533e+08 | 271718.504936 | (POLYGON ((-73.91990064270161 40.5996005215871... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 22 | 58.755000 | MULTIPOLYGON (((-73.91990064270161 40.59960052... | 45.487871 | #fee08b |
| 32 | 11.0 | 3.926651e+08 | 305305.863567 | (POLYGON ((-73.78833349834532 40.8346671297593... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 11 | 68.505313 | MULTIPOLYGON (((-73.78833349834532 40.83466712... | 64.109825 | #a6d96a |
In [106]:
a_school_map_table = a_school_map_table.rename(columns={
'School Name': 'Name',
})
b_school_map_table = b_school_map_table.rename(columns={
'School Name': 'Name',
})
district_map_table['Name'] = district_map_table['district']
In [107]:
#a_school_map_table['radiusInPixelsRange3-27'] = a_school_map_table['total_enrollment']
In [108]:
#b_school_map_table['radiusInPixelsRange3-27'] = b_school_map_table['total_enrollment']
In [109]:
# Extract columns
map_table = pd.concat([
district_map_table[['WKT','FillColor','Name']],
a_school_map_table[['WKT','FillColor','Name']],
b_school_map_table[['WKT','FillColor','Name']],
])
In [110]:
a_school_map_table[:3]
Out[110]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| WKT | Name | total_enrollment | FillColor | radiusInPixelsRange3-27 | |
|---|---|---|---|---|---|
| 1085 | POINT (-73.821532 40.737038) | Townsend Harris High School | 1133 | g | 1133 |
| 1086 | POINT (-73.821532 40.737038) | Townsend Harris High School | 1110 | g | 1110 |
| 1087 | POINT (-73.821532 40.737038) | Townsend Harris High School | 1141 | g | 1141 |
In [111]:
district_map_table
Out[111]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| school_dis | shape_area | shape_leng | geometry_object | geometry_layer | geometry_proj4 | district | economic_need_index | WKT | scaled_index | FillColor | Name | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 16.0 | 4.676362e+07 | 35848.904605 | POLYGON ((-73.93311862859143 40.69579115384632... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 16 | 79.038028 | POLYGON ((-73.93311862859143 40.69579115384632... | 84.226077 | #1a9850 | 16 |
| 1 | 32.0 | 5.189850e+07 | 37251.057847 | POLYGON ((-73.91180710069435 40.70343495202662... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 32 | 81.194444 | POLYGON ((-73.91180710069435 40.70343495202662... | 88.344579 | #1a9850 | 32 |
| 2 | 6.0 | 9.634170e+07 | 70447.849084 | POLYGON ((-73.92640556921116 40.87762147653734... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 6 | 77.312322 | POLYGON ((-73.92640556921116 40.87762147653734... | 80.930181 | #1a9850 | 6 |
| 3 | 31.0 | 1.604472e+09 | 434471.412859 | (POLYGON ((-74.05050806403247 40.5664220341608... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 31 | 49.263141 | MULTIPOLYGON (((-74.05050806403247 40.56642203... | 27.359534 | #fee08b | 31 |
| 4 | 8.0 | 2.588266e+08 | 223080.142631 | (POLYGON ((-73.83979488562171 40.8356192074928... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 8 | 78.060000 | MULTIPOLYGON (((-73.83979488562171 40.83561920... | 82.358158 | #1a9850 | 8 |
| 5 | 7.0 | 9.226247e+07 | 65294.452403 | (POLYGON ((-73.89680883223774 40.7958084451597... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 7 | 85.659751 | MULTIPOLYGON (((-73.89680883223774 40.79580844... | 96.872791 | #1a9850 | 7 |
| 6 | 23.0 | 4.740069e+07 | 40317.452033 | (POLYGON ((-73.92044366203014 40.6656262871675... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 23 | 80.548125 | MULTIPOLYGON (((-73.92044366203014 40.66562628... | 87.110185 | #1a9850 | 23 |
| 7 | 29.0 | 4.201981e+08 | 135035.241651 | POLYGON ((-73.73816144093141 40.72895809117297... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 29 | 52.408205 | POLYGON ((-73.73816144093141 40.72895809117297... | 33.366237 | #fee08b | 29 |
| 8 | 26.0 | 4.247909e+08 | 125677.678898 | POLYGON ((-73.74344992332192 40.77824115291502... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 26 | 35.461491 | POLYGON ((-73.74344992332192 40.77824115291502... | 1.000000 | #d73027 | 26 |
| 9 | 15.0 | 1.961534e+08 | 153439.165680 | POLYGON ((-73.98633135042395 40.69105051012824... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 15 | 56.116456 | POLYGON ((-73.98633135042395 40.69105051012824... | 40.448561 | #fee08b | 15 |
| 10 | 17.0 | 1.284414e+08 | 68341.398899 | POLYGON ((-73.92044366203014 40.6656262871675,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 17 | 70.930000 | POLYGON ((-73.92044366203014 40.6656262871675,... | 68.740694 | #a6d96a | 17 |
| 11 | 19.0 | 2.034175e+08 | 184183.167312 | (POLYGON ((-73.846736514711 40.60485301485166,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 19 | 79.049174 | MULTIPOLYGON (((-73.846736514711 40.6048530148... | 84.247363 | #1a9850 | 19 |
| 12 | 27.0 | 7.955971e+08 | 589135.502728 | (POLYGON ((-73.82784008953526 40.5887858248046... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 27 | 61.418587 | MULTIPOLYGON (((-73.82784008953526 40.58878582... | 50.575011 | #a6d96a | 27 |
| 13 | 13.0 | 1.048706e+08 | 86635.210559 | POLYGON ((-73.97906084911834 40.70594602894087... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 13 | 67.609955 | POLYGON ((-73.97906084911834 40.70594602894087... | 62.399797 | #a6d96a | 13 |
| 14 | 18.0 | 1.751488e+08 | 121184.158477 | (POLYGON ((-73.86706149472118 40.5820879767934... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 18 | 63.183750 | MULTIPOLYGON (((-73.86706149472118 40.58208797... | 53.946265 | #a6d96a | 18 |
| 15 | 20.0 | 2.426965e+08 | 94309.778946 | POLYGON ((-74.02552971543656 40.65147855069281... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 20 | 67.645238 | POLYGON ((-74.02552971543656 40.65147855069281... | 62.467184 | #a6d96a | 20 |
| 16 | 3.0 | 1.134889e+08 | 52072.051321 | POLYGON ((-73.95671863064405 40.78660079332199... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 3 | 58.725581 | POLYGON ((-73.95671863064405 40.78660079332199... | 45.431685 | #fee08b | 3 |
| 17 | 5.0 | 5.251977e+07 | 44469.588221 | POLYGON ((-73.93515659239551 40.83268240623763... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 5 | 79.583051 | POLYGON ((-73.93515659239551 40.83268240623763... | 85.267006 | #1a9850 | 5 |
| 18 | 9.0 | 8.341539e+07 | 46648.958586 | POLYGON ((-73.9212971968614 40.85428933985649,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 9 | 87.297134 | POLYGON ((-73.9212971968614 40.85428933985649,... | 100.000000 | #1a9850 | 9 |
| 19 | 1.0 | 3.516075e+07 | 28641.276279 | POLYGON ((-73.97177410965313 40.72582128133706... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 1 | 69.308088 | POLYGON ((-73.97177410965313 40.72582128133706... | 65.643032 | #a6d96a | 1 |
| 20 | 14.0 | 1.503102e+08 | 95792.082090 | POLYGON ((-73.95439555417087 40.73911477252251... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 14 | 72.794898 | POLYGON ((-73.95439555417087 40.73911477252251... | 72.302430 | #a6d96a | 14 |
| 21 | 2.0 | 2.804677e+08 | 214498.703163 | (POLYGON ((-74.0438776163991 40.69018767537123... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 2 | 53.006055 | MULTIPOLYGON (((-74.0438776163991 40.690187675... | 34.508060 | #fee08b | 2 |
| 22 | 4.0 | 5.262043e+07 | 52061.828459 | (POLYGON ((-73.92133752419399 40.8008521064970... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 4 | 78.418235 | MULTIPOLYGON (((-73.92133752419399 40.80085210... | 83.042345 | #1a9850 | 4 |
| 23 | 10.0 | 2.825415e+08 | 94957.570537 | POLYGON ((-73.86789798628736 40.90294017690526... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 10 | 81.173067 | POLYGON ((-73.86789798628736 40.90294017690526... | 88.303750 | #1a9850 | 10 |
| 24 | 10.0 | 3.282963e+06 | 7883.372664 | (POLYGON ((-73.9089323517538 40.8721573479701,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 10 | 81.173067 | MULTIPOLYGON (((-73.9089323517538 40.872157347... | 88.303750 | #1a9850 | 10 |
| 25 | 12.0 | 6.907182e+07 | 48527.595776 | POLYGON ((-73.88284445574813 40.84781722645163... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 12 | 86.122078 | POLYGON ((-73.88284445574813 40.84781722645163... | 97.755781 | #1a9850 | 12 |
| 26 | 25.0 | 4.436285e+08 | 175827.007127 | POLYGON ((-73.82049919995312 40.80101146781899... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 25 | 49.729444 | POLYGON ((-73.82049919995312 40.80101146781899... | 28.250119 | #fee08b | 25 |
| 27 | 28.0 | 2.475679e+08 | 114694.912786 | POLYGON ((-73.84485477879177 40.7357514698091,... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 28 | 53.155288 | POLYGON ((-73.84485477879177 40.7357514698091,... | 34.793079 | #fee08b | 28 |
| 28 | 24.0 | 3.949782e+08 | 127343.703736 | (POLYGON ((-73.90641585511733 40.7398683641967... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 24 | 62.594286 | MULTIPOLYGON (((-73.90641585511733 40.73986836... | 52.820457 | #a6d96a | 24 |
| 29 | 30.0 | 3.181290e+08 | 150392.978241 | POLYGON ((-73.90647314610101 40.79018117520807... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 30 | 59.615349 | POLYGON ((-73.90647314610101 40.79018117520807... | 47.131037 | #fee08b | 30 |
| 30 | 21.0 | 2.101971e+08 | 123858.087345 | POLYGON ((-73.96184657346174 40.62757081710622... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 21 | 66.737037 | POLYGON ((-73.96184657346174 40.62757081710622... | 60.732626 | #a6d96a | 21 |
| 31 | 22.0 | 3.855533e+08 | 271718.504936 | (POLYGON ((-73.91990064270161 40.5996005215871... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 22 | 58.755000 | MULTIPOLYGON (((-73.91990064270161 40.59960052... | 45.487871 | #fee08b | 22 |
| 32 | 11.0 | 3.926651e+08 | 305305.863567 | (POLYGON ((-73.78833349834532 40.8346671297593... | geo_export_9660305b-263b-4410-9560-e071b847c573 | +proj=longlat +ellps=WGS84 +no_defs | 11 | 68.505313 | MULTIPOLYGON (((-73.78833349834532 40.83466712... | 64.109825 | #a6d96a | 11 |
In [112]:
type(district_map_table)
Out[112]:
geotable.GeoTable
In [ ]:
In [ ]:
In [113]:
target_path = target_folder + '/choropleth.csv'
map_table.to_csv(target_path, index=False)
print('a_geotable_path = %s' % target_path)
a_geotable_path = /tmp/choropleth.csv
In [114]:
map_table
Out[114]:
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<p>233 rows × 3 columns</p>
| WKT | FillColor | Name | |
|---|---|---|---|
| 0 | POLYGON ((-73.93311862859143 40.69579115384632... | #1a9850 | 16 |
| 1 | POLYGON ((-73.91180710069435 40.70343495202662... | #1a9850 | 32 |
| 2 | POLYGON ((-73.92640556921116 40.87762147653734... | #1a9850 | 6 |
| 3 | MULTIPOLYGON (((-74.05050806403247 40.56642203... | #fee08b | 31 |
| 4 | MULTIPOLYGON (((-73.83979488562171 40.83561920... | #1a9850 | 8 |
| 5 | MULTIPOLYGON (((-73.89680883223774 40.79580844... | #1a9850 | 7 |
| 6 | MULTIPOLYGON (((-73.92044366203014 40.66562628... | #1a9850 | 23 |
| 7 | POLYGON ((-73.73816144093141 40.72895809117297... | #fee08b | 29 |
| 8 | POLYGON ((-73.74344992332192 40.77824115291502... | #d73027 | 26 |
| 9 | POLYGON ((-73.98633135042395 40.69105051012824... | #fee08b | 15 |
| 10 | POLYGON ((-73.92044366203014 40.6656262871675,... | #a6d96a | 17 |
| 11 | MULTIPOLYGON (((-73.846736514711 40.6048530148... | #1a9850 | 19 |
| 12 | MULTIPOLYGON (((-73.82784008953526 40.58878582... | #a6d96a | 27 |
| 13 | POLYGON ((-73.97906084911834 40.70594602894087... | #a6d96a | 13 |
| 14 | MULTIPOLYGON (((-73.86706149472118 40.58208797... | #a6d96a | 18 |
| 15 | POLYGON ((-74.02552971543656 40.65147855069281... | #a6d96a | 20 |
| 16 | POLYGON ((-73.95671863064405 40.78660079332199... | #fee08b | 3 |
| 17 | POLYGON ((-73.93515659239551 40.83268240623763... | #1a9850 | 5 |
| 18 | POLYGON ((-73.9212971968614 40.85428933985649,... | #1a9850 | 9 |
| 19 | POLYGON ((-73.97177410965313 40.72582128133706... | #a6d96a | 1 |
| 20 | POLYGON ((-73.95439555417087 40.73911477252251... | #a6d96a | 14 |
| 21 | MULTIPOLYGON (((-74.0438776163991 40.690187675... | #fee08b | 2 |
| 22 | MULTIPOLYGON (((-73.92133752419399 40.80085210... | #1a9850 | 4 |
| 23 | POLYGON ((-73.86789798628736 40.90294017690526... | #1a9850 | 10 |
| 24 | MULTIPOLYGON (((-73.9089323517538 40.872157347... | #1a9850 | 10 |
| 25 | POLYGON ((-73.88284445574813 40.84781722645163... | #1a9850 | 12 |
| 26 | POLYGON ((-73.82049919995312 40.80101146781899... | #fee08b | 25 |
| 27 | POLYGON ((-73.84485477879177 40.7357514698091,... | #fee08b | 28 |
| 28 | MULTIPOLYGON (((-73.90641585511733 40.73986836... | #a6d96a | 24 |
| 29 | POLYGON ((-73.90647314610101 40.79018117520807... | #fee08b | 30 |
| ... | ... | ... | ... |
| 1010 | POINT (-73.904143 40.677528) | r | Aspirations Diploma Plus High School |
| 1011 | POINT (-73.904143 40.677528) | r | Aspirations Diploma Plus High School |
| 363 | POINT (-73.919949 40.818645) | r | Bronx Haven High School |
| 361 | POINT (-73.919949 40.818645) | r | Bronx Haven High School |
| 360 | POINT (-73.919949 40.818645) | r | Bronx Haven High School |
| 362 | POINT (-73.919949 40.818645) | r | Bronx Haven High School |
| 737 | POINT (-73.871505 40.743228) | r | VOYAGES Preparatory |
| 738 | POINT (-73.871505 40.743228) | r | VOYAGES Preparatory |
| 739 | POINT (-73.871505 40.743228) | r | VOYAGES Preparatory |
| 736 | POINT (-73.871505 40.743228) | r | VOYAGES Preparatory |
| 883 | POINT (-73.923881 40.666251) | r | Brownsville Academy High School |
| 881 | POINT (-73.923881 40.666251) | r | Brownsville Academy High School |
| 880 | POINT (-73.923881 40.666251) | r | Brownsville Academy High School |
| 882 | POINT (-73.923881 40.666251) | r | Brownsville Academy High School |
| 307 | POINT (-73.939974 40.807692) | r | Harlem Renaissance High School |
| 306 | POINT (-73.939974 40.807692) | r | Harlem Renaissance High School |
| 305 | POINT (-73.939974 40.807692) | r | Harlem Renaissance High School |
| 304 | POINT (-73.939974 40.807692) | r | Harlem Renaissance High School |
| 898 | POINT (-73.920658 40.659914) | r | East Brooklyn Community High School |
| 899 | POINT (-73.920658 40.659914) | r | East Brooklyn Community High School |
| 897 | POINT (-73.920658 40.659914) | r | East Brooklyn Community High School |
| 896 | POINT (-73.920658 40.659914) | r | East Brooklyn Community High School |
| 1290 | POINT (-73.915217 40.695875) | r | Bushwick Community High School |
| 1289 | POINT (-73.915217 40.695875) | r | Bushwick Community High School |
| 1288 | POINT (-73.915217 40.695875) | r | Bushwick Community High School |
| 1291 | POINT (-73.915217 40.695875) | r | Bushwick Community High School |
| 730 | POINT (-73.95818 40.692015) | r | Brooklyn High School for Leadership and Community |
| 728 | POINT (-73.95818 40.692015) | r | Brooklyn High School for Leadership and Community |
| 731 | POINT (-73.95818 40.692015) | r | Brooklyn High School for Leadership and Community |
| 729 | POINT (-73.95818 40.692015) | r | Brooklyn High School for Leadership and Community |
In [115]:
len(school_map_table)
Out[115]:
7173
In [116]:
max(district_map_table['scaled_index'])
Out[116]:
100.00000000000001
In [117]:
%matplotlib inline
t['economic_need_index'].plot(kind='bar')
Out[117]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f8b5a317b00>
In [118]:
sorted_school_table.to_csv('data.csv', index=False)
In [ ]:
Notebook Creator
Notebook Created
Notebook Modified
Notebook Contents
Recent Tools