2

I have a pivot table that has 11 ShipTypeGroup fields with "Long" field properties that can't be divided by 12, due to decimal places. Therefore I am trying to create a new field, copy attributes across from each ShipTypeGroup and recalculate. Each ShipTypeGroup must be recalculated into a field that has the same ShipTypeGroup. So for example, adding a column called Ave_STG_1 and then calculating the figures from ShipTypeGroup1 field and so on for all 10 types. However when it throws up an error or doesn't calculate the correct fields properly. I'm not sure if I have passed the newly created field into the "Calculate Field" part of the script properly, or do I add an if/else statement.

import arcpy


pt = r"C:\AIS\Density_Grid_test.gdb\PivotTable"
flist = arcpy.ListFields(pt, "*ShipTypeGroup*")
for f in flist:
    arcpy.AddField_management(pt, f.name.replace("ShipTypeGroup","Ave_STG"),"DOUBLE")
    newflist = arcpy.ListFields(pt, "*Ave_STG*")
    for nf in newflist:
        arcpy.CalculateField_management(pt, nf.name, "!{}!/12".format(f.name), "PYTHON_9.3")
        print f.name + " changed into " + nf.name + " and recalculated"
  • do not work with embedded loops. store your new name in a variable then use it for addfield and calculatefield within the same loop. – radouxju Apr 26 '16 at 14:05
2

Why not just do the following instead of doing it under a nested loop:

import arcpy

pt = r"C:\AIS\Density_Grid_test.gdb\PivotTable"
arcpy.AddField_management(pt, "Ave_STG", "DOUBLE")
arcpy.CalculateField_management(pt, "Ave_STG", "!ShipTypeGroup! / 12", "PYTHON_9.3")

or you could try doing it with an UpdateCursor

import arcpy
pt = r"C:\AIS\Density_Grid_test.gdb\PivotTable"
arcpy.AddField_management(pt, "Ave_STG", "DOUBLE")
with arcpy.da.UpdateCursor(pt, ['ShipTypeGroup', 'Ave_STG']) as cursor:
for row in cursor:
    try:
        row[1] = row[0] / 12
        cursor.updateRow(row)
    except TypeError, e:
        print e.message
        continue
  • 1
    Hi, i should have been explicit. I have over 11 ShipTypeGroup fields, and they all need calculating. I have edited the question now to make it more explicit. – MacroZED Apr 26 '16 at 13:57
  • Thanks for the clarification. Could you tell me what error you are getting? @Zahir_Ibrahim – freshhmints Apr 26 '16 at 14:04
  • Then you must use a cursor, since the field calculator is a waste of time since it can only edit one field at a time while a cursor can edit all 11 fields in one pass. The da cursor will be 50 times faster if you do all 11 fields as you update each row. – Richard Fairhurst Apr 26 '16 at 14:05
2

You can try something like this (see also @radouxju's comment):

import arcpy
pt = r"C:\AIS\Density_Grid_test.gdb\PivotTable"
flist = arcpy.ListFields(pt, "*ShipTypeGroup*")
for f in flist:
    arcpy.AddField_management(pt, f.name.replace("ShipTypeGroup","Ave_STG"),"DOUBLE")
newflist = arcpy.ListFields(pt, "*Ave_STG*")
for nf in newflist:
    arcpy.CalculateField_management(pt, nf.name, "!{}!/12".format(f.name), "PYTHON_9.3")
    print f.name + " changed into " + nf.name + " and recalculated"

This way you first loop to make sure that all the fields are added first. Then you get the list of field names that hopefully match your wildcard. Finally you loop through that list and calculate fields.

If you've done this a few times already, you may be getting errors about a field existing. So before you add the fields you can delete the new ones to clear them out.

http://pro.arcgis.com/en/pro-app/tool-reference/data-management/delete-field.htm

import arcpy
pt = r"C:\AIS\Density_Grid_test.gdb\PivotTable"
newflist = arcpy.ListFields(pt, "*Ave_STG*")
arcpy.DeleteField_Management(pt,newflist)
flist = arcpy.ListFields(pt, "*ShipTypeGroup*")
for f in flist:
    arcpy.AddField_management(pt, f.name.replace("ShipTypeGroup","Ave_STG"),"DOUBLE")
newflist = arcpy.ListFields(pt, "*Ave_STG*")
for nf in newflist:
    arcpy.CalculateField_management(pt, nf.name, "!{}!/12".format(f.name), "PYTHON_9.3")
    print f.name + " changed into " + nf.name + " and recalculated"

Edit

Based on your comment below @Zahir_Ibrahim I think you need a way to loop through the groups first, then add the fields, then use an update cursor or use calculate field. Your comment makes me think you have a number as a suffix to designate the group. If the numbers are sequential you could try this, continuing to use the field calculator:

for i in range(1,10):
    flist = arcpy.ListFields(pt, "*ShipTypeGroup_%s"%i)
    for f in flist:
        arcpy.AddField_management(pt, f.name.replace("ShipTypeGroup","Ave_STG"),"DOUBLE")
    newflist = arcpy.ListFields(pt, "*Ave_STG_%s"%i)
    for nf in newflist:
        arcpy.CalculateField_management(pt, nf.name, "!{}!/12".format(f.name), "PYTHON_9.3")
        print f.name + " changed into " + nf.name + " and recalculated"
  • Thank you for your answer. The issue is that each ShipTypeGroup must be recalculated into a field that has the same ShipTypeGroup. So for example, Im adding a column for ShipTypeGroup1 and renaming to Ave_STG_1. Now when Im doing my calculation, it must be on the figures from the same ShipTypeGroup, so in this instance 1. This is the issue for all 10 ShipTypeGroups. – MacroZED Apr 27 '16 at 7:40
  • @Zahir_Ibrahim see the edits for one option. – dslamb Apr 27 '16 at 18:14
0

I managed to get this to work using the following script:

#identify the dataset
dg = r"path/to/dataset"

#create an average variable from a float_days input parameter - which converts the days into a week
average = float_days/7.0

#identifies the shiptypegroup fields in the dataset
fn = [field.name for field in arcpy.ListFields(dg, "*ShipTypeGroup*")]

#for field in fieldnames list:
for fname in fn:

#create a new field name variable based on the shiptypegroup fieldname  - replace ShipTypeGroup with Ave_STG        
    newfname = fname.replace("ShipTypeGroup", "Ave_STG_")

#create a new field with the above name
    arcpy.AddField_management(dg, newfname, "DOUBLE")

#find that new field just created
    newfield = [field.name for field in arcpy.ListFields(dg, "{}".format(newfname))][0]

#create an expression to calculate that field based on the the shiptypegroup value divided by the average variable created above which gives the weekly average
    expression = "!{}!/{}".format(fname, average)

#calculate the newly created average field using the expression above
    arcpy.CalculateField_management(dg, newfield , expression, "PYTHON")

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.