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