0

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 = [
'Cat1','Cat2','Cat3','Cat4','Cat5','Cat6','Cat7','Cat8','Cat9','Cat10'
]
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)
        latlng.append(sheet[cell_name].value)
        detStore.append(sheet[cell_name].value)
    for column in "CD":
        cell_name = "{}{}".format(column, row)
        detStore.append(sheet[cell_name].value)
    # 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)):
            outputStore.append(detStore[d])
        outputStore.append(radiusVals[r])
        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])
            outputStore.append(buSearch)
        otp.append(outputStore)
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)
    write.writerow(fields)
    write.writerows(otp)
7
  • Did you use a profiler to see where is / are the speed bottleneck(s) ? Look at github.com/rkern/line_profiler for example. Commented Apr 14, 2021 at 20:44
  • 1
    This seems more like a task for Code Review
    – Vince
    Commented Apr 14, 2021 at 20:49
  • I have added the question there too. thank you for the suggestion.
    – alauddin
    Commented 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
    Commented 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) Commented Apr 15, 2021 at 19:43

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.