Field Calculator convert text to integer then populate new field with sum

MatthewRice · ‎04-30-2015

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!)

DarrenWiens2 · ‎04-30-2015

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

DarrenWiens2 · ‎04-30-2015

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!)

DanPatterson_Retired · ‎04-30-2015

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]

curtvprice · ‎04-30-2015

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!,  ..........)