Transposing table with pivoting (not Transpose node)

KNIME Analytics Platform
1

Hello again,

I’m encountering another problem which is not easily solved via Table Transposer Node.

Currently my table looks something like this:

Country Images Templates Chapters
USA 0 38 1
Belgium 23 2 2
Netherlands 4 345 0
Switzerland 32 21 0
Germany 0 0 54
Austria 0 1 12

Obviously, I need columns and rows transposed. Easily done in Excel but in Knime I’ll encounter the error “Execute failed: Unsupported column type ‘?’”.

I also tried the Column Auto Type Cast Node but it’ll corrupt the data types of the results in the matrix (String instead of integer).

Is there any way I can transpose via Unpivot and Pivot? If so, how may an attempt look like?

Thank you!

1 Like
2

Hello @PeoplesAnywhere
You are right, depending on the Pivot() content, sometimes you get Unsupported column type ‘?’ in return.

‘Column Auto Type Cast’ node would be my first choice as well. And I cannot deal with the error without see the data.

If your columns in string format are correct, you can use ‘String Manipulation (Multi Column)’ to convert target columns into integer:

numeric toInt() toDouble()…

toInt($$CURRENTCOLUMN$$)

BR

2 Likes
3

Thanks @gonhaddock again for your quick answer.

I tried switching from String to Integer but it fails because transposing creates a new row header. The former header now is recognized as String like its values below and cannot be changed to Integer.

Any idea on that?

4

I see now the problem.
Your best approach is this:

image

Split the first row with ‘include rows by number’ option. Then follow the sketch configuration.

Let me know for further advice.

BR

1 Like
5

Where can I find the Insert Column Header Node?
I’m using Knime 5.2.1 and there’s only a Column Renamer (Dictionary) Node looking exactly like the one in your screenshot.

6

In addition:

Can you use your mentioned apporach, if there’s another header in a second row present?

Example looks like this:

Category Graphics Content Other
Type Images Templates Chapters
USA 4 23 1
Belgium 0 0 43
Netherlands 21 22 5
Switzerland 76 0 0
Germany 1 0 1

Many thanks for your suggestions so far!

7

Hello @PeoplesAnywhere

Please check this workflow; you will get the chance to upload missed extensions.

20240305_count_string_occurrences_v0.knwf (67.5 KB)

BR

1 Like
8

Thank you! I got it work thanks to your help!

Now I will try to get another header (Graphics, Content, Other) on top of the other. If you have an idea for that, too, I’d be very grateful.

9

Hello @PeoplesAnywhere
Just nest the Headers: like ‘Graphics_Images’, ‘Content_Templates’…

BR

10

@gonhaddock okay. Thing here is it’s two different columns which originate from a Joiner Node.
How’s it possible to nest the two columns and “unnest” them in the end?

11

Hello @PeoplesAnywhere
All this would be easier if you can provide some sample data.

But, it is all about pivots, unpivots…

In the latest image that you posted, the procedure is similar to the workflow. Splitting two rows instead the first, and the string manipulation for nesting just after unpivot the headers dictionary. Then use the nested column as dictionary.

BR

12

@gonhaddock certainly.

Please find attached a simplified version of the attempt so far.
The Cell Splitter node is set to first and second row.
The Column Renamer node errors because of the duplicates as I haven’t found the solution to nest both headers with the String Manipulation node.

Project_Countries_Categories_Content.knwf (30.1 KB)

13

Hello,
Please check the following workflow. I’ve posted two alternative solutions for your use case.

20240306_Project_Countries_Categories_Content_v0.knwf (97.1 KB)

The second is more simple.

BR

2 Likes
14

@gonhaddock Thanks alot for both of your solutions!

Since the header is now nested, how do I get the first header on top of the second one like shown in my example above?

15

Hello @PeoplesAnywhere
I think we need some more context. KNIME is a data analysis tool; and you are requesting now a specific visualization.

In terms of analysis you can keep the data ‘long’, allowing to keep $Category$ and $Type$ in separated-wise lists; or ‘wide’, and aiming to do so, headers had to be nested. There isn’t an option for double header; you cannot merge upper header either for category sets.

Summarizing, data analysis is arranged in columns, and the requested visualization is arranged in a cell to cell basis as in excel/spreadsheet

In terms of visualization, we are not talking about data wrangling anymore. You would have to create visualization components. For your use case I guess HTML w. CSS coding widgets, they can deliver such type of visualization.

BR