ECSP




Pay Notebook Creator: Haige Cui0
Set Container: Numerical CPU with TINY Memory for 10 Minutes 0
Total0
In [1]:
ready_table_path = 'Table with average monthly savings(within 0.5 Mile) and tree count(within 0.5 Mile).csv'
search_radius_in_miles = 0.5
import pandas as pd
import numpy as np
ready_table = pd.read_csv(ready_table_path)
In [2]:
ready_table = ready_table[ready_table['Industry'] == 'Commercial']
ready_table = ready_table[ready_table['Program'] == 'ICAP']
ready_table[:3]
Out[2]:
<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>
Unnamed: 0 Unnamed: 0.1 Company Name BIN Industry Business Program Effective Date Address Postcode Borough Latitude Longitude Month Count Periodic Savings Total Tree Count within 0.5 Mile Periodic Savings within 0.5 Mile
49 49 52 Arista Coffee, Inc. 4059082 Commercial Company is a roaster of coffee beans ICAP 2016-10-28 59-01 55th Street 11318 QUEENS 40.716759 -73.913146 14 782.90 377 2249.792500
179 179 199 FEDERAL EXPRESS 3398770 Commercial OVERNIGHT SHP. ICAP 2015-07-08 51 20TH STREET, 1ST FL. 11232 BROOKLYN 40.665231 -73.998209 29 5747.47 145 2717.536429
318 318 357 Montefiore Medical Center 2096399 Commercial Research Facility ICAP 2016-07-01 111 EAST 210TH STREET 10467 BRONX 40.879885 -73.880481 18 11400.63 303 40453.725000
In [7]:
# Make kdtree
from pysal.cg.kdtree import KDTree
from pysal.cg import RADIUS_EARTH_MILES

sav_xys = ready_table[['Longitude', 'Latitude']].values
bin_sav = KDTree(sav_xys, distance_metric='Arc', radius=RADIUS_EARTH_MILES)
sav_count = len(sav_xys)
sav_xys
Out[7]:
array([[-73.913146,  40.716759],
       [-73.998209,  40.665231],
       [-73.880481,  40.879885],
       [-73.908282,  40.719945]])
In [4]:
def get_sav_average(r):
    xy = r['Longitude'], r['Latitude']
    distances, indices = bin_sav.query(
        xy,
        k=len(bin_sav.data),
        distance_upper_bound=search_radius_in_miles)
    indices = indices[~np.isnan(indices)]
    indices = [int(x) for x in indices]
    selected_sav_table = ready_table.loc[ready_table.index[indices]]
    
    #selected_sav_table = ready_table[ready_table['Industry'] == 'Commercial']
    #selected_sav_table = ready_table[ready_table['Program'] == 'ICAP']
    return selected_sav_table['Periodic Savings'].mean()
In [5]:
ready_table['Periodic Savings for your comparables within 0.5 Mile'] = ready_table.apply(get_sav_average, axis=1)
ready_table[:3]
Out[5]:
<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>
Unnamed: 0 Unnamed: 0.1 Company Name BIN Industry Business Program Effective Date Address Postcode Borough Latitude Longitude Month Count Periodic Savings Total Tree Count within 0.5 Mile Periodic Savings within 0.5 Mile Periodic Savings for your comparables within 0.5 Mile
49 49 52 Arista Coffee, Inc. 4059082 Commercial Company is a roaster of coffee beans ICAP 2016-10-28 59-01 55th Street 11318 QUEENS 40.716759 -73.913146 14 782.90 377 2249.792500 470.55
179 179 199 FEDERAL EXPRESS 3398770 Commercial OVERNIGHT SHP. ICAP 2015-07-08 51 20TH STREET, 1ST FL. 11232 BROOKLYN 40.665231 -73.998209 29 5747.47 145 2717.536429 5747.47
318 318 357 Montefiore Medical Center 2096399 Commercial Research Facility ICAP 2016-07-01 111 EAST 210TH STREET 10467 BRONX 40.879885 -73.880481 18 11400.63 303 40453.725000 11400.63
In [ ]:
Notebook Creator Notebook Created Notebook Modified Notebook Contents Recent Tools