nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to calculate a field using other field

ZairaRosas

Hi everyone.

I'm trying to give a unique ID to a bunch of rows with the same name and then other unique ID to another bunch of rows with another name and go on. Something like this:

ID           Name
1                A
1                A
2                B
2                B

I have a lot of records so I don't wanna do it one by one. I think I can do this with a python script, but I'm not sure how!

Anyone can help me, please!!

Find more posts tagged with

Accepted answers

LanceCole

Zaira,

Thank you, I understand now what you are attempting. Couple of additional questions;

1) can you include the ID in your original table before your transpose the additional records.

2) are you attempting to only assign an ID based upon ndr_est, correct? or will it also be dependent upon edo as well?

3) do you have another table with the wanted ID and ndr_est values available? If so, you can perform a temporary attribute Join in ArcGIS using the ndr_est values and use field calculator to copy the ID values from one table to another then remove the join. Please see Overview of joins and relates—ArcGIS Pro | ArcGIS Desktop.

4) are you comfortable with python scripts? If so, you can use something along the lines of the following:

<SPAN class="keyword token">import</SPAN> arcpy

<SPAN class="comment token"># function returns list of unique values in a given field of a table</SPAN>
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">unique_values</SPAN><SPAN class="punctuation token">(</SPAN>table <SPAN class="punctuation token">,</SPAN> field<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</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>table<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">return</SPAN> sorted<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">{</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">}</SPAN><SPAN class="punctuation token">)</SPAN>

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\path\to\MyGDB.gdb\MyTable'</SPAN> <SPAN class="comment token"># path to your table or featureclass</SPAN>
lst <SPAN class="operator token">=</SPAN> unique_values<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'nbr_est'</SPAN><SPAN class="punctuation token">)</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>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'nbr_est'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ID'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> lst<SPAN class="punctuation token">.</SPAN>index<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>
        cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<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>

This script runs standalone and will create a list of unique values from the field 'nbr_est' in the table or feature class "MyTable" contained in the GBD "MyGDB.gdb". The second part of the script uses the index position of the unique value in the list to assign an integer ID based upon the contents of 'nbr_est'. Please do note, this script will not always return the same ID for a given 'nbr_est' value, especially if additional rows have been added to the table or feature class containing additional 'nbr_est' values. However, if you are running it once this should work well to set the initial ID values.

All comments

JoshuaBixby

Try the following:

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class or table</SPAN>
case_fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"nbr_est"</SPAN><SPAN class="punctuation token">]</SPAN>
sort_field<SPAN class="punctuation token">,</SPAN> sort_order <SPAN class="operator token">=</SPAN> <SPAN class="string token">"OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ASC"</SPAN>
count_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ID"</SPAN>

fields <SPAN class="operator token">=</SPAN> case_fields <SPAN class="operator token">+</SPAN>  <SPAN class="punctuation token">[</SPAN>count_field<SPAN class="punctuation token">]</SPAN>
sql_orderby <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY {}, {} {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>
    <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>case_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> sort_field<SPAN class="punctuation token">,</SPAN> sort_order
<SPAN class="punctuation token">)</SPAN>
count <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</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>fc<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> sql_orderby<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
    cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>v_prev<SPAN class="punctuation token">,</SPAN> count<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
    count <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>
    <SPAN class="keyword token">for</SPAN> v<SPAN class="punctuation token">,</SPAN> _ <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>v<SPAN class="punctuation token">,</SPAN> v_prev<SPAN class="punctuation token">)</SPAN>
        <SPAN class="keyword token">if</SPAN> v <SPAN class="operator token">!=</SPAN> v_prev<SPAN class="punctuation token">:</SPAN>
            v_prev <SPAN class="operator token">=</SPAN> v
            count <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN>
        cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>v<SPAN class="punctuation token">,</SPAN> count<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
        count <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</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>

JoeBorgione

Dictionary and an update cursor is a fun way to take of your problem. /blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-dictionaries

LanceCole

Zaira,

Thank you, I understand now what you are attempting. Couple of additional questions;

1) can you include the ID in your original table before your transpose the additional records.

2) are you attempting to only assign an ID based upon ndr_est, correct? or will it also be dependent upon edo as well?

4) are you comfortable with python scripts? If so, you can use something along the lines of the following:

<SPAN class="keyword token">import</SPAN> arcpy

<SPAN class="comment token"># function returns list of unique values in a given field of a table</SPAN>
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">unique_values</SPAN><SPAN class="punctuation token">(</SPAN>table <SPAN class="punctuation token">,</SPAN> field<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</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>table<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">return</SPAN> sorted<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">{</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">}</SPAN><SPAN class="punctuation token">)</SPAN>

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\path\to\MyGDB.gdb\MyTable'</SPAN> <SPAN class="comment token"># path to your table or featureclass</SPAN>
lst <SPAN class="operator token">=</SPAN> unique_values<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'nbr_est'</SPAN><SPAN class="punctuation token">)</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>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'nbr_est'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ID'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> lst<SPAN class="punctuation token">.</SPAN>index<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>
        cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<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>

ZairaRosas

Oh sorry!!

I made a table with transposed fields where 1 record from my original table became into 10 records on the new table with the transposed fields. So I want to give to my 10 new records an ID in a new field according to the name field.
I'm doing all of this on ArcGIS Pro 2.3.3, inside of the attributes table.
I attached an image.

I want every 10 records with the same nbr_est to have the same ID in a new field. There's a lot of records and that's why I don't wanna do it with queries and the simple field calculator.

I'm sorry, I hope I have explained my self way better. Thank you so so much.

LanceCole

Zaira,

Yes, this is something that can be completed very easily using Python or even field calculator, however, can you please provide a more detailed explanation for what you are trying to accomplish? Your question is a little confusing pertaining to how and when you are generating the "unique IDs".

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.