nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Code field based on rank of another field.

DanSeidensticker

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?

Find more posts tagged with

Accepted answers

DanPatterson_Retired

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.

All comments

LJackson29

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

Score	Watershed	County	Overall Rank	Rank_win_Watershed	Rank_win_WatershedCounty
30	A	1	5	2	1
10	A	1	7	3	2
70	A	2	1	1	1
25	B	1	6	2	1
40	B	2	4	1	1
30	C	1	5	3	1
60	C	2	2	1	1
55	C	2	3	2	2

Many thanks!

JoshuaBixby

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

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:

<SPAN class="keyword token">from</SPAN> itertools <SPAN class="keyword token">import</SPAN> count<SPAN class="punctuation token">,</SPAN> groupby

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class or table</SPAN>
sort_fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"EPDO"</SPAN><SPAN class="punctuation token">]</SPAN>
rank_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"RANK_EPDO"</SPAN>
sql_orderby <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>sort_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort_fields <SPAN class="operator token">+</SPAN> <SPAN class="punctuation token">[</SPAN>rank_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> sql_orderby<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; cntr <SPAN class="operator token">=</SPAN> count<SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> key<SPAN class="punctuation token">,</SPAN> group <SPAN class="keyword token">in</SPAN> groupby<SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">lambda</SPAN> x<SPAN class="punctuation token">:</SPAN> x<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dense_rank <SPAN class="operator token">=</SPAN> next<SPAN class="punctuation token">(</SPAN>cntr<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> group<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="punctuation token">[</SPAN>dense_rank<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<SPAN class="keyword token">del</SPAN> cur‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

DanSeidensticker

Thanks for the code!

JamesMacKay3

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):
&nbsp;&nbsp;&nbsp; # Read the values from the dataset
&nbsp;&nbsp;&nbsp; allValues = set()
&nbsp;&nbsp;&nbsp; with arcpy.da.SearchCursor(layerName, [ valueField ]) as searchCursor:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for row in searchCursor:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; allValues.add(row[0])

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

&nbsp;&nbsp;&nbsp; # Set the ranks on the rows
&nbsp;&nbsp;&nbsp; with arcpy.da.UpdateCursor(layerName, [valueField, rankField]) as updateCursor:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for row in updateCursor:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row[1] = lookup[row[0]]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; updateCursor.updateRow(row)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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")‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

DanSeidensticker

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

JamesMacKay3

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.

DanPatterson_Retired

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.

DanPatterson_Retired

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.