topic Re: Is it possible to iterate through a set of update cursors with the next() parameter? in Python Questions

Is it possible to iterate through a set of update cursors with the next() parameter?

FredericHyde — Sat, 11 Dec 2021 15:16:03 GMT

I have a feature class that is the result of a spatial join between a group of Credit_Recipient (RecClmNum) and Credit_Allocation (AllocClmNum) land parcels. The join essentially associates Credit_Allocation parcels that are within a given buffer of the Credit_Recipient parcels. This means I may have 1, 2, or X number of Credit_Allocation parcels associated to a single Credit_Recipient parcel. I have created a new table into which I want to add each Credit_Recipient parcel number, the credit value (CorrRecCreVal) it needs to receive, and the Credit_Allocation parcel number from which it will receieve the value. The calculation for the credit allocation is a straight subtraction of the required credit value CorrRecCreVal) from the available credit (CorrAllocCreVal) in the allocation parcel. The code below does what I want but only on the first row (and only if I use the "next()" parameter). I need the script to repeat the code from the "SearchCursor.da" point. If I use the "for row in cursor" option it doesn't do the correct calculation (basically it does do it, but regardless of whether the Credit_Recipient has or hasn't received an allocation).

sourceFC = "UpdatedRecipientClaims"
sourceFieldsList = ["RecClmNum","TransferredValue"]
# use list comprehension to build a dictionary from a da SearchCursor.
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC,sourceFieldsList)}

sourceFC2 = "ActualTbleCopy2SortedAllocationClaims"
sourceFieldsList2 = ["AllocClmNum","CorrAllocCreVal"]
# Use list comprehension to build a dictionary from a da SearchCursor
valueDict2 = {r2[0]:(r2[1:]) for r2 in arcpy.da.SearchCursor(sourceFC2,sourceFieldsList2)} 

SearchFC = "RecipientBufferClaims"
SearchFieldsList = ["RecClmNum","CorrRecCreVal","AllocClmNum"]

with arcpy.da.SearchCursor(SearchFC,SearchFieldsList) as srchCursor:
    srchRow = srchCursor.next()
    #for srchRow in srchCursor:
    # store the Join value SearchCursor in the Key... variable
    KeyRecipientClaim = srchRow[0]
    KeyRecipientCredit = srchRow[1]
    KeyAllocationClaim = srchRow[2]
    # store the field values in a variable for the insert cusor
    rowValues = [(srchRow[0],srchRow[1])]
    # if the recipient claim (KeyRecipientClaim) is not in the returned variable
    # rowValues, then insert it into the table. If I use the "for srchRow in
    # srchCursor:" instead of "srchRow = srchCursor.next()" it calculates on all the
    # RecipientClaims regardless of whether they have received a credit or not.
    if KeyRecipientClaim not in sourceFC:
        insrtCurs = arcpy.da.InsertCursor(sourceFC,sourceFieldsList)
        for row in rowValues:
            insrtCurs.insertRow(row)
            del insrtCurs
            del row
            query = """ "AllocClmNum" = '%s'"""%KeyAllocationClaim
            arcpy.SelectLayerByAttribute_management(sourceFC2,"NEW_SELECTION",query)
            upCurs = arcpy.da.UpdateCursor(sourceFC2,sourceFieldsList2)
            for upRow in upCurs:
                if upRow[1] >= 15000:
                    NewAllocCredVal = upRow[1] = upRow[1] - KeyRecipientCredit
                    if NewAllocCredVal >= 15000:
                        upCurs.updateRow(upRow)
                        del upCurs
                        del upRow
                        arcpy.SelectLayerByAttribute_management(sourceFC2,"CLEAR_SELECTION")
                        query2 = """ "RecClmNum" = '%s'"""%KeyRecipientClaim 
                        arcpy.SelectLayerByAttribute_management(sourceFC,"NEW_SELECTION",query2)
                        arcpy.CalculateField_management(sourceFC,"AllocClmNum",'KeyAllocationClaim',"PYTHON_9.3")
                        arcpy.SelectLayerByAttribute_management(sourceFC,"CLEAR_SELECTION")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

JoshuaBixby — Thu, 08 Sep 2016 17:23:31 GMT

"Code below"? I see an attached screenshot of a table but no code.

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

FredericHyde — Thu, 08 Sep 2016 17:29:47 GMT

my mistake. I've updated the question. Thanks.

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

NeilAyres — Thu, 08 Sep 2016 19:18:59 GMT

