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

5776
4
12-10-2013 04:31 AM
by Anonymous User
Not applicable
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
0 Kudos
4 Replies
by Anonymous User
Not applicable
Original User: Pieman86

Hi Anne,

In the Python codeblock, you can define functions like the following:

def count(fields):
    return len([f for f in fields if f != None])

def sum_value(fields):
    return sum([f for f in fields if f != None])

def mean(fields):
    return None if count(fields) == 0 else float(sum_value(fields))/count(fields)


Then calculate your expression using the following:

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

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

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

Note the use of an array ( [ ] ) to pass in your fields.
0 Kudos
AnneKull1
New Contributor
Hi Simon,

Thank you very much. These scripts work well.

Best regards,
Anne
0 Kudos
by Anonymous User
Not applicable
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! ])
0 Kudos
ChrisPedrezuela
Occasional Contributor III
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.
0 Kudos