topic Re: Create sequential unique ID field based on an advanced sorting in Python Questions

Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Fri, 01 May 2015 02:33:28 GMT

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

Re: Create sequential unique ID field based on an advanced sorting

DanPatterson_Retired — Fri, 01 May 2015 02:59:32 GMT

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

Re: Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Fri, 01 May 2015 03:51:42 GMT

Yes I have advanced license

Re: Create sequential unique ID field based on an advanced sorting

DanPatterson_Retired — Fri, 01 May 2015 03:53:46 GMT

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

Re: Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Fri, 01 May 2015 04:08:09 GMT

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.

Re: Create sequential unique ID field based on an advanced sorting

DanPatterson_Retired — Fri, 01 May 2015 04:12:17 GMT

Edit

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

Re: Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Fri, 01 May 2015 04:22:19 GMT

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

Re: Create sequential unique ID field based on an advanced sorting

DanPatterson_Retired — Fri, 01 May 2015 04:34:19 GMT

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,

Re: Create sequential unique ID field based on an advanced sorting

DanPatterson_Retired — Fri, 01 May 2015 17:43:58 GMT

thought I dealt with this before, I wrote it down

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

Re: Create sequential unique ID field based on an advanced sorting

BlakeTerhune — Sun, 12 Dec 2021 06:54:23 GMT

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()

Re: Create sequential unique ID field based on an advanced sorting

DanPatterson_Retired — Fri, 01 May 2015 23:43:42 GMT

Me thinks he is no longer interested having found a solution on one of several sites he posted the question on... python - Creating multiple fields with ranks using arcpy - Stack Overflow which references an Arcpy article Ranking field values | ArcPy Café with the cavaet that it doesn't work in these situations...

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

Re: Create sequential unique ID field based on an advanced sorting

BlakeTerhune — Fri, 01 May 2015 23:51:30 GMT

Looks like the solution posted there is very similar to mine. Thanks for the follow-up Dan.

Re: Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Sun, 03 May 2015 23:55:22 GMT

Hi Dan, It's been the weekend here, hence why I haven't responded. I will check the code as soon as I can. Also this is the only post on this subject I have created.

Re: Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Sun, 03 May 2015 23:56:41 GMT

Hi Blake,

Thanks for the code, I'll see how this works as soon as I can.

Cheers

Re: Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Mon, 04 May 2015 04:20:42 GMT

Blake, I tried the code but It didn't populate the new field.

I'll just stick to the Excel way as is will be quicker.

Re: Create sequential unique ID field based on an advanced sorting

BlakeTerhune — Sun, 12 Dec 2021 06:54:26 GMT

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()

Re: Create sequential unique ID field based on an advanced sorting

AndrewNeedham — Tue, 05 May 2015 04:44:26 GMT

Thanks Blake,

Re: Create sequential unique ID field based on an advanced sorting

ChadFoster1 — Tue, 30 Jan 2018 00:22:25 GMT

Awesome, I tweaked it a bit more to allow for setup as a tool with parameters, NICE WORK!