Calculating Standard Deviation of values in a table

Question

I have a service published to AGOL as a hosted feature layer.  It has a FC and several tables.  I'm using the FC and tables to create a Dashboard.  I'd like to be able to use attribute expressions to calculate zscores on several different fields in tables I have setup in the dashboard but I can't figure out how to get the stdev and mean of any particular field in an attribute expression for the tables.  Is this possible?

Thanks,

Ethan

EthanHunt1 · Answer

Cody,

Thanks for the response.  I should probably add some explanation to what I'm trying to do.  So I have a dataset that I have published to AGOL as a hosted feature layer, and it contains several tables, one of which has lots of stats (baseball related).  Here's a snippet of the table.

So I have a table that has batting average (BA) for hundreds of players.  I would like to color the background of each cell based on that numbers zscore for everyone's BA.  I'd prefer to do this with advanced formatting within the table element in the dashboard, rather than before the data gets uploaded to AGOL, and ESRI has a stdev function, but I can't figure out how to get all the values for BA from the table within the table elements advanced formatting expression.

I have the same problem with Mean.  There's an esri function for it, but I can't figure out how to pass it all the values of BA in the table, and then calculate a zscore for every datapoint to assign background colors to them.  Is that possible within a table element in a dashboard?  If it's not, could a Notebook do something like that for me?  One of the reasons I'd prefer to do it in the dashboard is because I'll be refreshing the hosted feature layer daily once the MLB season starts.

CodyPatterson · Answer

Hey @EthanHunt1

Here is a script to do it in a calculate cell expression here, this assumes that all numbers needed to calculate the stdev are in the same row:

def calculate_standard_deviation(values):
    mean = sum(values) / len(values)

squared_diff = [(x - mean) ** 2 for x in values]

average_squared_diff = sum(squared_diff) / len(values)

standard_deviation = average_squared_diff ** 0.5

return standard_deviation

Here is how the calculate field would appear:

Here is the output:

If you are pulling from many different cells, I can change it around to do it another way!

Hope that helps!

Cody