Using field calculator to find the next nearest (next minimum value)

Question

I'm new to Python, but I'm attempting to find the next smallest value in a field, using another field as a case field (within field calculator in ModelBuilder). I've drafted the following, but it does not appear to be functional.

Does anyone have any ideas?

Expression:

NNDist( !ToBreak!, !FID_LicTypesZIP!, !MIN_ToBreak!, !NNDist!)

Code Block:

def NNDist(ToBreak, Customer, Minimum, NNDist):
 for Customer:
 for ToBreak:
 if ToBreak>Minimum and ToBreak<NNDist
 return ToBreak
 else:
 return -1

• Richard Fairhurst: The Field Calculator cannot directly reference more than one record at a time. You can use a cursor inside of a field calculation, but when this is all you need to fix in your model, you should export the ModelBuilder tool to a Python script and write the cursor routine in that. You will be able to do much more with a cursor in a Python script than in Field Calculator or ModelBuilder. See my Blog geonet.esri.com/blogs/richard_fairhurst/2014/11/08/… that out perform the Field Calculator by factors of 20 time or more.
• bmeece: Using this method, which field would I use as the ValueDict vs. key value? Is the value dictionary equivalent to the case field in a summary stats table?
• Richard Fairhurst: The dictionary is a container of things you can lookup, just like a dictionary in the real world, The key value is like a word in a dictionary, the thing you look up. The value in the dictionary is the data associated with the key value, just like all of the information you would find about the word you look up in the dictionary. So, compared to Summary Statistics the key is equivalent to the case field in summary stats and the value would equivalent to all of the summary fields. However, the key and value in a dictionary can are capable of holding many other kinds of things.
• Richard Fairhurst: The performance of a cursor and dictionary is somewhat faster than Summary Statistics, but using a dictionary is about 20 times faster than doing a select by attribute against a field in a joined table. The key is equivalent to the field used to create the join.

Answer 1

To find the next highest value in a field, you can use a Search Cursor in your field calculator:

myVals = list()
with arcpy.da.SearchCursor('TestTable', 'MyInt') as cursor:
 for row in cursor: myVals.append(row[0])
def nextHighest(inputField):
 tempList = [a for a in mylist if a > inputField]
 if tempList: return min(tempList)

Expression:

nextHighest(!MyInt!)

enter image description here

Answer 2

field calculator will not store the value. You should use a "da" SearchCursor for that. If you want a solution using desktop tools, you could build a model with summary statistics. It is not very efficient, but it can be done with model builder or a few manual steps:

• run summary stat with your case field

• join table with your summary stat based on the case field

• select by attribute the minimum in each case ("field value" = "minimum of the field" in the joined table

• invere the selection

• run summary stat with your case field on the selected value ==> this will give you the second max.