What this line supposed to be doing :

NewAllocCredVal = upRow[1] = upRow[1] - KeyRecipientCredit

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

DanPatterson_Retired — Sat, 11 Dec 2021 15:16:06 GMT

double assignment, I hope... upRow[1] is updated from old upRow[1] - KeyRecipientCredit, then assigned again to variable NewAllocCredVal

>>> a = 5
>>> fac = 2
>>> b = a = a-fac
>>> a
3
>>> b
3
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍# next loop
>>> b = a = a-fac
>>> a
1
>>> b
1‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

FredericHyde — Thu, 08 Sep 2016 20:33:48 GMT

Indeed that was the idea. But I know the whole thing is a bit of a mess. I'm re-working the code in smaller more manageble snippets. Basically as I update a new row I have to ensure that not only am I using the highest Allocation credit value assigned to the Credit Recipient (as there are often more than one) but that the Credit Recipient is'nt used again (on the next iteration/row), or alternatively that if my Allocation Credit is used (ie below 15000) that my iteration passes over that record to update using the next available Allocation Credit. Not sure if that clarifies anything.

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

DanPatterson_Retired — Thu, 08 Sep 2016 20:41:50 GMT

I would be careful that you aren't just making references to an object rather than copying the object. I think you are fine in this case, but the double-assignment can be a bit dicey for other data structures.

I would definitely clean up the 'if' statements, since you don't provide a direct 'conclusion'/alternate action when the if statement is false. You are relying on the looping to continue the operations, but being explicit in what you want to happen should the condition fail would be good, if not only for keeping track of processes.

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

JoshuaBixby — Thu, 08 Sep 2016 21:52:03 GMT

Definitely re-post the code after you have re-worked it. Any looping logic can be implemented using either for or while statements, one usually just looks cleaner than the other. You should be able to get your for loops to work. Also, regarding your comments on lines #03 and #08, the code isn't using a "list comprehension to build a dictionary," the code is using a dictionary comprehension to build a dictionary. If you want to generalize the statement, you can just go with "comprehension."

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

RichardFairhurst — Thu, 08 Sep 2016 22:26:17 GMT

I would look at dictionaries for all matching of tables and not use any embedded cursors or do any selectLayerByAttribute operations inside cursor loops which are slow. That approach will literally speed your code up by at least 10 times for each time you replace an embed a cursor or replace a SelectLayerByAttribute query in a cursor loop. I never use embedded search or update cursors or selectLayerByAttribute within a cursor loop anymore. All data record comparisons, selections and transfers between any two tables use a dictionary between the tables in my code. See my Blog on Turbo Charging Data Manipulation with Python Cursors and Dictionaries for the basics of the method.

The code below is not going to read through your sourceFC table:

if KeyRecipientClaim not in sourceFC:

that code translates to:

if KeyRecipientClaim not in "UpdatedRecipientClaims":

which ought to cause an error.

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

FredericHyde — Sat, 11 Dec 2021 15:16:09 GMT

Thanks for all your ideas. I have simplified (I think) the script requirements. I am using a dictionary to access my "allocation credit values" that need to update as they are used by the "recipient claims". The CorrAllocCreVal field needs to self adjust as it applies a value to the recipient claims in the other table (see the image below). For example: "Claim Number" 1014397 of the SortedAllocationClaim table (on the left) has a credit value of 5253078.82. This value needs to be distributed to the following four "Recipient Claim Numbers" in the AllocationClaimsSPJ table (on the right) : 1010284 (needs 9520.36 in credit), 1010306 (needs 9520.36 in credit), 1010328(needs 9520.37 in credit), 1010285 (needs 7720.36 in credit). Even though I am accessing the dictionary value for the Allocation Credit Value (Dict_AllocClmsSpj ) the script updates only based on the last AllocCreVal field only based on the last value in the based on the last value of the group associated the claim number .

# set your variables for the Allocation Claims feature class
updateFC_AllocClms = "SortedAllocationClaims"
fields_AllocClms = ["AllocClmNum","CorrAllocCreVal"]

# set your variables for the feature class with the joined Allocation Claims and Recipient Claims (spatial)
sourceFC_AllocClmsSpj = "AllocationClaimsSPJ"
fields_AllocClmsSpj = ["OBJECTID","AllocClmNum","RecClmNum","RecCreVal"]
# set the dictionary
Dict_AllocClmsSpj = {r[1]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC_AllocClmsSpj,fields_AllocClmsSpj)} 

