topic Re: Field Calculator convert text to integer then populate new field with sum in Python Questions

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

MatthewRice — Sun, 12 Dec 2021 01:03:26 GMT

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

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

DarrenWiens2 — Sun, 12 Dec 2021 01:03:28 GMT

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

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

DarrenWiens2 — Sun, 12 Dec 2021 01:03:31 GMT

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

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

DanPatterson_Retired — Sun, 12 Dec 2021 01:03:34 GMT

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]

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

curtvprice — Sun, 12 Dec 2021 01:03:37 GMT

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