Code field based on rank of another field.

4912
9
Jump to solution
04-16-2018 01:59 PM
DanSeidensticker
Occasional Contributor

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?

1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Emeritus

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

9 Replies
DanPatterson_Retired
MVP Emeritus

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

0 Kudos
DanPatterson_Retired
MVP Emeritus

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.

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

DanSeidensticker
Occasional Contributor

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

0 Kudos
LeilaJackson1
Occasional Contributor III

I got the above code to work to rank my data overall by score (descending - so high score gets a rank of 1), but I would also like to rank scores by 1 or 2 other fields for example within Watershed and within Watershed and County. Is there a way to add this to the code in Calculate Field?

For example

ScoreWatershedCountyOverall RankRank_win_WatershedRank_win_WatershedCounty
30A1521
10A1732
70A2111
25B1621
40B2411
30C1531
60C2211
55C2322

 

Many thanks!

0 Kudos
JamesMacKay3
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")‍
DanSeidensticker
Occasional Contributor

Thanks for the code!

0 Kudos
JoshuaBixby
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.

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