Field Calculating with Python Script using multiple fields and sequential numbering

[SpencerLarson]

I would like help with the proper python script to field calculate a fields value from two separate fields plus a sequential number when there is more than 1 record with the same facility number.  Basically build a expression where the fields  rpsuid + facilityNumber + sequential number if more than two records with the same facility number.  The field waterUtilityNodeIDPK I populated manually to show what I would like the result to look like.  Please notice the record number with the facility number named Comm where there is only one record. In the IDPK field the value is 6705Comm with no sequential number since there is only one.

[DarrenWiens2]

Have you got a start on anything?

[SpencerLarson]

I have some python code that does sequential numbering which works and I would think I need some type of if then statement to only add a sequential number at the end if more than one value .  I am a novice to python and have taken some course work but programming doesn't come natural to me.  I really struggle.  Willing to to learn just need help and hints.

[ClintonDow1]

I'd say this might be easiest to accomplish in two passes over your feature class using data access cursors, at least to make it understandable when you're learning Python.

The data type 'dictionary' in Python is a great way to deal with tabular data, the first pass on your table can use an arcpy.da.SearchCursor to create a dictionary where the keys are your rpsuid numbers, and the value is a list of the features' ObjectIDs that have the same rpsuid. Then on the second pass with an arcpy.da.UpdateCursor, you can check the rpsuid against the dictionary's keys, which will return the list of ObjectIDs. Then find the ObjectID's position in the list and append it to that Feature's facility to ultimately get the final value for the idpk field.

I'll leave it as a learning exercise  But feel free to ask if you want some help with the code.

[RandyBurton]

How about something like:

layer = 'layerName' # your layer or shapefile
field0 = "rpsuid" # first field to concatenate
field1 = "facilityNumber" # second field to concatenate
field2 = "waterUtilityNodeIDPK" # field to update

d = {} # dictionary for counting

with arcpy.da.UpdateCursor(layer, [field0, field1, field2]) as rows:
    for row in rows:

        dictValue = "{}{}".format(row[0],row[1])

        if dictValue not in d.keys():
            d[dictValue] = 0 # insert key into dictionary and set value to 0 or 1
            # 0 will start appending with '_1' and 1 will start with '_2'
            row[2] = dictValue # value to update field
            # print dictValue

        else:
            d[dictValue] += 1 #increment value in dictionary
            row[2] = "{}_{}".format(dictValue,d[dictValue]) # value to update field
            # print "{}_{}".format(dictValue,d[dictValue])
            
        rows.updateRow(row)

# print d
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You may be able to add an sql_clause in the UpdateCursor if you want a certain order.  The sequential numbering is not exactly as you desire; that would require two passes.  But this produces results that may be acceptable:

6705Water
6705Water_1
6705Sewer
6705Sewer_1
6705Sewer_2
6705Comm
‍‍‍‍‍‍

[DanPatterson_Retired]

In the perfect scenario, your field would be sorted as in your example, so that replicant case are sequential.  In such rare situations you can use...

old = ""
cnt = 0
def seq_count(val):
    global old
    global cnt
    if old == val:
        cnt += 1
        ret = "{} {:04.0f}".format(val, cnt)
    else:
        cnt = 0
        ret = "{} {:04.0f}".format(val, cnt)
    old = val
    return ret
__esri_field_calculator_splitter__
seq_count(!Test!)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Replacing the !Test! field with your field's name.  Of course this is python etc.

However... unless the table is sorted physically (not just hitting the sort ascending/descending option), then the above won't work, and you would have to build a dictionary to save your counts.  So this is just an example and probably not a solution unless you want to sort your table on that field

[RandyBurton]

Using two passes, this provides the sequential numbering desired.

layer = 'layerName' # your layer or shapefile
field0 = "rpsuid" # first field to concatenate
field1 = "facilityNumber" # second field to concatenate
field2 = "waterUtilityNodeIDPK" # field to update

d0 = {} # dictionary for counting (first pass)
d1 = {} # dictionary for counting (second pass)

# first pass - just count
with arcpy.da.SearchCursor(layer, [field0, field1, field2]) as rows:
    for row in rows:
        dictValue = "{}{}".format(row[0],row[1])
        if dictValue not in d0.keys():
            d0[dictValue] = 1 # insert key into dictionary and set value to 1
        else:
            d0[dictValue] += 1 # increment value in dictionary

# second pass - update
with arcpy.da.UpdateCursor(layer, [field0, field1, field2]) as rows:
    for row in rows:
        dictValue = "{}{}".format(row[0],row[1])
        if dictValue not in d1.keys():
            d1[dictValue] = 1 # insert key into dictionary and set value to 1
            # check value in d0 from first pass
            if d0[dictValue] > 1:
                row[2] = "{}_{}".format(dictValue,d1[dictValue]) # value to update field
            else:
                row[2] = dictValue # value to update field
        else:
            d1[dictValue] += 1 #increment value in dictionary
            row[2] = "{}_{}".format(dictValue,d1[dictValue]) # value to update field
        rows.updateRow(row)

print "Done"‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Results:

6705Water_1
6705Water_2
6705Sewer_1
6705Sewer_2
6705Sewer_3
6705Comm‍‍‍‍‍‍

[SpencerLarson]

Randy- Appreciate your help with this. Correct me if I am wrong but the way the code was written would be appropriate if one was running the code from say idle or the command line, right?  If I plugged this into the field calculator I wouldn't need the first line of code layer = 'layerName' # your layer or shapefile as well as line 4 field2= "waterUtilityNodeIDPK" # field to update , right?

[DanPatterson_Retired]

Randy's code isn't meant to run in the field calculator.  You would have to turn it into a 'def' as in my example

[RandyBurton]

You can run the code inside ArcMap's python window, or by adding a few additional lines ("import arpy", path to layer, etc) you can run it frome idle or command line.  It is not meant to run in field calculator.