topic Re: Field Calculating with Python Script using multiple fields and sequential numbering in Python Questions

Field Calculating with Python Script using multiple fields and sequential numbering

SpencerLarson — Fri, 22 Sep 2017 23:12:50 GMT

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.

Re: Field Calculating with Python Script using multiple fields and sequential numbering

DarrenWiens2 — Fri, 22 Sep 2017 23:14:38 GMT

Have you got a start on anything?

Re: Field Calculating with Python Script using multiple fields and sequential numbering

SpencerLarson — Fri, 22 Sep 2017 23:19:58 GMT

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.

Re: Field Calculating with Python Script using multiple fields and sequential numbering

ClintonDow1 — Fri, 22 Sep 2017 23:47:25 GMT

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.

Re: Field Calculating with Python Script using multiple fields and sequential numbering

RandyBurton — Sat, 11 Dec 2021 21:30:04 GMT

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

Re: Field Calculating with Python Script using multiple fields and sequential numbering

DanPatterson_Retired — Sat, 11 Dec 2021 21:30:07 GMT

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

Re: Field Calculating with Python Script using multiple fields and sequential numbering

RandyBurton — Sat, 11 Dec 2021 21:30:09 GMT

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

Re: Field Calculating with Python Script using multiple fields and sequential numbering

SpencerLarson — Tue, 26 Sep 2017 16:23:47 GMT

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?

Re: Field Calculating with Python Script using multiple fields and sequential numbering

DanPatterson_Retired — Tue, 26 Sep 2017 16:25:59 GMT

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

Re: Field Calculating with Python Script using multiple fields and sequential numbering

RandyBurton — Tue, 26 Sep 2017 18:07:26 GMT

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.

Re: Field Calculating with Python Script using multiple fields and sequential numbering

RandyBurton — Sat, 11 Dec 2021 21:30:12 GMT

With Field Calculator, you can only do a single pass, and you wouldn't get the exact name you are looking for. Each name would be appended with an ID number. That said, you might get something close with this.

Pre-Logic Script Code:

d ={} # global dictionary for counting

def seq_count(f0,f1,f2):
    global d # access counting dictionary
    dictValue = "{}{}".format(f0,f1)
    if dictValue not in d.keys():
        d[dictValue] = 1 # insert key into dictionary and set value to 1
        return "{}_{}".format(dictValue,d[dictValue]) # value to update field
    else:
        d[dictValue] += 1 #increment value in dictionary
        return "{}_{}".format(dictValue,d[dictValue]) # value to update field‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Call it with:

seq_count(!rpsuid!, !facilityNumber!, !waterUtilityNodeIDPK!)‍‍‍

Result:

6705Water_1
6705Water_2
6705Sewer_1
6705Sewer_2
6705Sewer_3
6705Comm_1‍‍‍‍‍‍

Re: Field Calculating with Python Script using multiple fields and sequential numbering

ClintonDow1 — Tue, 26 Sep 2017 19:11:02 GMT

Nice You can also use a collections.Counter dictionary subclass instead of doing it manually.

Re: Field Calculating with Python Script using multiple fields and sequential numbering

DanPatterson_Retired — Tue, 26 Sep 2017 19:14:50 GMT

Sorting the table and using my suggested field calculator is looking quicker all the time

Re: Field Calculating with Python Script using multiple fields and sequential numbering

ClintonDow1 — Tue, 26 Sep 2017 19:41:19 GMT

Yeah that's fair yours would be quicker, Counter certainly is not the fastest data structure available, since its implemented in Python and not native C. (It is substantially improved in Python 3, at least) But still it makes the function readable and is fine to use until optimization is required. Different tools for different problems, I guess