11

Is there a way to calculate a sorted field with sequential numbers? 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.

enter image description here

Can this be done either in the Field Calculator or using an Update Cursor in arcpy?

  • In ArcObjects with an ITableSort you should be able to do it.. not so much in python. How is the table sorted? you could read it up to a dictionary with OID and sort field, sort the dictionary, create another dictionary with OID and Value, iterate the sorted first dictionary to assign the value to the second then cursor through assigning with the second dictionary... a bit of mucking around but that's all I can think of without using ArcObjects. – Michael Stimson May 15 '16 at 22:32
  • @MichaelMiles-Stimson that's not a bad idea, I could probably load it into dictionaries to determine a sort order then write those values out to the Seq. – Midavalo May 15 '16 at 22:36
  • That's how I've done it before and it's worked fine. I can't find my code right now; It was a one-off so it's probably on one of my backup discs... If I come across it I'll post as an answer - provided there isn't already a good answer to this question. – Michael Stimson May 15 '16 at 23:01
  • I've always been annoyed that this cannot be done easily in ArcGIS. Whereas, it's trivial in MapInfo. The easiest way that I have come across is using the Sort Tool but that creates another dataset which you'd have to join back. – Fezter May 16 '16 at 0:15
  • Your python syntax works perfectly, thanks for that. I just wonder whether it is possible to start the first row with 1 rather than 0. If it is possible can you give me the code for it. Have a good week end Fred – Fred Apr 6 '18 at 14:45
12

"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! )

enter image description here

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

  • I believe the first four lines of this code are being run for each and every record. That must not be allowed, since the layer only needs to be sorted once for the entire calculation. Consider using the trick I show in my post or demonstrate that the layer is only being read once to determine the record sort order for only the first record. – Richard Fairhurst May 16 '16 at 1:38
  • @RichardFairhurst I tested my original expression on 10 thousands records, it took 2mins 06 sec to complete, modification resulted in 5 seconds improvement. It seems that first lines aren't repeated on every record. Yes field calculator is much slower than script, convenient though – FelixIP May 16 '16 at 2:01
  • Test the same table against my calculation. If they take the virtually same time to do the calculation then I will accept your assumption that it is only being processed once. 2 min and 6 seconds is fairly slow. – Richard Fairhurst May 16 '16 at 2:16
  • OK 1.5 seconds would seem to indicate that the first 4 lines are not being processed for each record. Anyway, the dictionary is the way to go in either case. However, what do you do when I want the Seq number to not be unique on each record when the values in the other fields are the same? That would be what I would want for the related table in a 1:M relationship. – Richard Fairhurst May 16 '16 at 2:22
  • +1 @RichardFairhurst for dictionary. Shuffling through list was a slow part in my original. Re not to be unique it is a big variation of OP – FelixIP May 16 '16 at 2:28
6

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

  • How does it handle duplicates? – FelixIP May 16 '16 at 1:44
  • Add the OID to the field list. I have added the OID to the field list to make sure it is unique. – Richard Fairhurst May 16 '16 at 1:47
  • Alternatively if there are duplicates and the user wants all of the duplicates to have the same SEQ value then leave out the ObjectID and use set() on the list before running the for loop and adding it to the dictionary. – Richard Fairhurst May 16 '16 at 1:55
  • +1 Thanks @RichardFairhurst, much the same as my attempt to write in arcpy, although I didn't realise you could call most of that from within the Field Calculator – Midavalo May 16 '16 at 6:53
2

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!)

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.