Jump to content
  • Introduction

    TIBCO Spotfire® has the ability to make forecasts in time using the Holt Winters forecasting technique; these forecasts appear directly in the visualization.

    This IronPython script retrieves the forecast data for continued analysis in Spotfire.

    Code Sample

    # Copyright © 2017. TIBCO Software Inc.  Licensed under TIBCO BSD-style license.
    
    # Create data tables containing the results and data from a Holt-Winters forecast on a Spotfire line chart
    # Ian Cook
    # TIBCO Software, Inc.
    # December 2014
    
    # note: this assumes you have only one line chart on the page,
    # and the line chart has only one forecast fitting model
    
    # import required classes
    from Spotfire.Dxp.Application.Visuals import LineChart
    from Spotfire.Dxp.Data import DataTableSaveSettings, DataType, DataValueCursor
    from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings
    from System import Array
    from System.IO import BinaryReader, MemoryStream, SeekOrigin, StreamWriter
    
    # get the line chart visual
    # (could also do this by passing the line chart visual as a parameter to the IronPython script)
    for p in Document.Pages:
    	if p.Title == "My Page Title":
    		for v in p.Visuals:
    			if v.TypeId.Name == "Spotfire.LineChart":
    				linechart = v.As[LineChart]()
    
    # get the forecast fitting model
    for fm in linechart.FittingModels:
    	if fm.TypeId.Name == "Spotfire.ForecastHoltWintersFittingModel":
    		forecast = fm
    
    # get the forecast results data source
    forecastDataSource = forecast.GetResultsDataSource()
    
    # add or replace a data table containing the forecast results
    if Document.Data.Tables.Contains("Forecast Results"):
    	Document.Data.Tables["Forecast Results"].ReplaceData(forecastDataSource)
    else:
    	newTable1 = Document.Data.Tables.Add("Forecast Results", forecastDataSource)
    	tableSettings = DataTableSaveSettings (newTable1, False, False)
    	Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)
    
    # get info about the trellis columns from the forecast results and create cursors for them
    trellisColumnCursors = []
    trellisColumnNames = []
    trellisColumnTypes = []
    for col in Document.Data.Tables["Forecast Results"].Columns:
    	if col.Properties.DataType == DataType.Binary:
    		break
    	trellisColumnCursors.append(DataValueCursor.Create(col))
    	trellisColumnNames.append(col.Name)
    	trellisColumnTypes.append(col.Properties.DataType)
    
    # create cursors for the WKB binary columns that contain the fitted, forecast, and confidence lines data
    fittedDataCursor = DataValueCursor.Create(Document.Data.Tables["Forecast Results"].Columns["Fitted Line Binary Data"])
    forecastDataCursor = DataValueCursor.Create(Document.Data.Tables["Forecast Results"].Columns["Forecast Line Binary Data"])
    confidenceDataCursor = DataValueCursor.Create(Document.Data.Tables["Forecast Results"].Columns["Confidence Line Binary Data"])
    
    # create a list of all the cursors and change it into an array
    columnCursors = list(trellisColumnCursors)
    columnCursors.extend([fittedDataCursor, forecastDataCursor, confidenceDataCursor])
    columnCursors = Array[DataValueCursor](columnCursors)
    
    # define a function to retrieve data from the WKB binary columns containing lines data
    def getLineData(dataRows, cursorArray, trellisColumnNames, trellisColumnTypes, binaryColumnIndex, isConfidenceLines):
    	data = ""
    	for trellisColumnName in trellisColumnNames:
    		data += trellisColumnName + "\t"
    	if isConfidenceLines:
    		data += "X\tY Lower\tY Upper\r\n"
    	else:
    		data += "X\tY\r\n"
    	for row in dataRows:
    		trellisdata = ""
    		for cursorIndex in range(0, len(trellisColumnNames)):
    			trellisdata += cursorArray[cursorIndex].CurrentValue.ToString() + "\t"
    		bs = cursorArray[binaryColumnIndex].CurrentValue.GetByteStream()
    		br = BinaryReader(bs)
    		br.ReadBytes(14)
    		dataPoints = br.ReadUInt32()
    		if isConfidenceLines:
    			bs2 = cursorArray[binaryColumnIndex].CurrentValue.GetByteStream()
    			br2 = BinaryReader(bs2)
    			br2.ReadBytes(14 + 4 + dataPoints * 16 + 5)
    			dataPoints2 = br2.ReadUInt32()
    			if dataPoints != dataPoints2:
    				raise Exception("Lower and upper confidence lines do not have same number of points.")
    		while dataPoints > 0:
    			dataPoints -= 1
    			data += trellisdata
    			data += br.ReadDouble().ToString()
    			data += "\t"
    			data += br.ReadDouble().ToString()
    			if isConfidenceLines:
    				data += "\t"
    				br2.ReadDouble()
    				data += br2.ReadDouble().ToString()
    			data += "\r\n"
    	stream = MemoryStream()
    	writer = StreamWriter(stream)
    	writer.Write(data)
    	writer.Flush()
    	stream.Seek(0, SeekOrigin.Begin)
    	readerSettings = TextDataReaderSettings()
    	readerSettings.Separator = "\t"
    	readerSettings.AddColumnNameRow(0)
    	columnIndex = 0
    	for trellisColumnType in trellisColumnTypes:
    		readerSettings.SetDataType(columnIndex, trellisColumnType)
    		columnIndex += 1
    	readerSettings.SetDataType(columnIndex, DataType.Real)
    	columnIndex += 1
    	readerSettings.SetDataType(columnIndex, DataType.Real)
    	if isConfidenceLines:
    		columnIndex += 1
    		readerSettings.SetDataType(columnIndex, DataType.Real)
    	return TextFileDataSource(stream, readerSettings)
    
    # create or update the table of the fitted line data
    dataRows = Document.Data.Tables["Forecast Results"].GetRows(columnCursors)
    fittedLineTextDataSource = getLineData(dataRows, columnCursors, trellisColumnNames, trellisColumnTypes, len(trellisColumnNames), False)
    if Document.Data.Tables.Contains("Fitted Line Data"):
    	Document.Data.Tables["Fitted Line Data"].ReplaceData(fittedLineTextDataSource)
    else:
    	newTable2 = Document.Data.Tables.Add("Fitted Line Data", fittedLineTextDataSource)
    	tableSettings = DataTableSaveSettings (newTable2, False, False)
    	Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)
    
    # create or update the table of the forecast line data
    dataRows = Document.Data.Tables["Forecast Results"].GetRows(columnCursors)
    forecastLineTextDataSource = getLineData(dataRows, columnCursors, trellisColumnNames, trellisColumnTypes, len(trellisColumnNames)+1, False)
    if Document.Data.Tables.Contains("Forecast Line Data"):
    	Document.Data.Tables["Forecast Line Data"].ReplaceData(forecastLineTextDataSource)
    else:
    	newTable3 = Document.Data.Tables.Add("Forecast Line Data", forecastLineTextDataSource)
    	tableSettings = DataTableSaveSettings (newTable3, False, False)
    	Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)
    
    # create or update the table of the confidence lines data
    dataRows = Document.Data.Tables["Forecast Results"].GetRows(columnCursors)
    confidenceLineTextDataSource = getLineData(dataRows, columnCursors, trellisColumnNames, trellisColumnTypes, len(trellisColumnNames)+2, True)
    if Document.Data.Tables.Contains("Confidence Line Data"):
    	Document.Data.Tables["Confidence Line Data"].ReplaceData(confidenceLineTextDataSource)
    else:
    	newTable4 = Document.Data.Tables.Add("Confidence Line Data", confidenceLineTextDataSource)
    	tableSettings = DataTableSaveSettings (newTable4, False, False)
    	Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)
     

    Example dxp file ( Download from Resources )

    License: TIBCO BSD-Style License

    get_forecast_data.dxp

     

     


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...