Increment Field in Sorted Table

[JoeBorgione]

I've seen a few posts here and on stack exchange that mention this topic, but when I use the code provided it fails. This seems pretty straight forward:

counter = 1
while counter < 11:
  print (counter)
  counter += 1
‍‍‍‍

 and when in an idle window, returns :

1

2

3

....

10 

 As expected.  However, if I put something like that in the field calculator, I get 000539 Error.  It seems that any auto-incrementer I've found online has a dependency on the OID in way or another.  I've tried a da.UpdateCursor, and CalculateField_managment:  

arcpy.CalculateField_management("Streets","CoordSortValue","+=1","PYTHON")‍‍

with no luck.

Is it possible to sort a table, and then calculate the position each record has in the sort?

[RichardFairhurst]

I assume you are not using the Sort tool to create a copy and just wanting to sort in the table view, since the Sort tool would reorder the records in the new output in a way that the ObjectID would serve as the position counter of each record.  The Field Calculator will not respect any sort other than the ObjectID order, so you can only use the Field Calculator if that is the order you want and you just want to eliminate record gaps.  For that calculation you need to use the Python Field Calculation below:

Pre-Logic Code:

rec=0

def autoIncrement():

global rec

pStart = 1 

pInterval = 1

if (rec == 0): 

  rec = pStart 

else: 

  rec += pInterval 

return rec

Code Block:

autoIncrement()

There is no way to use the sort order of the Table View to order the records of a sequential number.  If you want to sort the records on one or more fields in a way that does not follow the order of the objectIDs, the best approach is to create a Python script that loads a dictionary with a tuple of the values of the fields you want, including the ObjectID as the last field to ensure all records are unique.  The dictionary can be sorted and then assigned values with sequential numbers based on the sorted key.  Although a python script can be set up in the Field Calculator, I never take that approach since scripts are best if they are run in Idle or another Python interpreter.  See my Blog on Turbo Charging Data Manipulation with Python Cursors and Dictionaries.  Something like the following untested code may work:

import arcpy

sourceFC = r"C:\Path\SourceFeatureClass1"  
  
sourceFieldsList = ["SortField1", "SortField2", "SortField3", "@OID" ]  
  
# Use list comprehension to build a dictionary from a da SearchCursor  
valueDict = {tuple(r[0:]):0) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

counter = 0
for item in sorted(valueDict.keys):
    counter += 1
    valueDict[item] = counter

sourceFieldsList.append("counterField")  

with arcpy.da.UpdateCursor(sourceFC, sourceFieldsList) as updateRows:  
    for updateRow in updateRows:  
        keyValue = tuple(updateRow[0:-1]) 
        # verify that the keyValue is in the Dictionary  
        if keyValue in valueDict:
            updateRow[-1] = valueDict[keyValue]
            updateRows.updateRow(updateRow)

del valueDict‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[DanPatterson_Retired]

Joe...

The principle is documented in Numpy and Arcpy Play Nice ...

In that example a new file is created, however with a tiny tweek the sorted results could be appended to an existing table using arcpy.da.ExtendTable ... perhaps when you have some time with your numpy lessons

[JoeBorgione]

Thank you gentlemen.  Richard, I have indeed read you 'Turbocharging' post.  Now with this I think I understand the dictionary approach a bit better.

Dan- as always your input is appreciated.  I'm going to give both approaches a go today.

rfairhur24‌

Dan_Patterson‌

[DanPatterson_Retired]

a note about extendtable in Pro 2.0.1... got a bug I am documenting, The table doesn't update automagically if the project containing the table is open... even removing it and reopening it doesn't work.  I will post a code sample when classes are over today.  It is easy

intable 'OBJECTID' and 'fieldtosort' used in TableToNumpyArray  call it 'a'

sort 'a'  using argsort on 'a' 'fieldtosort' as the key field.  this gives an 'idx' index

slice 'a' using 'idx  ... a[idx]  called 'result'

I change the dtype of the array here so that you get a new field name... this is the piece I will have to share later

    but I think  ...> a.dtype = [('OBJECTID'), '<i4'), ('Sorted_num', '<i4')]  # IFFFF 'fieldtosort' is numeric, otherwise you just snag the dtype from array 'a' and change the fieldname as shown, but reuse the dtype

arcpy.da.ExtendTable(table path, 'OBJECTID', array a, 'OBJECTID')

Sounds complicated, but it is 3-4 lines of code.  

I also have some code in my blog that does the fancy sorting (can't remember what it is called offhand)

ie sorteddata  =    [1, 1, 1, 2, 2, 3, 4]

conventional srt = [1, 2, 3, 4, 5, 6, 7]

whateverit is .... = [1, 1, 1, 4, 4, 6,7]

then there is another one as well

Sorts text as well as number and by default sorts multiple columns in the order provided

I should redo a blog on this once I get the 'ExtendTable' issue resolved for Pro 2.0.1

[JoshuaBixby · Accepted solution]

Maybe I am confused on what you are trying to accomplish, but does something like the following work:

from itertools import count
cntr = count()

fc = # path to feature class

with arcpy.da.UpdateCursor(fc,["CoordSortValue","Streets"],sql_clause=(None,"ORDER BY Streets")) as cur:
    for i, j in cur:
        cur.updateRow([next(cntr), j])‍‍‍‍‍‍‍‍

The above assume you want to sort by "Streets" and store the sorted order in "CoordSortValue".

[DanPatterson_Retired]

ExtendTable.... permanent joins... arcpy and numpy place nice 

It has the sorting example that I was referring to

[JoeBorgione]

NIce... Let me see what I can do with it when I get to work.  I'm working with one of the other guys on a project; his angle is web access to the data, while my angle is providing the data.  One of the objectives is to be able to sort a couple different ways in the web app with multiple fields being considered in the sort, not unlike the advanced sort option available in ArcMap.

For whatever reason, the sort tool does not work on my computer.  I've got ESRI looking into it, but my money is on a software repair or perhaps re-install. (Hooray!)  With Joshua's approach I can use 3 different fields in the sort (which is all we need, haven't tried more), and that's pretty cool, especially with me making the tweaks...

Dan_Patterson‌

bixb0012‌

[MatthewDuffy]

Unfortunately, none of these answers worked for me, as I wanted an auto increment identifier based on multiple fields. 

As such I am now exporting the table to Excel where I can perform the calculation on a field that has been sorted by multiple fields. 

After all these years, why isn't this functionality built-in to the field calculator tool for both ArcMap and ArcPro?

[DanPatterson_Retired]

Matthew, do you have an example? 

It can most certainly be done with numpy and extend table.  It is really easy to sort on multiple fields, add an incrementing text/number field.  If you have a use case, I can add it to 

/blogs/dan_patterson/2018/02/12/table-tools-a-set-of-tools-for-working-with-tabular-data 

in the field calculator section... there is a sequential number, but adding multifield sort followed by a unique identifier wouldn't be that hard.

Probably the reason why the tools don't exist is that there isn't enough demand, or the demand is not known.  (hence ArcGIS Ideas‌ was spawned.)