Changing column type

Hi,

I have to change the types of columns in an existing table based on meta-information extracted from another table using the Extract Table Spec node. As I cannot use the Column Rename node for this, I have to use one of the scripting nodes, for example JPython Script 2:1.

 

The problem is that I cannot figure out how to set the type of a column using the KNIME API. Does anybody have a (Python/Java/whatever) snippet demonstrating how to change the type of a column? Unfortunately, it isn't possible to just call something like myTable.getColumn('abc').setType('Double') given an input DataTable or ContainerTable - the input and output objects provided by the JPython node.

 

Thanks

Gideon

Can you use the new Java Snippet node to change the column type. If this assignment need to be done on a different input, I would suggest using the Extract Table Spec node together with TableRow to Variable to node and input this variable into the Java Snippet (enable the flow variable ports on the node's context menu). Hope this helps?

Thanks for the feedback. I tried your suggestion, but no luck. The problem with the Java Snippet is that I cannot dynamically define the output fields using the Extract Table spec node. I fiddled around with reflection, but as the snippet expects a certain name pattern for fields (like v_xxx for a flow variable, c_xxx for an input column, and out_xxx for an output column), there's now way I can get that done using reflection.

 

The snippet has a built-in isType(colName, type) function, but now way of changing the type.

 

So, back to square one...

Mmm, I draw a blank. Might the KNIME SDK at http://knime.org/downloads/overview help to implement a new node for your task, I don't know of any node replacing column types on the fly nor how to build a KNIME workflow.

This problem has come up before when the column rename node is just unsatisfactory for the task. Myself and others have expressed an interest in a new node previously.  We really need a typecasting node with the inclusion/exclusion format to allow these tasks to be done more automated.

thanks

simon.

Hi there,
I second the need for a new type-setting node. Column rename doesn’t seem to fit the bill in some cases. My current challenge is looping through multiple excel files to read in the data, however in some cases the numbers are integers and some doubles - ultimately the loop fails upon concatenation. Adding a column rename before loop end means the headings all need to be consistent, or we need to get into variables again which gets complicated for a simple task.

KNIME team - are there any other options?

image
ERROR Loop End 0:1033 Execute failed: Input table’s structure differs from reference (first iteration) table: Column 3 [Total Energy (Number (double))] vs. [Total Energy (Number (integer))]

1 Like

Hi!

This is annoying problem. Are the double values really double or maybe integers? Is it only this one column Total Energy?

There are other possibilities except Column Rename node you can try putting before Loop End node: Column Auto Type Cast node, Number To String node (then after Loop End use String To Number node), Double to Int node.

Maybe some combination will work :wink:

Br,
Ivan

Hi @supersharp

you need to configure this in the Loop End node. There is an option Allow variable column types if you activate this the node will automatically convert columns into its smallest supertype so they can be combined together.

Btw: the other option Allow changing table specifications allows you to have a changing set of columns in each iteration and they will be combined.

Best wishes, Iris

4 Likes

Thanks @Iris - this seemed to be the easiest solution. I guess I had not come across this before.
@ipazin - thanks for the suggestions, I’ll check out the Auto Type Cast node (may be useful for another project)!

Hi,

I’ve found a pretty robust approach:

  1. Split apart those of the type DataCell
  2. Loop over columns
  3. “return null;” via Java Snipped and enforce desired type


Happy processing
Mike

3 Likes