nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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 <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN>
<SPAN class="keyword token">while</SPAN> counter <SPAN class="operator token"><</SPAN> <SPAN class="number token">11</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN>counter<SPAN class="punctuation token">)</SPAN>
&nbsp; counter <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

and when in an idle window, returns :

....

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<SPAN class="punctuation token">.</SPAN>CalculateField_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Streets"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"CoordSortValue"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"+=1"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"PYTHON"</SPAN><SPAN class="punctuation token">)</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

with no luck.

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

Find more posts tagged with

Accepted answers

bixb0012

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

<SPAN class="keyword token">from</SPAN> itertools <SPAN class="keyword token">import</SPAN> count
cntr <SPAN class="operator token">=</SPAN> count<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"CoordSortValue"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Streets"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"ORDER BY Streets"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> i<SPAN class="punctuation token">,</SPAN> j <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>next<SPAN class="punctuation token">(</SPAN>cntr<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> j<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

All comments

RandyBurton

It might be worthwhile to ask this as a new question since this thread is 2 years old and is marked as answered. You can link to this question so that everyone knows what you have tried.

bixb0012

I agree with Dan, this can most certainly be done, and there are multiple ways to do it. If you can provide more details about your situation, especially some example/sample data, I am quite sure the community hear can come up with a solution.

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

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?

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‌

DanPatterson_Retired

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

It has the sorting example that I was referring to

bixb0012

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

<SPAN class="keyword token">from</SPAN> itertools <SPAN class="keyword token">import</SPAN> count
cntr <SPAN class="operator token">=</SPAN> count<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"CoordSortValue"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Streets"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"ORDER BY Streets"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> i<SPAN class="punctuation token">,</SPAN> j <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>next<SPAN class="punctuation token">(</SPAN>cntr<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> j<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

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

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

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

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"&nbsp; 
&nbsp; 
sourceFieldsList = ["SortField1", "SortField2", "SortField3", "@OID" ]&nbsp; 
&nbsp; 
# Use list comprehension to build a dictionary from a da SearchCursor&nbsp; 
valueDict = {tuple(r[0:]):0) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

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

sourceFieldsList.append("counterField")&nbsp; 

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

del valueDict‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.