Randomly select a record to update using arcpy.da.UpdateCursor

Question

This question is related to Correct usage of a counter using arcpy.da.UpdateCursor. What I would like to do is use a cursor with a where clause to select a number of records. For each row, the values in two fields, TYPE_A and TYPE_B should be compared. If those two values are not the same, the field NUM_ID should be updated with the current value of the counter.

Once that is done, all but one of the rest of the records in the selection should be updated with the value of the incremented counter.

Some example data:

FEATURE 1 :NUM_ID = 4   TYPE_A = NEW   TYPE_B = NEW
FEATURE 2 :NUM_ID = 4   TYPE_A = NEW   TYPE_B = EXISTING
FEATURE 3 :NUM_ID = 4   TYPE_A = NEW   TYPE_B= NEW
FEATURE 4 :NUM_ID = 4   TYPE_A = NEW   TYPE_B= NEW
FEATURE 5 :NUM_ID = 11  TYPE_A = NEW   TYPE_B = EXISTING
FEATURE 6 :NUM_ID = 14  TYPE_A = NEW   TYPE_B = NEW
FEATURE 7 :NUM_ID = 11  TYPE_A = NEW   TYPE_B = EXISTING

The 4 features with NUM_ID = 4would be selected and Feature 2 will be given the NUM_ID 21, as its values for TYPE_A and TYPE_B are different. The next step would be to update all but one of the other 3 records by incrementing the counter. The next selection would be the 2 features with NUM_ID = 11. As both the records have different values for both fields, they would both be updated. The last selection will be for NUM_ID = 4. As both its values for the fields are the same, and it is the only record in its selection set, no update is needed.

The result:

FEATURE 1 :NUM_ID = 23   TYPE_A = NEW   TYPE_B = NEW
FEATURE 2 :NUM_ID = 21   TYPE_A = NEW   TYPE_B = EXISTING
FEATURE 3 :NUM_ID = 22   TYPE_A = NEW   TYPE_B= NEW
FEATURE 4 :NUM_ID = 4   TYPE_A = NEW   TYPE_B= NEW
FEATURE 5 :NUM_ID = 11  TYPE_A = NEW   TYPE_B = EXISTING
FEATURE 6 :NUM_ID = 14  TYPE_A = NEW   TYPE_B = NEW
FEATURE 7 :NUM_ID = 25  TYPE_A = NEW   TYPE_B = EXISTING

The code I have:

getNum("%Selected Features%")

---

def getNum(ftr):
  lst = [4, 11, 14, 15] #values for SQL expression
  x = 20
  fields = ('TYPE_A', 'TYPE_B', 'NUM_ID')
  assert "NUM_ID" in [f.name for f in arcpy.ListFields(ftr)], "NUM_ID field no in feature class"
  for l in lst:
    y = x + 1
   whereclause = "%(sql_name)s = '%(l)s' """ {"sql_name":arcpy.AddFieldDelimiters(ftr, "NUM_ID"), "l":l}
    with arcpy.da.UpdateCursor(ftr, fields, whereclause) as cursor:
      for row in cursor:
        if row[0] ! = row[1]:
          row[2] = y
        #some condition here
        cursor.updateRow(row)
    y += 1

TYPE_A only has one value in it for all features i.e NEW. TYPE_B could have any value. It does not matter which feature gets updated amongst those which have the same values for both fields (NEW), as long as one of them keeps the original value for NUM_ID. While this code updates the row correctly where the two fields don't match, I do not know how to let one of the records keep the original value, while the rest should be updated.

Answer 1

Okay I think I kind of understand where you are going with this so I updated my answer; I think it now produces the expected output given your sample data.

import arcpy
from pprint import pprint

def getNum(ftr):
    lst = [4, 11, 15] #values for SQL expression
    x = 20
    fields = ('TYPE_A', 'TYPE_B', 'NUM_ID')
    assert "NUM_ID" in [f.name for f in arcpy.ListFields(ftr)], "NUM_ID field no in feature class"
    y = x + 1
    for l in lst:
        # Check whether we need to do anything with this recordset at all; if there are no records where TYPE_A <> TYPE_B then we need not do anything
        whereclauseFilter = "%(num_id)s = %(l)s AND %(type_a)s <> %(type_b)s" % {"num_id":arcpy.AddFieldDelimiters(ftr, "NUM_ID"), "type_a":arcpy.AddFieldDelimiters(ftr, "TYPE_A"), "type_b":arcpy.AddFieldDelimiters(ftr, "TYPE_B"), "l":l}
        rowcountFilter = int(arcpy.GetCount_management(arcpy.SelectLayerByAttribute_management(arcpy.MakeTableView_management(ftr), where_clause=whereclauseFilter)).getOutput(0))
        if rowcountFilter > 0:
            # Okay, yes we do need to do something, but we take off the TYPE_A <> TYPE_B check so we can update the other records as well
            whereclauseNoFilter = "%(num_id)s = %(l)s" % {"num_id":arcpy.AddFieldDelimiters(ftr, "NUM_ID"), "l":l}
            rowcountNoFilter = int(arcpy.GetCount_management(arcpy.SelectLayerByAttribute_management(arcpy.MakeTableView_management(ftr), where_clause=whereclauseNoFilter)).getOutput(0))
            # Added ORDER BY postfix so that (hopefully) the records with different TYPE_A and TYPE_Bs are visited first
            with arcpy.da.UpdateCursor(ftr, fields, whereclauseNoFilter, sql_clause=("","ORDER BY TYPE_A, TYPE_B")) as cursor:
                z = 0 # Keep track of where we are within this record set
                for row in cursor:
                    # If we are on the last (or only) record in the record set, do not update it
                    if z == rowcountNoFilter - 1:
                        break
                    row[2] = y
                    cursor.updateRow(row)
                    y += 1
                    z += 1

def printTable(table):
    fieldnames = [field.name for field in arcpy.Describe(table).fields]
    tablelist = [[row.getValue(fieldname) for fieldname in fieldnames] for row in arcpy.SearchCursor(table)]
    pprint(fieldnames)
    pprint(tablelist)

if __name__ == "__main__":
    tbl1 = r"C:\GISData\test6.gdb\test3"
    tbl2 = r"C:\GISData\test6.gdb\test4"
    printTable(tbl1)
    arcpy.env.overwriteOutput = True
    arcpy.Copy_management(tbl1, tbl2)
    getNum(tbl2)
    printTable(tbl2)

Example output:

[u'OBJECTID', u'NUM_ID', u'TYPE_A', u'TYPE_B']
[[1, 4, u'NEW', u'NEW'],
 [2, 4, u'NEW', u'EXISTING'],
 [3, 4, u'NEW', u'NEW'],
 [4, 4, u'NEW', u'NEW'],
 [5, 11, u'NEW', u'EXISTING'],
 [6, 14, u'NEW', u'NEW'],
 [7, 11, u'NEW', u'EXISTING']]
[u'OBJECTID', u'NUM_ID', u'TYPE_A', u'TYPE_B']
[[1, 22, u'NEW', u'NEW'],
 [2, 21, u'NEW', u'EXISTING'],
 [3, 23, u'NEW', u'NEW'],
 [4, 4, u'NEW', u'NEW'],
 [5, 24, u'NEW', u'EXISTING'],
 [6, 14, u'NEW', u'NEW'],
 [7, 11, u'NEW', u'EXISTING']]

Answer 2

I would add another double type field and run the numpy R.random() function on it. You could then identify the highest or lowest selected value from the random field, then perform the update based on those other non highest or lowest selected records. Here is the basic numpy R.random() code example:

import numpy.random as R

def getRandomValue():
    return R.random()