Rank features by Field A for each unique value in Field B

[Welch_Nicolas]

Apologies in advance if this has been asked and answered elsewhere, but I couldn't find it. 

I have a table of parcel lot lines. I've sorted them by parcel number and length. Now I need to add a field that ranks them by length for each unique parcel, so it would look something like this: 

Parcel number	Length	RankByLength
Parcel1	30	1
Parcel1	35	2
Parcel1	100	3
Parcel1	102	4
Parcel2	38	1
Parcel2	40	2
Parcel2	90	3
Parcel2	91	4

I would think it's a pretty small bit of code to do it, but nevertheless still beyond my capabilities. 

[DanPatterson]

https://community.esri.com/docs/DOC-14928-free-tools-for-arcgis-pro-table-tools 

Rank by field(s) might work, if not, let me know and I will see if it can be tweaked

[Welch_Nicolas]

Thanks, Dan. I downloaded and tried Rank by field(s) last night, thinking, like you, that it would do this. But it just produced an overall ranked list, rather than rankings for each unique parcel number. 

[DanPatterson]

hmmmm I would have to throw in a select by attribute into the mix then to query by the primary column, then aggregate the results later.

I won't get to that for a while, but if you had only a few cases of the parcel column, that would be the procedure...get the individual ranked listings, then merge them together.

[Welch_Nicolas]

Unfortunately there about 160,000 parcels. 

Using Frequency, I have a field with the count of parcel lines for each parcel. Is there a simple code block that could do this using Calculate Field? Conceptually it seems simple: something that adds one iteratively until the rank = the line frequency, then restart at 1. But I don't have the knowledge to generate that code. 

[DavidPike]

This is pretty shoddy code i'm sure, but it should do the job.

whack this into the pre-logic script box (python)

pn = 0
rank = 1
def func(parcel_number):
    global pn
    global rank
    if pn == 0:
        pn = parcel_number
        result = 1
    elif parcel_number == pn:
        rank += 1
        result = rank
    else:
        pn = parcel_number
        rank = 1
        result = 1
    return result
   

then pass in:

func(!Parcel_number!) 

or whatever you parcel field is, this of course depends upon your fields being sorted, if not you will probably be best with an update cursor.

[JoshuaBixby]

Field Calculator doesn't honor any sorting from an open table view in Pro, so this might work but could very likely give incorrect results as well.

[JoshuaBixby · Accepted solution]

This should work for you in the interactive Python window (just fill in value for tbl):

from itertools import count, groupby

tbl = # path to feature class/table or name of layer/table view
fields = ["ParcelNumber", "Length", "RankByLength"]
sql = "ORDER BY ParcelNumber, Length"
with arcpy.da.UpdateCursor(tbl,fields, sql_clause=(None,sql)) as cur:
    for k,g in groupby(cur, key=lambda x: x[0]):
        ctr = count(1)
        for row in g:
            cur.updateRow(row[:-1] + [next(ctr)])‍‍‍‍‍‍‍‍‍‍

[Welch_Nicolas]

WOW. This worked brilliantly. Thank you!! 

This is all part of a ModelBuilder model, so I'm taking this opportunity to learn how to create and add scripts in ModelBuilder. 

[JoshuaBixby]

Please Mark Correct a response if it answered your question so the question can be closed out.