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.
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
Sorting the table and using my suggested field calculator is looking quicker all the time
Nice You can also use a collections.Counter dictionary subclass instead of doing it manually.
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 <SPAN class="operator token">=</SPAN><SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="comment token"># global dictionary for counting</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">seq_count</SPAN><SPAN class="punctuation token">(</SPAN>f0<SPAN class="punctuation token">,</SPAN>f1<SPAN class="punctuation token">,</SPAN>f2<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">global</SPAN> d <SPAN class="comment token"># access counting dictionary</SPAN> dictValue <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{}{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>f0<SPAN class="punctuation token">,</SPAN>f1<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> dictValue <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> d<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> d<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token"># insert key into dictionary and set value to 1</SPAN> <SPAN class="keyword token">return</SPAN> <SPAN class="string token">"{}_{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>dictValue<SPAN class="punctuation token">,</SPAN>d<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># value to update field</SPAN> <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> d<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token">#increment value in dictionary</SPAN> <SPAN class="keyword token">return</SPAN> <SPAN class="string token">"{}_{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>dictValue<SPAN class="punctuation token">,</SPAN>d<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># value to update field</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
Call it with:
seq_count<SPAN class="punctuation token">(</SPAN>!rpsuid!<SPAN class="punctuation token">,</SPAN> !facilityNumber!<SPAN class="punctuation token">,</SPAN> !waterUtilityNodeIDPK!<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
Result:
6705Water_1 6705Water_2 6705Sewer_1 6705Sewer_2 6705Sewer_3 6705Comm_1<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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.
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
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?
Using two passes, this provides the sequential numbering desired.
layer <SPAN class="operator token">=</SPAN> <SPAN class="string token">'layerName'</SPAN> <SPAN class="comment token"># your layer or shapefile</SPAN> field0 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"rpsuid"</SPAN> <SPAN class="comment token"># first field to concatenate</SPAN> field1 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"facilityNumber"</SPAN> <SPAN class="comment token"># second field to concatenate</SPAN> field2 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"waterUtilityNodeIDPK"</SPAN> <SPAN class="comment token"># field to update</SPAN> d0 <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="comment token"># dictionary for counting (first pass)</SPAN> d1 <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="comment token"># dictionary for counting (second pass)</SPAN> <SPAN class="comment token"># first pass - just count</SPAN> <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>layer<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>field0<SPAN class="punctuation token">,</SPAN> field1<SPAN class="punctuation token">,</SPAN> field2<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> rows<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> rows<SPAN class="punctuation token">:</SPAN> dictValue <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{}{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> dictValue <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> d0<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> d0<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token"># insert key into dictionary and set value to 1</SPAN> <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> d0<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token"># increment value in dictionary</SPAN> <SPAN class="comment token"># second pass - update</SPAN> <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>layer<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>field0<SPAN class="punctuation token">,</SPAN> field1<SPAN class="punctuation token">,</SPAN> field2<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> rows<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> rows<SPAN class="punctuation token">:</SPAN> dictValue <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{}{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> dictValue <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> d1<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> d1<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token"># insert key into dictionary and set value to 1</SPAN> <SPAN class="comment token"># check value in d0 from first pass</SPAN> <SPAN class="keyword token">if</SPAN> d0<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">:</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{}_{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>dictValue<SPAN class="punctuation token">,</SPAN>d1<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># value to update field</SPAN> <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> dictValue <SPAN class="comment token"># value to update field</SPAN> <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> d1<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token">#increment value in dictionary</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{}_{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>dictValue<SPAN class="punctuation token">,</SPAN>d1<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># value to update field</SPAN> rows<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Done"</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
Results:
6705Water_1 6705Water_2 6705Sewer_1 6705Sewer_2 6705Sewer_3 6705Comm<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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 <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN> cnt <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">seq_count</SPAN><SPAN class="punctuation token">(</SPAN>val<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">global</SPAN> old <SPAN class="keyword token">global</SPAN> cnt <SPAN class="keyword token">if</SPAN> old <SPAN class="operator token">==</SPAN> val<SPAN class="punctuation token">:</SPAN> cnt <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN> ret <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{} {:04.0f}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>val<SPAN class="punctuation token">,</SPAN> cnt<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> cnt <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN> ret <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{} {:04.0f}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>val<SPAN class="punctuation token">,</SPAN> cnt<SPAN class="punctuation token">)</SPAN> old <SPAN class="operator token">=</SPAN> val <SPAN class="keyword token">return</SPAN> ret __esri_field_calculator_splitter__ seq_count<SPAN class="punctuation token">(</SPAN>!Test!<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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
How about something like:
layer <SPAN class="operator token">=</SPAN> <SPAN class="string token">'layerName'</SPAN> <SPAN class="comment token"># your layer or shapefile</SPAN> field0 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"rpsuid"</SPAN> <SPAN class="comment token"># first field to concatenate</SPAN> field1 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"facilityNumber"</SPAN> <SPAN class="comment token"># second field to concatenate</SPAN> field2 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"waterUtilityNodeIDPK"</SPAN> <SPAN class="comment token"># field to update</SPAN> d <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="comment token"># dictionary for counting</SPAN> <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>layer<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>field0<SPAN class="punctuation token">,</SPAN> field1<SPAN class="punctuation token">,</SPAN> field2<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> rows<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> rows<SPAN class="punctuation token">:</SPAN> dictValue <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{}{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> dictValue <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> d<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> d<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="comment token"># insert key into dictionary and set value to 0 or 1</SPAN> <SPAN class="comment token"># 0 will start appending with '_1' and 1 will start with '_2'</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> dictValue <SPAN class="comment token"># value to update field</SPAN> <SPAN class="comment token"># print dictValue</SPAN> <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> d<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token">#increment value in dictionary</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{}_{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>dictValue<SPAN class="punctuation token">,</SPAN>d<SPAN class="punctuation token">[</SPAN>dictValue<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># value to update field</SPAN> <SPAN class="comment token"># print "{}_{}".format(dictValue,d[dictValue])</SPAN> rows<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># print d</SPAN> <SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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 <SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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.
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.
Have you got a start on anything?
Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
