0

I am a bit rusty with Python, I know conceptually what I need to do but am not sure of the syntax/formatting.

The table I'm working with has an id field with negative and positive values and null values. I'm trying to find the max value of of the field and increment that value and use that value to replace the lowest negative value. So for example in this table:

ID
01
04
06
-1
010
012
-2

the max is 12, so -1 gets replaced with 13 and -2 gets replaced with 14.

I used a search cursor to find the min and max values and was able to replace the first negative number with the max value, but I couldn't get it to increment properly. Here's what I'm working with:

import arcpy

fc = r'C:\Test.gdb\test'
field = ['ID']

maxid = max(row[0] for row in arcpy.da.SearchCursor(fc, field))
minid = min(row[0] for row in arcpy.da.SearchCursor(fc, field))

with arcpy.da.UpdateCursor(fc, field) as cursor:
    for row in cursor:
        if row[0] == minid:
            row[0] = maxid
            cursor.updateRow(row)
            del cursor

I've tried maxid += 1 and maxid = maxid + 1 and it gave me syntax errors - the field itself is a text field, not an int field (hence the preceding zeroes in the table). Am I on the right path but just using the wrong syntax to add 1 and move to the next? Or do I need to temporarily convert the field first and then run the update cursor? If so, what is the best method for doing so? I've tried messing with the int function to no avail and have also just tried making a second field in the table and using the field calculator to convert, but that failed as well. I think I'm close but feel like I'm missing something simple. Any advice or suggestions?

1
  • 1
    It's a major mistake to treat numeric and string tokens as equivalents, since sorting works differently. You need to explicitly cast the str values to int before sorting, possibly first removing the leading zeros, since they might result in octal-integer conversion. Your loop logic doesn't attempt what you assert you want, and the del cursor inside the loop that uses cursor is unfortunate.
    – Vince
    Jan 11, 2023 at 22:57

1 Answer 1

1

Something like the following should work. It gets the max ID, then uses an UpdateCursor with a SQL clause to limit the rows to just the negative values in ascending order and loops through them, incrementing the max ID as it goes:

import arcpy

fc = r'C:\Test.gdb\test'
field = ['ID']

# Get max ID, casting str to int
maxid = max(int(row[0]) for row in arcpy.da.SearchCursor(fc, field))

with arcpy.da.UpdateCursor(
        fc, field,
        sql_clause=(None, f"where CAST({field} AS INTEGER) < 0 ORDER BY CAST({field} AS INTEGER)")) as rows:
    for row in rows:
        maxid+=1  # increment the maxid
        # cast back to string and zero pad to 2 characters so '1' becomes '01', but '10' stays as '10'
        row[0] = f"{maxid:02}"  # equivalent to str(maxid).zfill(2)
        rows.updateRow(row)

        # *Do not* del cursor while you are using it

Note: this answer only applies to geodatabases, including file geodatabases (as asked about here). It will not work for shapefiles. From the documentation:

DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables).

2
  • As @Vince mentioned before in one of my answers, in the documentation for UpdateCursor, it states that "DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables)" So your ORDER BY in a geodatabase may or may not work as expected... Jan 17, 2023 at 20:15
  • 1
    @JasonMiller I know, I tested my code using a file geodatabase and it works. I've edited my answer to reflect that
    – user2856
    Jan 17, 2023 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.