I have written a Python snippet that reads lat and long stored in an Excel file. Converts them to a point which is then used to perform multiple geospatial analysis including buffer, intersection, and searching within a shapefile's attribute table (finding count of a specific attribute occurrence and a single row can have multiple attributes in the same column). This code snippet is very slow. I need to traverse around 2000 records and search for around 179 attributes (count) against 10 different radii. It takes around 2.45 minutes to traverse against 1 point for 10 radii and search for the count of only 10 attribute occurrences. Is there any way to speed up this process?

I am attaching the code below.

# importing libraries
from openpyxl import load_workbook
from shapely.geometry import Point
import pandas as pd
import geopandas as gpd
#import matplotlib.pyplot as plt
import csv

#loading input excel
book = load_workbook(r'File path of input file.xlsx')
sheet = book.active
bookOut = r'outputfile.csv'
#outputSheet = bookOut.active
#searching for a specific category and returning its count
def bSearch(lst, cat):
     c = 0
     for index, row in lst[0:len(lst)].iterrows():
        if cat in row['Category_t']:
            c = c+1
     return (c)
# creates buffer
def createbuf(p,r):
    bufp = p['geometry'].buffer(distance = r)
    buf = gpd.GeoDataFrame(geometry = bufp, crs = ucs.crs)
    return (buf)

# calculates intersection
def calint(b,u):
    areaoi = gpd.overlay(u, b, how= "intersection") 
    return (areaoi)

# shapefiles Input
ucs = gpd.read_file(r'Boundary.shp')
ucs = ucs.to_crs(epsg=32643)

#Business List
business = gpd.read_file(r'List of Categories.shp')
business = business.to_crs(epsg=32643)
businessArray = [
radiusVals=[0.5, 1, 1.5, 2, 2.5, 3.000, 4.000, 5.000, 7.000, 10.000]
# lat long
latlng = []
i = 0
otp = []
for row in range(143, sheet.max_row+1):
    detStore = []
    ind = 0
    for column in "AB":
        cell_name = "{}{}".format(column, row)
    for column in "CD":
        cell_name = "{}{}".format(column, row)
    # PCS SRID
    p1 = Point((latlng[i],latlng[i+1]))
    df = pd.DataFrame({'a':[latlng[i+1],latlng[i]]})
    po = gpd.GeoDataFrame(geometry = [p1], crs = ucs.crs)
    po['geometry'] = po['geometry'].to_crs(epsg = 32643)
    print(i,  "--", latlng[i+1], latlng[i])
    i = i+2
    # calling functions
    for r in range (0, len(radiusVals)):
        outputStore = []
        for d in range (0, len(detStore)):
        bufo = createbuf(po,(radiusVals[r]/111))
        aoi = calint(bufo ,ucs)
        busList = calint(bufo, business)
        for b in range (0, len(businessArray)):
            buSearch = bSearch (busList,businessArray[b])
print (otp)
fields = ['Location X', 'Location Y', 'Type', 'Name', 'Radius', 'Cat1','Cat2','Cat3','Cat4','Cat5','Cat6','Cat7','Cat8','Cat9','Cat10']
with open(bookOut, 'w',  newline='') as f:
    write = csv.writer (f)
  • Did you use a profiler to see where is / are the speed bottleneck(s) ? Look at github.com/rkern/line_profiler for example. Apr 14, 2021 at 20:44
  • 1
    This seems more like a task for Code Review
    – Vince
    Apr 14, 2021 at 20:49
  • I have added the question there too. thank you for the suggestion.
    – alauddin
    Apr 15, 2021 at 17:40
  • @J.Monticolo I have tried to find out the glitch. the search function takes the most time where I need to find out the count of a specific value in a column
    – alauddin
    Apr 15, 2021 at 17:41
  • Why don't you extract the pandas Series lst['Category_t'] and do a where filter, then c will be equal to the Series length IMHO ? (doc : pandas.pydata.org/docs/reference/api/pandas.Series.where.html) Apr 15, 2021 at 19:43


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.