Spotfire add column from python list

Question

How would one add a python list to a spotfire data table as a new column. For example I wish to add a column that has values calculated using python.

from Spotfire.Dxp.Data import *

# Get the data table
DataTable = Document.Data.Tables.TryGetValue("Table Name")[1]

# define some cursors
CursorA = DataValueCursor.CreateFormatted(DataTable.Columns["Column A"])
CursorB = DataValueCursor.CreateNumeric(DataTable.Columns["Column B"])
CursorC = DataValueCursor.CreateNumeric(DataTable.Columns["Column C"])

# define a list
NewColumnValues = []

# Go row by row and calculate the values I want.
for row in DataTable.GetRows(CursorA, CursorB, CursorC):
    A = CursorA.CurrentValue
    B = CursorB.CurrentValue
    C = CursorC.CurrentValue
    V = SomeComplicatedFunction(A, B, C)
    NewColumnValues.append(V)

# And now add that column to the datatable
# If only it would work like this...
DataTable.AddColumns('NewColumnName', NewColumnValues)

Is there a way to do that? The only examples I can see that use the AddColumns method involve adding a column read from another file, and I can't see how to make them work.

score 3 · Answer 1 · edited May 23 '17 at 12:30

I've cracked it, with some guidance from this related solution: How to create a data table on the fly in Spotfire via python

The short answer is that you cannot add a column from a python list directly, but you can create a text object and import directly from that without having to save it anywhere.

Here's how the script looks.

from Spotfire.Dxp.Data import *
from System.IO import StringReader, StreamReader, StreamWriter, MemoryStream, SeekOrigin
from Spotfire.Dxp.Data.Import import *

# Get the data table
DataTable = Document.Data.Tables.TryGetValue("Table Name")[1]

# define some cursors
CursorA = DataValueCursor.CreateFormatted(DataTable.Columns["Column A"])
CursorB = DataValueCursor.CreateNumeric(DataTable.Columns["Column B"])
CursorC = DataValueCursor.CreateNumeric(DataTable.Columns["Column C"])
CursorRowId = DataValueCursor.CreateNumeric(DataTable.Columns["Row ID"])
# Note that column "Row ID" must be a unique identifier, and it can't be a calculated field (even a frozen one).

textData = ""
# Go row by row and calculate the values I want.
    for row in DataTable.GetRows(CursorA, CursorB, CursorC, CursorRowId):
    A = CursorA.CurrentValue
    B = CursorB.CurrentValue
    C = CursorC.CurrentValue
    V = SomeComplicatedFunction(A, B, C)
    textData += "%d\t%f\r\n" % (CursorRowId, V)

# So now textData is a two column text string containing all the data I need.
# Turn it into an in-memory text data source.

stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

readerSettings = TextDataReaderSettings()
readerSettings.Separator = "\t"
readerSettings.SetDataType(0, DataType.String)
readerSettings.SetColumnName(0, 'Row ID')
readerSettings.SetDataType(1, DataType.Real)
readerSettings.SetColumnName(1, 'Calculated Value')

NewInfo = TextFileDataSource(stream, readerSettings)

# Define the table relationship
leftColumnSignature = DataColumnSignature("Row ID", DataType.Integer)
rightColumnSignature = DataColumnSignature("Row ID", DataType.Integer)
columnMap = {leftColumnSignature:rightColumnSignature}
ignoredColumns = []
columnSettings = AddColumnsSettings(columnMap, JoinType.LeftOuterJoin, ignoredColumns)

# Add the column
DataTable.AddColumns(NewInfo, columnSettings)

And that will create a new column called "Calculated Value".

I get the following error on the last line: "TypeError: AddColumns() takes exactly 3 arguments (2 given)" which makes no sense because in the Spotfire documentation the AddColumns() function indeed takes the two arguments which you provided :( Unless that's changed from 7.7.x to 7.11.0 — Alex Braksator, Jul 20 '18 at 22:07
@alex is the rest of the code as my example? The "takes exactly N arguments (N-1 given)" usually has to do with an object's self object not being present, so I'm wondering if you've tried to call AddColumns() instead of DataTable.AddColumns()? — EddyTheB, Jul 22 '18 at 07:37

Spotfire add column from python list

1 Answers1

Linked