5

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.

6
  • 1
    Please correct the syntax errors and undefined variables. Also try to design your example code so that it can be run from pure Python instead of within the Calculate Value tool so that it can be debugged.
    – blah238
    Jan 31, 2013 at 12:08
  • To clarify, the slct variable is undefined in this example. Also, in your previous question the whereclause selected on the NUM_ID field, but in this one it selects on the LABEL field, which is not defined in the fields list you pass to the cursor. So which is it?
    – blah238
    Jan 31, 2013 at 12:13
  • Is the order with which the records are assigned new NUM_IDs significant?
    – blah238
    Jan 31, 2013 at 14:01
  • What should happen when all of the records in a record set have different values for TYPE_A and TYPE_B? Should they all get updated NUM_IDs or do you still want to leave one of them alone? This is one reason why I asked for more sample data :)
    – blah238
    Jan 31, 2013 at 14:48
  • @blah238 the order in which the NUM_IDs are assigned does not matter, as long as the case in which TYPE_A is not the same as TYPE_B is assigned first. I'll update with all my test cases now Jan 31, 2013 at 17:09

2 Answers 2

2

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']]
3
  • BTW if anyone can write a more elegant dictionary-based whereclause formatter than what I've done that would be welcomed. Let's say that it needs to remain a 1-liner though :)
    – blah238
    Jan 31, 2013 at 21:58
  • Thanks for your detailed answer. This works perfectly, except the requirements for the task have changed again :( I've updated my sample data and expected output. I think the only change that needs to be made is when the whereclauseNoFilter is called. Feb 1, 2013 at 6:30
  • Tweaked again... I think it does what your test indicates. The order isn't exactly the same but hopefully that doesn't matter. I think I am done messing with this though, you have enough to go on I think.
    – blah238
    Feb 1, 2013 at 7:23
0

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()
1
  • 1
    I think this question is less about randomly selecting things as leaving exactly 1 record (the last is fine, need not be random) untouched. Why that is I do not know but I don't think a random seed is the solution here.
    – blah238
    Jan 31, 2013 at 13:39

Your Answer

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

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