# run the script

# update the Sorted Allocation Claims FC with the new values for Allocation Credits ("CorrAllocCreVal") 
with arcpy.da.UpdateCursor(updateFC_AllocClms, fields_AllocClms) as upRows:
     for upRow in upRows:
          KeyAllocationClaim = upRow[0] # key value is AllocClmNum
          if KeyAllocationClaim in Dict_AllocClmsSpj: # if the KeyAllocationClaim is in the dictionary
               if upRow[1] >= 15000: # and as long as credit value is >= to 15000
                    upRow[1] = upRow[1] - Dict_AllocClmsSpj[KeyAllocationClaim][2] # subtract the Allocation Credit Value from the Recipient Credit Value in the  Dict_AllocClmsSpj
                    upRows.updateRow(upRow)     ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

RichardFairhurst — Sat, 11 Dec 2021 15:16:12 GMT

The way you have set up the dictionary it is going to overwrite all records read except the last record, as you have observed. This is expected behavior. To deal with 1:M relationships you have to determine if the values are a summary value like a sum or average, or if you want a list of values to iterate from the dictionary. So the example you should be looking at the "Using a Python Dictionary Built using a da SearchCursor to Replace a Summary Statistics Output Table" example in my Blog for an example of how to load a dictionary for a sum and count that can also find the average of the records.

It sounds like you want a list of values to iterate so that you can draw down each until you reach your minimum value. So another for loop would be involved. Some untested code below shows the basic approach:

import arcpy

# set your variables for the Allocation Claims feature class
updateFC_AllocClms = "SortedAllocationClaims"
fields_AllocClms = ["AllocClmNum","CorrAllocCreVal"]
# set your variables for the feature class with the joined Allocation Claims and Recipient Claims (spatial)
sourceFC_AllocClmsSpj = "AllocationClaimsSPJ"
fields_AllocClmsSpj = ["OBJECTID","AllocClmNum","RecClmNum","RecCreVal"]
# set the dictionary
Dict_AllocClmsSpj = {}
valueDict = {} 
with arcpy.da.SearchCursor(sourceFC_AllocClmsSpj,fields_AllocClmsSpj) as searchRows: 
   for searchRow in searchRows: 
      keyValue = searchRow[1] 
      if not keyValue in Dict_AllocClmsSpj: 
         # assign a new keyValue entry to the dictionary storing a list 
         Dict_AllocClmsSpj[keyValue] = [searchRow[1:]] 
      else:
         # dictionary keyValue exists so append to the list
         Dict_AllocClmsSpj[keyValue].append( searchRow[1:])


# update the Sorted Allocation Claims FC with the new values for Allocation Credits ("CorrAllocCreVal") 
with arcpy.da.UpdateCursor(updateFC_AllocClms, fields_AllocClms) as upRows:
   for upRow in upRows:
      KeyAllocationClaim = upRow[0] # key value is AllocClmNum
      if KeyAllocationClaim in Dict_AllocClmsSpj: # if the KeyAllocationClaim is in the dictionary
         for record in Dict_AllocClmsSpj[KeyAllocationClaim]:
            if upRow[1] >= 15000: # and as long as credit value is >= to 15000
               upRow[1] = upRow[1] - record[2] # subtract the Allocation Credit Value from the Recipient Credit Value in the Dict_AllocClmsSpj
               upRows.updateRow(upRow)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

FredericHyde — Sat, 11 Dec 2021 15:16:15 GMT

Thankyou Richard. That does exactly what I had asked for. I have slightly changed the

"UpdateCursor(updateFC_AllocClms, fields_AllocClms)" section to check for the minimum value :

with arcpy.da.UpdateCursor(updateFC_AllocClms, fields_AllocClms) as upRows:
     for upRow in upRows:
          KeyAllocationClaim = upRow[0] # key value is AllocClmNum
          if KeyAllocationClaim in Dict_AllocClmsSpj: # if the KeyAllocationClaim is in the dictionary
               for record in Dict_AllocClmsSpj[KeyAllocationClaim]:
                    if (upRow[1] - record[2]) >= 15000: # and as long as credit value is >= to 15000
                         upRow[1] = upRow[1] - record[2] # subtract the Allocation Credit Value from the Recipient Credit Value in the  Dict_AllocClmsSpj
                         upRows.updateRow(upRow)     ‍‍‍‍‍‍‍‍

