2

I am trying to calculate sequential ID Values (SeqID) based on Boolean field (Boolean) along several rows in attribute table through the field calculator of ArcGIS Pro, Python, (expression and codeblock).

enter image description here

As you can see in the picture above, the ID number only progress in the count when the Boolean switch from zero to one and one to zero.

I have seen a post where someone solve it in Microsoft Excel but I need to do this in ArcGIS Pro. The solution using Excel is in this post (link: https://stackoverflow.com/questions/45703154/increment-counter-based-on-change-in-cell-value)

3 Answers 3

6

A simple python script can generate the sequential numbering you require using a Calculate Field tool, I show this below. It works because I declare the variables id and previous as global, so their values persist as the tool steps through the rows of the table.

Calculate field tool

The code block is:

id = 1
previous = -1
def CreateID(BoolVal):
    global id,previous
    if previous == -1:
        # Deal with first value scenario
        previous = BoolVal
        return id
    else:
        # Increment id if BoolVal is not the same as previous
        if BoolVal == previous:
            return id
        else:
            id = id + 1
            previous = BoolVal
            return id 

The code could potentially be written in an even more concise manner but I leave it like this as its much easier to understand the logic.

3
  • 1
    @user2856 I will explore that idea, have to admit they are not something I use. Sounds like they are a smarter way of achieving the same result?
    – Hornbydd
    Commented Feb 20, 2022 at 12:30
  • 1
    Sorry, my generator suggestion isn't really appropriate for this problem, I didn't read closely enough, saw global and my first reaction is always globals are bad.
    – user2856
    Commented Feb 20, 2022 at 22:48
  • 1
    Ah ok, glad you said that. I had a play with some code earlier today and could not get it to work! Assumed I was doing something wrong!
    – Hornbydd
    Commented Feb 20, 2022 at 22:53
2

Here's a python 3 way of doing it without globals because I always try to avoid globals, though they're not really an issue in a field calculator expression.

Expression:

get_id(!Boolean!)

Code Block:

import itertools

def create_id():
    counter = itertools.count(start=0)
    previous = None  # use None instead of a number to make it more generic
    current = None

    def f(value):
        nonlocal previous
        nonlocal current

        if value != previous:
            previous = value
            current = next(counter)
            return current
        else:
            return current

    return f

get_id = create_id()

Or with globals (ugh):

Expression:

get_id(!Boolean!)

Code Block:

import itertools

counter = itertools.count(start=0)
previous = None  # use None instead of a number to make it more generic
current = None

def get_id(value):
    global previous
    global current

    if value != previous:
        previous = value
        current = next(counter)
        return current
    else:
        return current
2
  • 1
    It's really interesting to see the different coding styles, sort of opens ones mind to the alternative approaches that can be executed within the code block. A function inside a function, that's a new one on me!
    – Hornbydd
    Commented Feb 21, 2022 at 21:05
  • 1
    @Hornbydd that shows the lengths I go to avoid globals :)
    – user2856
    Commented Feb 21, 2022 at 21:34
1

I have nothing against ArcGIS's Calculate Field tool, it is the right tool for many users in many situations, but it is the wrong tool for doing order-dependent updates on a data set. Yes, Esri went as far as to include a Sequential Number Helper in the Calculate Field dialog window, but that was more about appeasing customers that kept asking for it rather than sound data management.

Looking to the section of the SQL standard that defines the behavior of cursors, ISO/IEC CD 9075-2 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation), it clearly states (at least through SQL:99 that I have seen in person, but I assume the same language exists in later editions):

When the ordering of a cursor is not defined by an <order by clause>, the relative position of two rows is implementation-dependent.

The importance of that statement can't be emphasized enough. Without including an explicit SQL ORDER BY clause, the user is leaving the order of records returned in a cursor up to the application or database management system. In this specific situation with ArcGIS Pro, Esri does not document how Calculate Field constructs the underlying SQL used to create the cursor. Additionally, many DBMSs explicitly state that no consistent ordering is guaranteed without including a SQL ORDER BY clause. For example, from Microsoft SQL Server SELECT - ORDER BY Clause (Transact-SQL):

The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

With vendors either not documenting their ordering rules or saying no order is guaranteed without a SQL ORDER BY clause, a user can't know for certain the order ahead of time, and worse, the order may change over time even for the same data set.

Since Esri does not allow a user to put a SQL ORDER BY clause into the Calculate Field dialog window, it is not a good approach to adding sequential values to a column when those sequential values depend on a specific ordering of the data. The ArcPy Data Access UpdateCursor is one option that does allow a user to control record order.

For this specific data set, it appears OID is being used as the ordering field. Given that "OID" is used as a shorthand or alias for ObjectID in parts of ArcGIS, it is generally best to avoid using it as a field name since ObjectID is an application/system-managed field.

The following UpdateCursor code should work in the interactive Python window in Pro:

fc = # path to feature class or name of layer loaded into Pro Map window
fields = ["Boolean", "SeqID"]
sql_orderby = "ORDER BY OID"
with arcpy.da.UpdateCursor(fc, fields, sql_clause=(None, sql_orderby)) as cur:
    i = 1
    prev, _ = next(cur)
    cur.updateRow([prev, i])
    for boolean, _ in cur:
        if boolean != prev:
            i += 1
            prev = boolean
        cur.updateRow([boolean, i])

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.