Pivoting data from repeating group columns to single column group

KNIME Analytics Platform
1

Hello

One issue I get with my survey data, that they produce lots of columns that hold same type of data, but in repeating groups of columns.

columnNames: A1,B1,C1,A2,B2,C2,A43,B43,C43
Responses: Name1,IDcode1,Answer1,Name2,IDcode2,Answer2,Name43,IDcode43,Answer43

How do I put them into single column group?

ColumnNames: A,B,C
Response1: Name1,IDcode1,Answer1
Response2: Name2,IDcode2,Answer2
Response3: Name43,Idcode43,Answer43

Any help would be much appreciated!

Thank you,

Milly

2

Close to what you need could be done by Unpivot node.

3

Yes, but doesn’t that just put all of the unpivoted columns into single column? I need to make sure the data groups remain on same row in different columns, so I can’t unpivot one column at a time (plus, there might be over a hundred columns that would mean to make over 100 unpivot nodes…)

4
  1. Unpivot
  2. String Replacer or Rule Engine to extract the new column names from the old ones
  3. Pivot
5

Thanks, it worked!