Use IronPython to Retrieve Holt-Winters Forecast in Spotfire®

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