Using wildcard condition to find mean value from table in ArcPy?

I have a feature class with 20 fields and 13 of them have datas from year 2003 to 2015. I create a new field named 'mean' which will calculate the mean value only for the values from 2003 to 2015. All this fields has a equal sufix named 'ACH'. Here is the problem: I tried many ways to do the mean in the new field, using wildcard, but the script complete with no errors but do not calculate.

It`s not the mean from the column itself. I need to sum all the columns and divide in the new 'mean' fild, with this I will get the mean value for each cell.

What`s the best way to do?

I found some ways, but no one using wildcards.

import arcpy, math

featureclass = arcpy.GetParameterAsText(0)
field_names = [f.name for f in arcpy.ListFields(featureclass, '*ACH*')]

if field_names == '*ACH*':

x = sum([a for a in [0,  !field1!, !field2!, !field3!, !field4!] if a is not None] ) / max([1,len([a for a in [ !field1!, !field2!, !field3!, !field4!] if a is not None] )])

arcpy.CalculateField_management(featureclass, 'mean', '!x!' , "PYTHON_9.3", "")

Here is the table. There is many others fields in the table, but I want to calculate only the mean from the fields ACH_M1S_20XX (XX representes the year) tha preffix is ACH. I only want to calculate the values from all years, and put in a new field called mean.

field1 + field2 + .... + fieldX / field quantities = mean. there is a value for each cell in the line. remember, it`s not the column mean, is the line mean. • It sounds like a cursor in arcpy would be the best. You know what fields are suitable to be considered in the mean so make a list, including the 'mean' field and cursor through the rows. Do you have any part working code to show where you're up to? – Michael Stimson Jul 4 '17 at 22:23
• For questions about ArcPy please always provide a code snippet that illustrates what you have tried so far and where you are stuck. – PolyGeo Jul 4 '17 at 22:25
• I will put my code soon, but I do not want to put a fix field, I want to put the suffix from the fields and after get the mean in a new field. The parameter will be the wildcard – Vinicius Cruvinel Jul 4 '17 at 22:34
• First thing, if field_names == 'ACH' cannot be, field_names is a list and 'ACH' is a string, they cannot be compared. Secondly x, being a python function needs to be put in the code block but still it cannot be called like that. !x! is not a field, unless you have a field called x in your table, and as such is out of scope for field calculator. How about you show me your table with just the fields you're interested in and manually calculate one or two rows so I can get some idea of what you're really attempting to do. – Michael Stimson Jul 5 '17 at 1:37
• I put more details and a imge to help. Many thanks in advance. – Vinicius Cruvinel Jul 5 '17 at 1:50

Your question is a little vague but I think you're asking something like this:

import os, sys, arcpy
TableToCalc = r'd:\your\table.dbf'       # change this to match your table
MeanFields  = ['field1','field2','mean'] # change this to your Real fields
cList       = range(len(MeanFields)-1)   # list of all the fields index except the last
with arcpy.da.UpdateCursor(TableToCalc,MeanFields) as uCur:
for uRow in uCur:
UsedValues  = 0
Accumulator = 0
for Index in cList:
if uRow[Index] != None:
if uRow[Index] > 0: # some condition to decide if this value is 'good enough'
Accumulator += uRow[Index]
UsedValues  += 1
if UsedValues > 0: # if no suitable values then don't calc
uRow[len(MeanFields)-1] = Accumulator/UsedValues # mean of 'good' values
uCur.updateRow(uRow)

This will cursor through your table with an set list of fields and find the mean for each row of all suitable values. Without a sample of your data it's hard to say what condition you will need but, if you're accessing rows from a database, you have to consider the possibility of None () values.

Change the field list MeanFields to match your table fields but remember to have your calculate 'to' field as the last field.

Edit:

Using your edited code you can list the fields to get the names without specifying in the code verbatim:

TableToCalc = arcpy.GetParameterAsText(0)
MeanFields  = [f.name for f in arcpy.ListFields(featureclass, 'ACH*')]
cList       = range(len(MeanFields))
field_names.append('mean') # add the mean field to the end

# then use the previous code
with arcpy.da.UpdateCursor(TableToCalc,MeanFields) as uCur:
for uRow in uCur:
UsedValues  = 0
Accumulator = 0
for Index in cList:
if uRow[Index] != None:
if uRow[Index] > 0: # some condition to decide if this value is 'good enough'
Accumulator += uRow[Index]
UsedValues  += 1
if UsedValues > 0:
uRow[len(MeanFields)-1] = Accumulator/UsedValues # mean of 'good' values
uCur.updateRow(uRow)

I am assuming that the fields are numeric because they have numbers in them.

• Michael, your script runs, but do not show any results. I will update now some code to discuss. – Vinicius Cruvinel Jul 5 '17 at 1:30
• Michael, the code works very well! no 0 or <null> values are been considering, right? many thanks! – Vinicius Cruvinel Jul 5 '17 at 2:24
• Yes, the count of fields is not incremented where the field contains <null> or 0. A value of 0 could be a legitimate value but could also mean 'no data collected', if 0 values are to be considered in the mean comment out the line if uRow[Index] > 0: which will cause the script to increment the count but will still ignore ,null> values. – Michael Stimson Jul 5 '17 at 2:37