Transposing Values in String Column

KNIME Analytics Platform
1

I have 2 columns as follows.
ID1 ID2
=== ===
37694272150,"{37679554832, 37679697110, 37679890168, 37679555678, 37679445246, 37680578814, 37680279266}"
37694531072,"{37825246513, 37604994707, 37595808756, 37599158594, 37605674627}"
I would like to transpose the data as follows:

ID1 ID2
37694272150 37679554832
37694272150 37679697110
37694272150 37679890168
37694272150 37679555678
37694272150 37679445246,
37694272150 37680578814
37694272150 37680279266
37694531072, 37825246513
37694531072 37604994707
37694531072 37595808756
37694531072 37599158594
37694531072 37605674627

I tried transpose but not able to achieve it. Any help is appreciated.

2

Hi @rameveer007,
I am not sure if I understand the formatting of your table correctly. Maybe you could try using a combination of the Cell Splitter Node and the Ungroup Node found here:
Cell Splitter
Ungroup
If that did not help it would be great if you could provide a real example table to better grasp the current state of your data.

Best regards,
Felix

3

As this doesn’t seem like a development topic, I have moved it to the Analytics Platform forum, where maybe you will get a bit more visibility. :slight_smile:

1 Like
4

Sure. Thank you.

Col1 = 37694272150 (Key1)
Col2 = “{37679554832, 37679697110, 37679890168, 37679555678, 37679445246, 37680578814, 37680279266}”
Col1 = 37694531072 (Key2)
Col2 = “{37825246513, 37604994707, 37595808756, 37599158594, 37605674627}”

Output I would like is as follows.

E Analytics Platform

Transposing Values in String Column

KNIME Analytics Platform

1

/

3

rameveer007

3

13h

I have 2 columns as follows.
ID1 ID2
=== ===
37694272150,"{37679554832, 37679697110, 37679890168, 37679555678, 37679445246, 37680578814, 37680279266}"
37694531072,"{37825246513, 37604994707, 37595808756, 37599158594, 37605674627}"
I would like to transpose the data as follows:

Col1 Col2
37694272150 37679554832 (Key1)
37694272150 37679697110 (Key2)
37694272150 37679890168 (Key 3)
37694272150 37679555678 (…)
37694272150 37679445246,
37694272150 37680578814
37694272150 37680279266
37694531072, 37825246513
37694531072 37604994707
37694531072 37595808756
37694531072 37599158594
37694531072 37605674627

5
  1. get rid of ( and " in column 2 using String Manipulation node.
  2. Use Split Column nod to convert Col2 to the number of columns.
  3. Unpivot your table using new columns as values (first column will stay).
1 Like
6

I will try that. Just to clarify my question. I am putting image.

Current
Col1 (Current Key) Col2
37694272150 {37679554832, 37679697110, 37679890168, 37679555678, 37679445246, 37680578814, 37680279266}
Transposed
Col1 (Proposed Key 1) Col1 (Proposed Key 2)
37694272150 37679554832
37694272150 37679697110
37694272150 37679890168
37694272150 37679555678
37694272150 37679445246
37694272150 37680578814
37694272150 37680279266
7

I used a combination of Transposes to hopefully get there. Maybe you have a look.

image
image708×508 126 KB

kn_example_transpose2.knwf (76.8 KB)

8

Hi @rameveer007,

use a String Manipulation to get rid of { and }, a Cell Splitter to convert Col2 into List and an Ungroup to convert List values into Column.
ColListToTable.knwf (8.6 KB)

4 Likes
9

well also unpivoting seems to do the trick …

image
image1714×944 268 KB

kn_example_transpose2.knwf (81.5 KB)

2 Likes
10

Thank you very much. The flow was extremely helpful and the suggestion from previous user as well. You guys are the best !!

4 Likes