Sequential Numbering based on last numbering

2190
11
06-29-2018 08:40 AM
CCWeedcontrol
Occasional Contributor III

I have a road layer that i need to add sequential numbering to, the RD_ID field. Some already have a RD_ID number and some are missing. I need to populate attributes of only the missing ones that only have CC in front of them but in sequential order but starting from the last number left off at. I attached a picture of what i need. thanks.

I have the following that adds sequential ID but it doesn't take in consideration where the last RD_ID left off.

import arcpy

fc = r'C:\Temp\Roads.shp'

startNumber = 0

with arcpy.da.UpdateCursor(fc, "RD_ID") as cursor:
    for row in cursor:
            row[0] = startNumber
            startNumber = startNumber + 1
            cursor.updateRow(row)

print 'Done'
0 Kudos
11 Replies
ChrisDonohue__GISP
MVP Alum

You probably already thought of this, but I will throw it out there:

Would it be possible to do a query first on the full dataset to just select those items that need to be updated?  Then sort all the data to find the last used ID number.  In your code, use the last used ID number plus 1.  Then run the Python on just the selected records.  See the link below for suggested Python code to do this.

How To: Create sequential numbers in a field using Python in the Field Calculator 

There is probably a more elegant full-on Python solution out there.  I'm curious to see what code the Python experts come up with for this one.

Chris Donohue, GISP

forestknutsen1
MVP Regular Contributor

I have a update script for a table that a domain is based off of that does some thing like this. Please see the code snippet below.

The main idea is to read in all the other values into a list and then get the max(). Once that is done increment form there...

import arcpy
import os
import operator

domain_name = "T_LineSegment"
sde_path = r"xxxx"
gis_user = "xxxx"
master_table = "ARCFM8.T_LineSegment"
temp_domain_table = r"in_memory/temp_domain_table"
code_field = "code"
description_field = "description"

# add new domain codes
print "Updating domain codes..."
old_codes = []
with arcpy.da.SearchCursor(os.path.join(sde_path, master_table), ["GISDOMAINCODE", "OBJECTID"]) as search_cursor:
    for row in search_cursor:
        try:
            old_codes.append(int(row[0]))
        except TypeError:
            if row[0] is not None and row[0] != "":
                print "Error in domain code objectid: {}".format(row[1])
new_code = max(old_codes) + 1

with arcpy.da.UpdateCursor(os.path.join(sde_path, master_table), ["JUNCTIONLINECODE", "GISDOMAINCODE"]) \
        as update_cursor:
    for row in update_cursor:
        if row[0] is not None or row[0] != "":
            if row[1] is None or row[1] == "":
                print "\tAdding new code {} for {}".format(str(new_code), row[0])
                row[1] = new_code
                new_code += 1
        update_cursor.updateRow(row)

 

CCWeedcontrol
Occasional Contributor III

I am getting an error at line 16, I think this because i have text and integers (CC12345).

import arcpy

fc = r'C:\Temp\Roads.shp'

#add new domain codes
print "Updating domain codes..."
fields = ["RD_ID", "FID"]
old_codes = []
with arcpy.da.SearchCursor(fc, fields) as cursor:
    for row in cursor:
        try:
            old_codes.append(int(row[0]))
        except TypeError:
            if row[0] is not None and row[0] != "":
                print "Error in domain code objectid: {}".format(row[1])
new_code = max(old_codes) + 1

with arcpy.da.UpdateCursor(fc, "RD_ID", "RD_ID") \
        as update_cursor:
    for row in update_cursor:
        if row[0] is not None or row[0] != "":
            if row[1] is None or row[1] == "":
                print "\tAdding new code {} for {}".format(str(new_code), row[0])
                row[1] = new_code
                new_code += 1
        update_cursor.updateRow(row)
0 Kudos
forestknutsen1
MVP Regular Contributor
DarrenWiens2
MVP Honored Contributor

Extract the numerical part of the code first, otherwise '+' means concatenate, and you can't concatenate string and int.

vals = ['CC12345', 'CC12346', 'CC12349', 'CC12348']
print(max(vals))

max_int = int(max(vals)[2:])
print(max_int)

max_int += 1
print(max_int)

new_code = 'CC' + str(max_int)
print(new_code)

CC12349
12349
12350
CC12350
DarrenWiens2
MVP Honored Contributor

I'd probably go a step farther and put everything into a dictionary. Assumes all your numbers are 5 digits:

vals = ['CC12345', 'CAL12345', 'CC12346', 'CAL12341', 'CC12349', 'CC12348']
vals_dict = {}
for val in vals:
 vals_dict[val[:-5]] = max(val[-5:], vals_dict.get(val[:-5], 0))
print(vals_dict)

{'CC': '12349', 'CAL': '12345'}
JoshuaBixby
MVP Esteemed Contributor

Strictly from a data management perspective, how are you defining sequential-ness without using an ORDER BY SQL clause?  If the order matters, which is seems to in your case, there is no guarantee of order from a cursor unless you use ORDER BY.

CCWeedcontrol
Occasional Contributor III

Darren and you bring up a good questions. I did think about putting that field into a directory and sort.

I thought of stripping the "CC" first then adding it back. I don't get an errors but the script is not working correctly it currently updates all the attributes. It populates all of the attributes with CC48932 not just the ones that start with "CC", it doesn't seem to increase. The last RD_ID is CC48931.

rd_list = []          
with arcpy.da.SearchCursor(fc, ["RD_ID"]) as cursor:          
    for row in cursor:  
        try:          
            if "CC" in row[0]:                
                point_list.append(int(row[0].strip("CC")))             
        except TypeError:          
            pass                  
del cursor          
 
              
rd_list.sort()          
RD_ID = rd_list[-1] + 1          
    
#whereclause = "RD_ID = '%CC%'"
with arcpy.da.UpdateCursor(fc, "RD_ID") as rows:           
    for row in rows:
        row[0] = 'CC%05d' %RD_ID 
        RD_ID + 1                      
        rows.updateRow(row)      
    del row           
    del rows
0 Kudos
forestknutsen1
MVP Regular Contributor

Looks like you are not limiting the rows you update with the update cursor. Near line 17 or so. You need to filter out the rows you want to modify with some logic. I am guessing that the ones you want to update have an RD_ID of null (None) or an empty string, if so you could just to an "if statement." 

 

for row in rows:
    if row[0] is None or row[0] == "":
        row[0] = 'CC%05d' %RD_ID