How to create a data table on the fly in Spotfire

Question

I need to iterate each row, add items to a dictionary, do some sorting and then spit out the results into a data table I need to create on the fly via script.

http://spotfirecommunity.tibco.com/community/blogs/tips/archive/2011/03/06/displaying-cross-table-data-as-a-new-data-table.aspx

I can't access this article any more, does anyone have the code to do something similar to this at all?

Thanks

Answer 1

Worked it out with some help.

You have to create a .Net DataSet and DataTable, add rows in memory, process the table and create a tab separated string from the data, then use a Stream to create a Spotfire TextDataSource...then you can create a data table in Spotfire using the in memory text data source (rather than a physical text file).

There is other stuff in this code related to the problem I was trying to solve, but it may be useful to other people.

import clr
clr.AddReference('System.Data')
import System
from System.Data import DataSet, DataTable, XmlReadMode
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings
from System.IO import StringReader, StreamReader, StreamWriter, MemoryStream, SeekOrigin
from System.Threading import Thread
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import DataSelection
from Spotfire.Dxp.Data import DataPropertyClass
from Spotfire.Dxp.Data import Import
from System import DateTime
from System import DateTime, TimeSpan, DayOfWeek
from datetime import date
from System.Net import HttpWebRequest
import time
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings



rowCount = Document.Data.Tables['Registrations'].RowCount
rowsToInclude = IndexSet(rowCount,True)
myList = []


#Get a cursor to the two columns we want to use. cursor1 is for the key column and cursor2 is for the column selected by the user input
registration = DataValueCursor.CreateFormatted(Document.Data.Tables['Registrations'].Columns["Registration ID"])
date1 = DataValueCursor.Create[DateTime](Document.Data.Tables['Registrations'].Columns["Date 1"])
date2 = DataValueCursor.Create[DateTime](Document.Data.Tables['Registrations'].Columns["Date 2"])
date3 = DataValueCursor.Create[DateTime](Document.Data.Tables['Registrations'].Columns["Date 3"])
date4 = DataValueCursor.Create[DateTime](Document.Data.Tables['Registrations'].Columns["Date 4"])
date5 = DataValueCursor.Create[DateTime](Document.Data.Tables['Registrations'].Columns["Date 5"])

#get users logon name not prid
#print Thread.CurrentPrincipal.Identity.Name

dataSet = DataSet()
dataTable = DataTable("pattern")
dataTable.Columns.Add("Registration ID", System.String)
dataTable.Columns.Add("Date 1", System.DateTime)
dataTable.Columns.Add("Date 2", System.DateTime)
dataTable.Columns.Add("Date 3", System.DateTime)
dataTable.Columns.Add("Date 4", System.DateTime)
dataTable.Columns.Add("Date 5", System.DateTime)
dataTable.Columns.Add("Sequence", System.String)
dataSet.Tables.Add(dataTable)

for  row in  Document.Data.Tables['Registrations'].GetRows(rowsToInclude,registration, date1,date2,date3, date4, date5):
   dates=dict()
   reg = registration.CurrentValue
   value1 = date1.CurrentValue  
   value2 = date2.CurrentValue 
   value3 = date3.CurrentValue
   value4 = date4.CurrentValue
   value5 = date5.CurrentValue  
   dates['date1'] = value1
   dates['date2'] = value2
   dates['date3'] = value3
   dates['date4'] = value4
   dates['date5'] = value5

   sortedDates = sorted(dates.values())

   #print sorted(dates.values())

   pattern = ""
   for key, value in sorted(dates.iteritems(), key=lambda (k,v): (v,k)):   
                if str(value) <> '01/01/0001 00:00:00':
                    print 'Found a zero date'
                    if key == 'date1':
                                pattern = pattern + 'd1'
                                print 'Found a date 1'
                    if key == 'date2':
                                pattern = pattern + 'd2'
                                print 'Found a date 2'
                    if key == 'date3':
                                pattern = pattern + 'd3'
                                print 'Found a date 3'
                    if key == 'date4':
                                pattern = pattern + 'd4'
                                print 'Found a date 4'
                    if key == 'date5':
                                pattern = pattern + 'd5'
                                print 'Found a date 5'


   dt = dataTable.NewRow()
   dt["Registration ID"] = reg
   dt["Date 1"] = value1
   dt["Date 2"] = value2
   dt["Date 3"] = value3
   dt["Date 4"] = value4
   dt["Date 5"] = value5
   dt["Sequence"] = pattern  
   dataTable.Rows.Add(dt)


textData = "Registration ID\tDate 1\tDate 2\tDate 3\tDate 4\tDate 5\tSequence\r\n"
for row in dataTable.Rows:
                textData +=  row["Registration ID"] + "\t" + str(row["Date 1"]) + "\t" + str(row["Date 2"]) + "\t" + str(row["Date 3"]) + "\t" + str(row["Date 4"]) + "\t" + str(row["Date 5"]) + "\t" + row["Sequence"] + "\r\n"



print textData

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

readerSettings = TextDataReaderSettings()
readerSettings.Separator = "\t"
readerSettings.AddColumnNameRow(0)
readerSettings.SetDataType(0, DataType.String)
readerSettings.SetDataType(1, DataType.Date)
readerSettings.SetDataType(2, DataType.Date)
readerSettings.SetDataType(3, DataType.Date)
readerSettings.SetDataType(4, DataType.Date)
readerSettings.SetDataType(5, DataType.String)

dSource = TextFileDataSource(stream, readerSettings)

if Document.Data.Tables.Contains("Sequence"):
                Document.Data.Tables["Sequence"].ReplaceData(dSource)
else:
                newTable = Document.Data.Tables.Add("Sequence", dSource)
                tableSettings = DataTableSaveSettings (newTable, False, False)
                Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)

Answer 2

Instead of

for row in dataTable.Rows:
 textData +=  row["Registration ID"] + "\t" + str(row["Date 1"]) + "\t" + str(row["Date 2"]) + "\t" + str(row["Date 3"]) + "\t" + str(row["Date 4"]) + "\t" + str(row["Date 5"]) + "\t" + row["Sequence"] + "\r\n"

I used

output = cStringIO.StringIO()
for row in dataTable.Rows:
  output.write(row["Registration ID"] + "\t" + str(row["Date 1"]) + "\t" +             str(row["Date 2"]) + "\t" + str(row["Date 3"]) + "\t" + str(row["Date 4"]) + "\t" + str(row["Date 5"]) + "\t" + row["Sequence"] + "\r\n")

it was a great increase in performance.