Jump to content
  • IronPython script to Add Rows With Zero


    If you have the need to visualise 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.

    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


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...