Calculating sequential numbers into sorted table using ArcMap

Question

I am using ArcMap 10.3.

Is there a way to calculate a sorted field with sequential numbers in the Field Calculator or using an Update Cursor in ArcPy?

I have seen Sorting feature class to calculate sequential ID field using ArcGIS Field Calculator? that outlines how to calculate sequential numbers, but this is always calculated on FID order, not on sorted order.

#Pre-logic Script Code:
rec=0
def autoIncrement(): 
    global rec 
    pStart = 1  
    pInterval = 1 
    if (rec == 0):  
        rec = pStart  
    else:  
        rec += pInterval  
    return rec
    
#Expression:
autoIncrement()

An example of what I'm trying to do. I've used an advanced sort to sort by year, month, day, and now want to have sequential numbers in the Seq field. You'll see that my OBJECTID field is not in order, so the above code won't work.

Answer 1

"Solution" with 2 sorted fields (ascending):

mxd = arcpy.mapping.MapDocument("CURRENT")
lr=arcpy.mapping.ListLayers(mxd)[0]
tbl=arcpy.da.TableToNumPyArray(lr,("oid","A","B"))
bs=sorted(tbl, key=lambda x: (x[1], x[2]))
def sortSeq(fid,a,b):
 for i,ent in enumerate(bs):
   if ent[0]==fid: return i

--------------------------------------

sortSeq( !OID!, !A!, !B! )

UPDATED VERSION:

mxd = arcpy.mapping.MapDocument("CURRENT")
lr=arcpy.mapping.ListLayers(mxd)[0]
tbl=arcpy.da.TableToNumPyArray(lr,("oid","A","B"))
bs=sorted(tbl, key=lambda x: (x[1], x[2]))
aDict={}
for i,row in enumerate(bs):
 aDict[row[0]]=i
def sortSeq(fid):
 return aDict[fid]

-----------------------

sortSeq( !OID!)

takes 1.5 seconds to complete task on 10000 records. Original takes slightly more than 2 minutes

Answer 2

This is a two step process, and as a result, the Field Calculator is not well suited to it. It is better to run this in a standalone script. However, it can be done in the field calculator, provided you use a trick. You do need to use a cursor to load all of the values to a global dictionary from a sorted list, but only during the calculation of the first record. For all other records you have to skip the dictionary creation to avoid constantly rereading the entire table for each row.

The three field values have to be placed in a tuple to act as a key that will sort properly. I will assume all 3-field combination values are unique in the SamplePoint table, but I added the ObjectID to ensure it is unique. You have to provide the path and shapefile name in line 8 (or I could use the technique that FelixIP uses where the first layer in the current map is used). If you want to use different fields for a key you have to change the field list in line 10 and match them with the input fields in line 3 and line 15.

#Pre-logic Script Code:
relateDict = {}
def autoIncrement(myYear, myMonth, myDay, OID): 
    global relateDict  
    # only populate the dictionary if it has no keys  
    if len(relateDict) == 0:  
        # Provide the path to the relate feature class/table  
        relateFC = r"C:\Users\OWNER\Documents\ArcGIS\SamplePoints.shp"  
        # create a field list with the relate fields in sort order  
        relateFieldsList = ["Year", "Month", "Day", "OID@"]  
        # process a da search cursor to transfer the data to the dictionary  
        relateList = sorted([(r[0:]) for r in arcpy.da.SearchCursor(relateFC, relateFieldsList)])
        for relateSort in range(0, len(relateList)):
            relateDict[relateList[relateSort]] = relateSort + 1
    return relateDict[(myYear,myMonth,myDay,OID)]    

#Expression:
autoIncrement(!Year!, !Month!, !Day!, !OBJECTID!)

I also would not recommend using field names of Year, Month and Day, since those only work in shapefiles and are not allowed in geodatabases. A geodatabase will change the names to Year_1, Month_1, Day_1 if you try to add them to the field list in the properties of the table.

If the purpose of this table is to relate it to another table/feature class on a multi-field key, consider using the tool I created in my Blog named Multiple Field Key to Single Field Key Tool - Relate Two Layers Based on More than One Field

Answer 3

I had the same question but for a simpler problem, based on having only one Field to sort. I was successful with the following script:

# Pre-Logic Script Code:
# Specify that the target Map Document is the current one
mxd = arcpy.mapping.MapDocument("CURRENT")
# Specify that the target layer is the first layer in the table of 
# content
lr=arcpy.mapping.ListLayers(mxd)[0]

tbl=arcpy.da.TableToNumPyArray(lr,("fid","Name_of_sorted_Field"))
bs=sorted(tbl,key=lambda x: x[1])
aDict={}
for i,row in enumerate(bs):
 aDict[row[0]]=i
def sortSeq(fid):
 return aDict[fid]

---------------------------------------------------------------
# to run the code, the following goes in the expression window
sortSeq(!FID!)