Python Sequential Numbering

[DavidForbuss1]

Hi everyone!

I'm using the sequential numbering script in the field calculator, and I'm trying to modify it to iterate through field values from another field so that the sequence starts over for each of the unique other values.  My table looks like this -->>  I'm trying to generate a Name with a Basin ID as the prefix.  For now, I've been just manually selecting the basins and running the script for each one, but I'd like to be able to have the script run through them all at once:

Script I'm using:

SequentialNumber()

# Calculates a sequential number
# More calculator examples at esriurl.com/CalculatorExamples
rec=0
def SequentialNumber():
global rec
pStart = 1
pInterval = 1
if (rec == 0):
rec = pStart
else:
rec = rec + pInterval
return "WA1MH"+str(rec).zfill(4)

I'm pretty rusty on python...any help would be appreciated!

Thanks,

David Forbuss

GIS Technician

Rogue Valley Sewer Services

P O Box 3130

Central Point OR 97502

541-664-6300

dforbuss@rvss.us

[JoeBorgione]

To go through the individual records you are going to need an update cursor.

I'm not sure why you are using a global variable designator.  There really is no need to and there a lot of pythonistas that frown upon their use.

[DavidForbuss1]

I just used the default script that populates when you click the helper...the global is built into it.

[JoeBorgione]

You can use that for a start and build it into a cursor.  Otherwise you'll be stuck running it iteratively by hand.

Assuming you have several different prefixes, you'll probably need to work through a list of them, and plug that value as your prefix and then concatenate the number you want. It's late friday afternoon and I've got my eye on a cold adult beverage.  Do some hacking and if you need a hand, post it up on monday.

[DavidForbuss1]

I was thinking more along the lines of defining a variable like:

basins=!Basin_ID!

Then just building that into the return portion:

return str(basins)+"MH"+str(rec).zfill(4)

I keep getting syntax errors though.  

[JoshuaBixby]

It is always helpful to paste the exact error and traceback rather than just saying "getting syntax errors."  Does your syntax error look like this?

>>> basins=!Basin_ID!
  File "<stdin>", line 1
    basins=!Basin_ID!
           ^
SyntaxError: invalid syntax
>>> 

You can't assign a column value using the Esri's exclamation point syntax within the code block, it has to be in the field expression and passed to a parameter defined for the function.

I suggest you read through Calculate Field Python examples—ArcGIS Pro | Documentation, it has many examples, and should give you a better idea of how to pass table values to Python functions.

[JoeBorgione]

I'm not a fan of using the plus sign for concatenating strings, and really prefer the f string method

Here is an example:

rec = 0

for i in range(1,10):
    print(f'MH{str(i).zfill(4)}')
    

Without seeing your entire code block, it's hard saying what the syntax error might be.  Can you include a sample of your data in csv form?

[DavidForbuss1]

Here's my data in csv format.  I've started filling in some of the descriptions by manually selecting the basins and running the sequential script, but it's likely that the basin names will change in the future, so that's my motivation to get a script that can be run again to repop to the description column.

[JoeBorgione · Accepted solution]

Here is how I ended up doing it with an update cursor:

import arcpy

table = r'C:\GIS\Forbuss\Forbuss.gdb\ForbussDataTable'
nameTable = r'C:\GIS\Forbuss\Forbuss.gdb\ForbussDataTable_Statistics'
nameList = [r[0] for r in  arcpy.da.SearchCursor(nameTable,'basin_name','basin_name IS NOT NULL') ]

arcpy.management.MakeTableView(table,'tableView')

for name in nameList:
    select = f"basin_name = '{name}'"
    fields = ['description','basin_name']
    with arcpy.da.UpdateCursor('tableView',fields,select) as cursor:
        rec = 0
        for row in cursor:
            rec+=1
            row[0] = (f'{row[1]}{str(rec).zfill(4)}')
            cursor.updateRow(row)

Lines 3,4 & 5 set the stage; I converted your csv into a fgdb table, and then using list comprehension and a search cursor, created a list of basin names (where the basin name is not null).  Then in Line 7 I turn the original table into a table view since I'm running this as a stand alone script.  Finally at row 9 I iterate through the list of basin names and with an update cursor update the 'description' field.

[DavidForbuss1]

Joe -- You're not allowed to retire...that worked!!  Thank you so much!