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

[FredericHyde]

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

[RichardFairhurst · Accepted solution]

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

[FredericHyde]

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.

[RichardFairhurst]

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

[FredericHyde]

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

[RichardFairhurst]

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

[FredericHyde]

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