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

1534
15
Jump to solution
09-08-2016 10:16 AM
FredericHyde
New Contributor II

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")

 

0 Kudos
1 Solution

Accepted Solutions
RichardFairhurst
MVP Honored Contributor

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

       

View solution in original post

15 Replies
JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos
FredericHyde
New Contributor II

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

0 Kudos
NeilAyres
MVP Frequent Contributor

What this line supposed to be doing :

NewAllocCredVal = upRow[1] = upRow[1] - KeyRecipientCredit
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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
FredericHyde
New Contributor II

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.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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."

RichardFairhurst
MVP Honored Contributor

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.

FredericHyde
New Contributor II

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)     
0 Kudos