Select to view content in your preferred language

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

3873
10
Jump to solution
05-26-2020 09:34 AM
Welch_Nicolas
New Contributor

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 numberLengthRankByLength
Parcel1301
Parcel1352
Parcel11003
Parcel11024
Parcel2381
Parcel2402
Parcel2903
Parcel2914

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

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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)])‍‍‍‍‍‍‍‍‍‍

 

 

 

View solution in original post

10 Replies
DanPatterson
MVP Esteemed Contributor

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


... sort of retired...
0 Kudos
Welch_Nicolas
New Contributor

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. 

0 Kudos
DanPatterson
MVP Esteemed Contributor

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.


... sort of retired...
0 Kudos
Welch_Nicolas
New Contributor

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. 

0 Kudos
DavidPike
MVP Frequent Contributor

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
New Contributor

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. 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos