In [66]:
import fiona

#load the file
c = fiona.open('../../data/planning_neighborhoods/planning_neighborhoods.shp','r')

In [73]:
import pandas as pd

In [74]:
crimes_df = pd.read_excel("../../data/data_upload/CI.3.a.ViolentCrimes.xlsx", "Sheet1")
crimes_df.head(5)


Out[74]:
Neighborhood Number Rate_per_1000
0 Bayview 3596 105.8
1 Bernal Heights 1128 43.8
2 Castro/Upper Market 1264 64.1
3 Chinatown 479 50.8
4 Crocker Amazon 272 20.7

5 rows × 3 columns


In [75]:
# construct DF and List of Neighborhoods + cordinates
neigh_list = []
for geog in list(c):
    cord = geog['geometry']['coordinates']
    neighborhood = geog['properties']['neighborho']
    dict1 = {'cord':cord,'Neighborhood':neighborhood}
    neigh_list.append(dict1)

In [76]:
neigh_df = pd.DataFrame(neigh_list)
neigh_df.head(5)


Out[76]:
Neighborhood cord
0 Seacliff [[(5988365.05782, 2115435.35639), (5988288.754...
1 Haight Ashbury [[(6002126.62248, 2108273.90418), (6001869.999...
2 Outer Mission [[(5996370.18846, 2086244.42625), (5996290.129...
3 Russian Hill [[(6006763.32154, 2122720.15547), (6006843.699...
4 Noe Valley [[(6005034.95197, 2103689.95042), (6005165.082...

5 rows × 2 columns


In [77]:
# Merge the Datasets
merged_df = pd.merge(neigh_df,crimes_df, left_on='Neighborhood', right_on='Neighborhood', how='inner')
merged_df


Out[77]:
Neighborhood cord Number Rate_per_1000
0 Seacliff [[(5988365.05782, 2115435.35639), (5988288.754... 34 13.3
1 Lakeshore [[(5991520.59383, 2086337.31583), (5991006.287... 442 22.7
2 Excelsior [[(6000325.2104, 2089160.63105), (6001087.7367... 1315 34.5
3 Parkside [[(5992160.94327, 2097376.25898), (5991875.425... 400 14.6
4 Mission [[(6005206.72, 2100493.38004), (6005165.08261,... 5038 91.5
5 Marina [[[(5999268.871310309, 2121576.646927133), (59... 599 28.4
6 Bayview [[[(6015800.182407722, 2100260.713509217), (60... 3596 105.8
7 Presidio [[(5998940.95399, 2122155.51683), (5998966.983... 2 0.7
8 Chinatown [[(6010143.00212, 2115802.89174), (6010134.294... 479 50.8

9 rows × 4 columns


In [79]:
print crimes_df['Neighborhood']
print neigh_df['Neighborhood']
%dirs


0                       Bayview
1                Bernal Heights
2           Castro/Upper Market
3                     Chinatown
4                Crocker Amazon
5     Diamond Heights/Glen Park
6         Downtown/Civic Center
7                     Excelsior
8            Financial District
9              Golden Gate Park
10               Haight Ashbury
11               Inner Richmond
12                 Inner Sunset
13                    Lakeshore
14                       Marina
15                  Mission Bay
16                      Mission
17                     Nob Hill
18                   Noe Valley
19                  North Beach
20                   Ocean View
21                Outer Mission
22               Outer Richmond
23                 Outer Sunset
24              Pacific Heights
25                     Parkside
26                 Potrero Hill
27                     Presidio
28             Presidio Heights
29                 Russian Hill
30                     Seacliff
31              South of Market
32          Treasure Island/YBI
33                   Twin Peaks
34            Visitacion Valley
35           West of Twin Peaks
36             Western Addition
Name: Neighborhood, dtype: object
0                  Seacliff
1            Haight Ashbury
2             Outer Mission
3              Russian Hill
4                Noe Valley
5              Inner Sunset
6     Downtown/Civic Center
7           Diamond Heights
8       Treasure Island/YBI
9                 Lakeshore
10           Outer Richmond
11           Crocker Amazon
12                Excelsior
13                 Parkside
14       Financial District
15               Ocean View
16                  Mission
17       West of Twin Peaks
18           Inner Richmond
19                   Marina
20                  Bayview
21        Visitacion Valley
22          Pacific Heights
23                 Presidio
24                 Nob Hill
25             Outer Sunset
26         Western Addition
27         Golden Gate Park
28         Presidio Heights
29          South of Market
30                Glen Park
31             Potrero Hill
32      Castro/Upper Market
33               Twin Peaks
34           Bernal Heights
35                Chinatown
36              North Beach
Name: Neighborhood, dtype: object
Out[79]:
[]

In [ ]: