Summarize values per row using Python?

Question

Hi,

I am trying to solve a problem with Python and need some advice on how to get started.

My problem is this: 
I have an raster attribute table with an unknown number of fields with similar names, such as AGE1, AGE2, AGE3, ??? ,AGEn. For each row in the table I want to calculate the sum of the values in all the fields starting with AGE and send the sum to a new field, let???s call it TotAGE.

So for example, if I have three fields called AGE*, I want my resulting table to look like this:
RowID1 AGE1 AGE2 AGE3 TotAGE
1      2      4      6      12
2      3      2      2      7

My problem is that the number of AGE-fields varies so I can???t use a static field calculaion like: Totage = AGE1 + AGE2 + AGE3

My original idea was to create a fieldlist using ListFields with a the wildcard AGE*, then open up a searchcursor where I use the fieldlist to catch my relevant fields and finally for each row to summarize the values in the fieldlist and send it to the new field with an insertcursor. Something tells me that???s not best way to approach this???

Any help is appreciated!

Of course, if there???s a way to do this without using Python, that???s fine too.

StefanHaglund1 · Answer

Hi again,

I�??ve now back to this problem again and I am a little bit further ahead. I decided to try the CalculateField approach as wannaz suggested. It is (should be) a much nicer solution but I am running into problems�?�

The code I have is this:

import arcgisscripting
gp = arcgisscripting.create(9.3)

fc = r'C:\bla\blabbla.dbf'
fieldlist = gp.ListFields(fc, "AGE*")

pylist = []

for field in fieldlist:
    fn = field.Name
    fn_format = "!" + fn + "! "
    pylist.append(fn_format)
    CalcList = " + ".join(pylist)
    
Exp = '"' + CalcList + '"'
gp.CalculateField_management(fc, "Tot", Exp)
print Exp

The script executes without errors and printing Exp gives a nice: 
"!AGE1!  + !AGE2!  + !AGE3!  + !AGE4!  + !AGE5!  + !AGE6!  + !AGE7!  + !AGE8!  + !AGE9!  + !AGE10!  + !AGE11!  + !AGE12!  + !AGE13!  + !AGE14! "

I can�??t see anything wrong with that expression, but nothing is written to the table.

If I run a similar expression from the commandline it works, and looks like this: CalculateField_management C:\bla\blabbla.dbf.dbf TOT '!AGE1! + !AGE2!' PYTHON_9.3 #

In the code above, if I switch
Exp = '"' + CalcList + '"'
gp.CalculateField_management(fc, "Tot", Exp)

for

#Fakeexp = "2 + 3 "
#gp.CalculateField_management(fc, "Tot", Fakeexp)

it also works.

Can anyone please advice what I am doing wrong?

ChrisMathers · Answer

I would use a regular expression to scan the list of fields returned and grab the ones that you want, write those out to a new list. Take that and feed it into a for loop to get the values and add em up. I know some people dont like regular expressions though because they can be a big pain.
 
fields=gp.FieldsList(featureclass)
newlist=[]
for field in fields:
    if re.match('^AGE', field)<>None:
        newlist.append(re.match('^AGE', field))
while row:
    value=0
    for field in newlist:
        value+=row.getvalue(field)
    row.Setvalue(newfield, value)
    row.UpdateRow()
    row=cursor.Next()

CedricWannaz · Answer

Now that I read again your first post, .. if you wanted to add/create a field that is the sum of other fields, knowing a common pattern in field names but neither the exact names nor the number of fields, you'd better do it this way:

1. Add/create the field using AddField_management( myFC, myField, 'LONG' ), with myField='AGE_sum' for example and whatever type you need.
2. Extract field names as you just did.
3. Build an expression that sums up (or whatever else you want to compute) all the fields, in the format of the field calculator. For example (using Python syntax and not VBA, but both are possible): expression = '!AGE_1! + !AGE_2! + !AGE_3!'
4. Calculate the new field using CalculateField_management( myFC, myField, expression, 'PYTHON_9.3' )

In my previous answer I assumed that you needed explicitly the values in your script.

Cheers,

Cedric