Select to view content in your preferred language

Sequential numbers

486
3
10-03-2023 06:42 AM
GaryRowan
New Contributor II

Hi there. Need some help. My query is essentially this, How do i apply sequential number, with prefix, and which is displayed in chronological order to match attributes of an other field?

I am half way there. its just the last bit. .....

I have a layer with multiple fields. These include for example; 'Mapref' and 'Type' fields.  The Mapref is simply to display a map reference number on the map. The Type distinguishes between several individual landuse types (eg 'openspace' 'residential' facilities'...  assigned to each feature). 

I arranged the Type data in ascending order and I have successfully applied a sequential number to each feature under the Mapref field with a prefix, for example "BL1", BL2", "BL3" etc using 'Calculate Field'.  All good so far.  However, in the attribute table (which i intend to export for reporting) that sequential numbering jumps around between 'types'.  For example,  i might have 'openspace' being displayed in the Mapref field as BL1, BL2, ..then BL63.  Can i align the numbering in the Mapref so all the openspace is on numerical order, then all the other types are in numerical order after that? 

baffled. Thanks

0 Kudos
3 Replies
JohannesLindner
MVP Frequent Contributor

Run this in the Python Window (edit the first 5 lines to reflect your data)

fc = "TestPoints"  # layer name of path to feature class
type_field = "TextField1"
sequence_field = "TextField2"
order_field = "OBJECTID"
prefixes = {"Type A": "A", "Type B": "B", "Type C": "C"}

for t in prefixes:
    i = 1
    with arcpy.da.UpdateCursor(fc, [sequence_field], where_clause=f"{type_field} = '{t}'", sql_clause=[None, f"ORDER BY {order_field}"]) as cursor:
        for row in cursor:
            cursor.updateRow([f"{prefixes[t]}{i}"])
            i += 1

 

JohannesLindner_0-1696352590315.png

 


Have a great day!
Johannes
0 Kudos
GaryRowan
New Contributor II

@JohannesLindner thank you very much for your time on this and the suggested code.  However, i wonder did i over complicate my query.  Extract of my data below. you will see that the "Church" features do not run sequentially in the "Mapref" order.  Data in rows 15 and 15 (both Churches) goes "B66" then "B72".  I have executed a sequential order of the Mapref column.  I just wonder do i need to tweak the code so that all the 'Churches' are in numerical order i,e, "B66" then "B67"?  (i did ensure that the 'Type' column was arranged in alphabetical order before running the sequential numbering in mapref).  My sequential code is below.

GaryRowan_1-1696356595458.png

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

0 Kudos
JohannesLindner
MVP Frequent Contributor

Calculate Field does not care about how you sort your rows, it will iterate through the table by ObjectID.

It might be possible to change that order in the code block, but it's much easier to just do it in pure Python. Calculate Field is nice for quick and simple calculations, but it is very limited.

 

Use this expression in the Python Window:

fc = "TestPoints"
type_field = "TextField1"
sequence_field = "TextField2"
prefix = "B"

types = {row[0] for row in arcpy.da.SearchCursor(fc, [type_field])}
i = 1
for t in sorted(types):
    with arcpy.da.UpdateCursor(fc, [sequence_field], f"{type_field} = '{t}'") as cursor:
        for row in cursor:
            cursor.updateRow([f"{prefix}{i}"])
            i += 1

Have a great day!
Johannes
0 Kudos