# Calculate Sequential Values based on Boolean field

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)

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.

• @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? Commented Feb 20, 2022 at 12:30
• 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 `global`s are bad. Commented Feb 20, 2022 at 22:48
• 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! Commented Feb 20, 2022 at 22:53

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
``````
• 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! Commented Feb 21, 2022 at 21:05
• @Hornbydd that shows the lengths I go to avoid globals :) Commented Feb 21, 2022 at 21:34

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

``````