IronPython script to Add Rows With Zero

Introduction

If you have the need to visualize empty data on a line chart - for example, if sales in one month was zero, so there's no data for that month, yet you need to show zero for that month, then you need to add a row to your data showing zero sales for that month.

Add Rows With Zero

The below script can be used to create rows with zeros for a certain column. It works by creating a new data table in memory and adding the rows to the original data table.

# Copyright © 2017. TIBCO Software Inc.  Licensed under TIBCO BSD-style license.

#Author - Andrew Berridge, TIBCO Software, September 2016

import System
import Spotfire.Dxp.Application
from Spotfire.Dxp.Data import *
from Spotfire.Dxp.Application.Visuals import VisualContent
from System.Collections.Generic import HashSet
from System.IO import FileStream, FileMode, File, MemoryStream, SeekOrigin, StreamWriter
import System.String
from Spotfire.Dxp.Data.Import import TextDataReaderSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data.Import import DataTableDataSource
from System import DateTime
print DateTime.Today

def LoadCSV(dataTableName, stream):
	settings = TextDataReaderSettings()
	settings.Separator = ","
	settings.AddColumnNameRow(0)
	settings.ClearDataTypes(False)
	settings.SetDataType(0, DataType.Date)
	settings.SetDataType(1, DataType.Integer)
	stream.Seek(0, SeekOrigin.Begin)
	fs = TextFileDataSource(stream, settings)
	if Document.Data.Tables.Contains(dataTableName):
		Document.Data.Tables[dataTableName].ReplaceData(fs)
	else:
		Document.Data.Tables.Add(dataTableName, fs)

# Create new data table with a row for every month
stream = MemoryStream()
csvWriter = StreamWriter(stream)#, Encoding.UTF8)
csvWriter.WriteLine("Date,Attritionrn")

end = DateTime.Today
start = DateTime(2012,01,01) # Change this for your start date
currentDate = start
while currentDate < end:
	print currentDate
	currentDate = currentDate.AddMonths(1)
	csvWriter.WriteLine(currentDate.ToLongDateString() + ",0rn")

# Now load the generated data
csvWriter.Flush()
LoadCSV("Rows with Zero", stream)

# The original data table
table = Document.Data.Tables["Attrition"]

# Add Rows
ds = DataTableDataSource(Document.Data.Tables["Rows with Zero"])
				
addRowsSettings = AddRowsSettings(table, ds, "Is Zero Row", "Yes", "No")

# Now remove existing Zero Rows and Is Zero Row column
if (table.Columns.Contains("Is Zero Row")):
	existingZeroRows = table.Select('[Is Zero Row] = "Yes"')
	table.RemoveRows(existingZeroRows)
	table.Columns.Remove("Is Zero Row")

# Do the work of adding rows:
table.AddRows(ds, addRowsSettings)

License:  TIBCO BSD-Style License