Ranking field values

At the Esri International User Conference this year, an attendee came to the analysis island to ask “how do I create a rank field?”. They had run the Generate Near Table geoprocessing tool (see illustration of table below) and were looking for a way to further rank the distances. Ideally, the table would be updated to include a RANK field starting at ‘1’ for the smallest distance for each PATIENT and increasing sequentially based on the distance. The rank could then be used to facilitate further review and reporting. We were able to come up with the addRanks function below in a few minutes automating a key missing piece of the user’s workflow.

Original table
pre_ranking

Table after running addRanks function.
post_ranking

import arcpy

def addRanks(table, sort_fields, category_field, rank_field='RANK'):
    """Use sort_fields and category_field to apply a ranking to the table.

    Parameters:
        table: string
        sort_fields: list | tuple of strings
            The field(s) on which the table will be sorted.
        category_field: string
            All records with a common value in the category_field
            will be ranked independently.
        rank_field: string
            The new rank field name to be added.
    """

    # add rank field if it does not already exist
    if not arcpy.ListFields(table, rank_field):
        arcpy.AddField_management(table, rank_field, "SHORT")

    sort_sql = ', '.join(['ORDER BY ' + category_field] + sort_fields)
    query_fields = [category_field, rank_field] + sort_fields

    with arcpy.da.UpdateCursor(table, query_fields,
                               sql_clause=(None, sort_sql)) as cur:
        category_field_val = None
        i = 0
        for row in cur:
            if category_field_val == row[0]:
                i += 1
            else:
                category_field_val = row[0]
                i = 1
            row[1] = i
            cur.updateRow(row)

if __name__ == '__main__':
    addRanks(r'C:\Data\f.gdb\gen_near_table_patients2hosp',
             ['distance'], 'patient', 'rank')

Note: dBase (and shapefiles) does not support ORDER BY as used above by arcpy.da.UpdateCursor’s sql_clause argument.

Advertisements

6 thoughts on “Ranking field values

  1. This is right what I have been looking for. Thanks for posting. I was just wondering how to make this ModelBuilder compatible, with the parameters and such. I understand how to hard code the parameters at the bottom, but I was wondering how to input the parameters as you would with a tool or a model. I am sure there is way to do this, but I am not well versed in Python functions or Python, in general. Thanks for any replies in advanced.

    • Hi JA,
      Really just a few simple things. To receive the arguments into the script, you can use arcpy GetParameterAsText or GetParameter functions. And then it’s just a matter of building a script tool with parameters that calls your .py file.

    • I just thought I would further clarify that I do understand how to declare parameters in ArcGIS after loading scripts/models/tools, just not when it comes to Python functions. I have tried declaring them as I normally would, but I am getting the error:

      Traceback (most recent call last):
      File “E:\My Own Personal Scripts\RankFeatsArcPyCafe3.py”, line 43, in
      addRanks(*argv)
      File “E:\My Own Personal Scripts\RankFeatsArcPyCafe3.py”, line 24, in addRanks
      sort_sql = ‘, ‘.join([‘ORDER BY ‘ + category_field] + sort_fields)
      TypeError: can only concatenate list (not “unicode”) to list

      I happen to think that this may be because I am not declaring the argument types (feature class,string, fields, etc..) correctly. Then again, I am not sure.

  2. As I suspected, it was the sort_fields tuple that was causing the problems. While I am still not sure what parameter variable I would use to define a tuple of strings, I can just hard code that aspect because the field that I want to sort from is consistent for each feature. And thank you for your quick response. It prompted me to look into the parameters a little more closely.

  3. Pingback: Ranking values by shape area using ArcPy | DL-UAT

  4. “Note: dBase (and shapefiles) does not support ORDER BY as used above by arcpy.da.UpdateCursor’s sql_clause argument.”
    This is a VERY important note. The code wont error, it will just rank the un-sorted data. I ran into this problem without knowing as it was running rank on a in_memory table which apparently cant use the SQL statement either.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s