How do I move rows into columns

KNIME Analytics Platform
1

I have a text file with rows of data. I want to take every 4 rows and move them into columns:

row 1 → output row 1, column 1
row 2 → output row 1, column 2
row 3 → output row 1, column 3
row 4 → output row 1, column 4
row 5 → output row 2, column 1
row 6 → output row 2, column 2
row 7 → output row 2, column 3
row 8-> output row 2, column 4
etc

How can I do this. I couldn’t see how pivot or transpose could do this.
Thanks

then write out to excel…

2

Sounds like a job for a loop.

Can you use Chunk Loop Start node and set chunk size to 4.

You then will have to create a new column with an ID - e.g. use Column expressions and add a new column “ID” using rowIndex() function.

Then pivot by that ID column, group / aggregate as needed and then send it to loop end…

You should then have your columns 1-4 at the end and then you can just rename…

2 Likes
3

Thanks. Can you provide a little more detail on the adding the new column and row index.

4

Sure - this is what I have in mind:

image
image2458×1238 93 KB

In order to collect results at the end of the loop you need to make sure that the new column headings are the same. The easiest way to achieve this is to create at every iteration a new unique ID that you then pivot by…

An alternative that can work if you have multiple columns in your input data set is to reset the row ids, then to transpose…

Both options are in this prototype WF:

looppivot.knwf (88.9 KB)

3 Likes
5

Thank you for the example flow, which I have loaded and runs with the desired output. I understand the concept of creating the unique ID every ‘chunk’ , but can’t see where that is actually happening! Can you enlighten me please?

6

Well in the first example (with pivot node) it is happening in the column expressions node (see config dialogue in screenshot in my earlier post as well):

image
image1106×607 36.6 KB

In the second example the Row IDs are reset - once you “flip a table” on its side using Table Transposer, the RowIDs are used as column headers. To ensure that the column headers of all 4 columns are always the same, this node resets them. This makes sure that e.g. in the second iteration, when Row4-Row7 of your original dataset are processed, these are changed to Row0-Row3 again:

image
image1219×747 40.6 KB

2 Likes
7

Hello there!

You can use Pivoting node for this only before that you need to create sequence column (which will be actually Pivoting column) with 1,2,3,4 repeating itself (or generalized 1,2,3,…,n where n is number of wanted/needed columns).

For creating sequence column see here:

In Pivoting node you don’t need grouping column but rather List aggregation for which you then use Ungroup node to get desired format. However if you also create sequence grouping column you won’t need Ungroup node as that will be your grouping column.

Br,
Ivan

8

Thanks. I think the ‘evaluate on first row’ is important to mention as it means the same id is output for each row in the chunk and give the pivot node the column to group by?
image

9

Hey,

The evaluate on first row button just shows a preview - it applies your formula to the very first row and shows the result.

2 Likes