How to use SequentialNumber in For Loop

[ChrisGAEG]

I am trying to apply Sequential numbering in groups via a for loop. The loop should select a group of rows, apply sequential number to just those rows in the group, then move on to the next selected group. Here's the code I was trying to use in the Python Window in Pro.

for cable in myValues:
    arcpy.management.SelectLayerByAttribute(fiber, "NEW_SELECTION", "cable_name = '%s'"% cable, None)
    arcpy.management.CalculateField(fiber, "SegmentID", "SequentialNumber()", "PYTHON3", 
    rec=0
    def SequentialNumber():
        global rec
        pStart = 1
        pInterval = 1
        if (rec == 0):
            rec = pStart
        else:
            rec = rec + pInterval
        return rec
,'TEXT')

The process works manually, but I'm not sure what I'm missing. I think the issue is formatting the code block of the SequentialNumber function within the Calculate Attributes run inside the for loop. Any help on this is greatly appreciated! 

[JoeBorgione]

The loop should select a group of rows....  then move on to the next selected group

What is your selection criteria?  I don't see it in your code sample.

The process works manually...

And what doesn't work?  Are there errors?

[ChrisGAEG · Accepted solution]

Hi Joe, 

Thanks for responding. I needed to properly quote the code snip into the calculate field call. I used triple quotes as shown below to get the code to work. 

for cable in myValues:
    arcpy.management.SelectLayerByAttribute(fiber, "NEW_SELECTION", "cable_name = '%s'"                         % cable, None)
    arcpy.management.CalculateField(fiber, "SegmentID", "SequentialNumber()", "PYTHON3", """
rec=0 
def SequentialNumber():
    global rec
    pStart = 1
    pInterval = 1
    if (rec == 0):
        rec = pStart
    else:
        rec = rec + pInterval
    return rec """
,'TEXT')

Cheers!

[DanPatterson]

maybe

code_block = """
rec=0
def SequentialNumber():
    global rec
    pStart = 1
    pInterval = 1
    if (rec == 0):
        rec = pStart
    else:
        rec = rec + pInterval
    return rec
"""
for cable in myValues:
    arcpy.management.SelectLayerByAttribute(fiber, "NEW_SELECTION", "cable_name = '%s'"% cable, None)

    arcpy.management.CalculateField(fiber, "SegmentID", "SequentialNumber()", "PYTHON3", code_block, 'TEXT')

[ChrisGAEG]

Thank you Dan, this is another good option. I had one follow up question about this code. Is there a way to reference a value in the table as a variable and assign pStart to that value? For instance if the value in the first cell in a group of 5 rows was 5, the code would enumerate from 5 - 9 for that group. 

[DanPatterson]

Chris, you would be better off using search and update cursors.  Calculate field is a shortcut to these and for small/simple expressions.

I would normally use numpy and group entries accordingly, then do the sequential entries from there.  arcpy's ExtendTable is then used to bring the resultant column back into the featureclass table.

Panda' (groupby) has similar functionality

[ChrisGAEG]

Dan, 

Thanks for the advice. I ended up using Pandas. 

[BlakeTerhune]

What about using enumerate() to get sequential numbers in a loop.

for enum, cable in enumerate(myValues):
    arcpy.management.SelectLayerByAttribute(fiber, "NEW_SELECTION", f"cable_name = '{cable}'", None)
    arcpy.management.CalculateField(
        in_table=fiber,
        field="SegmentID",
        expression=enum,
        expression_type="PYTHON3"
    )