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

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)

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.

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.

• Hornbydd: @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?
• user2856: 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.
• Hornbydd: 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!

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

• Hornbydd: 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!
• user2856: @Hornbydd that shows the lengths I go to avoid globals :)

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