How to split a comma-delimited list in IronPython (Spotfire)?

Question

I have a existing data table with two columns, one is a ID and one is a list of IDs, separated by comma.

For example

ID | List 
---------
1  | 1, 4, 5
3  | 2, 12, 1

I would like to split the column List so that I have a table like this:

ID | List 
---------
1  | 1
1  | 4
1  | 5
3  | 2
3  | 12
3  | 1

I figured this out now:

tablename='Querysummary Data'
table=Document.Data.Tables[tablename]

topiccolname='TOPIC_ID'
topiccol=table.Columns[topiccolname]
topiccursor=DataValueCursor.Create[str](topiccol)

docscolname='DOC_IDS'
doccol=table.Columns[docscolname]
doccursor=DataValueCursor.Create[str](doccol)

myPanel = Document.ActivePageReference.FilterPanel
idxSet =  myPanel.FilteringSchemeReference.FilteringSelectionReference.GetSelection(table).AsIndexSet()


keys=dict()
topdoc=dict()
for row in table.GetRows(idxSet,topiccursor,doccursor):
   keys[topiccursor.CurrentValue]=doccursor.CurrentValue

for key in keys:
    str = keys[key].split(",")

    for i in str:
        topdoc[key]=i
        print key + " " +i

now I can print the topic id with the corresponding id. How can I create a new data table in Spotfire using this dict()?

Answer 1

I solved it myself finally..maybe there is some better code but it works:

tablename='Querysummary Data'
table=Document.Data.Tables[tablename]

topiccolname='TOPIC_ID'
topiccol=table.Columns[topiccolname]
topiccursor=DataValueCursor.Create[str](topiccol)

docscolname='DOC_IDS'
doccol=table.Columns[docscolname]
doccursor=DataValueCursor.Create[str](doccol)

myPanel = Document.ActivePageReference.FilterPanel
idxSet =  myPanel.FilteringSchemeReference.FilteringSelectionReference.GetSelection(table).AsIndexSet()

# build a string representing the data in tab-delimited text format
textData = "TOPIC_ID;DOC_IDS\r\n"

keys=dict()
topdoc=dict()
for row in table.GetRows(idxSet,topiccursor,doccursor):
   keys[topiccursor.CurrentValue]=doccursor.CurrentValue

for key in keys:
    str = keys[key].split(",")

    for i in str:
        textData += key + ";" +  i + "\r\n"

dataSet = DataSet()
dataTable = DataTable("DOCIDS") 
dataTable.Columns.Add("TOPIC_ID", System.String) 
dataTable.Columns.Add("DOC_IDS", System.String) 

dataSet.Tables.Add(dataTable)

# make a stream from the string
stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

# set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = ";"
readerSettings.AddColumnNameRow(0)
readerSettings.SetDataType(0, DataType.String)
readerSettings.SetDataType(1, DataType.String)
readerSettings.SetDataType(2, DataType.String)

# create a data source to read in the stream
textDataSource = TextFileDataSource(stream, readerSettings)

# add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains("Querysummary Mapping"):
    Document.Data.Tables["Querysummary Mapping"].ReplaceData(textDataSource)
else:
    newTable = Document.Data.Tables.Add("Querysummary Mapping", textDataSource)
    tableSettings = DataTableSaveSettings (newTable, False, False)
    Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)