copying columns with different names beneath other columns

[Mo_Gha]

Hello,
i want to copy all columns on the right side in a certain structure beneath ohther columns. To be more precise about the structure, I have on the left side my testdata, next to it my reference data and on the right side my result (wheater they are the same or not). I made this comparison for many different “attributes” (see column headers) and now I just want to put them in that structure i described.

Here is an example:

In the end i should get a long table with 6 columns somehow like this:
Row ID | Columnname_test (Attribute) | Columnname_reference (Attribute) | Column Value 1 | Column Value 2 | Result (Comparison)

I tried it with pivoting and transformation but it did not work out
I hope somebody can help me out.

Thank you very much!

[victor_palacios]

Hi,

I would use Column Filter, Column Rename, and Concatenate.

You use the column filter to get sets of target columns, rename the sets with column rename so that columns you want to stack have the same name, and then concatenate them one on top of the other. Hope that helps~

[Mo_Gha]

Hi @victor_palacios,

sorry for my late answer. How can I filter to get different sets of target columns? Because column expressions are not identical. Can you help me out?

Note: Do I have to use a loop or can i do it without?

Thanks alot

[victor_palacios]

Hi, I’m not sure what you mean.

1. To filter in KNIME you can use Row Filter or Column Filter depending on what you want to do.

2. Column Expression is a node, is that what you’re talking about?

3. You should always try to avoid loops as they are slow, but it depends on your goal.

It would be best to provide simple input and simple output that you’d like so I can understand the problem.

[Mo_Gha]

hey @victor_palacios,

sorry i mean the column filter. How do I have to use it? There are different options like regex etc. Which of them is relevant for me and how can i use it, so that groups are filtered in group of three attributes as i described above. Because always three attributes belong together. Hope you understand what I mean.

Thank you

[Daniel_Weikert]

Maybe you like to provide some demo data for your use case.
br

[victor_palacios]

Here’s a video for column filter: ETL with KNIME. The Column Filter Node - YouTube

[Mo_Gha]

Hi @victor_palacios,
thank you for that link, but thats not what i meant.
Below i provide a demo data, maybe that`ll help. Always three columns (next to each other) belong together, they have nearly the same name. I want to write them among each other, that means at the end there should only be three columns (and the column with Row ID).
Thanks
Demo Data.xlsx (10.9 KB)

[Mo_Gha]

@Daniel_Weikert @victor_palacios
Is the Demo Data ok? Can you work with it? I could not solve the problem yet. It would be nice if somebody can help me with that

[takbb]

Hi @Mo_Gha , so just to confirm understanding, your data as per your example has 13 columns

Rowid1| 6 x “General” cols | 6x"Granularity" cols
Rowid2| 6 x “General” cols | 6x"Granularity" cols
Rowid3| 6 x “General” cols | 6x"Granularity" cols
Rowid4| 6 x “General” cols | 6x"Granularity" cols
Rowid5| 6 x “General” cols | 6x"Granularity" cols

And you wish to “stack” these so that you have:

Rowid1| 6 x “General” cols
Rowid2| 6 x “General” cols
Rowid3| 6 x “General” cols
Rowid4| 6 x “General” cols
Rowid5| 6 x “General” cols
Rowid1| 6 x “Granularity” cols data
Rowid2| 6 x “Granularity” cols data
Rowid3| 6 x “Granularity” cols data
Rowid4| 6 x “Granularity” cols data
Rowid5| 6 x “Granularity” cols data

So the rowid gets repeated and the data from the granularity columns are placed at the bottom of the table which will just have columns labelled as “General” columns. Is that correct?

And will these columns always be named as per you example?

[Mo_Gha]

hi @takbb,

yes exactly like this. Yes the columns are always named like this, or to be more specific, the division and naming into three “categories” is always the same. (for example “text”, “text #1”, “comparison”).

Thank you!

[Daniel_Weikert]

Have you tried column splitter and then concatenate node?
br

[takbb]

Hi @Mo_Gha

I’m assuming the problem you are facing is that if you split the table into two, with the two sets of columns, when you try to concatenate the tables the rows don’t “stack” nicely, because you have different column names. It is a pity that the concatenate function doesn’t have an option to simply take the column names from the first table, and then concatenate by column position rather than name, which is what you are wanting to do here.

Your problem therefore involves an additional step. Firstly as @Daniel_Weikert has mentioned, you can use the Column Splitter or Column Filter. As both your resultant tables require one column in common, (ROW ID), I would in this case use two Column Filters. Both of these join to the original input data. You can use regex patterns to filter your columns.

One filter pattern would take the ROW ID and “General” columns
.(Row ID|General).

The other filter pattern would take the ROW ID and “Granularity” columns
.(Row ID|Granularity).

The next step, which is to rename the columns of the “Granularity” table to match the columns of the “General” table, so that it can be concatenated.

My method for doing this is as in the following image. Hopefully the comments in the diagram go some way to explaining how this works, but the net result of this (at the output of the “Insert Column Header” node is that the “Granularity” table now contains the column names from the “General” table.

Finally the tables can be concatenated:

It so happens that I have a component on the KNIME Hub that does the table renaming (using the above method). It was actually written to rename the default columns name from a “Table Creator” component, so it’s name is not obvious (may I should change that!).

The component is available here

Rename Table Creator Columns – takbb

Attached is a demo workflow containing both the “full” method, and the one using the component.

Split Columns and Concatenate Tables.knwf (50.5 KB)

If your table columns are subject to change, you’d need to adjust the regex pattern. Maybe you could use flow variables if this needs to be made more generic, but hopefully it gives some ideas and assistance.