Merge column values with their names...

KNIME Analytics Platform
1

Hi,

I have a table:

Col1 Col2
Val1 Val2
Val3 Val4

I would like to merge those columns with adding column names into strings, ie to get something like:

MergedColumnName
Col1:Val1 Col2:Val2
Col1:Val3 Col2:Val4

(I've managed to do this with quite long workflow, including unpivoting, aggregations, pivoting, but maybe you could suggest a more elegant solution).

 

best wishes,

Filip

2

Hi Filip,

 

hm, I would loop it.

Column List Loop Start -> Column Rename Regex -> String Manipulation -> Loop End (Column Append) -> Column Combiner -> Column Filter

 

But I don't think it is going to be much shorter...

Anyway, I attach you the workflow,

Regards, Iris

3

Thanks Iris,

Indeed it is a bit shorter,

Filip

4

Pasting the following into a JPython Function node would work:

"col1:" + val("col1") + " col2:" + val("col2")

 

Another option, which does not require typing column names, is the Perl Scripting node.

In the configuration GUI, just double-click the columns you want.  Make sure they are separated by a semi-colon.  The node needs these 'declared' so that the special variables %column and @column_names get assigned.  (would also be nice if the node had a way to say that all columns should be used, without having to select, but I don't know how to do this.)

For example:

$column{'col1'};$column{'col2'}; # Added by double-clicking, this will vary depending on input table.
join(" ", map { $_ . ":" . $column{$_} } @column_names); # Returns the string you want, this stays the same regardless of input table or chosen columns.

 

Just remember to select 'String' for return type.

 

And of course there are also the MPI Scripting nodes that would allow a more powerful solution (configurable GUI).

5