Do you think you can provide me with some clarification on the process. I'm not clear on what is happening in the dictionary building syntax you suggested. I've added my questions as comments in the code below.

with arcpy.da.SearchCursor(sourceFC_AllocClmsSpj,fields_AllocClmsSpj) as srchRows: 
     for srchRow in srchRows:
          # This seems straight forward enough : search through the feature class and assign the field "AllocClmNum" as a key/join field.
          # this is the field of common values that will be used to link the two tables.
          keyValue = srchRow[1] 
          
          # Question : what is being appended at the "else" statement?  
          # I read the following line as "if the actual value from 'AllocClmNum' (eg 1014414) is NOT in the dictionary, then assign 1014414 as a new keyValue"
          if not keyValue in Dict_AllocClmsSpj: 
               Dict_AllocClmsSpj[keyValue] = [srchRow[1:]]
          
          # but here I read the 'else' statement as "if the actual value from 'AllocClmNum' (eg 1014414) IS in the the dictionary, then append it to the dictionary" 
          # Haven't we done that already in the previous step?.  I can't quite get my head around the logic.
          else: 
               # dictionary keyValue exists so append to the list
               Dict_AllocClmsSpj[keyValue].append( srchRow[1:])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Many thanks.

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

RichardFairhurst — Tue, 13 Sep 2016 16:25:15 GMT

Your reading of the first part of the dictionary assignment when a new key value is encountered in not fully correct. You should read it as "if the actual value from 'AllocClmNum' (eg 1014397) is NOT in the dictionary, then assign 1014397 as a new keyValue that points to a list containing the list of field values associated with the record being read."

The answer to your question is that for each additional record associated with an established keyValue I am appending to the list containing lists of field values the next list of field values for the current record. So when the key 1014397 is first assigned to the dictionary that key points to this value:

[[1014397,1010284,9520.36]]

The next time key 1014397 is found it appends to the outer list the list of field values for the new record. So [1014397,1010306,9520.36] is appended to [[1014397,1010284,9520.36]] to make it [[1014397,1010284,9520.36],[1014397,1010306,9520.36]]. When all 4 records are read the keyValue 1014397 will reference the following list of lists: [[1014397,1010284,9520.36],[1014397,1010306,9520.36],[1014397,1010328,9520.37],[1014397,1010285,7720.36]]

The list of lists is iterated to retrieve each list of 3 field values separately and then you are accessing the last field value in that list during each cycle of the loop.

You can append to the inner lists as well, so you could add to each field record an initial flag value of False. Then when you iterate the list of lists as each record meets your criteria for allocation you can set the Flag to True. Then the list of lists would keep track of the records that actually altered your value and you would also know which records were skipped which did not alter your value, which you could use to assign a value back to the UpdatedRecipientClaims or append to a set of new tables that would show the transactions that occurred and the claims that were not processed.

Note: It is possible that rather than a list of field values you would get a tuple of field values from the table. If that is the case then to append to the list of field values you would first have to cast the tuple to a list, i.e., mylist = list(tuple).

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

FredericHyde — Tue, 13 Sep 2016 17:32:04 GMT

Thanks for the clarification, very helpful. I'm very interested in the last part of your message regarding keeping track of the records as they are altered. That's exactly what the final output of this project is. How would I flag each field record as False and set to True? (stop me if I am abusing of your knowledge...I was going to start a new post on how to do just that.)

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

RichardFairhurst — Sat, 11 Dec 2021 15:16:18 GMT

See if hte code below make sense to you. It is untested.

with arcpy.da.SearchCursor(sourceFC_AllocClmsSpj,fields_AllocClmsSpj) as searchRows: 
   for searchRow in searchRows: 
      keyValue = searchRow[1] 
      if not keyValue in Dict_AllocClmsSpj:
         lstRow = searchRow[1:]
         lstRow.append(False) 
         # assign a new keyValue entry to the dictionary storing a list 
         Dict_AllocClmsSpj[keyValue] = [lstRow] 
      else:
         lstRow = searchRow[1:]
         lstRow.append(False) 
         # dictionary keyValue exists so append to the list
         Dict_AllocClmsSpj[keyValue].append(lstRow)


...

    Record[3] = True‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Is it possible to iterate through a set of update cursors with the next() parameter?

FredericHyde — Fri, 16 Sep 2016 13:32:29 GMT

Thanks again Richard. That helps. I have posted a new follow up question for my next steps. Hope to hear from you again!