Calculate Sequential Values based on Boolean field

Question

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

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)

Answer 1

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.

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.

Answer 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

Answer 3

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