UpdateCursor for field calculation based on unique values

Question

UPDATE TO PREVIOUS QUESTION: I have an address feature class. I want to use an UpdateCursor that focuses on two fields: StreetName and Block_ID. For every instance of a unique StreetName, I want the Block_ID to start at 10 and add multiples of 10. For example:

StreetName Block_ID
Main St 10
Main St 20
Main St 30
1st St 10
1st St 20
3rd St 10
3rd St 20

I have the following code but I know it is way off.....I was just testing that it was populating the Block_ID:

def unique_values(table , field):
 with arcpy.da.SearchCursor(table, [field]) as cursor:
 return sorted({row[0] for row in cursor})
# myValues is the list of unique street names identified in SearchCursor
myValues = unique_values(Table, 'STREET') 
arcpy.AddMessage(myValues)
# Here is where we calc block id
strtFld = 'STREET'
blkFld = 'BLK_NO'
update = Table
i=0
with arcpy.da.UpdateCursor(update, [blkFld, strtFld]) as cursor:
 for row in cursor:
 row[1] = myValues[i]
 row.setValue(row[0], i=i*10)
 cursor.updateRow(row)

• Vince: First, rewrite the code with arcpy.da.UpdateCursor -- old-style cursors are ancient, clunky and slow. Second, if you don't use an ORDER BY force sorted access, set up a dictionary to track the last used value, and update it with each value.
• dbChic_noob: Thanks @Vince for pointing out my typo -- tried to type a quick example and missed '.da'. I looked up the ORDER BY option you mentioned but couldn't quite get it to work on a fc.
• Vince: Still very wrong. The SearchCursor indent would cause failure, row.setValue doesn't exist in DA, and your i increment is wrong.
• dbChic_noob: Yeah, I know it is a bit of a disaster....working through some examples online. Not sure what you mean on the search cursor....it works elsewhere as I use it to also create a csv --- the results are correct. However, pulling it into my update cursor isn't working. I know my "i" increment is wrong for a number of reasons but that's where I am at as I continue to work through suggestions. Just wanted to give an updated edit to my current progress for anyone with useful suggestions.

Answer 1

You can import pandas module (if you have ArcGIS version> ~10.3 (?)) and use cumcount:

import pandas as pd
import arcpy
fc = "ak_riks"
cols = ["LANSKOD"]
newfield = "count"
df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,cols), columns=cols)
givenumber = iter(list(df.groupby(cols).cumcount()*10+10))
arcpy.AddField_management(in_table=fc, field_name=newfield, field_type="long")
with arcpy.da.UpdateCursor(fc, newfield) as cursor:
 for row in cursor:
 row[0] = next(givenumber)
 cursor.updateRow(row)

enter image description here

Answer 2

Check example 5B at the bottom of the help page of the da.SearchCursor to see how to use the sql_clause parameter.

I would use the code below (untested), no unique_values function needed:

strtFld = 'STREET'
blkFld = 'BLK_NO'
update = Table
street = ""
i = 10
with arcpy.da.UpdateCursor(update, [strtFld, blkFld], sql_clause=(None, "ORDER BY {}".format(strtFld))) as cursor:
 for row in cursor:
 if row[0] == street:
 i += 10
 else:
 i = 10
 row[1] = i
 street = row[0]
 cursor.updateRow(row)

• dbChic_noob: Thanks for your suggestion! The reason I didn't try that example before is because it stated, "ORDER BY is only supported when working with databases. It is not supported by other data sources". My data is in a feature class. I did execute your code sample. It din't fail but it also didn't populate the BLK_NO field. Can you explain this part for me: 'if row[0] == street: 1 += 10' ?
• GISGe: Try the code now, the fields were not ordered right, it should work now. If you have a geodatabase feature class, the sql_clause parameter can be used (not with a shapefile). street hold the STREET value of the previous row. If the current row has the same STREET value, BLK_NO is incremented, if it's different, BLK_NO starts back at 10.
• dbChic_noob: Thank you for the clarification! I noticed the fields weren't quite right last night when I was messing around with the code --- with my very limited understanding. I managed to only get it to calculate all BLK_NO to 10......So, I popped in your edited code and it mostly worked. It's interesting that some street name it kept the count correctly based on the street name... while others it did not. For example: Park Rd had proper increments for nearly 30 segments; however 1st St has only 5 occurrences and it managed to have 4 BLK_NO = 10 and 1 BLK_NO = 20. I'll review the data for a pattern.
• GISGe: There must be something with your data indeed. I would check that there's no trailing spaces. Don't forget to accept the answer if it solved your issue, thanks.
• dbChic_noob: I checked the data...and noticed the pattern was related to the ORDER_BY field....it was ordering by BLK_NO instead of the STREET field. Once I changed that, it worked like a charm! Thanks for your help!