Using Sum() or something similar with CalculateField_management and FieldLists

Question

I need some help with using variables with CalculateField_management tool. I've tried several different scenarios but don't seem to have any luck.

What I have is a list of fields containing water consumption data for various months.

In my tables I have fields with the following field names: [JUN_12], [JUL_12], [AUG_12], etc... Each table spans 2 years June 2012 - May 2013, the next table will go from June 2013 - May 2014, etc...

These tables are actually views that resides in our billing system. Eventually, I will have another table that will have [JUN_13], [JUL_13] (incremented a year).

I'm trying to avoid hard coding each column name in my code to make it easier to update the code once a year. As these tables are created throughout the years, I'd like to only have to update the yr1 and yr2 parameters and have the script run. This should be the only change from one year / table to the next...

I am fairly new to python and appreciate any help that I can get.

Here's an example of what I have:

#Variables...
yr1 = "08"
yr2 = "09"

sep = "_"

y1 = sep + yr1
y2 = sep + yr2

m1 = "JAN"
m2 = "FEB"
m3 = "MAR"
m4 = "APR"
m5 = "MAY"
m6 = "JUN"
m7 = "JUL"
m8 = "AUG"
m9 = "SEP"
m10 = "OCT"
m11 = "NOV"
m12 = "DEC"

fclass = EnergyGDB + "\\" + PZSPJoin
f1 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m6,y1))][0]
f2 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m7,y1))][0]
f3 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m8,y1))][0]
f4 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m9,y1))][0]
f5 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m10,y1))][0]
f6 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m11,y1))][0]
f7 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m12,y1))][0]
f8 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m1,y2))][0]
f9 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m2,y2))][0]
f10 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m3,y2))][0]
f11 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m4,y2))][0]
f12 = [str(f.name) for f in arcpy.ListFields(fclass, "{0}{1}".format(m5,y2))][0]

fieldList = [f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12]
fields = ",".join("!{0}!".format(x) for x in fieldList)

#Calculation:
arcpy.CalculateField_management(PZSPJoin, "TotalCCF", "sum([{0}])".format(fields), "PYTHON", "")

There are a few other things that are happening in between the calculation and the variables, however this is where I'm having the issue. I'm trying to add all of the fields and have a total in a new field.

If I replace the variables with the actual field names, the script works just fine; but as soon as I introduce variables, it breaks.

Any help would be greatly appreciated. Thanks!

Answer 1

When you are using VB as your parser for Calculate field, the field names must be enclosed in brackets []. (For Python as the parser, field names are enclosed in exclamation points !!) See the help for Calculate Field.

So, in your example, an easy way to code it would be thus, using str.format():

arcpy.CalculateField_management(table, "field", 
                        "[{0}] + [{1}] + [{2}]".format(field1, field2, field3), "VB", "")

This can become a bit unwieldy when you are summing up a lot of fields, so the following is a good alternative:

fieldlist = [field, field2, field3, etc.]
fields = ",".join("!{0}!".format(x) for x in fieldlist)

arcpy.CalculateField_management(table, "field", 
                                "sum([{0}])".format(fields), "PYTHON", "")

The same can be applied to your ListFields() to make that a bit simpler (and easier to understand in my opinion). You need to cast to string since ListFields() returns unicode. If you are only returning one field at a time, adding [0] (which just accesses the first item in the list) will return a string:

field1 = [str(f.name) for f in arcpy.ListFields(fclass, "*{0}_{1}*".format(m1,yr1))][0]