topic Re: Permanently sort table by attribute in Python Questions

Permanently sort table by attribute

CCWeedcontrol — Fri, 10 Dec 2021 22:15:50 GMT

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'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Permanently sort table by attribute

CCWeedcontrol — Wed, 11 Apr 2018 20:39:30 GMT

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

Re: Permanently sort table by attribute

DanPatterson_Retired — Fri, 10 Dec 2021 22:15:53 GMT

lower and upper case are sorted differently

and upexpectedly ordered at times

a = ['a', 'aa', 'a_a', 'A']

sorted(a)

['A', 'a', 'a_a', 'aa']‍‍‍‍‍

Re: Permanently sort table by attribute

CCWeedcontrol — Wed, 11 Apr 2018 22:02:35 GMT

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.

Re: Permanently sort table by attribute

BalajiVeera — Fri, 10 Dec 2021 22:15:55 GMT

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

Re: Permanently sort table by attribute

CCWeedcontrol — Thu, 12 Apr 2018 15:33:27 GMT

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

Re: Permanently sort table by attribute

BalajiVeera — Fri, 10 Dec 2021 22:15:58 GMT

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)