How to change multiple columns data type in the script with IronPython

[Guest]

https://community.spotfire.com/s/question/0D54z00007mxWqjCAE/how-to-change-the-column-data-type-in-the-script-with-ironpython

​

I confirmed that it is possible to change the data type of a single column specified through the link above.

​

​In addition, I would like to change the data type of the 4th to last column of the data table to real type. but, It's not going well. I left it under the code I tried, so I would appreciate it if you could answer me.

​

**error code that i tried**

​columns = list(table.Columns)[3:]

for column in columns:

t.ColumnReplacements.Add(

column,

'real([' + column + '])',

ColumnSelection(column)

)

table.AddTransformation(t)

​

print("Done Changing Data Type for column {} to Real".format(column.Name))```

​

**error msg that i tried**

TypeError: unsupported operand type(s) for +: 'str' and 'DataColumn'

​

​Thank you so much!

Best Regars

Mike

[Gaia Paolini]

Would this answer help?

https://community.spotfire.com/s/question/0D54z00007pNOBICA4/is-there-a-way-to-make-spotfire-automatically-guess-the-datatypes-of-columns-when-importing-data-using-ironpython

[Gaia Paolini]

also a tip for easier adding of code: use the button below marked </> to insert code so formatting is kept

[Guest]

Thank you for your prompt reply and I would like to ask you some questions.

​

I only fixed the table name received from the attached code below and operated it, but it's my first time with ironpython, so I'm not used to it yet, so please understand even if the questions are poor.

​

First, I want to change the data type from the 4th to last column of the table to real, but the code below is a code that changes the value of the entire column. How would you like to change it?

​

Additionally, can I also know the code to change the datatype of the rest of the column to real, except for the columns named "aaa", "bbb", and "ccc", for example?

​

And when I run the code below, I get something and check the error message. It doesn't seem to have worked properly.

The column that needs to be changed in the middle has not changed.

​

**error msg**

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

was not real

Traceback (most recent call last):

 File "<string>", line 36, in <module>

Exception: Failed to execute data transformation.

​

​

Spotfire.Dxp.Data.Exceptions.ImportException: Failed to execute data transformation. ---> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.

  at System.ThrowHelper.ThrowKeyNotFoundException()

  at System.Collections.Generic.Dictionary`2.get_Item(TKey key)

  at Spotfire.Dxp.Data.DataProperties.ImmutableDataColumnProperties.HasPropertyValue(String name)

  at Spotfire.Dxp.Data.DataProperties.WrappedPropertyContainer.HasPropertyValue(String name)

  at Spotfire.Dxp.Data.DataProperties.WrappedPropertyContainer.HasPropertyValue(String name)

  at Spotfire.Dxp.Data.Collections.SearchableCollection.TryGet[T](T t, String propertyName, Object& value)

  at Spotfire.Dxp.Internal.Search.SyntaxTree.ColumnSearchComparisonOperator.MatchPropertyEquals[T](T item, InternalTryGetProperty`1 tryGetProperty)

  at Spotfire.Dxp.Internal.Search.SyntaxTree.ColumnSearchComparisonOperator.Match[T](T item, InternalTryGetProperty`1 tryGetProperty)

  at Spotfire.Dxp.Data.Collections.SearchableCollection.<FindAll>d__1`1.MoveNext()

  at Spotfire.Dxp.Data.DataRowReaderColumnCollection.FindAll(String searchExpression)

  at Spotfire.Dxp.Data.DataRowReader.Spotfire.Dxp.Data.Transformations.IColumnSelectionInput.FindAll[T](String expression)

  at Spotfire.Dxp.Data.Transformations.ColumnSelection.GetColumns[T](IColumnSelectionInput input, PartialDataLoadReport loadReport, ColumnReplacement replaceOp, Boolean isRemoval)

  at Spotfire.Dxp.Data.Transformations.ExpressionTransformationInfo.CreateColumnReplacements(CxxSession session, GlobalMethodRegistry globalMethodRegistry, ExpressionTransformationInput input, ExpressionTransformation transformation, HashSet`1 removed, Dictionary`2 replaceMap, Metadata metadata, PartialDataLoadReport loadReport)

  at Spotfire.Dxp.Data.Transformations.ExpressionTransformationInfo.<>c__DisplayClass19_1.<Create>b__1()

  at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, IndeterminateProgressFormatter progressFormatter, ProgressOperation operation)

  at Spotfire.Dxp.Data.Transformations.ExpressionTransformationInfo.<>c__DisplayClass19_0.<Create>b__0()

  at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, IndeterminateProgressFormatter progressFormatter, ProgressOperation operation)

  at Spotfire.Dxp.Data.Transformations.ExpressionTransformationInfo.Create(ExpressionTransformation transformation, ExpressionTransformationInput input, PartialDataLoadReport loadReport, CxxSession session, IDataPropertyRegistry propertyRegistry, GlobalMethodRegistry methodRegistry, SpecificValidityCalculation specificValidityCalculation)

  at Spotfire.Dxp.Data.Transformations.ExpressionTransformationReader..ctor(DataRowReader inputReader, IDataPropertyRegistry propertyRegistry, ExpressionTransformation transformation, ImportContext importContext, PartialDataLoadReport loadReport, SpecificValidityCalculation specificValidityCalculation)

  at Spotfire.Dxp.Data.Transformations.ExpressionTransformation.<>c__DisplayClass6_0.<ConnectCore>b__0()

  at Spotfire.Dxp.Data.DataTransformationConnection.ExecuteTransformation()

  --- End of inner exception stack trace ---

  at Spotfire.Dxp.Data.DataTransformationConnection.ExecuteTransformation()

  at Spotfire.Dxp.Data.DataFlow.<>c__DisplayClass41_0.<ExecuteWithProgress>b__0()

  at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)

  at Spotfire.Dxp.Data.DataFlow.ExecuteWithProgress(DataTransformation transformation, DataRowReader currentReader, DataFlowResult result)

  at Spotfire.Dxp.Data.DataFlow.Execute()

  at Spotfire.Dxp.Data.DataFlow.DataFlowConnection.ExecuteQueryCore2()

  at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()

  at Spotfire.Dxp.Data.Producers.DataTransformationColumnProducer..ctor(DataPool pool, DataManager dataManager, DataTable dataTable, CxxSession session, GlobalMethodRegistry globalMethodRegistry, DataColumnProducer originalData, DataFlow flow)

  at Spotfire.Dxp.Data.DataTable.<>c__DisplayClass273_0.<AddTransformations>b__0()

  at Spotfire.Dxp.Data.DataTable.<>c__DisplayClass275_0.<AddTransformations>b__1()

  at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException, Boolean isStreamingProperty)

  at Spotfire.Dxp.Data.DataTable.<>c__DisplayClass275_0.<AddTransformations>b__0()

  at Spotfire.Dxp.Framework.Commands.CommandHistory.Transaction(Executor executor, Boolean visible, Boolean sticky, Guid stickyGuid, Boolean isHighlight)

  at Spotfire.Dxp.Framework.Commands.CommandHistory.Transaction(String displayName, Executor executor)

  at Spotfire.Dxp.Data.DataTable.AddTransformations(DataColumnProducer originalProducer, Func`1 producerCreator)

  at Microsoft.Scripting.Interpreter.FuncCallInstruction`3.Run(InterpretedFrame frame)

  at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)

  at Microsoft.Scripting.Interpreter.LightLambda.Run4[T0,T1,T2,T3,TRet](T0 arg0, T1 arg1, T2 arg2, T3 arg3)

  at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)

  at Microsoft.Scripting.Interpreter.DynamicInstruction`4.Run(InterpretedFrame frame)

  at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)

  at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1)

  at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx)

  at Microsoft.Scripting.Hosting.ScriptSource.Execute(ScriptScope scope)

  at Spotfire.Dxp.Application.IronPython27.IronPythonScriptEngine.ExecuteForDebugging(String scriptCode, Dictionary`2 scope, Stream outputStream)

​

<

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelection

from Spotfire.Dxp.Data import *

table = Document.Data.Tables['MyNewTableWithWrongDataTypes']

rowsToInclude = IndexSet(table.RowCount,True)

t = ExpressionTransformation()

#get the list of columns

columns = table.Columns

#iterate on columns, collect values, try to turn them into floating point. If they are not actually floating point, do nothing, otherwise change the column type.

for cc in columns:

ccname=cc.Name

cursor = DataValueCursor.CreateFormatted(table.Columns[ccname])

values=[]

for row in table.GetRows(rowsToInclude,cursor):

values.append(cursor.CurrentValue)

try:

values = [float(x) for x in values ]

t.ColumnReplacements.Add(

ccname,'real(['+ccname+'])',ColumnSelection(ccname)

)

except:

print ('was not real')

pass

​

table.AddTransformation(t)

>

[Gaia Paolini]

The code changes all columns, but you can limit the loop to only the fourth column.

If the message appears it means something went wrong in the try/except block. The assumption is that the values of that column are not compatible with being turned to numbers.

Can you explain the logic? I cannot both change the 4th column and all column except some named ones. You either decide to change all columns with a defined name, or all columns with a defined position, or to change all columns that have not the defined names.

[Guest]

Ah, it seems that I wrote it confusingly. What I'm saying is that I want to see the code of two ways.

1. Convert the data type from the fourth to last column to real

2. Change the data type of the rest of the column except for a specific column

[Gaia Paolini]

I changed the code so the changing of data types for each column is now in a function.

You can define and combine use cases by changing the starting column index and/or the list of excluded columns.

The example is for the 'iris' data table.

See attached file.

[Guest]

Thank you very much for your kind and quick reply.

​

I set use_case = 1 in the method you told me, and added the enclosed_columns condition to operate the code, but the result is strange. Whether the column is a string type or a real type, the error message('something went wrong, probable column was not numeric:', column_name) is continuously displayed from the fourth column and the data type of the columns is not changed.

​

(The columns I want to change consist of string and real type data types, and I hope that the columns set to string will be changed to real data type.)

​

I'm attaching column Properties and error message images, so I'd appreciate it if you could check when you have time.

Please understand that the column name is covered by security issues.

​

[Guest]

​Photos cannot be attached due to company security issues.

What I wanted to show you... The data types of the columns are mixed with string and real, and error messages occur in all columns regardless of data type, and the results are not reflected after operation.

[Gaia Paolini]

Without seeing a data sample I cannot tell for sure.

You are saying that some columns that you believe contain Real numbers are not changed to Real?

Could it be because these columns contains some values that are not numeric? Sometimes there are columns that are Strings looking like numbers, except that some nulls have been encoded as 'NA' strings. You can check by plotting the column values e.g. in a bar chart.

[Guest]

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelection

from Spotfire.Dxp.Data import *

 

 

table = Document.Data.Tables['table123']

t = ExpressionTransformation()

t.ColumnReplacements.Add(

'www','integer([www])',ColumnSelection('www')

)

table.AddTransformation(t)

print("Done Changing wafer_id Datatypes....")

 

######################################################

## functions

######################################################

def changeColumnDataTypeToReal(table, column, transformation):

cursor = DataValueCursor.CreateFormatted(column)

column_name = column.Name

values = []

for row in table.GetRows(rowsToInclude, cursor):

values.append(cursor.CurrentValue)

try:

values = [float(x) for x in values]

transformation.ColumnReplacements.Add(

column_name, 'real(['+column_name+'])', ColumnSelection(column_name)

)

table.AddTransformation(transformation)

print('column changed to real:', column_name)

except:

print('something went wrong, probably column was not numeric:', column_name)

pass

return

######################################################

 

table = Document.Data.Tables['table123']

rowsToInclude = IndexSet(table.RowCount, True)

t = ExpressionTransformation()

columns = list(table.Columns)

 

# input para

use_case = 1

 

if use_case==1:

excluded_columns = ['ppp','rrr','www']

start_index=3

else:

excluded_columns = ['ppp','rrr','www']

start_index=0

 

for cc in columns[start_index:]:

if cc.Name not in excluded_columns:

changeColumnDataTypeToReal(table, cc, t)

[Guest]

how can i show you data sample?

[Gaia Paolini]

please check your messages, I have tried to contact you

[Gaia Paolini]

I replaced the statement:

rowsToInclude = IndexSet(table.RowCount, True)

which includes all the rows, with this:

rowsToInclude=column.RowValues.ValidRows

which for each column in the function call, only includes valid values, i.e. not null.

It worked for me with the sample set that includes null values in columns.

[Guest]

I'm sorry for the late reply. I went on a long vacation. I'll give you an example table in Python code.

The example table has only the 21st row, but in fact, the original data table has more rows and real values.

For example, the a,b column has a real value in the 30th line.

There is a table as in the example, and since it has a value from the fourth column, I want to change the property of the columns from the fourth column to the last column to real.

I hope the example provided will help solve the problem.

I always thank you for your reply, and I will wait for your reply.

oh, I deliberately put 'NaN' in the empty place. In real table, it is an empty space.

import pandas as pd data = { 'cola' : ['test']*21,'id': ['name']*21, 'sub_id': [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21], 'a': ['NaN']*21, 'b': ['NaN']*21, 'c': [0.0272, 'NaN', 0.0272, 'NaN', 'NaN', 0.0272, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 0.027175, 'NaN', 'NaN', 'NaN', 0.0272, 'NaN', 'NaN', 'NaN', 0.027125], 'd': ['NaN']*21, 'e': [0.0278, 'NaN', 0.0278, 'NaN', 'NaN', 0.0278, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 0.027775, 'NaN', 'NaN', 'NaN', 0.0278, 'NaN', 'NaN', 'NaN', 0.0277], 'f': [0.0283, 'NaN', 0.0283, 'NaN', 'NaN', 0.0283, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 0.02825, 'NaN', 'NaN', 'NaN', 0.0283, 'NaN', 'NaN', 'NaN', 0.028225], 'g': [0.01525, 'NaN', 0.01525, 'NaN', 'NaN', 0.01525, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 0.015375, 'NaN', 'NaN', 'NaN', 0.01525, 'NaN', 'NaN', 'NaN', 0.015075], 'h': [0.04225, 'NaN', 0.04225, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 0.04205, 'NaN', 'NaN', 'NaN', 0.04225, 'NaN', 'NaN', 'NaN', 0.042075] } df = pd.DataFrame(data)

[Gaia Paolini]

Does my answer of 22 days ago not solve your problem either?

[Guest]

​Unfortunately, it doesn't work.

I will send the example table code and ironpython code sharing again.

​Please check under the same conditions.

​

• ​example table

import pandas as pd data = { 'cola' : ['test']*21,'id': ['name']*21, 'sub_id': [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21], 'a': [None]*21, 'b': [None]*21, 'c': [0.0272, None, 0.0272, None, None, 0.0272, None, None, None, None, None, None, 0.027175, None, None, None, 0.0272, None, None, None, 0.027125], 'd': [None]*21, 'e': [0.0278, None, 0.0278, None, None, 0.0278, None, None, None, None, None, None, 0.027775, None, None, None, 0.0278, None, None, None, 0.0277], 'f': [0.0283, None, 0.0283, None, None, 0.0283, None, None, None, None, None, None, 0.02825, None, None, None, 0.0283, None, None, None, 0.028225], 'g': [0.01525, None, 0.01525, None, None, 0.01525, None, None, None, None, None, None, 0.015375, None, None, None, 0.01525, None, None, None, 0.015075], 'h': [0.04225, None, 0.04225, None, None, None, None, None, None, None, None, None, 0.04205, None, None, None, 0.04225, None, None, None, 0.042075] } df = pd.DataFrame(data) df.to_csv('test.csv', index = False)

• ironpython code.

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelectionfrom Spotfire.Dxp.Data import * table = Document.Data.Tables['df']t = ExpressionTransformation()t.ColumnReplacements.Add( 'sub_id','integer([sub_id])',ColumnSelection('sub_id'))table.AddTransformation(t)print("Done Changing wafer_id Datatypes....") ######################################################## functions######################################################def changeColumnDataTypeToReal(table, column, transformation): cursor = DataValueCursor.CreateFormatted(column) column_name = column.Name values = [] for row in table.GetRows(rowsToInclude, cursor): values.append(cursor.CurrentValue) try: values = [float(x) for x in values] transformation.ColumnReplacements.Add( column_name, 'real(['+column_name+'])', ColumnSelection(column_name) ) table.AddTransformation(transformation) print('column changed to real:', column_name) except: print('something went wrong, probably column was not numeric:', column_name) pass return###################################################### table = Document.Data.Tables['df']rowsToInclude = IndexSet(table.RowCount, True)t = ExpressionTransformation()columns = list(table.Columns) # input parause_case = 1 if use_case==1: excluded_columns = ['col','id','sub_id'] start_index=3else: excluded_columns = ['col','id','sub_id'] start_index=0 for cc in columns[start_index:]: if cc.Name not in excluded_columns: changeColumnDataTypeToReal(table, cc, t)

## if i run rowsToInclude=column.RowValues.ValidRows this code,

## i got error like below

## error :

Done Changing wafer_id Datatypes....

Traceback (most recent call last):

 File "<string>", line 36, in <module>

NameError: name 'column' is not defined

[Gaia Paolini]

I did not clarify well enough that rowsToInclude needs now to be defined within the changeColumnDataTypeToReal function, because it now changes for every column.

If you put it as the first statement of the function, your example works for me.

[Gaia Paolini]

the only issue is that it creates a second column called c(2). I cannot work out why, it only happens when the column is called c.

[Gaia Paolini]

you could also turn this statement:

if cc.Name not in excluded_columns:

into

if cc.Name not in excluded_columns and cc.DataType!=DataType.Real:

to avoid transforming columns that are already real.

[Guest]

Thank you for your kind reply.

However, the string attribute column (a,b,d) has not yet been changed to real.

it seems necessary to modify the try syntax in the def function.

First of all, the results come out as follows.

Done Changing wafer_id Datatypes....

('something went wrong, probably column was not numeric:', 'a')

('something went wrong, probably column was not numeric:', 'b')

('something went wrong, probably column was not numeric:', 'd')

I attach the code that I operated.

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelectionfrom Spotfire.Dxp.Data import * table = Document.Data.Tables['df']t = ExpressionTransformation()t.ColumnReplacements.Add( 'sub_id','integer([sub_id])',ColumnSelection('sub_id'))table.AddTransformation(t)print("Done Changing wafer_id Datatypes....") ######################################################## functions######################################################def changeColumnDataTypeToReal(table, column, transformation): cursor = DataValueCursor.CreateFormatted(column) column_name = column.Name values = [] for row in table.GetRows(rowsToInclude, cursor): values.append(cursor.CurrentValue) try: values = [float(x) for x in values] transformation.ColumnReplacements.Add( column_name, 'real(['+column_name+'])', ColumnSelection(column_name) ) table.AddTransformation(transformation) print('column changed to real:', column_name) except: print('something went wrong, probably column was not numeric:', column_name) pass return###################################################### table = Document.Data.Tables['df']rowsToInclude = IndexSet(table.RowCount, True)t = ExpressionTransformation()columns = list(table.Columns) # input parause_case = 1 if use_case==1: excluded_columns = ['col','id','sub_id'] start_index=3else: excluded_columns = ['col','id','sub_id'] start_index=0 for cc in columns[start_index:]: if cc.Name not in excluded_columns and cc.DataType!=DataType.Real: changeColumnDataTypeToReal(table, cc, t)

[Gaia Paolini]

with the data generated by Python and the syntax I shared, it worked for me:

a, b and d are originally strings.

This is the output I am getting:

Done Changing wafer_id Datatypes....

a

('column changed to real:', 'a')

real([a])

b

('column changed to real:', 'b')

real()

c

d

('column changed to real:', 'd')

real([d])

e

f

g

h

Then the columns are real:

This is the script I used: (my data table is called sample2)

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelectionfrom Spotfire.Dxp.Data import * table = Document.Data.Tables['sample2']t = ExpressionTransformation()t.ColumnReplacements.Add( 'sub_id','integer([sub_id])',ColumnSelection('sub_id'))table.AddTransformation(t)print("Done Changing wafer_id Datatypes....") ######################################################## functions######################################################def changeColumnDataTypeToReal(table, column, transformation): rowsToInclude=column.RowValues.ValidRows cursor = DataValueCursor.CreateFormatted(column) column_name = column.Name values = [] for row in table.GetRows(rowsToInclude, cursor): values.append(cursor.CurrentValue) try: values = [float(x) for x in values] transformation.ColumnReplacements.Add( column_name, 'real(['+column_name+'])', ColumnSelection(column_name) ) table.AddTransformation(transformation) print('column changed to real:', column_name) print ('real(['+column_name+'])') except: print('something went wrong, probably column was not numeric:', column_name) pass return###################################################### table = Document.Data.Tables['sample2']t = ExpressionTransformation()columns = list(table.Columns) # input parause_case = 1 if use_case==1: excluded_columns = ['cola','id','sub_id'] start_index=3else: excluded_columns = ['cola','id','sub_id'] start_index=0 for cc in columns[start_index:]: print (cc) if cc.Name not in excluded_columns and cc.DataType!=DataType.Real: changeColumnDataTypeToReal(table, cc, t)

[Guest]

I knew why my cord didn't work.

Because it was a problem of column name. The column originally used was not recognized because there were ', '_', and ':' in the column name, but the code worked when the column name was changed only by text.

The non-operational column name is a column that combines two words, so for example, the column name is defined in this way as "123: apple". Is there an idea to create a column name that can be distinguished in that way but allows the code to work?

Thank you so much for helping me.

[Gaia Paolini]

Please open a new question. This question has been answered I hope, and it would be best if this was treated as a different one as it relates to column names.

[Guest]

Okay, I have one last question, so I'd like to ask you one more question before I make another one.

I would appreciate it if you could tell me the rules that should not be used when reading column data.

This time, I knew that characters such as Space (Blank), '-', and ':' should not be used, but can I know what additional characters are not available?

[Guest]

I checked an additional problematic case with column name (2). What should I do at this time?

​

I'll attach table code and spotfire ironpython code.

​

• raw data python code

import pandas as pd data = { 'cola' : ['test']*21,'id': ['name']*21, 'sub_id': [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21], 'aa': [None]*21, 'bb': [None]*21, 'cc': [0.0272, None, 0.0272, None, None, 0.0272, None, None, None, None, None, None, 0.027175, None, None, None, 0.0272, None, None, None, 0.027125], 'ee': [None]*21, 'ee_eg': [0.0278, None, 0.0278, None, None, 0.0278, None, None, None, None, None, None, 0.027775, None, None, None, 0.0278, None, None, None, 0.0277], 'ff': [0.0283, None, 0.0283, None, None, 0.0283, None, None, None, None, None, None, 0.02825, None, None, None, 0.0283, None, None, None, 0.028225], 'gg': [0.01525, None, 0.01525, None, None, 0.01525, None, None, None, None, None, None, 0.015375, None, None, None, 0.01525, None, None, None, 0.015075], 'gg_eg': [None]*21 } df = pd.DataFrame(data) df.to_csv('test2.csv', index = False)

​

• ironpython code​

​

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelectionfrom Spotfire.Dxp.Data import * table = Document.Data.Tables['df2']t = ExpressionTransformation()t.ColumnReplacements.Add( 'sub_id','integer([sub_id])',ColumnSelection('sub_id'))table.AddTransformation(t)print("Done Changing wafer_id Datatypes....") ######################################################## functions######################################################def changeColumnDataTypeToReal(table, column, transformation): rowsToInclude=column.RowValues.ValidRows cursor = DataValueCursor.CreateFormatted(column) column_name = column.Name values = [] for row in table.GetRows(rowsToInclude, cursor): values.append(cursor.CurrentValue) try: values = [float(x) for x in values] transformation.ColumnReplacements.Add( column_name, 'real(['+column_name+'])', ColumnSelection(column_name) ) table.AddTransformation(transformation) print('column changed to real:', column_name) print ('real(['+column_name+'])') except: print('something went wrong, probably column was not numeric:', column_name) pass return###################################################### table = Document.Data.Tables['df2']t = ExpressionTransformation()columns = list(table.Columns) # input parause_case = 1 if use_case==1: excluded_columns = ['cola','id','sub_id'] start_index=3else: excluded_columns = ['cola','id','sub_id'] start_index=0 for cc in columns[start_index:]: print (cc) if cc.Name not in excluded_columns and cc.DataType!=DataType.Real: changeColumnDataTypeToReal(table, cc, t)

​Before operating the code, the columns of the original table are aa,bb,cc,ee,ee_eg,gg,gg_eg.

However, after operating the code, the column changes to aa,bb,cc,ee,ee(2),ff,gg,gg_eg.

The reason for setting the column at first was that Spotfire did not distinguish between ee and ee_eg well, so I thought (2) was added. However, the column names gg and gg_eg operate without any problems, so I am not sure why..

[Guest]

I attach an error message that occurs when the code is turned to the original column name.

Traceback (most recent call last): File "<string>", line 54, in <module> File "<string>", line 17, in changeColumnDataTypeToRealSystemError: Attempt to start ReadTransaction in state 'Detached'.  System.InvalidOperationException: Attempt to start ReadTransaction in state 'Detached'. at Spotfire.Dxp.Framework.DocumentModel.Node.ReadTransaction[T](ValueReader`1 valueReader) at Spotfire.Dxp.Data.DataColumn.get_RowValues() at Microsoft.Scripting.Interpreter.FuncCallInstruction`2.Invoke(Object arg0) at IronPython.Runtime.Binding.PythonGetMemberBinder.FastPropertyGet`1.GetProperty(CallSite site, TSelfType target, CodeContext context) at Microsoft.Scripting.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame) at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame) at Microsoft.Scripting.Interpreter.LightLambda.Run4[T0,T1,T2,T3,TRet](T0 arg0, T1 arg1, T2 arg2, T3 arg3) at Microsoft.Scripting.Interpreter.DynamicInstruction`6.Run(InterpretedFrame frame) at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame) at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1) at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx) at Microsoft.Scripting.Hosting.ScriptSource.Execute(ScriptScope scope) at Spotfire.Dxp.Application.IronPython27.IronPythonScriptEngine.ExecuteForDebugging(String scriptCode, Dictionary`2 scope, Stream outputStream)

