How can I naturally sort alphanumeric values in a string?

9861
8
Jump to solution
07-28-2016 11:26 AM
RobBlash
Occasional Contributor III

My field had values like B3, M4, S5, E26, E10, E9, E11 (all within one field).

I need to naturally sort so the values look like this: B3, E9, E10, E11, E26, M4, S5

I've tried the following code as suggested here, but it gives me an error stating "The value type is incompatible with the field type". If anyone has some suggestions I'd be interested! BTW I used this in Field Calculator

import re

def sorted_nicely( l 😞

    """ Sorts the given iterable in the way that is expected.

    Required arguments:

    l -- The iterable to be sorted.

    """

    convert = lambda text: int(text) if text.isdigit() else text

    alphanum_key = lambda key: [convert(c) for c in re.split('([0-9]+)', key)]

    return sorted(l, key = alphanum_key)

0 Kudos
1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Emeritus
def srt(val):
    """split and sort"""
    old = val.split(", ")
    new = ["{}{:0>2.0f}".format(i[0],int(i[1:]))  for i in old]
    new.sort()
    out = ", ".join([i for i in new])
    return out

This example

val = "B3, M4, S5, E26, E10, E9, E11"

returns this

'B03, E09, E10, E11, E26, M04, S05'

is that better?

View solution in original post

8 Replies
DarrenWiens2
MVP Honored Contributor

Do you always have 1 letter followed by X numbers?

0 Kudos
DanPatterson_Retired
MVP Emeritus
import re
def sorted_nicely( l ):
    """ Sorts the given iterable in the way that is expected.
    Required arguments:
    l -- The iterable to be sorted.
    """
    convert = lambda text: int(text) if text.isdigit() else text
    alphanum_key = lambda key: [convert(c) for c in re.split('([0-9]+)', key)]
    return sorted(l, key = alphanum_key)
if __name__ == "__main__":
    """ test """
    vals = [ 'B3', 'M4', 'S5', 'E26', 'E10', 'E9', 'E11' ]
    ret = sorted_nicely( vals )
    print("input {}\n sorted {}".format(vals, ret))

seems to work

runfile('C:/mystuff.....)
input ['B3', 'M4', 'S5', 'E26', 'E10', 'E9', 'E11']
sorted ['B3', 'E9', 'E10', 'E11', 'E26', 'M4', 'S5']
In [3]:

but to get it to work in the field calculator will be messy, it may be easier to dump the field out to an array, with the FID or OBJECTID field, sort the field in question and send the results back to join to the original file.  or some ugly cursor magic to read the field first or make a new field, split your existing data and pad the numbers

It is always best to use same width formatting ... E00, E01etc

DanPatterson_Retired
MVP Emeritus

An alternate if you want to create a new field for sorting later

def make_sort(l):
    """make the field and sort"""
    for i in l:
        print("{}{:0>3.0f}".format(i[0],float(i[1:])))
if __name__ == "__main__":
    """ test """
    vals = [ 'B3', 'M4', 'S5', 'E26', 'E10', 'E9', 'E11' ]
    make_sort(vals)

Will make these results... you just have to 'field calculator it'  ie.  make_sort('youroriginalfield')

your new field
B003
M004
S005
E026
E010
E009
E011

Then just sort your new field

JoshuaBixby
MVP Esteemed Contributor

Before talking about the Field Calculator or this cursor or that cursor, what exactly are you trying to accomplish?  I see you have a field with some mixed alphanumeric numbers, and that you want them sorted "naturally."  Why do you want them sorted?  Is it for display purposes, analysis, something else?  Are you trying to permanently reorder the records in the table?

0 Kudos
DanPatterson_Retired
MVP Emeritus

permanent sorting would still require a key.  You can generate a key value pair by splitting the letters from the numbers.  By design, it is best to account for possibilities like this up front just by having your alphanumeric key formed so they were the same length.  You could use double letters, and triple numbers for example.  One advantage of this type of key is that they are readily sorted, but the letter component could be used as a subclassification system, making it easier to remember that EA0000 to EZ9999 belong to one group with 26 subgroups of 10000 each.

0 Kudos
RobBlash
Occasional Contributor III

My initial question was not clear, my apologies. I'm creating a map book street index, for display purposed only. All of those values are within a string for a single record, so I'm trying to sort within that one string. I can alter the grid to add leading zeros (A01 instead of A1) but I'd rather avoid that if possible. My screen shot doesn't show any numbers smaller <10 but they're mixed in here and there.

StreetIndex.PNG

0 Kudos
DanPatterson_Retired
MVP Emeritus
def srt(val):
    """split and sort"""
    old = val.split(", ")
    new = ["{}{:0>2.0f}".format(i[0],int(i[1:]))  for i in old]
    new.sort()
    out = ", ".join([i for i in new])
    return out

This example

val = "B3, M4, S5, E26, E10, E9, E11"

returns this

'B03, E09, E10, E11, E26, M04, S05'

is that better?

RobBlash
Occasional Contributor III

Perfect, thanks!

0 Kudos