2

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!

2

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]
  • Thanks for your help Paul. I've attempted to apply your recommendations, but am still experiencing an error. In the CalculateField section, I'm getting a Invalid field [] error. – EugeneDurshpek Jul 29 '13 at 19:03
  • @EugeneDurshpek Can you edit your OP to reflect the new code? – Paul Jul 29 '13 at 19:24
  • 1
    @EugeneDurshpek, when the response doesn't fit in a comment (e.g. because you need code formatting) the convention is to amend (edit) the question instead of the other person's answer. Another approach is to leave a comment and link to a chat room created for exploring the Q (and then summarize the results of that chat back here later). – matt wilkie Jul 29 '13 at 19:26
  • ...like here: chat.stackexchange.com/rooms/9871/… – matt wilkie Jul 29 '13 at 19:28
  • @Paul I'm unable to chat in the chat room. It's telling me that I must have a reputation of at lease 20 to talk there. I'm new to this site; as you can tell. I thought that I edited the original post, but must have done it wrong. Paul, I added arcpy.AddMessage(fields) as you mentioned in the chat, and came up with None as the result. – EugeneDurshpek Jul 29 '13 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.