Spotfire - Unpivot JSON data to table

import clr
clr.AddReference('System.Data')
clr.AddReference('System.Web.Extensions')
import System
from System import DateTime
from System.Data import DataSet, DataTable
from System.IO import StreamReader, StreamWriter, MemoryStream, SeekOrigin
from System.Net import HttpWebRequest
from System.Web.Script.Serialization import JavaScriptSerializer
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings

myJson = '''[
{"Column 1": 1,"API":"42013342780000","PRODUCTION":[{"MONTH": "2011-03-01","OIL_BOE":5657,"GAS_BOE":1},{"MONTH":"2011-04-01","OIL_BOE":6328,"GAS_BOE":1}]},
{"Column 1": 2,"API":"42013309790000","PRODUCTION":[{"MONTH": "2002-01-01","OIL_BOE": 3,"GAS_BOE":1}, {"MONTH": "2002-02-01","OIL_BOE": 4,"GAS_BOE":1}]},
{"Column 1": 3,"API":"42013309800000","PRODUCTION":[{"MONTH": "2002-01-01","OIL_BOE": 5,"GAS_BOE":1}, {"MONTH": "2002-02-01","OIL_BOE": 6,"GAS_BOE":1}, {"MONTH": "2002-03-01","OIL_BOE": 7,"GAS_BOE":1}]}
]'''


# Deserialize the JSON to a .net object
flowers = JavaScriptSerializer().DeserializeObject(myJson)

strData = ''
for f in flowers:
    for x in f['PRODUCTION']:
        strData += f['API'] + '\t' + str(x['MONTH']) + '\t' + str(x['OIL_BOE'])+ '\t' + str(x['GAS_BOE']) + '\r\n'

def generateDataTableFromString(strData, tblName, colHeaders, colTypes):
    # headers
    headers = '\t'.join(colHeaders) + '\r\n'

    # append data to header
    strData = headers + strData

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

    # set up the text data reader
    readerSettings = TextDataReaderSettings()
    readerSettings.Separator = "\t"
    readerSettings.AddColumnNameRow(0)

    # assign data type
    for i, col in enumerate(colTypes):
        readerSettings.SetDataType(i, col)

    # 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(tblName):
        Document.Data.Tables[tblName].ReplaceData(textDataSource)
    else:
        newTable = Document.Data.Tables.Add(tblName, textDataSource)
        tableSettings = DataTableSaveSettings(newTable, False, False)
        Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)


# get parameters to build route details tables
tblName = 'well_data_1'
tblColumnsHeaders = ['API', 'MONTH','OIL_BOE','GAS_BOE']
tblColumnsTypes = [DataType.String , DataType.Date, DataType.Real  , DataType.Real] 

# generate tables
generateDataTableFromString(strData, tblName, tblColumnsHeaders, tblColumnsTypes)

 

Spotfire - Create a button to toggle a map layer on and off (Ironpython)

from Spotfire.Dxp.Application.Visuals import *

v = viz.As[VisualContent]()

for i in v.Layers:

          if i.Title == 'County_shape':
                  if i.Enabled == True:
                          i.Enabled == False
             
                  else:
                          i.Enabled == True

Spotfire - Remove bookmarks (Ironpython)

from Spotfire.Dxp.Application.AnalyticItems import BookmarkManager

bookmarkManager = Application.Document.Context.GetService(BookmarkManager)

for b in bookmarkManager.GetBookmarks():

       bookmarkManager.Remove(b)

Spotfire - Replace and refresh data and onclick button loading event (HTML and JS)

ironPython:

import System
import Spotfire.Dxp.Application
from Spotfire.Dxp.Data import *
from Spotfire.Dxp.Data.Import import DataTableDataSource
from Spotfire.Dxp.Data import CalculatedColumn
from System.IO import FileStream, FileMode, File, MemoryStream, SeekOrigin, StreamWriter, StreamReader
import System.String
from Spotfire.Dxp.Data.Import import TextDataReaderSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource
import clr
clr.AddReference("System.Windows.Forms")
import System.Windows.Forms as WinForms
from Spotfire.Dxp.Framework.ApplicationModel import NotificationService
from System.Windows.Forms import OpenFileDialog
from System.Collections.Generic import List, Dictionary
from Spotfire.Dxp.Application.Visuals import HtmlTextArea
from Spotfire.Dxp.Framework.ApplicationModel import *

#get data source location
file_loc = OpenFileDialog()
file_loc.InitialDirectory
file_loc.ShowDialog()

try: #validate the source

ds = Document.Data.CreateFileDataSource(file_loc.FileName)

     #since source_tbl is linked to source, need to replace it
source_tbl = Document.Data.Tables["well_attributes"].ReplaceData(ds)

except ValueError:
WinForms.MessageBox.Show("No file selected! Please select the file to refresh.")
        #refresh text area
vis.As[HtmlTextArea]().HtmlContent += " " # Force re-render
raise #Rethrow the original exception, so I get a stack trace to it.

 except IOError:
WinForms.MessageBox.Show("Please select the right file type.")
vis.As[HtmlTextArea]().HtmlContent += " "
raise

#notification service
notify = Application.GetService[NotificationService]();

# Tables(s) to refresh - change/add more if required
Tbls = List[DataTable]()
Tbls.Add(Document.Data.Tables['myTableName'])


# After tables are refreshed, update html
def afterLoad(exception, Document=Document, notify=notify):
if not exception:

# Hide the loading div
newHtml = vis.As[HtmlTextArea]().HtmlContent
oldDiv = '<div id="loading" style="display:inline">'
newDiv = '<div id="loading" style="display:none">'
newHtml = newHtml.Replace(oldDiv,newDiv)

# Apply change
vis.As[HtmlTextArea]().HtmlContent = newHtml;
vis.As[HtmlTextArea]().HtmlContent += " " # Force re-render

else:
notify.AddErrorNotification("Error refreshing table(s)","Error details",str(exception))

# Refresh table(s)
Document.Data.Tables.RefreshAsync(Tbls, afterLoad)

#message box after successful load and refresh data
WinForms.MessageBox.Show("Data has been successfully refreshed! ")

HTML:

<p style="text-indent: 4em;"> <font size="2"><strong> Please click to select well_attributes file (.csv file) to refresh:</strong></font>
<div id="clicker" style="visibility: visible;">
<SpotfireControl id="67e98bb593da44afaf6cfda44a8030e2" />
<font color="red" size="2"><strong>
<div id="loading"
style="display: none;">Refreshing...Please wait. DO NOT load file again!
</div></strong></font>
</div>

Javascript:

$('#67e98bb593da44afaf6cfda44a8030e2').click(function() {

   

// Show the loading prompt

$('#loading').css('display', 'inline');

// Click the python button 

$('#clickerpy input').click()

// disable the refresh button 

document.getElementById("67e98bb593da44afaf6cfda44a8030e2").disabled = true

   

});