I have a table that i need to get sorted base on a field (the STREET field). I don't want to create a new table i would like to work with the existing table. I am able to get the counter field populated with numbers and they look right but i can't see to get the "COUNTER" field sorted. I don't get an error with the following but it's not sorting by the COUNTER field. import arcpy #create order of 'Street' lyr = 'C:\Temp\Default.gdb\CurrntRoadNamesTablePublic_1' # replace this with your layer name fields = [ 'STREET' , 'OID@' ] # set up a dictionary and counter dict = { } counter = 0 # Use list comprehension to build a dictionary of Nummer/ObjectID tuple keys dict = { ( row [ 0 : ] ) : 0 for row in arcpy . da . SearchCursor ( lyr , fields ) } # Sort the dictionary keys and increase the counter values based on the key order for value in sorted ( dict . keys ( ) ) : counter += 1 dict [ value ] = counter # Write back to the layer with an update cursor using the dictionary. Change 'COUNTER' to your counter field fields = [ 'STREET' , 'OID@' , 'COUNTER' ] with arcpy . da . UpdateCursor ( lyr , fields ) as cursor : for row in cursor : row [ 2 ] = dict [ ( row [ 0 ] , row [ 1 ] ) ] cursor . updateRow ( row ) with arcpy . da . UpdateCursor ( lyr , "COUNTER" , sql_clause = ( None , "ORDER BY COUNTER" ) ) as cursor : for row in cursor : cursor . updateRow ( row ) print 'Done'

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; } } }

Permanently sort table by attribute

CCWeedcontrol

I have a table that i need to get sorted base on a field (the STREET field). I don't want to create a new table i would like to work with the existing table. I am able to get the counter field populated with numbers and they look right but i can't see to get the "COUNTER" field sorted. I don't get an error with the following but it's not sorting by the COUNTER field.

<SPAN class="keyword token">import</SPAN> arcpy

<SPAN class="comment token">#create order of 'Street'</SPAN>

lyr <SPAN class="operator token">=</SPAN> <SPAN class="string token">'C:\Temp\Default.gdb\CurrntRoadNamesTablePublic_1'</SPAN> <SPAN class="comment token"># replace this with your layer name&nbsp; </SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'STREET'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'OID@'</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="comment token"># set up a dictionary and counter&nbsp; </SPAN>
dict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN>&nbsp; 
counter <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>&nbsp; 
<SPAN class="comment token"># Use list comprehension to build a dictionary of Nummer/ObjectID tuple keys&nbsp; </SPAN>
dict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">0</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>lyr<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">}</SPAN>&nbsp; 
<SPAN class="comment token"># Sort the dictionary keys and increase the counter values based on the key order&nbsp; </SPAN>
<SPAN class="keyword token">for</SPAN> value <SPAN class="keyword token">in</SPAN> sorted<SPAN class="punctuation token">(</SPAN>dict<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>&nbsp; 
&nbsp;&nbsp;&nbsp; counter <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>&nbsp; 
&nbsp;&nbsp;&nbsp; dict<SPAN class="punctuation token">[</SPAN>value<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> counter&nbsp; 
<SPAN class="comment token"># Write back to the layer with an update cursor using the dictionary. Change 'COUNTER' to your counter field&nbsp; </SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'STREET'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'OID@'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'COUNTER'</SPAN><SPAN class="punctuation token">]</SPAN>&nbsp; 
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>lyr<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>&nbsp; 
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> dict<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>&nbsp; 

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>lyr<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"COUNTER"</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 COUNTER"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Done'</SPAN><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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

タグ付けされた投稿をもっと見る

Accepted answers

BalajiVeera

The sql_clause parameter not intended to update the sorted fields. It's in part of filter the features in order to iterate within Cursor either Update (or) Search.

For sorted field, we have to use Sort_management to sort the STREET field in to temporary feature class and written back in to original feature class (Or)

use this code to overwrite with the sorted rows

lyr = 'C:\Temp\Default.gdb\CurrntRoadNamesTablePublic_1'

datatosort = [] 
with arcpy.da.SearchCursor(lyr,"*") as cursor: 
    for row in cursor: 
       datatosort.append(row) 

#STREET field Index 
streetindex = 2
rowindex = 0 
datatosort.sort(key=lambda tup: tup[streetindex]) 

with arcpy.da.UpdateCursor(lyr,"*") as cursor: 
    for row in cursor: 
       row = datatosort[rowindex] 
       rowindex += 1 
       cursor.updateRow(row)

Change the streetindex value as per STREET field index

All comments

BalajiVeera

If agree with capitalize the Street name in between of this process before Sorting

lyr = 'C:\Temp\Default.gdb\CurrntRoadNamesTablePublic_1'

#STREET field Index 
streetindex = 2
rowindex = 0 
datatosort = [] 
with arcpy.da.SearchCursor(lyr,"*") as cursor:
   for row in cursor:
      if row[streetindex]:
         datatosort.append(row[:streetindex] + (row[streetindex].capitalize(),) + row[streetindex + 1:])
      else:
         datatosort.append(row) 

datatosort.sort(key=lambda tup: tup[streetindex]) 

with arcpy.da.UpdateCursor(lyr,"*") as cursor: 
   for row in cursor:
      row = datatosort[rowindex] 
      rowindex += 1 
      cursor.updateRow(row)

CCWeedcontrol

It seemed at it kind of worked. I noticed that every street with capital letter in the begging of the name was sorted correctly but towards the bottom the streets that don't have the first letter capitalized get sorted starts over after streets with a capital "Z".

BalajiVeera

The sql_clause parameter not intended to update the sorted fields. It's in part of filter the features in order to iterate within Cursor either Update (or) Search.

For sorted field, we have to use Sort_management to sort the STREET field in to temporary feature class and written back in to original feature class (Or)

use this code to overwrite with the sorted rows

lyr = 'C:\Temp\Default.gdb\CurrntRoadNamesTablePublic_1'

datatosort = [] 
with arcpy.da.SearchCursor(lyr,"*") as cursor: 
    for row in cursor: 
       datatosort.append(row) 

#STREET field Index 
streetindex = 2
rowindex = 0 
datatosort.sort(key=lambda tup: tup[streetindex]) 

with arcpy.da.UpdateCursor(lyr,"*") as cursor: 
    for row in cursor: 
       row = datatosort[rowindex] 
       rowindex += 1 
       cursor.updateRow(row)

Change the streetindex value as per STREET field index

CCWeedcontrol

ok, i wasn't sure why it's not getting sorted. I thought maybe it was because of that but it's not the case because nothing is getting sorted.

DanPatterson_Retired

lower and upper case are sorted differently

and upexpectedly ordered at times

a <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'aa'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a_a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'A'</SPAN><SPAN class="punctuation token">]</SPAN>

sorted<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN>

<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'A'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a_a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'aa'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

CCWeedcontrol

Could it be because some street name fist letter is not capitalized?

会話に参加する

サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。

クイックリンク

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