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