Sort UpdateCursor with Multiple SQL ORDER BY Statements?

[VinceE]

I am trying to loop through an existing point feature class with an UpdateCursor (DA version) and assign consecutive labels (A, B, C, D...) to a field for features that fall within the same "group," which is a numbered attribute stored in another field. Lets call them the GROUP field and the LABEL field.

I have accomplished this as follows, and it seems to work fine. It uses SQL's "ORDER BY GROUP," then increments through an alphabet string to assign the LABEL, then starts over at 'A' when the GROUP changes.

from string import ascii_uppercase
from arcpy.da import UpdateCursor

fc = r'C:\Users\vce50\Desktop\Cursors\Cursors\Cursors.gdb\Random_Points'
fields = ['GRP', 'LBL']
where = f"""GRP IS NOT NULL AND TRIM(BOTH ' ' FROM GRP) <> ''"""
sql_clause = (None, 'ORDER BY GRP ASC')

with UpdateCursor(fc, fields, where, sql_clause=sql_clause) as ucurs:
    for group, _ in ucurs:
        current_group = group
        try:
            if current_group != previous_group:
                letter_index = 0
        except NameError:
            letter_index = 0

        new_lbl = ascii_uppercase[letter_index]
        ucurs.updateRow([group, new_lbl])

        letter_index += 1
        previous_group = current_group

The problem is that I would also like to order the labels spatially (north to south, for example), as a secondary sort after the GROUP. I am using a File GDB feature class, so I think that means no spatial SQL queries. I have also tried adding a Y_COOR field (DOUBLE type), and using that as a secondary SQL ORDER BY:

sql_clause = (None, f"""ORDER BY {grp_fld} ASC; ORDER BY {y_coor} DESC""")

However, I cannot seem to get this to work. It seems like only one ORDER BY query can be passed. Is that the case?

I would prefer to do this in place with an UpdateCursor, but could also read all rows using a SearchCursor, sort with Python's sorted() and lambda function to order by multiple fields, and then write rows to a new blank feature class if the UpdateCursor won't work. Any recommendations would be appreciated!

[VinceE]

This is great, thanks for sharing this. For the fun of it, I rewrote to avoid using the nested cursors. I think a lot of this is a matter of style/opinion, but this separates the reading and editing from the writing, and also only sorts using the lambda function, rather than once with the SQL query and once with the lambda.

It's basically the same as yours, it just creates a dictionary of updated values with the OID as the key, which enables writing in the correct order. This only uses two cursors (I believe you are recreating an UpdateCursor with every SearchCursor loop--I don't know how expensive that is). However, this version does have to create the intermediate dictionary.

While I still think using the SQL query could be cleaner/easier to read, I assume it is slower because it forces the user to add and calculate a separate coordinate field (unless they have a spatially enabled geodatabase). Because the spirit of the question was specifically about using the SQL query, I am going to leave that as marked correct for posterity, but thanks all the input!

from string import ascii_uppercase
from arcpy.da import SearchCursor, UpdateCursor

fc = r'PATH'

# Ensure OID and SHAPE are index 0 and 1.
fld = [f.name for f in ap.ListFields(fc) if f.type not in ['Geometry', 'OID']]
all_fld = [ap.da.Describe(fc)['OIDFieldName'], 'SHAPE@'] + fld
# ['OBJECTID', 'SHAPE@', 'LBL', 'XCOOR', 'YCOOR', 'GRP']

# Indices of fields, used to call specific values in cursor rows.
grp_idx = all_fld.index('GRP')
lbl_idx = all_fld.index('LBL')

# SQL WHERE:
where = f"""GRP IS NOT NULL AND TRIM(BOTH ' ' FROM GRP) <> ''"""

# Use Python sorted w/ lambda funct; update the given row; store in dict w/ OID key.
# Sort by GROUP, then DESC x-coor.
row_dict = {}
with SearchCursor(fc, all_fld, where) as scurs:
    scurs_sort = sorted(scurs, key=lambda x: (x[grp_idx], -x[1].centroid.X))
    for row in scurs_sort:
        row = list(row)     # Can't update SCurs tuple, unlike UCurs list
        current_group = row[grp_idx]
        try:
            if current_group != previous_group:
                letter_index = 0
        except NameError:
            letter_index = 0

        row[lbl_idx] = ascii_uppercase[letter_index]
        row_dict[row[0]] = row[1:]
        
        letter_index += 1
        previous_group = current_group

# Write the new row back to the same FC, using the OID as key again.
with UpdateCursor(fc, all_fld, where) as ucurs:
    for oid, *_ in ucurs:
        new_data = row_dict[oid]
        ucurs.updateRow([oid, *new_data])

[VinceE]

To follow up with this, I have had luck simultaneously using an UpdateCursor and an InsertCursor on the same feature class. I had a field with a list of values in it, and I needed to explode that list out so each item was in a single row with it's own point. This meant updating the current row to include only the first value in the list, and then creating new rows in the same feature class for each subsequent value in the list.

Using the Editor class enabled me to do this. I don't know exactly what makes this necessary, but I was not able to do it without the Editor.

with ap.da.Editor(outgdb):
    with UpdateCursor(infc, '*') as ucurs, InsertCursor(infc, '*') as icurs: