2

Problem:

Using ArcPy in ArcGIS Pro 2.5.2. I want to increase a fields value by 1 as long as the value in another fields stays the same. In the picture below I show what I mean (filled in manually). I want to increase Field to Update by 1 as long as the value of Codes does not change.

What I tried:

First read Unique codes into a list:

fields = ["Codes"]
with arcpy.da.SearchCursor(TestUpdateField, fields) as cursor:
    lst = sorted({row[0] for row in cursor})

Using that list I tried to update the other value:

 fields = ["Codes", "Fields_to_Update"]
        with arcpy.da.SearchCursor(TestUpdateField, fields) as cursor:
            for row in cursor:
    #for every unique code
                for i in lst:
    #if the code is equal to the unique code
                    if row[0] == i:
    #Field to Update +1
                        row[1] += 1
            cursor.Updaterow(row[1])

Resulting in an index error as my tuple is out of range.

example

3
  • 2
    Is your table always sorted by code as you show it? Is row order important?
    – Hornbydd
    Oct 29, 2021 at 16:13
  • Your uniqueness logic looks wrong (doesn't use set), the looping in the UpdateCursor looks inefficient, and the updateRow is wrong (must pass in a row array that matches the field list length). I'd use a set to feed a dictionary of zeroes (or just skip the SearchCursor and init to 1 inside the update), then increment as each value is found (sorted, if in an RDBMS).
    – Vince
    Oct 29, 2021 at 19:23
  • @Hornbydd not always sorted by Codes, the row order is also not important. As long as the combination of Codes + Field_to_Update is unique.
    – MarcM
    Oct 31, 2021 at 6:43

3 Answers 3

2

It is also possible to use setdefault or get methods of native python dictionary as suggested by @Vince. I would use setdefault as in below while using its value length as the counter for the Fields_to_update:

fields = ["Codes", "Fields_to_Update"]
with arcpy.da.UpdateCursor(TestUpdateField, fields) as cursor:
    counter_dict = {}
    for row in cursor:
        _ = counter_dict.setdefault(row[0], []).append(1)
        counter = len(counter_dict[row[0]])
        row[1] = counter
        cursor.updateRow(row)

In fact, this can also be achieved by using the same logic in the field calculator with python interpreter.

3
  • TypeError: object of type 'NoneType' has no len() for counter. It seems your append does not work. Not sure if an append works as it is an 'int'.
    – MarcM
    Nov 2, 2021 at 13:10
  • @MarcM, I separated setdefault and I think this will do the trick. I thought the chaining the setdefault would return the dictionary value but it returns None. Now I am appending first and then counting.
    – fatih_dur
    Nov 3, 2021 at 5:51
  • thank you, your solution works. Nice logic.
    – MarcM
    Nov 3, 2021 at 14:31
0

Try something like this...

import arcpy

featureClass = "C:/YourFeatureClass"
fields = ["CODES", "Field_to_Update"]

with arcpy.da.UpdateCursor(featureClass, fields, sql_clause=(None, "ORDER BY CODES")) as cursor:
    prevFieldValue = ""
    counter = 1
    for row in cursor:
        if row[0] == prevFieldValue:
            counter += 1
        else:
            counter = 1
        
        row[1] = counter
        cursor.updateRow(row)
        prevFieldValue = row[0]

If you need to have the rows in a more specific order, then you just need to add to the "sql_clause" portion... you do NOT need to include those fields (that are specified in the "sql_clause" portion) in the "fields" variable...


!!EDIT!! I am leaving my previous answer posted, but as Vince pointed out in the comments (which I wasn't completely aware of), my code may not work as expected for non-database feature classes (including ones in file geodatabases...) Therefore, you could use arcpy.UpdateCursor (instead of arcpy.da.UpdateCursor) using the code below as an example...

import arcpy

featureClass = "C:/YourFeatureClass"
fields = "CODES;Field_to_Update"
sortFields = "CODES A"

cursor = arcpy.UpdateCursor(featureClass, fields=fields, sort_fields=sortFields)
prevFieldValue = ""
counter = 1
for row in cursor:
    if row.getValue("CODES") == prevFieldValue:
        counter += 1
    else:
        counter = 1
    
    row.setValue("Field_to_Update", counter)
    cursor.updateRow(row)
    prevFieldValue = row.getValue("CODES")
4
  • 2
    Note: ORDER BY does not function on shapefile or file geodatabase sources -- DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources. If "YourFeatureClass" doesn't end in .sde then this may silently fail.
    – Vince
    Oct 30, 2021 at 20:12
  • I wish ESRI would be more clear with their documentation regarding this... It's not clear if by "databases" if they mean file geodatabases... I've been using SQL statements for several years with file geodatabases without any issues (at least not that I'm aware of) and I tested the code in my answer against a file geodatabase before submitting... Oct 31, 2021 at 15:52
  • File geodatabase is not built on a 3rd party SQL engine (a "database"). The code you've written won't fail, per se, but it will not order the output as expected, either.
    – Vince
    Oct 31, 2021 at 17:43
  • Provided alternate answer using roughly same logic with arcpy.UpdateCursor instead. It seems like being able to sort an arcpy.da.UpdateCursor on a file geodatabase should be a basic functionality that's already built in, but apparently ESRI thinks otherwise... Nov 1, 2021 at 17:56
0

It is also possible to use pandas module:

import pandas as pd
import arcpy

fc = r'C:\folder\TestUpdateField.shp'
fields = ['OID@','Codes']

df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc, fields), columns=fields) #Create a pandas dataframe
df['cumcount'] = df.groupby(fields[1]).cumcount()+1 #Count each code and store results in a new column
updatedict = dict(zip(df['OID@'],df['cumcount'])) #zip oid and count into a dictionary

with arcpy.da.UpdateCursor(fc, fields) as cursor: #Update
    for row in cursor:
        if row[0] in updatedict:
            row[1]=updatedict[row[0]]
            cursor.updateRow(row)
2
  • No errors, code seems to run but row[1] is still empty. Looking into it.
    – MarcM
    Nov 2, 2021 at 13:23
  • I forgot cursor.updateRow(row)
    – BERA
    Nov 2, 2021 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.