topic How to calculate a field using other field in Python Questions

How to calculate a field using other field

ZairaRosas — Thu, 01 Aug 2019 14:52:55 GMT

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

Re: How to calculate a field using other field

LanceCole — Thu, 01 Aug 2019 16:35:41 GMT

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

Re: How to calculate a field using other field

ZairaRosas — Thu, 01 Aug 2019 18:28:02 GMT

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.

Re: How to calculate a field using other field

LanceCole — Sat, 11 Dec 2021 11:11:28 GMT

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.

Re: How to calculate a field using other field

JoeBorgione — Fri, 02 Aug 2019 02:43:21 GMT

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

Re: How to calculate a field using other field

JoshuaBixby — Sat, 11 Dec 2021 11:11:30 GMT

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