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.
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
Nice You can also use a collections.Counter dictionary subclass instead of doing it manually.
Sorting the table and using my suggested field calculator is looking quicker all the time
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