I have a .dbf with about 50 text fields containing numerals, I would like to convert those to integers and then sum them all in a new double field I created, is there an easy field calculation that could do this. I have working code but wanted to see other peoples examples/thoughts.
Pre-Logic Script Code:
def addition(*args):
my_sum = 0
for i in args:
my_sum += int(i)
return my_sum
prior2_3 = (my new field):
addition( !Hits_07_21!, !Hits_07_22!, !Hits_07_23!, !Hits_07_24!, !Hits_07_25!, !Hits_07_26!, !Hits_07_27!, !Hits_07_28!, !Hits_07_29!, !Hits_07_30!, !Hits_07_31!, !Hits_08_01!, !Hits_08_02!, !Hits_08_03!, !Hits_08_04!, !Hits_08_05!, !Hits_08_06!, !Hits_08_07!, !Hits_08_08!, !Hits_08_09!, !Hits_08_10!, !Hits_08_11!, !Hits_08_12!, !Hits_08_13!, !Hits_08_14!, !Hits_08_15!, !Hits_08_16!, !Hits_08_17!, !Hits_08_18!, !Hits_08_19!, !Hits_08_20!, !Hits_08_21!, !Hits_08_22!, !Hits_08_23!, !Hits_08_24!, !Hits_08_25!, !Hits_08_26!, !Hits_10_08!, !Hits_10_09!, !Hits_10_10!, !Hits_10_11!, !Hits_10_13!, !Hits_10_14!, !Hits_10_15!, !Hits_10_16!, !Hits_10_17!, !Hits_10_18!, !Hits_10_19!, !Hits_10_20!, !Hits_10_21!, !Hits_10_22!, !Hits_10_23!, !Hits_10_24!, !Hits_10_25!, !Hits_10_26!, !Hits_10_27!, !Hits_10_28!, !Hits_10_29!, !Hits_11_02!, !Hits_11_03!, !Hits_11_04!, !Hits_11_05!, !Hits_11_06!, !Hits_11_07!, !Hits_11_08!, !Hits_11_09!, !Hits_11_10!, !Hits_11_11!, !Hits_11_12!, !Hits_11_13!, !Hits_12_01!, !Hits_12_02!, !Hits_12_03!, !Hits_12_10!, !Hits_12_11!, !Hits_12_13!, !Hits_12_14!, !Hits_12_15!, !Hits_12_16!, !Hits_12_17!, !Hits_12_18!, !Hits_12_19!, !Hits_12_20!, !Hits_12_21!, !Hits_12_23!, !Hits_12_24!, !Hits_12_25!, !Hits_12_26!, !Hits_12_27!, !Hits_12_28!, !Hits_12_29!, !Hits_12_30!, !Hits_12_31!, !Hits_01_01!, !Hits_01_02!, !Hits_01_03!, !Hits_01_04!, !Hits_01_05!, !Hits_01_06!, !Hits_01_07!, !Hits_01_08!, !Hits_01_11!, !Hits_01_13!, !Hits_01_15!, !Hits_01_16!, !Hits_01_17!, !Hits_01_19!, !Hits_01_20!, !Hits_01_21!, !Hits_01_22!, !Hits_01_23!, !Hits_01_24!, !Hits_01_25!, !Hits_01_26!, !Hits_01_27!, !Hits_01_28!, !Hits_01_29!, !Hits_01_30!, !Hits_01_31!, !Hits_02_01!, !Hits_02_02!, !Hits_02_03!, !Hits_04_29!)
If you're open to bypassing the field calculator completely, you can do this with an Update Cursor and plain old Arcpy.
>>> fc = "points" # your feature layer ... myField = "prior2_3" # the field name ... fieldList = [] # empty list ... fieldList.append(myField ) # add the known field name to the list ... hitsFields = arcpy.ListFields(fc,"Hits*") # find all the fields starting with "Hits" ... for hitsField in hitsFields: ... fieldList.append(hitsField.name) # add the hits field names to the list ... with arcpy.da.UpdateCursor(fc,fieldList) as cursor: ... for row in cursor: ... sum = 0 ... for field in range(1,len(fieldList)): # loop through all hits fields ... sum += int(row[field]) # add the values ... row[0] = sum # store the value in the known field, first in the list ... cursor.updateRow(row) # update the field
Alternatively, you can do the following in the field calculator. I'm not sure what the performance implications of creating a one-row Search Cursor for every row in your data would be, but it works.
def addition(fid): fc = "points" myField = "prior2_3" fieldList = [] fieldList.append(myField ) hitsFields = arcpy.ListFields(fc,"Hits*") for hitsField in hitsFields: fieldList.append(hitsField.name) with arcpy.da.SearchCursor(fc,fieldList,' "FID" = ' + str(fid)) as cursor: for row in cursor: sum = 0 for field in range(1,len(fieldList)): sum += int(row[field]) return sum
the expression would be:
addition(!FID!)
You should really consider converting you table to a numpy array then you can process the data quite simply. For example. I don't have ArcMap installed on this iThingy, but you should examine:
TableToNumPyArray ArcGIS Help (10.2, 10.2.1, and 10.2.2)
Then you could replace my 'fields' variable with a listFields on your table, skip the seed to get array 'a', convert it to an integer array as in 'dt', do the sum (array c) then whatever. The principles are demonstrated here.
I prefer numpy arrays when working with multiple columns for pretty much anything, they are are often a good substitute for cursors and even can be combined with them. So just a thought as to the possibilities.
import numpy as np fields = ['a','b','c','d','e'] seed = [['1','2','3','4','5'], ['2','3','4','5','1'], ['2','3','4','5','2']] a = np.array(seed) dt = np.dtype('int') b = np.asarray(a,dtype=dt) c = np.sum(b,axis=0) print('Data as text:\n{}'.format(a)) print('Data as int:\n{}'.format(b)) print('Data sum by column:\n{}'.format(c))
Output for the above example
Data as text: [['1' '2' '3' '4' '5'] ['2' '3' '4' '5' '1'] ['2' '3' '4' '5' '2']] Data as int: [[1 2 3 4 5] [2 3 4 5 1] [2 3 4 5 2]] Data sum by column: [ 5 8 11 14 8]
Here's my crack at this, using the magic of Python list comprehensions.
Here's my version of the arcpy approach:
arcpy.AddField_management(tbl, "FSUM", "DOUBLE") sfields = [f.name for f in arcpy.ListFields(tbl, "Hits*")] with arcpy.da.updateCursor(tbl, ["FSUM"] + sfields]) as rows: for row in rows: row[0] = sum([float(x) for x in row[1:]]) rows.updateRow(row)
And here's how that would look using Field Calculator:
Code block:
def addition(*args): return sum([float(x) for x in args])
Expression:
addition(!Hits_07_21!, ..........)