Unpivoting Multiple Columns

[RedJungleBear]

Hi,

I have a data set that downloaded in this format:

However, I’d like to transform it so it looks like this:

The issue is that the number of date/action/value sets per ID varies from 2 to 20 and I have 10,000 rows. Is there an easy way to do this in KNIME?

Thanks

[armingrudd]

Hi @RedJungleBear and welcome to KNIME Community Forum,

The short solution is to use Column Aggregator to aggregate each group of columns (dates, actions and values) with the “List” method and then use the Ungroup node. But since you have not provided the column names and maybe the data set does not contain meaningful column names, I have provided another solution which does not rely on column names but works with the order of the columns:

unpivot_pivot.knwf (41.7 KB)

[ipazin]

Hi there @RedJungleBear,

welcome to KNIME Community!

Should be pretty easy. As @armingrudd suggested use Column Aggregator once for each group of columns and after that simply use Ungroup node. If you column names are similar (eg. Date1,Date2,Date3…) you can use Wildcard/Regex Selection option in Column Aggregator nodes to define your groups.

If any issues/questions feel free to ask.

Br,
Ivan