7

I have three nested SearchCursors. It works correctly but with slow performance. There are some solutions to improve the searchcursor performance like dictionary but i don't know how to use it with conditional statements. The code is as below.How to get rid of nested loops and improve performance?

with arcpy.da.SearchCursor(parameters[1].valueAsText,["Station","Code"],where_clause = sql_exp1) as cur:
    for row in cur:
        if  row[0] != None:
            with s arcpy.da.SearchCursor(parameters[2].valueAsText,["Code","Name","Code1"]) as curt:
                for rowt in curt:
                    if rowt[0] == row[1]:
                        with  arcpy.da.SearchCursor(parameters[0].valueAsText,["Name","Origin","Destination","Code1"]) as curl:
                            for rowl in curl:
                                if rowl[0] == rowt[1] and rowl[3] == rowt[2]:
                                    listnumber.append(rowl[0])
                                    linecodeFinal.append(rowl[3])
            codelinestr = ','.join(str(b) for b in linecodeFinal)
  • Another option would be pandas. FC/table-> (numpy array if desired) -> pandas dataframe -> SQL-like table join. – Paul Dec 4 '16 at 1:19
  • I have checked this post and the fabulous blog community.esri.com/blogs/richard_fairhurst/2014/11/08/… I am newbie in python and cannot figure out how to embed a numpy function within a da.SearchCursor. I am trying to replace an arcpy Field Calculation, with a codeblock and expression. The idea is I need to perform a calculation based on the mean of the rows that have the same value in the field "code_12". Currently my code scratch is: import os import arcpy import numpy folderPath = r"path" arcpy.env.workspace=folderP – betel77 Aug 23 '18 at 11:50
10

I have to keep posting that I will never use embedded search cursors ever for relating tables to each other. Only dictionaries should be used to do what you are doing. Performance will increase 100 fold over a 3 level set of embedded search cursors, since dictionaries use random access from data stored in memory which is immediate, verses cursor's and sql that access the table linearly by repeated reading the data from disk which is incredibly sloooow. See my Blog on Turbo Charging Data Manipulation with Python Cursors and Dictionaries

Load each of the embedded cursors to their own dictionary using the related field as the key value using a style shown below that add all other fields into a list for the values. Then just run a cursor on the master data source and look up the key value in the first related table. Use a for loop on the first related table's value list to get the look up for the second level table key and then append the returned value of that table to the list you are building.

The conditional statements are virtually identical to what you have in your code and you should not think of dictionary logic any differently. The examples in my blog already show this kind of conditional logic. It occurs in the line that reads: if keyValue in valueDict:

Anyway, the logic of your code is less obvious than the logic of the dictionary code I use. I have tried to equate what you have done to how it should be done using dictionaries, but I may not have kept track of the list for loops that I needed to build. I made the Name Code1 value key of the two embedded cursors a tuple dictionary key for the most embedded level to make the matching easier. Not sure why you have origin and destination at that level, since they are not used. The second dictionary is basically using (Name, Code1) key as the equivalent of a multi-field composite key in a table relationship (something my blog discussed in general terms, but did not show in an example).

I have revised the code to use Paul's suggestion of using the defaultdict(list). The code has not been tested so some debugging is still most likely needed, but it illustrates the overall approach.

from time import strftime  
  
print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")  
  
import arcpy  
  
sourceFC1 = parameters[2].valueAsText  
  
sourceFieldsList1 = ["Code","Name","Code1"] 
  
valueDict1 = defaultdict(list)  
with arcpy.da.SearchCursor(sourceFC1, sourceFieldsList1) as searchRows:  
    for searchRow in searchRows:  
        keyValue = searchRow[0]  
        valueDict1[keyValue].append((searchRow[1], searchRow[2]))  
                        
sourceFC2 = parameters[0].valueAsText  
  
sourceFieldsList2 = ["Name","Code1","Origin","Destination"] 
  
valueDict2 = defaultdict(list)  
with arcpy.da.SearchCursor(sourceFC2, sourceFieldsList2) as searchRows:  
    for searchRow in searchRows:  
        keyValue = (searchRow[0], searchRow[1])
        valueDict2[keyValue].append(searchRow[2:])
  
updateFC = parameters[1].valueAsText 
  
updateFieldsList = ["Station","Code"]  
  
with arcpy.da.UpdateCursor(updateFC, updateFieldsList, where_clause = sql_exp1) as updateRows:  
    for updateRow in updateRows:  
        if updateRow[0] != None:
        # store the Join value of the row being updated in a keyValue1 variable  
            keyValue1 = updateRow[1]  
            # verify that the keyValue1 is in the Dictionary  
            if keyValue1 in valueDict1:  
                # transfer the values stored under the keyValue1 from the dictionary to the updated fields.  
                for keyValue2 in valueDict1[keyValue1]: 
                    print(keyValue2)
                    if keyValue2 in valueDict2: 
                        listnumber.append(keyValue2[0])
                        listcodeFinal.append(keyValue2[1])
            codelinestr = ','.join(str(b) for b in linecodeFinal)
del valueDict1
del valueDict2  
  
print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")  
print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")
  • defaultdict is perfect for this. No need to check for key values at all just simply valueDict2 = defaultdict(list).....valueDict2[keyValue].append(searchRow[2:]). +1 since dictionaries are awesome. – Paul Dec 4 '16 at 1:11
  • @Richard Thank you. To other users that have same problem: using the Richard approach is the best solution to improve performance. I tested The code that richard posted as answer.It's work without any errors – BBG_GIS Dec 4 '16 at 10:23

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.