topic Re: Counting consecutive numbers in a list in Python Questions

Counting consecutive numbers in a list

JasonWilder — Sun, 12 Dec 2021 02:14:21 GMT

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

Re: Counting consecutive numbers in a list

Arne_Gelfert — Fri, 22 Mar 2019 14:56:36 GMT

So which part of this is not working? Looks like you're trying to tackle this 2 ways.

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 15:12:26 GMT

Hi. Both code blocks work independently of each other. I don't understand how to get my desired result using the return from def count_consec into the update row cursor in order to write the count of consecutive numbers into the Count field. Probably missing something fundamental. My gut is telling me the count code in the update cursor code block needs to be simplified and rewritten with the code from the def. I appreciate the reply.

Re: Counting consecutive numbers in a list

DanPatterson_Retired — Sun, 12 Dec 2021 02:14:24 GMT

numpy is available in arcmap and pro, you can use it

for counting

random_list=[1,4,5,6,7,9,9,19,21,22,23,24]

uni, cnt = np.unique(random_list, return_counts=True)

uni
array([ 1,  4,  5,  6,  7,  9, 19, 21, 22, 23, 24])

cnt
array([1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1], dtype=int64)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

for sequences, ie identify where the absolute value of the sequential differences is less than or equal to 1

a
array([1, 4, 2, 4, 0, 0, 1, 4, 4, 4, 3, 2, 3, 4, 0, 2, 0, 1, 0, 1])

w =np.where(np.abs(a[:-1] - a[1:]) <= 1, 1, 0)

w
array([0, 0, 0, 0, 1, 1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 1, 1])‍‍‍‍‍‍‍

Re: Counting consecutive numbers in a list

Arne_Gelfert — Fri, 22 Mar 2019 15:20:38 GMT

Oh, I think, I get it... you don't want to be populating the 'Count' field with the current count, 1 for the 1st, 2 for the 2nd, but with the total count of consecutive numbers. So if there are 7 55's in field 1, field 2 should have the value 7 for all 55's. Is that it?

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 15:43:09 GMT

Yep! The field comes in to me in a specific way, so I don't sort the field or count all instances of specific number. I think about it like making subsets for each range of consecutive numbers the code finds going down the column, then counting how many numbers are in the subset. For example; starting at the first row: 1,2,3,4 (first subset has 4 consecutive), the next number in the sequence is 8 (second subset with only 1 consecutive), next numbers are 34,35,36,37,38 (third subset with 5), next numbers may be 1,2,3,4 again (fourth subset has 4 consecutive), and so on down the rows. Then, the 'Count' field would contain the corresponding count of the subset.

Re: Counting consecutive numbers in a list

Arne_Gelfert — Fri, 22 Mar 2019 15:49:22 GMT

Ok, still different from what I understood But I think I get it now.

Re: Counting consecutive numbers in a list

DanPatterson_Retired — Sun, 12 Dec 2021 02:14:27 GMT

So to clarify, is this what you are looking for?

      [[ 0, 54,  6,  0,  6],   
       [ 1, 34,  3,  6,  9],
       [ 2, 52,  2,  9, 11],
       [ 3, 21,  2, 11, 13],
       [ 4, 55,  1, 13, 14],
       [ 5, 52,  2, 14, 16],
       [ 6, 53,  1, 16, 17],
       [ 7, 55,  1, 17, 18],
       [ 8, 35,  3, 18, 21],
       [ 9, 53,  1, 21, 22]])

for '0'  first sequence starts at 54, is six values long and  begins at index 0 to 6
    '1'  second sequence begins at 34, is 3 long from the 6 to 9 value in the original
    '2'  third sequence 2 long, from the 9 to 11 in the original
    etc‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 16:18:43 GMT

Hi Dan. Thanks for the reply! I like the numpy approach as it simplifies things quite a bit, but don't think the return is precisely what I need. I should clarify, the random_list was something I was playing with to test the def count_consec code. The def returns counts as I would expect from any list with or without consecutive numbers. Now just figuring out how to leverage all or part of that def or potentially numpy into the Update Cursor code.

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 16:22:19 GMT

Hi Dan. I didn't see your explanation at first in your code block when I was reading it. Let me think about it more. It may be what I need.

Re: Counting consecutive numbers in a list

JoshuaBixby — Fri, 22 Mar 2019 16:24:42 GMT

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?

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 16:38:06 GMT

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.

Re: Counting consecutive numbers in a list

JoshuaBixby — Fri, 22 Mar 2019 17:55:29 GMT

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.

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 19:05:54 GMT

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

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 19:42:42 GMT

Hey Dan. I got study your output a bit more and yes, I think this is what I am after actually. Just need to capture the number in the third column of that array and use it in the cursor so I can write it to the 'Count' field. Any hints on how to do that?

Re: Counting consecutive numbers in a list

DanPatterson_Retired — Fri, 22 Mar 2019 20:43:29 GMT

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)

Re: Counting consecutive numbers in a list

JasonWilder — Fri, 22 Mar 2019 20:55:45 GMT

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!

Re: Counting consecutive numbers in a list

JoshuaBixby — Fri, 22 Mar 2019 21:04:50 GMT

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.

Re: Counting consecutive numbers in a list

DanPatterson_Retired — Fri, 22 Mar 2019 21:30:18 GMT

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

Re: Counting consecutive numbers in a list

Arne_Gelfert — Sun, 12 Dec 2021 02:14:29 GMT

Okay, folks, this contribution of mine may not be plug and play. I agree with the above statements that type of problem is best solved with SQL. If that's not possible, it look like some sort of nested Update Cursors. I didn't go so far as to try that on some dummy feature class but I've been thinking about this all day (.. the likely reason being that I'm merely a programming hack and not a computer scientist). Anyway, if your table rows were just a list, this is what I came up with. It's really just some Friday fun... I'm sorry if it doesn't help you finish the job. Happy Friday.

numbers = [1,2,3,4,17,1,18,19,20,5,6,34,35,27,28,29,17,56,57,58,59,343,12,35,36,78,79,80]

#Creates a list of list tracking counts of values
counter = [[num,0] for num in numbers]

#Creates a counter for each series of consecutive values
consec_cnt = 1

for i,item in enumerate(counter):
 
   if i == 0:
      item[1] = 1
 
   elif item[0] == counter[i-1][0]+1:
      consec_cnt += 1
      item[1] = consec_cnt
 
      if i == len(counter)-1:
       
         hi = consec_cnt
         for each in counter[i-hi+1:i+1]:
            each.append(hi)

   else:
      hi = consec_cnt
      consec_cnt = 1
      item[1] = consec_cnt
 
      for each in counter[i-hi:i]:
         each.append(hi)

print(counter) 


[[1, 1, 4], [2, 2, 4], [3, 3, 4], [4, 4, 4], [17, 1, 1], [1, 1, 1], [18, 1, 3], [19, 2, 3], [20, 3, 3], [5, 1, 2], [6, 2, 2], [34, 1, 2], [35, 2, 2], [27, 1, 3], [28, 2, 3], [29, 3, 3], [17, 1, 1], [56, 1, 4], [57, 2, 4], [58, 3, 4], [59, 4, 4], [343, 1, 1], [12, 1, 1], [35, 1, 2], [36, 2, 2], [78, 1, 3], [79, 2, 3], [80, 3, 3]]

#In this list, each item consists of the value from original list, a 1,2,3 for first, second, third consecutive, and a value of max consecutives in a row
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