Spotfire: average of a column with ironpython, using expression

Question

I wish to perform some basic statistic functions on my table in Spotfire. For example: I want to calculate average of all values in column [A] and I need to have this value as variable in IronPython.

I can read all values in the column, sum them and calculate the average completely in IronPython, but since I would also like to use other Spotfire's statistical functions, I expected that I could somehow call Spotfire API and use expression (in the same manner that calculated column is created). Something like this:

expr = "Avg([{col}])".format(col = colName)

Now I would need a way to call this expression and store the result in a variable to use later.

In pseudo code:

colName = "Weight"
value = API.SomeFunction(expr)

I would be grateful for any hints and also for information if this is possible at all.

Lovro Lovro · Accepted Answer · 2019-10-14T07:49:05

In case someone else might benefit from this. The best approximation that I found so far is this (relevant excerpt from the code):

row6 = "Srel"
for col in SampleColumns:
    colExp = "StdDev([{col}]) / Avg([{col}]) * 100".format(col = col)
    colName = col + colExp[0:colExp.find("(")]
    sT.Columns.AddCalculatedColumn(colName, colExp)
    val = getFirstFloatValue(sT, colName)
    row6 += TAB + str(val)
    sT.Columns.Remove(colName)
row6 += EOL

I create calculated column for each combination of the expression an column, that I need the value of. Then I read the first value of calculated column (all values are the same):

def getFirstFloatValue(table, colName):
    cursor = DataValueCursor.Create[float](table.Columns[colName])
    for row in table.GetRows(cursor):
        return cursor.CurrentValue
    return

I use the value val and then delete column.

I believe there might be better solutions, but I am posting this anyway, because someone else might benefit from this solution.

Spotfire: average of a column with ironpython, using expression

1 Answers