Guest Posted October 27, 2023 Share Posted October 27, 2023 https://community.spotfire.com/s/question/0D54z00007mxWqjCAE/how-to-change-the-column-data-type-in-the-script-with-ironpythonI 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 RegarsMike Link to comment Share on other sites More sharing options...
Gaia Paolini Posted October 27, 2023 Share Posted October 27, 2023 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 Link to comment Share on other sites More sharing options...
Gaia Paolini Posted October 27, 2023 Share Posted October 27, 2023 also a tip for easier adding of code: use the button below marked </> to insert code so formatting is kept Link to comment Share on other sites More sharing options...
Guest Posted October 27, 2023 Share Posted October 27, 2023 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 realwas not realwas not realwas not realwas not realwas not realwas not realwas not realwas not realwas not realwas not realwas not realwas not realwas not realwas not realTraceback (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,ColumnSelectionfrom Spotfire.Dxp.Data import *table = Document.Data.Tables['MyNewTableWithWrongDataTypes']rowsToInclude = IndexSet(table.RowCount,True)t = ExpressionTransformation()#get the list of columnscolumns = 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.Namecursor = 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')passtable.AddTransformation(t)> Link to comment Share on other sites More sharing options...
Gaia Paolini Posted October 27, 2023 Share Posted October 27, 2023 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 More sharing options...
Guest Posted October 27, 2023 Share Posted October 27, 2023 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 real2. Change the data type of the rest of the column except for a specific column Link to comment Share on other sites More sharing options...
Gaia Paolini Posted October 27, 2023 Share Posted October 27, 2023 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. Link to comment Share on other sites More sharing options...
Guest Posted October 30, 2023 Share Posted October 30, 2023 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. Link to comment Share on other sites More sharing options...
Guest Posted October 30, 2023 Share Posted October 30, 2023 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 More sharing options...
Gaia Paolini Posted October 30, 2023 Share Posted October 30, 2023 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 More sharing options...
Guest Posted October 30, 2023 Share Posted October 30, 2023 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 More sharing options...
Guest Posted October 30, 2023 Share Posted October 30, 2023 how can i show you data sample? Link to comment Share on other sites More sharing options...
Gaia Paolini Posted October 30, 2023 Share Posted October 30, 2023 please check your messages, I have tried to contact you Link to comment Share on other sites More sharing options...
Gaia Paolini Posted October 31, 2023 Share Posted October 31, 2023 I replaced the statement:rowsToInclude = IndexSet(table.RowCount, True)which includes all the rows, with this:rowsToInclude=column.RowValues.ValidRowswhich 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 More sharing options...
Guest Posted November 22, 2023 Share Posted November 22, 2023 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 More sharing options...
Gaia Paolini Posted November 22, 2023 Share Posted November 22, 2023 Does my answer of 22 days ago not solve your problem either? Link to comment Share on other sites More sharing options...
Guest Posted November 22, 2023 Share Posted November 22, 2023 Unfortunately, it doesn't work.I will send the example table code and ironpython code sharing again.Please check under the same conditions.example tableimport 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 More sharing options...
Gaia Paolini Posted November 22, 2023 Share Posted November 22, 2023 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. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted November 22, 2023 Share Posted November 22, 2023 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. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted November 22, 2023 Share Posted November 22, 2023 you could also turn this statement:if cc.Name not in excluded_columns:intoif cc.Name not in excluded_columns and cc.DataType!=DataType.Real:to avoid transforming columns that are already real. Link to comment Share on other sites More sharing options...
Guest Posted November 22, 2023 Share Posted November 22, 2023 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 More sharing options...
Gaia Paolini Posted November 23, 2023 Share Posted November 23, 2023 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()cd('column changed to real:', 'd')real([d])efghThen 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) Link to comment Share on other sites More sharing options...
Guest Posted November 27, 2023 Share Posted November 27, 2023 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 More sharing options...
Gaia Paolini Posted November 27, 2023 Share Posted November 27, 2023 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. Link to comment Share on other sites More sharing options...
Guest Posted November 27, 2023 Share Posted November 27, 2023 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? Link to comment Share on other sites More sharing options...
Guest Posted November 28, 2023 Share Posted November 28, 2023 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 codeimport 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 codefrom 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 More sharing options...
Guest Posted November 28, 2023 Share Posted November 28, 2023 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 More sharing options...
Gaia Paolini Posted November 28, 2023 Share Posted November 28, 2023 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. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted November 28, 2023 Share Posted November 28, 2023 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 More sharing options...
Solution Gaia Paolini Posted November 30, 2023 Solution Share Posted November 30, 2023 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 More sharing options...
Guest Posted December 6, 2023 Share Posted December 6, 2023 sorry for late reply.It works well! Thank you so much. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now