nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to field calculate the mean of a field array?

LukeHaskett

Hello,

I'm stumped by a problem that I think should have a fairly straightforward answer...

I have a few hundred features with a number of attributes, all numerical. Some features have all attributes filled in, but some have a handful of null values. Is there any way of calculating the mean value for each feature that takes into account these null values? For instance, it calculates how many attributes are not null for each feature and uses that to generate the mean?

Would really appreciate any help on this!

Thanks

Vind meer berichten getagd met

Accepted answers

All comments

DanPatterson_Retired

what is the source type of your data? Featureclass tables? dbase? excel?

And I assume you want to automate the whole process rather than doing the process table by table and field by field.

You could use numpy, and convert the tables to numpy arrays and do the columns all at once.

<SPAN class="comment token">#..... stuff before includes FeatureclassToNumPyArray or TabletoNumPyArray</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to get the table into an array... Then use nanmean to the mean</SPAN>
b
Out<SPAN class="punctuation token">[</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN> 
array<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">[</SPAN> nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">5</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">5</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">[</SPAN> nan<SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">8</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">6</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">[</SPAN>&nbsp; <SPAN class="number token">5</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">7</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">4</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">7</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">[</SPAN> nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">9</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">9</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">[</SPAN> nan<SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">4</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">9</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">6</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">[</SPAN> nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">4</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; nan<SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">6</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="number token">5</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

np<SPAN class="punctuation token">.</SPAN>nanmean<SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">,</SPAN> axis<SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN>
Out<SPAN class="punctuation token">[</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN> array<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN> <SPAN class="number token">5</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="number token">7</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="number token">5.75</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="number token">9</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="number token">6.33333333</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="number token">5</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Then you could cycle through the tables and get the mean

PS 'nan' is the numpy equivalent of None in python and nodata in tables.

JoeBorgione

Fairly simple database manipulation; you want the mean that includes the number records where field-of-choice is null, right? Try this: right click on the field name and select Statistics. That will give you mean. Select where field-of-choice is null. Calculate the field-of-choice value = mean from the Statistics results.

With all due respect to Dan and his elegant solution, sometimes going with the basics can be useful too....

Dan_Patterson‌

LukeHaskett

Thanks Dan, I'm not sure I fully follow you. The data source is a featureclass attribute table and ideally I'd be looking to use the Field Calculator or a python script to find the means. Unfortunately, I really don't follow the code block you've copied in, can you suggest how I might make use of it? I had thought numpy might be the answer.

LukeHaskett

Hi Joe, I'm looking for statistics to be done on a field array, not only one field. So each feature has ten fields that may or may not be null... I want a script that figures out how many are null to then sum and correctly produce the mean (e.g. if only 8 of the 10 fields are scored, the sum of those will be divided by 8). If you're answer addresses this, please could you rephrase it as I don't follow it.

LukeHaskett

I am trying the following but still not having any success:

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">mean</SPAN><SPAN class="punctuation token">(</SPAN>Welcoming<SPAN class="punctuation token">,</SPAN> Access<SPAN class="punctuation token">,</SPAN> Community<SPAN class="punctuation token">,</SPAN> Safe<SPAN class="punctuation token">,</SPAN> Provision<SPAN class="punctuation token">,</SPAN> Quality<SPAN class="punctuation token">,</SPAN> Security<SPAN class="punctuation token">,</SPAN> Dog<SPAN class="punctuation token">,</SPAN> Litter<SPAN class="punctuation token">,</SPAN> Grounds<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; fieldList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>Welcoming<SPAN class="punctuation token">,</SPAN> Access<SPAN class="punctuation token">,</SPAN> Community<SPAN class="punctuation token">,</SPAN> Safe<SPAN class="punctuation token">,</SPAN> Provision<SPAN class="punctuation token">,</SPAN> Quality<SPAN class="punctuation token">,</SPAN> Security<SPAN class="punctuation token">,</SPAN> Dog<SPAN class="punctuation token">,</SPAN> Litter<SPAN class="punctuation token">,</SPAN> Grounds<SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; validList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> fieldList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> i <SPAN class="operator token">!=</SPAN> None<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; validList<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; meanVal <SPAN class="operator token">=</SPAN> sum<SPAN class="punctuation token">(</SPAN>validList<SPAN class="punctuation token">)</SPAN><SPAN class="operator token">/</SPAN>len<SPAN class="punctuation token">(</SPAN>validList<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> meanVal‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

I get an error that 9 required positional arguments are missing. Any suggestions please?

[edited to use Python syntax highlighting, with proper indent - VA]

JoshuaBixby

How are you calling your function from the field calculator? Obviously the function definition is going into the code block, but what are you putting into the expression box. The error message makes me think you are calling your function incorrectly.

LukeHaskett

Hi Joshua, I'm putting this in:

mean([ !Welcoming!, !Access!, !Community!, !Safe!, !Provision!, !Quality!, !Security!, !Dog!, !Litter!, !Grounds! ])

VinceAngelo

The mean of a sample population is the sum divided by the count. The "count" is of the non-NULL values. All you need is a mechanism to sum values, incrementing the count for each value summed.

sum <SPAN class="operator token">=</SPAN> <SPAN class="number token">0.0</SPAN>
count <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
<SPAN class="keyword token">for</SPAN> elem <SPAN class="keyword token">in</SPAN> array<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> elem<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sum <SPAN class="operator token">+=</SPAN> elem
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; count <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>‍‍‍‍‍‍

<SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>count <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Mean = {:.2f}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>sum <SPAN class="operator token">/</SPAN> float<SPAN class="punctuation token">(</SPAN>count<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Mean undefined!"</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

This would be easily adapted to a DA Cursor, since a row is an array.

LukeHaskett

Hi Vince, thanks for your suggestion. How would I enter this code? Is this something that can be used in the Field Calculator?

VinceAngelo

You'd need to increase the indent, and construct the list, but yes, this simple bit of code could be added anywhere. Your code above would work if you format it correctly for the Field Calculator, but it's wasteful -- Why assemble a new list just to compile a sum? (It's an order of magnitude more work, and of O(n^2) complexity).

Please be sure to include all critical details in the initial question (use of Field Calculator, for example), since changing the question in follow-ups will reduce the value of answers to those who search for the same question.

- V

VinceAngelo

Remove the brackets, which compile a list (maybe) to pass the N parameters, or change the function to receive a list. Right now you're just violating Field Calculator linkage rules.

DanPatterson_Retired

I had thought of that of course.. but I think Luke has multiple fields in a table, and there is a need to do this all at once, rather than one field at a time, hence, the all fields all at once solution, which can then be wrapped in an all files, for all fields all at once solution.

Of course, the problem could be simpler than assumed

JoshuaBixby

I agree with Vince about providing the critical details. Not only does it get you better or higher quality answers, it saves community members time by not having them inadvertently answer a question you aren't asking.

The only caveat or footnote I would add to Vince's comment about constructing another list is that Python's built-in data constructors and functions are highly optimized. Whereas creating a new list and using append to populate it isn't very efficient, list comprehensions are quite efficient. In this case, using a list comprehension to construct a new filtered list allows for the use of more Python built-in functions, and the more built-in functions you can use, the better.

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">mean</SPAN><SPAN class="punctuation token">(</SPAN>lst<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; lst <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>i <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> lst <SPAN class="keyword token">if</SPAN> i<SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> sum<SPAN class="punctuation token">(</SPAN>lst<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">/</SPAN> float<SPAN class="punctuation token">(</SPAN>len<SPAN class="punctuation token">(</SPAN>lst<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

You can call it using your original syntax, which created a list:

mean<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN> !Welcoming!<SPAN class="punctuation token">,</SPAN> !Access!<SPAN class="punctuation token">,</SPAN> !Community!<SPAN class="punctuation token">,</SPAN> !Safe!<SPAN class="punctuation token">,</SPAN> !Provision!<SPAN class="punctuation token">,</SPAN> !Quality!<SPAN class="punctuation token">,</SPAN> !Security!<SPAN class="punctuation token">,</SPAN> !Dog!<SPAN class="punctuation token">,</SPAN> !Litter!<SPAN class="punctuation token">,</SPAN> !Grounds! <SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

For someone new to Python, I would say don't pay much attention to either Vince's or my comments about optimizing code. Optimizing code gets tricky because it involves balancing computer science theory with the reality of interpreter implementations, and often times premature attempts to optimize code confuse the new learner and set him/her back from learning the basic.

Fundamentally, your problem today was that you were incorrectly calling your function. The way you defined your function, it wanted 10 arguments instead of a single argument containing a list. Since you were passing the function a single list of 10 items, it was informing you that there were 9 missing arguments.

JoeBorgione

Yep... I get it.. But there are so many "it's gotta be automated, but I can't describe it" types of questions here....

VinceAngelo

Joshua --

Perhaps it was the fact that I was using a VM, but I got different results on successive executions of a benchmark test, with some iterations showing each of the three mean methods as being fastest(!). I needed to iterate the algorithm invocations ten thousand times, using an array of length 10,000 in order to make more than a hundredth of a second difference, so I guess the most intuitive algorithm for the developer is the "best" in this context.

- V

DanPatterson_Retired

On Code Sharing .... Statistics... Descriptive Statistics for Multiple Fields ....

Take this table which contains nulls, use Column Statistics

and get this result.

Of course the user must still determine the utility of any calculated values (eg sum of X coordinates).

All calculations done using numpy as described earlier since they can exclude nulls since TableToNumPyArray and FeatureToNumPyArray handle table nulls quite nicely. So instead of a search cursor going row by row, a calculation is performed on a column of data at once.

The basic principles I described earlier still apply if one wishes to use python/numpy code rather than go through the fluff of a dialog interface.

The code is accessible in the download.

I have no intention of batching this... batching would require that all fields in all tables be the same... if they are, then batch my code functions (defs) ore preferably, hire a student to do the work for you

PS... anyone wanting to benchmark this against search cursors, I can send a geodatabase of desired size and content. Alternately, I have code that will create a geodatabase table of NxM rows and columns of double values, as a point featureclass. The data can be constructed of any desired statistical distribution that numpy supports and you can incorporate nulls within the fields. It is speedy for the speed needy

LukeHaskett

Thank you so much Joshua, this very neat bit of code did the job perfectly!

LaurenGriffin

Check out this case study. It had to deal with the same issue: Modeling literacy

You can do this with Calculate Field. This is an excerpt from the case study. I hope this is helpful!

Lauren

Once the new fields have been created, you will use the Calculate Field tool to compute the mean values for each dataset. To compute the mean value for the adolescent birth rate dataset, for example, use the parameters below:

Input Table: AdolescentBirthRate
Field Name: MeanAdBirthRt
Expression: getMean( !Y1990!, !Y1991!, !Y1992!, !Y1993!, !Y1994!, !Y1995!, !Y1996!, !Y1997!, !Y1998!, !Y1999!, !Y2000!, !Y2001!, !Y2002!, !Y2003!, !Y2004!, !Y2005!, !Y2006!, !Y2007!, !Y2008!, !Y2009!, !Y2010!, !Y2011!, !Y2012!, !Y2013!, !Y2014!, !Y2015!)

Code Block:

def getMean(*allYears):&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp; notNull = list(filter(None,allYears))&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp; theSum = sum(notNull)&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp; theCnt = len(notNull)&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp; return theSum/theCnt

The Code Block defines the getMean function. This is what each Python statement does:

Python Statement	What it does
def getMean(*allYears):	Indicates you want to define a new function called getMean that will use the sequence of values passed to it from the Expression parameter.
notNull = list(filter(None,allYears))	This line, indented four spaces, indicates Python should put all of the non-Null values into a list called notNull.
theSum = sum(notNull)	This line, indented four spaces, instructs Python to sum the non-Null values.
theCnt = len(notNull)	This line, indented four spaces, instructs Python to count the non-Null values.
return theSum/theCnt	This line, indented four spaces, sets the value of the Field Name provided to be the mean (the sum divided by the count).

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.