Counting consecutive numbers in a list

10264
21
03-22-2019 07:33 AM
JasonWilder
New Contributor III

Table Mock-Up

Hi there. I need help assembling a python script that counts the number of consecutive numbers in a Field (Long Int) and writes that count in another field for those numbers. The field comes sorted appropriately.The image is what I am trying to accomplish. I cobbled together an Update Cursor, but having a hard time integrating the counting code into it. Any help is much appreciated.

import arcpy

fc = r'C:\Users\a19573\Desktop\Test\Test.gdb\TestCount'
fields = ['Consecutive','Count']

count_values = {}

with arcpy.da.UpdateCursor (fc, fields) as cursor:
    for row in cursor:
        if not row[0] in count_values.keys():
            count_values[row[0]] = 1
        else:
            count_values[row[0]] += 1
        row[1] = count_values[row[0]]
        cursor.updateRow(row)

### Test Code to integrate into Update Cursor above
random_list=[1,4,5,6,7,9,9,19,21,22,23,24] # Set of consecutive numbers in Field 'Consecutive'
def count_consec(lst):
    consec = [1]
    for x, y in zip(lst, lst[1:]):
        if x == y - 1:
            consec[-1] += 1
        else:
            consec.append(1)
    return consec
print(count_consec(random_list))
0 Kudos
21 Replies
DanPatterson_Retired
MVP Emeritus

I have a solution if that is the case let me know and I can send you the code. 

I was just curious as to why you needed to have the count replicated for the sequence, rather than just producing a summary table of the results.

The output I produced actually was ready to go to a table for use within arc* I just wasn't sure whether you needed the from-to numbers (ie 53-59) or just the sequence (0-6)

0 Kudos
JasonWilder
New Contributor III

Thanks again Dan for your help. My ultimate goal was to get the count replicated for the sequence in order to do further processing, like a definition query (e.g. Count > 2) or perhaps some other analysis on it. If replication can achieved, great!

0 Kudos
DanPatterson_Retired
MVP Emeritus

It may be better to stick with the cursor approach if you are going to do a requery.

The approach I was going to suggest is to do the pattern identification as a prelude to splitting the problem into smaller tasks.

I did a blog post on pattern identification and sequencing

/blogs/dan_patterson/2018/05/24/patterns-sequences-occurrence-and-position 

and a related post on what you can do with splitting/slicing

/blogs/dan_patterson/2019/01/29/split-sort-slice-the-top-x-in-y 

Not knowing the steps that come next might make my approach moot

0 Kudos
JasonWilder
New Contributor III

Hey Dan. I've learned much today from you and folks on this thread since I originally posted. I can appreciate the intricacies of what I am trying to accomplish and that it is not as straightforward as I hoped. I'll keep at it. I appreciate the help. Thanks!

JoshuaBixby
MVP Esteemed Contributor

In most database platforms, if not all, result set order is not guaranteed unless you use an ORDER BY clause.  If there is a need to guarantee an order in the cursor, you should specify the sql_clause parameter with an ORDER BY statement on the field you want ordered.

How is the feature class/table being sorted before it gets to you, and how is the feature class/table getting to you?

JasonWilder
New Contributor III

Hi Joshua. Thanks for your time. It comes to me as Feature Class. The feature class geometry and the 'Consecutive' field comes to me ordered based on how the data is collected in the field. Its collected the same each time.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I would still use ORDER BY because Esri does not state anywhere that insertion order into a feature class is honored when cursors access the data.

0 Kudos
JasonWilder
New Contributor III

Thanks Joshua. Wasn't aware of the insertion order could cause issues. I always assumed it starts at row 0 every time.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

With new features classes, the ObjectID numbering always starts with 1 as the documentation covers:  Fundamentals of ObjectID fields—Help | ArcGIS Desktop 

ObjectID fields are sequential and start with the number 1 for geodatabase data. For shapefiles and dBASE tables, the OID or FID column begins at 0.

My comment isn't about records being inserted into a feature class but how those records are accessed by cursors after the feature class is populated.   Cursors are control structures for traversing records, and they are created from SQL SELECT statements.  Most database platforms (possibly all but I haven't checked every vendor's documentation) do not guarantee result set order from a SELECT statement unless using an ORDER BY clause.  Just because the data was inserted into a feature class in a specific order, doesn't mean queries against the data will return that order or any other particular order without explicit ordering being forced.

When it comes to file geodatabases, there appears to be an implicit/default result set ordering based on ObjectID, i.e., with no explicit ordering, the records will get returned in order of ObjectID.  I saw "appears" because that is what a lot of people see anecdotally, but Esri doesn't clearly state this behavior.

0 Kudos
JasonWilder
New Contributor III

Thanks Joshua. This helps. I'll need to formulate my approach around this behavior to be sure my code is repeatable every time.

0 Kudos