topic Re: Sequential Numbering based on last numbering in Python Questions

Sequential Numbering based on last numbering

CCWeedcontrol — Sun, 12 Dec 2021 05:39:48 GMT

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'‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Sequential Numbering based on last numbering

ChrisDonohue__GISP — Fri, 29 Jun 2018 15:46:05 GMT

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

Re: Sequential Numbering based on last numbering

forestknutsen1 — Sun, 12 Dec 2021 05:39:50 GMT

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)

Re: Sequential Numbering based on last numbering

CCWeedcontrol — Sun, 12 Dec 2021 05:39:53 GMT

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

Re: Sequential Numbering based on last numbering

forestknutsen1 — Fri, 29 Jun 2018 18:02:31 GMT

Try casting it to an integer.

int(x)

parsing - How do I parse a string to a float or int in Python? - Stack Overflow

Re: Sequential Numbering based on last numbering

DarrenWiens2 — Sun, 12 Dec 2021 05:39:56 GMT

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

Re: Sequential Numbering based on last numbering

JoshuaBixby — Fri, 29 Jun 2018 20:50:20 GMT

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.

Re: Sequential Numbering based on last numbering

DarrenWiens2 — Sun, 12 Dec 2021 05:39:59 GMT

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'}‍‍‍‍‍‍‍

Re: Sequential Numbering based on last numbering

CCWeedcontrol — Sun, 12 Dec 2021 05:40:02 GMT

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

Re: Sequential Numbering based on last numbering

forestknutsen1 — Sun, 12 Dec 2021 05:40:05 GMT

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

Re: Sequential Numbering based on last numbering

CCWeedcontrol — Sun, 12 Dec 2021 05:40:08 GMT

I add an if statement but the null and blanks one don't get updated sequentially. I get no error's, i did add "=" to line 26.

RD_ID += 1

import arcpy

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

#arcpy.CalculateField_management(fc, "RD_ID", '""" """', "PYTHON")

rd_list = []          
with arcpy.da.SearchCursor(fc, ["RD_ID"]) as cursor:          
    for row in cursor:  
        try:          
            if "CC" in row[0]:                
                rd_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:
        if row[0] in (""," ", None):
            row[0] = 'CC%05d' %RD_ID 
            RD_ID += 1                      
            rows.updateRow(row)      
    del row           


print 'Done'    
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Sequential Numbering based on last numbering

forestknutsen1 — Mon, 02 Jul 2018 18:08:18 GMT

They are not getting updated at all? Or they are not getting the right values when updated? Can you set some breakpoints around the if statement and see the behavior?