Create sequential unique ID field based on an advanced sorting

AndrewNeedham · ‎04-30-2015

Hi All

Is it possible to create sequential unique ID field based on an advanced sorting of the attributes using Python? And without installing ET GeoWizards? (ArcGIS 10.2)

This doesn't work

38517 - Create sequential numbers in a field using Python in the Field Calculator

ET GeoWizards

SOLUTION for adding new sequential ID numbers based on sorted data

Cheers

BlakeTerhune · ‎05-04-2015

If you like, post the code you used and we can help you debug it.

I just tested my logic and it worked in a file geodatabase. Here is updated code that is a little more flexible.

def main():
    import arcpy
    import os

    temp_gdb = r"N:\TechTemp\BlakeT\Work\TEMP.gdb"
    fc = os.path.join(temp_gdb, "TEMP_Line")
    sortFieldName = "Note1"
    seqFieldName = "SeqID"

    # Step 1
    existingFields = [field.name for field in arcpy.ListFields(fc)]
    if seqFieldName not in existingFields:
        arcpy.AddField_management(fc, seqFieldName, "SHORT")

    # Step 2
    cursorFields = [sortFieldName, seqFieldName]
    sql_postfix = 'ORDER BY {}'.format(sortFieldName)
    with arcpy.da.UpdateCursor(fc, cursorFields, sql_clause=(None, sql_postfix)) as u_cursor:
        # Step 3
        for seqid, row in enumerate(u_cursor, start=1):
            # Step 4
            row[1] = seqid
            u_cursor.updateRow(row)


if __name__ == '__main__':
    main()

View solution in original post

DanPatterson_Retired · ‎04-30-2015

And you don't have the advanced license either do you? Sort tool help

AndrewNeedham · ‎04-30-2015

Yes I have advanced license

DanPatterson_Retired · ‎04-30-2015

The link I provided has a discussion. You will at least be able to determine whether it meets your needs then.

AndrewNeedham · ‎04-30-2015

It's one workaround, I would also get the same result by adding a new field (for the Unique ID's) , Advanced sorting the data, then copying attributes to excel, adding sequential numbers then copying back to Arc while editing. It would be nice to have the ability of field calculator with selections etc, using python.

DanPatterson_Retired · ‎04-30-2015

Edit

Forgot autoincrement uses the feature ID. I will try to find a simpler workaround and post it (numpy offers possibilities)

AndrewNeedham · ‎04-30-2015

The autoincrement doesn't work on sorted fields, it works off the created order eg FID/ObjectID

DanPatterson_Retired · ‎04-30-2015

yes I caught that and edit mine a while ago.

don't have arcmap on the iThingy, I was thinking of a workaround when sorting on geometry is done,

DanPatterson_Retired · ‎05-01-2015

thought I dealt with this before, I wrote it down

Sorting and ranking... arcpy and numpy play nice...

BlakeTerhune · ‎05-01-2015

Could you try something like this?

If applicable, Add Field to store sequential id
Since you're on 10.2, you can create an update cursor with a sql clause using ORDER BY (see second to last code sample). The link is for 10.3 but I've tested this in 10.2.2 on SDE and file geodatabase just fine.
Set up the iteration on the cursor using enumeration
Iterate through the cursor and update the sequential id field with the enumeration number

I haven't tested this, but here's an example:

def main():
    import arcpy
    import os

    temp_gdb = r"C:\Temp\MyGDB.gdb"
    fc = os.path.join(temp_gdb, "MyFC")

    # Step 1
    arcpy.AddField_management(fc, "SeqID", "SHORT")

    # Step 2
    fields = ["OID@", "MySortField", "SeqID"]
    with arcpy.da.UpdateCursor(fc, fields, sql_clause=(None, 'ORDER BY MySortField')) as u_cursor:
        # Step 3
        for seqid, row in enumerate(u_cursor, start=1):
            # Step 4
            row[2] = seqid
            u_cursor.updateRow(row)


if __name__ == '__main__':
    main()