Code field based on rank of another field.

1264
8
Jump to solution
04-16-2018 01:59 PM
Highlighted
New Contributor III

Hi all,

I would like to have all the records in a table given a rank value based on the values in another field. The caveat is that there are tied rankings. For example in the attached screen cap, EPDO is the data to rank, and RANK_EPDO is field to store the rank. Anyone aware of any tools in ArcGIS that will accomplish this?

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
MVP Esteemed Contributor

BTW, If you just sort the table, the code won't work unless it is permanently sorted.

Here is an example where the key field was sorted (many values of 1) and notice the ranks are essentially those of the objectid as they were encountered.

View solution in original post

8 Replies
Highlighted
MVP Esteemed Contributor

That is one way that tied ranks are dealt with...  It is sometimes referred to as 'rankmin'.

I bet your next rank is 15 and there is no 10,.... 14 is there?

What you are supposed to do is randomly choose from on of the 9's to represent their kindred

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

BTW, If you just sort the table, the code won't work unless it is permanently sorted.

Here is an example where the key field was sorted (many values of 1) and notice the ranks are essentially those of the objectid as they were encountered.

View solution in original post

Highlighted
Occasional Contributor

Good catch, I only tested that against data that was already coincidentally sorted that way on another field.  I'm going to delete my earlier post so it doesn't mislead anyone.

Highlighted
New Contributor III

Thanks all. As mentioned, as long as the table is permanently sorted first w/ the Sort Tool, the above code works.

Reply
0 Kudos
Highlighted
Occasional Contributor

In case anyone comes across this and doesn't/can't have permanently sorted data...

Was thinking about this some more this evening, trying to come up with a way to solve the case where the dataset isn't permanently sorted using the field calculator...  Don't think it's possible using the calculator since to the best of my knowledge you can't do a look-ahead/sort, but using the Python window this should work:

def applyRanks(layerName, valueField, rankField):
    # Read the values from the dataset
    allValues = set()
    with arcpy.da.SearchCursor(layerName, [ valueField ]) as searchCursor:
        for row in searchCursor:
            allValues.add(row[0])

    # Create a value/rank lookup
    lookup = { value : rank + 1 for (rank, value) in  enumerate(sorted(allValues, reverse=True)) }

    # Set the ranks on the rows
    with arcpy.da.UpdateCursor(layerName, [valueField, rankField]) as updateCursor:
        for row in updateCursor:
            row[1] = lookup[row[0]]
            updateCursor.updateRow(row)

Once added to the Python window, a call like this (using the fields from the original question) should apply the ranks:

applyRanks("theLayerName", "EPDO", "RANK_EPDO")‍
Highlighted
New Contributor III

Thanks for the code!

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

Without using an ORDER BY clause with your cursors, this approach could result in the rankings generated by the search cursor being incorrectly applied with the update cursor.

Highlighted
MVP Esteemed Contributor

As Dan spoke to in his first reply, there are multiple ways to handle ranking when ties are involved.  You appear to be using dense ranking, which is fine, but I would argue is not the "default" or most common technique for dealing with ties.  If you are working with dense rankings, I recommend your column heading include the word "dense" or an abbreviation for it.

Modifying the code from one of my responses on GroupBy 2 columns and keep all fields, I came up with a single-pass cursor approach that uses 1 or more sorting columns:

from itertools import count, groupby

fc = # path to feature class or table
sort_fields = ["EPDO"]
rank_field = "RANK_EPDO"
sql_orderby = "ORDER BY {}".format(", ".join(sort_fields))
with arcpy.da.UpdateCursor(fc,
                           sort_fields + [rank_field],
                           sql_clause=(None, sql_orderby)) as cur:
    cntr = count(1)
    for key, group in groupby(cur, lambda x: x[:-1]):
        dense_rank = next(cntr)
        for row in group:
            cur.updateRow(row[:-1] + [dense_rank])
           
del cur‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