Jump to content
We've recently updated our Privacy Statement, available here. ×

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


Guest
Go to solution Solved by Gaia Paolini,

Recommended Posts

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

Link to comment
Share on other sites

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)

>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

image.png.ae2145445d6f82164de4dca789af39af.png

Link to comment
Share on other sites

​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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 4 weeks later...

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)
Link to comment
Share on other sites

​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

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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

a, b and d are originally strings.

image.png.11ef2bc55f54e888d3eba4eb98dbec54.pngThis 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:

image.png.f43386b1a68d87bc691687a623f928e5.pngThis 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)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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?

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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)

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...