# Transpose

**URL:** <https://forum.knime.com/t/transpose/15311>\
**Category:** KNIME Analytics Platform\
**Created:** [May 27, 2019, 2:16pm UTC](https://forum.knime.com/t/transpose/15311 "2019-05-27T14:16:13Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Greggy](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/aca169/32.png) [@Greggy](https://forum.knime.com/u/Greggy)\
**Post date:** [May 27, 2019, 2:16pm UTC](https://forum.knime.com/t/transpose/15311/1 "2019-05-27T14:16:13Z")

</div>

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:

![image](https://forum-cdn.knime.com/uploads/default/original/2X/b/b4ff695592863170a809e9e1c9574b70a6a45fa0.png)

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

thank you

---

<div class="post-metadata">

**Author:** ![Aswin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/aswin/32/3344_2.png) [@Aswin](https://forum.knime.com/u/Aswin)\
**Post date:** [May 27, 2019, 3:34pm UTC](https://forum.knime.com/t/transpose/15311/2 "2019-05-27T15:34:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Greggy](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/aca169/32.png) [@Greggy](https://forum.knime.com/u/Greggy)\
**Post date:** [May 28, 2019, 11:18am UTC](https://forum.knime.com/t/transpose/15311/3 "2019-05-28T11:18:54Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\
**Post date:** [May 28, 2019, 11:35am UTC](https://forum.knime.com/t/transpose/15311/4 "2019-05-28T11:35:16Z")

</div>

Hit here @Greggy,

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

![G%26T](https://forum-cdn.knime.com/uploads/default/original/2X/b/b484a6542a45bae9e623efd2ac1a14a1fc67f0e1.png)

Here is workflow attached so take a look and if any questions feel free to ask.  
[2019\_05\_28\_Group&Transpose.knwf](https://forum-cdn.knime.com/uploads/default/original/2X/b/b227ed95f7c024ad76f749341da4bced1e866ca4.knwf) (11.6 KB)

Br,  
Ivan

---

<div class="post-metadata">

**Author:** ![Greggy](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/aca169/32.png) [@Greggy](https://forum.knime.com/u/Greggy)\
**Post date:** [May 28, 2019, 1:23pm UTC](https://forum.knime.com/t/transpose/15311/5 "2019-05-28T13:23:15Z")

</div>

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?

 ![image](https://forum-cdn.knime.com/uploads/default/original/2X/e/e8e4007cb4bd2114de3029b4edb7469782fd6e2c.png)

---

<div class="post-metadata">

**Author:** ![Aswin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/aswin/32/3344_2.png) [@Aswin](https://forum.knime.com/u/Aswin)\
**Post date:** [May 28, 2019, 2:34pm UTC](https://forum.knime.com/t/transpose/15311/6 "2019-05-28T14:34:58Z")

</div>

Here you go:

 ![image](https://forum-cdn.knime.com/uploads/default/original/2X/b/b3685fdf4e9298154b2d7bf526040f694f0c9110.png)

(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](https://forum-cdn.knime.com/uploads/default/original/2X/e/e1218bb2ef0eb86e9325fcf52afc1469bb039f0b.knwf) (28.8 KB)

---

<div class="post-metadata">

**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\
**Post date:** [May 28, 2019, 9:31pm UTC](https://forum.knime.com/t/transpose/15311/7 "2019-05-28T21:31:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Aswin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/aswin/32/3344_2.png) [@Aswin](https://forum.knime.com/u/Aswin)\
**Post date:** [May 29, 2019, 8:20am UTC](https://forum.knime.com/t/transpose/15311/8 "2019-05-29T08:20:11Z")

</div>

…or a Math Formula with `0/0` as the expression. This yields `NaN`, and when one checks the “Convert to Int” box, the `NaN`s are converted to an empty column of type Int. (Edit: it turns out that also Double `NaN`s 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 🙂

---

<div class="post-metadata">

**Author:** ![Greggy](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/aca169/32.png) [@Greggy](https://forum.knime.com/u/Greggy)\
**Post date:** [May 29, 2019, 9:37am UTC](https://forum.knime.com/t/transpose/15311/9 "2019-05-29T09:37:02Z")

</div>

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.

![image](https://forum-cdn.knime.com/uploads/default/original/2X/2/221e742d54317ba4cd29058970edc583cd44b5e7.png)

Can you find an easy way to fix this?

Thank you in advance!

---

<div class="post-metadata">

**Author:** ![Aswin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/aswin/32/3344_2.png) [@Aswin](https://forum.knime.com/u/Aswin)\
**Post date:** [May 29, 2019, 10:40am UTC](https://forum.knime.com/t/transpose/15311/10 "2019-05-29T10:40:38Z")

</div>

Maybe using Sets instead of Lists solves that problem:  
[KNIME\_project3.knwf](https://forum-cdn.knime.com/uploads/default/original/2X/f/f26ffd6bbe31a7e74c6eb44f3da45b853210b38e.knwf) (30.5 KB)

---

<div class="post-metadata">

**Author:** ![Greggy](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/aca169/32.png) [@Greggy](https://forum.knime.com/u/Greggy)\
**Post date:** [May 29, 2019, 11:04am UTC](https://forum.knime.com/t/transpose/15311/11 "2019-05-29T11:04:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Aswin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/aswin/32/3344_2.png) [@Aswin](https://forum.knime.com/u/Aswin)\
**Post date:** [May 29, 2019, 11:09am UTC](https://forum.knime.com/t/transpose/15311/12 "2019-05-29T11:09:52Z")

</div>

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:

 ![image](https://forum-cdn.knime.com/uploads/default/original/2X/5/5cb892c031a9dd8c98b6ab1d74027104aeed6073.png)

---

<div class="post-metadata">

**Author:** ![Greggy](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/aca169/32.png) [@Greggy](https://forum.knime.com/u/Greggy)\
**Post date:** [May 29, 2019, 11:38am UTC](https://forum.knime.com/t/transpose/15311/13 "2019-05-29T11:38:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Aswin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/aswin/32/3344_2.png) [@Aswin](https://forum.knime.com/u/Aswin)\
**Post date:** [May 29, 2019, 11:49am UTC](https://forum.knime.com/t/transpose/15311/14 "2019-05-29T11:49:30Z")

</div>

Here is the workflow changed to work with Doubles instead of Ints so that you do not need a conversion node:  
[KNIME\_project3.knwf](https://forum-cdn.knime.com/uploads/default/original/2X/f/fad7cbb4ee743546ffbba9fc975ea5828f0af67a.knwf) (28.8 KB)

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [June 5, 2019, 11:49am UTC](https://forum.knime.com/t/transpose/15311/15 "2019-06-05T11:49:34Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
