Permanently sort table by attribute

653
6
Jump to solution
04-11-2018 08:11 AM
CCWeedcontrol
Occasional Contributor III

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'
0 Kudos
1 Solution

Accepted Solutions
BalajiVeera
Occasional Contributor

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

View solution in original post

6 Replies
CCWeedcontrol
Occasional Contributor III

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

0 Kudos
DanPatterson_Retired
MVP Emeritus

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']
0 Kudos
CCWeedcontrol
Occasional Contributor III

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.

0 Kudos
BalajiVeera
Occasional Contributor

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
Occasional Contributor III

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

0 Kudos
BalajiVeera
Occasional Contributor

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)