[Gaia Paolini]

I cannot find a specific guide for Spotfire, but I think this is perhaps more about Iron Python naming of variables.

You should avoid spaces, names starting with a number and special characters. However, underscore (_) is fine.

[Gaia Paolini]

Regarding this problem:

Before operating the code, the columns of the original table are aa,bb,cc,ee,ee_eg,gg,gg_eg.

However, after operating the code, the column changes to aa,bb,cc,ee,ee(2),ff,gg,gg_eg.

I think you should open a support case. I had the same issue with a column named 'c' and thought that maybe that was a protected name. But I cannot guess that 'ee' is a protected name.

[Gaia Paolini]

I finally worked it out with the help of Spotfire Engineering: the expression

ColumnSelection(column_name)

needs to be surrounded by " enclosed by ' ..' (sorry it is difficult to see)

ColumnSelection('"'+column_name+'"')

to stop it searching for 'c' and also grabbing 'cola'. I tried it with your code and your latest generated example and it seems ok.

Can you let me know if it works.

Note that this can not be applied to calculated columns, only to imported ones.

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelection
from Spotfire.Dxp.Data import *
 
 
table = Document.Data.Tables['df2']
t = ExpressionTransformation()
t.ColumnReplacements.Add(
    'sub_id','integer([sub_id])',ColumnSelection('sub_id')
)
table.AddTransformation(t)
print("Done Changing wafer_id Datatypes....")
 
