Transposing, Pivoting, Column split help

Hello All,

I am working with some data that is being imported to a table after a loop and is then manipulated to a columns. Once its in columns I am trying to Transpose and pivot the information into an easy scrollable list. I typed out a chart of example format below.

|A) |s |$ |1.1 GG| |B) |s |$ |2.1 GG| |C) |s |$ |3.1 GG|
|A) |s |@ |1.2 HH| |B) |s |@ |2.2 HH| |C) |s |@ |3.2 HH|
|A) |s |# |1.3 II| |B) |s |# |2.3 II| |C) |s |# |3.3 II|
|A) |s |& |1.4 JJ| |B) |s |& |2.4 JJ| |C) |s |& |3.4 JJ|

The hoping to finish with something like this removing the “s” column and the space between the “GG,HH,II,JJ”

 $  @	 #   &

A) 1.1 | 1.2| 1.3 |1.4
B) 2.1 |2.2 | 2.3 |2.4
C) 3.1 | 3.2| 3.3 |3.4

The final thing I want to be able to add is the URL path from the file directory when I import the data form the list of files and file reader. I’ve read some similar post using the tring replace and giving the rows a new number and then sorting them numerical but this seems to be complicated.

I appreciate the help! I’ve been learning alot with knime in the last month.


I constructed a workflow that does what you want and hopefully also demonstrates what you could use to ‘detect’ structures.

  • first you load the data
  • the you transpose the first row
  • you select the rows that match you ‘headers’ (the A, B, C)
  • you identify the rows that contain your number pattern with Regex
  • you assign them row numbers
  • you match them back together
  • you iterate over these ‘pairs’ (header and value column)
  • the value column has to be split up by RegEx and converted into numbers
  • a final transpose gives you the result
  • the Loop end collects the results

(\d+.\d+)(\s+)(\D+) => one or more numbers, followed by a dot, followed by one or more numbers (would have to check if there are integers you might have to change the regex (cf. for that). Followed by one or more Blanks, followed by Non-Numeric characters - if there are other patterns you might have to define them.

Result looks like this

kn_example_pivoting_selecting_columns.knwf (90.9 KB)