In [2]:

#CrossCompute
target_folder = '/tmp'

In [3]:

from geotable import GeoTable
t = GeoTable.load('ParksProperties.zip')
t.iloc[0]

Out[3]:

OBJECTID                                                       28295
GISPROPNUM                                                      Q507
OMPPROPID                                                       Q507
DEPARTMENT                                                      Q-01
PERMITDIST                                                          
PERMITPARE                                                          
PARENTID                                                        Q-01
LOCATION                             29 St. bet. 40 Ave. and 39 Ave.
COMMUNITYB                                                       401
COUNCILDIS                                                        26
PRECINCT                                                            
ZIPCODE                                                        11101
BOROUGH                                                            Q
ACRES                                                          0.057
RETIRED                                                        False
EAPPLY                                                              
PIP_RATABL                                                          
GISOBJID                                                   100024485
CLASS                                                           PARK
COMMISSION         [1447646056, 32535, -1920231825, 32535, -12713...
ACQUISITIO                                       2015-10-07 00:00:00
ADDRESS                                              39-22 29 STREET
JURISDICTI                                                       DPR
MAPPED                                                         False
NAME311                                    Windmill Community Garden
PERMIT                                                              
SIGNNAME                                   Windmill Community Garden
SUBCATEGOR                                                          
TYPECATEGO                                                    Garden
URL                                                                 
WATERFRONT                                                        No
NYS_ASSEMB                                                        30
NYS_SENATE                                                        12
US_CONGRES                                                        12
GlobalID                      {D1553DBD-8718-40B7-95E0-1B2E5B6AD619}
geometry_object    POLYGON ((1002248.37672089 213640.5232279748, ...
geometry_layer                                       ParksProperties
geometry_proj4     +proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...
Name: 0, dtype: object

In [4]:

simplified_park_table = t[['SIGNNAME', 'geometry_object', 'geometry_layer', 'geometry_proj4']]
simplified_park_table.head()

Out[4]:

	SIGNNAME	geometry_object	geometry_layer	geometry_proj4
0	Windmill Community Garden	POLYGON ((1002248.37672089 213640.5232279748, ...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...
1	Howard Von Dohlen Playground	POLYGON ((1036254.216845557 194050.8304520547,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...
2	Playground Seventy Five	POLYGON ((1037422.57868205 204086.9130701423, ...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...
3	Butterfly Gardens	POLYGON ((988060.1144150496 179682.8237550557,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...
4	Martin Luther King Jr. Park	POLYGON ((997843.6890079677 230729.1396412253,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...

In [5]:

# result = pd.merge(left, right, on='key')


simplified_park_table = t[['SIGNNAME', 'geometry_object', 'geometry_layer', 'geometry_proj4']].copy()
simplified_park_table.head()


simplified_park_table['name'] = simplified_park_table['SIGNNAME'].str.lower()
simplified_park_table['name'][0]

Out[5]:

'windmill community garden'

In [6]:

import pandas as pd
crime_table = pd.read_csv('nyc-park-crime-stats-q3-2017.csv', skiprows=3)
crime_table

Out[6]:

	PARK	BOROUGH	SIZE (ACRES)	CATEGORY	MURDER	RAPE	ROBBERY	FELONY ASSAULT	BURGLARY	GRAND LARCENY	GRAND LARCENY OF MOTOR VEHICLE	TOTAL
0	PELHAM BAY PARK	BRONX	2771.75	ONE ACRE OR LARGER	0	1	0	0	0	1	0	2
1	VAN CORTLANDT PARK	BRONX	1146.43	ONE ACRE OR LARGER	0	0	1	0	1	1	0	3
2	ROCKAWAY BEACH AND BOARDWALK	QUEENS	1072.56	ONE ACRE OR LARGER	0	1	1	2	0	10	0	14
3	FRESHKILLS PARK	STATEN ISLAND	913.32	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
4	FLUSHING MEADOWS CORONA PARK	QUEENS	897.69	ONE ACRE OR LARGER	0	0	6	1	0	9	0	16
5	LATOURETTE PARK & GOLF COURSE	STATEN ISLAND	843.97	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
6	MARINE PARK	BROOKLYN	798.00	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
7	BELT PARKWAY/SHORE PARKWAY	BROOKLYN/QUEENS	760.43	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
8	BRONX PARK	BRONX	718.37	ONE ACRE OR LARGER	0	0	2	0	0	2	0	4
9	FRANKLIN D. ROOSEVELT BOARDWALK AND BEACH	STATEN ISLAND	644.35	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
10	ALLEY POND PARK	QUEENS	635.51	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
11	PROSPECT PARK	BROOKLYN	526.25	ONE ACRE OR LARGER	0	0	7	3	0	11	0	21
12	FOREST PARK	QUEENS	506.86	ONE ACRE OR LARGER	0	0	0	0	0	1	0	1
13	GRAND CENTRAL PARKWAY	QUEENS	460.16	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
14	FERRY POINT PARK	BRONX	413.80	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
15	CONEY ISLAND BEACH & BOARDWALK	BROOKLYN	399.20	ONE ACRE OR LARGER	0	0	6	3	0	18	0	27
16	CUNNINGHAM PARK	QUEENS	358.00	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
17	RICHMOND PARKWAY	STATEN ISLAND	350.98	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
18	CROSS ISLAND PARKWAY	QUEENS	326.90	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
19	GREAT KILLS PARK	STATEN ISLAND	315.09	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
20	WOLFE'S POND PARK	STATEN ISLAND	302.69	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
21	CONFERENCE HOUSE PARK	STATEN ISLAND	286.38	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
22	RANDALL'S ISLAND PARK	MANHATTAN	256.11	ONE ACRE OR LARGER	0	0	0	0	1	51	0	52
23	ROCKAWAY COMMUNITY PARK	QUEENS	255.40	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
24	KISSENA PARK	QUEENS	237.15	ONE ACRE OR LARGER	0	0	0	1	0	2	0	3
25	RIVERSIDE PARK	MANHATTAN	235.77	ONE ACRE OR LARGER	0	0	7	0	2	9	0	18
26	HUTCHINSON RIVER PARKWAY	BRONX	229.14	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
27	BLUE HERON PARK	STATEN ISLAND	217.45	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
28	DYKER BEACH PARK	BROOKLYN	216.66	ONE ACRE OR LARGER	0	0	0	1	0	1	0	2
29	WILLOWBROOK PARK	STATEN ISLAND	214.95	ONE ACRE OR LARGER	0	0	0	0	0	0	0	0
...	...	...	...	...	...	...	...	...	...	...	...	...
1125	SEABURY PARK	BRONX	0.19	BASKETBALL & PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1126	EAE J MITCHELL PARK	BRONX	0.18	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1127	PENN TRIANGLE	BROOKLYN	0.18	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1128	METROPOLITAN RECREATION CENTER	BROOKLYN	0.18	POOL & RECREATION CENTER LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1129	RAMON APONTE PARK	MANHATTAN	0.17	BASKETBALL & PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1130	FIELD OF DREAMS PARK	BRONX	0.17	BASKETBALL & PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1131	BILL BOJANGLES ROBINSON PLAYGROUND	MANHATTAN	0.17	BASKETBALL & PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1132	RECREATION CENTER 54	MANHATTAN	0.17	POOL & RECREATION CENTER LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1133	PEARL ST PLAYGROUND	MANHATTAN	0.16	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1134	LEWIS PLAYGROUND	BROOKLYN	0.16	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1135	NORTH PACIFIC PLAYGROUND	BROOKLYN	0.16	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1136	MORRIS MESA	BRONX	0.15	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1137	PEACE PARK	BRONX	0.14	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1138	DUKE PARK	BROOKLYN	0.14	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1139	SOBEL PLAYGROUND	BROOKLYN	0.14	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1140	CAPTAIN JACOB JOSEPH PLAYGROUND	MANHATTAN	0.14	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1141	GRAND CANAL COURT	MANHATTAN	0.13	BASKETBALL & PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1142	LT. FEDERICO NARVAEZ TOT LOT	BROOKLYN	0.13	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1143	WEST 186TH STREET BASKETBALL COURT	MANHATTAN	0.12	BASKETBALL & PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1144	SOPHIE IRENE LOEB	MANHATTAN	0.12	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1145	BARRETTO PARK	BRONX	0.12	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1146	ABYSSINIAN TOT LOT	MANHATTAN	0.12	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1147	ALEXANDER'S ALLEY	BRONX	0.11	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1148	EAST FLATBUSH CHILDREN'S PARK	BROOKLYN	0.11	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1149	CORONA HEALTH SANCTUARY	QUEENS	0.10	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1150	MOHEGAN TRIANGLE	BRONX	0.10	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1151	PLAYGROUND OF THE AMERICAS	MANHATTAN	0.08	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1152	PRIVATE WILLIAM GRAY PARK	QUEENS	0.07	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1153	ADRIENNE'S PLAYGROUND	MANHATTAN	0.00	PLAYGROUND LESS THAN ONE ACRE	0	0	0	0	0	0	0	0
1154	TOTAL	NaN	NaN	NaN	0	13	137	91	7	261	2	511

<p>1155 rows × 12 columns</p>

In [7]:

simple_crime_table = crime_table[['PARK','TOTAL']].copy()

In [8]:

simple_crime_table['name'] = simple_crime_table['PARK'].str.lower()
simple_crime_table['name'][0]

Out[8]:

'pelham bay park'

In [9]:

left = simplified_park_table
right = simple_crime_table
result = pd.merge(left, right, on='name')

In [10]:

result[result['TOTAL']>0].head()

Out[10]:

	SIGNNAME	geometry_object	geometry_layer	geometry_proj4	name	PARK	TOTAL
17	Reiff Playground	POLYGON ((1011625.531851053 200910.7166666389,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...	reiff playground	REIFF PLAYGROUND	2
20	Booker T. Washington Playground	POLYGON ((994463.4490493089 231089.5316369832,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...	booker t. washington playground	BOOKER T. WASHINGTON PLAYGROUND	1
34	Cherry Tree Park	POLYGON ((998909.3118767142 225955.2187428921,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...	cherry tree park	CHERRY TREE PARK	1
36	Richman (Echo) Park	POLYGON ((1011661.451070547 248972.2855695635,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...	richman (echo) park	RICHMAN (ECHO) PARK	4
42	Thomas Jefferson Park	POLYGON ((1002278.696542144 227937.7689116448,...	ParksProperties	+proj=lcc +lat_1=40.66666666666666 +lat_2=41.0...	thomas jefferson park	THOMAS JEFFERSON PARK	2

In [11]:

result['FillReds'] = result['TOTAL']

In [ ]:

In [14]:

target_path = target_folder + '/map.csv'
result.to_csv(target_path,index=False)
print('the_geotable_path = %s' % target_path)

the_geotable_path = /tmp/map.csv

In [ ]:

Pay Notebook Creator: Constantino Zenteno Jr.	0
Set Container: Numerical CPU with TINY Memory for 10 Minutes	0
Total	0

coxAQ