Inserting row into table and then updating it in sum of each column using ArcPy?

Question

My problem is the following:

• I have a table with an unknown number of columns (n)

• Use arcpy.ListFields create a list of columns and generates Field name list

• Then, using arcpy.da.InsertCursor create a new row at the end of the table ...

for example

 fieldNameList = [] 
 fields = arcpy.ListFields(fc, "*")
 for field in fields:
 if field.type in ("Double", "Integer", "Single"): 
 fieldNameList.append(field.name) 
 cur = arcpy.da.InsertCursor(fc, fieldNameList)
 for x in xrange(-1, 0):
 cur.insertRow((SUM_FIELD1, SUM_FIELD2...SUM_FIELD n )) ?????????!!!!
 del cur

I do not know how to calculate the sum for each column and then the result to update in the created row. The sum should be separately calculated for each column ...

• Michael Stimson: Is it the sum of the preceding values? You might need to use a search cursor to get those values and sum them.
• Vince: Your logic is a bit off. You'd be better off calculating the sums via a arcpy.da.SearchCursor query, then once you have the sums bundled in a dictionary by column name, insert a row with the sums.
• Michael Stimson: That's how I'd do it Vince. Perhaps you could put that in as an answer.
• Vince: @MichaelMiles-Stimson Feel free to run with it (I'm on a code deadline)
• kamfulebu: Yes, the sum of the previous value. I am an absolute beginner in using python. Can you give me an example? thank you

Answer 1

I don't use Dictionaries (I don't fully understand them), however the new row can also be a list. Start by compiling a list of the sums and then insert that.

fieldNameList = []
values = [] # store the sum values here.
fields = arcpy.ListFields(fc, "*")
# get the OID/FID field name to skip
desc = arcpy.Describe(fc)
if desc.hasOID:
 OIDname = desc.OIDFieldName.upper()
else:
 OIDname = ""
for field in fields:
 if field.name.upper() != OIDname: # skip the OID/FID field.
 if field.type in ("Double", "Integer", "Single"):
 # sum each suitable field, but not the NULL ones - they would be bad
 with arcpy.da.SearchCursor(fc,field.name,field.name + " is not NULL") as sCur:
 thisValue = 0
 for row in sCur:
 thisValue += row[0]
 values.append(thisValue) # this will be the inserted row
 fieldNameList.append(field.name) 
with arcpy.da.InsertCursor(fc, fieldNameList) as cur:
 cur.insertRow(values)

This does of course mean that you'll read through the rows for each numeric field.. but that also includes OID/FID - that's bad! the values for OID/FID can't be modified so best to skip that one. Also shape_area, shape_length are read-only but this looks like tabular data so I wont worry about that (this time).

• kamfulebu: That's it! Thank you very much @Michael Miles-Stimson
• PolyGeo: For followup questions I think you should ask a new question.
• Vince: I think this has left the realm of GIS and entered Spreadsheets 101.
• Michael Stimson: That's always an option @Vince, export table to an Excel format (csv, Excel, txt) and do the calc there. I'd be the first to admit to doing that - after all isn't why I have Excel installed? I'm with PolyGeo, I think that a different calc, especially one that is so fundamentally different, deserves a fresh question. Refer to this post in the new question for background information.
• kamfulebu: Thank you guys. Of course, this problem is easily solved in Excel but I'm learning the basics of programming in arcpy and python, trying to understand some of the basics.