[Basic] Grouping and Pivoting a table

Hi,
I’m struggling with a basic operation (I am still discovering Knime :sweat_smile:)

I have the following table

User Answer
User 1 User 1_Answer 1
User 2 User 2_Answer 1
User 3 User 3_Answer 1
User 4 User 4_Answer 1
User 5 User 5_Answer 1
User 1 User 1_Answer 2
User 2 User 2_Answer 2
User 3 User 3_Answer 2
User 4 User 4_Answer 2
User 5 User 5_Answer 2

And I’d like to pivot the table like this:

User Answer 1 Answer 2
User 1 User 1_Answer 1 User 1_Answer 2
User 2 User 2_Answer 1 User 2_Answer 2
User 3 User 3_Answer 1 User 3_Answer 2
User 4 User 4_Answer 1 User 4_Answer 2
User 5 User 5_Answer 1 User 5_Answer 2

My first idea would be to add a “Group” column to the table and then pivot it:

User Answer Group
User 1 User 1_Answer 1 1
User 2 User 2_Answer 1 1
User 3 User 3_Answer 1 1
User 4 User 4_Answer 1 1
User 5 User 5_Answer 1 1
User 1 User 1_Answer 2 2
User 2 User 2_Answer 2 2
User 3 User 3_Answer 2 2
User 4 User 4_Answer 2 2
User 5 User 5_Answer 2 2

But even adding the Group column is not so easy (at least for me ^^).
I tried to increase the Group value every time we “reach” a “User 1” row but did not find a proper way to do it.

All in all, I suspect there would be a much more efficient way to go from the initial state of my table to the expected output.

Thanks,
Pierre-Louis

Hi @pierrelouis ,
I have thought of two methods. Personally, I suggest using the first one. The idea is: 1. Group by users, but put the answers in a List. 2. Expand the List in the column direction.
Among them, I added two more rows to the test data.


group example.knwf (29.1 KB)

3 Likes

Welcome to the Forum - good news - your thoughts go entirely into the right direction.

You can use a Cell Splitter before Pivoting to split the Anwer columns using the _ as delimitter.

Example here:
BasicPivoting.knwf (82.5 KB)

1 Like

Hi @tomljh,

I applied the first method and, I agree, it seems the most “elegant” to me.
Aggregating answers in a list before splitting back such a list into columns is a nice trick :ok_hand:
(but it’s true that aggregating into list is not the first function most of us think of I guess ^^)

Thanks a lot :pray:

1 Like

Thanks @MartinDDDD !

I should have been more careful in the way I described my problem as the answers can actually be 0 or 1, like this:

User 1 0
User 2 0
User 3 1
User 4 0
User 5 1
User 1 1
User 2 1
User 3 0
User 4 0
User 5 1

… making it impossible to extract the “grouping” information from it as you did.

I see - so you can still group by User column and pivot by answer column - what you are then missing is a third column to aggregate on What do you want to see in the rows of your 0 and 1 columns? User 1, User 2 etc?

In that case you can just replicate the User Column (e.g. String Manipulation Node or Column Expressions Node) and then use that new column as aggregation column…

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