joins

sjoin_experiments.py

# METHOD 1: INDEX ON GRID, LOOP THROUGH ISOCHRONES
# Add columns of zero and empty dictionaries
grid_copy['count_unique'] = 0
grid_copy['sum_times'] = 0
empty = {key: np.inf for key in isochrones.origin.unique()}
grid_copy['unique_ids'] = [[] for i in range(len(grid_copy))]
grid_copy['min_times'] = [{key: np.inf for key in isochrones.origin.unique()} for i in range(len(grid_copy))]
spatial_index = grid_copy.sindex
for idx, isochrone in isochrones.iterrows():
    possible_matches_index = list(spatial_index.intersection(isochrone.geometry.bounds))
    possible_matches = grid_copy.iloc[possible_matches_index]    
    precise_matches = possible_matches[possible_matches.intersects(isochrone.geometry)]
    grid_copy.iloc[precise_matches.index].apply(lambda x: x['unique_ids'].append(isochrone.origin), axis=1)
    grid_copy.iloc[precise_matches.index].apply(lambda x: x['min_times'].update({isochrone.origin:isochrone.time}) if x['min_times'][isochrone.origin] > isochrone.time else x, axis=1)
grid_copy['sum_times'] = grid_copy.apply(lambda x: sum(x['min_times'].values()), axis=1)
grid_copy['count_unique'] = grid_copy.apply(lambda x: len(set(x['unique_ids'])), axis=1)

# METHOD 2: GEOPANDAS SPATIAL JOIN
spatial_join = gpd.sjoin(grid, isochrones, how='left')
min_times = spatial_join.groupby([spatial_join.index, 'origin'])['time'].min()
total_times = min_times.groupby(level=[0]).sum()
count_unique = min_times.groupby(level=[0]).count()
# Add columns of zero
grid['count_unique'] = 0
grid['sum_times'] = 0
grid.loc[count_unique.index, 'count_unique'] = count_unique
grid.loc[total_times.index, 'sum_times'] = total_times