How to calculate a field using other field

3640
5
Jump to solution
08-01-2019 07:52 AM
ZairaRosas
New Contributor II

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

0 Kudos
1 Solution

Accepted Solutions
LanceCole
MVP Regular Contributor

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:

import arcpy

# function returns list of unique values in a given field of a table
def unique_values(table , field):
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})

fc = r'C:\path\to\MyGDB.gdb\MyTable' # path to your table or featureclass
lst = unique_values(fc, 'nbr_est')
with arcpy.da.UpdateCursor(fc, ['nbr_est', 'ID']) as cursor:
    for row in cursor:
        row[1] = lst.index(row[0])
        cursor.updateRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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.

View solution in original post

5 Replies
LanceCole
MVP Regular Contributor

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".

ZairaRosas
New Contributor II

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.

0 Kudos
LanceCole
MVP Regular Contributor

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:

import arcpy

# function returns list of unique values in a given field of a table
def unique_values(table , field):
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})

fc = r'C:\path\to\MyGDB.gdb\MyTable' # path to your table or featureclass
lst = unique_values(fc, 'nbr_est')
with arcpy.da.UpdateCursor(fc, ['nbr_est', 'ID']) as cursor:
    for row in cursor:
        row[1] = lst.index(row[0])
        cursor.updateRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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.

JoshuaBixby
MVP Esteemed Contributor

Try the following:

fc = # path to feature class or table
case_fields = ["nbr_est"]
sort_field, sort_order = "OBJECTID", "ASC"
count_field = "ID"

fields = case_fields +  [count_field]
sql_orderby = "ORDER BY {}, {} {}".format(
    ", ".join(case_fields), sort_field, sort_order
)
count = 1

with arcpy.da.UpdateCursor(fc, fields, sql_clause=(None, sql_orderby)) as cur:
    cur.updateRow([v_prev, count])
    count += 1
    for v, _ in cur:
        print(v, v_prev)
        if v != v_prev:
            v_prev = v
            count = 1
        cur.updateRow([v, count])
        count += 1
JoeBorgione
MVP Emeritus

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-diction... 

That should just about do it....