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 [ ]: