# Is there a faster way in python of finding the smallest number in a field?

Using arcgis desktop 10.3.1 I have a script which uses a search cursor to append values to a list and then use min() to find the smallest integer. The variable is then used in a script. The Feature class has 200,000 rows and the script takes a very long time to complete. Is there a way to do this quicker? At the moment I think I would just do it by hand rather than write a script due to the length of time it takes.

import arcpy
fc = arcpy.env.workspace = arcpy.GetParameterAsText(0)
Xfield = "XKoordInt"
cursor = arcpy.SearchCursor(fc)
ListVal = []
for row in cursor:
ListVal.append(row.getValue(Xfield))
value = min(ListVal)-20
print value
expression = "(!XKoordInt!-{0})/20".format(value)
arcpy.CalculateField_management (fc, "Matrix_Z" ,expression, "PYTHON")

• I think there is a faster no-Python way to do this that you seemed to be working on at gis.stackexchange.com/q/197873/115 – PolyGeo Jun 13 '16 at 20:30
• Any reason why you are not using arcpy.Statistics_analysis? desktop.arcgis.com/en/arcmap/10.3/tools/analysis-toolbox/… – Berend Jun 14 '16 at 8:32
• Yes. I have to start somewhere and have to only very rarely have do any programming with arcpy. It is fantastic that so many people are able to suggest so many approaches. This is the best way to learn new things. – Robert Buckley Jun 14 '16 at 13:55
• min_val = min([i[0] for i in arcpy.da.SearchCursor(fc,Xfield)]) – BERA Mar 21 '18 at 13:25

I can see several things that may be causing your script to be slow. The thing that is likely being very slow is the arcpy.CalculateField_management() function. You should use a cursor, it will by several magnitudes faster. Also, you said you are using ArcGIS Desktop 10.3.1, but you're using the old ArcGIS 10.0 style cursors, which are also much slower.

The min() operation even on a a list of 200K will be pretty quick. You can verify this by running this small snippet; it happens in the blink of an eye:

>>> min(range(200000)) # will return 0, but is still checking a list of 200,000 values very quickly


See if this is any faster:

import arcpy
fc = arcpy.env.workspace = arcpy.GetParameterAsText(0)
Xfield = "XKoordInt"
with arcpy.da.SearchCursor(fc, [Xfield]) as rows:
ListVal = [r[0] for r in rows]

value = min(ListVal) - 20
print value

# now update
with arcpy.da.UpdateCursor(fc, [Xfield, 'Matrix_Z']) as rows:
for r in rows:
if r[0] is not None:
r[1] = (r[0] - value) / 20.0
rows.updateRow(r)


EDIT:

I ran some timing tests and as I suspected, the field calculator took almost twice as long as the new style cursor. Interestingly, the old style cursor was ~3x slower than the field calculator. I created 200,000 random points and used the same field names.

A decorator function was used to time each function (may be some slight overhead in the setup and tear down of functions, so maybe the timeit module would be a little more accurate to test snippets).

Here are the results:

Getting the values with the old style cursor: 0:00:19.23
Getting values with the new style cursor: 0:00:02.50
Getting values with the new style cursor + an order by sql statement: 0:00:00.02

And the calculations:

field calculator: 0:00:14.21
old style update cursor: 0:00:42.47
new style cursor: 0:00:08.71


And here is the code I used (broke everything down to individual functions to use the timeit decorator):

import arcpy
import datetime
import sys
import os

def timeit(function):
"""will time a function's execution time
Required:
function -- full namespace for a function
Optional:
args -- list of arguments for function
kwargs -- keyword arguments for function
"""
def wrapper(*args, **kwargs):
st = datetime.datetime.now()
output = function(*args, **kwargs)
elapsed = str(datetime.datetime.now()-st)[:-4]
if hasattr(function, 'im_class'):
fname = '.'.join([function.im_class.__name__, function.__name__])
else:
fname = function.__name__
print'"{0}" from {1} Complete - Elapsed time: {2}'.format(fname, sys.modules[function.__module__], elapsed)
return output
return wrapper

@timeit
def get_value_min_old_cur(fc, field):
rows = arcpy.SearchCursor(fc)
return min([r.getValue(field) for r in rows])

@timeit
def get_value_min_new_cur(fc, field):
with arcpy.da.SearchCursor(fc, [field]) as rows:
return min([r[0] for r in rows])

@timeit
def get_value_sql(fc, field):
"""good suggestion to use sql order by by dslamb :) """
wc = "%s IS NOT NULL"%field
sc = (None,'Order By %s'%field)
with arcpy.da.SearchCursor(fc, [field]) as rows:
for r in rows:
# should give us the min on the first record
return r[0]

@timeit
def test_field_calc(fc, field, expression):
arcpy.management.CalculateField(fc, field, expression, 'PYTHON')

@timeit
def old_cursor_calc(fc, xfield, matrix_field, value):
wc = "%s IS NOT NULL"%xfield
rows = arcpy.UpdateCursor(fc, where_clause=wc)
for row in rows:
if row.getValue(xfield) is not None:

row.setValue(matrix_field, (row.getValue(xfield) - value) / 20)
rows.updateRow(row)

@timeit
def new_cursor_calc(fc, xfield, matrix_field, value):
wc = "%s IS NOT NULL"%xfield
with arcpy.da.UpdateCursor(fc, [xfield, matrix_field], where_clause=wc) as rows:
for r in rows:
r[1] = (r[0] - value) / 20
rows.updateRow(r)

if __name__ == '__main__':
Xfield = "XKoordInt"
Mfield = 'Matrix_Z'
fc = r'C:\Users\calebma\Documents\ArcGIS\Default.gdb\Random_Points'

# first test the speed of getting the value
print 'getting value tests...'
value = get_value_min_old_cur(fc, Xfield)
value = get_value_min_new_cur(fc, Xfield)
value = get_value_sql(fc, Xfield)

print '\n\nmin value is {}\n\n'.format(value)

# now test field calculations
expression = "(!XKoordInt!-{0})/20".format(value)
test_field_calc(fc, Xfield, expression)
old_cursor_calc(fc, Xfield, Mfield, value)
new_cursor_calc(fc, Xfield, Mfield, value)



And finally, this is what the actual print out was from my console.

>>>
getting value tests...
"get_value_min_old_cur" from <module '__main__' from 'C:/Users/calebma/Desktop/speed_test2.py'> Complete - Elapsed time: 0:00:19.23
"get_value_min_new_cur" from <module '__main__' from 'C:/Users/calebma/Desktop/speed_test2.py'> Complete - Elapsed time: 0:00:02.50
"get_value_sql" from <module '__main__' from 'C:/Users/calebma/Desktop/speed_test2.py'> Complete - Elapsed time: 0:00:00.02

min value is 5393879

"test_field_calc" from <module '__main__' from 'C:/Users/calebma/Desktop/speed_test2.py'> Complete - Elapsed time: 0:00:14.21
"old_cursor_calc" from <module '__main__' from 'C:/Users/calebma/Desktop/speed_test2.py'> Complete - Elapsed time: 0:00:42.47
"new_cursor_calc" from <module '__main__' from 'C:/Users/calebma/Desktop/speed_test2.py'> Complete - Elapsed time: 0:00:08.71
>>>


Edit 2: Just posted some updated tests, I found a slight flaw with my timeit function.

• r[0] = (r[0] - value) / 20.0 TypeError: unsupported operand type(s) for -: 'NoneType' and 'int' – Robert Buckley Jun 13 '16 at 14:52
• That just means you have some null values in your "XKoordInt". See my edit, all you have to do is skip nulls. – crmackey Jun 13 '16 at 15:12
• Be careful with range. ArcGIS still uses Python 2.7, so it returns a list. But in 3.x, range is its own special kind of object which may have optimizations. A more reliable test would be min(list(range(200000))), which would ensure you're working with a plain list. Also consider using the timeit module for performance testing. – jpmc26 Jun 13 '16 at 23:49
• You could probably gain some more time by using sets rather than lists. That way you aren't storing duplicate values, and you're searching on unique values only. – Fezter Jun 14 '16 at 0:23
• @Fezter It depends on the distribution. There would have to be enough exact duplicates to outweigh the cost of hashing all the values and checking if each one is in the set during construction. E.g., if only 1% is duplicated, it's probably not worth the cost. Also note that if the value is floating point, there are unlikely to be many exact duplicates. – jpmc26 Jun 14 '16 at 4:25

As @crmackey points out, the slow portion is probably due to the calculate field method. As an alternative to the other suitable solutions, and assuming you are using a geodatabase to store your data, you could use the Order By sql command to sort in ascending order before doing the update cursor.

start = 0
Xfield = "XKoordInt"
minValue = None
wc = "%s IS NOT NULL"%Xfield
sc = (None,'Order By %s'%Xfield)
with arcpy.da.SearchCursor(fc, [Xfield],where_clause=wc,sql_clause=sc) as uc:
for row in uc:
if start == 0:
minValue = row[0]
start +=1
row[0] = (row[0] - value) / 20.0
uc.updateRow(row)


In this case the where clause removes the nulls before doing the query, or you can use the other example which checks for None before updating.

• Nice! Using the order by as ascending and grabbing the first record will definitely be faster than getting all the values and then finding the min(). I'll include this in my speed tests as well to show the performance gain. – crmackey Jun 13 '16 at 15:50
• I'll be curious to see where it ranks. I wouldn't be surprised if the extra sql operations make it slow. – dslamb Jun 13 '16 at 15:53
• timing benchmarks have been added, see my edit. And I think you were correct, the sql seemed to add some extra overhead but it did out perform the cursor that steps through the whole list by 0.56 seconds, which is not as much of a performance gain as I would have expected. – crmackey Jun 13 '16 at 16:29

You can also use numpy in cases like this, although it will be more memory intensive.

You'll still get a bottle neck when loading the data to a numpy array and then back to the datasource again, but I've found that the performance difference is better (in numpy's favor) with larger data sources, especially if you need multiple statistics/calculations.:

import arcpy
import numpy as np
fc = arcpy.env.workspace = arcpy.GetParameterAsText(0)
Xfield = "XKoordInt"

allvals = arcpy.da.TableToNumPyArray(fc,['OID@',Xfield])
value = allvals[Xfield].min() - 20

print value

newval = np.zeros(allvals.shape,dtype=[('id',int),('Matrix_Z',int)])
newval['id'] = allvals['OID@']
newval['Matrix_Z'] = (allvals[Xfield] - value) / 20

arcpy.da.ExtendTable(fc,'OBJECTID',newval,'id',False)


Why not sort the table ascending, then use a search cursor to grab the value for the first row? http://pro.arcgis.com/en/pro-app/tool-reference/data-management/sort.htm

import arcpy
workspace = r'workspace\file\path'
arcpy.env.workspace = workspace

input = "input_data"
sort_table = "sort_table"

arcpy.Sort_management (input, sort_table, sort_field)

min_value = 0

count= 0
witha arcpy.da.SearchCursor(input, [sort_field]) as cursor:
for row in cursor:
count +=1
if count == 1: min_value +=row[0]
else: break
del cursor


I would wrap the SearchCursor in a generator expression (i.e. min()) for both speed and succinctness. Then incorporate the minimum value from the generator expression in a da type UpdateCursor. Something like the following:

import arcpy

fc = r'C:\path\to\your\geodatabase.gdb\feature_class'

minimum_value = min(row[0] for row in arcpy.da.SearchCursor(fc, 'some_field')) # Generator expression

with arcpy.da.UpdateCursor(fc, ['some_field2', 'some_field3']) as cursor:
for row in cursor:
row[1] = (row[0] - (minimum_value - 20)) / 20 # Perform the calculation
cursor.updateRow(row)


In your loop you have two function references which are revaluated for each iteration.

for row in cursor: ListVal.append(row.getValue(Xfield))

It should be faster(but a bit more complex) to have the references outside of the loop:

getvalue = row.getValue
append = ListVal.append

for row in cursor:
append(getvalue(Xfield))

• Wouldn't this actually slow it down? You are actually creating a new separate reference for the builtin append() method of the list datatype. I don't think this is where his bottleneck is happening, I would bet money the calculate field function is the culprit. This can be verified by timing the field calculator vs a new style cursor. – crmackey Jun 13 '16 at 15:24
• actually i would be interested in the timings as well :) But it is an easy replace in the original code and therefore fast checked. – Matte Jun 13 '16 at 15:27
• I know I did some benchmark tests a while back on cursors vs field calculator. I'll do another test and report my findings in my answer. I think it would also be good to show old vs new cursor speed too. – crmackey Jun 13 '16 at 15:32