Field calculator calculate mean values of multiple rows when some values are Nulls

Question

Original User: AnneKull

Hello!

I need to calculate average value of 7 fields (Index2006, Index2007, Index2008, Index2009, Index2010, Index2011, Index2012) to field IlvesAve (see illustration). 
[ATTACH=CONFIG]29714[/ATTACH]
The problem is that I have Null values in many rows. 0 values are real counting data, while Null values indicate the missing data. Null values can't be taken into account otherwise the results are incorrect. I can calculate sum value of indexes:

Code:

def stack(item1,item2,item3,item4,item5,item6,item7):
  itemList = [item1,item2,item3,item4,item5,item6,item7]
  myList = [item for item in itemList if (item != None)]
  return count(myList)

Expression:
stack ( !Index2006! , !Index2007! , !Index2008! , !Index2009! , !Index2010! , !Index2011! , !Index2012! )
[ATTACH=CONFIG]29715[/ATTACH]

But I do not know how to calculate mean or count. I cannot divide sum value with a constant because there are always different number of fields with non Null values that need to be taken into account. Can anyone help me?

Best regards
Anne

calc.png

tabel.png

qfArZd9x9q.png

visible-rows.PNG

ChrisPedrezuela · Answer

Here is a sample code for the field calculator:
def mean(field1, field2, field3):
  fieldList = [field1, field2, field3]
  validList = []
  for i in fieldList:
    if i != None:
      validList.append(i)
  meanVal = sum(validList)/len(validList)
  return meanVal

Hi Xavier! I think importing numpy for this case is a bit of a stretch since youll have to load numpy everytime you iterate thru the rows.

Hows Davao by the way or are you in Manila?

regards,
Chris P.

Anonymous User · Answer

Original User: xfuentes

I had trouble running this code so I thought I should share what I did to make this running. I am using ArcGIS 10.1.

Pre-Logic Script Code:

import numpy
def mean(fields):
    return numpy.mean([f for f in fields if f != None])

Expression (selected field in ArcMap Table):

mean([ !Index2006! , !Index2007! , !Index2008! , !Index2009! , !Index2010! , !Index2011! , !Index2012! ])

AnneKull1 · Answer

Hi Simon,

Thank you very much. These scripts work well.

Best regards,
Anne