Transpose

[Greggy]

Dear Knime community,

I’m facing a certain challenge to convert some of my database into a format that my management requires.

In short, I need to transpose a set of data presented in columns to a set of data rather presented in rows.

To make it clearer, look at the below explanation I’ve made:

Has anyone an idea on how to do this, please?

thank you

[Aswin]

This doesn’t look like a normal transpose. Do you want to rearrange your data by cell color? Or do you want to group the data in Column 2 based on the elements in Column 1? In the latter case I suggest using the GroupBy node with Column 1 as grouping column, and group the elements of Column 2 together as a List. Then, use the Expand Split Collection Column node to expand sideways. Then, apply Transpose node.

[Greggy]

Thank you Aswin. I actually put the colors, in thinking it would help for the understanding !
What node should I use to group the elements of column 2 together, knowing that the numbers of the second columns are linked to the numbers of the first column?
For instance, 7 & 6 from column 2 are linked to 5 from the first column. Hence the reason why when I transpose, 7 & 6 should be below 5 (see column 3 of the second chart). Not sure if I am clear. Please let me know otherwise…

[ipazin]

Hit here @Greggy,

welcome to KNIME Community Forum. This is what @Aswin had in mind if not mistaken:

Here is workflow attached so take a look and if any questions feel free to ask.
2019_05_28_Group&Transpose.knwf (11.6 KB)

Br,
Ivan

[Greggy]

Thank you Ipazin. That’s great!
It does work for two columns but if I have more columns (I actually have 11 columns). How can I do?
Any idea?

[Aswin]

Here you go:

(A bit annoying that I had to resort to a Java Snippet to create an empty column of type Int. Are there better ways to do this? Ideally the Constant Value Column would contain an “empty column” option)
KNIME_project3.knwf (28.8 KB)

[ipazin]

Hi there,

@Aswin

Are there better ways to do this?

Column Expression node creates empty column of type Int with missingValue() function and column type as Integer chosen. Rule Engine node without any rules creates an empty column but type String. To get type Int add rule FALSE => 1 (or any other number) i.e. some rule which will never be true but if would be true would have integer value. The latter is a bit weird but can do the job

@Aswin

Ideally the Constant Value Column would contain an “empty column” option)

Not sure Constant Value Column would be a best place to add this option but seems like an idea worth thinking about

Br,
Ivan

[Aswin]

…or a Math Formula with 0/0 as the expression. This yields NaN, and when one checks the “Convert to Int” box, the NaNs are converted to an empty column of type Int. (Edit: it turns out that also Double NaNs are converted to to an empty column, albeit a Double column)

Still, I think an “empty column” checkbox in the Constant Value Column node or a dedicated Empty Column node would be more elegant

[Greggy]

Thank again both of you for the effort!
I however notice that the Groupby node does not work when you have a repetition in the second column. For instance, with the below set of data the flow does not work.

Can you find an easy way to fix this?

Thank you in advance!

[Aswin]

Maybe using Sets instead of Lists solves that problem:
KNIME_project3.knwf (30.5 KB)

[Greggy]

Hi Aswin, I haven’t seen any change in the workflow, so it still breaks when I run the flow

[Aswin]

It looks the same from the outside, but I made changes in the configuration of the Create Collection Column and the GroupBy nodes. Are you sure you are looking at the updated workflow and not the old one?

Here is the result on my computer:

[Greggy]

Thank you so much Aswin! It looks to work now. I only need to conduct some checks but it seems it gives what I needed.

Just one point, my data were in Double and the flow didn’t work because of it. So I added the Double To Int node before the first transpose.

[Aswin]

Here is the workflow changed to work with Doubles instead of Ints so that you do not need a conversion node:
KNIME_project3.knwf (28.8 KB)