Columns to "matrix" pivote/transpose

Seems ("as usual") straightforward, but I am still stuck:

I have a table with a column and a set  number of rows that I want to transform into a 24*12 matrix (and at the same time colour the cells according to second column).

A loop within a loop using pivot? Or Transpose? Or is there a single node that can fix this?

I enclose a case as file (I want Tm in matrix formate and coloured by Call)

Maybe someone did something similar? This is basically the text output from a Roche Lightcycler (though the problem per se is of a table-handling nature).

Hi Docminus,

this sounds similar to this problem. Basically, you just have to compute row- and column-indizes from your Position column or the row indizes (e.g. with Math Formula Nodes) and then pivot.

If I recall correctly the pivoting Node is not very good at handling colours, so for the colouring step either color after the pivot or compute colours before/in parallel to pivoting with a Color Manager and then (re-)apply afterwards with a Color Appender.

Hope this helps.

Try the column to grid node.

simon.

No way it is that simple Simon ;-P

Thanks for that. That's the most important part. Though the problem with the colouring still remains.

Marlin - thanks for your tip as well, haven't check it out yet aside from the color appender and that doesn't seem to work as hoped.

Docminus,

don't bother trying my tip. I've already used Simons suggestion myself and replaced a Pivot in one workflow, because the Column To Grid is also way faster than the Pivot.

Now about that coloring... I'm not sure I fully understand what your desired result is. After looking at it again, it seems like you might be trying to color individual cells or columns? That would not be possible, as far as I know. If that's not what you want, could you elaborate on your desired result a bit more?

Marlin,

you understood correctly. I wanted to color the individual cell based on another column (or row) connected to this result matrix. I guess I will have to do it manually as before in Excel.

Docminus,

what a shame... seems like such a basic capability, right? It could be that there is, but if so, I have yet to see it. I could have used that ability too once, and ended up just replacing values by colors completely, because "pure" colors can be rendered in cells, just not in conjunction with a value. So basically I created an interactive heat map. In the same way you could replace your values by their colors or store colors in a second column besides your values, but... yeah... not ideal.

Of course you can always use Knime to "preprocess", export to excel, and then use Conditional Format for the colors, that might take away some of the manual work...

To developers: This would be an ideal task for an AdapterCell, right? And maybe a custom renderer could inject colors into existing renderers from outside as a kind of RendererAdapter?

I have put in many posts over the years for this exact functionality to colour individual cells but there has been a lot of reluctance to take it up.

simon.

Well I would give it a try with the idea I had (I'm waiting on some other questions in the developers corner and also for inspiration to strike on some other issues, anyway) but the developers where very liberal with everything to hinder the proper reuse of already existing code, which is quite discouraging. It's like they're trying to force me to copy their source code verbatim so they have legal leverage or something. I'm not saying they are, but man, this "open platform" is like a jungle enhanced with brick walls...

The Erlwood Community nodes have just had a major update carried out to them and released today. You will need to go to the File/Install KNIME Extensions menu to locate them under the ChemInformatics section in the Stable build.

These provide a new node called XLS Coloured Writer, this outputs the KNIME Table to an Excel sheet, but you will notice there is a Colouring Tab which allows you to colour individual columns cells by a set of rules for numerical and string columns.

I hope this delivers what you are trying to do.

Simon.