Update record based on series of conditionals using Python

Question

I have a feature class which I've been using a relate to compare duplicate field values and was thinking of a way to eliminate the manual process of checking these duplicate values and making a decision as to which records to keep. There are several parameters which determine which record is kept vs. which one is discarded. For now I'd like to flag which records are kept so I can do some spot checking on the attributes.

I have some mock data below to show the desired results in a "Flagged" field. The conditions would involve first using a where clause to prompt the cursor to scan the "ID" field where ID = X. For this selection compare fieldB records, if they are equal values and one of them does not contain the letter 'C' compare fieldA records and flag the one with the higheest numeric value unless there is a null in one of the fieldA records. If one of these records contains a null value, compare the records in fieldC and flag the greatest value.

Feature class records:

fieldA  fieldB  fieldC  Flagged ID
1       ABC     4                X
2       AB      5                X
3       A       6                X
1       EFG     4                Y
2       EF      5                Y
3       E       6                Y

Desired result in "Flagged" field:

fieldA  fieldb  fieldC  Flagged ID
1       ABC     4                X
2       AB      5                X
3       A       6         1X     X
1       EFG     4                Y
2       EF      5                Y
3       E       6         1Y     Y

Answer 1

I think list comprehension as well as applying sql statements to your search cursor will help you out. Here's a few tips:

If you want all the unique values in a field (field ID, in this case).

##Find all unique values in a field
#Return python set of unique values in field "ID"
values = set([r[0] for r in arcpy.da.SearchCursor (table, "ID")])

Max value for field fieldB, limited to ID X:

##Find max value in field 'fieldb' for ID equal to X
#add field delimiters (quote, double-quote, etc)
delimFldB = arcpy.AddFieldDelimiters (table, "fieldB")
#selection sql
sql = "{0} = '{1}'".format (delimFldB, "X")
#max from cursor limited by sql where clause
maxID_X = max ([r[0] for r in arcpy.da.SearchCursor (table, "fieldB", sql)])

Combine them:

##Combine: iterate through each unique value in ID and find max value in field fieldB
#Return python set of unique values in field "ID"
idValues = set([r[0] for r in arcpy.da.SearchCursor (table, "ID")])
#add field delimiters (quote, double-quote, etc)
delimFldB = arcpy.AddFieldDelimiters (table, "fieldB")
#iterate unique ID values
for id in idValues:
    #selection sql
    sql = "{0} = '{1}'".format (delimFldB, id)
    #max from cursor limited by sql where clause
    maxID = max ([r[0] for r in arcpy.da.SearchCursor (table, "fieldB", sql)])
    #print max value by id
    print id, maxID