0

I would like to understand how to loop the following set of data access search and update routines.

How do I take the chunk of code below and just loop through it?

I need to iterate through the "Buffer Recipient Claims" feature class and :

  1. Select the first row in the table if the field "Allocation Credit Value" (AllocFrcstdCreVal) is greater than 15000
  2. Subtract the "Allocation Credit Value" from the "Recipient Forecasted Credit Value" (RecFrcstdCredVal) for that row.
  3. Identify this record as having had a credit allocation.
  4. Move on to the next row in the "Buffer Recipient Claims" feature class and repeat the process.

Straightforward enough so long as we have one Allocating Claim Number for one Recipient Claim Number.

The trick is that there are several Allocation Claim Numbers (which provide the Allocation Credit Value) for one Recipient Claim number. This is important because as I apply the credits from the Allocation Claim its value drops by the amount required by the Recipient Claim. If that Allocation Claim credit value drops below 15000 I need to be able to select the next available Allocating Claim in the table. I have already sorted the Allocating Claims in descending order of Credit value so that the next availble Allocating Claim value is always the highest avaialable (and I want to run the Allocating Claims down as close to 15000 before having to switch.).

As it stands now the code does pretty much what I want it to do albeit just on the first records selected by Recipient Claim Number.
I'm guessing I may need to create a "def" module or set a count to step through the records (neither of which I have yet done).

# ========================
# immediate mode
# prepare basic settings/parameters
import arcpy
from arcpy import env
env.workspace = "CURRENT"

# =====================================
# this works on one row and stops.  It needs to loop.

# these are the field values used for the two data sets.
# "RecipientBufferClaims" fields:
s_fieldList = ["RecClmNum","RecFrcstdCredVal","CorrRecCreVal","AllocClmNum","AllocFrcstdCreVal","CorrAllocCreVal","Sequence","AllocationStatus"]

# "TableAllocationClaims" fields:
u_field_list = ["AllocClmNum","CorrAllocCreVal","AllocFrcstdCreVal"]

# Search the first row of the "RecipientBufferClaims" FC for the required values 
# and put those values into memory.  Order the "RecipientBufferClaims" FC by their sequence number (sequence field).
# note : oddly enough I had to use "DESC" for the table to sort from row 1 upwadrds.
with arcpy.da.SearchCursor("RecipientBufferClaims",s_fieldList,sql_clause= (None,"ORDER BY Sequence DESC")) as srows:
    for srow in srows:
        # I think this is the way to place these fields in memory as variables assigned to the correlating "srow"s.
        V_RecClmNum = srow[0]
        V_RecCreVal = srow[1]
        V_CorrRecipientCreVal = srow[2]
        V_AllocatingClmNum = srow[3]
        V_AllocatingCreVal = srow[4]
        V_CorrAllocCreVal = srow[5]
        V_Sequence = srow[6]
        V_AllocationStat = srow[7]

# set the "where clause" to search in the "TableAllocationClaims" LUT for the right Allocating Claim number.  
    whereClause = ' "AllocClmNum" ' + " = '" + str(V_AllocatingClmNum) + " ' "

# set the "where clause" to search in the "TRecipientBufferClaims" feature class for the right Recipient Claim number. 
    whereClause2 = ' "RecClmNum" ' + " = '" + str(V_RecClmNum) + " ' "

    # Search through the "TableAllocationClaims" as per the "whereClause" and if the value is > than 1500
    # subtract the value from the Recipient Credit field from the Allocating Credit field.  
    # Use the RecipientCreditValue returned from the search routine above.
    with arcpy.da.SearchCursor("TableAllocationClaims",u_field_list, whereClause) as s2rows:
        for s2row in s2rows:
            V2_CorrAllocClm = s2row[0]
            V2_CorrAllocCreVal = s2row[1]
            V2_FrcstdCreVal = s2row[2]
            if V2_CorrAllocCreVal >= 15000:
                with arcpy.da.UpdateCursor("TableAllocationClaims",u_field_list,whereClause) as u3rows:
                    for u3row in u3rows:
                        u3row[1] = V2_CorrAllocCreVal - V_CorrRecipientCreVal
                        u3rows.updateRow(u3row)

                # updates the CorrAllocCreVal field in "RecipientBufferClaims" with the 
                # value returned above filtered as per the "whereClause".
                with arcpy.da.UpdateCursor("RecipientBufferClaims",s_fieldList,whereClause) as u4rows:
                    for u4row in u4rows:
                        u4row[5] = u3row[1]
                        u4rows.updateRow(u4row)

            # marks how the credit allocation was distributed.
            with arcpy.da.UpdateCursor("RecipientBufferClaims",s_fieldList,whereClause2) as u5rows:
                for u5row in u5rows:
                    if u5row[3] == V_AllocatingClmNum:
                        u5row[2] = 0
                        u5row[7] = "R_" + str(V_RecClmNum) + "_A_" + str(V_AllocatingClmNum) + "_Allocated"
                        u5rows.updateRow(u5row)
  • 3
    at every row in your search cursor you are then stepping through every row in the same table again, and then through another table two more times. So if there are 1000 records in your two tables, by the time the Search Cursor is finished it has potentially stepped through several million rows – Midavalo Aug 24 '16 at 18:25
  • 4
    It could be easier if you get out of the cursor mind-frame and instead use dictionaries and/or a class to track all the information you need. It would keep you from needing repeated/nested cursor iterations. However, that's a huge re-write. – Tom Aug 24 '16 at 19:24
  • 3
    I agree with @Tom that Python dictionaries (which I think are easier to learn about before Python classes) would provide immediate benefit here. There are now many examples that use them with ArcPy on this site, and many more examples of using them more generally at Stack Overflow. At the moment I think the amount of code presented exceeds what would be considered as a code snippet so I think you should try and abstract it into a series of test snippets that can be researched, tested and if necessary asked about separately. – PolyGeo Aug 24 '16 at 23:09
  • Thanks for your suggestions regarding the use of dictionaries as a solution. I will look at how I can integrate them. If they can be dynamic (ie a value associated to key can change) then I think they may be really useful. I'm going to guess that I can have arcpy build them automatically as there can be thousands of claim numbers. – Frederic Aug 25 '16 at 12:08
  • @ Tom. I'd like to upvote your comment if I could. I have indeed re-directed my approach using dictionairies. As per PolyGeo's suggestion I will work in smaller snippets and post those as questions if the need arises. – Frederic Aug 30 '16 at 12:08
