How to merge two rows with the same name into one Column?

Hi everyone, hope you are well.

I am using Knime for my assignment and was wondering if I could have some help with it.

I have some information (Pokemon related XD) where they define all of their information. I was trying to do a value counter for how many of those pokemon have a certain type - the example in the pictures are Bug. However, some Pokemon have two types and Bug is their secondary type So I tried to do a value counter where one focuses on Type No. 1 and the other focuses on Type No. 2.

After that, I would like to add both of them into one column (since they both have the same name) - but it keeps saying “dup”. I just want the values to be in one column labelled “Bug”

If you can help me, that would be very much appreciated.

Kind Regards

Adam

image

image

For instance, where they say “Bug (2)”, I would like that paired with “Bug” in the same column and the value is just added to it.

Instead of Value Counter use Group by node. Group on a type and calculate 2 columns.
Then use Math Formula to add 2 values on the line.

Ok. So If I wanted to group by Bug, how would I do it?

It will not by Bug. It will be by all the names. Use sum function for 2 columns with types.

I want the amount of times that Bug appears in the list from both Type No. 1 and Type No. 2 into one column. It seems so simple but I am super confused.

Sorry, you need to use Count function.

So, when you mean group by all the names, do you mean all the variables or all the different types?

Hello @Platinum_2021,

and welcome to KNIME Community!

Sounds simple but seems not so simple :slight_smile: After Value Counter node instead of Concatenate node use Full outer join from Joiner (Labs) with RowIDs as matching columns. Follow it with Column Aggregator to sum up two count columns.

Give it a try and if still having troubles can make you example.

Br,
Ivan

Hey Adam,

I believe this can be achieved with Unpivoting having the columns containing the bug types included as “Value Columns” and excluding the rest. This will give you a table with one column containing all bug type occurrences—on which you can do, e.g., a GroupBy.

Hope this might be helpful.

Sincerely,
Joachim

2 Likes

Hello,

actually Unpivoting node does the trick (again!).

Tnx for sharing simple solution and welcome to Community @jofi!

Br,
Ivan

Thanks for the welcome, @ipazin. I’m happy to contribute!

Sincerely,
Joachim

1 Like

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