Transpose row to column and column to row

Type Category MSU 1-Jan 2-Jan
Overall HK Original 1 2
Overall HK Confirmed 1.2 1.2
Overall HK Received 1.3 1.3
Overall HK OOS 1.4 5
Overall Hair Original 1 2
Overall Hair Confirmed 1.2 1.2
Overall Hair Received 1.3 1.3
Overall Hair OOS 1.4 5

Below is the result i need, how to do? can you let me know details steps?

Type Category Date Original Confirmed Received OOS
Overall HK 1-Jan 1 1.2 1.3 1.4
Overall Hair 1-Jan 1 1.2 1.3 1.4
Overall HK 2-Jan 2 1.2 1.3 5
Overall Hair 2-Jan 2 1.2 1.3 5

1 Like

Hi,

I liked this one. Thanks for such a question. It was somehow challenging and made me think for a few minutes!
Here is the workflow:

Input:
2

Output:
3

I converted the MSU values into columns at first. Then 2 loops: One to loop over date columns and second one to loop over every 4 rows of data (as there are 4 unique values in MSU column).
The Column Expressions creates the “Date” column based on current column name and puts the values for MSU columns from current column (of date columns).
Column Filter node excludes MSU and the current column (the latter is set in flow variables tab).
And finally the GroupBy node aggregates rows based on Type, Category and Date and uses “Sum” function on MSU columns (Type based aggregation).

transpose.knwf (43.6 KB)

Best,
Armin

P.S. Sorry! I forgot to delete the Math Formula (Variable) node. It does nothing in this workflow. (I was going to use the iteration number of the first loop but then I changed my mind)

2 Likes

Hi @Rain0407

You can do this by first using the Unpivoting and than the pivoting node, see the attached workflow.

@armingrudd I would not solve this with a double loop, mainly because of the overhead. It is not feasible for real tables here.

Cheers, Iris transpose_Unpiv_Pivot.knwf (9.8 KB)

6 Likes

Thank you so much @Iris. I wasn’t familiar with the functionality of the Unpivoting node.
Thanks for teaching this to me. :blush::heart_eyes:
Cheers,
Armin

1 Like

Thank you @armingrudd @Iris !!!

Very helpful!!!

2 Likes

@armingrudd did you never see me unpivoting? I am a big fan of it :slight_smile:

1 Like

Hi, Iris!

Would I be able to perform this for a table that has repeated values?
Using the “First” aggregation mode would eliminate those…

Thank you!

2 Likes

Hi @ArthurMundim

yes this work for repeated values. Because I do use the row Id in the pivoting node. This does ensure that the original data table structure is recreated.

Best wishes, Iris

Hi @Iris,

I reopen this this topic because I have a question about the Unpivoting Node. I have a table with two columns. in the first column are the labels and in the scond there are the values. This Looks like this:


You can see that there are dublicate values in the lable column but unique values in the value column. I want the unique values in the pivoting table in the column [JOB]FaultMemReportMirrorMemoryDTCByStatusMask.DTC so the result should look like this

[JOB]FaultMemReportMirrorMemoryDTCByStatusMask.DTC
U140900
U012820
U140A00

This Job I have to do for all the row in my two column Input table. So how do I have to configurate the Unpivoting Node and the Pivoting Node?

Thx and greetings,
Brotfahrer

Hi there @Brotfahrer,

if I got you right you don’t need to do any unpivoting but only use Pivoting node with no group columns, Label as pivoting column and Value as aggregation column with aggregation method List. Then use Ungroup node. That should do it :wink:

Br,
Ivan

4 Likes

Hi @ipazin,

perfect!

1 Like

Hi, I’m new to Knime and have a question regarding structuring some data to a “flatfile”.

|Row0|Row1|Row2| Row3| Row4| Row5|
|Store| Year|Month| Net sales| Other income| Sum revenue|
|Store 1|2021|Jan| 100| 50| 150|
|Store 1|2021|Feb| 110| 60| 170|
|Store 1|2021|Mar| 120| 70| 190|
|Store 2|2021|Jan| 190| 140| 330|
|Store 2|2021|Feb| 200| 150| 350|
|Store 2|2021|Mar| 210| 160| 370|
|Store 3|2021|Jan| 280| 230| 510|
|Store 3|2021|Feb| 290| 240| 530|
|Store 3|2021|Mar| 300| 250| 550|

Below is the result i need, how to do? can you let me know details steps?

|Store|Year|Month|Type|Value|

|Store 1|2021|Jan|Net sales|100|
|Store 1|2021|Feb|Net sales|110|
|Store 1|2021|Mar|Net sales|120|
|Store 1|2021|Jan|Other income|190|
|Store 1|2021|Feb|Other income|200|
|Store 1|2021|Mar|Other income|210|
|Store 1|2021|Jan|Sum revenue|280|
|Store 1|2021|Feb|Sum revenue|290|
|Store 1|2021|Mar|Sum revenue|300|
|Store 1|2020|Jul|Sum revenue|310|
|Store 1|2020|Aug|Sum revenue|320|
|Store 1|2020|Sep|Sum revenue|330|
|Store 1|2020|Oct|Sum revenue|340|
|Store 1|2020|Nov|Sum revenue|350|
|Store 1|2020|Dec|Sum revenue|360|
|Store 2|2021|Jan|Net sales|360|
|Store 2|2021|Feb|Net sales|360|
|Store 2|2021|Mar|Net sales|360|
|Store 2|2021|Jan|Other income|360|
|Store 2|2021|Feb|Other income|360|
|Store 2|2021|Mar|Other income|360|
|Store 2|2021|Jan|Sum revenue|360|
|Store 2|2021|Feb|Sum revenue|360|
|Store 2|2021|Mar|Sum revenue|360|
|Store 2|2020|Jul|Sum revenue|360|

Thanks a lot!

Hi and welcome
Looks like an unpivot operation. Have a look at that node.
Just to let you know. It’s always best to upload a sample file (excel, csv,… attached)
br

KNIME.xlsx (44.6 KB)

Thank you very much! I’ve thought about “groupby” and then “unpivot” by not sure how to get the data values correct for each line item - below I uploaded the file, would really appreciate if you could have a quick look at it. BR Fredrik

Hi @fredrikt

As @Daniel_Weikert suggest, go for the Unpivot node. Configure it like this


gr. Hans

Thanks :slight_smile: But this wont solve it - I will have all values in one column, but not split by type net sales/cogs/other income?

Hi @fredrikt

to make the unpivot node work. I made some changes to the Excel Reader configuration, so I would have a “normal” data-set.
Schermafdruk van 2021-09-04 08-55-55
The outcome of my workflow looks like:
Schermafdruk van 2021-09-04 08-52-30

See the workflow: transpose.knwf (37.7 KB)

gr. Hans

2 Likes

Got it! Thank you so much - you are an angel!

Regarding column “Year” and column “month” which is now in separate columns - is it possible to merge them info a single column called “date” and instead of the format 2021 Jan, 2021 Feb, 2021, Mar - show something like e.g. Jan-21. Feb-21 and Mar-21?

BR

Fredrik

2 Likes