1

Your code should work if you bring your second search cursor into your first for loop. At the moment it is outside the for loop.

Try this:

# ========================
# immediate mode
# prepare basic settings/parameters
import arcpy
from arcpy import env
env.workspace = "CURRENT"

# =====================================
# this works on one row and stops.  It needs to loop.

# these are the field values used for the two data sets.
# "RecipientBufferClaims" fields:
s_fieldList = ["RecClmNum","RecFrcstdCredVal","CorrRecCreVal","AllocClmNum","AllocFrcstdCreVal","CorrAllocCreVal","Sequence","AllocationStatus"]

# "TableAllocationClaims" fields:
u_field_list = ["AllocClmNum","CorrAllocCreVal","AllocFrcstdCreVal"]

# Search the first row of the "RecipientBufferClaims" FC for the required values
# and put those values into memory.  Order the "RecipientBufferClaims" FC by their sequence number (sequence field).
# note : oddly enough I had to use "DESC" for the table to sort from row 1 upwadrds.
with arcpy.da.SearchCursor("RecipientBufferClaims",s_fieldList,sql_clause= (None,"ORDER BY Sequence DESC")) as srows:
    for srow in srows:
        # I think this is the way to place these fields in memory as variables assigned to the correlating "srow"s.
        V_RecClmNum = srow[0]
        V_RecCreVal = srow[1]
        V_CorrRecipientCreVal = srow[2]
        V_AllocatingClmNum = srow[3]
        V_AllocatingCreVal = srow[4]
        V_CorrAllocCreVal = srow[5]
        V_Sequence = srow[6]
        V_AllocationStat = srow[7]

# set the "where clause" to search in the "TableAllocationClaims" LUT for the right Allocating Claim number.
        whereClause = ' "AllocClmNum" ' + " = '" + str(V_AllocatingClmNum) + " ' "

# set the "where clause" to search in the "TRecipientBufferClaims" feature class for the right Recipient Claim number.
        whereClause2 = ' "RecClmNum" ' + " = '" + str(V_RecClmNum) + " ' "

    # Search through the "TableAllocationClaims" as per the "whereClause" and if the value is > than 1500
    # subtract the value from the Recipient Credit field from the Allocating Credit field.
    # Use the RecipientCreditValue returned from the search routine above.
        with arcpy.da.SearchCursor("TableAllocationClaims",u_field_list, whereClause) as s2rows:
            for s2row in s2rows:
                V2_CorrAllocClm = s2row[0]
                V2_CorrAllocCreVal = s2row[1]
                V2_FrcstdCreVal = s2row[2]
                if V2_CorrAllocCreVal >= 15000:
                    with arcpy.da.UpdateCursor("TableAllocationClaims",u_field_list,whereClause) as u3rows:
                        for u3row in u3rows:
                            u3row[1] = V2_CorrAllocCreVal - V_CorrRecipientCreVal
                            u3rows.updateRow(u3row)

                    # updates the CorrAllocCreVal field in "RecipientBufferClaims" with the
                    # value returned above filtered as per the "whereClause".
                    with arcpy.da.UpdateCursor("RecipientBufferClaims",s_fieldList,whereClause) as u4rows:
                        for u4row in u4rows:
                            u4row[5] = u3row[1]
                            u4rows.updateRow(u4row)

                # marks how the credit allocation was distributed.
                with arcpy.da.UpdateCursor("RecipientBufferClaims",s_fieldList,whereClause2) as u5rows:
                    for u5row in u5rows:
                        if u5row[3] == V_AllocatingClmNum:
                            u5row[2] = 0
                            u5row[7] = "R_" + str(V_RecClmNum) + "_A_" + str(V_AllocatingClmNum) + "_Allocated"
                            u5rows.updateRow(u5row)

Please post a comment about your results if you try this. Thanks.

  • I tried your solution and while it does loop through the entire table (as it should) the result wasn't exactly I was looking for, however it did lead me to a few more ideas. I've started to reduce the number of redundant "update and search" cursors (as Midavalo noted) and am looking at using a "while" loop. I'll post when I get things sorted. – Frederic Aug 26 '16 at 0:14
  • While your solution does loop through the table it doesn't do so with the parameters I am looking for as noted in the original question. I'm going to re-think my approach probably by using dictionaries and re-post my question in smaller segments as suggested by Polygeo. I think my question wasn't very clear to start with, – Frederic Aug 31 '16 at 16:33

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.