######################################################
## functions
######################################################
def changeColumnDataTypeToReal(table, column, transformation):
	rowsToInclude=column.RowValues.ValidRows
	cursor = DataValueCursor.CreateFormatted(column)
	column_name = column.Name
	values = []
	for row in table.GetRows(rowsToInclude, cursor):
		values.append(cursor.CurrentValue)
	try:
		values = [float(x) for x in values]
		transformation.ColumnReplacements.Add(
            column_name, 'real(['+column_name+'])', ColumnSelection('"'+column_name+'"')
        )
		table.AddTransformation(transformation)
		print('column changed to real:', column_name)
		#print ('real(['+column_name+'])')
	except:
		print('something went wrong, probably column was not numeric:', column_name)
		pass
	return
######################################################
 
table = Document.Data.Tables['df2']
t = ExpressionTransformation()
columns = list(table.Columns)
 
# input para
use_case = 1
 
if use_case==1:
    excluded_columns = ['cola','id','sub_id']
    start_index=3
else:
    excluded_columns = ['cola','id','sub_id']
    start_index=0
 
for cc in columns[start_index:]:
    #print (cc)
    if cc.Name not in excluded_columns and cc.DataType!=DataType.Real:
        changeColumnDataTypeToReal(table, cc, t)

 

[Guest]

sorry for late reply.

It works well! Thank you so much.