Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

This question is related to my question here. 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.

share|improve this question
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 '13 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 '13 at 12:13
    
Is the order with which the records are assigned new NUM_IDs significant? –  blah238 Jan 31 '13 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 '13 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 –  Arabella Jan 31 '13 at 17:09

2 Answers 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']]
share|improve this answer
    
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 '13 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. –  Arabella Feb 1 '13 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 '13 at 7:23

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()
share|improve this answer
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 '13 at 13:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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