Transposing, Pivoting, Column split help


#1

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.

-Mike


#2

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. https://regex101.com 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)